[]
产品首页 在线Demo 产品文档 API 参考 技术社区
立即试用
(Showing Draft Content)

GcExcel 与 POI 性能对比

Apache POI是一个非常流行的Java库，用于处理和操作电子表格数据。鉴于POI的广泛应用，我们将POI作为基准，把GcExcel与POI进行性能对比测试，如果您正在寻找合适的表格组件，请阅读下面的性能测试报告，以让您更充分地了解GcExcel的性能情况。

我们设计了一系列的性能测试，包括打开文件、保存文件、导入文件、导出文件（单一Excel文件含多个sheet）、导出文件（多个Excel文件每个仅含一个sheet）、设置数据以及读取数据等方案。这些测试旨在比较GcExcel和POI在处理电子表格时的速度和效率。

测试环境：

Platform

CPU

Memory

OS version

Windows

Intel(R) Core(TM) i7-9750H CPU @ 2.60GHz

32GB RAM

Windows 11 Pro

测试版本：

  • GcExcel：6.1

  • Apache POI：5.2.3

测试用例：

用例设计：

Excel文件中的数据量，取决于行与列的乘积，即总单元格数量，本测试中，数据量单位均为单元格数量。

同时请注意，千万级以上数据量场景需较多的内存，并非常用场景，此处用做极限用例展示 GcExcel 与 POI 的区别。

1. 打开文件

描述：分别测试GcExcel 与 POI 打开Excel文件时的性能情况。

测试代码：

//GcExcel
private Workbook _workbook;
@TimeLimit
@Benchmark
public void open() {
    _workbook.open(_filePath);
}
//POI
private Workbook _workbook;
@TimeLimit
@Benchmark
public void open() throws IOException {
    try {
        _workbook = new SXSSFWorkbook(new XSSFWorkbook(_filePath));
    } catch (OutOfMemoryError e) {
        System.out.println("out of memory: " + this.getClass().getSimpleName());
    }
}

测试结果 （N/A表示无法完成测试，获取不到结果）

打开文件（单元格数量）

GcExcel

POI

50万个

986.71ms

5013.67ms

100万

1445.88ms

9778.38ms

500万

3499.97ms

33949.29ms

1000万

6200.15ms

63073.73ms

1500万

9486.98ms

N/A

2. 保存文件

描述：测试GcExcel 或 POI 保存文件时的性能。

测试代码：

//GcExcel
private Workbook _workbook;
@TimeLimit
@Benchmark
public void save() {
    _workbook.save("output\\" + "50w_noStyle" + "out.xlsx");
}
//POI
private Workbook _workbook;
@TimeLimit
@Benchmark
public void open() throws IOException {
    try {
        _workbook.write(new FileOutputStream("output\\" + "50w_noStyle" + "out.xlsx"));
    } catch (OutOfMemoryError e) {
        System.out.println("out of memory: " + this.getClass().getSimpleName());
    }
}

测试结果 （N/A表示无法完成测试，获取不到结果）

保存文件（单元格数量）

GcExcel

POI

50万

2343.08ms

6304.81ms

100万

3985.49ms

11298.62ms

500万

15294.28ms

45816.86ms

1000万

28664.09ms

N/A

1500万

46692.52ms

N/A

3. 导入文件

描述：测试把Excel当做表单，在服务端做数据整合时的性能。

测试代码：

//GcExcel

private String _filePath;
private Workbook _workbook;

@Setup
public void testInit() {
    _workbook = new Workbook();
    _filePath = FileHelper.GetResourceFilePath("年终资产负债表(含分析).xlsx");
}

@TimeLimit
@Benchmark
public void open() {
    for (int i = 0; i < 100; i++) {
        _workbook.importData(_filePath, "基础", 4, 2, 14, 6);
    }
}
//POI
private String _filePath;
private Workbook _workbook;

@Setup
public void testInit() {
    _filePath = FileHelper.GetResourceFilePath("年终资产负债表(含分析).xlsx");
}

@TimeLimit
@Benchmark
public void open() throws IOException {
    // k的循环条件决定了导入的次数
    for (int k = 0; k < 100; k++) {
        _workbook = new XSSFWorkbook(_filePath);
        Object[][] values = new Object[14][6];
        Sheet sheet = _workbook.getSheetAt(0);
        for (int i = 0; i < 14; i++) {
            Row row = sheet.getRow(i + 4);
            for (int j = 0; j < 6; j++) {
                Cell cell = row.getCell(j + 2);

                if (cell == null) {
                    values[i][j] = null;
                } else {
                    CellType type = cell.getCellType();
                    switch (type) {
                        case NUMERIC:
                            values[i][j] = cell.getNumericCellValue();
                            break;
                        case ERROR:
                            values[i][j] = cell.getErrorCellValue();
                            break;
                        case STRING:
                            values[i][j] = cell.getStringCellValue();
                            break;
                        case BOOLEAN:
                            values[i][j] = cell.getBooleanCellValue();
                            break;
                        case BLANK:
                        case _NONE:
                        case FORMULA:
                        default:
                            values[i][j] = null;
                            break;
                    }
                }
            }
        }
    }
}

测试结果 （N/A表示无法完成测试，获取不到结果）

导入文件

获取数据（年终资产负债表）

GcExcel

POI

100次

1828.96ms

3705.22ms

500次

6413.12ms

9501.68ms

1000次

12789.12ms

14924.06ms

5000次

56425.77ms

54952.95ms

4. 导出文件（单一Excel）

描述：模拟用户导出的case，使用固定的模板。把每一条数据导出成一页sheet生成到同一个Excel中。

测试代码：

//GcExcel
private String _filePath;
private Workbook _workbook;
private List<SalaryItem> _salaryList;

@Setuppublic void testInit() {
    _workbook = new Workbook();
    _filePath = FileHelper.GetResourceFilePath("工资单模板2.xlsx");
    _workbook.open(_filePath);
    String json = null;
    try {
        InputStream stream = new FileInputStream(FileHelper.GetResourceFilePath("SalaryList100.json"));

        ByteArrayOutputStream result = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024];
        int length;
        while ((length = stream.read(buffer)) != -1) {
            result.write(buffer, 0, length);
        }
        json = result.toString("UTF-8");
    } catch (IOException e) {
        e.printStackTrace();
    }
    _salaryList = new Gson().fromJson(json, new TypeToken<List<SalaryItem>>(){}.getType());
    File file = new File("output\\salary\\100");
    if (!file.exists() || !file.isDirectory()) {
        file.mkdirs();
    }
}

@TimeLimit@Benchmarkpublic void calculate() {
    IWorksheet sheet = _workbook.getActiveSheet();
    for (SalaryItem item :
            _salaryList) {
        sheet.getRange("C3").setValue(item.Name);
        sheet.getRange("I1").setValue(item.DateTime);

        sheet.getRange("E6").setValue(item.SalaryInfo.Basic);
        sheet.getRange("E7").setValue(item.SalaryInfo.Additional);
        sheet.getRange("E8").setValue(item.SalaryInfo.Other);

        sheet.getRange("E10").setValue(item.LeaveInfo.Absent);
        sheet.getRange("E12").setValue(item.LeaveInfo.SickLeave);
        sheet.getRange("E14").setValue(item.LeaveInfo.Late);
        sheet.getRange("E15").setValue(item.LeaveInfo.PaidLeave);
        sheet.getRange("E16").setValue(item.LeaveInfo.OtherLeave);

        sheet.getRange("I6").setValue(item.DeductionInfo.HousingFun);
        sheet.getRange("I7").setValue(item.DeductionInfo.PensionInsurance);
        sheet.getRange("I8").setValue(item.DeductionInfo.MedicalInsurance);
        sheet.getRange("I9").setValue(item.DeductionInfo.UnemploymentInsurance);

        sheet.getRange("I12").setValue(item.IndividualTaxDeductionInfo.ChildrenEducation);
        sheet.getRange("I13").setValue(item.IndividualTaxDeductionInfo.ElderlySupport);
        sheet.getRange("I14").setValue(item.IndividualTaxDeductionInfo.HouseLoan);
        sheet.getRange("I15").setValue(item.IndividualTaxDeductionInfo.Rental);
        sheet.getRange("I16").setValue(item.IndividualTaxDeductionInfo.ContinuingEducation);
        sheet.getRange("I17").setValue(item.IndividualTaxDeductionInfo.ChildCareExpenses);
        sheet.getRange("I18").setValue(item.IndividualTaxDeductionInfo.SeriousIllness);
        sheet.getRange("I19").setValue(item.IndividualTaxDeductionInfo.Other);
        sheet.getRange("I20").setValue(item.IndividualTaxDeductionInfo.IndividualTax);
        sheet.getRange("I21").setValue(item.IndividualTaxDeductionInfo.Deductions);

        sheet.getRange("E22").setValue(item.TotalPayment);
        sheet.getRange("I22").setValue(item.ActualPayment);

        sheet.getRange("C23").setValue(item.ManagerName);
        sheet.getRange("J23").setValue(item.WhoMakeSheet);
        _workbook.save("output\\salary\\100\\" + item.Name +".xlsx");
    }
}
//POI
private String _filePath;
private Workbook _workbook;
private List<SalaryItem> _salaryList;

@Setuppublic void testInit() throws IOException {
    _filePath = FileHelper.GetResourceFilePath("工资单模板2.xlsx");
    _workbook = new XSSFWorkbook(_filePath);
    String json = null;
    try {
        InputStream stream = new FileInputStream(FileHelper.GetResourceFilePath("SalaryList100.json"));

        ByteArrayOutputStream result = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024];
        int length;
        while ((length = stream.read(buffer)) != -1) {
            result.write(buffer, 0, length);
        }
        json = result.toString("UTF-8");
    } catch (IOException e) {
        e.printStackTrace();
    }
    _salaryList = new Gson().fromJson(json, new TypeToken<List<SalaryItem>>() {
    }.getType());
    File file = new File("output\\salary\\100");
    if (!file.exists() || !file.isDirectory()) {
        file.mkdirs();
    }
}

@TimeLimit@Benchmarkpublic void calculate() throws IOException {

    try {
        for (SalaryItem item :
                _salaryList) {
            Sheet sheet = _workbook.cloneSheet(0);
            sheet.getRow(2).getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.Name);
            sheet.getRow(0).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.DateTime);

            sheet.getRow(5).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.SalaryInfo.Basic);
            sheet.getRow(6).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.SalaryInfo.Additional);
            sheet.getRow(7).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.SalaryInfo.Other);

            sheet.getRow(9).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.LeaveInfo.Absent);
            sheet.getRow(11).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.LeaveInfo.SickLeave);
            sheet.getRow(13).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.LeaveInfo.Late);
            sheet.getRow(14).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.LeaveInfo.PaidLeave);
            sheet.getRow(15).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.LeaveInfo.OtherLeave);

            sheet.getRow(5).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.DeductionInfo.HousingFun);
            sheet.getRow(6).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.DeductionInfo.PensionInsurance);
            sheet.getRow(7).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.DeductionInfo.MedicalInsurance);
            sheet.getRow(8).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.DeductionInfo.UnemploymentInsurance);

            sheet.getRow(11).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.ChildrenEducation);
            sheet.getRow(12).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.ElderlySupport);
            sheet.getRow(13).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.HouseLoan);
            sheet.getRow(14).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.Rental);
            sheet.getRow(15).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.ContinuingEducation);
            sheet.getRow(16).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.ChildCareExpenses);
            sheet.getRow(17).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.SeriousIllness);
            sheet.getRow(18).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.Other);
            sheet.getRow(19).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.IndividualTax);
            sheet.getRow(20).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.Deductions);

            sheet.getRow(21).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.TotalPayment);
            sheet.getRow(21).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.ActualPayment);

            sheet.getRow(22).getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.ManagerName);
            sheet.getRow(22).getCell(9, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.WhoMakeSheet);
        }
        _workbook.write(new FileOutputStream("output\\" + "salary_single_100.xlsx"));
    } catch (OutOfMemoryError e) {
        System.out.println("out of memory: " + this.getClass().getSimpleName());
    }
}

测试结果 （N/A表示无法完成测试，获取不到结果）

导出Excel

GcExcel

POI

100次

3297.51ms

1563.38ms

500次

9900.73ms

5562.78ms

1000次

18623.26ms

19273.78ms

10000次

286385.61ms

N/A

5. 导出文件（多个Excel文件，单一工作表）

描述：模拟用户导出的case，使用固定的模板。把每一条数据导出成一页sheet生成到独立的一个Excel中。

测试代码：

//GcExcel
private String _filePath;
private Workbook _workbook;
private List<SalaryItem> _salaryList;

@Setuppublic void testInit() {
    _workbook = new Workbook();
    _filePath = FileHelper.GetResourceFilePath("工资单模板2.xlsx");
    _workbook.open(_filePath);
    String json = null;
    try {
        InputStream stream = new FileInputStream(FileHelper.GetResourceFilePath("SalaryList100.json"));

        ByteArrayOutputStream result = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024];
        int length;
        while ((length = stream.read(buffer)) != -1) {
            result.write(buffer, 0, length);
        }
        json = result.toString("UTF-8");
    } catch (IOException e) {
        e.printStackTrace();
    }
    _salaryList = new Gson().fromJson(json, new TypeToken<List<SalaryItem>>(){}.getType());
    File file = new File("output\\salary\\100");
    if (!file.exists() || !file.isDirectory()) {
        file.mkdirs();
    }
}

@TimeLimit@Benchmarkpublic void calculate() {
    IWorksheet sheet = _workbook.getActiveSheet();
    for (SalaryItem item :
            _salaryList) {
        sheet.getRange("C3").setValue(item.Name);
        sheet.getRange("I1").setValue(item.DateTime);

        sheet.getRange("E6").setValue(item.SalaryInfo.Basic);
        sheet.getRange("E7").setValue(item.SalaryInfo.Additional);
        sheet.getRange("E8").setValue(item.SalaryInfo.Other);

        sheet.getRange("E10").setValue(item.LeaveInfo.Absent);
        sheet.getRange("E12").setValue(item.LeaveInfo.SickLeave);
        sheet.getRange("E14").setValue(item.LeaveInfo.Late);
        sheet.getRange("E15").setValue(item.LeaveInfo.PaidLeave);
        sheet.getRange("E16").setValue(item.LeaveInfo.OtherLeave);

        sheet.getRange("I6").setValue(item.DeductionInfo.HousingFun);
        sheet.getRange("I7").setValue(item.DeductionInfo.PensionInsurance);
        sheet.getRange("I8").setValue(item.DeductionInfo.MedicalInsurance);
        sheet.getRange("I9").setValue(item.DeductionInfo.UnemploymentInsurance);

        sheet.getRange("I12").setValue(item.IndividualTaxDeductionInfo.ChildrenEducation);
        sheet.getRange("I13").setValue(item.IndividualTaxDeductionInfo.ElderlySupport);
        sheet.getRange("I14").setValue(item.IndividualTaxDeductionInfo.HouseLoan);
        sheet.getRange("I15").setValue(item.IndividualTaxDeductionInfo.Rental);
        sheet.getRange("I16").setValue(item.IndividualTaxDeductionInfo.ContinuingEducation);
        sheet.getRange("I17").setValue(item.IndividualTaxDeductionInfo.ChildCareExpenses);
        sheet.getRange("I18").setValue(item.IndividualTaxDeductionInfo.SeriousIllness);
        sheet.getRange("I19").setValue(item.IndividualTaxDeductionInfo.Other);
        sheet.getRange("I20").setValue(item.IndividualTaxDeductionInfo.IndividualTax);
        sheet.getRange("I21").setValue(item.IndividualTaxDeductionInfo.Deductions);

        sheet.getRange("E22").setValue(item.TotalPayment);
        sheet.getRange("I22").setValue(item.ActualPayment);

        sheet.getRange("C23").setValue(item.ManagerName);
        sheet.getRange("J23").setValue(item.WhoMakeSheet);
        _workbook.save("output\\salary\\100\\" + item.Name +".xlsx");
    }
}
//POI
private String _filePath;
private Workbook _workbook;
private List<SalaryItem> _salaryList;

@Setuppublic void testInit() throws IOException {
    _filePath = FileHelper.GetResourceFilePath("工资单模板2.xlsx");
    _workbook = new XSSFWorkbook(_filePath);
    String json = null;
    try {
        InputStream stream = new FileInputStream(FileHelper.GetResourceFilePath("SalaryList100.json"));

        ByteArrayOutputStream result = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024];
        int length;
        while ((length = stream.read(buffer)) != -1) {
            result.write(buffer, 0, length);
        }
        json = result.toString("UTF-8");
    } catch (IOException e) {
        e.printStackTrace();
    }
    _salaryList = new Gson().fromJson(json, new TypeToken<List<SalaryItem>>() {
    }.getType());
    File file = new File("output\\salary\\100");
    if (!file.exists() || !file.isDirectory()) {
        file.mkdirs();
    }
}

@TimeLimit@Benchmarkpublic void calculate() throws IOException {

    try {
        for (SalaryItem item :
                _salaryList) {
            Sheet sheet = _workbook.cloneSheet(0);
            sheet.getRow(2).getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.Name);
            sheet.getRow(0).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.DateTime);

            sheet.getRow(5).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.SalaryInfo.Basic);
            sheet.getRow(6).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.SalaryInfo.Additional);
            sheet.getRow(7).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.SalaryInfo.Other);

            sheet.getRow(9).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.LeaveInfo.Absent);
            sheet.getRow(11).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.LeaveInfo.SickLeave);
            sheet.getRow(13).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.LeaveInfo.Late);
            sheet.getRow(14).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.LeaveInfo.PaidLeave);
            sheet.getRow(15).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.LeaveInfo.OtherLeave);

            sheet.getRow(5).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.DeductionInfo.HousingFun);
            sheet.getRow(6).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.DeductionInfo.PensionInsurance);
            sheet.getRow(7).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.DeductionInfo.MedicalInsurance);
            sheet.getRow(8).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.DeductionInfo.UnemploymentInsurance);

            sheet.getRow(11).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.ChildrenEducation);
            sheet.getRow(12).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.ElderlySupport);
            sheet.getRow(13).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.HouseLoan);
            sheet.getRow(14).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.Rental);
            sheet.getRow(15).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.ContinuingEducation);
            sheet.getRow(16).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.ChildCareExpenses);
            sheet.getRow(17).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.SeriousIllness);
            sheet.getRow(18).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.Other);
            sheet.getRow(19).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.IndividualTax);
            sheet.getRow(20).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.IndividualTaxDeductionInfo.Deductions);

            sheet.getRow(21).getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.TotalPayment);
            sheet.getRow(21).getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.ActualPayment);

            sheet.getRow(22).getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.ManagerName);
            sheet.getRow(22).getCell(9, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(item.WhoMakeSheet);
        }
        _workbook.write(new FileOutputStream("output\\" + "salary_single_100.xlsx"));
    } catch (OutOfMemoryError e) {
        System.out.println("out of memory: " + this.getClass().getSimpleName());
    }
}

测试结果 （N/A表示无法完成测试，获取不到结果）

导出Excel

GcExcel

POI

100次

8410.50ms

1218.64ms

500次

29983.84ms

4966.60ms

1000次

59652.28ms

6481.71ms

10000次

617264.57ms

120346.63ms

6. 设置数据

描述：模拟用户导出的case，使用固定的模板。把每一条数据导出成一页sheet生成到独立的一个Excel中。

测试代码：

//GcExcel
private String _filePath;
private Workbook _workbook;
private Workbook _workbook2;
private Object _rangeValue;

@Setuppublic void testInit() {
    _workbook = new Workbook();
    _filePath = FileHelper.GetResourceFilePath("100%\\50w_noStyle.xlsx");
    _workbook.open(_filePath);
    _rangeValue = _workbook.getWorksheets().get(0).getRange("A1:T25000");
    _workbook2 = new Workbook();
}

@TimeLimit@Benchmarkpublic void open() {
    _workbook2.getWorksheets().get(0).getRange("A1:T25000").setValue(_rangeValue);
}
//POI
private String _filePath;
private Workbook _workbook;
private Workbook _workbook2;
private Object[][] _rangeValue;

public static final int ROWCOUNT = 25000;
public static final int COLUMNCOUNT = 20;

@Setuppublic void testInit() throws IOException {
    try {
        _filePath = FileHelper.GetResourceFilePath("100%\\50w_noStyle.xlsx");
        _workbook = new XSSFWorkbook(_filePath);
        _workbook2 = new SXSSFWorkbook();

        _rangeValue = new Object[ROWCOUNT][COLUMNCOUNT];
        Sheet sheet = _workbook.getSheetAt(0);
        for (int i = 0; i < ROWCOUNT; i++) {
            Row row = sheet.getRow(i);
            for (int j = 0; j < COLUMNCOUNT; j++) {
                Cell cell = row.getCell(j);

                if (cell == null) {
                    _rangeValue[i][j] = null;
                } else {
                    CellType type = cell.getCellType();
                    switch (type) {
                        case NUMERIC:
                            _rangeValue[i][j] = cell.getNumericCellValue();
                            break;
                        case ERROR:
                            _rangeValue[i][j] = cell.getErrorCellValue();
                            break;
                        case STRING:
                            _rangeValue[i][j] = cell.getStringCellValue();
                            break;
                        case BOOLEAN:
                            _rangeValue[i][j] = cell.getBooleanCellValue();
                            break;
                        case BLANK:
                        case _NONE:
                        case FORMULA:
                        default:
                            _rangeValue[i][j] = null;
                            break;
                    }
                }
            }
        }
    } catch (OutOfMemoryError e) {
        System.out.println("out of memory: " + this.getClass().getSimpleName());
    }
}

@TimeLimit@Benchmarkpublic void open() {
    try {
        Sheet sheet = _workbook2.createSheet();
        for (int i = 0; i < ROWCOUNT; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < COLUMNCOUNT; j++) {
                Cell cell = row.createCell(j);
                if (_rangeValue[i][j] instanceof Double) {
                    cell.setCellValue((double) _rangeValue[i][j]);
                } else if (_rangeValue[i][j] instanceof String) {
                    cell.setCellValue((String) _rangeValue[i][j]);
                } else if (_rangeValue[i][j] instanceof Boolean) {
                    cell.setCellValue((Boolean) _rangeValue[i][j]);
                }
            }
        }
    } catch (OutOfMemoryError e) {
        System.out.println("out of memory: " + this.getClass().getSimpleName());
    }
}

测试结果 （N/A表示无法完成测试，获取不到结果）

导出Excel（单元格数量）

GcExcel

POI

50万

13.97ms

1403.75ms

100万

12.17ms

2678.86ms

500万

21.80ms

7681.18ms

1000万

44.74ms

N/A

1500万

65.18ms

N/A

7. 获取数据

描述：模拟用户导出的case，使用固定的模板。把每一条数据导出成一页sheet生成到独立的一个Excel中。

测试代码：

//GcExcel
private String _filePath;
private Workbook _workbook;

@Setuppublic void testInit() {
    _workbook = new Workbook();
    _filePath = FileHelper.GetResourceFilePath("100%\\50w_noStyle.xlsx");
    _workbook.open(_filePath);
}

@TimeLimit@Benchmarkpublic void open() {
    _workbook.getWorksheets().get(0).getRange("A1:T25000").getValue();
}
//POI
private String _filePath;
private Workbook _workbook;

@Setuppublic void testInit() throws IOException {
    _filePath = FileHelper.GetResourceFilePath("100%\\50w_noStyle.xlsx");
    _workbook = new XSSFWorkbook(_filePath);
}

@TimeLimit@Benchmarkpublic void open() {
    try {
        Object[][] values = new Object[25000][20];
        Sheet sheet = _workbook.getSheetAt(0);
        for (int i = 0; i < 25000; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                System.out.println("-----------------" + i + "行");
            }
            for (int j = 0; j < 20; j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    values[i][j] = null;
                } else {
                    CellType type = cell.getCellType();
                    switch (type) {
                        case NUMERIC:
                            values[i][j] = cell.getNumericCellValue();
                            break;
                        case ERROR:
                            values[i][j] = cell.getErrorCellValue();
                            break;
                        case STRING:
                            values[i][j] = cell.getStringCellValue();
                            break;
                        case BOOLEAN:
                            values[i][j] = cell.getBooleanCellValue();
                            break;
                        case BLANK:
                        case _NONE:
                        case FORMULA:
                        default:
                            values[i][j] = null;
                            break;
                    }
                }
            }
        }
    } catch (OutOfMemoryError e) {
        System.out.println("out of memory: " + this.getClass().getSimpleName());
    }
}

测试结果 （N/A表示无法完成测试，获取不到结果）

导出Excel（单元格数量）

GcExcel

POI

50万

217.32ms

1303.69ms

100万

249.86ms

1068.17ms

500万

385.64ms

1171.99ms

1000万

573.88ms

3217.78ms

1500万

675.03ms

2158.06ms