使用EasyExcel和POI操作Excel实现文件读和写

是只菜鸟呀

已于 2024-04-18 00:14:03 修改

阅读量712

点赞数 10

文章标签： excel

于 2024-04-16 20:48:22 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_63144583/article/details/137838163

版权

使用easyExcel实现文件读写

实现流程

1.导入依赖

2.定义数据模型

3.定义监听器

4.读取或写入数据

5.释放资源

实现

导入依赖

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

定义数据模型

@@ExcelProperty(value = "id" ,index = 0) 列的字段名称和索引

@Data
@AllArgsConstructor
@NoArgsConstructor
public class CategoryExcelVo {

	@ExcelProperty(value = "id" ,index = 0)
	private Long id;

	@ExcelProperty(value = "名称" ,index = 1)
	private String name;

	@ExcelProperty(value = "图片url" ,index = 2)
	private String imageUrl ;

	@ExcelProperty(value = "上级id" ,index = 3)
	private Long parentId;

	@ExcelProperty(value = "状态" ,index = 4)
	private Integer status;

	@ExcelProperty(value = "排序" ,index = 5)
	private Integer orderNum;

}

定义监听器

public class ExcelListener<T> extends AnalysisEventListener<T> {

    private static final int BATCH_COUNT = 100;

    private List cacheList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    private CategoryMapper categoryMapper;

    public ExcelListener(CategoryMapper categoryMapper){
        this.categoryMapper = categoryMapper;
    }
    //读取结束之前，每次调用invoke()方法
    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        CategoryExcelVo categoryExcelVo = (CategoryExcelVo) t;
        cacheList.add(categoryExcelVo);
        if(cacheList.size() >= BATCH_COUNT){
            saveData();
            cacheList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
    }

    private void saveData(){
        categoryMapper.insertData(cacheList);
    }
}

读取

@Override
public void importData(MultipartFile file) {
    try {
        ExcelListener<CategoryExcelVo> listener = new ExcelListener<>(categoryMapper);
        EasyExcel.read(file.getInputStream(),CategoryExcelVo.class,listener).sheet().doRead();
    } catch (IOException e) {
        throw new GuiguException(ResultCodeEnum.DATA_ERROR);
    }
}

写入

@Override
    public void exportData(HttpServletResponse response) {
        try {

            // 设置响应结果类型
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");

            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("分类数据", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            //response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

            // 查询数据库中的数据
            List<Category> categories = categoryMapper.selectAll();
            List<CategoryExcelVo> res = new ArrayList<>();
            categories.forEach(category ->{
                CategoryExcelVo temp = new CategoryExcelVo();
                BeanUtils.copyProperties(category,temp,CategoryExcelVo.class);
                res.add(temp);
            });
            EasyExcel.write(response.getOutputStream(),CategoryExcelVo.class).sheet().doWrite(res);
        } catch (IOException e) {
            throw new GuiguException(ResultCodeEnum.DATA_ERROR);
        }
    }

使用POI实现Excel文件的读写

导入依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.0</version>
</dependency>

读取

 /**
     * 通过POI获取Excel文件中的内容
     * @throws Exception
     */
    public static void read() throws Exception{
        FileInputStream in = new FileInputStream("E:\\info.xlsx");
        XSSFWorkbook excel = new XSSFWorkbook(in);
        XSSFSheet sheet = excel.getSheetAt(0);

        //获取有文字的最后一行行号
        int lastRowNum = sheet.getLastRowNum();

        for(int i = 1;i <= lastRowNum;i++){
            //获得某一行
            XSSFRow row = sheet.getRow(i);
            System.out.println(row.getCell(1).getStringCellValue() + " " +
                    row.getCell(2).getStringCellValue());
        }
        excel.close();
        in.close();
    }

写入

    /**
     * 通过POI创建Excel文件且写入文件内容
     */
    public static void write() throws Exception{
        //在内容中创建一个Excel文件
        XSSFWorkbook excel = new XSSFWorkbook();
        //在Excel文件中创建sheet页
        XSSFSheet sheet = excel.createSheet("info");
        //在Excel文件中创建对象  索引从0开始
        XSSFRow row = sheet.createRow(1);
        //创建单元格并且写入文件内容
        row.createCell(1).setCellValue("姓名");
        row.createCell(2).setCellValue("城市");

        //创建一个新行
        row = sheet.createRow(2);
        //创建单元格并且写入文件内容
        row.createCell(1).setCellValue("张三");
        row.createCell(2).setCellValue("北京");

        row = sheet.createRow(3);
        //创建单元格并且写入文件内容
        row.createCell(1).setCellValue("李四");
        row.createCell(2).setCellValue("南京");

        //通过输出流将内存中的内容写入磁盘
        OutputStream out = new FileOutputStream(new File("E:\\info.xlsx"));
        excel.write(out);
        out.close();
        excel.close();
    }

使用场景（数据导出）

 public void exportBusinessData(HttpServletResponse response) {
        //查数据库，获取营业数据
        LocalDate dateBegin = LocalDate.now().minusDays(30);
        LocalDate dateEnd = LocalDate.now().minusDays(1);

        LocalDateTime begin = LocalDateTime.of(dateBegin, LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(dateEnd, LocalTime.MAX);
        //查询概览数据
        BusinessDataVO businessDataVO = workspaceService.getBusinessData(begin, end);
        //通过POI将数据写入到Excel中
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");
        try {
            //基于模板文件创建一个新的Excel文件
            XSSFWorkbook excel = new XSSFWorkbook(in);
            //填充数据
            //获取表格文件sheet标签页
            XSSFSheet sheet = excel.getSheet("sheet1");
            //填充时间段
            sheet.getRow(1).getCell(1).setCellValue("时间: " + dateBegin + " 至 " + dateEnd);
            //获得第四行
            XSSFRow row = sheet.getRow(3);
            row.getCell(2).setCellValue(businessDataVO.getTurnover());
            row.getCell(4).setCellValue(businessDataVO.getOrderCompletionRate());
            row.getCell(6).setCellValue(businessDataVO.getNewUsers());
            row = sheet.getRow(4);
            row.getCell(2).setCellValue(businessDataVO.getValidOrderCount());
            row.getCell(4).setCellValue(businessDataVO.getUnitPrice());


            for(int i = 0;i < 30;i++){
                LocalDate date = dateBegin.plusDays(i);
                //查询某一天的数据
                BusinessDataVO businessData = workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));
                row = sheet.getRow(7 + i);
                row.getCell(1).setCellValue(date.toString());
                row.getCell(2).setCellValue(businessData.getTurnover());
                row.getCell(3).setCellValue(businessData.getValidOrderCount());
                row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
                row.getCell(5).setCellValue(businessData.getUnitPrice());
                row.getCell(6).setCellValue(businessData.getNewUsers());
            }

            //通过输出流将Excel文件下载到客户端浏览器
            ServletOutputStream out = response.getOutputStream();
            excel.write(out);
            in.close();
            out.close();
            excel.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }