Poi和easyExcel

Poi和easyExcel

对Excel进行操作 把数据变为excel或者把excel写入数据库

poi的原理是直接先把数据加载到内存里面 这样很消耗内存 可能造成oom(内存溢出)

而easyExcel是一行一行进行解析 所以说它是针对poi优化的框架

其实excel有两个重要的版本 一个03 一个07 对应的后缀为xls 和xlsx 依赖分别是poi和poi-ooxml

03版本有65536行的一个限制 而07没有

excel由工作簿 工作表 行 列 组成

#03版本
    @Test
    public void test03() throws Exception {
        String path="E:\\idea\\ideaspace\\poi\\";
        Workbook workbook=new HSSFWorkbook();
        Sheet sheet=workbook.createSheet("张宇航的表");
        Row row=sheet.createRow(0);
        Cell cell=row.createCell(0);
        cell.setCellValue("1111");
        Cell cell1=row.createCell(1);
        cell1.setCellValue("111");
        Row row1=sheet.createRow(1);
        Cell cell2=row1.createCell(0);
        cell2.setCellValue("1111");
        Cell cell3=row1.createCell(1);
        String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell3.setCellValue(s);
        FileOutputStream fileOutputStream = new FileOutputStream(path + "张宇航.xls");

        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("文件生成完毕");


    }
    #07版本
   @Test
    public void test07() throws Exception {
        String path="E:\\idea\\ideaspace\\poi\\";
        Workbook workbook=new XSSFWorkbook();
        Sheet sheet=workbook.createSheet("张宇航的表");
        Row row=sheet.createRow(0);
        Cell cell=row.createCell(0);
        cell.setCellValue("奥利给");
        Cell cell1=row.createCell(1);
        cell1.setCellValue("给力奥");
        Row row1=sheet.createRow(1);
        Cell cell2=row1.createCell(0);
        cell2.setCellValue("啥也不是");
        Cell cell3=row1.createCell(1);
        String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell3.setCellValue(s);
        FileOutputStream fileOutputStream = new FileOutputStream(path + "张宇航.xlsx");

        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("文件生成完毕");



    }

注意对象和文件后缀

超过65536的话就会报上的错误 
@Test
    public void test03Big() throws Exception {
        long start = System.currentTimeMillis();

        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("張宇航的表");
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }

        }
        System.out.println("完事");
        FileOutputStream fileOutputStream = new FileOutputStream(path = "zyh.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end - start) / 1000);

    }
     @Test
    public void test07Big() throws Exception {
        long start = System.currentTimeMillis();

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("張宇航的表");
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }

        }
        System.out.println("完事");
        FileOutputStream fileOutputStream = new FileOutputStream(path = "zyh.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end - start) / 1000);单位毫秒

    }

在控制台中可以看出03的用了3秒多 07用了20多秒

   这个就是07的升级版SXSSF 只用了3秒 

   @Test
    public void testnb() throws Exception {
        long start = System.currentTimeMillis();

        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("張宇航的表");
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }

        }
        System.out.println("完事");
        FileOutputStream fileOutputStream = new FileOutputStream(path = "zyhsuper.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //清除临时文件
        ((SXSSFWorkbook) workbook).dispose();
        long end = System.currentTimeMillis();
        System.out.println((double) (end - start) / 1000);

    }

读取Excel

    @Test
    public void test03() throws Exception {
        //Excel能操作的 他都可以操作
        FileInputStream fileInputStream = new FileInputStream(path+"zyh.xls");
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        Sheet sheetAt = workbook.getSheetAt(0);

        Row row = sheetAt.getRow(0);
        Cell cell = row.getCell(0);
        //读取值的时候 需要注意数据类型    
        System.out.println((cell.getStringCellValue()));
        System.out.println((cell.getNumericCellValue()));
        fileInputStream.close();


    }

计算公式

当EXCEL中包含计算公式时，利用POI去执行的方法如下。

第一种，当你知道你要执行的workbook类型时（HSSF 或者XSSF），可以直接调用静态方法，一次将workbook上的所有公式计算完毕。

File tempFile = new File("/somepath/test.xls");
InputStream tempFileStream = new FileInputStream(tempFile);
HSSFWorkbook workBook = new HSSFWorkbook(tempFileStream);
// 执行计算公式
HSSFFormulaEvaluator.evaluateAllFormulaCells(workBook);

注意：当你利用POI写入单元格的内容不是Number类型，而是文本类型时。上述计算是不会执行的。


第二种： 遍历整个workbook去执行：

FileInputStream fis = new FileInputStream("/somepath/test.xls");
Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
    Sheet sheet = wb.getSheetAt(sheetNum);
    for(Row r : sheet) {
        for(Cell c : r) {
            if(c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                evaluator.evaluateFormulaCell(c);
            }
        }
    }
}

EasyExcel

    /**
     * 最简单的读
     * <p>1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
     * <p>3. 直接读即可
     */
    @Test
    public void simpleRead() {
        String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }

 /**
     * 最简单的写
     * <p>1. 创建excel对应的实体对象 参照{@link com.alibaba.easyexcel.test.demo.write.DemoData}
     * <p>2. 直接写即可
     */
    @Test
    public void simpleWrite() {
        String fileName = TestFileUtil.getPath() + "write" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去读，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    }
/**
     * 文件下载（失败了会返回一个有部分数据的Excel）
     * <p>1. 创建excel对应的实体对象 参照{@link DownloadData}
     * <p>2. 设置返回的 参数
     * <p>3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
     */
    @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        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");
        EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("模板").doWrite(data());
    }

    /**
     * 文件上传
     * <p>1. 创建excel对应的实体对象 参照{@link UploadData}
     * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link UploadDataListener}
     * <p>3. 直接读即可
     */
    @PostMapping("upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener(uploadDAO)).sheet().doRead();
        return "success";
    }