POI-Excel

数据准备

    private static final List<String> HEADER_LIST = Lists.newArrayList(
            "订单号", "下单时间", "下单城市", "商品名称", "商品ID", "用户信息", "售卖价", "尾款金额", "预付款金额", "商品类型");
    private static final List<String> CONTENT_LIST = Lists.newArrayList(
            "1001001001", "2019-01-01", "上海", "测试商品", "ABC", "测试用户", "100.99", "99", "100", "测试");

    @Data
    private static class TestData {
        @ExcelProperty("订单号")
        private String uniOrderId;
        @ExcelProperty("下单时间")
        private String addTime;
        @ExcelProperty("下单城市")
        private String cityName;
        @ExcelProperty("商品名称")
        private String productItemName;
        @ExcelProperty("商品ID")
        private String productId;
        @ExcelProperty("用户信息")
        private String mobile;
        @ExcelProperty("售卖价")
        private String price;
        @ExcelProperty("预付款金额")
        private String preAmount;
        @ExcelProperty("尾款金额")
        private String remainAmount;
        @ExcelProperty("商品类型")
        private String multiCard;
    }

    private static List<TestData> data(int count) {
        List<TestData> list = Lists.newArrayList();
        for (int i = 0; i < count; i++) {
            TestData data = new TestData();
            data.setUniOrderId("1001001001");
            data.setAddTime("2019-01-01");
            data.setCityName("上海");
            data.setProductItemName("测试商品");
            data.setProductId("ABC");
            data.setMobile("测试用户");
            data.setPrice("100.99");
            data.setPreAmount("99");
            data.setRemainAmount("100");
            data.setMultiCard("测试");
            list.add(data);
        }
        return list;
    }

JXL 最大行数为65535

private static void jxlTest(int count) throws Exception {
        WorkbookSettings settings = new WorkbookSettings();
        settings.setGCDisabled(true);
        WritableWorkbook workbook = Workbook.createWorkbook(new File("jxl.xls"), settings);
        WritableSheet sheet = workbook.createSheet("sheet0", 0);
        int index = 0;
        for (String header : HEADER_LIST) {
            sheet.addCell(new Label(index++, 0, header));
        }
        for (int i = 1; i <= count; i++) {
            index = 0;
            for (String content : CONTENT_LIST) {
                sheet.addCell(new Label(index++, i, content));
            }
        }
        workbook.write();
        workbook.close();
    }

 

HSSF 最大行数为65535

    private static void hssfTest(int count) throws Exception {
        HSSFWorkbook workBook = new HSSFWorkbook();
        HSSFSheet sheet = workBook.createSheet("sheet0");
        HSSFRow row = sheet.createRow(0);
        int index = 0;
        for (String header : HEADER_LIST) {
            row.createCell(index++).setCellValue(header);
        }
        for (int i = 1; i <= count; i++) {
            index = 0;
            HSSFRow contentRow = sheet.createRow(i);
            for (String content : CONTENT_LIST) {
                contentRow.createCell(index++).setCellValue(content);
            }
        }
        FileOutputStream stream = new FileOutputStream("hssf.xls");
        workBook.write(stream);
        workBook.close();
    }

XSSF  耗时严重、CPU和内存占用较高

    private static void xssfTest(int count) throws Exception {
        XSSFWorkbook workBook = new XSSFWorkbook();
        XSSFSheet sheet = workBook.createSheet("sheet0");
        XSSFRow row = sheet.createRow(0);
        int index = 0;
        for (String header : HEADER_LIST) {
            row.createCell(index++).setCellValue(header);
        }
        for (int i = 1; i <= count; i++) {
            index = 0;
            XSSFRow contentRow = sheet.createRow(i);
            for (String content : CONTENT_LIST) {               
                contentRow.createCell(index++).setCellValue(content);
            }
        }
        FileOutputStream stream = new FileOutputStream("hssf.xls");
        workBook.write(stream);
        workBook.close();
    }

SXSSF  对XSSFWorkbook对象的一种流扩展，在写入的对象达到一定量级后将excel中的信息flush到磁盘上

    private static void sxssfTest(int count) throws Exception {
        SXSSFWorkbook workBook = new SXSSFWorkbook(500);
        SXSSFSheet sheet = workBook.createSheet("sheet0");
        SXSSFRow row = sheet.createRow(0);
        int index = 0;
        for (String header : HEADER_LIST) {
            row.createCell(index++).setCellValue(header);
        }
        for (int i = 1; i <= count; i++) {
            index = 0;
            SXSSFRow contentRow = sheet.createRow(i);
            for (String content : CONTENT_LIST) {
                contentRow.createCell(index++).setCellValue(content);
            }
        }
        FileOutputStream stream = new FileOutputStream("sxssf.xlsx");
        workBook.write(stream);
        workBook.close();
    }

Easy Excel

private static void easyExcelTest(int count) throws Exception {
        ExcelWriter excelWriter = EasyExcel.write("sxeasyExcel.xlsx", TestData.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("sheet0").build();
        excelWriter.write(data(count), writeSheet);

        excelWriter.finish();
    }

        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.10</version>
        </dependency>
        <dependency>
            <groupId>net.sf.jxls</groupId>
            <artifactId>jxls-core</artifactId>
            <version>0.9.9</version>
        </dependency>    
    
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

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