Java 操作 Excel (读取Excel2003 2007，Poi实现)

来源：互联网发布：淘宝买家订单数据诈骗编辑：程序博客网时间：2024/06/07 04:05

一. Apache POI 简介（ http://poi.apache.org/）
    使用Java程序读写Microsoft Office，提供了下面这几种类型：
    HSSF－提供读写Microsoft Excel XLS格式档案的功能。
    XSSF－提供读写Microsoft Excel OOXML XLSX格式档案的功能。
    HWPF－提供读写Microsoft Word DOC格式档案的功能。
    HSLF－供读写Microsoft PowerPoint格式档案的功能。
    HDGF－提供读Microsoft Visio格式档案的功能。
    HPBF－提供读Microsoft Publisher格式档案的功能。

二、POI操作Excel
    1. 官方快速帮助：http://poi.apache.org/spreadsheet/quick-guide.html
    2. 导入包：
               dom4j-1.6.1.jar
               junit-3.8.1.jar
               poi-3.8-20120326.jar
               poi-ooxml-3.8-20120326.jar
               poi-ooxml-schemas-3.8-20120326.jar
               xmlbeans-2.3.0.jar

参考：
    1. http://www.blogjava.net/vwpolo/archive/2009/09/16/295243.html
    2. http://hacker-zxf.javaeye.com/blog/746546
    3. http://zmx.javaeye.com/blog/622536
    4. http://canfly2010.javaeye.com/blog/701726

Java代码

package excel.poi.input;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.POITextExtractor;
import org.apache.poi.extractor.ExtractorFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.XmlException;
publicclass ReadExcel {
/**
* 读取office 2003 xls
* @param filePath
*/
@SuppressWarnings({ "unchecked", "deprecation" })
publicvoid loadXls(String filePath){
try {
InputStream input = new FileInputStream("D://test.xls");
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
// Iterate over each row in the sheet
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
System.out.println("Row #" + row.getRowNum());
// Iterate over each cell in the row and print out the cell"s
// content
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
System.out.println("Cell #" + cell.getCellNum());
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
System.out.println(cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
System.out.println(cell.getCellFormula());
break;
default:
System.out.println("unsuported sell type");
break;
}
}
}
} catch (IOException ex) {
ex.printStackTrace();
}
}
/**
* 读取xlsx文本
* @param filePath
*/
publicvoid loadXlsxText(String filePath){
File inputFile = new File("D://test.xlsx");
try {
POITextExtractor extractor = ExtractorFactory.createExtractor(inputFile);
System.out.println(extractor.getText());
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (OpenXML4JException e) {
e.printStackTrace();
} catch (XmlException e) {
e.printStackTrace();
}
}
/**
* 读取office 2007 xlsx
* @param filePath
*/
publicvoid loadXlsx(String filePath){
// 构造 XSSFWorkbook 对象，strPath 传入文件路径
XSSFWorkbook xwb = null;
try {
xwb = new XSSFWorkbook("D://test.xlsx");
} catch (IOException e) {
System.out.println("读取文件出错");
e.printStackTrace();
}
// 读取第一章表格内容
XSSFSheet sheet = xwb.getSheetAt(0);
xwb.getSheetAt(1);
// 定义 row、cell
XSSFRow row;
String cell;
// 循环输出表格中的内容
for (int i = sheet.getFirstRowNum(); i < sheetgetPhysicalNumberOfRowsispan>
row = sheet.getRow(i);
for (int j = row.getFirstCellNum(); j < rowgetPhysicalNumberOfCellsjspan>
// 通过 row.getCell(j).toString() 获取单元格内容，
cell = row.getCell(j).toString();
System.out.print(cell + "/t");
}
System.out.println("");
}
}
publicstaticvoid main(String[] args) {
ReadExcel readExcel =new ReadExcel();
readExcel.loadXlsx("");
}
}

Workbook wb = WorkbookFactory.create(new FileInputStream(FILE_URL));
可以自动判断2003还是2007版的文档格式。

转自：http://blog.csdn.net/yuanfen860913/article/details/5940387

0 0