excel2003 java_java操作Excel 2003或2007

Java 操作 Excel

关于Java操作Excel文件，最近在网上经常看到有网友寻求这方面的资料，在这里我简单介绍下我对这方面的了解

一、 读取Excel文件，现在比较流行的第三方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服务的，这是由于Excel07的文件存储结构导致的

以下是我封装好的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("读取的目标文件不存在");

} catch (IOException e) {

throw new SampleException("打开Excel文件失败");

}

} else {

throw new SampleException("无法使用在其他进程或者程序已经打开的文件");

}

}

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) {//设置待操作的工作页

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("无法获取无效的工作页的总行数");

}

}

public int getColNum() {//获取指定工作页面的列数

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) {//获取总行数

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+"超出了最大行数"+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("指定操作的目标文件不存在");

} 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+"超出了最大行数"+rowNum);

}

setRow(index);

if(col == 0)

col = colNum;

for(int i = 0 ; i

values.add(getCellToString(i));

}

row = null;

return values.toArray(new String[col]);

} else {

throw new SampleException("未指定操作的工作页");

}

}

public String getCellToString(int 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) {

// 判断当前Cell的Type

switch (cell.getCellType()) {

// 如果当前Cell的Type为NUMERIC

case HSSFCell.CELL_TYPE_NUMERIC:

case HSSFCell.CELL_TYPE_FORMULA:

// 判断当前的cell是否为Date

if (HSSFDateUtil.isCellDateFormatted(cell)) {

// 如果是Date类型则，转化为Data格式

Date date = cell.getDateCellValue();

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

cellvalue = sdf.format(date);

}

else { // 如果是纯数字；取得当前Cell的数值

cellvalue = String.valueOf(cell.getNumericCellValue());

if(cellvalue.endsWith(".0")) {

cellvalue = cellvalue.replace(".0", "");

}

}

break;

// 如果当前Cell的Type为STRING，取得当前的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;

// 默认的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("总行数：" + excel.getRowNum());

System.out.println("总列数：" + 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;

}

}

在使用的使用初始化类后要先调用setSheet方法，不然是无法继续操作的

接着是操作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("读取的目标文件不存在");

} catch (IOException e) {

throw new SampleException("打开Excel文件失败");

}

} else {

throw new SampleException("无法使用在其他进程或者程序已经打开的文件");

}

}

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("无法获取无效的工作页的总行数");

}

}

public int getColNum() {//获取指定工作页面的列数

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+"超出了最大行数"+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("指定操作的目标文件不存在");

} 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+"超出了最大行数"+rowNum);

}

setRow(index);

if(col == 0)

col = colNum;

for(int i = 0 ; i

values.add(getCellToString(i));

}

row = null;

return values.toArray(new String[col]);

} else {

throw new SampleException("未指定操作的工作页");

}

}

public String getCellToString(int 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) {

// 判断当前Cell的Type

switch (cell.getCellType()) {

// 如果当前Cell的Type为NUMERIC

case XSSFCell.CELL_TYPE_NUMERIC:

case XSSFCell.CELL_TYPE_FORMULA:

// 判断当前的cell是否为Date

if (HSSFDateUtil.isCellDateFormatted(cell)) {

// 如果是Date类型则，转化为Data格式

Date date = cell.getDateCellValue();

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

cellvalue = sdf.format(date);

}

else { // 如果是纯数字；取得当前Cell的数值

cellvalue = String.valueOf(cell.getNumericCellValue());

if(cellvalue.endsWith(".0")) {

cellvalue = cellvalue.replace(".0", "");

}

}

break;

// 如果当前Cell的Type为STRING，取得当前的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;

// 默认的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("总行数：" + excel.getRowNum());

System.out.println("总列数：" + 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;

}

}代码几乎一样，唯一不同的是所使用的对象的区别，Excel03使用到的对象是HSSF开头的，而Excel07使用的是XSSF开头的

这两个类都有自带一个main方法可用于测试，这是读取excel文件的，希望对各位有一定的帮助

二、 接来下将以下jxl的操作，需要下载jxl.jar包，不过目前好像已经没有团队在维护该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("不存在该页数");

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("未设定使用工作页面");

if(rowCount <= 0)

rowCount = sheet.getRows();

return rowCount;

}

public int getColNum() {

if(sheet == null)

throw new NullPointerException("未设定使用工作页面");

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("未设定使用工作页面");

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就方便多了