尝试将数据重新插入excel文件时出错

Question

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

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

线程“主”org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException：保存失败：保存包时发生错误：部分 /docProps/app.xml无法保存在流中，编组器org[e-mail]（ZipPackage.java:479）在org.apache.poi.openxml4j.opc.OPCPackage.save（OPCPackage.java:1414）在org.apache.poi.POIXMLDocument.write（POIXMLDocument.java:179）在com.gbt.POC.TxtFileReader.main（TxtFileReader.java:359）由org.apache.poi.openxml4j.exceptions.OpenXML引起4JException：部分 /docProps/app.xml无法保存在流中，编组器org.apache.poi.openxml4j.opc.internal.marshallers.DefaultMarshaller@74ad1f1f在org.apache.poi.openxml4j.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);

任何帮助都是提前赞赏的。

Answer 1

您可以使用Xcelite图书馆让您的生活更加轻松:

>

首先创建一个小bean类

创建一个Xcelite对象，并让它创建一个工作表

获取作家

给你写豆子

写入输出流

import com.ebay.xcelite.annotations.Column;

public class Entity {
    @Column
    String empid1;
    @Column
    String fname;
    @Column
    String ccode1;
}

Xcelite xcelite = new Xcelite();
XceliteSheet sheet = xcelite.createSheet("Clean");
SheetWriter writer = sheet.getBeanWriter(Entity.class);

    writer.write(entities);
    xcelite.write(outstream);

总而言之，这里是您修改的代码：

    LinkedList<String[]> llist = new LinkedList <> ();
    String in0[] = new String[3];
    llist.add(in0);
    String in1[] = new String[3];
    in1[0] = "1";
    in1[1] = "testtest";
    in1[2] = "IND";
    llist.add(in1);

    String[] data;
    FileOutputStream outstream = new FileOutputStream( "C:\\Users\\Desktop\\data.xls");
    Xcelite xcelite = new Xcelite();
    XceliteSheet sheet = xcelite.createSheet("Clean");
    SheetWriter writer = sheet.getBeanWriter(Entity.class);
    List<Entity> entities = new ArrayList<>();

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

            String replace;
            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];
            }
            Entity entity = new Entity();
            entities.add(entity);
            entity.empid1 = data[0];
            entity.fname = data[1];
            entity.ccode1 = ccode1;
        }
    }
    writer.write(entities);
    xcelite.write(outstream);
}

Answer 2

我复制并粘贴了你的代码，看看是什么错误。不幸的是，我看不到你的错误。但在搜索错误后，我找到了一些答案。。

发生此异常是因为excel工作表的POI API多次写入excel工作表。每次它都需要在同一个File对象上创建一个新的FileOutputStream实例。

要解决此问题，请先将所有行写入工作簿实例，然后使用FileOutputStream写入工作簿。

https://tjavadeeps . WordPress . com/2015/04/14/org-Apache-poi-open XML 4j-exceptions-open XML 4 jruntimeexception-fail-to-save-an-error-occurs-while-saving-the-package/

org . Apache . poi . open XML 4j . exceptions . open XML 4 jruntimeexception:保存失败

希望你得到一个提示。

接下来，我制作了我的txt文件，并像你做的那样保存到excel，它工作了，

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);
    }
}