0

Recently upgraded from poi-2.5.1 to poi-4.1.2 and what is happening is that a blank line is being inserted as the first line in the resulting .xls file. This was determined from comparing the file that is created from poi-2.5.1 to the one generated from poi-4.1.2. When manually removing the first line, I'm able to open the spreadsheet normally, with the first line in the file you get error the file format and extension don't match.

My application runs in weblogic-12.2.1.4 it takes the table that is being displayed to the user and creates an excel spreadsheet in a jsp which is downloaded to the user.

I've tried searching for this problem and all I can find is how to remove the line when reading a spreadsheet.

the code

        OutputStream output = response.getOutputStream();
        response.setHeader("Expires", "0");
        response.setHeader("Pragma", "private");
        response.setHeader("Content-Disposition","attachment;filename=" + tableId + "xls");
        response.setContentType("application/x-download; charset=UTF-8");
    
        Workbook book = new HSSFWorkbook();
      
        CreationHelper createHelper = book.getCreationHelper();
        Sheet sheet = book.createSheet();
    ...
    ...
    ...
       
        book.write(output);
        output.close();

I've adding out.clear() and response.reset() before the code snippet and it doesn't help.

I've also tried comment everything out after Workbook book = new HSSFWorkbook();

and then doing the book.write(output); and I still get the blank line.

Here is the output with just the Workbook book = new HSSFWorkbook(); viewed in Notepad++ What the output looks like in Notepad++

rwilkins29
  • 1
  • 2

1 Answers1

0

After searching and trying different things for over a month, I determined that the problem was that I had switched the from one long line to where I could see all of the imports easily,

correct way

    <%@page import = "java.util.*,java.net.URLDecoder,org.apache.poi.hssf.usermodel.*,org.apache.poi.hssf.util.HSSFColor,org.apache.log4j.Logger"%>

incorrect way - that causes the problem. I had changed it to this way so that I could easily see all of the files being imported. There are 20 files being imported. I just reduced it to 5 for this example.

<%@page import = "java.util.*,
                  java.net.URLDecoder,
                  org.apache.poi.hssf.usermodel.*,
                  org.apache.poi.hssf.util.HSSFColor,
                  org.apache.log4j.Logger"%>

For some reason when the import statement is not in one line it puts a blank as the first row in the OutputStream

rwilkins29
  • 1
  • 2
  • Would you mind clarifying your answer? it's hard to understand the solution and an explanation of the originally observed behaviour. – seb Apr 12 '21 at 00:40
  • The originally observed behavior is that the first line in the file created is a blank line which causes excel to consider the file corrupted. – rwilkins29 Apr 13 '21 at 14:12