package com.util.poi;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;import org.apache.commons.io.FilenameUtils;import org.apache.commons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;/** * Excel組件 * * @author taller * @version 1.0 * @since 1.0 */ public class ExcelHelper { /** * Excel 2003 */ PRivate final static String XLS = "xls"; /** * Excel 2007 */ private final static String XLSX = "xlsx"; /** * 分隔符 */ private final static String SEPARATOR = ":"; /** * 由Excel文件的Sheet導出至List * * @param file * @param sheetNum * @return */ public static List<String> exportListFromExcel(File file, int sheetNum,int cellNum) throws IOException { return exportListFromExcel(new FileInputStream(file), FilenameUtils.getExtension(file.getName()), sheetNum,cellNum); } /** * 由Excel流的Sheet導出至List * * @param is * @param extensionName * @param sheetNum * @return * @throws IOException */ public static List<String> exportListFromExcel(InputStream is, String extensionName, int sheetNum,int cellNum) throws IOException { Workbook workbook = null; if (extensionName.toLowerCase().equals(XLS)) { workbook = new HSSFWorkbook(is); } else if (extensionName.toLowerCase().equals(XLSX)) { workbook = new XSSFWorkbook(is); } return exportListFromExcel(workbook, sheetNum,cellNum); } /** * 由指定的Sheet導出至List * * @param workbook * @param sheetNum * @return * @throws IOException */ private static List<String> exportListFromExcel(Workbook workbook, int sheetNum,int cellNum) { Sheet childSheet = workbook.getSheetAt(sheetNum); List<String> list = new ArrayList<String>(); for (int j = 0; j <= childSheet.getLastRowNum(); j++) { HSSFRow row = (HSSFRow) childSheet.getRow(j); StringBuilder sb = new StringBuilder(); if (null != row) { for (int k = 0; k < row.getLastCellNum(); k++) { HSSFCell cell = row.getCell(k); if (null != cell) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: // 數字 cell.setCellType(HSSFCell.CELL_TYPE_STRING); sb.append(getContent(cell.getStringCellValue()) + ""+SEPARATOR); break; case HSSFCell.CELL_TYPE_STRING: // 字符串 if(StringUtils.isNotEmpty((cell.getStringCellValue()) )) sb.append(getContent(cell.getStringCellValue()) + "" +SEPARATOR); else sb.append(" "+SEPARATOR); break; case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean sb.append(cell.getBooleanCellValue() + ""+SEPARATOR); break; case HSSFCell.CELL_TYPE_FORMULA: // 公式 sb.append(cell.getCellFormula() + ""+SEPARATOR); break; case HSSFCell.CELL_TYPE_BLANK: // 空值 sb.append(" "+SEPARATOR); break; case HSSFCell.CELL_TYPE_ERROR: // 故障 sb.append(" "+SEPARATOR); break; default: sb.append("未知類型 "); break; } } else { sb.append(" "+SEPARATOR); } } } if( row.getLastCellNum()<cellNum){ for(int i =0;i<cellNum-row.getLastCellNum();i++){ sb.append(" "+SEPARATOR); } } list.add(sb.toString()); } return list; } public static String getContent(String string){ if(string==null){ return " "; } string=string.replaceAll("http://s+", ""); string=string.replaceAll(" ",""); string=string.replaceAll("'",""); string=string.replaceAll("/"",""); string=string.replaceAll(";",""); string=string.replaceAll(":",""); string=string.trim(); return string; }}