Freemarker整合poi导出带有图片的Excel教程
blog.csdn.net/x541211190/… 1.根据此人博客可完成此功能，但原文中有一处错误需要纠正，当单元格合并时，计算起始单元格有错误。
源代码：
/**
* 构造合并单元格集合
*
* @param createRowIndex:
* @param cellRangeAddresses:
* @param startIndex:
* @param cellInfo:
* @param style:
* @return int
*/
private static int getCellRanges(int createRowIndex, List<CellRangeAddressEntity> cellRangeAddresses,
int startIndex, Cell cellInfo, Style style) {
if (cellInfo.getMergeAcross() != null || cellInfo.getMergeDown() != null) {
CellRangeAddress cellRangeAddress = null;
if (cellInfo.getMergeAcross() != null && cellInfo.getMergeDown() != null) {
int mergeAcross = startIndex;
if (cellInfo.getMergeAcross() != 0) {
// 获取该单元格结束列数
mergeAcross += cellInfo.getMergeAcross();
}
int mergeDown = createRowIndex;
if (cellInfo.getMergeDown() != 0) {
// 获取该单元格结束列数
mergeDown += cellInfo.getMergeDown();
}
cellRangeAddress = new CellRangeAddress(createRowIndex, mergeDown, (short) startIndex,
(short) mergeAcross);
} else if (cellInfo.getMergeAcross() != null && cellInfo.getMergeDown() == null) {
int mergeAcross = startIndex;
if (cellInfo.getMergeAcross() != 0) {
// 获取该单元格结束列数
mergeAcross += cellInfo.getMergeAcross();
// 合并单元格
cellRangeAddress = new CellRangeAddress(createRowIndex, createRowIndex, (short) startIndex,
(short) mergeAcross);
}
} else if (cellInfo.getMergeDown() != null && cellInfo.getMergeAcross() == null) {
int mergeDown = createRowIndex;
if (cellInfo.getMergeDown() != 0) {
// 获取该单元格结束列数
mergeDown += cellInfo.getMergeDown();
// 合并单元格
cellRangeAddress = new CellRangeAddress(createRowIndex, mergeDown, (short) startIndex,
(short) startIndex);
}
}
if (cellInfo.getMergeAcross() != null) {
int length = cellInfo.getMergeAcross().intValue();
for (int i = 0; i < length; i++) {
startIndex += cellInfo.getMergeAcross();
}
}
CellRangeAddressEntity cellRangeAddressEntity = new CellRangeAddressEntity();
cellRangeAddressEntity.setCellRangeAddress(cellRangeAddress);
if (style != null && style.getBorders() != null) {
cellRangeAddressEntity.setBorders(style.getBorders());
}
cellRangeAddresses.add(cellRangeAddressEntity);
}
return startIndex;
}
修改后代码：
/**
* 构造合并单元格集合
*
* @param createRowIndex:
* @param cellRangeAddresses:
* @param startIndex:
* @param cellInfo:
* @param style:
* @return int
*/
private static int getCellRanges(int createRowIndex, List<CellRangeAddressEntity> cellRangeAddresses,
int startIndex, Cell cellInfo, Style style) {
if (cellInfo.getMergeAcross() != null || cellInfo.getMergeDown() != null) {
CellRangeAddress cellRangeAddress = null;
if (cellInfo.getMergeAcross() != null && cellInfo.getMergeDown() != null) {
int mergeAcross = startIndex;
if (cellInfo.getMergeAcross() != 0) {
// 获取该单元格结束列数
mergeAcross += cellInfo.getMergeAcross();
}
int mergeDown = createRowIndex;
if (cellInfo.getMergeDown() != 0) {
// 获取该单元格结束列数
mergeDown += cellInfo.getMergeDown();
}
cellRangeAddress = new CellRangeAddress(createRowIndex, mergeDown, (short) startIndex,
(short) mergeAcross);
} else if (cellInfo.getMergeAcross() != null && cellInfo.getMergeDown() == null) {
int mergeAcross = startIndex;
if (cellInfo.getMergeAcross() != 0) {
// 获取该单元格结束列数
mergeAcross += cellInfo.getMergeAcross();
// 合并单元格
cellRangeAddress = new CellRangeAddress(createRowIndex, createRowIndex, (short) startIndex,
(short) mergeAcross);
}
} else if (cellInfo.getMergeDown() != null && cellInfo.getMergeAcross() == null) {
int mergeDown = createRowIndex;
if (cellInfo.getMergeDown() != 0) {
// 获取该单元格结束列数
mergeDown += cellInfo.getMergeDown();
// 合并单元格
cellRangeAddress = new CellRangeAddress(createRowIndex, mergeDown, (short) startIndex,
(short) startIndex);
}
}
//此处应该去掉for循环，合并后应该是直接加上合并后的单元格个数，并不是循环
if (cellInfo.getMergeAcross() != null) {
int length = cellInfo.getMergeAcross().intValue();
startIndex +=length;
}
CellRangeAddressEntity cellRangeAddressEntity = new CellRangeAddressEntity();
cellRangeAddressEntity.setCellRangeAddress(cellRangeAddress);
if (style != null && style.getBorders() != null) {
cellRangeAddressEntity.setBorders(style.getBorders());
}
cellRangeAddresses.add(cellRangeAddressEntity);
}
return startIndex;
}
2.在原基础上添加打印设置
/**
* 导出Excel到输出流（支持Excel2003版，xls格式）
*
* @param freemakerEntity
* @param outputStream
*/
private static void createImageExcleToStream(FreemarkerInput freemakerEntity, OutputStream outputStream) {
Writer out = null;
try {
// 创建xml文件
Template template = getTemplate(freemakerEntity.getTemplateName(), freemakerEntity.getTemplateFilePath());
File tempXMLFile = new File(freemakerEntity.getXmlTempFile() + freemakerEntity.getFileName() + ".xml");
FileUtils.forceMkdirParent(tempXMLFile);
out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(tempXMLFile), "UTF-8"));
template.process(freemakerEntity.getDataMap(), out);
if (log.isDebugEnabled()) {
log.debug("1.完成将文本数据导入到XML文件中");
}
SAXReader reader = new SAXReader();
Document document = reader.read(tempXMLFile);
Map<String, Style> styleMap = readXmlStyle(document);
log.debug("2.完成解析XML中样式信息");
List<Worksheet> worksheets = readXmlWorksheet(document);
if (log.isDebugEnabled()) {
log.debug("3.开始将XML信息写入Excel，数据为：" + worksheets.toString());
}
HSSFWorkbook wb = new HSSFWorkbook();
for (Worksheet worksheet : worksheets) {
HSSFSheet sheet = wb.createSheet(worksheet.getName());
Table table = worksheet.getTable();
List<Row> rows = table.getRows();
List<Column> columns = table.getColumns();
// 填充列宽
int columnIndex = 0;
for (int i = 0; i < columns.size(); i++) {
Column column = columns.get(i);
columnIndex = getCellWidthIndex(columnIndex, i, column.getIndex());
sheet.setColumnWidth(columnIndex, (int) column.getWidth() * 50);
}
int createRowIndex = 0;
List<CellRangeAddressEntity> cellRangeAddresses = new ArrayList<>();
for (int rowIndex = 0; rowIndex < rows.size(); rowIndex++) {
Row rowInfo = rows.get(rowIndex);
if (rowInfo == null) {
continue;
}
createRowIndex = getIndex(createRowIndex, rowIndex, rowInfo.getIndex());
HSSFRow row = sheet.createRow(createRowIndex);
if (rowInfo.getHeight() != null) {
Integer height = rowInfo.getHeight() * 20;
row.setHeight(height.shortValue());
}
List<Cell> cells = rowInfo.getCells();
if (CollectionUtils.isEmpty(cells)) {
continue;
}
int startIndex = 0;
for (int cellIndex = 0; cellIndex < cells.size(); cellIndex++) {
Cell cellInfo = cells.get(cellIndex);
if (cellInfo == null) {
continue;
}
// 获取起始列
startIndex = getIndex(startIndex, cellIndex, cellInfo.getIndex());
HSSFCell cell = row.createCell(startIndex);
String styleID = cellInfo.getStyleID();
Style style = styleMap.get(styleID);
/*设置数据单元格格式*/
CellStyle dataStyle = wb.createCellStyle();
// 设置边框样式
setBorder(style, dataStyle);
// 设置对齐方式
setAlignment(style, dataStyle);
// 填充文本
setValue(wb, cellInfo, cell, style, dataStyle);
// 填充颜色
setCellColor(style, dataStyle);
cell.setCellStyle(dataStyle);
//单元格注释
if (cellInfo.getComment() != null) {
Data data = cellInfo.getComment().getData();
Comment comment = sheet.createDrawingPatriarch()
.createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
comment.setString(new HSSFRichTextString(data.getText()));
cell.setCellComment(comment);
}
// 合并单元格
startIndex = getCellRanges(createRowIndex, cellRangeAddresses, startIndex, cellInfo, style);
}
}
// 添加合并单元格
addCellRange(sheet, cellRangeAddresses);
//添加打印设置
HSSFPrintSetup ps=sheet.getPrintSetup();
ps.setLandscape(false);//打印方向，true:横向 ，false:纵向
ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);//纸张A4
sheet.setMargin(HSSFSheet.BottomMargin,(double)0.5);//页边距（下）
sheet.setMargin(HSSFSheet.LeftMargin,(double)0.3);//页边距（左）
sheet.setMargin(HSSFSheet.RightMargin,(double)0.3);//页边距（右）
sheet.setMargin(HSSFSheet.TopMargin,(double)0.5);//页边距（右）
sheet.setHorizontallyCenter(true);//设置打印页面为水平居中
sheet.setAutobreaks(true);//将整个工作表缩放打印在一页上
}
// 加载图片到excel
log.debug("4.开始写入图片：" + freemakerEntity.getExcelImageInputs());
if (!CollectionUtils.isEmpty(freemakerEntity.getExcelImageInputs())) {
writeImageToExcel(freemakerEntity.getExcelImageInputs(), wb);
}
log.debug("5.完成写入图片：" + freemakerEntity.getExcelImageInputs());
// 写入excel文件,response字符流转换成字节流，template需要字节流作为输出
wb.write(outputStream);
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
log.error("导出excel异常：" + e.getMessage());
} finally {
try {
out.close();
} catch (Exception e) {
}
}
}
3.边框设置
/**
* 设置边框
*
* @param style:
* @param dataStyle:
* @return void
*/
private static void setBorder(Style style, CellStyle dataStyle) {
if (style != null && style.getBorders() != null) {
for (int k = 0; k < style.getBorders().size(); k++) {
Style.Border border = style.getBorders().get(k);
if (border != null) {
if ("Bottom".equals(border.getPosition())) {
dataStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
//当weight等于1时，线宽为默认样式；当weight等于2时，线宽为样式为中等粗线，其他样式可根据绘制Excel线宽进行对比weight值对应那种线宽样式
if(border.getWeight()==1){
dataStyle.setBorderBottom(BorderStyle.THIN);
}else if(border.getWeight()==2){
dataStyle.setBorderBottom(BorderStyle.MEDIUM);
}
}
if ("Left".equals(border.getPosition())) {
dataStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
//当weight等于1时，线宽为默认样式；当weight等于2时，线宽为样式为中等粗线，其他样式可根据绘制Excel线宽进行对比weight值对应那种线宽样式
if(border.getWeight()==1){
dataStyle.setBorderLeft(BorderStyle.THIN);
}else if(border.getWeight()==2){
dataStyle.setBorderLeft(BorderStyle.MEDIUM);
}
}
if ("Right".equals(border.getPosition())) {
dataStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
//当weight等于1时，线宽为默认样式；当weight等于2时，线宽为样式为中等粗线，其他样式可根据绘制Excel线宽进行对比weight值对应那种线宽样式
if(border.getWeight()==1){
dataStyle.setBorderRight(BorderStyle.THIN);
}else if(border.getWeight()==2){
dataStyle.setBorderRight(BorderStyle.MEDIUM);
}
}
if ("Top".equals(border.getPosition())) {
dataStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
//当weight等于1时，线宽为默认样式；当weight等于2时，线宽为样式为中等粗线，其他样式可根据绘制Excel线宽进行对比weight值对应那种线宽样式
if(border.getWeight()==1){
dataStyle.setBorderTop(BorderStyle.THIN);
}else if(border.getWeight()==2){
dataStyle.setBorderTop(BorderStyle.MEDIUM);
}
}
}
}
}
}
