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

这篇文章给大家分享的是有关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>

感谢各位的阅读！关于“java如何实现将excel表格数据解析成JSONArray”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！