java如何實現(xiàn)將excel表格數(shù)據(jù)解析成JSONArray

這篇文章給大家分享的是有關(guān)java如何實現(xiàn)將excel表格數(shù)據(jù)解析成JSONArray的內(nèi)容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

程序主體：

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>

感謝各位的閱讀！關(guān)于“java如何實現(xiàn)將excel表格數(shù)據(jù)解析成JSONArray”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！