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

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

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

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

JSON转换:org.json.jar

测试类:  importFile.java:

[java] view plain copy

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

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

EXCELBean.java:

[java] view plain copy

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


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