快捷導(dǎo)航

java 讀取excel文件轉(zhuǎn)換成json格式的實(shí)例代碼

更新時(shí)間：2018年04月13日 09:04:28 作者：biubiukey

這篇文章主要介紹了 java 讀取excel文件轉(zhuǎn)換成json格式的實(shí)例代碼,需要的朋友可以參考下

需要讀取excel數(shù)據(jù)轉(zhuǎn)換成json數(shù)據(jù)，寫(xiě)了個(gè)測(cè)試功能，轉(zhuǎn)換正常：

JSON轉(zhuǎn)換：org.json.jar

測(cè)試類： 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){ 
        //用來(lái)存放表中數(shù)據(jù) 
        List<JSONObject> listMap = new ArrayList<JSONObject>(); 
        //獲取第一個(gè)sheet 
        sheet = wb.getSheetAt(0); 
        //獲取最大行數(shù) 
        int rownum = sheet.getPhysicalNumberOfRows(); 
        //獲取第一行 
        row = sheet.getRow(0); 
        //獲取最大列數(shù) 
        int colnum = row.getPhysicalNumberOfCells(); 
        //這里創(chuàng)建json對(duì)象，實(shí)測(cè)用map的話，json數(shù)據(jù)會(huì)有問(wèn)題  
        JSONObject jsonMap = new JSONObject(); 
        //循環(huán)行 
        for (int i = 1; i < rownum; i++) { 
          row = sheet.getRow(i); 
          if(row !=null){ 
            //創(chuàng)建list對(duì)象接收讀出的excel數(shù)據(jù) 
            List<String> list = new ArrayList<String>(); 
            //循環(huán)列 
            for (int j=0;j<colnum;j++){ 
              cellData = (String) EXCELBean.getCellFormatValue(row.getCell(j)); 
              list.add(cellData); 
            } 
            //System.out.println(list.get(59)); 
              //下面具體是本人對(duì)數(shù)據(jù)按需求進(jìn)行格式處理   ---創(chuàng)建json對(duì)象會(huì)報(bào)異常，捕捉一下。 
              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)); 
                //創(chuàng)建jsonBmt對(duì)象，進(jìn)一步把以上對(duì)象嵌套 
                JSONObject jsonBmt = new JSONObject(); 
                jsonBmt.put("Turnkey", jsonTurnkey); 
                jsonBmt.put("PowerCore", jsonPowerCore); 
                jsonBmt.put("PowerIsland", jsonPowerIsland); 
                //把以上幾個(gè)嵌套入第一層對(duì)象中 
                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 
            //最外層加個(gè)key-gridData 
            jsonMap.put("gridData", listMap); 
            System.out.println(jsonMap);       
      }       
  }   
}

讀取excel 工具類，看到網(wǎng)友的讀取方法引用一下：

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)){ 
          //轉(zhuǎn)換為日期格式Y(jié)YYY-mm-dd 
          cellValue = cell.getDateCellValue(); 
        }else{ 
          //數(shù)字 
          cellValue = String.valueOf(cell.getNumericCellValue()); 
        } 
        break; 
      } 
      case Cell.CELL_TYPE_STRING:{ 
        cellValue = cell.getRichStringCellValue().getString(); 
        break; 
      } 
      default: 
        cellValue = ""; 
      } 
    }else{ 
      cellValue = ""; 
    } 
    return cellValue; 
  } 
}

總結(jié)

以上所述是小編給大家介紹的java 讀取excel文件轉(zhuǎn)換成json格式，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！

您可能感興趣的文章: