添加依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
<!--- easyexcel依赖poi --->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.75</version>
</dependency>
Excel模板
最终效果
代码示例
模板上{.}与{前缀.}都是对应的是集合属性，{}里面要与属性字段名对应，如果模板里只有一个列表集合可用{.}，多个列表可使用多个{前缀.}添加多个
OutputStream out = null;
try {
File templateFile = new File("D:\\template.xlsx");
String excelType = templateFile.getName().substring(templateFile.getName().lastIndexOf(".") + 1);
HttpServletResponse response = getResponse();
out = response.getOutputStream();
response.addHeader("Content-Disposition","attachment;filename=" + new String("填充后的template".getBytes(),"iso-8859-1") + "." + excelType);
response.setContentType("application/msexcel;charset=UTF-8");
ExcelWriterBuilder easyExcelFactory = EasyExcel.write(out);
// easyExcey可以自动判断Excel类型，如果报错可以手动设置类型，个人测试如果模板不是本地文件，直接从网络获取的文件流的话，需要手动设置类型
// URL url = new URL("http://");
// HttpURLConnection httpURLConnection = (HttpURLConnection) url.openConnection();
// if (httpURLConnection.getResponseCode() == 200) {
// ExcelWriter excelWriter = easyExcelFactory.withTemplate(httpURLConnection.getInputStream()).build();
// }
// if ("xls".equals(excelType)) {
// easyExcelFactory.excelType(ExcelTypeEnum.XLS);
// }
ExcelWriter excelWriter = easyExcelFactory.withTemplate(templateFile).build();
WriteSheet writeSheet = EasyExcel.writerSheet().registerWriteHandler(new MergeStrategyHandler()).build();
// 写入普通变量
JSONObject jsonObject = new JSONObject();
jsonObject.put("total", 5000);
// 可以填充图片
// ByteArrayOutputStream byteArrayOut = null;
// try {
// String imageUrlStr = "http://127.0.0.1:8080/image.jpg";
// URL url = new URL(imageUrlStr);
// HttpURLConnection httpURLConnection = (HttpURLConnection) url.openConnection();
// if (httpURLConnection.getResponseCode() == 200) {
// BufferedImage bufferImg = ImageIO.read(url.openStream());
// byteArrayOut = new ByteArrayOutputStream();
// // 图片后缀格式
// String sfx = imageUrlStr.substring(imageUrlStr.lastIndexOf(".") + 1);
// ImageIO.write(bufferImg, sfx, byteArrayOut);
// bufferImg.flush();
// jsonObject.put("image", byteArrayOut.toByteArray());
// // esayExcel 3.x以上版本,可自行自定义图片位置及大小
// jsonObject.put("image", imageCells(byteArrayOut.toByteArray()));
// }
// } catch (IOException e) {
// e.printStackTrace();
// } finally {
// if (byteArrayOut != null) {
// byteArrayOut.close();
// }
// }
excelWriter.fill(jsonObject, writeSheet);
// 列表数据填充
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
// 如果有多个list 模板上必须有{前缀.} 这里的前缀使用表名，然后多个list必须用 FillWrapper包裹
JSONArray jsonArray = new JSONArray();
for (int i = 0; i < 3; i++) {
JSONObject obj = new JSONObject();
obj.put("field1", i + "_field1");
obj.put("field2", i + "_field2");
obj.put("field3", i + "_field3");
obj.put("field4", i + "_field4");
obj.put("field5", i + "_field5");
jsonArray.add(obj);
}
excelWriter.fill(new FillWrapper("table", jsonArray), fillConfig, writeSheet);
excelWriter.finish();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 模板中填充集合有合并单元格，第二行开始不会自动合并单元格，需要复制上一行的信息进行合并
*/
static class MergeStrategyHandler extends AbstractMergeStrategy {
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if (relativeRowIndex == null || relativeRowIndex == 0) {
return;
}
int rowIndex = cell.getRowIndex();
int colIndex = cell.getColumnIndex();
sheet = cell.getSheet();
Row preRow = sheet.getRow(rowIndex - 1);
Cell preCell = preRow.getCell(colIndex);
CellStyle cs = cell.getCellStyle();
cell.setCellStyle(cs);
for (CellRangeAddress cellRangeAddress : sheet.getMergedRegions()) {
if (cellRangeAddress.containsRow(preCell.getRowIndex()) && cellRangeAddress.containsColumn(preCell.getColumnIndex())) {
int lastColIndex = cellRangeAddress.getLastColumn();
int firstColIndex = cellRangeAddress.getFirstColumn();
CellRangeAddress cra = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), firstColIndex, lastColIndex);
sheet.addMergedRegion(cra);
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
return;
}
}
}
}
/**
* esayExcel 3.x以上版本，Excel填充的图片设置
* @param bytes
* @return
*/
public static WriteCellData<Void> imageCells(byte[] bytes) {
WriteCellData<Void> writeCellData = new WriteCellData<>();
// 这里可以设置为 EMPTY 则代表不需要其他数据了
//writeCellData.setType(CellDataTypeEnum.EMPTY);
// 可以放入多个图片
List<ImageData> imageDataList = new ArrayList<>();
writeCellData.setImageDataList(imageDataList);
ImageData imageData = new ImageData();
imageDataList.add(imageData);
imageData.setImage(bytes);
// 图片类型
//imageData.setImageType(ImageData.ImageType.PICTURE_TYPE_PNG);
// 上 右 下 左边距 类似于 css 的 margin
imageData.setTop(0);
imageData.setRight(0);
imageData.setBottom(0);
imageData.setLeft(0);
// relative表示相对于当前的单元格index。first是左上点，last是对角线的右下点，这样确定一个图片的位置和大小。
// 例：0,0,0,0 表示在当前单元格大小，FirstRow：0,FirstColumn：0,LastRow：0,LastColumn：1 表示图片基于当前单元格，横向占据两个单元格，纵向占据两个单元格
imageData.setRelativeFirstRowIndex(0);
imageData.setRelativeFirstColumnIndex(0);
imageData.setRelativeLastRowIndex(1);
imageData.setRelativeLastColumnIndex(1);
return writeCellData;
}
附上读取模板变量名 https://blog.csdn.net/weixin_43686429/article/details/115792722
1409
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
