使用poi解析excel示例代码
package com.test;
import java.io.FileInputStream;
import java.util.ArrayList;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidOperationException;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
public class ReadExcel {
/**
* 解析指定目录下的Excel文件,并返回二元集合形式的数据
* @param fileName 文件名
* @param path 文件路径
* @return 已解析完成的二元集合数据
*/
public ArrayList> readExcel(String fileName, String path) {
ArrayList> Row = new ArrayList>();
Workbook workBook = null;
try {
// 此处为兼容excel2003与2007
try {
workBook = new XSSFWorkbook(path + "\\" + fileName);
} catch (InvalidOperationException ex) {
workBook = new HSSFWorkbook(new FileInputStream(path + "\\" + fileName));
}
/*
* 开始遍历excel文件:
* 此处嵌套三层for循环,最外层遍历工作薄,第二层遍历行,第三层遍历该行的每个单元格
*/
for (int numSheet = 1; numSheet
Sheet sheet = workBook.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
// 循环列Cell
ArrayList arrCell = new ArrayList();
for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell == null) {
continue;
}
arrCell.add(getValue(cell));
}
Row.add(arrCell);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return Row;
}
/**
* 读取单元格中的值
* @param cell 单元格
* @return 单元格中的字符串
*/
private String getValue(Cell cell) {
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
/*
* 当单元格为数字类型时,还需要判断是否为日期类型
* 因为在Excel中的Date类型也是以Double类型的数字存储的
*/
if (HSSFDateUtil.isCellDateFormatted(cell))
return String.valueOf(new Date(cell.getDateCellValue().getTime()));
return String.valueOf(cell.getNumericCellValue());
} else {
return String.valueOf(cell.getStringCellValue());
}
}
public static void main(String[] args) {
ReadExcel s = new ReadExcel();
//ArrayList> row=s.readExcel("TEST.xlsx","D:\\Program Files\\Java");
ArrayList> row = s.readExcel("test.xls", "C:\\");
System.out.println("size:" + row.size());
for (ArrayList cell : row) {
for (String str : cell) {
System.out.print(str + "\t");
}
System.out.println();
}
}
}