XLSX file getting corrupted when trying to write data into multiple sheets in JAVA

Question

I'm new to JAVA. I have a requirement to load multiple files in a specific folder into an excel xlsx (not xls file because the input files have more than 65k records) file different sheets. Below is the code that I wrote. The xlsx file is getting corrupted, it is not opening. Can you please help?

      public class twotxttoxlsx{
             public static void main(String[]args){
             try{

               File folder = new File("C:/txttoxls/");
            File[] listOfFiles = folder.listFiles();
                  XSSFWorkbook workbook=new XSSFWorkbook();

            for (File file : listOfFiles) {


   if (file.isFile()) {

       String thisline;
       ArrayList<String> al = null;
       ArrayList<ArrayList<String>> arlist = new ArrayList<ArrayList<String>>();

       XSSFSheet sheet =  workbook.createSheet(file.getName());  
           FileInputStream fis = new FileInputStream(file);
           System.out.println("Got here");
           BufferedReader br = new BufferedReader(new InputStreamReader(fis));

           while ((thisline = br.readLine()) != null) {
               al = new ArrayList<String>();
               String strar[] = thisline.split("#");

               for (int j = 0; j < strar.length; j++) { 
                   for (int k = 0; k < arlist.size(); k++) {

                       ArrayList<String> ardata = (ArrayList<String>) arlist.get(k);

                       XSSFRow row = sheet.createRow((short) k);


                       for (int p = 0; p < ardata.size(); p++) {

                           XSSFCell cell = row.createCell((short) p);
                           cell.setCellValue(ardata.get(p).toString());

                       }
                   }

                   al.add(strar[j]);

               } 

             arlist.add(al);
           }

           fis.close();  
           FileOutputStream fileOut = new FileOutputStream("C:/txttoxls/Test.xlsx");
           workbook.write(fileOut);
           fileOut.flush();
           fileOut.close();
           br.close();
   }
   }

           System.out.println("Your excel file has been generated!");

            } catch ( Exception ex ) {
                 System.out.println(ex);

                }
                }
  }

Edited on 4/27: working code but performance issue exists.

 import java.io.*;
 import java.util.ArrayList; 
 import org.apache.poi.xssf.usermodel.XSSFSheet;
 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 import org.apache.poi.xssf.usermodel.XSSFRow;
 import org.apache.poi.xssf.usermodel.XSSFCell;


public class createXLSXfileTest{
public static void main(String[]args){

    XSSFWorkbook workbook=new XSSFWorkbook();
    XSSFSheet sheet = (XSSFSheet) workbook.createSheet("sheet1");  
    XSSFSheet sheet1 = (XSSFSheet) workbook.createSheet("Sheet2");
    try{

           File file = new File("C:/Users/Desktop/file1.txt");

          FileOutputStream fileOut = new FileOutputStream("C:/Users/Desktop/test.xlsx");

      if (file.isFile()) {

       String thisline;
       ArrayList<String> al = null;
       ArrayList<ArrayList<String>> arlist = new ArrayList<ArrayList<String>>();


           FileInputStream fis = new FileInputStream(file);
           System.out.println("loading 1st file");
           BufferedReader br = new BufferedReader(new InputStreamReader(fis));

           while ((thisline = br.readLine()) != null) {
               al = new ArrayList<String>();
               String strar[] = thisline.split("#");

               for (int j = 0; j < strar.length; j++) { 
                   for (int k = 0; k < arlist.size(); k++) {

                       ArrayList<String> ardata = (ArrayList<String>) arlist.get(k);

                       XSSFRow row = sheet.createRow((short) k);


                       for (int p = 0; p < ardata.size(); p++) {

                           XSSFCell cell = row.createCell((short) p);
                           cell.setCellValue(ardata.get(p).toString());

                       }
                   }

                   al.add(strar[j]);

               } 

             arlist.add(al);
           }

           fis.close();  

      }
      File file1 = new File("C:/Users/Desktop/file2.txt");
      if (file1.isFile()) {

          String thisline;
          ArrayList<String> al = null;
          ArrayList<ArrayList<String>> arlist = new ArrayList<ArrayList<String>>();


              FileInputStream fis = new FileInputStream(file1);
              System.out.println("loading 2nd file");
              BufferedReader br = new BufferedReader(new InputStreamReader(fis));

              while ((thisline = br.readLine()) != null) {
                  al = new ArrayList<String>();
                  String strar[] = thisline.split("#");

                  for (int j = 0; j < strar.length; j++) { 
                      for (int k = 0; k < arlist.size(); k++) {

                          ArrayList<String> ardata = (ArrayList<String>) arlist.get(k);

                          XSSFRow row = sheet1.createRow((short) k);


                          for (int p = 0; p < ardata.size(); p++) {

                              XSSFCell cell = row.createCell((short) p);
                              cell.setCellValue(ardata.get(p).toString());

                          }
                      }

                      al.add(strar[j]);

                  } 

                arlist.add(al);
              }

              fis.close();  
                             workbook.write(fileOut);
              fileOut.flush();
              fileOut.close();
              br.close();


      System.out.println("Your excel file has been generated!");


   }} catch ( Exception ex ) {
   System.out.println(ex);

  }

  }
 }

Answer 1

The only thing I can see is that your sheet name could possibly be a problem.

XSSFSheet sheet = workbook.createSheet(file.getName());

Excel sheet names are extremely restrictive. Maximum length 31, and many special characters are not allowed, including /[]:\? and many more. Consider writing a "toWorksheetName" function to clean the filename up into a legal sheet name.

Another thing you can try is to attempt to read the XLS with POI. That might help narrow down the problem. Help is here, under "Working with .xlsx files".

On a side note: You do not need to close fis; closing br is enough. Close() is passed down from the decorator to the wrapped streams. This won't break anything though.