You need to read excel data and convert it into json data. You have written a test function and the conversion is normal:
JSON conversion: org.json.jar
Test class: 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; //File path, String filePath = "f:/haoxy/Worktable222.xls"; wb =EXCELBean.readExcel(filePath); if(wb != null){ //Use to store data in the table List<JSONObject> listMap = new ArrayList<JSONObject>(); //Get the first sheet sheet = wb.getSheetAt(0); //Get the maximum number of rows int rownum = sheet.getPhysicalNumberOfRows(); //Get the first row row = sheet.getRow(0); //Get the maximum number of columns int colnum = row.getPhysicalNumberOfCells(); //Create json object here. If you use map in actual measurement, there will be problems with json data JSONObject jsonMap = new JSONObject(); //Loop row for (int i = 1; i < rownum; i++) { row = sheet.getRow(i); if(row !=null){ //Create list object to receive read excel data List<String> list = new ArrayList<String>(); //Loop column for (int !=null){ //Create list object to receive read excel data List<String> list = new ArrayList<String>(); //Loop column for (int !=null){ //Create list object to receive read excel data List<String> list = new ArrayList<String>(); //Loop column for (int !=null){ //Create list object to receive read excel data List<String> list = new ArrayList<String>(); //Loop column for (int ) j=0;j<colnum;j++){ cellData = (String) EXCELBean.getCellFormatValue(row.getCell(j)); list.add(cellData); } //System.out.println(list.get(59)); //The following is the format of the data according to the requirements.--Creating a json object will report an exception, and capture it. 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)); //Create jsonBmt object and further nest the above objects JSONObject jsonBmt = new JSONObject(); jsonBmt.put("Turnkey", jsonTurnkey); jsonBmt.put("PowerCore", jsonPowerCore); jsonBmt.put("PowerIsland", jsonPowerIsland); //Nest the above several into the first layer object 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 //Add a key-gridData jsonMap.put("gridData", listMap); System.out.println(jsonMap); } } }Reading the excel tool class, see the reading method of netizens as quoted:
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 { //Read 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){ //Judge cell type switch(cell.getCellType()){ case Cell.CELL_TYPE_NUMERIC:{ cellValue = String.valueOf(cell.getNumericCellValue()); break; } case Cell.CELL_TYPE_FORMULA:{ //Judge whether cell is in the date format if(DateUtil.isCellDateFormatted(cell)){ //Convert to date format YYYY-mm-dd cellValue = cell.getDateCellValue(); }else{ //Number cellValue = cell.getDateCellValue(); }else{ //Number cellValue = String.valueOf(cell.getNumericCellValue()); } break; } case Cell.CELL_TYPE_STRING:{ cellValue = cell.getRichStringCellValue().getString(); break; } default: cellValue = ""; } }else{ cellValue = ""; } return cellValue; } }Summarize
The above is the java reading excel files into json format introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to everyone in time. Thank you very much for your support to Wulin.com website!