java操作Excel--jxl与poj的比较

最新推荐文章于 2021-04-20 09:48:24 发布

转载最新推荐文章于 2021-04-20 09:48:24 发布 · 607 阅读

收录于

javaWeb开发

本文介绍使用Java JXL库和Apache POI库进行Excel文件读写的方法。包括创建Excel文件、设置单元格内容及从Excel中读取数据的过程，并对比了两种库在处理大量数据时的效率。

[java]view plaincopy 
  
print?

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();
}
}
}
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();
}
}
}