java實現將excel表格資料解析成JSONArray

634828354發表於2018-01-30
JavaExcelJSON

程式主體：

點選(此處)摺疊或開啟

  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>


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30046312/viewspace-2150637/，如需轉載，請註明出處，否則將追究法律責任。

相關文章