package excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
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.HSSFWorkbook;
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 classExcelUtil {privateWorkbook workbook;privateOutputStream os;private String pattern;//日期格式
public voidsetPattern(String pattern) {this.pattern =pattern;
}publicExcelUtil(Workbook workboook) {this.workbook =workboook;
}public ExcelUtil(InputStream is, String version) throws FileNotFoundException, IOException {if ("2003".equals(version)) {
workbook= new HSSFWorkbook(is);
}else{
workbook= new XSSFWorkbook(is);
}
}publicString toString() {return "共有" + getSheetCount() + "個sheet 頁！";
}public String toString(intsheetIx) throws IOException {return "第" + (sheetIx + 1) + "個sheet 頁，名稱：" + getSheetName(sheetIx) + "，共" + getRowCount(sheetIx) + "行！";
}
public staticboolean isExcel(String pathname) {if (pathname == null) {return false;
}return pathname.endsWith(".xls") || pathname.endsWith(".xlsx");
}
public List>read() throws Exception {return read(0, 0, getRowCount(0) - 1);
}
public List> read(intsheetIx) throws Exception {return read(sheetIx, 0, getRowCount(sheetIx) - 1);
}
public List> read(int sheetIx, int start, intend) throws Exception {
Sheet sheet=workbook.getSheetAt(sheetIx);
List> list = new ArrayList>();if (end >getRowCount(sheetIx)) {
end=getRowCount(sheetIx);
}int cols = sheet.getRow(0).getLastCellNum(); //第一行總列數
for (int i = start; i <= end; i++) {
List rowList = new ArrayList();
Row row=sheet.getRow(i);for (int j = 0; j < cols; j++) {if (row == null) {
rowList.add(null);continue;
}
rowList.add(getCellValueToString(row.getCell(j)));
}
list.add(rowList);
}returnlist;
}
public int getRowCount(intsheetIx) {
Sheet sheet=workbook.getSheetAt(sheetIx);if (sheet.getPhysicalNumberOfRows() == 0) {return 0;
}return sheet.getLastRowNum() + 1;
}
public int getColumnCount(int sheetIx, introwIndex) {
Sheet sheet=workbook.getSheetAt(sheetIx);
Row row=sheet.getRow(rowIndex);return row == null ? -1: row.getLastCellNum();
}
privateString getCellValueToString(Cell cell) {
String strCell= "";if (cell == null) {return null;
}switch(cell.getCellType()) {caseCell.CELL_TYPE_BOOLEAN:
strCell=String.valueOf(cell.getBooleanCellValue());break;caseCell.CELL_TYPE_NUMERIC:if(HSSFDateUtil.isCellDateFormatted(cell)) {
Date date=cell.getDateCellValue();if (pattern != null) {
SimpleDateFormat sdf= newSimpleDateFormat(pattern);
strCell=sdf.format(date);
}else{
strCell=date.toString();
}break;
}//不是日期格式，則防止當數字過長時以科學計數法顯示
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
strCell=cell.toString();break;caseCell.CELL_TYPE_STRING:
strCell=cell.getStringCellValue();break;default:break;
}returnstrCell;
}
public String getSheetName(intsheetIx) throws IOException {
Sheet sheet=workbook.getSheetAt(sheetIx);returnsheet.getSheetName();
}
public intgetSheetCount() {returnworkbook.getNumberOfSheets();
}public staticboolean isExcel2003(String pathname) {return pathname.endsWith(".xls");
}public static voidmain(String[] args) throws Exception {
String pathName= "D:/a/e/2.xlsx";
File f= newFile(pathName);
InputStreamin = newFileInputStream(f);
String version= isExcel2003(pathName)?"2003":"2007";
ExcelUtil excelUtil= new ExcelUtil(in, version);//讀取第一個sheet
List> read = excelUtil.read(0);for (int i = 0; i < read.size(); i++) {
List rowList = read.get(i);for(String s : rowList) {
System.out.println(s);
}
System.out.println();
}
}
}