Forum:

How to output HSSF excel data in browser?

Greenhorn

Posts: 6

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi all,

I'm currently working on a web project to display various reports to the users of the system. One of the options they have is to get the report presented in the browser as an .xls file and here I'm using the Jakarta HSSF API to create the .xls files.

In order to present the file to the user, I thought about creating a temporary file but not wanting to overload the server with temporary .xls files, my original idea was to save it to the client machine. Perhaps with a pop-up prompting him for name and path of file or perhaps as a temporary internet file. But I'm not certain how exactly I go about that and though I've been reading up on it, I haven't found a solution yet.

Then I thought, why not simply write it to the client browser via the servlet/JSPs output stream? Well, I've tried a few different approaches but none have the desired output format (the text looks gibberish for some reason, perhaps due to content type or something not correct). As you can see in the code below, I've made a write-to-file test to ensure the content looks good enough and it does. It's just the view in the browser thats faulty..

So please, ladies and gents, be a sport and give a hand if you have a suggestion

-Maya

<%@ page import="java.io.*, org.apache.poi.hssf.usermodel.*"%>
<%
    // create the workbook
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFRow row = sheet.createRow(1);
    row.createCell((short)0).setCellValue("test");
    row.createCell((short)1).setCellValue("test");
    
    // get an outputStream to the wb (calling the wb.write(nameOfOutputStream) will do the writing)
    BufferedOutputStream bops = new BufferedOutputStream(response.getOutputStream());
    wb.write(bops);
    //bops.flush();
  
     // set contenttype and such
    response.setContentType("application/vnd.ms-excel");
    response.addHeader("Pragma", "No-cache");
    response.addHeader("Cache-control", "no-cache");
    response.addDateHeader("Expires",1 );
    
    // create the writer that will write to page with the stream of the wb
    Writer writer = new BufferedWriter(new OutputStreamWriter(bops));        
    writer.flush();
    
    // file test
    File file = new File("C:\\test.xls");
    FileOutputStream fops = new FileOutputStream(file);
    wb.write(fops);
    fops.flush();
    fops.close();
    
    // close what's open
    writer.close();
    bops.close();
 
%>
<html>
<body>
  <head>
    <script type="text/javascript">
    window.open("c:\\test.xls");
    </script>
  <head>
  <body>
    test
  </body>
</html>

live long and prosper

Jeanne Boyarsky

author & internet detective

Posts: 41964

911

I like...

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Maya,
I see two things:

1) You need to set those response headers before you open/write to the output stream. Otherwise the browser doesn't know it is an excel file.
2) Don't put any HTML in the page. You can ouput an excel file or an html file, but not both. The way it is written, you will wind up with some html at the bottom of your excel file.

[OCP 17 book] | [OCP 11 book] | [OCA 8 book] [OCP 8 book] [Practice tests book] [Blog] [JavaRanch FAQ] [How To Ask Questions] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2

Java 8 (verified skill)
Skill verified by Jeanne Boyarsky

JUnit 5 (verified skill)
Skill verified by Jeanne Boyarsky

maya pedersen

Greenhorn

Posts: 6

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hey Jeanne,

I tried it both ways, pre and post to the opening and writing of the streams but nothing helped. And yes, I'm sorry, I should have removed the HTML as well, it just muddled my intentions.

None the less, I found the solution myself, as it appears that placing the code for piping of excel data (or all binary data for that matter) in the jsp page itself is not desirable. The minute I did the exact same thing in my servlet class, I had the proper output read from the stream.

So to all you out there that use HSSF or likes and try to avoid the creation of files, be sure to place the code in a java class, NOT in the .jsp.. (more on http://jakarta.apache.org/poi/faq.html Q:7)

Godspeed,

Maya

live long and prosper

With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.