目录
测试结果
表格数据
代码
依赖
测试结果
/**
* C:\\Users\\Administrator\\Desktop\\testFile/abc.xlsx
* test 结果
* 13
* {"Sheet1":[{"s3":"ae","s4":"aa","s5":"aa","s1":"a","s2":"ab"},
* {"s3":"af","s4":"ab","s5":"ac","s1":"b","s2":"ac"}
* ]}
*
* C:\\Users\\Administrator\\Desktop\\testFile/ac.xls
*
* 13
* {"Sheet1":[{"s3":"ae","s4":"aa","s5":"aa","s1":"a","s2":"ab"},
* {"s3":"af","s4":"ab","s5":"ac","s1":"b","s2":"ac"} ]}
*/
表格数据
代码
package com.superman.uitl;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.FileInputStream;
/**
* xlsx or xls 都 ok
* excel 转 json
*
* @author Administrator
*
*/
public class ExeclToJson {
private final static Logger logger = LoggerFactory.getLogger(ExeclToJson.class);
//private final static ObjectMapper mapper = new ObjectMapper();
/**
* C:\\Users\\Administrator\\Desktop\\testFile/abc.xlsx
* test 结果
* 13
* {"Sheet1":[{"s3":"ae","s4":"aa","s5":"aa","s1":"a","s2":"ab"},
* {"s3":"af","s4":"ab","s5":"ac","s1":"b","s2":"ac"}
* ]}
*
* C:\\Users\\Administrator\\Desktop\\testFile/ac.xls
*
* 13
* {"Sheet1":[{"s3":"ae","s4":"aa","s5":"aa","s1":"a","s2":"ab"},
* {"s3":"af","s4":"ab","s5":"ac","s1":"b","s2":"ac"} ]}
*/
//test
public static void main(String[] args) {
String fileUrl = "C:\\Users\\Administrator\\Desktop\\testFile/ac.xls";
JSONObject jo = ExeclToJson.excelToJson(fileUrl);
logger.info(jo.getJSONArray("Sheet1").size()+"");
logger.info(jo.toJSONString());
}
//excel 转 json
public static JSONObject excelToJson(String fileUrl){
try {
FileInputStream inp = new FileInputStream(fileUrl);
Workbook workbook = WorkbookFactory.create(inp);
//获取sheet数
int sheetNum = workbook.getNumberOfSheets();
JSONObject jsonObject = new JSONObject();
for (int s = 0; s < sheetNum; s++) {
// Get the Sheet of s.
Sheet sheet = workbook.getSheetAt(s);
//获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
if (rownum <= 1) {
continue;
}
//获取第一行
Row row1 = sheet.getRow(0);
//获取最大列数
int colnum = row1.getPhysicalNumberOfCells();
JSONArray jsonArray = new JSONArray();
for (int i = 1; i < rownum; i++) {
Row row = sheet.getRow(i);
if (row != null) {
// List<Object> list = new ArrayList<>();
JSONObject rowObj = new JSONObject();
//循环列
for (int j = 0; j < colnum; j++) {
Cell cellData = row.getCell(j);
if (cellData != null) {
//判断cell类型
switch (cellData.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
//判断cell是否为日期格式
if (DateUtil.isCellDateFormatted(cellData)) {
//转换为日期格式YYYY-mm-dd
rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getDateCellValue());
} else {
//数字
rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING: {
rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getStringCellValue());
break;
}
default:
rowObj.put(row1.getCell(j).getStringCellValue(), "");
}
} else {
rowObj.put(row1.getCell(j).getStringCellValue(), "");
}
}
jsonArray.add(rowObj);
}
}
// logger.info(jsonArray.toJSONString());
jsonObject.put(sheet.getSheetName(), jsonArray);
return jsonObject;
}
logger.info(jsonObject.toJSONString());
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
依赖
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>
ok
