java 讀取excel文件轉換成json格式的實例代碼

需要讀取excel數據轉換成json數據，寫了個測試功能，轉換正常：

JSON轉換：org.json.jar

測試類： importFile.java:

 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); } } }

讀取excel 工具類，看到網友的讀取方法引用一下：

EXCELBean.java:

 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; } }

總結

以上所述是小編給大家介紹的java 讀取excel文件轉換成json格式，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對武林網網站的支持！