Show Me The Code -- excel解析的两种方法
概述
java对excel进行解析有两种常见的方法：jxl和POI
jxl：java Excel api 是一个开放源码的项目，它本身是由java编写的，所有对平台没有要求。
POI：Apache的一个开源项目，POI存在一些小的bug，并且对图片的处理并不友好，其优势在于对公式的支持比较完善。
对比两者的一些结论：jxl对于java开发者来说还是更合适一些，它在图片处理方面也具有优势，并且当所处理的excel文档本身比较小时它在性能上也优于POI，只是在对公式或者宏的兼容上不如POI，POI在excel文档体积大时具有性能上的优势。
下面展示两种方法对excel文件的基本读写功能
所需jar
jxl只需要一个jar，剩下的三个都是对POI的支持，所以真正使用的时候，jxl的jar包更容易被导入项目，对POI来说最好的方法是到Apache官网上下载他的完整资源包，然后得到三个版本匹配的jar，不同版本的jar可能出现兼容性问题
code
jxl
package com.firefly.jxl;
import java.io.File;
import java.io.IOException;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class JXLTest {
private static String EXCEL_PATH = "D://excel//TestExcel.xls";
public static void main(String[] args) {
// writeExcel();
ReadExcel();
}
public static void ReadExcel() {
System.out.println(" --------- read start ---------");
System.out.println(" --------- check file exists ---------");
File excel = new File(EXCEL_PATH);
if(!excel.exists()) {
System.out.println(" read excel exception case the excel file not exsits ");
return;
}
File xlsFile = excel;
try {
// 获得工作簿对象
Workbook workbook = Workbook.getWorkbook(xlsFile);
// 获得所有工作表
Sheet[] sheets = workbook.getSheets();
// 遍历工作表
if (sheets != null){
for (Sheet sheet : sheets){
// 获得行数
int rows = sheet.getRows();
// 获得列数
int cols = sheet.getColumns();
// 读取数据
for (int row = 0; row < rows; row++){
for (int col = 0; col < cols; col++){
System.out.printf("%10s", sheet.getCell(col, row)
.getContents());
}
System.out.println();
}
}
}
workbook.close();
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void writeExcel() {
System.out.println(" --------- write start ---------");
System.out.println(" --------- check file exists ---------");
File excel = new File(EXCEL_PATH);
//判断该文件以及该路径是否存在，不存在则创建
if(!excel.exists()) {
System.out.println(" --------- excel file not exists ---------");
if(!excel.getParentFile().exists())
System.out.println(" --------- folder not exists ---------");
excel.getParentFile().mkdirs();
try {
excel.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}
File xlsFile = excel;
try {
// 创建一个工作簿
WritableWorkbook workbook = Workbook.createWorkbook(xlsFile);
// 创建一个工作表
WritableSheet sheet = workbook.createSheet("sheet1", 0);
for (int row = 0; row < 10; row++){
for (int col = 0; col < 10; col++){
// 向工作表中添加数据
sheet.addCell(new Label(col, row, "data" + row + col));
}
}
System.out.println(" --------- write success ---------");
workbook.write();
workbook.close();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
POI
package com.firefly.poi;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class POITest {
private static String EXCEL_PATH = "D://excel//TestExcelForPoi.xls";
public static void main(String[] args) {
// TODO Auto-generated method stub
// writeExcel();
ReadExcel();
}
public static void ReadExcel() {
System.out.println(" --------- read start ---------");
System.out.println(" --------- check file exists ---------");
File excel = new File(EXCEL_PATH);
if(!excel.exists()) {
System.out.println(" --------- excel file not exists ---------");
return;
}
File xlsFile = excel;
// 获得工作簿
Workbook workbook;
try {
workbook = WorkbookFactory.create(xlsFile);
// 获得工作表个数
int sheetCount = workbook.getNumberOfSheets();
// 遍历工作表
for (int i = 0; i < sheetCount; i++){
Sheet sheet = workbook.getSheetAt(i);
// 获得行数
int rows = sheet.getLastRowNum() + 1;
// 获得列数，先获得一行，在得到改行列数
Row tmp = sheet.getRow(0);
if (tmp == null){
continue;
}
int cols = tmp.getPhysicalNumberOfCells();
// 读取数据
for (int row = 0; row < rows; row++){
Row r = sheet.getRow(row);
for (int col = 0; col < cols; col++){
System.out.printf("%10s", r.getCell(col).getStringCellValue());
}
System.out.println();
}
}
} catch (EncryptedDocumentException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void writeExcel() {
System.out.println(" --------- write start ---------");
// 创建工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建工作表
HSSFSheet sheet = workbook.createSheet("sheet1");
for (int row = 0; row < 10; row++){
HSSFRow rows = sheet.createRow(row);
for (int col = 0; col < 10; col++){
// 向工作表中添加数据
rows.createCell((short) col).setCellValue("dataPoi update" + row + col);
}
}
//判断该文件以及该路径是否存在，不存在则创建
System.out.println(" --------- check file exists ---------");
File excel = new File(EXCEL_PATH);
if(!excel.exists()) {
System.out.println(" --------- excel file not exists ---------");
if(!excel.getParentFile().exists())
System.out.println(" --------- folder not exists ---------");
excel.getParentFile().mkdirs();
try {
excel.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}
File xlsFile = excel;
FileOutputStream xlsStream;
try {
xlsStream = new FileOutputStream(xlsFile);
workbook.write(xlsStream);
System.out.println(" -------- excel write success -------");
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
sFile);
workbook.write(xlsStream);
System.out.println(" -------- excel write success -------");
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}