如何使用java填写Excel文件?

I have the following code to fill in the Excel file, with information that I get from the Internet using Jsoup.

我有下面的代码来填充Excel文件，使用Jsoup从因特网上获取信息。

package knvbj;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.nodes.TextNode;
import org.jsoup.select.Elements;

public class KNVBJ {

private static int Clnummer=1;
    public static void main(String[] args) throws IOException {
       FileOutputStream out = new FileOutputStream("/Users/muratcanpinar/Downloads/KNVBJ/build/classes/knvbj/ClubInformation.xlsx");
        List<String> urlList = ReadXlsx.readXlsx();
        urlList.get(1);
        for (String url : urlList) {
            System.out.println("url: " + url);
        }

        for (int i = 0; i < urlList.size(); i++) {
            Document doc = Jsoup.connect(urlList.get(i))
                    .data("query", "Java")
                    .userAgent("Mozilla")
                    .cookie("auth", "token")
                    .timeout(3000)
                    .post();

            Element content1 = doc.getElementsByClass("details").first();
            String body = content1.toString();
            Document docb = Jsoup.parseBodyFragment(body);
            Element bbd = docb.body();
            String kkj = bbd.toString();                

            Document finalDocument = Jsoup.parse(kkj);
            Element ClubName = finalDocument.getElementsByClass("title").first();
            String NameOfClub = ClubName.text();
            System.out.println(NameOfClub);    

            Element Adres = finalDocument.getElementsByClass("text").get(1);

            String[] addressParts = Adres.html().split("<br />");
            String SplitString;
            String PlaatsName;
            String Straat;
            String telNo;
            String Accommodatie;
            String Postcode;                

            Accommodatie = addressParts[0].trim();
            Straat = addressParts[1].trim();
            SplitString = addressParts[2].trim();
            telNo = addressParts[3].trim();

            String splitted[]= SplitString.split(" ");
            Postcode = splitted[0];
            PlaatsName = splitted[1];

            System.out.println(Accommodatie + " " + Straat + " " + " postcode " + Postcode + " Plaatsname " + PlaatsName+ " "+ telNo);

            Elements anchors = finalDocument.getElementsByTag("a");
            String email = anchors.get(1).text();    

            String fname = "/Users/muratcanpinar/Downloads/KNVBJ/src/knvbj/Voetbalclubs.xlsx";
            InputStream inp = new FileInputStream(fname);                       

            Workbook wb = new XSSFWorkbook(inp);

            Sheet sheet = wb.getSheetAt(0);
            Row r1 = sheet.getRow(0);

            r1.createCell(Clnummer++).setCellValue(NameOfClub);
            r1.createCell(Clnummer++).setCellValue(Accommodatie);
            r1.createCell(Clnummer++).setCellValue(Straat);
            r1.createCell(Clnummer++).setCellValue(Postcode);
            r1.createCell(Clnummer++).setCellValue(PlaatsName);
            r1.createCell(Clnummer++).setCellValue(telNo);
            r1.createCell(Clnummer++).setCellValue(email);

            wb.write(out);               
        }
  out.close();
    }           
}

With this above code i can just fill one row, en then a get this error

用上面的代码，我可以填满一行，然后就会得到这个错误。

Exception in thread "main" org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException: Fail to save: an error occurs while saving the package : The part /docProps/app.xml fail to be saved in the stream with marshaller org.apache.poi.openxml4j.opc.internal.marshallers.DefaultMarshaller@f46fdc1
    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 knvbj.KNVBJ.main(KNVBJ.java:101)
Caused by: org.apache.poi.openxml4j.exceptions.OpenXML4JException: The part /docProps/app.xml fail to be saved in the stream with marshaller org.apache.poi.openxml4j.opc.internal.marshallers.DefaultMarshaller@f46fdc1
    at org.apache.poi.openxml4j.opc.ZipPackage.saveImpl(ZipPackage.java:470)
    ... 3 more
Java Result: 1

Can somebody tell me what I am doing four? Thanks a lot.

有人能告诉我我在做什么吗?非常感谢。

8 个解决方案

#1

The problem lies in your FileOutputStream variable out being used more than once for the same Workbook. Opening and closing the FileOutputStream out within the loop fix your exception. POI, and/or the xml/zip library, don't like to use the same stream more than once.

问题在于您的FileOutputStream变量在同一工作簿中多次使用。在循环中打开和关闭FileOutputStream，可以修复您的异常。POI和/或xml/zip库，不喜欢多次使用相同的流。

If you use the same code you had with 1 loop, it works, with 2, it will crashes with the exception you have.

如果您使用与1循环相同的代码，那么它就会工作，并且它将与您所拥有的异常一起崩溃。

Here's a quick fix with a simple code to replace what the JSoup code did :

这里有一个简单的代码来替换JSoup代码所做的事情:

  private static int Clnummer = 1;

  public static void main(String[] args) throws IOException {
    for (int i = 0; i < 2; i++) {
      FileOutputStream out = new FileOutputStream("yourfilePath");
      String NameOfClub = "Potaoes club";
      System.out.println(NameOfClub);

      String PlaatsName;
      String Straat;
      String telNo;
      String Accommodatie;
      String Postcode;

      Accommodatie = "123";
      Straat = "Potatoes club street";
      telNo = "123456789";

      Postcode = "P0P0P0";
      PlaatsName = "PotatoCity";

      String email = "potatoKing@potato.com";

      String fname = "guessing this is a template file";
      InputStream inp = new FileInputStream(fname);                       

      Workbook wb = new XSSFWorkbook(inp);

      Sheet sheet = wb.getSheetAt(0);
      Row r1 = sheet.getRow(0);

      r1.createCell(Clnummer++).setCellValue(NameOfClub);
      r1.createCell(Clnummer++).setCellValue(Accommodatie);
      r1.createCell(Clnummer++).setCellValue(Straat);
      r1.createCell(Clnummer++).setCellValue(Postcode);
      r1.createCell(Clnummer++).setCellValue(PlaatsName);
      r1.createCell(Clnummer++).setCellValue(telNo);
      r1.createCell(Clnummer++).setCellValue(email);

      wb.write(out);
      out.close();
    }
  }
}

#2

In the current code, you are trying to write the ClubInformation.xlsx into one sheet of Voetbalclubs.xlsx . Thus it is giving an error. (xslx is a xml format, thus you get error while writing /docProps/app.xml).

在当前的代码中，您正在尝试编写ClubInformation。xlsx为一页的Voetbalclubs。xlsx。因此，它给出了一个错误。(xslx是一种xml格式，因此在编写/docProps/app.xml时出错)。

I have modified your code as below. Change the line List<String> urlList = Arrays.asList("http://google.com"); as per your need. Let me know if this works

我已经修改了你的代码如下。更改行列表 urlList = Arrays.asList("http://google.com");根据你的需要。如果可以的话让我知道。

public class KNVBJ {

private static int Clnummer=1;
    public static void main(String[] args) throws IOException {
       FileOutputStream out = new FileOutputStream("ClubInformation.xlsx");
        List<String> urlList = Arrays.asList("http://google.com");
        urlList.get(0);
        for (String url : urlList) {
            System.out.println("url: " + url);
        }
        String fname = "Voetbalclubs.xlsx";
        FileOutputStream output = new FileOutputStream(fname); 
        for (int i = 0; i < urlList.size(); i++) {
            Document doc = Jsoup.connect(urlList.get(i))
                    .data("query", "Java")
                    .userAgent("Mozilla")
                    .cookie("auth", "token")
                    .timeout(3000)
                    .post();

            Element content1 = doc.getElementsByClass("details").first();
            String body = content1.toString();
            Document docb = Jsoup.parseBodyFragment(body);
            Element bbd = docb.body();
            String kkj = bbd.toString();                

            Document finalDocument = Jsoup.parse(kkj);
            Element ClubName = finalDocument.getElementsByClass("title").first();
            String NameOfClub = ClubName.text();
            System.out.println(NameOfClub);    

            Element Adres = finalDocument.getElementsByClass("text").get(1);

            String[] addressParts = Adres.html().split("<br />");
            String SplitString;
            String PlaatsName;
            String Straat;
            String telNo;
            String Accommodatie;
            String Postcode;                

            Accommodatie = addressParts[0].trim();
            Straat = addressParts[1].trim();
            SplitString = addressParts[2].trim();
            telNo = addressParts[3].trim();

            String splitted[]= SplitString.split(" ");
            Postcode = splitted[0];
            PlaatsName = splitted[1];

            System.out.println(Accommodatie + " " + Straat + " " + " postcode " + Postcode + " Plaatsname " + PlaatsName+ " "+ telNo);

            org.jsoup.select.Elements anchors = finalDocument.getElementsByTag("a");
            String email = anchors.get(1).text();    

                   Workbook wb = new XSSFWorkbook();

            Sheet sheet = wb.getSheetAt(0);
            Row r1 = sheet.getRow(0);

            r1.createCell(Clnummer++).setCellValue(NameOfClub);
            r1.createCell(Clnummer++).setCellValue(Accommodatie);
            r1.createCell(Clnummer++).setCellValue(Straat);
            r1.createCell(Clnummer++).setCellValue(Postcode);
            r1.createCell(Clnummer++).setCellValue(PlaatsName);
            r1.createCell(Clnummer++).setCellValue(telNo);
            r1.createCell(Clnummer++).setCellValue(email);

            wb.write(output);        

        }
  out.close();
    }           
}

#3

Make a test program of the last 13 lines using fixed sane values. If this fails as well the problem is most likely the Input Template. If this works the problem are the values you get from Soup. Print them so see if there are any strange values.

使用固定的正常值来测试最后13行的测试程序。如果失败，问题很可能是输入模板。如果这是可行的，问题是你从汤中得到的价值。打印出来，看看有没有什么奇怪的值。

Posting only the smaller 13 lines program will also increase the chance of getting answers. And of course you can try to use another Voetbalclubs.xlsx file for fun, too.

只发布更小的13行程序也会增加得到答案的机会。当然，你可以尝试使用其他的Voetbalclubs。xlsx文件也很有趣。

#4

First: It's better to start with a working example and work your way from there. So start with the sample code that writes a simple string to a single cell a new sheet, then write to an existing sheet on a local filesystem, and only then write data you've parsed from the web. This way, when you run into problems, you've a better idea where to look for a solution.

首先，最好从一个工作的例子开始，然后从那里开始工作。因此，从编写简单字符串到单个单元格的示例代码开始，然后在本地文件系统上写入现有的表，然后只写从web解析的数据。这样，当你遇到问题的时候，你会有更好的想法去寻找解决方案。

The exception you're listing is the generic exception that gets thrown by ZipPackage when saving fails:

您所列出的异常是在保存失败时由ZipPackage抛出的通用异常:

if (!defaultPartMarshaller.marshall(part, zos))
    throw new OpenXML4JException("The part " + part.getPartName().getURI()
    + " fail to be saved in the stream with marshaller " + defaultPartMarshaller);

So the marshall method on the defaultPartMarshaller returns false and the internal exception which is the cause of the failure is lost. The DefaultMarshaller does not do much, it simply asks the part to save itself to the OutputStream.

因此，在defaultPartMarshaller上的marshall方法返回false，而内部异常是失败的原因。DefaultMarshaller并没有做太多工作，它只是要求部分将自己保存到OutputStream。

From there it gets a little less certain what kind of PackagePart is being saved. But for instance the ZipPartMarshaller catches any exceptions that occur and logs them before returning false:

从那里它就不太确定什么类型的PackagePart正在被保存。但是，例如，ZipPartMarshaller捕获任何发生的异常并在返回false之前记录它们:

try {
    ...
} catch (IOException ioe) {
    logger.log(POILogger.ERROR,"Cannot write: " + part.getPartName() + ": in ZIP",
        ioe);
    return false;
}

So could you take a look at the rest of the output, see if any more relevant info gets logged before this exception?

那么，您是否可以查看一下剩余的输出，看看在这个异常之前是否有更多的相关信息被记录下来?

If you cannot find more relevant logging, this is quite normal cause by default, the logger is a NullLogger which doesn't log a thing. Could you set the runtime property org.apache.poi.util.POILogger=org.apache.poi.util.SystemOutLogger (for example by starting java with command line argument -Dorg.apache.poi.util.POILogger=org.apache.poi.util.SystemOutLogger) and see if this produces more logging?

如果您无法找到更多相关的日志记录，这是很正常的，因为默认情况下，logger是一个不记录任何东西的NullLogger。可以设置运行时属性org. apache.poilogger = org.apache.poiutil。SystemOutLogger(例如，用命令行参数- dorg.apache.poilogger = org.apache.poilogger = org.apache.poilogger)来启动java，看看这会不会产生更多的日志记录?

#5

For me this appears to have been caused by a timeout in AWS, closing the output stream. the error message is not helpful and misleading. That's the best I could come up with so far from available info.

对我来说，这似乎是因为AWS的超时，关闭了输出流。错误信息没有帮助和误导。这是迄今为止我所能想到的最好的信息。

#6

I was getting a similar error when the file used to create the output stream already had data. If you are looking to append data to the file, you must indicate so in the file output stream object:

当用于创建输出流的文件已经有数据时，我也遇到了类似的错误。如果要将数据追加到文件中，则必须在文件输出流对象中指出:

FileOutputStream out = new FileOutputStream("/Users/muratcanpinar/Downloads/KNVBJ/build/classes/knvbj/ClubInformation.xlsx", true);

When you do this, wb.write(out) should work as expected.

当你这样做时，wb.write(out)应该按照预期工作。

#7

package knvbj;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.nodes.TextNode;
import org.jsoup.select.Elements;

public class KNVBJ {

private static int Clnummer=1;
    public static void main(String[] args) throws IOException {       
        List<String> urlList = ReadXlsx.readXlsx();
        urlList.get(1);
        for (String url : urlList) {
        System.out.println("url: " + url);
    }

    for (int i = 0; i < urlList.size(); i++) {
        Document doc = Jsoup.connect(urlList.get(i))
                .data("query", "Java")
                .userAgent("Mozilla")
                .cookie("auth", "token")
                .timeout(3000)
                .post();

        Element content1 = doc.getElementsByClass("details").first();
        String body = content1.toString();
        Document docb = Jsoup.parseBodyFragment(body);
        Element bbd = docb.body();
        String kkj = bbd.toString();                

        Document finalDocument = Jsoup.parse(kkj);
        Element ClubName = finalDocument.getElementsByClass("title").first();
        String NameOfClub = ClubName.text();
        System.out.println(NameOfClub);    

        Element Adres = finalDocument.getElementsByClass("text").get(1);

        String[] addressParts = Adres.html().split("<br />");
        String SplitString;
        String PlaatsName;
        String Straat;
        String telNo;
        String Accommodatie;
        String Postcode;                

        Accommodatie = addressParts[0].trim();
        Straat = addressParts[1].trim();
        SplitString = addressParts[2].trim();
        telNo = addressParts[3].trim();

        String splitted[]= SplitString.split(" ");
        Postcode = splitted[0];
        PlaatsName = splitted[1];

        System.out.println(Accommodatie + " " + Straat + " " + " postcode " + Postcode + " Plaatsname " + PlaatsName+ " "+ telNo);

        Elements anchors = finalDocument.getElementsByTag("a");
        String email = anchors.get(1).text();    

        String fname = "/Users/muratcanpinar/Downloads/KNVBJ/src/knvbj/Voetbalclubs.xlsx";
        InputStream inp = new FileInputStream(fname);                       

        Workbook wb = new XSSFWorkbook(inp);

        Sheet sheet = wb.getSheetAt(0);
        Row r1 = sheet.getRow(0);

        r1.createCell(Clnummer++).setCellValue(NameOfClub);
        r1.createCell(Clnummer++).setCellValue(Accommodatie);
        r1.createCell(Clnummer++).setCellValue(Straat);
        r1.createCell(Clnummer++).setCellValue(Postcode);
        r1.createCell(Clnummer++).setCellValue(PlaatsName);
        r1.createCell(Clnummer++).setCellValue(telNo);
        r1.createCell(Clnummer++).setCellValue(email);                          
    }
FileOutputStream out = new FileOutputStream("/Users/muratcanpinar/Downloads/KNVBJ/build/classes/knvbj/ClubI nformation.xlsx",true);
wb.write(out); 
out.close();
    }           
}

you need to create output Stream after you created all cells,then write them to the file. see the codes in details.

您需要在创建所有单元格之后创建输出流，然后将它们写到文件中。详见代码。

#8

-1

Write below sentence outside for loop

在下面的句子中写出循环语句。

wb.write(out);

wb.write(出);

#1