How to fill in Excel file using java

问题:

I have the following code to fill in the Excel file, with information that I get from the Internet using 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.

回答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.

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

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

  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 = "[e-mail]";

      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. (xlsx is a xml format, thus you get error while writing /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

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.

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.

回答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.

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

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.

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:

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?

回答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.

回答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.

回答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:

Write below sentence outside for loop

wb.write(out);