Create an excel file for users to download using Apache POI

Question

I'm able to create an excel file using apache poi. however, i want users to be able to download this as a "true" excel file. the effect i want to achieve would be to have a popup box allowing the user to download the file. this is similar to using

<%@ page contentType="application/vnd.ms-excel" pageEncoding="ISO-8859-1"%> 
<%response.setHeader("Content-Disposition", "attachment;filename=myfile.xls"); %>

with one critical exception: i must allow the user to download a proper excel file. i read somewhere the above code simply says to the client that the server is sending an excel file

BalusC · Accepted Answer · 2013-07-05T15:20:52.547

40

Do the job in a normal servlet instead of a JSP file. A JSP file is meant for dynamically generating HTML code and is using a character writer for that instead of a binary output stream and would thus only corrupt your POI-generated Excel file which is in essence a binary stream.

So, basically all you need to do in the doGet() method of the servlet is the following:

response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=filename.xls");
HSSFWorkbook workbook = new HSSFWorkbook();
// ...
// Now populate workbook the usual way.
// ...
workbook.write(response.getOutputStream()); // Write workbook to response.
workbook.close();

Now, to download it, invoke the servlet by its URL instead of the JSP file.

edited Jul 05 '13 at 15:20

answered Jun 27 '12 at 13:27

BalusC

1,082,665
372
3,610
3,555

writableworkbook is part of the jexcel api right? this ought to work even if im using poi right? – user571099 Jun 27 '12 at 13:41
@user571099 the workbook is an `HSSFWorkbook` here, not a `WritableWorkbook`. – Jean-François Savard Mar 26 '15 at 14:38
This was exactly what I was looking for! I assume this can be similarily done with Jersey RESTful web service? – Brian Jul 12 '15 at 22:05
What kind of object is "response" instance? From which class was it instantiated? – Origamer7 Apr 01 '16 at 10:21
@Stravos: in your servlet. If you have no clue what a "servlet" is, step back, hover the [servlets] tag below the question until black popbox shows up and click there in *info* link. – BalusC Apr 01 '16 at 10:22
I still don't get it, so I rephrase my question and split it into two parts: Part 1: Which library should I import in order to be able to create an instance of "response". Part 2: From which class (of the aforementioned unknown library) should I create the "response" instance? – Origamer7 Apr 04 '16 at 08:06
@Stravos: if you don't have a servlet in first place, then you have a major http://xyproblem.info. Take a step back and ask a new question if you still stucks. Do not use comments section for offtopic questions. – BalusC Apr 04 '16 at 08:08

score 21 · Answer 2 · answered Jun 28 '12 at 16:31

While it is true that it is more usual to write a binary attachment using a servlet rather than a jsp, it is certainly possible to write a binary attachment from a jsp. And the advantage of doing so is that you need not worry about configuring web.xml or reloading your application. That can be an important consideration, depending on your web server environment.

Here is an example jsp that uses poi to send a binary attachment to a browser.

<%@page import="org.apache.poi.hssf.usermodel.*" %><%@page import="java.io.*" %><%

// create a small spreadsheet
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue("Some text");

// write it as an excel attachment
ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
wb.write(outByteStream);
byte [] outArray = outByteStream.toByteArray();
response.setContentType("application/ms-excel");
response.setContentLength(outArray.length);
response.setHeader("Expires:", "0"); // eliminates browser caching
response.setHeader("Content-Disposition", "attachment; filename=testxls.xls");
OutputStream outStream = response.getOutputStream();
outStream.write(outArray);
outStream.flush();

%>

The important trick is to make sure there is only one line with all your imports and other directives prior to the "<%" that begins your code. Otherwise, the jsp may output some initial new lines and corrupt your output.

Also, I suggest you always set the content length. Some browsers will not work correctly if it is not set. That is why I first output my spreadsheet to a byte array, so I could set the length prior to actually sending the data.

Thank you for the answer but may I ask you where do you define the path to the xls file? I mean, how jsp knows, where to pick that xls file from? Could it work with pdf, which was created by java program before? — Ondrej Tokar, Apr 30 '15 at 19:09

score 0 · Answer 3 · answered Nov 03 '17 at 13:34

if you want to download don,t use hssf workbook it will slower and consume more space use apche poi 3.17 beta-1

SXSSFWorkbook workbook = new SXSSFWorkbook(100); 
workbook.setCompressTempFiles(true);
Sheet sh = workbook.createSheet();
//write your data on sheet

//below code will download file in browser default download folder
response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment; filename="+filename+".xlsx");
            workbook.write(response.getOutputStream());
            workbook.close();
            workbook.dispose();

For Pdf Use Itext

     Document document = new Document();
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            PdfWriter.getInstance(document, baos);
            document.open(); 
//write your code

 document.add("content");
            document.close();
            response.setHeader("Expires", "0");
            response.setHeader("Cache-Control","must-revalidate, post-check=0, pre-check=0");
            response.setHeader("Pragma", "public");
            response.setContentType("application/pdf");
            response.addHeader("Content-Disposition", "attachment; filename="+filename+".pdf");
            response.setContentLength(baos.size());
            OutputStream os = response.getOutputStream();
            baos.writeTo(os);
            os.flush();
            os.close();

Create an excel file for users to download using Apache POI

3 Answers3

Linked

Related