You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Dean Schulze <de...@yahoo.com> on 2008/05/27 23:19:18 UTC
Converting JSP that outputs CSV to HSSF gives no output
I have a JSP that creates a CSV file, which works. When I convert the JSP page
to use HSSF instead it produces no output. The JSP just produces a blank page.
With the CSV output the blank JSP popups up and is replaced by the Dialog to
open or save the .csv file.
My JSP is below.
And yes, I have verified that the two header and records Lists contain data.
Thanks.
Dean
<%@ page language="java" %>
<%@ page
import="java.util.*,java.io.OutputStream,org.apache.poi.hssf.usermodel.*" %>
<%
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment; filename=browselist.xls");
List headers =(List)request.getAttribute("headers");
List records =(List)request.getAttribute("records");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFFont headerFont = wb.createFont();
headerFont.setFontName(HSSFFont.FONT_ARIAL);
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle HeaderStyle = wb.createCellStyle();
HeaderStyle.setFont(headerFont);
int rowNum = 0;
if (headers != null) {
HSSFRow headerRow = sheet.createRow(rowNum++);
Object[] arr = headers.toArray();
for (int i=1; i<arr.length; i++) {
String s = (String)arr[i];
HSSFCell cell = headerRow.createCell((short)i);
cell.setCellValue(s);
}
}
if (records != null) {
Object[] arr = records.toArray();
for (int i=0; i<arr.length; i++) {
String record = (String)arr[i];
HSSFRow recordRow = sheet.createRow(rowNum++);
StringTokenizer st = new StringTokenizer(record,"|");
boolean skip = true;
short cellNum = 0;
while (st.hasMoreTokens()) {
String tok=st.nextToken();
if (skip) {
skip = false;
continue;
}
if(tok.equals(" "))
tok="";
HSSFCell cell = recordRow.createCell(cellNum++);
cell.setCellValue(tok);
}
}
}
OutputStream sos = response.getOutputStream();
wb.write(sos);
sos.close();
%>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Converting JSP that outputs CSV to HSSF gives no output
Posted by Dean Schulze <de...@yahoo.com>.
I found the problem. The GET I was sending includes a list of ids, and for some
use cases the list of ids exceeded 8 kbytes. Once I fixed that the HSSF worked
as expected.
Thanks for the responses.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Converting JSP that outputs CSV to HSSF gives no output
Posted by David Fisher <df...@jmlafferty.com>.
Next thing I saw is that you should remove "sos.close();" - you don't
want to close your JSP's output stream.
BTW - since Excel is a binary format and JSP is text output, you
really should do it as a servlet.
Here is a quick cut and paste of one that we use:
public class ExcelServlet extends HttpServlet {
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
response.setHeader("Expires","Thu, 01 Dec 1994 16:00:00 GMT");
response.setDateHeader("Last-Modified",
System.currentTimeMillis() );
try {
response.setHeader("Content-disposition",
"attachment;filename=" + file);
response.setContentType("application/vnd.ms-excel");
List headers =(List)request.getAttribute("headers");
List records =(List)request.getAttribute("records");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFFont headerFont = wb.createFont();
headerFont.setFontName(HSSFFont.FONT_ARIAL);
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle HeaderStyle = wb.createCellStyle();
HeaderStyle.setFont(headerFont);
int rowNum = 0;
if (headers != null) {
HSSFRow headerRow = sheet.createRow(rowNum++);
Object[] arr = headers.toArray();
for (int i=1; i<arr.length; i++) {
String s = (String)arr[i];
HSSFCell cell = headerRow.createCell((short)i);
cell.setCellValue(s);
}
}
if (records != null) {
Object[] arr = records.toArray();
for (int i=0; i<arr.length; i++) {
String record = (String)arr[i];
HSSFRow recordRow = sheet.createRow(rowNum++);
StringTokenizer st = new StringTokenizer(record,"|");
boolean skip = true;
short cellNum = 0;
while (st.hasMoreTokens()) {
String tok=st.nextToken();
if (skip) {
skip = false;
continue;
}
if(tok.equals(" "))
tok="";
HSSFCell cell = recordRow.createCell(cellNum++);
cell.setCellValue(tok);
}
}
}
OutputStream sos = response.getOutputStream();
wb.write(sos);
}
}
}
On May 28, 2008, at 10:46 AM, Dean Schulze wrote:
>
>
>
> David Fisher <dfisher <at> jmlafferty.com> writes:
>
>>
>> Dean,
>>
>> Excel is a very exact and binary format.
>>
>> You must put out no whitespace from your JSP (I recommend using a
>> servlet, but..)
>>
>> Change the top part to this:
>>
>> <%@ page language="java"
>> %><%@ page
>> import
>> ="java.util.*,java.io.OutputStream,org.apache.poi.hssf.usermodel.*"
>> %><%
>>
>> And make sure you don't have a trailing blank after the final %>
>>
>> Regards,
>> Dave
>
>
> David,
>
> Thanks for your response, but after doing what you recommend I still
> get no
> output. View Page Source shows nothing, but when I look at it with
> Firefox Web
> tools it shows
>
> <html>
> <head/>
> <body>
> <pre/>
> </body>
> </html>
>
>
> The HSSF docs don't mention using HSSF in a JSP so maybe HSSF is
> incompatible
> with JSPs.
>
> I'm going to take a look at using the Excel XML format and see if
> that works.
>
> CSV works, but it produces unformatted output.
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Converting JSP that outputs CSV to HSSF gives no output
Posted by Dean Schulze <de...@yahoo.com>.
David Fisher <dfisher <at> jmlafferty.com> writes:
>
> Dean,
>
> Excel is a very exact and binary format.
>
> You must put out no whitespace from your JSP (I recommend using a
> servlet, but..)
>
> Change the top part to this:
>
> <%@ page language="java"
> %><%@ page
> import
> ="java.util.*,java.io.OutputStream,org.apache.poi.hssf.usermodel.*"
> %><%
>
> And make sure you don't have a trailing blank after the final %>
>
> Regards,
> Dave
David,
Thanks for your response, but after doing what you recommend I still get no
output. View Page Source shows nothing, but when I look at it with Firefox Web
tools it shows
<html>
<head/>
<body>
<pre/>
</body>
</html>
The HSSF docs don't mention using HSSF in a JSP so maybe HSSF is incompatible
with JSPs.
I'm going to take a look at using the Excel XML format and see if that works.
CSV works, but it produces unformatted output.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Converting JSP that outputs CSV to HSSF gives no output
Posted by David Fisher <df...@jmlafferty.com>.
Dean,
Excel is a very exact and binary format.
You must put out no whitespace from your JSP (I recommend using a
servlet, but..)
Change the top part to this:
<%@ page language="java"
%><%@ page
import
="java.util.*,java.io.OutputStream,org.apache.poi.hssf.usermodel.*"
%><%
And make sure you don't have a trailing blank after the final %>
Regards,
Dave
On May 27, 2008, at 4:19 PM, Dean Schulze wrote:
>
> I have a JSP that creates a CSV file, which works. When I convert
> the JSP page
> to use HSSF instead it produces no output. The JSP just produces a
> blank page.
> With the CSV output the blank JSP popups up and is replaced by the
> Dialog to
> open or save the .csv file.
>
> My JSP is below.
>
> And yes, I have verified that the two header and records Lists
> contain data.
>
> Thanks.
>
> Dean
>
>
>
> <%@ page language="java" %>
> <%@ page
> import
> ="java.util.*,java.io.OutputStream,org.apache.poi.hssf.usermodel.*" %>
> <%
> response.setContentType("application/vnd.ms-excel");
> response.setHeader("Content-Disposition","attachment;
> filename=browselist.xls");
>
> List headers =(List)request.getAttribute("headers");
> List records =(List)request.getAttribute("records");
>
> HSSFWorkbook wb = new HSSFWorkbook();
> HSSFSheet sheet = wb.createSheet();
>
> HSSFFont headerFont = wb.createFont();
> headerFont.setFontName(HSSFFont.FONT_ARIAL);
> headerFont.setFontHeightInPoints((short) 10);
> headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
>
>
> HSSFCellStyle HeaderStyle = wb.createCellStyle();
> HeaderStyle.setFont(headerFont);
>
> int rowNum = 0;
>
> if (headers != null) {
>
> HSSFRow headerRow = sheet.createRow(rowNum++);
> Object[] arr = headers.toArray();
>
> for (int i=1; i<arr.length; i++) {
>
> String s = (String)arr[i];
> HSSFCell cell = headerRow.createCell((short)i);
> cell.setCellValue(s);
> }
> }
>
> if (records != null) {
>
> Object[] arr = records.toArray();
>
> for (int i=0; i<arr.length; i++) {
>
> String record = (String)arr[i];
> HSSFRow recordRow = sheet.createRow(rowNum++);
>
> StringTokenizer st = new StringTokenizer(record,"|");
> boolean skip = true;
> short cellNum = 0;
>
> while (st.hasMoreTokens()) {
>
> String tok=st.nextToken();
> if (skip) {
> skip = false;
> continue;
> }
>
> if(tok.equals(" "))
> tok="";
>
> HSSFCell cell = recordRow.createCell(cellNum++);
> cell.setCellValue(tok);
> }
> }
> }
>
> OutputStream sos = response.getOutputStream();
> wb.write(sos);
> sos.close();
>
> %>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org