java实现将excel表格数据解析成JSONArray

程序主体：

点击(此处)折叠或打开

1. /*json头模板*/
   
2.     public static final int HEADER_VALUE_TYPE_O = 1;
   
3. 
   
4.     /*实例*/
   
5.     public static ExcelToJson getExcelToJson() {
   
6.         return new ExcelToJson();
   
7.     }
   
8. 
   
9.     /*读取excel*/
   
10.     public JSONArray readExcel(File file, int headerIndex, int headType) {
    
11.         List<Map<String, Object>> lists = new ArrayList<Map<String, Object>>();
    
12.         if (!fileNameFileter(file)) {
    
13.             return null;
    
14.         } else {
    
15.             try {
    
16.                 WorkbookFactory factory = new WorkbookFactory();
    
17.                 Workbook workbook = factory.create(file);
    
18.                 Sheet sheet = workbook.getSheetAt(0);
    
19.                 Row headerRow = getHeaderRow(sheet, headerIndex);
    
20.                 FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
    
21.                 for (int r = headerIndex + 1; r < sheet.getLastRowNum() + 1; r++) {
    
22.                     Row dataRow = sheet.getRow(r);
    
23.                     Map<String, Object> map = new HashMap<String, Object>();
    
24.                     for (int h = 0; h < dataRow.getLastCellNum(); h++) {
    
25.                         String key = getHeaderCellValue(headerRow, h, headType);
    
26.                         Object value = getCellValue(dataRow, h, formulaEvaluator);
    
27.                         if (!key.equals("") && !key.equals("null") && key != null) {
    
28.                             map.put(key, value);
    
29.                         }
    
30.                     }
    
31.                     lists.add(map);
    
32. 
    
33.                 }
    
34.             } catch (Exception e) {
    
35.                 e.printStackTrace();
    
36.             }
    
37.         }
    
38.         JSONArray jsonArray = JSONArray.fromObject(lists);
    
39.         return jsonArray;
    
40.     }
    
41. 
    
42.     /*文件过滤,只有表格才可以处理*/
    
43.     public boolean fileNameFileter(File file) {
    
44.         boolean endsWith = false;
    
45.         if (file != null) {
    
46.             String fileName = file.getName();
    
47.             endsWith = fileName.endsWith(".xls") || fileName.endsWith(".xlsx");
    
48.         }
    
49.         return endsWith;
    
50.     }
    
51. 
    
52.     /*获取表的行*/
    
53.     public Row getHeaderRow(Sheet sheet, int index) {
    
54.         Row headerRow = null;
    
55.         if (sheet != null) {
    
56.             headerRow = sheet.getRow(index);
    
57.         }
    
58.         return headerRow;
    
59.     }
    
60. 
    
61.     /*获取表头的value*/
    
62.     public String getHeaderCellValue(Row headerRow, int cellIndex, int type) {
    
63.         Cell cell = headerRow.getCell(cellIndex);
    
64.         String headerValue = null;
    
65.         if (cell != null) {
    
66.             if (HEADER_VALUE_TYPE_O == type) {
    
67.                 headerValue = cell.getRichStringCellValue().getString();
    
68.             }
    
69.         }
    
70.         return headerValue;
    
71.     }
    
72. 
    
73.     /*获取单元格的值*/
    
74.     public Object getCellValue(Row row, int cellIndex, FormulaEvaluator formulaEvaluator) {
    
75.         Cell cell = row.getCell(cellIndex);
    
76.         if (cell != null) {
    
77.             switch (cell.getCellType()) {
    
78.                 //String
    
79.                 case Cell.CELL_TYPE_STRING:
    
80.                     return cell.getRichStringCellValue().getString();
    
81. 
    
82.                 //Number
    
83.                 case Cell.CELL_TYPE_NUMERIC:
    
84.                     if (DateUtil.isCellDateFormatted(cell)) {
    
85.                         return cell.getDateCellValue().getTime();
    
86.                     } else {
    
87.                         return cell.getNumericCellValue();
    
88.                     }
    
89. 
    
90.                     //boolean
    
91.                 case Cell.CELL_TYPE_BOOLEAN:
    
92.                     return cell.getBooleanCellValue();
    
93. 
    
94.                 //公式
    
95.                 case Cell.CELL_TYPE_FORMULA:
    
96.                     return formulaEvaluator.evaluate(cell).getNumberValue();
    
97.                 default:
    
98.                     return null;
    
99.             }
    
100.         }
     
101.         return null;
     
102.     }

测试方法：

点击(此处)折叠或打开

1. /*测试入口*/
   
2.     public static void main(String[] args) {
   
3.         File file = new File("C:\\a.xls");
   
4.         ExcelToJson excelToJson = getExcelToJson();
   
5.         JSONArray jsonArray = excelToJson.readExcel(file, 0, 1);
   
6.         System.out.println(jsonArray.toString());
   
7.     }

依赖的jar包：

点击(此处)折叠或打开

1.         <!--POI-->
   
2.         <dependency>
   
3.             <groupId>org.apache.poi</groupId>
   
4.             <artifactId>poi</artifactId>
   
5.             <version>3.15</version>
   
6.         </dependency>
   
7. 
   
8.         <dependency>
   
9.             <groupId>org.apache.poi</groupId>
   
10.             <artifactId>poi-ooxml</artifactId>
    
11.             <version>3.15</version>
    
12.         </dependency>
    
13.         <!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
    
14.         <dependency>
    
15.             <groupId>net.sourceforge.jexcelapi</groupId>
    
16.             <artifactId>jxl</artifactId>
    
17.             <version>2.6.12</version>
    
18.         </dependency>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30046312/viewspace-2150637/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30046312/viewspace-2150637/