java操作Excel 2003或2007

昵稱11821931 2013-06-07

展開全文

Java 操作 Excel

關(guān)于Java操作Excel文件，最近在網(wǎng)上經(jīng)?？吹接芯W(wǎng)友尋求這方面的資料，在這里我簡(jiǎn)單介紹下我對(duì)這方面的了解

一、讀取Excel文件，現(xiàn)在比較流行的第三方jar包有apache的poi和另一個(gè)jar包jxl

1先見poi，這個(gè)是目前做的最突出的一個(gè)操作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，后面幾個(gè)jar包是為Excel07服務(wù)的，這是由于Excel07的文件存儲(chǔ)結(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è)置待操作的工作頁(yè)

if(sheets == null || sheetNum <= index) {

throw new SampleException("無法獲取無效的工作頁(yè)");

}

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("無法獲取無效的工作頁(yè)的總行數(shù)");

}

public int getColNum() {//獲取指定工作頁(yè)面的列數(shù)

if(sheet == null) {

throw new SampleException("未指定操作的工作頁(yè)");

}

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("未指定操作的工作頁(yè)");

}

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("未指定操作的工作頁(yè)");

}

public String getCellToString(int i) {//獲取指定單元格的內(nèi)容

if(i > colNum) {

throw new SampleException("請(qǐng)求獲取的單元格不存在");

}

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的，其實(shí)都大同小異：

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("無法獲取無效的工作頁(yè)");

}

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("無法獲取無效的工作頁(yè)的總行數(shù)");

}

public int getColNum() {//獲取指定工作頁(yè)面的列數(shù)

if(sheet == null) {

throw new SampleException("未指定操作的工作頁(yè)");

}

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("未指定操作的工作頁(yè)");

}

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("未指定操作的工作頁(yè)");

}

public String getCellToString(int i) {//獲取指定單元格的內(nèi)容

if(i > colNum) {

throw new SampleException("請(qǐng)求獲取的單元格不存在");

}

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;

}

}代碼幾乎一樣，唯一不同的是所使用的對(duì)象的區(qū)別，Excel03使用到的對(duì)象是HSSF開頭的，而Excel07使用的是XSSF開頭的

這兩個(gè)類都有自帶一個(gè)main方法可用于測(cè)試，這是讀取excel文件的，希望對(duì)各位有一定的幫助

二、接來下將以下jxl的操作，需要下載jxl.jar包，不過目前好像已經(jīng)沒有團(tuán)隊(duì)在維護(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("不存在該頁(yè)數(shù)");

sheet = sheets[index];

rowCount = sheet.getRows();

colCount = sheet.getColumns();

}

public int getSheetNum() {

if(sheets == null || sheets.length==0)

throw new NullPointerException("該文件沒有工作頁(yè)面");

return sheets.length;

}

public int getRowNum() {

if(sheet == null)

throw new NullPointerException("未設(shè)定使用工作頁(yè)面");

if(rowCount <= 0)

rowCount = sheet.getRows();

return rowCount;

}

public int getColNum() {

if(sheet == null)

throw new NullPointerException("未設(shè)定使用工作頁(yè)面");

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è)定使用工作頁(yè)面");

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;

}

不過相對(duì)起來，jxl用于操作excel03就方便多了

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自：昵稱11821931 > 《操作excel》

舉報(bào)/認(rèn)領(lǐng)