在这里插入图片描述

导出Excel的技术分享-综合篇

简单的EasyExcel使用

/*** 最简单的写*/public void simpleWrite() {// 注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入// 写法1 JDK8+// since: 3.0.0-beta1String fileName = "simpleWrite" + System.currentTimeMillis() + ".xlsx";// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭// 如果这里想使用03 则 传入excelType参数即可EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());}private List<DemoData> data() {List<DemoData> list = ListUtils.newArrayList();for (int i = 0; i < 10; i++) {DemoData data = new DemoData();data.setString("字符串" + i);data.setDate(new Date());data.setDoubleData(0.56);list.add(data);}return list;}

行标题设置

   public static void main(String[] args) {String powerTitle = "行单元格样例演示\n" +"行单元格样例演示\n";ExcelWriter excelWriter = EasyExcel.write("行标题设置.xlsx").build();WriteSheet writeSheet = EasyExcel.writerSheet(0,"行标题设置").head(DemoData.class).relativeHeadRowIndex(2).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new IndexStandardStyleHandler(powerTitle)).build();excelWriter.write(data(),writeSheet);excelWriter.finish();}

public class IndexStandardStyleHandler implements SheetWriteHandler {private String tableTitle;public IndexStandardStyleHandler(String title) {this.tableTitle = title;}@Overridepublic void afterSheetCreate(SheetWriteHandlerContext context) {// 在创建工作表之后执行的操作WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder();
//        ExcelWriteHeadProperty excelWriteHeadProperty = writeSheetHolder.getExcelWriteHeadProperty();
//        excelWriteHeadProperty.setHeadRowNumber(3);// 设置行高和样式// 创建第一行并设置样式Row row = writeSheetHolder.getSheet().createRow(0);row.setHeight((short) 1000);Cell cell = row.createCell(0);cell.setCellValue(tableTitle);//设置标题// 准备样式CellStyle cellStyle = context.getWriteWorkbookHolder().getWorkbook().createCellStyle();cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setAlignment(HorizontalAlignment.CENTER);Font font = context.getWriteWorkbookHolder().getWorkbook().createFont();
//        font.setBold(true);font.setFontHeight((short) 500);font.setFontName("宋体");font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());cellStyle.setFont(font);cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setWrapText(true);cell.setCellStyle(cellStyle);//writeWorkbookHolder.createCellStyle(style,null);Sheet sheet = context.getWriteSheetHolder().getSheet();sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 1, 0, 2));}@Overridepublic int order() {return 3;}
}

单元格线设置

 public static void main(String[] args) {String powerTitle = "行单元格样例演示\n" +"行单元格样例演示\n";ExcelWriter excelWriter = EasyExcel.write("单元格线设置.xlsx").build();WriteSheet writeSheet = EasyExcel.writerSheet(0,"单元格线设置").head(DemoData.class).relativeHeadRowIndex(2).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CelllineStyleHandler()).registerWriteHandler(new IndexStandardStyleHandler(powerTitle)).build();excelWriter.write(data(),writeSheet);excelWriter.finish();}

public class CelllineStyleHandler implements CellWriteHandler {@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {for (int i = 0; i < cellDataList.size(); i++) {WriteCellData<?> writeCellData = cellDataList.get(i);WriteCellStyle writeCellStyle = writeCellData.getWriteCellStyle();writeCellStyle.setBorderLeft(THIN);writeCellStyle.setBorderRight(THIN);writeCellStyle.setBorderTop(THIN);writeCellStyle.setBorderBottom(THIN);writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);writeCellStyle.setShrinkToFit(true);}}@Overridepublic int order() {return 1;}
}

下拉框设置

  public static void main(String[] args) {String powerTitle = "行单元格样例演示\n" +"行单元格样例演示\n";ExcelWriter excelWriter = EasyExcel.write("下拉框设置.xlsx").build();WriteSheet writeSheet = EasyExcel.writerSheet(0,"下拉框设置").head(DemoData.class).relativeHeadRowIndex(2).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CelllineStyleHandler()).registerWriteHandler(new CellDropDownBoxWriteHandler()).registerWriteHandler(new IndexStandardStyleHandler(powerTitle)).build();excelWriter.write(data(),writeSheet);excelWriter.finish();}

public class CellDropDownBoxWriteHandler implements SheetWriteHandler {/*** 定义一个map key是需要添加下拉框的列的index value是下拉框数据*/Map<Integer, String[]> mapDropDown = new HashMap<>();String[] checkFrequency = {"字符串0","字符串1","字符串2","字符串3","字符串4"};@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {//下拉选在Excel中对应的列Field[] fields = writeSheetHolder.getClazz().getDeclaredFields();for (int i = 0; i < fields.length; i++) {Field field = fields[i];if (field.isAnnotationPresent(ExcelProperty.class) && field.isAnnotationPresent(ExcelProperty.class)){mapDropDown.put(i,checkFrequency);}}//获取工作簿Sheet sheet = writeSheetHolder.getSheet();///开始设置下拉框DataValidationHelper helper = sheet.getDataValidationHelper();//设置下拉框for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {/*起始行、终止行、起始列、终止列  起始行为1即表示表头不设置**///这里设置65535可能又问题，因为这个是excel的最大行数，如果数据量超过这个数，就会报错CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());/*设置下拉框数据**/DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());DataValidation dataValidation = helper.createValidation(constraint, addressList);//阻止输入非下拉选项的值dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);dataValidation.setShowErrorBox(true);dataValidation.setSuppressDropDownArrow(true);dataValidation.createErrorBox("提示", "输入值与单元格定义格式不一致");dataValidation.createPromptBox("填写说明", "填写内容只能为下拉数据集中的类型");sheet.addValidationData(dataValidation);}}@Overridepublic int order() {return 1;}
}

标题和内容字体样式设置不一样

 public static void main(String[] args) {String powerTitle = "行单元格样例演示\n" +"行单元格样例演示\n";ExcelWriter excelWriter = EasyExcel.write("标题和内容字体样式不一样设置.xlsx").build();WriteSheet writeSheet = EasyExcel.writerSheet(0,"标题和内容字体样式不一样设置").head(DemoData.class).relativeHeadRowIndex(2).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CelllineStyleHandler()).registerWriteHandler(new CellDropDownBoxWriteHandler()).registerWriteHandler(new FontStyleWriteHandler()).registerWriteHandler(new IndexStandardStyleHandler(powerTitle)).build();excelWriter.write(data(),writeSheet);excelWriter.finish();}

public class FontStyleWriteHandler implements CellWriteHandler {/*** 设置字体样式* @param writeSheetHolder* @param writeTableHolder Nullable.It is null without using table writes.* @param cellDataList     Nullable.It is null in the case of add header.There may be several when fill the data.* @param cell* @param head             Nullable.It is null in the case of fill data and without head.* @param relativeRowIndex Nullable.It is null in the case of fill data.* @param isHead           It will always be false when fill data.*/@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {for (int i = 0; i < cellDataList.size(); i++) {WriteCellData<?> writeCellData = cellDataList.get(i);WriteCellStyle writeCellStyle = writeCellData.getWriteCellStyle();if (2 == cell.getRowIndex()) {WriteFont writeFont = new WriteFont();writeFont.setBold(true);writeFont.setFontHeightInPoints((short) 13);writeFont.setFontName("楷体");writeCellStyle.setWriteFont(writeFont);} else {WriteFont writeFont = new WriteFont();writeFont.setFontName("宋体");writeCellStyle.setBorderLeft(THIN);writeCellStyle.setBorderRight(THIN);writeCellStyle.setBorderTop(THIN);writeCellStyle.setBorderBottom(THIN);writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);writeCellStyle.setWriteFont(writeFont);
//                writeCellStyle.setShrinkToFit(true);}}}@Overridepublic int order() {return 1;}
}

单元格合并设置

 public static void main(String[] args) {String powerTitle = "行单元格样例演示\n" +"行单元格样例演示\n";ExcelWriter excelWriter = EasyExcel.write("单元格合并设置.xlsx").build();WriteSheet writeSheet = EasyExcel.writerSheet(0,"单元格合并设置").head(DemoData.class).relativeHeadRowIndex(2).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CelllineStyleHandler()).registerWriteHandler(new CellDropDownBoxWriteHandler()).registerWriteHandler(new FontStyleWriteHandler()).registerWriteHandler(new ExcelFillCellMergeStrategyHandler(2,new int[]{0,1,2})).registerWriteHandler(new IndexStandardStyleHandler(powerTitle)).build();excelWriter.write(data(),writeSheet);excelWriter.finish();}

public class ExcelFillCellMergeStrategyHandler implements CellWriteHandler {/*** 合并字段的下标，如第一到五列new int[]{0,1,2,3,4}*/private int[] mergeColumnIndex;/*** 从第几行开始合并，如果表头占两行，这个数字就是2*/private int mergeRowIndex;public ExcelFillCellMergeStrategyHandler(int mergeRowIndex, int[] mergeColumnIndex) {this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {//当前行int curRowIndex = cell.getRowIndex();//当前列int curColIndex = cell.getColumnIndex();if (curRowIndex > mergeRowIndex) {for (int i = 0; i < mergeColumnIndex.length; i++) {if (curColIndex == mergeColumnIndex[i]) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;}}}for (int i = 0; i < list.size(); i++) {WriteCellData<?> writeCellData = list.get(i);WriteCellStyle writeCellStyle = writeCellData.getWriteCellStyle();writeCellStyle.setBorderLeft(THIN);writeCellStyle.setBorderRight(THIN);writeCellStyle.setBorderTop(THIN);writeCellStyle.setBorderBottom(THIN);writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);writeCellStyle.setShrinkToFit(true);}}private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {//获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() :cell.getNumericCellValue();Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() :preCell.getNumericCellValue();// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行//if (curData.equals(preData)) {Sheet sheet = writeSheetHolder.getSheet();List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();boolean isMerged = false;for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {CellRangeAddress cellRangeAddr = mergeRegions.get(i);// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {sheet.removeMergedRegion(i);cellRangeAddr.setLastRow(curRowIndex);sheet.addMergedRegion(cellRangeAddr);isMerged = true;}}// 若上一个单元格未被合并，则新增合并单元if (!isMerged) {CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex,curColIndex);sheet.addMergedRegion(cellRangeAddress);}}}@Overridepublic int order() {return 1;}
}

自定义多个sheet页导出设置

public class Test07 {public static void main(String[] args) throws NoSuchFieldException, ClassNotFoundException, IllegalAccessException {String powerTitle = "行单元格样例演示\n" +"行单元格样例演示\n";ExcelWriter excelWriter = EasyExcel.write("自定义多个sheet页导出设置.xlsx").build();// 第一个sheet的写入WriteSheet writeSheet01 = EasyExcel.writerSheet(0,"自定义多个sheet页导出设置").head(DemoData.class).relativeHeadRowIndex(2).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CelllineStyleHandler()).registerWriteHandler(new CellDropDownBoxWriteHandler()).registerWriteHandler(new FontStyleWriteHandler()).registerWriteHandler(new ExcelFillCellMergeStrategyHandler(2,new int[]{0,1,2})).registerWriteHandler(new IndexStandardStyleHandler(powerTitle)).build();excelWriter.write(data(),writeSheet01);// 后续sheet写入Map map = listGroupInfo(DemoData.class, dataList());int i = 1;Map<String,String> orgNameMap = new HashMap<>();orgNameMap.put("自定义sheet页1","自定义sheet页1");orgNameMap.put("自定义sheet页2","自定义sheet页2");orgNameMap.put("自定义sheet页3","自定义sheet页3");orgNameMap.put("自定义sheet页4","自定义sheet页4");for (Map.Entry<String, String> entry : orgNameMap.entrySet()) {String OrgCode = entry.getKey();String orgName = entry.getValue();Object list = map.get(OrgCode);// 设置非供电单位写入标题String noPowerTitle = "行单元格样例演示\n" +OrgCode+"行单元格样例演示\n";;WriteSheet sheet = EasyExcel.writerSheet(i, orgName).head(DemoData.class).relativeHeadRowIndex(2).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();excelWriter.write((Collection<?>) list,sheet);i = i + 1;}excelWriter.finish();}private static Map listGroupInfo(Class<?> head1, List<?> dataList1) throws ClassNotFoundException, NoSuchFieldException, IllegalAccessException {String groupField = "string";String name = head1.getName();Class<?> clazz = Class.forName(name);Field field = clazz.getDeclaredField(groupField);field.setAccessible(true);Map<Object, ? extends List<?>> map = dataList1.stream().collect(Collectors.groupingBy(obj -> {try {return field.get(obj);} catch (IllegalAccessException e) {e.printStackTrace();return null;}}));Set<Object> objects = map.keySet();Iterator<Object> iterator = objects.iterator();
//        while (iterator.hasNext()) {
//            List<?> list = map.get(iterator.next());
//            for (int i = 0; i < list.size(); i++) {
//                Object o = list.get(i);
//                Field fieldSerialNumber = clazz.getDeclaredField("serialNumber");
//                fieldSerialNumber.setAccessible(true);
//                fieldSerialNumber.set(o,String.valueOf(i+1));
//            }
//        }return map;}private static List<DemoData> data() {List<DemoData> list = ListUtils.newArrayList();for (int i = 0; i < 10; i++) {DemoData data = new DemoData();data.setString("字符串" + i);data.setDate(new Date());data.setDoubleData(0.56);list.add(data);}return list;}private static List<DemoData> dataList() {List<DemoData> list = ListUtils.newArrayList();for (int i = 0; i < 10; i++) {DemoData data1 = new DemoData();data1.setString("自定义sheet页1");data1.setDate(new Date());data1.setDoubleData(111.111);list.add(data1);}for (int i = 0; i < 10; i++) {DemoData data2 = new DemoData();data2.setString("自定义sheet页2");data2.setDate(new Date());data2.setDoubleData(222.222);list.add(data2);}for (int i = 0; i < 10; i++) {DemoData data3 = new DemoData();data3.setString("自定义sheet页3");data3.setDate(new Date());data3.setDoubleData(333.333);list.add(data3);}for (int i = 0; i < 10; i++) {DemoData data3 = new DemoData();data3.setString("自定义sheet页4");data3.setDate(new Date());data3.setDoubleData(444.444);list.add(data3);}return list;}}

public class ExcelFillCellMergeStrategyHandler implements CellWriteHandler {/*** 合并字段的下标，如第一到五列new int[]{0,1,2,3,4}*/private int[] mergeColumnIndex;/*** 从第几行开始合并，如果表头占两行，这个数字就是2*/private int mergeRowIndex;public ExcelFillCellMergeStrategyHandler(int mergeRowIndex, int[] mergeColumnIndex) {this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {//当前行int curRowIndex = cell.getRowIndex();//当前列int curColIndex = cell.getColumnIndex();if (curRowIndex > mergeRowIndex) {for (int i = 0; i < mergeColumnIndex.length; i++) {if (curColIndex == mergeColumnIndex[i]) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;}}}for (int i = 0; i < list.size(); i++) {WriteCellData<?> writeCellData = list.get(i);WriteCellStyle writeCellStyle = writeCellData.getWriteCellStyle();writeCellStyle.setBorderLeft(THIN);writeCellStyle.setBorderRight(THIN);writeCellStyle.setBorderTop(THIN);writeCellStyle.setBorderBottom(THIN);writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);writeCellStyle.setShrinkToFit(true);}}private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {//获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() :cell.getNumericCellValue();Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() :preCell.getNumericCellValue();// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行//if (curData.equals(preData)) {Sheet sheet = writeSheetHolder.getSheet();List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();boolean isMerged = false;for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {CellRangeAddress cellRangeAddr = mergeRegions.get(i);// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {sheet.removeMergedRegion(i);cellRangeAddr.setLastRow(curRowIndex);sheet.addMergedRegion(cellRangeAddr);isMerged = true;}}// 若上一个单元格未被合并，则新增合并单元if (!isMerged) {CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex,curColIndex);sheet.addMergedRegion(cellRangeAddress);}}}@Overridepublic int order() {return 1;}
}

单元格锁设置

 public static void main(String[] args) {String powerTitle = "行单元格样例演示\n" +"行单元格样例演示\n";ExcelWriter excelWriter = EasyExcel.write("单元格锁设置.xlsx").build();WriteSheet writeSheet = EasyExcel.writerSheet(0,"单元格锁设置").head(DemoDataLock.class).relativeHeadRowIndex(2).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CelllineStyleHandler()).registerWriteHandler(new LockHandler()).registerWriteHandler(new IndexStandardStyleHandler(powerTitle)).build();excelWriter.write(data(),writeSheet);excelWriter.finish();}

public class LockHandler extends LongestMatchColumnWidthStyleStrategy implements SheetWriteHandler {@Overridepublic void afterCellDispose(CellWriteHandlerContext context) {// 锁定有内容过的单元格(方法1)Class clazz = context.getWriteSheetHolder().getClazz();Field[] fields = clazz.getDeclaredFields();Field field = fields[context.getColumnIndex()];if (field.isAnnotationPresent(UnLockCell.class) && field.isAnnotationPresent(ExcelProperty.class)) {WriteCellStyle writeCellStyle = context.getFirstCellData().getOrCreateStyle();writeCellStyle.setLocked(false);}}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 设置保护密码writeSheetHolder.getSheet().protectSheet("ZhiChengKeJi");// 锁定单元格不可选中(防止别人直接复制内容到其他excel修改)((SXSSFSheet) writeSheetHolder.getSheet()).lockSelectLockedCells(false);
//        ((SXSSFSheet) writeSheetHolder.getSheet()).lockSelectLockedCells(true);}
}

设置大标题行样式

在这里插入图片描述

@PostMapping("/exportExcel")public void exportExcel(HttpServletResponse response) throws IOException {// 数据库获取数据List<Profile> list = iProfileService.exportExcel(response);List<ProfileExcel> profileExcels = ObjectConvertUtil.convertList(list, ProfileExcel.class);
//        List<ProfileExcel> profileExcels = ExcelConvert.INSTANCE.converList(list);// 输出ExcelUtils.write(response, "大佬信息", "岗位列表", ProfileExcel.class, profileExcels);}

展示工具类中的代码

 /*** 将列表以Excel的形式响应给前端** @param response 响应* @param fileName 文件名* @param sheetName Excel sheet 名* @param head Excel head 头* @param data 数据列表* @param <T> 泛型，保证 head 和 data 类型的一致性* @throws IOException 写入失败的情况*/public static <T> void write(HttpServletResponse response, String fileName, String sheetName, Class<T> head, List<T> data) throws IOException {// 这一部分是设置编码样式response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");// 输出ExcelEasyExcel.write(response.getOutputStream(),head).relativeHeadRowIndex(2)// 不要自动关闭，交给 Servlet 自己处理//.autoCloseStream(false).registerWriteHandler(new ExportIndexTypeSheetWriteHandler()).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new ExcelFillCellMergeStrategy(3,new int[]{0,1,2,3,4,5,6})).registerWriteHandler(new FontStyleWriteHandler()).sheet(sheetName).doWrite(data);}

核心注册类代码展示

实现SheetWriteHandler策略接口，在sheet创建之前完成对样式进行提前的设置

public class ExportIndexTypeSheetWriteHandler implements SheetWriteHandler {@Overridepublic void afterSheetCreate(SheetWriteHandlerContext context) {// 在创建工作表之后执行的操作WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder();
//        ExcelWriteHeadProperty excelWriteHeadProperty = writeSheetHolder.getExcelWriteHeadProperty();
//        excelWriteHeadProperty.setHeadRowNumber(3);// 设置行高和样式// 创建第一行并设置样式Row row = writeSheetHolder.getSheet().createRow(0);// 设置第一行的高为row.setHeight((short) 1000);// 提前创建第一行单元格并进行设置Cell cell = row.createCell(0);// 设置需要展示的文字信息String title = "大佬们的信息展示\n" +"这个是演示的数据标题\r\n";// 单元格填充内容cell.setCellValue(title);//设置标题// 准备样式// 从上下文内容中获取工作簿并创建样式CellStyle cellStyle = context.getWriteWorkbookHolder().getWorkbook().createCellStyle();// 这个接口在ss。POI的核心模块中存在// 设置单元格的垂直对齐类型 ： 垂直对齐以单元格高度为中心。cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 设置单元格的水平对齐样式：水平对齐居中，这意味着文本在单元格中居中。cellStyle.setAlignment(HorizontalAlignment.CENTER);// 从上下文获取字体Font font = context.getWriteWorkbookHolder().getWorkbook().createFont();// 设置字体加粗font.setBold(true);// 设置字体行高font.setFontHeight((short) 500);// 设置字体的样式名称font.setFontName("宋体");// 设置字体的颜色font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());// 需要在单元格样式中填充字体cellStyle.setFont(font);// 单元格设置为水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);// 设置单元格为允许换行，不然上述title中的单元格换行无效cellStyle.setWrapText(true);// 最后需要将单元格的样式填充到单元格中cell.setCellStyle(cellStyle);//writeWorkbookHolder.createCellStyle(style,null);// 有点重复操作，从上下文中获取默认第一个sheet页的样式Sheet sheet = context.getWriteSheetHolder().getSheet();// 设置单元格的合并区域，给坐标的方式。哪行开始哪行结束。哪列开始那列结束sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 1, 0, 5));}
}

标题行的样式填充颜色改变

在这里插入图片描述

颜色9是透明色。

相同单元格的样式合并

在这里插入图片描述

这种编码方式需要在代码使用的时候将那几列放到方法中。

public class ExcelFillCellMergeStrategy implements CellWriteHandler {/*** 合并字段的下标，如第一到五列new int[]{0,1,2,3,4}*/private int[] mergeColumnIndex;/*** 从第几行开始合并，如果表头占两行，这个数字就是2*/private int mergeRowIndex;public ExcelFillCellMergeStrategy() {}public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,Head head, Integer integer, Integer integer1, Boolean aBoolean) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {//当前行int curRowIndex = cell.getRowIndex();//当前列int curColIndex = cell.getColumnIndex();if (curRowIndex > mergeRowIndex) {for (int i = 0; i < mergeColumnIndex.length; i++) {if (curColIndex == mergeColumnIndex[i]) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;}}}}private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {//获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() :cell.getNumericCellValue();Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :preCell.getNumericCellValue();// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行//if (curData.equals(preData)) {Sheet sheet = writeSheetHolder.getSheet();List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();boolean isMerged = false;for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {CellRangeAddress cellRangeAddr = mergeRegions.get(i);// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {sheet.removeMergedRegion(i);cellRangeAddr.setLastRow(curRowIndex);sheet.addMergedRegion(cellRangeAddr);isMerged = true;}}// 若上一个单元格未被合并，则新增合并单元if (!isMerged) {CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex,curColIndex);sheet.addMergedRegion(cellRangeAddress);}}}}

单元格的横线设置

在这里插入图片描述

public class FontStyleWriteHandler implements CellWriteHandler {/*** 设置字体样式* @param writeSheetHolder* @param writeTableHolder Nullable.It is null without using table writes.* @param cellDataList     Nullable.It is null in the case of add header.There may be several when fill the data.* @param cell* @param head             Nullable.It is null in the case of fill data and without head.* @param relativeRowIndex Nullable.It is null in the case of fill data.* @param isHead           It will always be false when fill data.*/@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {// 创建字体样式// 设置内容字体样式WriteFont writeFont = new WriteFont();writeFont.setFontName("宋体");for (int i = 0; i < cellDataList.size(); i++) {WriteCellData<?> writeCellData = cellDataList.get(i);WriteCellStyle writeCellStyle = writeCellData.getWriteCellStyle();// 横线的设置writeCellStyle.setBorderLeft(THIN);writeCellStyle.setBorderRight(THIN);writeCellStyle.setBorderTop(THIN);writeCellStyle.setBorderBottom(THIN);writeCellStyle.setWriteFont(writeFont);writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);writeCellStyle.setShrinkToFit(true);}}}

导出字体的样式自定义

在这里插入图片描述

public class FontStyleWriteHandler implements CellWriteHandler {/*** 设置字体样式* @param writeSheetHolder* @param writeTableHolder Nullable.It is null without using table writes.* @param cellDataList     Nullable.It is null in the case of add header.There may be several when fill the data.* @param cell* @param head             Nullable.It is null in the case of fill data and without head.* @param relativeRowIndex Nullable.It is null in the case of fill data.* @param isHead           It will always be false when fill data.*/@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {// 创建字体样式// 设置内容字体样式WriteFont writeFont = new WriteFont();writeFont.setFontName("宋体");// 核心设置字体样式writeFont.setColor(IndexedColors.RED.getIndex());for (int i = 0; i < cellDataList.size(); i++) {WriteCellData<?> writeCellData = cellDataList.get(i);WriteCellStyle writeCellStyle = writeCellData.getWriteCellStyle();writeCellStyle.setBorderLeft(THIN);writeCellStyle.setBorderRight(THIN);writeCellStyle.setBorderTop(THIN);writeCellStyle.setBorderBottom(THIN);writeCellStyle.setWriteFont(writeFont);writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);writeCellStyle.setShrinkToFit(true);}}}