java 读取excel文件转换成json格式的实例代码

package com.siemens.util; import java.util.ArrayList; import java.util.List; import org.json.JSONException; import org.json.JSONObject; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; //import com.siemens.entity.master; //import com.siemens.service.masterService; //import com.siemens.serviceImpl.masterServiceImpl; //import com.siemens.serviceImpl.webServiceImpl; public class importFile {   public static void main(String[] args) throws JSONException{ //   master masters = new master(); //   ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml"); //   masterService ms = (masterService)ac.getBean("masterservice");      Workbook wb =null;       Sheet sheet = null;       Row row = null;       String cellData = null;       //文件路径，       String filePath = "f:/haoxy/Worktable222.xls";       wb =EXCELBean.readExcel(filePath);       if(wb != null){         //用来存放表中数据         List<JSONObject> listMap = new ArrayList<JSONObject>();         //获取第一个sheet         sheet = wb.getSheetAt(0);         //获取最大行数         int rownum = sheet.getPhysicalNumberOfRows();         //获取第一行         row = sheet.getRow(0);         //获取最大列数         int colnum = row.getPhysicalNumberOfCells();         //这里创建json对象，实测用map的话，json数据会有问题          JSONObject jsonMap = new JSONObject();         //循环行         for (int i = 1; i < rownum; i++) {           row = sheet.getRow(i);           if(row !=null){             //创建list对象接收读出的excel数据             List<String> list = new ArrayList<String>();             //循环列             for (int j=0;j<colnum;j++){               cellData = (String) EXCELBean.getCellFormatValue(row.getCell(j));               list.add(cellData);             }             //System.out.println(list.get(59));               //下面具体是本人对数据按需求进行格式处理   ---创建json对象会报异常，捕捉一下。               JSONObject jsonObject2 = new JSONObject();                 jsonObject2.put("skvDorCode",list.get(0));                 jsonObject2.put("description", list.get(1));                 jsonObject2.put("discipline", list.get(2));                 jsonObject2.put("prefabricatedSkids", list.get(3));                 jsonObject2.put("onRack", list.get(4));                 jsonObject2.put("offRack", list.get(5));                 jsonObject2.put("yard", list.get(6));                  jsonObject2.put("hsrg", list.get(7));                 JSONObject jsonPptData = new JSONObject();                 jsonPptData.put("SC FF",list.get(8));                 jsonPptData.put("CC SS CT",list.get(9));                 jsonPptData.put("CC SS OCC",list.get(10));                 jsonPptData.put("CC SS ACC",list.get(11));                 jsonPptData.put("CC MS CT",list.get(12));                 jsonPptData.put("CC MS OCC",list.get(13));                 jsonPptData.put("CC MS ACC",list.get(14));                 //turnkey                 JSONObject jsonTurnkey = new JSONObject();                 jsonTurnkey.put("plantDesign", list.get(26));                 jsonTurnkey.put("basicDesign", list.get(27));                 jsonTurnkey.put("detailDesign", list.get(28));                 jsonTurnkey.put("supplier", list.get(29));                 jsonTurnkey.put("errection", list.get(30));                 jsonTurnkey.put("commissioning", list.get(31));                 jsonTurnkey.put("blackBox", list.get(32));                 jsonTurnkey.put("optionalScope", list.get(33));                 jsonTurnkey.put("remark", list.get(34));                 jsonTurnkey.put("internalRemark", list.get(35));                 jsonTurnkey.put("revision", list.get(36));                 //PowerCore                 JSONObject jsonPowerCore = new JSONObject();                 jsonPowerCore.put("plantDesign", list.get(37));                 jsonPowerCore.put("basicDesign", list.get(38));                 jsonPowerCore.put("detailDesign", list.get(39));                 jsonPowerCore.put("supplier", list.get(40));                 jsonPowerCore.put("errection", list.get(41));                 jsonPowerCore.put("commissioning", list.get(42));                 jsonPowerCore.put("blackBox", list.get(43));                 jsonPowerCore.put("optionalScope", list.get(44));                 jsonPowerCore.put("remark", list.get(45));                 jsonPowerCore.put("internalRemark", list.get(46));                 jsonPowerCore.put("revision", list.get(47));                 //PowerIsland                 JSONObject jsonPowerIsland = new JSONObject();                 jsonPowerIsland.put("plantDesign", list.get(48));                 jsonPowerIsland.put("basicDesign", list.get(49));                 jsonPowerIsland.put("detailDesign", list.get(50));                 jsonPowerIsland.put("supplier", list.get(51));                 jsonPowerIsland.put("errection", list.get(52));                 jsonPowerIsland.put("commissioning", list.get(53));                 jsonPowerIsland.put("blackBox", list.get(54));                 jsonPowerIsland.put("optionalScope", list.get(55));                 jsonPowerIsland.put("remark", list.get(56));                 jsonPowerIsland.put("internalRemark", list.get(57));                 jsonPowerIsland.put("revision", list.get(58));                 //创建jsonBmt对象，进一步把以上对象嵌套                 JSONObject jsonBmt = new JSONObject();                 jsonBmt.put("Turnkey", jsonTurnkey);                 jsonBmt.put("PowerCore", jsonPowerCore);                 jsonBmt.put("PowerIsland", jsonPowerIsland);                 //把以上几个嵌套入第一层对象中                 jsonObject2.put("powerPlantTypes",jsonPptData);                 jsonObject2.put("businessMixTypes",jsonBmt);                 jsonObject2.put("treeDepth",Integer.parseInt(list.get(59).substring(0,list.get(59).indexOf('.'))));                 if(Integer.parseInt(list.get(59).substring(0,list.get(59).indexOf('.')))<=2){                   List<String> list3 = new ArrayList<String>();                   list3.add("non-empty-placeholder");                   jsonObject2.put("children",list3);                 }                 listMap.add(jsonObject2);                        }else{             break;           }                }// end for row             //最外层加个key-gridData             jsonMap.put("gridData", listMap);             System.out.println(jsonMap);             }         }   } 

package com.siemens.util; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class EXCELBean {   //读取excel   public static Workbook readExcel(String filePath){     Workbook wb = null;     if(filePath==null){       return null;     }     String extString = filePath.substring(filePath.lastIndexOf("."));     InputStream is = null;     try {       is = new FileInputStream(filePath);       if(".xls".equals(extString)){         return wb = new HSSFWorkbook(is);       }else if(".xlsx".equals(extString)){         return wb = new XSSFWorkbook(is);       }else{         return wb = null;       }     } catch (FileNotFoundException e) {       e.printStackTrace();     } catch (IOException e) {       e.printStackTrace();     }     return wb;   }   public static Object getCellFormatValue(Cell cell){     Object cellValue = null;     if(cell!=null){       //判断cell类型       switch(cell.getCellType()){       case Cell.CELL_TYPE_NUMERIC:{         cellValue = String.valueOf(cell.getNumericCellValue());         break;       }       case Cell.CELL_TYPE_FORMULA:{         //判断cell是否为日期格式         if(DateUtil.isCellDateFormatted(cell)){           //转换为日期格式YYYY-mm-dd           cellValue = cell.getDateCellValue();         }else{           //数字           cellValue = String.valueOf(cell.getNumericCellValue());         }         break;       }       case Cell.CELL_TYPE_STRING:{         cellValue = cell.getRichStringCellValue().getString();         break;       }       default:         cellValue = "";       }     }else{       cellValue = "";     }     return cellValue;   } }