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

需要读取excel数据转换成json数据，写了个测试功能，转换正常：

JSON转换：org.json.jar

测试类： importFile.java:

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

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 工具类，看到网友的读取方法引用一下：

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

原文链接：https://blog.csdn.net/qq_27985863/article/details/79917636

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

相关文章

快网idc优惠网

相关文章

微信

快网idc优惠网

QQ交流群