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格式 ，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对我们网站的支持！

您可能感兴趣的文章:

• Java实现excel表格转成json的方法