常用的EasyExcel表格处理 |
您所在的位置：网站首页 › 如果添加批注 › 常用的EasyExcel表格处理 |
常用的EasyExcel表格处理
|
EasyExcel官网：点击查看 1、模板表头设置批注此处主要根据自定义处理类CustomCellWriteHandler进行处理。 1.1 前端调用controller @PostMapping("/download/template") public void toDoExport(HttpServletResponse response) { // 设置模拟表头（此处为多表头，也可以传入单表头） List headList = new ArrayList(); List heads1 = new ArrayList(); heads1.add("总表头"); heads1.add("表头1"); List heads2 = new ArrayList(); heads2.add("总表头"); heads2.add("表头2"); List heads3 = new ArrayList(); heads3.add("总表头"); heads3.add("特殊表头"); headList.add(heads1); headList.add(heads2); headList.add(heads3); // 设置模拟内容（不需要data可为null） List data = new ArrayList(); List data1 = new ArrayList(); // 第1行的内容 data1.add("内容1-1"); data1.add("内容1-2"); data1.add("内容1-3"); List data2 = new ArrayList();// 第2行的内容 data2.add("内容2-1"); data2.add("内容2-2"); data2.add("内容2-3"); data.add(data1); data.add(data2); try { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 防止中文乱码 String fileName = URLEncoder.encode("导入模板名称", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); ServletOutputStream outputStream = response.getOutputStream(); ExcelWriter writer = EasyExcel.write(outputStream).build(); WriteSheet sheet = EasyExcel.writerSheet(0).needHead(Boolean.FALSE).sheetName("sheet页名称").build(); WriteTable table1 = EasyExcel.writerTable(1).head(headList) .registerWriteHandler(new AutoHeadColumnWidthStyleStrategy()) // 自动宽度 .registerWriteHandler(new CustomCellWriteHandler())//CustomCellWriteHandler进行处理 .needHead(Boolean.TRUE).build(); writer.write(data, sheet, table1); // data 如果不需要可以为null writer.finish(); } catch (IOException e) { e.printStackTrace(); } }1.2 批注渲染处理类（CustomCellWriteHandler） package com.xjj.task.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.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import java.util.List; public class CustomCellWriteHandler implements CellWriteHandler { @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List list, Cell cell, Head head, Integer integer, Boolean aBoolean) { Workbook workbook = writeSheetHolder.getSheet().getWorkbook(); CellStyle cellStyle = workbook.createCellStyle(); // 居中/换行 cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setWrapText(true); cellStyle.setFillBackgroundColor(IndexedColors.PALE_BLUE.getIndex()); Font font = workbook.createFont(); if (aBoolean) { // 判断是否为表头，true则是 // 首行字体略大 Integer row = cell.getRowIndex();// 行号（从0开始） if (row == 0) { font.setFontHeightInPoints((short) 15); // 首行设置字体大小为15 } else { font.setFontHeightInPoints((short) 13); String stringCellValue = cell.getStringCellValue(); int columnIndex = cell.getColumnIndex(); String txt = "此为批注内容"; if (columnIndex == 0 || stringCellValue.contains("特殊")) { // 设置第1列或包含“特殊”内容的列表头加批示 Sheet sheet = writeSheetHolder.getSheet(); Drawing draw = sheet.createDrawingPatriarch(); Comment comment = draw.createCellComment(new XSSFClientAnchor(1, 1, 1, 1, (short) columnIndex, 1, 1, 1)); // 批注内容 comment.setString(new XSSFRichTextString("表头" + (columnIndex + 1) + "的批注：" + txt)); cell.setCellComment(comment); } } // 字体样式 font.setFontName("宋体"); font.setBold(true); } else { font.setFontHeightInPoints((short) 11); // 边框 cellStyle.setBorderLeft(BorderStyle.NONE);//细实线 cellStyle.setBorderTop(BorderStyle.NONE); cellStyle.setBorderRight(BorderStyle.NONE); cellStyle.setBorderBottom(BorderStyle.NONE); } cellStyle.setFont(font); cell.setCellStyle(cellStyle); } }1.3 效果 此处主要根据自定义处理类SpinnerWriteHandler进行处理。 2.1 前端调用controller /** * 下载模板 */ @PostMapping("/downLoad/template") public void export(HttpServletResponse response) { try { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder.encode("导入模板", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); //1-TrainWay 为具体的业务类，可根据实际业务替换 //2-SpinnerWriteHandler为自定义的下拉选处理类 EasyExcel.write(response.getOutputStream(), TrainWay.class).registerWriteHandler(new SpinnerWriteHandler()).sheet("模板名称").doWrite(new ArrayList()); } catch (IOException e) { e.printStackTrace(); } }2.2 下拉选处理类（SpinnerWriteHandler） package jnpf.util; import cn.hutool.core.collection.CollUtil; import com.alibaba.excel.write.handler.SheetWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.usermodel.DataValidationConstraint; import org.apache.poi.ss.usermodel.DataValidationHelper; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.xssf.usermodel.XSSFDataValidation; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @author Han * @Description * @date 2024/2/8 */ public class SpinnerWriteHandler implements SheetWriteHandler { @Override public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { } @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { List wayList = new ArrayList(); // 自定义的下拉选选项 wayList.add("方式1"); wayList.add("方式2"); List orgList = new ArrayList(); // 自定义的下拉选选项 orgList.add("单位1"); orgList.add("单位2"); Map mapDropDown = new HashMap(); // 需要渲染的下拉选对象 if(CollUtil.isNotEmpty(wayList)){ String[] wayArray = wayList.toArray(new String[wayList.size()]); // 数组类型 mapDropDown.put(1, wayArray); // 此处1为对应选择的列数（从0列开始计算），wayArray为可以下拉选择的列 } if(CollUtil.isNotEmpty(orgList)){ String[] orgArray = wayList.toArray(new String[orgList.size()]); // 数组类型 mapDropDown.put(5, orgArray);// 此处5为对应选择的列数（从0列开始计算），orgArray为可以下拉选择的列 } Sheet sheet = writeSheetHolder.getSheet(); /// 开始设置下拉框 DataValidationHelper helper = sheet.getDataValidationHelper(); for (Map.Entry entry : mapDropDown.entrySet()) { //设置起始行为1（从0行开始）、终止行为1000 CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, entry.getKey(), entry.getKey()); //设置下拉框数据 DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue()); DataValidation dataValidation = helper.createValidation(constraint, addressList); //处理Excel兼容性问题 if (dataValidation instanceof XSSFDataValidation) { dataValidation.setSuppressDropDownArrow(true); dataValidation.setShowErrorBox(true); } else { dataValidation.setSuppressDropDownArrow(false); } sheet.addValidationData(dataValidation); } } }2.3 效果 |
【本文地址】
今日新闻 |
推荐新闻 |