easyExcel模版示例地址：
接下来代码展示
1.添加easyExcel依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.3</version></dependency>
2.创建一个类似于实体的Bean
package mon.util.bean;import com.alibaba.excel.annotation.write.style.ColumnWidth;import com.alibaba.excel.annotation.write.style.ContentRowHeight;import com.alibaba.excel.annotation.write.style.HeadRowHeight;import lombok.Data;import java.math.BigDecimal;@Datapublic class ExportReconciliationClearFormBean {//标题public String title;//序号public int number;//日期public String date;//业务系统人数public int number_of_business_system;//业务系统金额public BigDecimal business_system_amount;//财务到账人数public int number_of_people_in_the_account;//财务到账金额public BigDecimal financial_receipt_amount;//人数差异public int poor_number_of_people;//金额差异public BigDecimal difference_in_amount;//业务系统人数合计public int yw_people_count;//业务系统金额合计public BigDecimal yw_amount_count;//财务到账人数合计public int cw_people_count;//财务到账金额合计public BigDecimal cw_amount_count;//人数差异合计public int poor_number_of_people_count;//金额差异合计public BigDecimal difference_in_amount_count;//备注public String remarks;//这里省略了get和set方法 记得添加}}
3.根据你的需求创建摸版
{xxx}是Map填充 xxx对应key value放所对应的值{.xxx}是list填充 xxx对应着list的名称 如果是list<上面创建的bean> 那xxx对应bean的属性
4.后台Controller代码
/*** 导出对账确认表* qyh*/public void exportReconciliationClearForm() throws IOException, ParseException {Map<String,Object> map = new HashMap<>();List<ExportReconciliationClearFormBean> bean = new ArrayList<ExportReconciliationClearFormBean>();String date = getPara("date");SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//开始时间String tartingTime = date.substring(0,date.indexOf(" -"));//结束时间String endTime = date.substring(date.lastIndexOf("- ")+1,date.length());//系统类型 1职称 2计算机英语 3协会String orderType = getPara("orderType");map.put("tartingTime",sdf.parse(tartingTime.replace("/","-")));map.put("endTime",sdf.parse(endTime.replace("/","-")));map.put("orderType",orderType);int randInt = (int) ((Math.random() * 9 + 1) * 1000);String fileName = RdmUtils.replaceSign(endTime)+ randInt + ".zip";getResponse().setContentType("APPLICATION/OCTET-STREAM");//b.Content-Disposition 设置要被下载的文件名getResponse().setHeader("Content-Disposition", "attachment;filename=" + fileName);//获取输出流ServletOutputStream out = getResponse().getOutputStream();//计算机英语系统 报名 用书 证书String computerEnglish = getPara("orderTypeTwo");String[] computerEnglishType = computerEnglish.split(",");List<File> listFile = new ArrayList<>();if(orderType.equals("1")){Map<String,Object> orderMap = orderService.exportReconciliationClearForm(map);bean = (List<ExportReconciliationClearFormBean>) orderMap.get("bean");orderMap.remove("bean");orderMap.put("orderType",orderType);File file = ExportUtils.ExportReconciliationClearForm(orderMap,bean);listFile.add(file);//给文件打成zip包（如果不需要打成压缩包就不需要，直接返回file文件就可）ZipUtils.toZip(listFile,out);}else if(orderType.equals("2")){for (int i = 0;i<computerEnglishType.length;i++){String type = computerEnglishType[i];if(type.equals("1")){map.put("type",type);Map<String,Object> orderMap = orderService.exportReconciliationClearForm(map);bean = (List<ExportReconciliationClearFormBean>) orderMap.get("bean");orderMap.remove("bean");orderMap.put("orderType",orderType);orderMap.put("type",type);File file = ExportUtils.ExportReconciliationClearForm(orderMap,bean);listFile.add(file);}else if(type.equals("2")){map.put("type",type);Map<String,Object> orderMap = orderService.exportReconciliationClearForm(map);bean = (List<ExportReconciliationClearFormBean>) orderMap.get("bean");orderMap.remove("bean");orderMap.put("orderType",orderType);orderMap.put("type",type);File file = ExportUtils.ExportReconciliationClearForm(orderMap,bean);listFile.add(file);}else if(type.equals("3")){map.put("type",type);Map<String,Object> orderMap = orderService.exportReconciliationClearForm(map);bean = (List<ExportReconciliationClearFormBean>) orderMap.get("bean");orderMap.remove("bean");orderMap.put("orderType",orderType);orderMap.put("type",type);File file = ExportUtils.ExportReconciliationClearForm(orderMap,bean);listFile.add(file);}}ZipUtils.toZip(listFile,out);}else if(orderType.equals("3")){Map<String,Object> orderMap = orderService.exportReconciliationClearForm(map);bean = (List<ExportReconciliationClearFormBean>) orderMap.get("bean");orderMap.remove("bean");orderMap.put("orderType",orderType);File file = ExportUtils.ExportReconciliationClearForm(orderMap,bean);listFile.add(file);ZipUtils.toZip(listFile,out);}renderNull();}
5.附上打zip压缩包代码
private static final int BUFFER_SIZE = 2 * 1024;/*** 压缩成ZIP** @param srcFiles* 需要压缩的文件列表* @param out* 压缩文件输出流* @throws RuntimeException* 压缩失败会抛出运行时异常*/public static void toZip(List<File> srcFiles, OutputStream out)throws RuntimeException {long start = System.currentTimeMillis();ZipOutputStream zos = null;try {zos = new ZipOutputStream(out);for (File srcFile : srcFiles) {byte[] buf = new byte[BUFFER_SIZE];zos.putNextEntry(new ZipEntry(srcFile.getName()));int len;FileInputStream in = new FileInputStream(srcFile);while ((len = in.read(buf)) != -1) {zos.write(buf, 0, len);}zos.closeEntry();in.close();}long end = System.currentTimeMillis();System.out.println("压缩完成，耗时：" + (end - start) + " ms");} catch (Exception e) {throw new RuntimeException("zip error from ZipUtils", e);} finally {if (zos != null) {try {zos.close();} catch (IOException e) {e.printStackTrace();}}}}
6.模版填充
ExcelWriter excelWriter = EasyExcel.write(excelFile).registerWriteHandler(new HistoryExportCellWriteHandler()).withTemplate(templateFileName).build(); 如果你看过官方的文档会发现多了一个.registerWriteHandler(new HistoryExportCellWriteHandler())，这是给导出的Excel设置样式，下面有样式的代码
public static File ExportReconciliationClearForm(Map<String, Object> map, List<ExportReconciliationClearFormBean> list){// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替// {} 代表普通变量 {.} 代表是list的变量String templateFileName = PathKit.getWebRootPath() + File.separator + "Template" + File.separator + "exportReconciliationClearForm.xls";String orderType = (String) map.get("orderType");String path = "";String title = "";if(orderType.equals("1")){path = "XX系统收费对账确认表";title = "XX系统收费对账确认表";}else if(orderType.equals("2")){String orderTypeTwo = (String) map.get("type");if(orderTypeTwo.equals("1")){path = "XX系统收费对账确认表（考试报名）";title = "XX系统收费对账确认表（考试报名）";}else if(orderTypeTwo.equals("2")){path = "XX系统收费对账确认表（考试用书）";title = "XX系统收费对账确认表（考试用书）";}else if(orderTypeTwo.equals("3")){path = "XX系统收费对账确认表（考试证书）";title = "XX系统收费对账确认表（考试证书）";}}else if(orderType.equals("3")){path = "XX系统收费对账确认表";title = "XX系统收费对账确认表";}//地址 根据自己的项目地址设置String baseDownPath = AppConfig.baseDownPath;File excelFile = new File(PathKit.getWebRootPath() + File.separator + baseDownPath + File.separator + "Template" + File.separator + path + ".xls");ExcelWriter excelWriter = EasyExcel.write(excelFile).registerWriteHandler(new HistoryExportCellWriteHandler()).withTemplate(templateFileName).build();WriteSheet writeSheet = EasyExcel.writerSheet().build();// 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。// forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用// 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存// 如果数据量大 list不是最后一行 参照下一个FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();//放入的是list列表中的数据excelWriter.fill(list, fillConfig, writeSheet);map.put("title", title);//map中的数据excelWriter.fill(map, writeSheet);excelWriter.finish();return excelFile;}
7.设置样式代码
根据需求更改自己的样式设置列宽，行高等
package mon.util;import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.ss.usermodel.*;import javax.swing.*;import java.util.List;/*** 设置样式* qyh*/public class HistoryExportCellWriteHandler implements CellWriteHandler {@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {Workbook workbook = writeSheetHolder.getSheet().getWorkbook();CellStyle cellStyle = workbook.createCellStyle();Font font = workbook.createFont();if (cell.getRowIndex() == 0) {font.setFontHeightInPoints((short) 18);font.setFontName("宋体");font.setBold(true);cellStyle.setFont(font);cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置 自动换行cellStyle.setWrapText(true);Row row = cell.getRow();row.setHeightInPoints(27);} else if (cell.getRowIndex() == 1) {font.setFontHeightInPoints((short) 11);font.setFontName("宋体");cellStyle.setBorderBottom(BorderStyle.NONE);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);font.setBold(false);cellStyle.setFont(font);cellStyle.setAlignment(HorizontalAlignment.LEFT);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置 自动换行cellStyle.setWrapText(true);Row row = cell.getRow();row.setHeightInPoints(27);} else if (cell.getRowIndex() == 2) {//设置边框cellStyle.setBorderBottom(BorderStyle.NONE);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);//设置字体大小font.setFontHeightInPoints((short) 12);//设置字体样式font.setFontName("宋体");//设置加粗font.setBold(false);//设置字体样式cellStyle.setFont(font);//设置 文字左右居中 【水平居中需要使用以下两行】cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置文字居中 上下居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置 自动换行cellStyle.setWrapText(true);//获取行Row row = cell.getRow();//设计行高row.setHeightInPoints(27);} else {//设置边框cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);//居中cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);Row row = cell.getRow();//设计行高row.setHeightInPoints(27);}cell.setCellStyle(cellStyle);}}
8.我的导出结果
结束语
希望本文可以帮到大家，当然也有很多不足，希望大佬可以多多提意见，一起进步！！！
