POI 5.2.2 版本 excel 转json，json转excel 工具类

添加依赖：

<dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>fastjson</artifactId>
     <version>1.2.83</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.2</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-excelant</artifactId>
    <version>5.2.2</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>5.2.2</version>
</dependency>

 添加属性：

<properties>
     <java.version>1.8</java.version>
     <log4j2.version>2.17.1</log4j2.version>
</properties>

工具类：

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
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.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Set;


public class ExcelUtil {

    /**
     * 读取某一个单元格值
     *
     * @param cell
     * @return
     * @throws Exception
     */
    public static Object getCellValueByCell(Cell cell) throws Exception {
        //判断是否为null或空串
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat sdv = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US);
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        Object value = null;
        CellType cellType = cell.getCellType();
        if (cell instanceof XSSFCell){
            if (CellType.BLANK == cellType) {
                value = null;
            } else if (CellType.BOOLEAN == cellType) {
                value = cell.getBooleanCellValue();
            } else if (CellType.ERROR == cellType) {
                value = cell.getErrorCellValue();
            } else if (CellType.FORMULA == cellType) {
                value = cell.getNumericCellValue();
            } else if (CellType.NUMERIC == cellType) {
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = (Date) sdv.parse(cell.getDateCellValue().toString()); //将读出的时间进行格式转化，
                    value = sdf.format(date);//最终输出格式为 yyyy-MM-dd HH:mm:ss
                } else {
                    value = new DecimalFormat("0.########").format(cell.getNumericCellValue());
                }
            } else if (CellType.STRING == cellType) {
                value = cell.getStringCellValue() == null ? "" : cell.getStringCellValue().trim();
            } else {
                throw new Exception("不能识别类型！");
            }
        }
        if (cell instanceof HSSFCell){
            if (CellType.BLANK == cellType) {
                value = null;
            } else if (CellType.BOOLEAN == cellType) {
                value = cell.getBooleanCellValue();
            } else if (CellType.ERROR == cellType) {
                value = cell.getErrorCellValue();
            } else if (CellType.FORMULA == cellType) {
                value = cell.getNumericCellValue();
            } else if (CellType.NUMERIC == cellType) {
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = (Date) sdv.parse(cell.getDateCellValue().toString()); //将读出的时间进行格式转化，
                    value = sdf.format(date);//最终输出格式为 yyyy-MM-dd HH:mm:ss
                } else {
                    value = new DecimalFormat("0.########").format(cell.getNumericCellValue());
                }
            } else if (CellType.STRING == cellType) {
                value = cell.getStringCellValue() == null ? "" : cell.getStringCellValue().trim();
            } else {
                throw new Exception("不能识别类型！");
            }
        }

        return value;
    }

    /**
     * 读取Excel文件转成json
     * @param path excel文件全路径
     * @return
     * @throws IOException
     */
    public static String readExcel(String path) throws IOException {
        JSONArray array = new JSONArray();
        InputStream is = null;
        Workbook workbook = null;
        try {
            is = new FileInputStream(path);
            workbook = create(is);
            //获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)
            Sheet sheet = workbook.getSheetAt(0);
            Row row = sheet.getRow(0);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) { //从第二行开始读取数据，第一行数据为key,
                JSONObject object = new JSONObject();
                Row r = sheet.getRow(i); //第2行开始是数据行
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = r.getCell(j);
                    object.put(getCellValueByCell(sheet.getRow(0).getCell(j)).toString(), getCellValueByCell(cell));
                }
                array.add(object);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            workbook.close();
            is.close();
        }
        return array.toString();
    }


    public static Workbook create(InputStream inp) throws IOException, InvalidFormatException {
        if (!inp.markSupported()) {
            inp = new PushbackInputStream(inp, 8);
        }
        return WorkbookFactory.create(inp);
    }

    /**
     * 根据jsonArray 导出excel
     * @param jsonString json数组
     * @param path  excel导出文件全路径
     * @throws IOException
     */
    public static void writeExcel(String jsonString,String path) throws IOException {
        Set<String> keys = null;
        // 创建HSSFWorkbook对象
        XSSFWorkbook wb = new XSSFWorkbook();
        // 创建HSSFSheet对象
        XSSFSheet sheet = wb.createSheet("sheet0");

        int roleNo = 0;
        int rowNo = 0;
        JSONArray jsonArray = JSONArray.parseArray(jsonString);
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject =jsonArray.getJSONObject(i);
            // 创建HSSFRow对象
            XSSFRow row = sheet.createRow(roleNo++);
            // 创建HSSFCell对象
            if (keys == null) {
                //标题
                keys = jsonObject.keySet();
                for (String s : keys) {
                    XSSFCell cell = row.createCell(rowNo++);
                    cell.setCellValue(s);
                }
                rowNo = 0;
                row = sheet.createRow(roleNo++);
            }
            for (String s : keys) {
                XSSFCell cell = row.createCell(rowNo++);
                cell.setCellValue(jsonObject.getString(s));
            }
            rowNo = 0;
        }

        try (FileOutputStream output = new FileOutputStream(path)) {
            // 输出Excel文件
            wb.write(output);
            wb.close();
            output.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据jsonArray 导出excel
     * @param jsonObjectList json数组
     * @param path  excel导出文件全路径
     * @throws IOException
     */
    public static void writeExcel(List<JSONObject> jsonObjectList, String path) throws IOException {
        Set<String> keys = null;
        // 创建HSSFWorkbook对象
        XSSFWorkbook wb = new XSSFWorkbook();
        // 创建HSSFSheet对象
        XSSFSheet sheet = wb.createSheet("sheet0");

        int roleNo = 0;
        int rowNo = 0;
        for (int i = 0; i < jsonObjectList.size(); i++) {
            JSONObject jsonObject =jsonObjectList.get(i);
            // 创建HSSFRow对象
            XSSFRow row = sheet.createRow(roleNo++);
            // 创建HSSFCell对象
            if (keys == null) {
                //标题
                keys = jsonObject.keySet();
                for (String s : keys) {
                    XSSFCell cell = row.createCell(rowNo++);
                    cell.setCellValue(s);
                }
                rowNo = 0;
                row = sheet.createRow(roleNo++);
            }
            for (String s : keys) {
                XSSFCell cell = row.createCell(rowNo++);
                cell.setCellValue(jsonObject.getString(s));
            }
            rowNo = 0;
        }

        try (FileOutputStream output = new FileOutputStream(path)) {
            // 输出Excel文件
            wb.write(output);
            wb.close();
            output.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}