文章目录
一、简介二、导入 autopoi-web 包三、自定义导出样式类四、定义实体类五、导出 excel 接口六、测试结果七、前端示例一、简介
实现导出重复列头，自定义 excel 样式，先看最终导出效果，导出含有特殊情况二级列头重复
导入可参考我另一篇文章：https://yixiu./article/details/127564203
autopoi-web 官方文档：http://doc./1623954
二、导入 autopoi-web 包
autopoi-web 为 jeecg-boot 开源的包，用来方便的导入导出 excel
<dependency><groupId>org.jeecgframework</groupId><artifactId>autopoi-web</artifactId><version>1.4.3</version></dependency>
三、自定义导出样式类
自定义 excel 导出样式类 DivCellStyleConfig
getHeaderStyle：设置标题样式getTitleStyle：设置列头样式
public class DivCellStyleConfig extends AbstractExcelExportStyler implements IExcelExportStyler {public DivCellStyleConfig(Workbook workbook) {super.createStyles(workbook);}/*** descroption: 设置标题样式* @author: Ye* @date @time /10/27 16:45* @params: [color]* @return: org.apache.poi.ss.usermodel.CellStyle*/@Overridepublic CellStyle getHeaderStyle(short color) {CellStyle titleStyle = workbook.createCellStyle();Font font = workbook.createFont();font.setFontHeightInPoints((short) 18);font.setBold(true);font.setColor(HSSFColor.HSSFColorPredefined.ORCHID.getIndex());font.setFontName("Courier New");titleStyle.setFont(font);titleStyle.setBorderLeft(BorderStyle.THIN); // 左边框titleStyle.setBorderRight(BorderStyle.THIN); // 右边框titleStyle.setBorderBottom(BorderStyle.THIN);titleStyle.setBorderTop(BorderStyle.THIN);titleStyle.setAlignment(HorizontalAlignment.CENTER);titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 双击单元格的背景色// titleStyle.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.RED.getIndex());// 填充色需要结合 titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND)titleStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());// 填充背景颜色titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);return titleStyle;}/*** descroption: 设置列头样式* @author: Ye* @date @time /10/27 15:44* @params: [color]* @return: org.apache.poi.ss.usermodel.CellStyle*/@Overridepublic CellStyle getTitleStyle(short color) {CellStyle titleStyle = workbook.createCellStyle();Font font = workbook.createFont();font.setFontHeightInPoints((short) 11);font.setBold(true);font.setColor(HSSFColor.HSSFColorPredefined.PLUM.getIndex());font.setFontName("宋体");titleStyle.setFont(font);titleStyle.setBorderLeft(BorderStyle.THIN); // 左边框titleStyle.setBorderRight(BorderStyle.THIN); // 右边框// titleStyle.setBorderBottom(BorderStyle.THIN); // 下边框titleStyle.setBorderTop(BorderStyle.THIN); // 上边框titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中titleStyle.setWrapText(true); // 允许换行titleStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LAVENDER.getIndex());// 填充背景颜色titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);return titleStyle;}/*** descroption: 设置循环行有样式的一行（来实现换行变色效果）* @author: Ye* @date @time /10/27 15:48* @param workbook* @param isWarp* @return*/@Overridepublic CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {CellStyle style = workbook.createCellStyle();style.setBorderLeft(BorderStyle.THIN);style.setBorderRight(BorderStyle.THIN);style.setBorderBottom(BorderStyle.THIN);style.setBorderTop(BorderStyle.THIN);//设置填充前景色 LIGHT_TURQUOISE 浅绿松石style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_TURQUOISE.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);style.setDataFormat(STRING_FORMAT);if (isWarp) {style.setWrapText(true);}return style;}/*** descroption: 这里设置循环行，没有样式的一行* @author: Ye* @date @time /10/27 15:46* @param workbook* @param isWarp* @return*/@Overridepublic CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {CellStyle style = workbook.createCellStyle();//四个边的边框style.setBorderLeft(BorderStyle.THIN);style.setBorderRight(BorderStyle.THIN);style.setBorderBottom(BorderStyle.THIN);style.setBorderTop(BorderStyle.THIN);style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);style.setDataFormat(STRING_FORMAT);if (isWarp) {style.setWrapText(true);}return style;}}
四、定义实体类
最终导出的 excel 会去除语文成绩,数学成绩,英语成绩,语文排名,数学排名,英语排名字段的成绩和排名，但定义的实体类 @Excel 必须区别保证不重复，否则值映射不上
@Data@AllArgsConstructor@NoArgsConstructorpublic class Student implements Serializable {private static final long serialVersionUID = 1L;/* 序号 */@Excel(name = "序号", width = 10)private String id;/* 姓名 */@Excel(name = "姓名", width = 10, orderNum = "1")private String name;/* 性别 */@Excel(name = "性别", width = 10, orderNum = "2")private String sex;/* 民族 */@Excel(name = "民族", width = 10, orderNum = "3")private String nation;/* 出生年月 */@Excel(name = "出生年月", width = 20, format = "yyyy-MM-dd", orderNum = "4")@JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd")@DateTimeFormat(pattern = "yyyy-MM-dd")private Date birthdate;/* 学科成绩 *//* 语文成绩 */@Excel(name = "语文成绩", groupName = "学科成绩", orderNum = "5", width = 15)private String chineseScore;/* 数学成绩 */@Excel(name = "数学成绩", groupName = "学科成绩", orderNum = "6", width = 15)private String mathScore;/* 英语成绩 */@Excel(name = "英语成绩", groupName = "学科成绩", orderNum = "7", width = 15)private String englishScore;/* 学科排名 *//* 语文排名 */@Excel(name = "语文排名", groupName = "学科排名", orderNum = "8", width = 15)private String chineseRank;/* 数学排名 */@Excel(name = "数学排名", groupName = "学科排名", orderNum = "9", width = 15)private String mathRank;/* 英语排名 */@Excel(name = "英语排名", groupName = "学科排名", orderNum = "10", width = 15)private String englishRank;}
五、导出 excel 接口
代码解释
如果直接使用Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Student.class, list);当导出的 excel 行数大于1000 时，会使用 SXSSFWorkbook处理重复列头需要使用 getSheet(“xxx”).getRow(0) 但是会报空指针，必须使用 XSSFWorkbook 才可以处理重复列头如下代码，如果不使用SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook, 100);当 excel 数据量大时，会内存溢出，使用 SXSSFWorkbook 则不会 OOM
XSSFWorkbook workbook = new XSSFWorkbook();new ExcelExportServer().createSheet(workbook, exportParams, Student.class, list, null);//Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Student.class, list);SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook, 100);// 处理二级表头重复，去除语文成绩、语文排名后两个字Sheet sheet = sxssfWorkbook.getXSSFWorkbook().getSheet("学生信息");Row row = sheet.getRow(2);
excel 类型 ExcelType，包含 HSSF（.xls）, XSSF（.xlsx） 两种类型
HSSFWorkbook：支持 .xls 文件读写XSSFWorkbook：支持 .xlsx 文件读写SXSSFWorkbook：只能写不能读，默认内存中保留100行，超过的行将被刷新到磁盘
完整代码：导出 excel 含二级重复列头
@RestController@RequestMapping("/student")@Slf4j@Api(tags = "Student 管理")public class StudentController {/*** Description: student 导出* date: /10/27 14:21** @param response* @author: Ye* @return: void*/@ApiOperation(notes = "student 导出", value = "student 导出")@PostMapping("/export")public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException, ParseException {Student stu1 = new Student("1", "张三", "男", "汉", new SimpleDateFormat("yyyy-MM-dd").parse("2000-10-01"), "88", "99", "100", "3", "2", "1");Student stu2 = new Student("3", "李四", "男", "汉", new SimpleDateFormat("yyyy-MM-dd").parse("2001-10-01"), "88", "99", "100", "3", "2", "1");Student stu3 = new Student("5", "王五", "男", "汉", new SimpleDateFormat("yyyy-MM-dd").parse("-10-01"), "88", "99", "100", "3", "2", "1");List<Student> list = new ArrayList<>(Arrays.asList(stu1,stu2,stu3));ExportParams exportParams = new ExportParams();exportParams.setTitle("学生");exportParams.setSheetName("学生信息");//exportParams.setCreateHeadRows(true);exportParams.setType(ExcelType.XSSF);exportParams.setTitleHeight((short) 20);// 指定自定义 excel 样式类exportParams.setStyle(DivCellStyleConfig.class);//String[] exportFields = {"id","name","nation"}; 可以指定导出字段XSSFWorkbook workbook = new XSSFWorkbook();new ExcelExportServer().createSheet(workbook, exportParams, Student.class, list, null);//Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Student.class, list);SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook, 100);// 处理二级表头重复，去除语文成绩、语文排名后两个字Sheet sheet = sxssfWorkbook.getXSSFWorkbook().getSheet("学生信息");Row row = sheet.getRow(2);List<String> ls = Arrays.asList("语文成绩", "数学成绩", "英语成绩", "语文排名", "数学排名", "英语排名");for (Cell cell : row) {if (null != cell && cell.getCellType().toString().equals("STRING")) {String stringCellValue = cell.getStringCellValue();if (ls.contains(stringCellValue)) {cell.setCellValue(stringCellValue.substring(0, stringCellValue.length() - 2));}}}String fileName = "学生信息.xlsx";response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, String.valueOf(StandardCharsets.UTF_8)));response.setContentType("application/octet-stream;charset=UTF-8");response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");// 返回前端文件名需要添加response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");ServletOutputStream outputStream = response.getOutputStream();workbook.write(outputStream);outputStream.flush();workbook.close();}}
六、测试结果
调用 http://127.0.0.1:8080/student/export 接口 excel 导出成功
如果仅设置 setFillBackgroundColor 颜色
@Overridepublic CellStyle getHeaderStyle(short color) {CellStyle titleStyle = workbook.createCellStyle();Font font = workbook.createFont();font.setFontHeightInPoints((short) 18);font.setBold(true);font.setColor(HSSFColor.HSSFColorPredefined.ORCHID.getIndex());font.setFontName("Courier New");titleStyle.setFont(font);titleStyle.setBorderLeft(BorderStyle.THIN); // 左边框titleStyle.setBorderRight(BorderStyle.THIN); // 右边框titleStyle.setBorderBottom(BorderStyle.THIN);titleStyle.setBorderTop(BorderStyle.THIN);titleStyle.setAlignment(HorizontalAlignment.CENTER);titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 双击单元格的背景色titleStyle.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.RED.getIndex());;return titleStyle;}
效果如下
双击标题可看到 setFillBackgroundColor 设置的颜色
七、前端示例
前端导出 excel 示例
<!DOCTYPE html><html lang="en"><head><meta charset="utf-8"><title>Ajax 请求</title></head><body><p>导出 excel </p><button onclick="download()">导出</button></body></html><script src="/ajax/libs/jquery/3.6.1/jquery.js"></script><script type="text/javascript">function download() {$.ajax({type: "POST",url: "http://127.0.0.1:8080/student/export",// 自定义 headerheaders:{TOKEN:"eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzUxMiJ9.eyJwYXNzd29yZCI6IjEyMzQ1NiIsInVzZXJOYW1lIjoidGVzdDMiLCJleHAiOjE2NjcyOTUwMzJ9.y2JdlHNSYtbzbcVlw6IvO_k4o8UAAkyzzWQKHl6hTfN8MZeTlMvfHR1bCL-xYYgHdr7psj_Lz3HXgLvyv0WnZA"},xhrFields: {responseType: 'blob'},success: function(data,status,xhr){// 获取后端指定的文件名var contentDisposition=decodeURI(xhr.getResponseHeader("Content-Disposition"))var fileName = contentDisposition.substring(contentDisposition.indexOf("fileName=") + 9)console.log(fileName)const url = window.URL.createObjectURL(new Blob([data]));const a = document.createElement('a');a.href = urla.setAttribute("download", fileName)a.click()window.URL.revokeObjectURL(url)},error: function (data,status,xhr) {alert("下载失败")}})}</script>
