废话少说直接上代码，记得是poi3.8版本啊。方法入口唯一，自动判断格式，使用接口引用，自动选择执行方法。
方法入口：
public static ArrayList<String[]> explanExcelToList(String fileName) {
ArrayList<String[]> list = new ArrayList<String[]>();
Workbook wb = null;
try {
wb = get2003Workbook(new FileInputStream(fileName));
if (wb == null) {
wb = get2007Workbook(new FileInputStream(fileName));
if (wb == null) {
throw new RuntimeException("无法识别的格式，Unexpected Excel type (" + fileName + ")");
}
}
list = explanExcelToList(wb);
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
package com.order.cc.outboundcall.impExlDate.svc;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.RichTextString;
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;
import org.apache.struts.upload.FormFile;
public class ExcelHelper {
private static Integer SHEET_PAGE_NUM = 0;// 读取sheet页第一页
public static ArrayList<String[]> explanExcelToList(String fileName) {
ArrayList<String[]> list = new ArrayList<String[]>();
Workbook wb = null;
try {
wb = get2003Workbook(new FileInputStream(fileName));
if (wb == null) {
wb = get2007Workbook(new FileInputStream(fileName));
if (wb == null) {
throw new RuntimeException("无法识别的格式，Unexpected Excel type (" + fileName + ")");
}
}
list = explanExcelToList(wb);
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
public static ArrayList<String[]> explanExcelToList(Workbook wb) {
ArrayList<String[]> resList = new ArrayList<String[]>();
if (wb == null) {
return resList;
}
try {
wb.setMissingCellPolicy(Row.RETURN_BLANK_AS_NULL);// 空白设置为null
// 读取第一章表格内容
Sheet sheet = wb.getSheetAt(SHEET_PAGE_NUM);
if (sheet == null) {
return resList;
}
// 循环输出表格中的内容
resList = explantSheet(sheet);
} catch (Exception e) {
e.printStackTrace();
}
return resList;
}
/**
*
* 功能说明 使用接口解析Sheet,支持HSSFSheet 与XSSFSheet格式
*
* @创建人 yxh
* @时间 2016-4-26
* @参数说明 @param list
* @参数说明 @param sheet
* @参数返回说明 void
*/
public static ArrayList<String[]> explantSheet(Sheet sheet) {
ArrayList<String[]> list = new ArrayList<String[]>();
if (sheet == null) {
return list;
}
int rowNum = sheet.getRow(0) != null ? sheet.getRow(0).getPhysicalNumberOfCells() : 0;// 通过表头定义数组的位数，确定每行固定大小
if (rowNum == 0) {
rowNum = sheet.getRow(1) != null ? sheet.getRow(1).getPhysicalNumberOfCells() : 0;// 防止不写表头
}
for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {// 循环行
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
String[] contentArr = new String[rowNum];
for (int j = 0; j < rowNum; j++) {// 循环列
Cell cell = row.getCell(j);
String text = "";
if (cell != null) {
text = formatCell(cell);
}
contentArr[j] = text;
}
list.add(contentArr);
}
return list;
}
/**
*
* 功能说明 格式化表格内容
*
* @创建人 yxh
* @时间 2016-4-26
* @参数说明 @param cell
* @参数说明 @return
* @参数返回说明 String
*/
public static String formatCell(Cell cell) {
DataFormatter _formatter = new DataFormatter();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING: // 2016年4月27日11:19:20 在excel中视为字符串形式
return cell.getRichStringCellValue().getString();
case Cell.CELL_TYPE_NUMERIC:// 数值型
if (DateUtil.isCellDateFormatted(cell)) {// 日期 poi
return dateToString(cell.getDateCellValue());
} else {
return _formatter.formatCellValue(cell);
}
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() == true ? "true" : "false";// boolean
// 转成String
case Cell.CELL_TYPE_ERROR:
return ErrorEval.getText(cell.getErrorCellValue());// 返回错误码
case Cell.CELL_TYPE_FORMULA:// 公式
switch (cell.getCachedFormulaResultType()) {
case Cell.CELL_TYPE_STRING:
RichTextString str = cell.getRichStringCellValue();
if (str != null && str.length() > 0) {
return str.toString();
}
case Cell.CELL_TYPE_NUMERIC:
CellStyle style = cell.getCellStyle();
if (style == null) {
return cell.getNumericCellValue() + "";// double转成String
} else {
return _formatter.formatRawCellContents(cell.getNumericCellValue(), style.getDataFormat(), style.getDataFormatString());
}
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() ? "true" : "false";// boolean
// 转成String
case Cell.CELL_TYPE_ERROR:
return ErrorEval.getText(cell.getErrorCellValue());
}
default:
throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")");
}
}
public static String dateToString(Date date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return sdf.format(date);
}
public static String dateToString(String date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return sdf.format(date);
}
public static void closeFile(FormFile file) {
if (file != null) {
try {
file.destroy();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
public static Workbook get2003Workbook(InputStream is) {
Workbook wb = null;
try {
wb = new HSSFWorkbook(is);
} catch (Exception e) {
return wb;
}
return wb;
}
public static Workbook get2007Workbook(InputStream is) {
Workbook wb = null;
try {
wb = new XSSFWorkbook(is);
} catch (Exception e) {
return wb;
}
return wb;
}
public static boolean isExcel2003(InputStream is) {
try {
new HSSFWorkbook(is);
} catch (Exception e) {
return false;
}
return true;
}
public static boolean isExcel2007(InputStream is) {
try {
new XSSFWorkbook(is);
} catch (Exception e) {
return false;
}
return true;
}
public static void main(String[] args) {
// FormFile file=null;
// explanExcelToList(file);
}
}