1 /** 2 * Jun 25, 2012 3 */ 4 5 import java.io.File; 6 import java.io.FileInputStream; 7 import java.io.IOException; 8 import java.io.InputStream; 9 import java.util.ArrayList; 10 import java.util.List; 11 12 import org.apache.commons.io.FilenameUtils; 13 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 14 import org.apache.poi.ss.usermodel.*; 15 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 16 17 /** 18 * Excel组件 19 * 20 * @author Snowolf 21 * @version 1.0 22 * @since 1.0 23 */ 24 public abstract class ExcelHelper { 25 26 /** 27 * Excel 2003 28 */ 29 private final static String XLS = "xls"; 30 /** 31 * Excel 2007 32 */ 33 private final static String XLSX = "xlsx"; 34 /** 35 * 分隔符 36 */ 37 private final static String SEPARATOR = "|"; 38 39 /** 40 * 由Excel文件的Sheet导出至List 41 * 42 * @param file 43 * @param sheetNum 44 * @return 45 */ 46 public static List<String> exportListFromExcel(File file, int sheetNum) 47 throws IOException { 48 return exportListFromExcel(new FileInputStream(file), 49 FilenameUtils.getExtension(file.getName()), sheetNum); 50 } 51 52 /** 53 * 由Excel流的Sheet导出至List 54 * 55 * @param is 56 * @param extensionName 57 * @param sheetNum 58 * @return 59 * @throws IOException 60 */ 61 public static List<String> exportListFromExcel(InputStream is, 62 String extensionName, int sheetNum) throws IOException { 63 64 Workbook workbook = null; 65 66 if (extensionName.toLowerCase().equals(XLS)) { 67 workbook = new HSSFWorkbook(is); 68 } else if (extensionName.toLowerCase().equals(XLSX)) { 69 workbook = new XSSFWorkbook(is); 70 } 71 72 return exportListFromExcel(workbook, sheetNum); 73 } 74 75 /** 76 * 由指定的Sheet导出至List 77 * 78 * @param workbook 79 * @param sheetNum 80 * @return 81 * @throws IOException 82 */ 83 private static List<String> exportListFromExcel(Workbook workbook, 84 int sheetNum) { 85 86 Sheet sheet = workbook.getSheetAt(sheetNum); 87 88 // 解析公式结果 89 FormulaEvaluator evaluator = workbook.getCreationHelper() 90 .createFormulaEvaluator(); 91 92 List<String> list = new ArrayList<String>(); 93 94 int minRowIx = sheet.getFirstRowNum(); 95 int maxRowIx = sheet.getLastRowNum(); 96 for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) { 97 Row row = sheet.getRow(rowIx); 98 StringBuilder sb = new StringBuilder(); 99 100 short minColIx = row.getFirstCellNum(); 101 short maxColIx = row.getLastCellNum(); 102 for (short colIx = minColIx; colIx <= maxColIx; colIx++) { 103 Cell cell = row.getCell(new Integer(colIx)); 104 CellValue cellValue = evaluator.evaluate(cell); 105 if (cellValue == null) { 106 continue; 107 } 108 // 经过公式解析,最后只存在Boolean、Numeric和String三种数据类型,此外就是Error了 109 // 其余数据类型,根据官方文档,完全可以忽略http://poi.apache.org/spreadsheet/eval.html 110 switch (cellValue.getCellTypeEnum()) { 111 case BOOLEAN: 112 sb.append(SEPARATOR + cellValue.getBooleanValue()); 113 break; 114 case NUMERIC: 115 // 这里的日期类型会被转换为数字类型,需要判别后区分处理 116 if (DateUtil.isCellDateFormatted(cell)) { 117 sb.append(SEPARATOR + cell.getDateCellValue()); 118 } else { 119 sb.append(SEPARATOR + cellValue.getNumberValue()); 120 } 121 break; 122 case STRING: 123 sb.append(SEPARATOR + cellValue.getStringValue()); 124 break; 125 case FORMULA: 126 break; 127 case BLANK: 128 break; 129 case ERROR: 130 break; 131 default: 132 break; 133 } 134 } 135 list.add(sb.toString()); 136 } 137 return list; 138 } 139 }
由于Excel中的数据有日期、公式等等格式,参考http://poi.apache.org/spreadsheet/eval.html做了修改,完全兼容。
当前的Excel,C列是根据A、B相乘计算而来,D列是日期格式:
测试:
1 /** 2 * Jun 25, 2012 3 */ 4 5 import java.io.File; 6 import java.io.IOException; 7 import java.util.List; 8 9 import static org.junit.Assert.*; 10 import org.junit.Test; 11 12 /** 13 * 14 * @author Snowolf 15 * @version 1.0 16 * @since 1.0 17 */ 18 public class ExcelHelperTest { 19 20 @Test 21 public void test() { 22 String path = "d://MavenProject//usingGit//src//main//java//excel.xlsx"; 23 List<String> list = null; 24 try { 25 list = ExcelHelper.exportListFromExcel(new File(path), 0); 26 assertNotNull(list); 27 } catch (IOException e) { 28 fail(); 29 } 30 31 } 32 }
结果:
参考https://snowolf.iteye.com/blog/1569252