poi解析excel（含有公式）

  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