欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

java 读取excel文件转换成json格式的实例代码

程序员文章站 2023-12-05 22:05:58
需要读取excel数据转换成json数据,写了个测试功能,转换正常: json转换:org.json.jar   测试类:  importfile....

需要读取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格式 ,希望对大家有所帮助