java操作excel——jxl和poi比较

最近需要需要项目开发需要从excel导入数据到数据库，于是就开始找开源的java操作excel的框架。貌似比较流行的有jxl和poi两个框架。网上有些对这两个框架比较的文章，但都不是最近的。根据项目需要，下面对jxl和poi读写excel的性能做个比较。

 

jxl：jxl-2.6.12.jar

poi:poi-3.8.jar

 

JXLTestMain.java

package com.nexusy.excel.jxl;

import java.io.File;
import java.io.IOException;
import java.util.List;

import jxl.Cell;
import jxl.CellType;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import com.nexusy.excel.Record;
import com.nexusy.excel.TestUtil;

public class JXLTestMain {
    
    private final static String filename = "jxltest.xls";
    private final static String[] headers = {"ID", "标题", "价格", "数量", "描述"};
    
    private final static int rows = 65535;

    public static void main(String[] args) {
        writeExcel();
        readExcel();
    }
    
    public static void writeExcel() {
        try {
            Thread.sleep(1000*20);
        } catch (InterruptedException e1) {
            e1.printStackTrace();
        }
        try {
            WritableWorkbook workbook = Workbook.createWorkbook(new File(filename));
            
            WritableSheet  sheet = workbook.createSheet("jxl测试", 0);
            
            for (int i = 0; i < headers.length; i++) {
                Label label = new Label(i, 0 , headers[i]);
                sheet.addCell(label);
            }
            
            List<Record> records = TestUtil.getRecords(rows);
            long s1 = System.nanoTime();
            int c = 1;
            for (Record record : records) {
                sheet.addCell(new Number(0, c, record.getId()));
                sheet.addCell(new Label(1, c, record.getTitle()));
                sheet.addCell(new Number(2, c, record.getPrice()));
                sheet.addCell(new Number(3, c, record.getQuantity()));
                sheet.addCell(new Label(4, c, record.getDesc()));
                c++;
            }
            
            workbook.write();
            workbook.close();
            long s2 = System.nanoTime();
            System.out.println("jxl write " + rows + " rows to excel:" + (s2-s1));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }

    }

    public static void readExcel() {
        try {
            Thread.sleep(1000*20);
        } catch (InterruptedException e1) {
            e1.printStackTrace();
        }
        try {
            long s1 = System.nanoTime();
            Workbook workbook = Workbook.getWorkbook(new File(filename));
            Sheet sheet = workbook.getSheet(0);
            System.out.println(sheet.getName());
            for(int i = 0; i < sheet.getRows(); i++){
                Cell[] cells = sheet.getRow(i);
                for (Cell cell : cells) {
                    if(cell.getType() == CellType.NUMBER){
                        System.out.print(((NumberCell)cell).getValue()+"  ");
                    } else if(cell.getType() == CellType.LABEL){
                        System.out.print(cell.getContents()+"  ");
                    }
                }
                System.out.println();
            }
            workbook.close();
            long s2 = System.nanoTime();
            System.out.println("jxl read " + rows + " rows from excel:" + (s2-s1));
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

 

POITestMain.java

package com.nexusy.excel.poi;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.nexusy.excel.Record;
import com.nexusy.excel.TestUtil;

public class POITestMain {
    
    private final static String filename = "poitest.xls";
    private final static String[] headers = {"ID", "标题", "价格", "数量", "描述"};
    
    private final static int rows = 65535;

    public static void main(String[] args) {
        writeExcel();
        readExcel();
    }

    public static void writeExcel() {
        try {
            Thread.sleep(1000*20);
        } catch (InterruptedException e1) {
            e1.printStackTrace();
        }
        Workbook wb = new HSSFWorkbook();
        try {
            FileOutputStream fileOut = new FileOutputStream(filename);
            
            Sheet sheet = wb.createSheet("poi测试");
            Row row = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                row.createCell(i).setCellValue(headers[i]);
            }
            
            List<Record> records = TestUtil.getRecords(rows);
            long s1 = System.nanoTime();
            int r = 1;
            for (Record record : records) {
                row = sheet.createRow(r);
                row.createCell(0).setCellValue(record.getId());
                row.createCell(1).setCellValue(record.getTitle());
                row.createCell(2).setCellValue(record.getPrice());
                row.createCell(3).setCellValue(record.getQuantity());
                row.createCell(4).setCellValue(record.getDesc());
                r++;
            }
            
            wb.write(fileOut);
            fileOut.close();
            long s2 = System.nanoTime();
            System.out.println("poi write " + rows + " rows to excel:" + (s2-s1));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void readExcel() {
        try {
            Thread.sleep(1000*20);
        } catch (InterruptedException e1) {
            e1.printStackTrace();
        }
        try {
            long s1 = System.nanoTime();
            InputStream inp = new FileInputStream(filename);
            Workbook wb = new HSSFWorkbook(inp);
            Sheet sheet = wb.getSheetAt(0);
            System.out.println(sheet.getSheetName());
            
            for(Row row : sheet){
                for(Cell cell : row){
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "  ");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "  ");
                        break;

                    default:
                        break;
                    }
                }
                System.out.println();
            }
            inp.close();
            long s2 = System.nanoTime();
            System.out.println("poi read " + rows + " rows from excel:" + (s2-s1));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }


    }

}

 

 测试结果如下：

jxl写入65535行数据

 

jxl读取65535行数据： 

 

 

poi写入65535行数据：

 poi读取65535行数据：