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格式 ，希望对大家有所帮助