Java通过EasyExcel生成excel文件(多个sheet)、列合并 |
您所在的位置：网站首页 › Java将多个excel合并成一个 › Java通过EasyExcel生成excel文件(多个sheet)、列合并 |
Java通过EasyExcel生成excel文件(多个sheet)、列合并
|
目录 1、添加依赖 2、实体类 2.1 操作日志 2.2 用户信息 3、测试的excel数据文件 3.1 用户信息excel 3.2 操作日志excel 4、excel列合并工具类 5、调用方 1、添加依赖 com.alibaba fastjson 1.2.3 2、实体类 2.1 操作日志 package com.shucha.deveiface.biz.test; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.format.DateTimeFormat; import com.alibaba.excel.annotation.write.style.ColumnWidth; import lombok.Data; /** * @author tqf * @Description * @Version 1.0 * @since 2022-01-18 15:03 */ @Data public class Log { /** * 日志名称 */ @ColumnWidth(20) @ExcelProperty(value = "日志名称", index = 0) // @ExcelProperty(value = {"资源基本信息", "序号"},index = 0) private String logName; /** * 日志时间 */ @ColumnWidth(20) @ExcelProperty(value = "日志时间", index = 1) @DateTimeFormat("yyyy-MM-dd HH:mm:ss") private String date; /** * 字段名 */ @ColumnWidth(20) @ExcelProperty(value = "字段名", index = 2) private String fieldName; /** * 字段描述 */ @ColumnWidth(20) @ExcelProperty(value = "字段描述", index = 3) private String fieldDesc; /** * 字段值 */ @ColumnWidth(20) @ExcelProperty(value = "字段值", index = 4) private String fieldValue; } 2.2 用户信息 package com.shucha.deveiface.biz.test; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.HeadStyle; import lombok.Data; import org.apache.poi.ss.usermodel.FillPatternType; /** * @author tqf * @Description * @Version 1.0 * @since 2022-01-18 14:09 */ @Data public class User { /** * 姓名 */ // 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex() 设置标题的背景颜色 @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 14) @ExcelProperty(value = "姓名", index = 0) private String name; /** * 年龄 */ // 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex() 设置标题的背景颜色 @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 13) @ExcelProperty(value = "年龄", index = 1) private String age; } 3、测试的excel数据文件 3.1 用户信息excel 姓名年龄王海35谭杰12王娟25 3.2 操作日志excel 日志名称日志时间字段名字段描述字段值根据ID查询详情2021-01-13size条数25根据ID查询详情2021-01-13page页码21根据ID查询详情2021-01-13total总数25获取日志列表2021-01-12logName日志名称查询获取日志列表2021-01-12logTime日志时间2021-10-01 14:25:14获取日志列表2021-01-12logUser查询人姓名王杰查询用户信息2021-01-11userName用户名tqf_123查询用户信息2021-01-11age年龄25查询用户信息2021-01-11sex性别(0-男，1-女，2-未知)0 4、excel列合并工具类 package com.shucha.deveiface.biz.test; 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 lombok.Data; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import java.util.List; /** * 单元格合并 * * @author * @date */ @Data public class ExcelFillCellMergeStrategy implements CellWriteHandler { /** * 合并字段的下标 */ private int[] mergeColumnIndex; /** * 合并几行 */ private int mergeRowIndex; public ExcelFillCellMergeStrategy() { } public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex; } @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) { //当前行 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 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); } } } } 5、调用方 package com.shucha.deveiface.biz.test; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; import lombok.extern.slf4j.Slf4j; import javax.servlet.ServletOutputStream; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.List; /** * @author tqf * @Description 生成excel文件，多个sheet写入数据 * @Version 1.0 * @since 2022-07-11 10:16 */ @Slf4j public class MoreExcelSheet { // 生成的文件路径和名称 private static File file = new File("D:/Excel多个sheet.xls"); public static void main(String[] args) throws IOException { // 日志列表数据 List dtoList = getData(); // 用户信息列表数据 List userList = getUserData(); String sheetName[] = {"操作日志","用户信息"}; // sheet的对象class List classArray = new ArrayList(); classArray.add(Log.class); classArray.add(User.class); //需要合并的列 int[] mergeColumeIndex = {0,1}; // 从第二行后开始合并 int mergeRowIndex = 1; //不存在则新增 if(!file.getParentFile().exists()){ file.getParentFile().mkdirs(); } if(!file.exists()){ file.createNewFile(); } FileOutputStream output = new FileOutputStream(file); // ServletOutputStream output = response.getOutputStream(); ExcelWriter excelWriter = EasyExcel.write(output).build(); for (int i=0;i size ? size : len * (i + 1))); WriteSheet writeSheet; writeSheet = EasyExcel.writerSheet(i, "sheet"+(i+1)+"") .head(User.class) .build(); excelWriter.write(dataList, writeSheet); } //千万别忘记finish 会帮忙关闭流 excelWriter.finish(); output.flush(); } }生成文件截图如下：
|
【本文地址】
公司简介
联系我们
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |