pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>cn.sigangjun</groupId>
<artifactId>excel</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>excel</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
Record.java
package cn.sigangjun.excel;
/**
* @author sigangjun
* @date 2015-7-17
*/
public class Record {
private Long id;
private String title;
private Double price;
private Integer quantity;
private String desc;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public Integer getQuantity() {
return quantity;
}
public void setQuantity(Integer quantity) {
this.quantity = quantity;
}
public String getDesc() {
return desc;
}
public void setDesc(String desc) {
this.desc = desc;
}
}
TestUtil.java
package cn.sigangjun.excel;
import java.util.ArrayList;
import java.util.List;
/**
* @author sigangjun
* @date 2015-7-17
*/
public class TestUtil {
private static List<Record> records = new ArrayList<Record>();
public static List<Record> getRecords(int num){
records.clear();
for(int i = 0; i < num; i++){
Record record = new Record();
record.setId((long)i);
record.setPrice(i*1.1);
record.setTitle("商品" + i);
record.setQuantity(i);
record.setDesc("描述" + i);
records.add(record);
}
return records;
}
}
JXLTestMain.java
package cn.sigangjun.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 cn.sigangjun.excel.Record;
import cn.sigangjun.excel.TestUtil;
/**
* @author sigangjun
* @date 2015-7-17
*/
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 {
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 {
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 cn.sigangjun.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 cn.sigangjun.excel.Record;
import cn.sigangjun.excel.TestUtil;
/**
* @author sigangjun
* @date 2015-7-17
*/
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() {
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 {
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();
}
}
}