简介

github地址：https://github.com/alibaba/easyexcel

使用文档：https://www.yuque.com/easyexcel/doc/easyexcel

支持的功能

读excel

写excel:

分多个sheet写入，指定单元格样式，格式化显示等

实战

引入依赖

● 2+ 版本支持 Java7和Java6

● 3+ 版本至少 Java8

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

写Excel

实体类

@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

@Data
public class IndexData {
    @ExcelProperty(value = "字符串标题", index = 0)
    private String string;
    @ExcelProperty(value = "日期标题", index = 1)
    private Date date;
    /**
     * 这里设置3 会导致第二列空的
     */
    @ExcelProperty(value = "数字标题", index = 3)
    private Double doubleData;
}

@Data
public class ConverterData {
    /**
     * 我想所有的 字符串起前面加上"自定义："三个字
     */
    @ExcelProperty(value = "字符串标题", converter = CustomStringStringConverter.class)
    private String string;
    /**
     * 我想写到excel 用年月日的格式
     */
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty("日期标题")
    private Date date;
    /**
     * 我想写到excel 用百分比表示
     */
    @NumberFormat("#.##%")
    @ExcelProperty(value = "数字标题")
    private Double doubleData;
}

@ContentRowHeight(10)
@HeadRowHeight(20)
@ColumnWidth(25)
public class WidthAndHeightData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    /**
     * 宽度为50
     */
    @ColumnWidth(50)
    @ExcelProperty("数字标题")
    private Double doubleData;
}

// 头背景设置成红色 IndexedColors.RED.getIndex()
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 10)
// 头字体设置成20
@HeadFontStyle(fontHeightInPoints = 20)
// 内容的背景设置成绿色 IndexedColors.GREEN.getIndex()
@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 17)
// 内容字体设置成20
@ContentFontStyle(fontHeightInPoints = 20)
public class DemoStyleData {
    // 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex()
    @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 14)
    // 字符串的头字体设置成20
    @HeadFontStyle(fontHeightInPoints = 30)
    // 字符串的内容的背景设置成天蓝 IndexedColors.SKY_BLUE.getIndex()
    @ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
    // 字符串的内容字体设置成20
    @ContentFontStyle(fontHeightInPoints = 30)
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
}/**
 * 样式的数据类
 *
 * @author Jiaju Zhuang
 **/
@Data
// 头背景设置成红色 IndexedColors.RED.getIndex()
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 10)
// 头字体设置成20
@HeadFontStyle(fontHeightInPoints = 20)
// 内容的背景设置成绿色 IndexedColors.GREEN.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 17)
// 内容字体设置成20
@ContentFontStyle(fontHeightInPoints = 20)
public class DemoStyleData {
    // 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex()
    @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 14)
    // 字符串的头字体设置成20
    @HeadFontStyle(fontHeightInPoints = 30)
    // 字符串的内容的背景设置成天蓝 IndexedColors.SKY_BLUE.getIndex()
    @ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
    // 字符串的内容字体设置成20
    @ContentFontStyle(fontHeightInPoints = 30)
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
}

示例：

@PostMapping(vale = "/export_year")
    public void exportYear(HttpServletResponse response,
                            @Valid @RequestBody StatementExportAO ao){
List<StatementYearExcel> excelList = new ArrayList<>();
 StatementYearExcel excel = new StatementYearExcel();
 ……填充数据
 excelList.add(excel);
           
String file = "year_" + DateTimeUtils.now2String("yyyyMMddHHmmss") + ".xlsx";
String fileName = new String(file.getBytes(), StandardCharsets.ISO_8859_1);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
   try {
     ServletOutputStream out = response.getOutputStream();
     ExcelWriter excelWriter = EasyExcel.write(out).build();
     WriteSheet sheet = EasyExcel.writerSheet(0, "年结算单")
         .head(StatementYearExcel.class).build();
      excelWriter.write(excelList, sheet);
      excelWriter.finish();
    } catch (Exception e) {
         log.error("exportYear export error.", e);
   }
}

复杂头的编写

只要在实体类上标明关系即可

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class BillMonthExcel  implements Serializable {

  @ExcelProperty(value = "月份", index = 0)
  private String month;
 @ExcelProperty({"渠道收入统计", "渠道账单统计","渠道订单金额(元)"})
  private Double channelOrderAmount;
 @ExcelProperty({"渠道收入统计", "渠道账单统计","渠道手续费(元)"})
  private Double channelFee;
 @ExcelProperty({"渠道收入统计", "渠道账单统计","渠道入账金额(元)"})
  private Double channelInAmount;
 @ExcelProperty({"渠道收入统计", "渠道账单统计","渠道退款金额(元)"})
  private Double channelRefundAmount;
 @ExcelProperty({"渠道收入统计", "渠道账单统计","渠道实收金额(元)"})
  private Double channelRealAmount;
 @ExcelProperty({"渠道收入统计", "渠道提现统计","上月未提现(元)"})
  private Double lastMonthTakeAmount;
 @ExcelProperty({"渠道收入统计", "渠道提现统计","本月已提现(元)"})
  private Double takeAmount;
 @ExcelProperty({"渠道收入统计", "渠道提现统计","本月未提现(元)"})
  private Double restAmount;
 @ExcelProperty({"其他收入统计", "其他业务订单金额(元)"})
  private Double otherAmount;
 @ExcelProperty(value = "收入总计(元)", index = 10)
  private Double overallAmount;
}

read(InputStream inputStream, ReadListener readListener)方法

用于从给定的输入流(InputStream)中读取Excel文件内容，并通过提供的ReadListener接口实例来处理读取过程中的每一行数据。这种方法非常适合处理上传的文件或者内存中的Excel数据，因为它不直接依赖于文件路径。

ReadListener核心方法：
invoke(Object data, AnalysisContext context): 每读取一行数据时调用，data参数是映射后的对象（根据你自定义的模型类），context包含了读取的状态信息。

doAfterAllAnalysed(AnalysisContext context): 所有数据读取完毕后调用，可以在这里执行一些收尾操作。

void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context)：

• 专门用于处理Excel的表头信息
• headMap: Map<Integer, String> 类型，键是列的序号（从0开始），值是对应列的表头文本。例如，键0表示第一列的表头，键1表示第二列的表头，以此类推。
• context: AnalysisContext 类型，提供了有关当前分析上下文的信息，如当前读取的行号、工作表信息等。
• invokeHeadMap 方法只会被调用一次，即在读取到Excel的第一个数据行之前，用于获取表头信息。如果Excel文件没有表头，或者你选择跳过表头，那么这个方法不会被调用。

写同一个文件多个sheet并导出

public void exportTrackKeyword(List<String> industryCodes, HttpServletResponse response)  {
    //如果导出到目录可以用下面注释的内容，接下来是导出并下载
//        String filePath = System.getProperty("user.dir") +"/星光闪闪_"+ System.currentTimeMillis() + ".xlsx";
//        ExcelWriter excelWriter  = EasyExcel.write(filePath, XgssExport.class).build();
        OutputStream outputStream = null;
        //application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName="星光闪闪_"+ System.currentTimeMillis() + ".xlsx";

        try{
            fileName= URLEncoder.encode(fileName, "UTF-8");//如果有中文不转换客户端拿到会识别不出来文件名
            response.setHeader("Content-Disposition", "attachment; filename="+fileName);
            outputStream = response.getOutputStream();
            ExcelWriter writer =EasyExcel.write(outputStream, XgssExport.class).build();
            int sheetNo=0;
            for(String industryCode:  industryCodes){
               //查询数据，这里省略不写了
                List<Xgss> xgssList = selectList(industryCode);
                List<XgssExport> exports=new ArrayList<>();
                sheetNo++;
                String industryName = null;
                for(Xgss xgss: xgssList){
                    String markKws = xgss.getMarkKws();
                     XgssExport export=new XgssExport();
                     exports.add(export);
                    export.setKeyword(markKws);
                }
                WriteSheet sheet = EasyExcel.writerSheet(sheetNo,industryName).build();
//            excelWriter.write(exports, sheet);
                writer.write(exports,sheet);
            }
//        excelWriter.finish();
            writer.finish();
        }catch (Exception e){
            log.error("导出出错",e);
        }finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    log.error("导出关闭流失败",e);
                }
            }
        }
    }

导出提示The maximum length of cell contents (text) is 32767 characters

单元格最大长度校验，超过32767会报错

解决方案：

通过java反射机制，设置单元格最大校验限制为Integer.MAX_VALUE(2147483647)。
导出生成excel文件之前调用下边设置单元格最大限制方法。

	/** 
	  * @Description: 利用反射强制将EXCEL2007中的_maxTextLength属性值修改为Integer.MAX_VALUE
	  * @author: ly
	  * @date: 2024/2/6 
	  **/
	public static void resetCellMaxTextLength() {
		SpreadsheetVersion excel2007 = SpreadsheetVersion.EXCEL2007;
		if (Integer.MAX_VALUE != excel2007.getMaxTextLength()) {
			Field field;
			try {
				field = excel2007.getClass().getDeclaredField("_maxTextLength");
				field.setAccessible(true);
				field.set(excel2007,Integer.MAX_VALUE);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

ExcelUtils.resetCellMaxTextLength();
ResponseUtils.writeExcel(response, workbook, "我的excel.xlsx");