How to fill in Excel file using java

Question

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.

Answer 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 = "[email protected]";

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

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

Answer 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.