废话少说直接上代码,记得是poi3.8版本啊。方法入口唯一,自动判断格式,使用接口引用,自动选择执行方法。
方法入口:
public static ArrayList<String[]> explanExcelToList(String fileName) {
ArrayList<String[]> list = new ArrayList<String[]>();
Workbook wb = null;
try {
wb = getWorkbook(new FileInputStream(fileName));
if (wb == null) {
wb = getWorkbook(new FileInputStream(fileName));
if (wb == null) {
throw new RuntimeException("无法识别的格式,Unexpected Excel type (" + fileName + ")");
}
}
list = explanExcelToList(wb);
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
package .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 = getWorkbook(new FileInputStream(fileName));if (wb == null) {wb = getWorkbook(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* @时间 -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* @时间 -4-26* @参数说明 @param cell* @参数说明 @return* @参数返回说明 String*/public static String formatCell(Cell cell) {DataFormatter _formatter = new DataFormatter();switch (cell.getCellType()) {case Cell.CELL_TYPE_STRING: // 4月27日11:19:20 在excel中视为字符串形式return cell.getRichStringCellValue().getString();case Cell.CELL_TYPE_NUMERIC:// 数值型if (DateUtil.isCellDateFormatted(cell)) {// 日期 poireturn dateToString(cell.getDateCellValue());} else {return _formatter.formatCellValue(cell);}case Cell.CELL_TYPE_BOOLEAN:return cell.getBooleanCellValue() == true ? "true" : "false";// boolean// 转成Stringcase 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// 转成Stringcase 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 getWorkbook(InputStream is) {Workbook wb = null;try {wb = new HSSFWorkbook(is);} catch (Exception e) {return wb;}return wb;}public static Workbook getWorkbook(InputStream is) {Workbook wb = null;try {wb = new XSSFWorkbook(is);} catch (Exception e) {return wb;}return wb;}public static boolean isExcel(InputStream is) {try {new HSSFWorkbook(is);} catch (Exception e) {return false;}return true;}public static boolean isExcel(InputStream is) {try {new XSSFWorkbook(is);} catch (Exception e) {return false;}return true;}public static void main(String[] args) {// FormFile file=null;// explanExcelToList(file);}}