EasyExcel 读写 excel easyexcel web base64

官网： https://github.com/alibaba/easyexcel

 直接看官网吧，下面做备份．

参考：https://blog.csdn.net/jiangjiandecsd/article/details/81115622

https://blog.csdn.net/jianggujin/article/details/80200400 

推荐:

https://www.jianshu.com/p/3a64ade57bf2

EasyExcel教程:

https://blog.csdn.net/sinat_32366329/article/details/103109058?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase

 

摘要：　

１．easyexcel  读取excel

 2.  easyexcel   写入excel 

 3.  写入excel 列宽自适应  :  https://www.cnblogs.com/lshan/p/15075081.html

4. web 下载excel (直接下载)

５.web 下载excel base64

 

EasyExcel 读取excel: (noModel)

    /* 解析监听器，
     * 每解析一行会回调invoke()方法。
     * 整个excel解析结束会执行doAfterAllAnalysed()方法
     * 下面只是我写的一个样例而已，可以根据自己的逻辑修改该类。
     */
    public class ExcelListener extends AnalysisEventListener {

        //自定义用于暂时存储data。
        //可以通过实例获取该值
        private List<Object> datas = new ArrayList<Object>();
        public void invoke(Object object, AnalysisContext context)
        {
            System.err.println(JSONValue.toJSONString(context));
            System.out.println("当前行："+context.getCurrentRowNum());
            System.out.println(object);
            datas.add(object);//数据存储到list，供批量处理，或后续自己业务逻辑处理。
            doSomething(object);//根据自己业务做处理
        }
        private void doSomething(Object object) {
            //1、入库调用接口
        }
        public void doAfterAllAnalysed(AnalysisContext context) {
            // datas.clear();//解析结束销毁不用的资源
        }
        public List<Object> getDatas() {
            return datas;
        }
        public void setDatas(List<Object> datas) {
            this.datas = datas;
        }
    }


    /**
     * test read excel with easyexcel
     * @throws FileNotFoundException
     */
    @Test
    public void testReadExcelNoModel() throws FileNotFoundException {
//        InputStream inputStream = getInputStream("loan1.xls");
        FileInputStream inputStream = new FileInputStream("C:\\Users\\lenove\\Desktop\\seatest.xlsx");
        ExcelReader excelReader =null;
        try {
            // 解析每行结果在listener中处理
            ExcelListener listener = new ExcelListener();
/**   #######      方式一  return  List<List>     ######
//           # EasyExcelFactory.readBySax(inputStream, new Sheet(1,0),listener);
//            ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX,null, listener);
//            excelReader.read(new Sheet(1,0));
//            List<Object> datas = listener.getDatas();
//            System.err.println(datas);
 **/

//########    方式2        return   List<Map>#######
            excelReader = EasyExcel.read(inputStream,listener).headRowNumber(0).build();
            ReadSheet readSheet = EasyExcel.readSheet(0).build();
            excelReader.read(readSheet);
            System.err.println(JSONValue.toJSONString(listener.getDatas()));
        } catch (Exception e)
        {
            System.err.println("exception is "+e);
        } finally
        {
            try
            {
                   if (excelReader != null) {
                    // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
                    excelReader.finish();}
                    inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

 

 

with model:

 

IndexOrNameData:

@Data
public class IndexOrNameData {
    /**
     * 强制读取第三个 这里不建议 index 和 name 同时用，要么一个对象只用index，要么一个对象只用name去匹配
     */
    @ExcelProperty(index = 2)
    private Double doubleData;
    /**
     * 用名字去匹配，这里需要注意，如果名字重复，会导致只有一个字段读取到数据
     */
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
}

 

            MilestoneUploadExcelListener excelListener = new MilestoneUploadExcelListener();  
            File exfile = transferToFile(excelFile);
            EasyExcel.read(exfile, Milestone.class, excelListener).headRowNumber(1).sheet().doRead();　//指定ｈeader line
            List<Milestone> milestonelist = excelListener.getList();

 

 

 

    @Test
    public void extraRead() {
        String fileName = TestFileUtil.getPath() + "demo" + File.separator + "extra.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet
        EasyExcel.read(fileName,IndexOrName.class, new DemoExtraListener())
            // 需要读取批注 默认不读取
            .extraRead(CellExtraTypeEnum.COMMENT)
            // 需要读取超链接 默认不读取
            .extraRead(CellExtraTypeEnum.HYPERLINK)
            // 需要读取合并单元格信息 默认不读取
            .extraRead(CellExtraTypeEnum.MERGE).sheet().doRead();

    }

    /**
     * @param multipartFile
     * @return File
     */
    private File transferToFile(MultipartFile multipartFile) {
        File file = null;
        try {
            String originalFilename = multipartFile.getOriginalFilename();
            String[] filename = originalFilename.split("\\.");
            file = File.createTempFile(filename[0], filename[1]);
            multipartFile.transferTo(file);
            file.deleteOnExit();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return file;
    }

**
 * 模板的读取类
 *
 * @author Jiaju Zhuang
 */
public class IndexOrNameDataListener extends AnalysisEventListener<IndexOrNameData> {
    private static final Logger LOGGER = LoggerFactory.getLogger(IndexOrNameDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<IndexOrNameData> list = new ArrayList<IndexOrNameData>();

    @Override
    public void invoke(IndexOrNameData data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        LOGGER.info("存储数据库成功！");
    }
}

 

 

写excel:

DO：

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;

import lombok.Data;

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

 

 

   /**
     * 最简单的写
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>
     * 2. 直接写即可
     */
    @Test
    public void simpleWrite() {
        // 写法1
        String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());

        // 写法2
        fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(fileName, DemoData.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            excelWriter.write(data(), writeSheet);
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * 根据参数只导出指定列
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>
     * 2. 根据自己或者排除自己需要的列
     * <p>
     * 3. 直接写即可
     *
     * @since 2.1.1
     */
    @Test
    public void excludeOrIncludeWrite() {
        String fileName = TestFileUtil.getPath() + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";

        // 根据用户传入字段 假设我们要忽略 date
        Set<String> excludeColumnFiledNames = new HashSet<String>();
        excludeColumnFiledNames.add("date"); //直接使用@ExcelIgnore　在需要忽略的字段上
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, DemoData.class).excludeColumnFiledNames(excludeColumnFiledNames).sheet("模板")
            .doWrite(data());

        fileName = TestFileUtil.getPath() + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";
        // 根据用户传入字段 假设我们只要导出 date
        Set<String> includeColumnFiledNames = new HashSet<String>();
        includeColumnFiledNames.add("date");
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, DemoData.class).includeColumnFiledNames(includeColumnFiledNames).sheet("模板")
            .doWrite(data());
    }

 

 

web: 下载：

　　//#直接下载
    @ApiOperation(value="cainiaBill", notes = "request params like:  eg : {'bookingNo':'xx-xxx','fromDate','2021-07-01 00:00:00','toDate':'2021-07-11 00:00:00'}")
    @PostMapping("canewBill")
    public void  cainiaoNewBillingReport(HttpServletResponse response, @RequestBody JSONObject criteriaMap) 
    {
        try 
        {
        List<CainiaoNewBillingReportDO> data = cainiaoNewBillingReportService.genReportData(criteriaMap);
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        String fileName = "billing"+TimeUtils.getCurrentTimeString();
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), CainiaoNewBillingReportDO.class).registerWriteHandler(new CustomCellWriteHandler()).sheet("billing").doWrite(data);
        } catch (IOException e) {
            log.error("gen excel exception : {}",e);
            e.printStackTrace();
        }
    }
    
    //# 以base64格式输出
    @ApiOperation(value="cainiaonewBill", notes = "request params like:  eg : {'bookingNo':'xxx-BK00001061','fromDate','2021-07-01 00:00:00','toDate':'2021-07-11 00:00:00'}")
    @PostMapping("cillReport")
    public JSONObject  cainiaoNewBillingReport2(@RequestBody JSONObject criteriaMap) 
    {
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            List<CainiaoNewBillingReportDO> data = cainiaoNewBillingReportService.genReportData(criteriaMap);
            String fileName = "billing"+TimeUtils.getCurrentTimeString();
            EasyExcel.write(byteArrayOutputStream, CainiaoNewBillingReportDO.class).registerWriteHandler(new CustomCellWriteHandler()).sheet("billing").doWrite(data);
            byte[] encode = Base64.getEncoder().encode(byteArrayOutputStream.toByteArray());
            return ResponseUtil.getResult(200, ResponseCode.SUCCESS.getMessage(), encode);
    }