项目总结06：Java Excel文件导入功能HSSFWorkbook（xls）和 XSSFWorkbook （xlsx）

package com.blue.pem.client.util;import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;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.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 com.blue.common.util.PageData;public class ObjectExcelRead { /**
* @param filepath //文件路径
* @param filename //文件名
* @param startrow //开始行号
* @param startcol //开始列号
* @param sheetnum //sheet
* @return list
*/ /*读取xls文件*/
public static List<PageData> readExcelByFileForXls(File file, int startrow, int startcol, int sheetnum) {
List<PageData> varList = new ArrayList<PageData>(); try {
//File target = new File(filepath, filename);
//File target = new File(file);
FileInputStream fi = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(fi);//xslx
HSSFSheet sheet= wb.getSheetAt(sheetnum); //sheet 从0开始 int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号 for (int i = startrow; i < rowNum; i++) { //行循环开始 PageData varpd = new PageData();
HSSFRow row = sheet.getRow(i); //行
int cellNum = row.getLastCellNum(); //每行的最后一个单元格位置 for (int j = startcol; j < cellNum; j++) { //列循环开始 HSSFCell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
case 0:
cellValue = String.valueOf((int) cell.getNumericCellValue());
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
// cellValue = String.valueOf(cell.getDateCellValue());
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
} varpd.put("var"+j, cellValue); }
varList.add(varpd);
} } catch (Exception e) {
System.out.println(e);
} return varList;
} /*读取xlsx文件*/
public static List<PageData> readExcelByFileForXlsx(File file, int startrow, int startcol, int sheetnum) {
List<PageData> varList = new ArrayList<PageData>();
try {
//File target = new File(filepath, filename);
//File target = new File(file);
FileInputStream fi = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fi);//xslx
XSSFSheet sheet= wb.getSheetAt(sheetnum); //sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号 for (int i = startrow; i < rowNum; i++) { //行循环开始 PageData varpd = new PageData();
XSSFRow row = sheet.getRow(i); //行
int cellNum = row.getLastCellNum(); //每行的最后一个单元格位置 for (int j = startcol; j < cellNum; j++) { //列循环开始 XSSFCell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
case 0:
cellValue = String.valueOf((int) cell.getNumericCellValue());
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
// cellValue = String.valueOf(cell.getDateCellValue());
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
} varpd.put("var"+j, cellValue); }
varList.add(varpd);
} } catch (Exception e) {
System.out.println(e);
} return varList;
}
}