java 读取excel文件转换成json格式

2022-04-06 14:44:40

java 读取excel文件转换成json格式

java 读取excel文件转换成json格式

需要读取excel数据转换成json数据，写了个测试功能，转换正常：

JSON转换：org.json.jar

测试类：  importFile.java:

[java] view plain copy

  1. package com.siemens.util;
  3. import java.util.ArrayList;
  4. import java.util.List;
  5. import org.json.JSONException;
  6. import org.json.JSONObject;
  7. import org.apache.poi.ss.usermodel.Row;
  8. import org.apache.poi.ss.usermodel.Sheet;
  9. import org.apache.poi.ss.usermodel.Workbook;
  11. //import com.siemens.entity.master;
  12. //import com.siemens.service.masterService;
  13. //import com.siemens.serviceImpl.masterServiceImpl;
  14. //import com.siemens.serviceImpl.webServiceImpl;
  16. public class importFile {
  17.     public static void main(String[] args) throws JSONException{
  18. //      master masters = new master();
  19. //      ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
  20. //      masterService ms = (masterService)ac.getBean("masterservice");
  21.          Workbook wb =null;
  22.             Sheet sheet = null;
  23.             Row row = null;
  24.             String cellData = null;
  25.             //文件路径，
  26.             String filePath = "f:/haoxy/Worktable222.xls";
  28.             wb =EXCELBean.readExcel(filePath);
  30.             if(wb != null){
  31.                 //用来存放表中数据
  32.                 List<JSONObject> listMap = new ArrayList<JSONObject>();
  33.                 //获取第一个sheet
  34.                 sheet = wb.getSheetAt(0);
  35.                 //获取最大行数
  36.                 int rownum = sheet.getPhysicalNumberOfRows();
  37.                 //获取第一行
  38.                 row = sheet.getRow(0);
  39.                 //获取最大列数
  40.                 int colnum = row.getPhysicalNumberOfCells();
  41.                 //这里创建json对象，实测用map的话，json数据会有问题 
  42.                 JSONObject jsonMap = new JSONObject();
  43.                 //循环行
  44.                 for (int i = 1; i < rownum; i++) {
  45.                     row = sheet.getRow(i);
  46.                     if(row !=null){
  47.                         //创建list对象接收读出的excel数据
  48.                         List<String> list = new ArrayList<String>();
  49.                         //循环列
  50.                         for (int j=0;j<colnum;j++){
  51.                             cellData = (String) EXCELBean.getCellFormatValue(row.getCell(j));
  52.                             list.add(cellData);
  53.                         }
  54.                         //System.out.println(list.get(59));
  56.                             //下面具体是本人对数据按需求进行格式处理     ---创建json对象会报异常，捕捉一下。
  57.                             JSONObject jsonObject2 = new JSONObject();
  58.                                 jsonObject2.put("skvDorCode",list.get(0));
  59.                                 jsonObject2.put("description", list.get(1));
  60.                                 jsonObject2.put("discipline", list.get(2));
  61.                                 jsonObject2.put("prefabricatedSkids", list.get(3));
  62.                                 jsonObject2.put("onRack", list.get(4));
  63.                                 jsonObject2.put("offRack", list.get(5));
  64.                                 jsonObject2.put("yard", list.get(6));
  65.                                 jsonObject2.put("hsrg", list.get(7));
  67.                                 JSONObject jsonPptData = new JSONObject();
  68.                                 jsonPptData.put("SC FF",list.get(8));
  69.                                 jsonPptData.put("CC SS CT",list.get(9));
  70.                                 jsonPptData.put("CC SS OCC",list.get(10));
  71.                                 jsonPptData.put("CC SS ACC",list.get(11));
  72.                                 jsonPptData.put("CC MS CT",list.get(12));
  73.                                 jsonPptData.put("CC MS OCC",list.get(13));
  74.                                 jsonPptData.put("CC MS ACC",list.get(14));
  76.                                 //turnkey
  77.                                 JSONObject jsonTurnkey = new JSONObject();
  78.                                 jsonTurnkey.put("plantDesign", list.get(26));
  79.                                 jsonTurnkey.put("basicDesign", list.get(27));
  80.                                 jsonTurnkey.put("detailDesign", list.get(28));
  81.                                 jsonTurnkey.put("supplier", list.get(29));
  82.                                 jsonTurnkey.put("errection", list.get(30));
  83.                                 jsonTurnkey.put("commissioning", list.get(31));
  84.                                 jsonTurnkey.put("blackBox", list.get(32));
  85.                                 jsonTurnkey.put("optionalScope", list.get(33));
  86.                                 jsonTurnkey.put("remark", list.get(34));
  87.                                 jsonTurnkey.put("internalRemark", list.get(35));
  88.                                 jsonTurnkey.put("revision", list.get(36));
  90.                                 //PowerCore
  91.                                 JSONObject jsonPowerCore = new JSONObject();
  92.                                 jsonPowerCore.put("plantDesign", list.get(37));
  93.                                 jsonPowerCore.put("basicDesign", list.get(38));
  94.                                 jsonPowerCore.put("detailDesign", list.get(39));
  95.                                 jsonPowerCore.put("supplier", list.get(40));
  96.                                 jsonPowerCore.put("errection", list.get(41));
  97.                                 jsonPowerCore.put("commissioning", list.get(42));
  98.                                 jsonPowerCore.put("blackBox", list.get(43));
  99.                                 jsonPowerCore.put("optionalScope", list.get(44));
  100.                                 jsonPowerCore.put("remark", list.get(45));
  101.                                 jsonPowerCore.put("internalRemark", list.get(46));
  102.                                 jsonPowerCore.put("revision", list.get(47));
  104.                                 //PowerIsland
  105.                                 JSONObject jsonPowerIsland = new JSONObject();
  106.                                 jsonPowerIsland.put("plantDesign", list.get(48));
  107.                                 jsonPowerIsland.put("basicDesign", list.get(49));
  108.                                 jsonPowerIsland.put("detailDesign", list.get(50));
  109.                                 jsonPowerIsland.put("supplier", list.get(51));
  110.                                 jsonPowerIsland.put("errection", list.get(52));
  111.                                 jsonPowerIsland.put("commissioning", list.get(53));
  112.                                 jsonPowerIsland.put("blackBox", list.get(54));
  113.                                 jsonPowerIsland.put("optionalScope", list.get(55));
  114.                                 jsonPowerIsland.put("remark", list.get(56));
  115.                                 jsonPowerIsland.put("internalRemark", list.get(57));
  116.                                 jsonPowerIsland.put("revision", list.get(58));
  118.                                 //创建jsonBmt对象，进一步把以上对象嵌套
  119.                                 JSONObject jsonBmt = new JSONObject();
  121.                                 jsonBmt.put("Turnkey", jsonTurnkey);
  122.                                 jsonBmt.put("PowerCore", jsonPowerCore);
  123.                                 jsonBmt.put("PowerIsland", jsonPowerIsland);
  125.                                 //把以上几个嵌套入第一层对象中
  126.                                 jsonObject2.put("powerPlantTypes",jsonPptData);
  127.                                 jsonObject2.put("businessMixTypes",jsonBmt);
  128.                                 jsonObject2.put("treeDepth",Integer.parseInt(list.get(59).substring(0,list.get(59).indexOf('.'))));
  129.                                 if(Integer.parseInt(list.get(59).substring(0,list.get(59).indexOf('.')))<=2){
  130.                                     List<String> list3 = new ArrayList<String>();
  131.                                     list3.add("non-empty-placeholder");
  132.                                     jsonObject2.put("children",list3);
  133.                                 }
  134.                                 listMap.add(jsonObject2);
  135.                     }else{
  136.                         break;
  137.                     }
  138.                 }// end for row
  139.                         //最外层加个key-gridData
  140.                         jsonMap.put("gridData", listMap);
  141.                         System.out.println(jsonMap);
  142.             }
  143.     }
  144. }

读取excel 工具类，看到网友的读取方法引用一下：

EXCELBean.java:

[java] view plain copy

  1. package com.siemens.util;
  3. import java.io.FileInputStream;
  4. import java.io.FileNotFoundException;
  5. import java.io.IOException;
  6. import java.io.InputStream;
  8. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  9. import org.apache.poi.ss.usermodel.Cell;
  10. import org.apache.poi.ss.usermodel.DateUtil;
  11. import org.apache.poi.ss.usermodel.Workbook;
  12. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  14. public class EXCELBean {
  15.     //读取excel
  16.     public static Workbook readExcel(String filePath){
  17.         Workbook wb = null;
  18.         if(filePath==null){
  19.             return null;
  20.         }
  21.         String extString = filePath.substring(filePath.lastIndexOf("."));
  22.         InputStream is = null;
  23.         try {
  24.             is = new FileInputStream(filePath);
  25.             if(".xls".equals(extString)){
  26.                 return wb = new HSSFWorkbook(is);
  27.             }else if(".xlsx".equals(extString)){
  28.                 return wb = new XSSFWorkbook(is);
  29.             }else{
  30.                 return wb = null;
  31.             }
  33.         } catch (FileNotFoundException e) {
  34.             e.printStackTrace();
  35.         } catch (IOException e) {
  36.             e.printStackTrace();
  37.         }
  38.         return wb;
  39.     }
  40.     public static Object getCellFormatValue(Cell cell){
  41.         Object cellValue = null;
  42.         if(cell!=null){
  43.             //判断cell类型
  44.             switch(cell.getCellType()){
  45.             case Cell.CELL_TYPE_NUMERIC:{
  46.                 cellValue = String.valueOf(cell.getNumericCellValue());
  47.                 break;
  48.             }
  49.             case Cell.CELL_TYPE_FORMULA:{
  50.                 //判断cell是否为日期格式
  51.                 if(DateUtil.isCellDateFormatted(cell)){
  52.                     //转换为日期格式YYYY-mm-dd
  53.                     cellValue = cell.getDateCellValue();
  54.                 }else{
  55.                     //数字
  56.                     cellValue = String.valueOf(cell.getNumericCellValue());
  57.                 }
  58.                 break;
  59.             }
  60.             case Cell.CELL_TYPE_STRING:{
  61.                 cellValue = cell.getRichStringCellValue().getString();
  62.                 break;
  63.             }
  64.             default:
  65.                 cellValue = "";
  66.             }
  67.         }else{
  68.             cellValue = "";
  69.         }
  70.         return cellValue;
  71.     }
  73. }
上一篇：python3 编写超简单的重复文件扫描工具


下一篇：在Linux上如何将文本文件转换成PDF格式