Java把Excel表格转换成json
**pom文件
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.8.2</version>
</dependency>
<dependency>
<groupId>net.sf.json-lib</groupId>
<artifactId>json-lib</artifactId>
<version>2.4</version>
<classifier>jdk15</classifier>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.56</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.example</groupId>
<artifactId>WebMavwe</artifactId>
<version>1.0-SNAPSHOT</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>2.7.5</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-annotations</artifactId>
<version>2.7.5</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.7.5</version>
</dependency>
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
<version>1.9.4</version>
</dependency>
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-core-asl</artifactId>
<version>1.9.4</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.21</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.21</version>
</dependency>
</dependencies>`**
***导包
`package com.json;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
main方法*
public class JsonExcel {
private final static Logger logger = LoggerFactory.getLogger(JsonExcel.class);
//定义一个全局的ObjectMapper
private final static ObjectMapper mapper = new ObjectMapper();
public static void main(String[] args) {
try {
//获取Excel表格的路径
FileInputStream fis = new FileInputStream("D:\\WangZY\\Excel\\test.xls");
//获取Workbook
Workbook workbook = WorkbookFactory.create(fis);
//获取sheet数
int sheetNum = workbook.getNumberOfSheets();
//获取JSONObject 注意导fastjson包
JSONObject jsonObject = new JSONObject();
//循环
for (int i = 0; i < sheetNum; i++) {
Sheet sheet = workbook.getSheetAt(i);
//获取最大行数
int rowNum = sheet.getPhysicalNumberOfRows();
//简单判断一下
if(rowNum<=1){
continue;
}
//获取第一行
Row row = sheet.getRow(0);
//获取最大列数
int colnum = row.getPhysicalNumberOfCells();
// System.out.println("列::"+colnum);
JSONArray jsonArray = new JSONArray();
//循环读取行
for (int i1 = 1; i1 < rowNum; i1++) {
Row row1 = sheet.getRow(i1);
//判断行是否为空
if(row1 != null){
JSONObject rowObj = new JSONObject();
//循环列
for (int i2 = 0; i2 < colnum; i2++) {
//System.out.println("列数::"+colnum);
Cell row1Cell = row1.getCell(i2);
//判断列是否为空
if(row1Cell != null){
//System.out.println("问题?"+row1Cell);
//判断row1Cell的类型
switch (row1Cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC:{
//数字
rowObj.put(row.getCell(i2).getStringCellValue(),row1Cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA:{
//判断rowCell是否是日期格式
if(DateUtil.isCellDateFormatted(row1Cell)){
rowObj.put(row.getCell(i2).getStringCellValue(),row1Cell.getDateCellValue());
}else {
//数字
rowObj.put(row.getCell(i2).getStringCellValue(),row1Cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING:{
rowObj.put(row.getCell(i2).getStringCellValue(),row1Cell.getStringCellValue());
break;
}
default:
rowObj.put(row.getCell(i2).getStringCellValue(),"");
}
}else {
rowObj.put(row.getCell(i2).getStringCellValue(),"");
}
}
//
jsonArray.add(rowObj);
}
}
logger.info(jsonArray.toJSONString());
jsonObject.put(sheet.getSheetName(),jsonArray);
System.out.println(jsonObject);
}
logger.info(jsonObject.toJSONString());
// System.out.println(jsonObject);
} catch (Exception e) {
e.printStackTrace();
}
}
}