POI完成excel导出为json

POI完成excel导出为json

 1)导出类

package org.example.TestExcel;


import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.InputStream;

public class ExcelUtils {

    public static String getStringData(String path) {
        // sheet对应的json数据
        JSonObject jsonObject = new JSonObject();

        try {
            final int START_ROW_NUM = 3;

            File dir = new File(ExcelUtils.class.getClassLoader().getResource(path).getPath());
            for (File file : dir.listFiles()) {
                String fullPath = path + File.separator + file.getName();
                InputStream is = ExcelUtils.class.getClassLoader().getResourceAsStream(fullPath);

                Workbook workbook = WorkbookFactory.create(is);

                // 获取sheet数
                int sheetNum = workbook.getNumberOfSheets();

                for (int s = 0; s < sheetNum; s++) {
                    Sheet sheet = workbook.getSheetAt(s);

                    // 获取最大行数
                    int rowNum = sheet.getPhysicalNumberOfRows();
                    if (rowNum <= START_ROW_NUM) {
                        continue;
                    }

                    // 第一行
                    Row rowFirst = sheet.getRow(0);
                    // 获取最大列数
                    int column = rowFirst.getPhysicalNumberOfCells();

                    // 存储一行数据
                    JSonArray jsonArray = new JSonArray();

                    // 遍历每一行
                    for (int i = START_ROW_NUM; i < rowNum; i++) {
                        Row row = sheet.getRow(i);
                        if (row != null) {
                            // 存储这一行中每一列的数据
                            JSonObject rowObj = new JSonObject();

                            // 循环遍历每一列
                            for (int j = 0; j < column; j++) {
                                Cell cellData = row.getCell(j);
                                // 这一列存在
                                if (cellData != null) {
                                    switch (cellData.getCellTypeEnum()) {
                                        case NUMERIC: {
                                            rowObj.put(rowFirst.getCell(j).getStringCellValue(), cellData.getNumericCellValue());
                                            break;
                                        }
                                        case FORMULA: {
                                            if (DateUtil.isCellDateFormatted(cellData)) {
                                                // 日期
                                                rowObj.put(rowFirst.getCell(j).getStringCellValue(), cellData.getDateCellValue());
                                            } else {
                                                // 纯数字
                                                rowObj.put(rowFirst.getCell(j).getStringCellValue(), cellData.getNumericCellValue());
                                            }
                                            break;
                                        }
                                        case STRING: {
                                            rowObj.put(rowFirst.getCell(j).getStringCellValue(), cellData.getStringCellValue());
                                            break;
                                        }
                                        default: {
                                            System.err.println("不识别的类型" + cellData.getCellTypeEnum());
                                            rowObj.put(rowFirst.getCell(j).getStringCellValue(), "");
                                        }
                                        break;
                                    }
                                } else {
                                    rowObj.put(rowFirst.getCell(j).getStringCellValue(), "");
                                }
                            }

                            jsonArray.add(rowObj);
                        }
                    }

                    //sheet名字对应的内容
                    jsonObject.put(sheet.getSheetName(), jsonArray);
                }

                // 关闭
                is.close();
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }

        return jsonObject.toJSonString();
    }
}

2)简单使用

package org.example.TestExcel;

public class Main {
    public static void main(String[] args) {
        System.out.println(ExcelUtils.getStringData("excel"));
    }
}

{"PropertyResource":[{"value":1.0,"key":"Scene.Menu"},{"value":2.0,"key":"Scene.Main"}]}