java - 尝试将数据插入回 Excel 文件时出错

Question

我正在尝试读取文本文件，然后对其应用一些数据验证规则。添加规则后，我将数据写回excel文件。

但是，当尝试将其写回 excel 文件时，我收到此错误：

线程“main” org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException 中的异常：保存失败：保存包时出错：部分 /docProps/app.xml 无法使用编组器 org.apache 保存在流中.poi.openxml4j.opc.internal.marshallers.DefaultMarshaller@74ad1f1f at org.apache.poi.openxml4j.opc.ZipPackage.saveImpl(ZipPackage.java:479) at org.apache.poi.openxml4j.opc.OPCPackage.save( OPCPackage.java:1414) at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:179) at com.gbt.POC.TxtFileReader.main(TxtFileReader.java:359) 原因：org.apache.poi.openxml4j .exceptions.OpenXML4JException：/docProps/app.xml 部分无法保存在 org.apache.poi.openxml4j 的 marshaller org.apache.poi.openxml4j.opc.internal.marshallers.DefaultMarshaller@74ad1f1f 的流中。opc.ZipPackage.saveImpl(ZipPackage.java:470) ... 还有 3 个

这是我到目前为止所拥有的：

LinkedList < String[] > llist = new LinkedList < > ();

String[] data;

XSSFWorkbook workBook = new XSSFWorkbook();
FileOutputStream outstream = new FileOutputStream("data.xls");
XSSFSheet spreadSheet = workBook.createSheet("Clean");

for (int i = 0; i < llist.size(); i++) {
 if (i == 0) {
  System.out.println("Hello World!");
 } else {
  data = llist.get(i);

  String empid1 = data[0];
  String fname = data[1];
  String ccode1 = data[2];

  if (data[2].equals("IND")) {
   replace = data[2].replaceAll("IND", "IN");
   ccode1 = replace;
  } else if (data[2].equals("USA")) {
   replace = data[2].replaceAll("USA", "US");
   ccode1 = replace;
  } else {
   ccode1 = data[2];
  }

  //String newData=empid1+","+fname+","+ccode1;

  XSSFRow row = spreadSheet.createRow(i);

  XSSFCell cell = row.createCell(0);
  cell.setCellValue(empid1);

  cell = row.createCell(1);
  cell.setCellValue(fname);

  cell = row.createCell(2);
  cell.setCellValue(ccode1);
 }
}

workBook.write(outstream);

提前感谢任何帮助。

标签： javaexcelapache-poi

Answer 1

I copied and pasted your code to see what error is it. Unfortunately I couldn't see your error. But after searching the error I found some answers..

This exception occurred because the POI API for excel sheet writes to the excel sheet multiple times. And each time it requires a new instance of FileOutputStream over same File object.

To resolve this, wrote all Rows to the workbook instance first, and at one did write to the workbook using FileOutputStream.

https://tjavadeeps.wordpress.com/2015/04/14/org-apache-poi-openxml4j-exceptions-openxml4jruntimeexception-fail-to-save-an-error-occurs-while-saving-the-package/

org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException: Fail to save

Hope you get a hint.

Next, I made my txt file and save to excel like what you did and It works,

package excelTest;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.LinkedList;

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.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;

public class readExcel {

    public static void main(String[] args) throws IOException {
        LinkedList<String> llist = new LinkedList<>();
        String[] data = null;
        String replace = "";
        XSSFWorkbook workBook = new XSSFWorkbook();
        FileOutputStream outstream = new FileOutputStream( "C:\\Users\\Desktop\\data.xls");
        XSSFSheet spreadSheet = workBook.createSheet("Clean");

        /*
         * MyFile.txt
           ----------------------
            0
            IND 
            USA
            LOVE
           ----------------------
         */



        try (BufferedReader br = new BufferedReader(new FileReader( "C:\\Users\\Desktop\\myFile.txt"))) {
            String sCurrentLine;

            while ((sCurrentLine = br.readLine()) != null) {
                llist.add(sCurrentLine);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }


        String empid1 = "";
        String fname = "";
        String ccode1 = "";


        System.out.println("llist.get(i)"+llist.get(0)+"   "+llist.get(1));
        for (int i = 0; i < llist.size(); i++){
            System.out.println(llist.get(i));
        }
        empid1 = llist.get(1);  //IND
        fname = llist.get(2);   //USA
        ccode1 =llist.get(3);   //LOVE


        if (fname.equals("IND")) {
            replace = fname.replaceAll("IND", "IN");
            ccode1 = replace;
        } else if (fname.equals("USA")) {
            replace = fname.replaceAll("USA", "US");
            ccode1 = replace; //'LOVE' CHANGE TO US. (IF YOU WANT TO CHANGE TO US IN HERE-> fname=fname.replaceAll("USA","US");
        } else {
            ccode1 = fname; //'LOVE' CHANGE TO USA AGAIN.
        }


        XSSFRow row = spreadSheet.createRow(0);

        XSSFCell cell = row.createCell(0);
        cell.setCellValue(empid1);

        cell = row.createCell(1);
        cell.setCellValue(fname);

        cell = row.createCell(2);
        cell.setCellValue(ccode1);
        workBook.write(outstream);
    }
}