Java中使用easyexcel导入导出数据
工作中常常遇到导入导出Excel数据,还需要设置表格边框、合并单元格、字体居中等等各种样式,尝试了多种方式觉得阿里的easyexcel挺好使,记录一下使用、表格样式如下:
导入依赖
1 2 3 4 5 | <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version> 2.2 . 3 </version> </dependency> |
创建导出实体类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | @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模板
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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(); } |
数据导入读取
1 2 3 4 5 6 7 8 9 | 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工具类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | 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 ; } } } |
数据生成到表格
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | 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()方法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | //配置字体,表头背景等 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); } |
设置内容合并策略,指定列的内容相同,单元格合并
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 | 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); } } } } |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战