Java读取xlsx内容然后输出为json格式放入新的xlsx中

需要引入的maven依赖

​
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.15-beta2</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.2.7</version>
    </dependency>
    <!-- HSSF需要引入的 -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>RELEASE</version>
    </dependency>
    <!-- XSSF需要引入的 -->
    <dependency>
      <groupId>org.apache.xmlbeans</groupId>
      <artifactId>xmlbeans</artifactId>
      <version>2.6.0</version>
    </dependency>
    <dependency>
      <groupId>dom4j</groupId>
      <artifactId>dom4j</artifactId>
      <version>1.6.1</version><!--$NO-MVN-MAN-VER$ -->
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>

​

具体实现代码

//需要导入的包
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.sun.org.apache.xpath.internal.objects.XString;
import org.apache.poi.ss.usermodel.Cell;
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.util.regex.Pattern;

        
        XSSFWorkbook book;
        XSSFSheet sheet;
        JSONArray jsons;
        XSSFRow row;

        //输入文件
        InputStream is = new FileInputStream(new         File("C:\\Users\\熬夜小冠军\\Desktop\\1.xlsx"));
        book = new XSSFWorkbook(is);

        sheet = book.getSheetAt(0);

        jsons = new JSONArray();

        //输出文件
        FileOutputStream out = new FileOutputStream("C:\\Users\\熬夜小冠军\\Desktop\\tests.xlsx");


        for (int i = 1; i < sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (row != null) {
                JSONObject json = new JSONObject();
                //对于纯数字内容要做这一操作
                //row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                //row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                // row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                // row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                //放入对应字段和对应行数
                //var1 与 var2 的参考 {"var2":"熬夜","var1":"小冠军"}
                json.put("var1", row.getCell(0).getStringCellValue());
                json.put("var2", row.getCell(1).getStringCellValue());
                // json.put("var4", row.getCell(2).getStringCellValue());
                // json.put("var5", row.getCell(3).getStringCellValue());
                jsons.add(json);
            }
        }

        String x = jsons.toJSONString();

        Pattern p = Pattern.compile("[}]");          //设定字符
        String[] r = p.split(x);         //按设定的字符进行分割
        for (int i = 0; i < r.length; i++) {
            System.out.println(r[i].substring(1) + "}");
        }
        int index = 0;
        JSONObject json = new JSONObject();
        for (int i = 0; i < 1; i++) {
//创建 sheet页
            XSSFSheet sheets = wb.createSheet("第" + (i + 1) + "页");
            if (sheets.getLastRowNum() == 0) {
                System.out.println("===========");
            }

            
            for (int k = 0; k < r.length; k++) {
                //  for (int k = 0; k <= 30; k++) {
                XSSFRow rows = sheets.createRow(k);// 创建行数
//写入单元格子   列数=0+1
                int totalRows = sheets.getLastRowNum() + 1;
                //System.out.println(r[i].substring(1) + "}");
//rows.createCell(0)单元格第几列设置
                // rows.createCell(0).setCellValue(k);
                // rows.createCell(1).setCellValue(k);
                //  rows.createCell(2).setCellValue(k);
//                for (int ii = 0; ii < r.length; ii++) {
//
//
//                    System.out.println(r[i].substring(1) + "}");
//                }
                rows.createCell(3).setCellValue(r[k].substring(1) + "}");
                //rows.createCell(4).setCellValue(k);
                // rows.createCell(5).setCellValue(k);
                //rows.createCell(6).setCellValue(k);
                //rows.createCell(7).setCellValue(k);
                //rows.createCell(8).setCellValue(k);
            }
        }
        book.close();
        wb.write(out);
        out.close();
        System.out.println("OK");
    }

欢迎指正谢谢！