工作中常常遇到导入导出Excel数据,还需要设置表格边框、合并单元格、字体居中等等各种样式,尝试了多种方式觉得阿里的easyexcel挺好使,记录一下使用、表格样式如下:
导入依赖
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.3</version> </dependency>
创建导出实体类
@HeadRowHeight(38) //设置标题行高 @Data public class SwhcBdcVo implements Serializable { @ExcelProperty(value = { "基本信息", "序号" }, index = 0) //需要在表格显示的列 @ColumnWidth(6) //每列占用的宽度,列宽 private String xh; @ExcelProperty(value = { "基本信息", "姓名" }, index = 1) @ColumnWidth(8) private String qlrmc; @ExcelProperty(value = { "基本信息", "性别" }, index = 2) @ColumnWidth(6) private String xb; @ExcelProperty(value = { "基本信息", "身份证号" }, index = 3) @ColumnWidth(16) private String qlrzjh; @ExcelProperty(value = {"不动产信息", "房产来源(去向)" }, index = 4) @ColumnWidth(30) private String qx; @ExcelProperty(value = {"不动产信息", "具体地址" }, index = 5) @ColumnWidth(30) private String dz; @ExcelProperty(value = {"不动产信息", "建筑面积(m²)" }, index = 6) @ColumnWidth(11) private BigDecimal jzmj; @ExcelProperty(value = {"不动产信息", "产权性质" }, index = 7) @ColumnWidth(11) private String cqxz; @ExcelProperty(value = {"不动产信息", "登记时间(年月)" }, index = 8) @ColumnWidth(11) private String djsj; @ExcelProperty(value = {"不动产信息", "交易价格(万元)" }, index = 9) @ColumnWidth(11) private BigDecimal jyjg; @ExcelProperty(value = "备注", index = 10) @ColumnWidth(24) private String bz; @ExcelIgnore //不需要在表格展示的字段 private String ywh; @ExcelIgnore private Date compareDjsj; }
生成Excel模板
public void generateTemplate(HttpServletResponse response) { SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddHHmmss"); String fileName = URLEncoder.encode(String.format("%s.xlsx", sdf2.format(new Date())), StandardCharsets.UTF_8.toString()); response.setContentType("application/force-download"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); // 这里 需要指定写用哪个class去写 ExcelWriter writer = new ExcelWriterBuilder() .autoCloseStream(true) .excelType(ExcelTypeEnum.XLSX) .file(response.getOutputStream()) .head(SwhcBdcVo.class) // 核心代码:表头和正文的样式在此 .registerWriteHandler(setConfigure()) .build(); WriteSheet writeSheetfw = new WriteSheet(); writeSheetfw.setSheetName("Sheet1"); writer.write(null, writeSheetfw); // 关闭流 writer.finish(); }
数据导入读取
public R dr(MultipartFile file) { if (!StrUtil.endWithAny(file.getOriginalFilename(), ".xls", ".xlsx")) { return R.failed("上传的文件不是excel"); } List<SwhcBdcVo> drList = EasyExcelUtil.readExcel(file, "Sheet1", SwhcBdcVo.class, 2); //2为表格标题占用的行数,读取跳过标题 if (CollUtil.isEmpty(drList )) { return R.failed("未获取到导入表格数据"); } }
EasyExcelUtil工具类
import com.alibaba.excel.EasyExcel; import com.alibaba.excel.read.builder.ExcelReaderBuilder; import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder; import org.springframework.web.multipart.MultipartFile; import java.io.BufferedInputStream; import java.io.IOException; import java.io.InputStream; import java.util.List; public class EasyExcelUtil { public static <T> List<T> readExcel(MultipartFile excel, String sheetName, Class<T> clazz, int headLineNum) throws IOException { ExcelReaderBuilder readerBuilder = getReader(excel); if (readerBuilder == null) { return null; } ExcelReaderSheetBuilder sheet = readerBuilder.sheet(sheetName); if (sheet == null) { return null; } sheet.head(clazz).headRowNumber(headLineNum); return sheet.doReadSync(); } private static ExcelReaderBuilder getReader(MultipartFile excel) throws IOException { String filename = excel.getOriginalFilename(); if (filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))) { InputStream is = new BufferedInputStream(excel.getInputStream()); return EasyExcel.read(is); } else { return null; } } }
数据生成到表格
log.info("-------开始数据写表操作------------------"); //查询到的数据写入Excel表格,表格文件传入minio ByteArrayOutputStream out = new ByteArrayOutputStream(); //需要合并的列 int[] mergeColumeIndex = {0,1,2,3}; //从第三行后开始合并 int mergeRowIndex = 2; // 调用合并单元格工具类,此工具类是根据工程名称相同则合并后面数据 ExcelFillCellMergeStrategy excelFillCellMergeStrategy = new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex); EasyExcel.write(out, SwhcBdcVo.class) .autoCloseStream(true) .excelType(ExcelTypeEnum.XLSX) .head(SwhcBdcVo.class) // 核心代码:表头和正文的样式在此 .registerWriteHandler(setConfigure()) .registerWriteHandler(excelFillCellMergeStrategy) .sheet("Sheet1") .doWrite(totalPersonBdcList); byte[] bs = out.toByteArray(); out.close(); try (XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(bs)); OutputStream out1 = new FileOutputStream("./测试文件.xlsx")) { workbook.write(out1); log.info("-----------写入文件结束------------"); }
设置标题样式和内容样setConfigure()方法
//配置字体,表头背景等 private static HorizontalCellStyleStrategy setConfigure() { // 头的策略 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); // 背景色 headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex()); // 白色背景 WriteFont headWriteFont = new WriteFont(); // 加粗 // headWriteFont.setBold(true); // headWriteFont.setFontHeightInPoints((short) 14); // 设置行高,不重要 headWriteCellStyle.setWriteFont(headWriteFont); // 内容的策略 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); // 字体策略 WriteFont contentWriteFont = new WriteFont(); // 字体大小 // contentWriteFont.setFontHeightInPoints((short) 14); contentWriteCellStyle.setWriteFont(contentWriteFont); //导出数据垂直居中 contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //导出数据水平居中 contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // 设置边框 contentWriteCellStyle.setBorderLeft(BorderStyle.THIN); contentWriteCellStyle.setBorderTop(BorderStyle.THIN); contentWriteCellStyle.setBorderRight(BorderStyle.THIN); contentWriteCellStyle.setBorderBottom(BorderStyle.THIN); //设置 自动换行 //contentWriteCellStyle.setWrapped(true); //设置 // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现 return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); }
设置内容合并策略,指定列的内容相同,单元格合并
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; @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<CellData> 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; } } } } /** * 当前单元格向上合并 * * @param writeSheetHolder * @param cell 当前单元格 * @param curRowIndex 当前行 * @param curColIndex 当前列 */ 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); } } } }
标签:Java,String,int,easyexcel,excel,private,导入,new,import From: https://www.cnblogs.com/zhuifenglang/p/18082623