Excel文件转换成json文件 ExcelToJson

pom文件 , 导入fastjson和poi

	<dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.7</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
   </dependencies>

main方法

import java.util.Scanner;
public class Demo {
    public static void main(String[] args) throws Exception{
        //input写目录,扫描目录下所有xls和xlsx文件进行转换
        //input写具体文件,直接转换成json文件
        //output写生成json文件的保存路径
		ExcelToJson.excelToJson(input , output);
    }
}

ExcelToJson 类

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import xyz.wbls.javautils.FileUtils;

import java.io.*;

public class ExcelToJson {

    /**
     * excelToJson方法
     *
     * @param input  要转换的excel文件或者excel所在文件夹路径
     * @param output 转换完成后保存文件路径
     * @throws IOException 异常
     */
    public static void excelToJson(String input, String output) throws IOException {
        File in = new File(input);
        File out = new File(output);

        if (!out.exists()) out.mkdirs();

        if (in.isDirectory()) {
            //获取文件夹下所有xls和xlsx后缀的文件
            //FileUtils.getFiles(file , ...ext) 是另一篇文章
            // https://blog.csdn.net/qq_25278517/article/details/107610175
            File[] files = FileUtils.getFiles(in, "xls", "xlsx");
            for (File file : files) {
                saveFile(toJson(file.getCanonicalPath()), out, file.getName());
            }
        } else saveFile(toJson(input), out, in.getName());

        if (bufferedWriter != null) bufferedWriter.close();
    }


    private static BufferedWriter bufferedWriter;

    /**
     * @param jsonString 转换完成后的json字符串
     * @param out        转换完成后json文件保存路径
     * @param fileName   文件名
     * @throws IOException 异常
     */
    private static void saveFile(String jsonString, File out, String fileName) throws IOException {
        if (jsonString == null) return;

        File file = new File(out.getCanonicalPath() + "\\" + fileName + ".json");
        file.createNewFile();
        System.out.println(file.getCanonicalPath());
        bufferedWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), "utf-8"));
        bufferedWriter.write(jsonString);
        bufferedWriter.flush();
    }

    /**
     * @param filePath 文件路径
     * @return 返回转换以后的json字符串
     * @throws IOException 异常
     */
    private static String toJson(String filePath) throws IOException {
        Workbook wb;
        if (filePath == null) return null;

        FileInputStream fis = new FileInputStream(filePath);
        String ext = filePath.substring(filePath.lastIndexOf("."));

        if (ext.equals(".xls")) wb = new HSSFWorkbook(fis);
        else if (ext.equals(".xlsx")) wb = new XSSFWorkbook(fis);
        else return null;

        int sheetNum = wb.getNumberOfSheets();
        JSONObject jsonObject = new JSONObject();
        for (int s = 0; s < sheetNum; s++) {
            Sheet sheet = wb.getSheetAt(s);
            int rowCount = sheet.getPhysicalNumberOfRows();

            if (rowCount <= 1) continue;
            
            int firstRow = 0;
            for (int i = 0; i < rowCount; i++) {
                if (!isFirstRow(sheet, i)) continue;
                firstRow = i;
                break;
            }
            
            Row row1 = sheet.getRow(firstRow);
            int colCount = row1.getPhysicalNumberOfCells();
            JSONArray jsonArray = new JSONArray();
            for (int i = firstRow + 1; i < rowCount; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    JSONObject rowObj = new JSONObject(true);
                    for (int j = 0; j < colCount; j++) {
                        Cell cell = row.getCell(j);
                        if (cell != null) {
                            switch (cell.getCellType()) {
                                case Cell.CELL_TYPE_NUMERIC:
                                    rowObj.put(row1.getCell(j).getStringCellValue(), cell.getNumericCellValue());
                                    break;
                                case Cell.CELL_TYPE_FORMULA:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        rowObj.put(row1.getCell(j).getStringCellValue(), cell.getDateCellValue());
                                    } else {
                                        rowObj.put(row1.getCell(j).getStringCellValue(), cell.getNumericCellValue());
                                    }
                                    break;
                                case Cell.CELL_TYPE_STRING:
                                    rowObj.put(row1.getCell(j).getStringCellValue(), cell.getStringCellValue());
                                    break;
                                default:
                                    rowObj.put(row1.getCell(j).getStringCellValue(), "");
                            }
                        } else rowObj.put(row.getCell(j).getStringCellValue(), "");
                    }
                    jsonArray.add(rowObj);
                }
            }
            jsonObject.put(sheet.getSheetName(), jsonArray);
        }
        return JSONArray.toJSONString(jsonObject, true);
    }

    /**
     * 进行简单的判断是否可以做首行 , 过滤表格顶部有合并单元格
     * 对每一行做判断 ,得到以一个每列都不为空的行 ,作为首行
     * @param sheet sheet 工作表
     * @param number number 当前工作表的第n行
     * @return 可以做为首行返回true , 不可以返回false
     */
    public static boolean isFirstRow(Sheet sheet,int number){
        Row row = sheet.getRow(number);
        int physicalNumberOfCells = row.getPhysicalNumberOfCells();
        boolean isFirstRow = false;
        for (int j = 0; j < physicalNumberOfCells; j++) {
            if (row.getCell(j) == null) break;
            else if (row.getCell(j).getStringCellValue().equals("")) break;
            else if (j == physicalNumberOfCells - 1) isFirstRow = true;
        }
        return isFirstRow;
    }
}