easyexcel的使用和beetl工具类的使用

导包

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

直接读

EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(NirvanaWizardCodeDTO.class).sheet().doReadSync();

简单的使用导出

@Data
public class PlanRegionDto {
    /**
     * 排序
     */
    @ExcelProperty("排序")
    @ApiModelProperty(value = "排序")
    private Integer ordering;
    /**
     * 区域id
     */
    @ExcelProperty("区域名称")
    @ApiModelProperty(value = "区域名称")
    private String regionId;

}

@GetMapping("/export")
  public void export(HttpServletResponse response) {
    try {
    //导出的数据
      ArrayList<PlanRegionDto> data = new ArrayList<>();
      response.setContentType("application/vnd.ms-excel");
      response.setCharacterEncoding("utf-8");
      String fileName = URLEncoder.encode("导入模板", "UTF-8");
      response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
      EasyExcel.write(response.getOutputStream(), PlanRegionDto.class).sheet("模板").doWrite(data);
    } catch (Exception e) {
    }
  }

简单的使用导入

@PostMapping("/import")
 public void importSave(@RequestParam("file") MultipartFile file) {
 try {
      EasyExcel.read(
              file.getInputStream(),
              PlanRegionDto.class,
              new PlanRegionDtoListener(dao))
          .sheet()
          .doRead();
    } catch (IOException e) {
      e.printStackTrace();
      log.error("导入失败", e);
      return R.failed(e.getLocalizedMessage());
    }
 }

@Slf4j
public class PlanRegionDtoListener extends AnalysisEventListener<PlanRegionDto> {
	//导入dao
	 private PlanRegionDao dao
  public PlanRegionExcelListener(PlanRegionDao  dao) {
    this.dao= dao;
  }

  /**
   * 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
   *
   * @param exception
   * @param context
   * @throws Exception
   */
  @Override
  public void onException(Exception exception, AnalysisContext context) throws Exception {
    log.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
    // 如果是某一个单元格的转换异常 能获取到具体行号
    if (exception instanceof ExcelDataConvertException) {
      ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
      String error =
          MessageFormat.format(
              "第{0}行，第{1}列解析异常，数据为:{2}",
              excelDataConvertException.getRowIndex(),
              excelDataConvertException.getColumnIndex(),
              StringUtils.isBlank(excelDataConvertException.getCellData().toString())
                  ? ""
                  : excelDataConvertException.getCellData());
      log.error(error);
    
    }
    this.fail++;
  }

  /**
   * 读取每行就会调取
   *
   * @param data
   * @param context
   */
  @Override
  public void invoke(PlanRegionDto data, AnalysisContext context) {
  		//从0开始读取 可读取复杂的excel
   		//System.out.println("行："+context.readRowHolder().getRowIndex());
        //行的每个对应的值
        //System.out.println("Map结构："+context.readRowHolder().getCellMap());
	dao.insert(data)
  }

  /**
   * 读取完毕调
   *
   * @param context
   */
  @Override
  public void doAfterAllAnalysed(AnalysisContext context) {
    log.info("所有数据解析完成！");
   
  }
}

规定格式的导出

response.setHeader("Content-disposition", "attachment; filename=" + "catagory.xls");
	  response.setContentType("application/msexcel;charset=UTF-8");//设置类型
	  response.setHeader("Pragma", "No-cache");//设置头
	  response.setHeader("Cache-Control", "no-cache");//设置头
	  response.setDateHeader("Expires", 0);//设置日期头
	  ServletOutputStream outputStream = response.getOutputStream();
	  //加载对应的模板
	  ExcelWriter excelWriter  = EasyExcel.write(outputStream).withTemplate(ResourceUtils.getFile("classpath:templates/excel.xlsx")).build();
	  //设置sheet
	  WriteSheet writeSheet = EasyExcel.writerSheet().build();
	  ArrayList<Entity> list = new ArrayList<>();
	  list.add(new Entity("123"));
	  list.add(new Entity("564"));
	  //填充配置
	  FillConfig fillConfig  = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
	  excelWriter.fill(list,fillConfig,writeSheet);
	  //填充普通变量
	  Map<String, Object> map = new HashMap<String, Object>();
	  map.put("time", LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd hh:mm:ss")));
	  map.put("name", "张三");
	  excelWriter.fill(map, writeSheet);
	  excelWriter.finish();
	  outputStream.flush();
	  response.getOutputStream().close();

package com.zhk.study.easyexcel;

import lombok.AllArgsConstructor;
import lombok.Data;

@Data
@AllArgsConstructor
public class Entity {
    private String qwe;
}

{.qwe}是list

类型转换

public class GenderConverter implements Converter<String> {
    //在java中性别是用 0 1 来标识的  所以是int
    @Override
    public Class supportJavaTypeKey() {return Integer.class;}
	// 在excel中是男女 所以是string
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}
	//将excel的数据类型转为java数据类型
    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        String stringValue = cellData.getStringValue();
        if (stringValue == null) {
            throw new RuntimeException("性别填写为空");
        }
        if ("男".equals(stringValue)) {
           return 1;
        }
         return 0;
    }
	//将java的数据类型转为excel数据类型
    @Override
    public CellData convertToExcelData(Integer s, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if	(s == 0){
             return new CellData("女");
        }
        return new CellData("男");
    }
}

使用

//实体类属性上
@ExcelProperty(value = "性别", converter = GenderConverter .class, index = 3)
private Integer sex;

写追加数据

    	String fileName = "C:\\Users\\zhk_work\\Desktop\\zhk\\" +  "test.xlsx";
        ExcelWriter excelWriter = null;
        File templateFile = new File(fileName);
        File destFile = new File("C:\\Users\\zhk_work\\Desktop\\zhk\\", "123.xlsx");
        try {
                //追加数据，目标文件与原始文件不能是同一个文件名
                //withTemplate()指定模板文件
                excelWriter = EasyExcel.write().withTemplate(templateFile)
                        //.file() 指定目标文件，不能与模板文件是同一个文件
                        .file(destFile).autoCloseStream(false).build();
            WriteSheet writeSheet = EasyExcel.writerSheet("模板")
                    .build();
            excelWriter.write(data(), writeSheet);
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        if (destFile.exists()) {
            //删除原模板文件，新生成的文件变成新的模板文件
            templateFile.delete();
            destFile.renameTo(templateFile);
        }

读的话

  EasyExcel.read(filePath, YourDataClass.class, new DataListener())
                .sheet()
                .headRowNumber(9) // 从第十行开始读取，索引从0开始
                .doRead();

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

public class ExcelReader {

    public static void main(String[] args) {
        String filePath = "your_excel_file.xlsx"; // 替换为您的Excel文件路径

        EasyExcel.read(filePath, YourDataClass.class, new DataListener())
                .sheet()
                .doRead();
    }

    public static class DataListener extends AnalysisEventListener<YourDataClass> {

        private boolean stopReading = false; // 添加一个标志来控制是否停止读取

        @Override
        public void invoke(YourDataClass data, AnalysisContext context) {
            // 在这里处理每一行数据
            System.out.println(data.toString());

            // 在满足某些条件时停止读取，例如达到特定行数
            if (context.readRowHolder().getRowIndex() >= 20) {
                stopReading = true;
            }
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            // 所有数据读取完毕后的操作
        }

        @Override
        public boolean hasNext(AnalysisContext context) {
            return !stopReading; // 在hasNext方法中控制是否继续读取
        }
    }
}

beetl工具类的使用

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>4.5.10</version>
</dependency>

模板中的含义

${map中的key}

<%
for(student in list){
%>
${student.name}
<% } %>

格式化时间${map的key,dateFormat="yyyy-MM-dd HH:mm:ss"}

java操作

//获取模板并且赋值
 String content = BeetlUtil.render("文件路径.后缀",参数map);
//生成文件
FileWriter writer = new FileWriter("要生成的文件地址.后缀", CharsetUtil.CHARSET_GBK);
        writer.write(content);