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

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

成都创新互联公司专注于新田企业网站建设,成都响应式网站建设,商城网站定制开发。新田网站建设公司,为新田等地区提供建站服务。全流程按需网站制作,专业设计,全程项目跟踪,成都创新互联公司专业和态度为您提供的服务

程序主体:

  1. /*json头模板*/

  2.     public static final int HEADER_VALUE_TYPE_O = 1;

  3.     /*实例*/

  4.     public static ExcelToJson getExcelToJson() {

  5.         return new ExcelToJson();

  6.     }

  7.     /*读取excel*/

  8.     public JSONArray readExcel(File file, int headerIndex, int headType) {

  9.         List> lists = new ArrayList>();

  10.         if (!fileNameFileter(file)) {

  11.             return null;

  12.         } else {

  13.             try {

  14.                 WorkbookFactory factory = new WorkbookFactory();

  15.                 Workbook workbook = factory.create(file);

  16.                 Sheet sheet = workbook.getSheetAt(0);

  17.                 Row headerRow = getHeaderRow(sheet, headerIndex);

  18.                 FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

  19.                 for (int r = headerIndex + 1; r < sheet.getLastRowNum() + 1; r++) {

  20.                     Row dataRow = sheet.getRow(r);

  21.                     Map map = new HashMap();

  22.                     for (int h = 0; h < dataRow.getLastCellNum(); h++) {

  23.                         String key = getHeaderCellValue(headerRow, h, headType);

  24.                         Object value = getCellValue(dataRow, h, formulaEvaluator);

  25.                         if (!key.equals("") && !key.equals("null") && key != null) {

  26.                             map.put(key, value);

  27.                         }

  28.                     }

  29.                     lists.add(map);

  30.                 }

  31.             } catch (Exception e) {

  32.                 e.printStackTrace();

  33.             }

  34.         }

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

  36.         return jsonArray;

  37.     }

  38.     /*文件过滤,只有表格才可以处理*/

  39.     public boolean fileNameFileter(File file) {

  40.         boolean endsWith = false;

  41.         if (file != null) {

  42.             String fileName = file.getName();

  43.             endsWith = fileName.endsWith(".xls") || fileName.endsWith(".xlsx");

  44.         }

  45.         return endsWith;

  46.     }

  47.     /*获取表的行*/

  48.     public Row getHeaderRow(Sheet sheet, int index) {

  49.         Row headerRow = null;

  50.         if (sheet != null) {

  51.             headerRow = sheet.getRow(index);

  52.         }

  53.         return headerRow;

  54.     }

  55.     /*获取表头的value*/

  56.     public String getHeaderCellValue(Row headerRow, int cellIndex, int type) {

  57.         Cell cell = headerRow.getCell(cellIndex);

  58.         String headerValue = null;

  59.         if (cell != null) {

  60.             if (HEADER_VALUE_TYPE_O == type) {

  61.                 headerValue = cell.getRichStringCellValue().getString();

  62.             }

  63.         }

  64.         return headerValue;

  65.     }

  66.     /*获取单元格的值*/

  67.     public Object getCellValue(Row row, int cellIndex, FormulaEvaluator formulaEvaluator) {

  68.         Cell cell = row.getCell(cellIndex);

  69.         if (cell != null) {

  70.             switch (cell.getCellType()) {

  71.                 //String

  72.                 case Cell.CELL_TYPE_STRING:

  73.                     return cell.getRichStringCellValue().getString();

  74.                 //Number

  75.                 case Cell.CELL_TYPE_NUMERIC:

  76.                     if (DateUtil.isCellDateFormatted(cell)) {

  77.                         return cell.getDateCellValue().getTime();

  78.                     } else {

  79.                         return cell.getNumericCellValue();

  80.                     }

  81.                     //boolean

  82.                 case Cell.CELL_TYPE_BOOLEAN:

  83.                     return cell.getBooleanCellValue();

  84.                 //公式

  85.                 case Cell.CELL_TYPE_FORMULA:

  86.                     return formulaEvaluator.evaluate(cell).getNumberValue();

  87.                 default:

  88.                     return null;

  89.             }

  90.         }

  91.         return null;

  92.     }

测试方法:

  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.         

  2.         

  3.             org.apache.poi

  4.             poi

  5.             3.15

  6.         

  7.         

  8.             org.apache.poi

  9.             poi-ooxml

  10.             3.15

  11.         

  12.         

  13.         

  14.             net.sourceforge.jexcelapi

  15.             jxl

  16.             2.6.12

  17.         

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


网站标题:java如何实现将excel表格数据解析成JSONArray
URL链接:http://pwwzsj.com/article/pdhopi.html