excel to json java_java 读取 excel 转 json

import com.alibaba.fastjson.JSONArray;

import com.alibaba.fastjson.JSONObject;

import org.apache.commons.lang3.StringUtils;

import org.apache.poi.ss.usermodel.CellType;

import org.apache.poi.ss.usermodel.DateUtil;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileWriter;

import java.io.IOException;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.Map;

public class ExcelToJson {

JSONArray dataArray = new JSONArray();

;

String newFile = null;

File dataJson = null;

/**

* 读取xlsx文件

*

* @param path

* @throws IOException

* @throws ParseException

*/

public void readXlsx(String path) throws IOException, ParseException {

XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(path));

// 循环工作表Sheet

for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {

XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);

String sheetName = xssfSheet.getSheetName();

if (xssfSheet == null) {

continue;

}

//当前sheet的json文件

JSONObject sheetJson = new JSONObject();

//当前sheet的array，作为sheetJson 的value值

JSONArray sheetArr = new JSONArray();

//sheet的第一行，获取作为json的key值

JSONArray key = new JSONArray();

int xssfLastRowNum = xssfSheet.getLastRowNum();

System.out.println("当前表单的最后一行: " + xssfLastRowNum);

// 循环行Row

for (int rowNum = 0; rowNum <= xssfLastRowNum; rowNum++) {

XSSFRow xssfRow = xssfSheet.getRow(rowNum);

if (xssfRow == null) {

continue;

}

// 循环列Cell，在这里组合json文件

int firstCellNum = xssfRow.getFirstCellNum();

int lastCellNum = xssfRow.getLastCellNum();

JSONObject rowJson = new JSONObject();

for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {

XSSFCell cell = null;

try {

cell = xssfRow.getCell(cellNum);

if (cell == null) {

rowJson.put(key.getString(cellNum), "");

continue;

}

if (rowNum == 0)

key.add(toString(cell));

else {

//若是列号超过了key的大小，则跳过

if (cellNum >= key.size()) continue;

rowJson.put(key.getString(cellNum), toString(cell));

}

} catch (Exception e) {

e.printStackTrace();

}

}

if (!rowJson.isEmpty())

sheetArr.add(rowJson);

}

sheetJson.put(sheetName, shuffleData(sheetArr));

dataArray.add(sheetJson);

}

System.out.println(dataArray);

}

public static void main(String[] args) {

String path = "C:\\Users\\garila\\Downloads\\test.xlsx";

String newFilePath = path.substring(0, path.lastIndexOf("."));

System.out.println(newFilePath);

ExcelToJson excelToJson = new ExcelToJson();

try {

excelToJson.readXlsx(path);

excelToJson.createJson(newFilePath);

excelToJson.writeToJson();

} catch (IOException e) {

e.printStackTrace();

} catch (ParseException e) {

e.printStackTrace();

}

}

public String toString(XSSFCell cell) {

switch (cell.getCellTypeEnum()) {

case _NONE:

cell.setCellType(CellType.STRING);

return "";

case NUMERIC:

if (DateUtil.isCellDateFormatted(cell)) {

SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");

return sdf.format(cell.getDateCellValue());

}

cell.setCellType(CellType.STRING);

return cell.getStringCellValue();

case STRING:

String val = cell.getStringCellValue();

if ("无".equalsIgnoreCase(val)) return "";

return val;

case FORMULA:

return cell.getCellFormula();

case BLANK:

return "";

case BOOLEAN:

return cell.getBooleanCellValue() + "";

case ERROR:

return "非法字符";

default:

return "未知字符";

}

}

public void createJson(String path) {

newFile = path + ".json";

dataJson = new File(newFile);

if (dataJson.exists()) {

dataJson.delete();

} else {

try {

dataJson.createNewFile();

} catch (IOException e) {

e.printStackTrace();

}

}

}

public void writeToJson() {

FileWriter fw = null;

try {

fw = new FileWriter(newFile);

fw.write(dataArray.toString());

fw.close();

} catch (Exception e) {

}

}

public JSONArray shuffleData(JSONArray sheetArr) {

JSONArray array = new JSONArray();

sheetArr.forEach(jsonStr -> {

JSONObject object = JSONObject.parseObject(jsonStr.toString());

int count = 0;

int length = 0;

for (Map.Entry map : object.entrySet()) {

String key = map.getKey();

Object o = map.getValue();

length++;

boolean b = StringUtils.isEmpty(o.toString());

if (b) {

count++;

}

}

if (count != length) {

array.add(object);

}

});

return array;

}

}

maven依赖：

org.apache.poi

poi

3.17

org.apache.poi

poi-ooxml

3.17

poi-ooxml是读取xlsx的依赖包