Java 操作 Excel
關(guān)于Java操作Excel文件，最近在網(wǎng)上經(jīng)?？吹接芯W(wǎng)友尋求這方面的資料，在這里我簡單介紹下我對這方面的了解
一、 讀取Excel文件，現(xiàn)在比較流行的第三方jar包有apache的poi和另一個jar包jxl
1先見poi，這個是目前做的最突出的一個操作Excel文件的工具包，支持Excel03、Excel07版，目前最高的版本是3.8，需要下載的工具包有：poi-3.8-20120326.jar,poi-ooxml-3.8-20120323.jar,
poi-ooxml-schemas-3.8-20120326.jar,xbean.jar,dom4j.jar
如果單純操作Excel03的話，那可以只下載poi-3.8-20120326.jar，后面幾個jar包是為Excel07服務(wù)的，這是由于Excel07的文件存儲結(jié)構(gòu)導(dǎo)致的
以下是我封裝好的poi操作Excel03的代碼：
package org.gdupt.poiexcel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.gdupt.exceptions.SampleException;
import org.gdupt.interfaces.RExcel;
public class ReadExcel03 implements RExcel{
private InputStream is ;
private HSSFWorkbook wb;
private HSSFSheet[] sheets;
private HSSFSheet sheet;
private HSSFRow row;
private int sheetNum;
private int rowNum;
private int colNum;
public ReadExcel03(String path) {
if(!path.trim().toLowerCase().endsWith(".xls")) {
throw new SampleException("不是有效的2003Excel文件");
}
if(checkFile(path)) {
try {
is = new FileInputStream(path);
wb = new HSSFWorkbook(is);
initSheet();
} catch (FileNotFoundException e) {
throw new SampleException("讀取的目標(biāo)文件不存在");
} catch (IOException e) {
throw new SampleException("打開Excel文件失敗");
}
} else {
throw new SampleException("無法使用在其他進(jìn)程或者程序已經(jīng)打開的文件");
}
}
public void initSheet() {//初始化Excel文件的信息
sheetNum = wb.getNumberOfSheets();
sheets = new HSSFSheet[sheetNum];
for(int i = 0 ; i < sheetNum; i++) {
sheets[i] = wb.getSheetAt(i);
}
}
public void setSheet(int index) {//設(shè)置待操作的工作頁
if(sheets == null || sheetNum <= index) {
throw new SampleException("無法獲取無效的工作頁");
}
sheet = sheets[index];
rowNum = getRowNum();
colNum = getColNum();
}
public int getRowNum() {
if(rowNum != 0)
return rowNum;
if(sheet != null) {
rowNum = getRowNum(sheet);
return rowNum;
} else {
throw new SampleException("無法獲取無效的工作頁的總行數(shù)");
}
}
public int getColNum() {//獲取指定工作頁面的列數(shù)
if(sheet == null) {
throw new SampleException("未指定操作的工作頁");
}
if(colNum != 0)
return colNum;
HSSFRow row = sheet.getRow(0);
this.colNum = getCellNum(row);
return colNum;
}
public int getCellNum(HSSFRow row) {
int first = row.getFirstCellNum();
int last = row.getLastCellNum();
int cellNum = last - first;
return cellNum;
}
public int getRowNum(HSSFSheet sheet) {//獲取總行數(shù)
int first = sheet.getFirstRowNum();
int last = sheet.getLastRowNum() ;
int rowCount = last-first+1;
return rowCount;
}
public void setRow(int index) {//選中指定行數(shù)據(jù)
if(sheet != null) {
if(index > rowNum) {
throw new SampleException("指定獲取的行"+index+"超出了最大行數(shù)"+rowNum);
}
row = sheet.getRow(index);
} else {
throw new SampleException("未指定操作的工作頁");
}
}
public boolean checkFile(String path) {//檢查文件是否有其他程序或者進(jìn)程在使用
boolean result = false;
File file = new File(path);
if(!file.exists()) {
throw new SampleException("指定操作的目標(biāo)文件不存在");
} else {
File nFile = new File(path);
result = file.renameTo(nFile);
}
return result;
}
public String[] getRowValues(int index) {//獲取一行的值
return getCellValues(index, 0);
}
public String[] getCellValues(int index, int col) {
List values = new ArrayList();
if(sheet != null) {
if(index > rowNum) {
throw new SampleException("指定獲取的行"+index+"超出了最大行數(shù)"+rowNum);
}
setRow(index);
if(col == 0)
col = colNum;
for(int i = 0 ; i < col; i++) {
values.add(getCellToString(i));
}
row = null;
return values.toArray(new String[col]);
} else {
throw new SampleException("未指定操作的工作頁");
}
}
public String getCellToString(int i) {//獲取指定單元格的內(nèi)容
if(i > colNum) {
throw new SampleException("請求獲取的單元格不存在");
}
HSSFCell cell = row.getCell(i);
String str = getCellFormatValue(cell);
return str;
}
private String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
// 判斷當(dāng)前Cell的Type
switch (cell.getCellType()) {
// 如果當(dāng)前Cell的Type為NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA:
// 判斷當(dāng)前的cell是否為Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date類型則，轉(zhuǎn)化為Data格式
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
else { // 如果是純數(shù)字；取得當(dāng)前Cell的數(shù)值
cellvalue = String.valueOf(cell.getNumericCellValue());
if(cellvalue.endsWith(".0")) {
cellvalue = cellvalue.replace(".0", "");
}
}
break;
// 如果當(dāng)前Cell的Type為STRING，取得當(dāng)前的Cell字符串
case HSSFCell.CELL_TYPE_STRING:
cellvalue = cell.getRichStringCellValue().getString();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
boolean comment = cell.getBooleanCellValue();
cellvalue = comment?"Y":"N";
break;
// 默認(rèn)的Cell值
default:
cellvalue = "";
}
} else {
cellvalue = "";
}
return cellvalue;
}
public static void main(String[] args) {
String path = "C:\\Users\\Administrator\\Desktop\\test.xls";
ReadExcel03 excel = new ReadExcel03(path);
excel.setSheet(0);
System.out.println("總行數(shù)：" + excel.getRowNum());
System.out.println("總列數(shù)：" + excel.getColNum());
for(int i = 0 ; i < excel.getRowNum() ; i++) {
String[] values = excel.getRowValues(i);
for(int j = 0 ; j < values.length ; j++) {
System.out.print(values[j] + " ");
if(j == values.length-1)
System.out.println();
}
}
}
public void close() {
try {
if(is != null) {
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
@Override
public int getSheetNum() {
if(sheets != null)
return sheets.length;
else
throw new SampleException("無效的Excel文件");
}
@Override
public List getCellValues(int index) {
return null;
}
}
在使用的使用初始化類后要先調(diào)用setSheet方法，不然是無法繼續(xù)操作的
接著是操作Excel07的，其實都大同小異：
package org.gdupt.poiexcel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.gdupt.exceptions.SampleException;
import org.gdupt.interfaces.RExcel;
public class ReadExcel07 implements RExcel{
private InputStream is ;
private XSSFWorkbook wb;
private XSSFSheet[] sheets;
private XSSFSheet sheet;
private XSSFRow row;
private int sheetNum;
private int rowNum;
private int colNum;
public ReadExcel07(String path) {
if(!path.trim().toLowerCase().endsWith(".xlsx")) {
throw new SampleException("不是有效的2007Excel文件");
}
if(checkFile(path)) {
try {
is = new FileInputStream(path);
wb = new XSSFWorkbook(is);
initSheet();
} catch (FileNotFoundException e) {
throw new SampleException("讀取的目標(biāo)文件不存在");
} catch (IOException e) {
throw new SampleException("打開Excel文件失敗");
}
} else {
throw new SampleException("無法使用在其他進(jìn)程或者程序已經(jīng)打開的文件");
}
}
public void initSheet() {
sheetNum = wb.getNumberOfSheets();
sheets = new XSSFSheet[sheetNum];
for(int i = 0 ; i < sheetNum; i++) {
sheets[i] = wb.getSheetAt(i);
}
}
public void setSheet(int index) {
if(sheets == null || sheetNum <= index) {
throw new SampleException("無法獲取無效的工作頁");
}
sheet = sheets[index];
rowNum = getRowNum();
colNum = getColNum();
}
public int getRowNum() {
if(rowNum != 0)
return rowNum;
if(sheet != null) {
rowNum = getRowNum(sheet);
return rowNum;
} else {
throw new SampleException("無法獲取無效的工作頁的總行數(shù)");
}
}
public int getColNum() {//獲取指定工作頁面的列數(shù)
if(sheet == null) {
throw new SampleException("未指定操作的工作頁");
}
if(colNum != 0)
return colNum;
XSSFRow row = sheet.getRow(0);
this.colNum = getCellNum(row);
return colNum;
}
public int getCellNum(XSSFRow row) {
int first = row.getFirstCellNum();
int last = row.getLastCellNum();
int cellNum = last - first;
return cellNum;
}
public int getRowNum(XSSFSheet sheet) {
int first = sheet.getFirstRowNum();
int last = sheet.getLastRowNum() ;
int rowCount = last-first+1;
return rowCount;
}
public void setRow(int index) {
if(sheet != null) {
if(index > rowNum) {
throw new SampleException("指定獲取的行"+index+"超出了最大行數(shù)"+rowNum);
}
row = sheet.getRow(index);
} else {
throw new SampleException("未指定操作的工作頁");
}
}
public boolean checkFile(String path) {
boolean result = false;
File file = new File(path);
if(!file.exists()) {
throw new SampleException("指定操作的目標(biāo)文件不存在");
} else {
File nFile = new File(path);
result = file.renameTo(nFile);
}
return result;
}
public String[] getRowValues(int index) {//獲取一行的值
return getCellValues(index, 0);
}
public String[] getCellValues(int index, int col) {
List values = new ArrayList();
if(sheet != null) {
if(index > rowNum) {
throw new SampleException("指定獲取的行"+index+"超出了最大行數(shù)"+rowNum);
}
setRow(index);
if(col == 0)
col = colNum;
for(int i = 0 ; i < col; i++) {
values.add(getCellToString(i));
}
row = null;
return values.toArray(new String[col]);
} else {
throw new SampleException("未指定操作的工作頁");
}
}
public String getCellToString(int i) {//獲取指定單元格的內(nèi)容
if(i > colNum) {
throw new SampleException("請求獲取的單元格不存在");
}
short index = (short) i;
XSSFCell cell = row.getCell(index);
String str = getCellFormatValue(cell);
return str;
}
private String getCellFormatValue(XSSFCell cell) {
String cellvalue = "";
if (cell != null) {
// 判斷當(dāng)前Cell的Type
switch (cell.getCellType()) {
// 如果當(dāng)前Cell的Type為NUMERIC
case XSSFCell.CELL_TYPE_NUMERIC:
case XSSFCell.CELL_TYPE_FORMULA:
// 判斷當(dāng)前的cell是否為Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date類型則，轉(zhuǎn)化為Data格式
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
else { // 如果是純數(shù)字；取得當(dāng)前Cell的數(shù)值
cellvalue = String.valueOf(cell.getNumericCellValue());
if(cellvalue.endsWith(".0")) {
cellvalue = cellvalue.replace(".0", "");
}
}
break;
// 如果當(dāng)前Cell的Type為STRING，取得當(dāng)前的Cell字符串
case XSSFCell.CELL_TYPE_STRING:
cellvalue = cell.getRichStringCellValue().getString();
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
boolean comment = cell.getBooleanCellValue();
cellvalue = comment?"Y":"N";
break;
// 默認(rèn)的Cell值
default:
cellvalue = "";
}
} else {
cellvalue = "";
}
return cellvalue;
}
public static void main(String[] args) {
String path = "C:\\Users\\Administrator\\Desktop\\test.xlsx";
ReadExcel07 excel = new ReadExcel07(path);
excel.setSheet(0);
System.out.println("總行數(shù)：" + excel.getRowNum());
System.out.println("總列數(shù)：" + excel.getColNum());
for(int i = 0 ; i < excel.getRowNum() ; i++) {
String[] values = excel.getRowValues(i);
for(int j = 0 ; j < values.length ; j++) {
System.out.print(values[j] + " ");
if(j == values.length-1)
System.out.println();
}
}
}
public void close() {
try {
if(is != null) {
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
@Override
public int getSheetNum() {
if(sheets != null)
return sheets.length;
else
throw new SampleException("無效的Excel文件");
}
@Override
public List getCellValues(int index) {
return null;
}
}代碼幾乎一樣，唯一不同的是所使用的對象的區(qū)別，Excel03使用到的對象是HSSF開頭的，而Excel07使用的是XSSF開頭的
這兩個類都有自帶一個main方法可用于測試，這是讀取excel文件的，希望對各位有一定的幫助
二、 接來下將以下jxl的操作，需要下載jxl.jar包，不過目前好像已經(jīng)沒有團(tuán)隊在維護(hù)該jar包了，而且該工具包不支持excel07的操作
package org.gdupt.jxlexcel;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
import java.util.List;
import org.gdupt.interfaces.RExcel;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class ReadExcel implements RExcel{
private Workbook wb;
private Sheet[] sheets;
private Sheet sheet;
private int rowCount;
private int colCount;
public ReadExcel(InputStream is) {
try {
wb = Workbook.getWorkbook(is);
sheets = wb.getSheets();
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public Workbook getWorkbook() {
return wb;
}
public ReadExcel(String filePath) {
File file = new File(filePath);
init(file);
}
public ReadExcel(File file) {
init(file);
}
private void init(File file) {
try {
wb = Workbook.getWorkbook(file);
sheets = wb.getSheets();
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public void setSheet(int index) {
if(index >= sheets.length)
throw new IndexOutOfBoundsException("不存在該頁數(shù)");
sheet = sheets[index];
rowCount = sheet.getRows();
colCount = sheet.getColumns();
}
public int getSheetNum() {
if(sheets == null || sheets.length==0)
throw new NullPointerException("該文件沒有工作頁面");
return sheets.length;
}
public int getRowNum() {
if(sheet == null)
throw new NullPointerException("未設(shè)定使用工作頁面");
if(rowCount <= 0)
rowCount = sheet.getRows();
return rowCount;
}
public int getColNum() {
if(sheet == null)
throw new NullPointerException("未設(shè)定使用工作頁面");
if(colCount <= 0)
colCount = sheet.getColumns();
return colCount;
}
public String[] getRowValues(int index) {
Listvalues = getCellValues(index);
int length = values.size();
return values.toArray(new String[length]);
}
public List getCellValues(int index) {
String[] v = getCellValues(index, 0);
Listvalues = Arrays.asList(v);
return values;
}
public String[] getCellValues(int index, int count) {
if(sheet == null)
throw new NullPointerException("未設(shè)定使用工作頁面");
if(index > getRowNum())
throw new IndexOutOfBoundsException("不存在操作行");
Cell[] cells = sheet.getRow(index);
if(count == 0)
count = cells.length;
String[] values = new String[count];
for(int i = 0 ; i < count ; i++) {
values[i] = cells[i].getContents();
}
return values;
}
public void close(){
wb.close();
wb = null;
}
}
不過相對起來，jxl用于操作excel03就方便多了
