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

这篇文章给大家分享的是有关java如何实现将excel表格数据解析成JSONArray的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

程序主体：

1. /*json头模板*/

2.     public static final int HEADER_VALUE_TYPE_O = 1;

4.     /*实例*/

5.     public static ExcelToJson getExcelToJson() {

6.         return new ExcelToJson();

7.     }

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);

33.                 }

34.             } catch (Exception e) {

35.                 e.printStackTrace();

36.             }

37.         }

38.         JSONArray jsonArray = JSONArray.fromObject(lists);

39.         return jsonArray;

40.     }

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.     }

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.     }

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.     }

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();

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.                     }

90.                     //boolean

91.                 case Cell.CELL_TYPE_BOOLEAN:

92.                     return cell.getBooleanCellValue();

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>

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”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！