java导入Excel

用Excel作为数据源，通过Java Web进行导入，需要POI的jar。
apachepoi（org.apache.poi 3.8） 可以支持公式、日期等格式！
不说废话，上代码：


Java代码 复制代码 收藏代码
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.Cell;
15.import org.apache.poi.ss.usermodel.CellValue;
16.import org.apache.poi.ss.usermodel.FormulaEvaluator;
17.import org.apache.poi.ss.usermodel.Row;
18.import org.apache.poi.ss.usermodel.Sheet;
19.import org.apache.poi.ss.usermodel.Workbook;
20.import org.apache.poi.xssf.usermodel.XSSFWorkbook;
21.
22./**
23. * Excel组件
24. *
25. * @author Snowolf
26. * @version 1.0
27. * @since 1.0
28. */
29.public abstract class ExcelHelper {
30.
31. /**
32. * Excel 2003
33. */
34. private final static String XLS = "xls";
35. /**
36. * Excel 2007
37. */
38. private final static String XLSX = "xlsx";
39. /**
40. * 分隔符
41. */
42. private final static String SEPARATOR = "|";
43.
44. /**
45. * 由Excel文件的Sheet导出至List
46. *
47. * @param file
48. * @param sheetNum
49. * @return
50. */
51. public static List<String> exportListFromExcel(File file, int sheetNum)
52. throws IOException {
53. return exportListFromExcel(new FileInputStream(file),
54. FilenameUtils.getExtension(file.getName()), sheetNum);
55. }
56.
57. /**
58. * 由Excel流的Sheet导出至List
59. *
60. * @param is
61. * @param extensionName
62. * @param sheetNum
63. * @return
64. * @throws IOException
65. */
66. public static List<String> exportListFromExcel(InputStream is,
67. String extensionName, int sheetNum) throws IOException {
68.
69. Workbook workbook = null;
70.
71. if (extensionName.toLowerCase().equals(XLS)) {
72. workbook = new HSSFWorkbook(is);
73. } else if (extensionName.toLowerCase().equals(XLSX)) {
74. workbook = new XSSFWorkbook(is);
75. }
76.
77. return exportListFromExcel(workbook, sheetNum);
78. }
79.
80. /**
81. * 由指定的Sheet导出至List
82. *
83. * @param workbook
84. * @param sheetNum
85. * @return
86. * @throws IOException
87. */
88. private static List<String> exportListFromExcel(Workbook workbook,
89. int sheetNum) {
90.
91. Sheet sheet = workbook.getSheetAt(sheetNum);
92.
93. // 解析公式结果
94. FormulaEvaluator evaluator = workbook.getCreationHelper()
95. .createFormulaEvaluator();
96.
97. List<String> list = new ArrayList<String>();
98.
99. int minRowIx = sheet.getFirstRowNum();
100. int maxRowIx = sheet.getLastRowNum();
101. for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {
102. Row row = sheet.getRow(rowIx);
103. StringBuilder sb = new StringBuilder();
104.
105. short minColIx = row.getFirstCellNum();
106. short maxColIx = row.getLastCellNum();
107. for (short colIx = minColIx; colIx <= maxColIx; colIx++) {
108. Cell cell = row.getCell(new Integer(colIx));
109. CellValue cellValue = evaluator.evaluate(cell);
110. if (cellValue == null) {
111. continue;
112. }
113. // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了
114. // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html
115. switch (cellValue.getCellType()) {
116. case Cell.CELL_TYPE_BOOLEAN:
117. sb.append(SEPARATOR + cellValue.getBooleanValue());
118. break;
119. case Cell.CELL_TYPE_NUMERIC:
120. // 这里的日期类型会被转换为数字类型，需要判别后区分处理
121. if (DateUtil.isCellDateFormatted(cell)) {
122. sb.append(SEPARATOR + cell.getDateCellValue());
123. } else {
124. sb.append(SEPARATOR + cellValue.getNumberValue());
125. }
126. break;
127. case Cell.CELL_TYPE_STRING:
128. sb.append(SEPARATOR + cellValue.getStringValue());
129. break;
130. case Cell.CELL_TYPE_FORMULA:
131. break;
132. case Cell.CELL_TYPE_BLANK:
133. break;
134. case Cell.CELL_TYPE_ERROR:
135. break;
136. default:
137. break;
138. }
139. }
140. list.add(sb.toString());
141. }
142. return list;
143. }
144.}


由于Excel中的数据有日期、公式等等格式，参考http://poi.apache.org/spreadsheet/eval.html做了修改，完全兼容。

当前的Excel，C列是根据A、B相乘计算而来，D列是日期格式：



测试下：


Java代码 复制代码 收藏代码
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 = "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.}


就是这样了