aspose file tools*
The moose likes I/O and Streams and the fly likes Using POI - Connection reset by peer Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » I/O and Streams
Bookmark "Using POI - Connection reset by peer" Watch "Using POI - Connection reset by peer" New topic
Author

Using POI - Connection reset by peer

Pat Flickner
Ranch Hand

Joined: Nov 17, 2003
Posts: 173
I'm successfully creating Excel spreadsheets usiing Apache's POI, but I've just hit an odd snag: I'm getting "Connection reset by peer: socket write error" when I click the button to create an Excel spreadsheet after the process determines that the file is too large to process.

I have two basic types of web reports: one that shows the report in its entirety and one that shows only three metrics at a time with the option to display the entire report. If the report is too large for the browser (more than 1,000 lines and 180 columns), it disables the Show All button, leaving the user with the Export to Excel button only (displayed at all times). If you click Export to Excel here, it works like a charm. Matter of fact, it works everywhere else I've placed it on the list.

However, When I go to execute the same script from an unloaded report because of its size, it goes through everything just fine until it hits the "write(out);" line and then it blows up. It doesn't blow up, though, if I click Cancel and then click the Export to Excel button at the top. I would like to give the user the option of executing the report as soon as they click OK. Any clues? Thanks.

Regards,
Pat Flickner
[ November 14, 2006: Message edited by: Pat Flickner ]
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8997
    
    9

That sure is a poser, but you left out the two most important pieces of information: the code where the exception takes place and the exception stack trace from the server logs. You've got a lot of variables in this problem and identifying the exact problem is the first step to solving it.


[How To Ask Questions On JavaRanch]
Pat Flickner
Ranch Hand

Joined: Nov 17, 2003
Posts: 173
Well, barkeep, I'll get that info to you as soon as I get back home. At a conference 'til Monday, so I reckon you'll get that info on Tuesday barrin' any trouble.

And I never ask easy questions.

Oh, there's no stack trace, I do know that, just an exception. Basically what's happening is that if I don't complete the page somehow, it thinks the connection is still open. I proved it by loading an alternate page should the report be too large that contained a button to submit the excel report instead of executing an alert. And it just hit me how to force it to do this correctly: By letting the process complete and when done, use the last portion to execute an out.println with the quickie javascript code inserted; apparently the javascript is executing before the page is done, and so the connection is still open.

Don't worry, I'll still send you the code. Can't bypass showin' off my own cleverness . Anywhooo....

If some major inconvenience like work prevents me from getting to you sooner, y'all have a great Thanksgiving. 'Night.
Pat Flickner
Ranch Hand

Joined: Nov 17, 2003
Posts: 173
okay, here's the code (sorry it took so long -- had a bunch of stuff to make up when I got back):


public void processExcel(
String appID,
ReportInfoObject rptInfo,
ServletOutputStream out,
HttpServletRequest request)
{
try
{
HttpSession session = request.getSession();
ReportDataServiceIF ds =
ReportInfoObject.getReportDataService("PooledReport", appID);
String token = request.getParameter("tokenizedCriteria2");

//create new workbook
HSSFWorkbook wb = new HSSFWorkbook();

//create new sheet
// SHEET NAME CANNOT CONTAIN /\?* ECT.
String fileName = appID + " Report";
HSSFSheet s = wb.createSheet(fileName);

// set the sheet with the filename at the bottom
wb.setSheetName(0, fileName);

//declare a row object reference
HSSFRow r = null;

//declare a cell object reference
HSSFCell c = null;

// create a font object with normal font settings
HSSFFont f = wb.createFont();
f.setFontHeightInPoints((short) 10);
f.setColor((short) HSSFFont.COLOR_NORMAL);

// create a font object with bold font settings for headers
HSSFFont fheader = wb.createFont();
fheader.setFontHeightInPoints((short) 10);
fheader.setColor((short) HSSFFont.COLOR_NORMAL);
fheader.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

// create a cell style objects
// header normal text
HSSFCellStyle txt = wb.createCellStyle(); //text style
// header text with bold and background color cell style
HSSFCellStyle headercs = wb.createCellStyle();
// label bold style
HSSFCellStyle labelcs = wb.createCellStyle();
// table bold center cell style
HSSFCellStyle tablecs = wb.createCellStyle();

// set up the label's font
labelcs.setFont(fheader);

// set up the normal header text font
txt.setFont(f);

// set up the header font
headercs.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
headercs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headercs.setFont(fheader);

// set up the table font
tablecs.setFont(fheader);
tablecs.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
tablecs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
tablecs.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// init the row and cell numbers
short rownum = 0, cellnum = 0;

r = s.createRow(rownum++);

// Set the report name across the top -- merge all five columns
// so that the user can easily see the controlling report name
c = r.createCell(cellnum++);
c.setCellStyle(tablecs);
c.setCellValue("Report Name : " + rptInfo.getReportName());
s.addMergedRegion(
new Region(
(short) (rownum - 1),
(short) 0,
(short) (rownum - 1),
(short) 4));

// extract the primary labels for the report
CommonUtility commUtil =
new CommonUtility(
(String) request.getParameter("tokenizedCriteria3"));
String label[] = commUtil.getLabels();
String value[] = commUtil.getValues();
commUtil = null;

// run through the remaining labels and load them to the report
for (int i = 0; i < label.length; i++)
{
String valOut =
rptInfo.getReportParameters().getProperty(value[i]);

// set up the label on a new row
r = s.createRow(rownum++);

// descriptive label; boldface
c = r.createCell((short) 0);
c.setCellStyle(labelcs);
c.setCellValue(label[i] + ": ");

// label value; contains information pertinant to report
// selections
c = r.createCell((short) 1);
c.setCellStyle(txt);
c.setCellValue(valOut);

// span the field four columns starting in the second column
s.addMergedRegion(
new Region(
(short) (rownum - 1),
(short) 1,
(short) (rownum - 1),
(short) 4));
}
// End of Header space

//spacer
rownum++;

// Get the currency definition
// first, check for multiple markets; if multiple, currency
// is always US dollar
boolean rollMarkets = false;
if (rptInfo.getReportParameters().getProperty("MARKET_CD")
!= null)
{
String marketCodes =
rptInfo.getReportParameters().getProperty("MARKET_CD");
if (marketCodes.equalsIgnoreCase("All")
|| marketCodes.indexOf(",") > -1)
rollMarkets = true;
}

// determine whether the currency is local, US, or not present
// (this happens if no currency metrics are selected)
if (rptInfo.getReportParameters().getProperty("CURRENCY") != null)
{
String currText = null;
String currency =
rptInfo.getReportParameters().getProperty("CURRENCY");
if (currency.equals("US") || rollMarkets)
currText =
"All amounts in US dollars. "
+ rptInfo.getReportRenderer().getParameter(
"CURRENCY_RATE");
else
currText = "All amounts in local currency.";

r = s.createRow(rownum++);
cellnum = 0;
c = r.createCell(cellnum++);
c.setCellStyle(labelcs);
c.setCellValue(currText);

// span the field two columns
s.addMergedRegion(
new Region(
(short) (rownum - 1),
(short) 0,
(short) (rownum - 1),
(short) 1));
}
// End currency definition

// Add disclaimer if it exists
String disclaimer = rptInfo.getDisclaimer();
if (disclaimer != null && disclaimer.length() > 0)
{
HSSFFont f1 = wb.createFont();
f1.setFontHeightInPoints((short) 10);
f1.setColor((short) HSSFFont.COLOR_RED);
f1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle disc = wb.createCellStyle();
disc.setFont(f1);
disc.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
disc.setFillBackgroundColor(HSSFColor.WHITE.index);
disc.setFillForegroundColor(HSSFColor.WHITE.index);
disc.setWrapText(true);
rownum++;
r = s.createRow(rownum++);
c = r.createCell((short) 0);
c.setCellStyle(disc);
c.setCellValue(disclaimer);
s.addMergedRegion(
new Region(
(short) (rownum - 1),
(short) 0,
(short) (rownum ),
(short) 4));
rownum++;
}

// if the resultset has data, create the report; otherwise,
// return "No Data Found"; this can happen if there is no data
// but the user elects to create the excel report without having
// first generated a normal report
if (rptInfo.getReportRenderer().hasData())
{
//call common method
processExcelDrillArchTemplate(
s,
r,
c,
headercs,
labelcs,
tablecs,
txt,
rownum,
rptInfo);
}
else
{
r = s.createRow(rownum++); //new row for a new table
cellnum = 0;

c = r.createCell(cellnum++);
c.setCellStyle(labelcs);
c.setCellValue("No Data Found");
}

wb.write(out);
out.close();
}
catch (IOException e)
{
System.out.println(
"IOException creating the Excel report : " + e.toString());
}
catch (DataServiceException e)
{
System.out.println(
"DataServiceException creating the Excel report : "
+ e.toString());
}
catch (SQLException e)
{
System.out.println(
"SQLException creating the Excel report : " + e.toString());
}
catch (BusinessLogicException e)
{
System.out.println(
"BusinessLogicException creating the Excel report : "
+ e.toString());
}
}


/***************************************************************************
* Method: processExcelDrillArchTemplate
* Params: HSSFSheet, HSSFRow, HSSFCell, HSSFCellStyle, HSSFCellStyle,
* HSSFCellStyle, HSSFCellStyle, short rownum, ReportTable
*
* Return: void
*
* Usage: This method takes already constructed HSSF object and creates
* the common drill down architecture data layout in excel. This includes
* the YAXIS and XAXIS Header and the Data Structure Layout. The process
* is pretty much common to all Portfolio based apps. This method may be
* replaced once a session ejb handles a user-define excel object to either
* output to web or pass to file on disk.
*
* Author: Patricia Flickner
*
* Last Modified:
*
**************************************************************************/
public void processExcelDrillArchTemplate(
HSSFSheet s,
HSSFRow r,
HSSFCell c,
HSSFCellStyle headercs,
HSSFCellStyle labelcs,
HSSFCellStyle tablecs,
HSSFCellStyle txt,
short rownum,
ReportInfoObject rptInfo)
{
short cellnum;

Map sortedReportTables =
new TreeMap(
((ReportDataGridIF) rptInfo.getReportRenderer())
.getReportTables());

Iterator it = sortedReportTables.keySet().iterator();
int counter = 0;

while (it.hasNext())
{
counter++;
ReportTable table =
(ReportTable) sortedReportTables.get((String) it.next());

int dataLengthArray[] = new int[table.xAxis[0].length];

cellnum = 0;
rownum++;
r = s.createRow(rownum++);

// write the header data
// set the main header to be bold and colorful to match the Ref App
for (int j = 0; j < table.yAxisHead[0].length; j++)
{
c = r.createCell(cellnum++);
c.setCellStyle(headercs);
c.setCellValue(table.yAxisHead[0][j]);
}

for (int j = 0; j < table.xAxis[0].length; j++)
{
c = r.createCell(cellnum++);
c.setCellStyle(tablecs);
c.setCellValue(table.xAxis[0][j]);

// set column to largest column width
if (dataLengthArray[j] < table.xAxis[0][j].length())
{
dataLengthArray[j] = table.xAxis[0][j].length();
s.setColumnWidth(
cellnum,
(short) ((dataLengthArray[j] * 16)
/ ((double) 1 / 25)));
}
}

//do this for all tables and dynamically set array lenghts
int yAxislength[];

//write data to file
for (int i = 0; i < table.data.length; i++)
{
cellnum = 0;
r = s.createRow(rownum++); //new row for data

// create the y-axis (vertical-running) columns --
// these columns must be text format -- do not use
// the NumberFormat on the yAxis cells
for (int j = 0; j < table.yAxis[i].length; j++)
{
yAxislength = new int[table.yAxis[i].length];

String tableData = null;
if (table.yAxis[i][j] == null)
tableData = "";
else
tableData = table.yAxis[i][j];

if (!tableData.equals("blank"))
{
c = r.createCell(cellnum++);
c.setCellStyle(labelcs);
c.setCellValue(tableData);

// set the metric length to be the maximum width
if (yAxislength[j] < tableData.length())
{
yAxislength[j] = tableData.length();
short holder;

s.setColumnWidth(
(short) j,
(short) ((yAxislength[j] * 16)
/ ((double) 1 / 25)));
}
}
}

// spin through the data and load the data elements to the
// report (horizontal)
for (int j = 0; j < table.data[i].length; j++)
{
c = r.createCell(cellnum);

String tableData = null;
if (table.data[i][j] == null)
tableData = "";
else
{
tableData = table.data[i][j];

// format the value as a number; if it is not a number,
// it will default to the actual value
try
{
NumberFormat numberFormat =
NumberFormat.getInstance();
c.setCellValue(
new Double(
numberFormat.parse(tableData).toString())
.doubleValue());
}
catch (ParseException pe)
{
c.setCellValue(tableData);
}

// set column to largest column width
if (dataLengthArray[j] < tableData.length())
{
dataLengthArray[j] = tableData.length();
s.setColumnWidth(
cellnum,
(short) ((dataLengthArray[j] * 16)
/ ((double) 1 / 25)));
}
}
cellnum++;
}
}
}
}


Nothing special. It works great if I complete the task of creating the report page, even if it's just to display just a button that tells the user that the report must be sent directly to Excel. The ReportInfoObject just holds all the information to build the Excel report. Somehow it thinks that because I haven't finished building the page that the connection is still open. I'm sure there's a way to close the socket connection before getting the Excel report, but I don't know how to do that.

And I have a new issue (kind of related) that I'd never noticed before; if the user decides to cancel the download of an Excel report, I get a socket error: java.net.SocketException: Connection reset by peer: socket write error. It doesn't happen all the time, but it seems to be happening more frequently since I've been playing around with using Excel more extensively (and then canceling the downloads). Is there a way to prevent this since MS seems to be controlling it? Thanks. Here's the stack:


at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)
at java.net.SocketOutputStream.write(SocketOutputStream.java:157)
at com.ibm.ws.io.Stream.write(Stream.java:26)
at com.ibm.ws.io.WriteStream.flushMyBuf(WriteStream.java:145)
at com.ibm.ws.io.WriteStream.flush(WriteStream.java:137)
at com.ibm.ws.http.ResponseStream.flush(ResponseStream.java:295)
at com.ibm.ws.io.WriteStream.flush(WriteStream.java:139)
at com.ibm.ws.webcontainer.http.HttpConnection.flush(HttpConnection.java:381)
at com.ibm.ws.webcontainer.srp.SRPConnection.flush(SRPConnection.java:242)
at com.ibm.ws.webcontainer.srt.SRTOutputStream.flush(SRTOutputStream.java:45)
at com.ibm.ws.webcontainer.srt.BufferedServletOutputStream.flushBytes(BufferedServletOutputStream.java:370)
at com.ibm.ws.webcontainer.srt.BufferedServletOutputStream.flush(BufferedServletOutputStream.java:343)
at com.ibm.ws.webcontainer.srt.BufferedServletOutputStream.finish(BufferedServletOutputStream.java:169)
at com.ibm.ws.webcontainer.srt.BufferedServletOutputStream.close(BufferedServletOutputStream.java:433)
at com.ibm.ws.webcontainer.srt.SRTServletResponse.closeResponseOutput(SRTServletResponse.java:1418)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:247)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:125)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:300)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:246)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:652)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:448)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:936)


Have a great day. Hope your turkey day was awesome. Pat
[ November 29, 2006: Message edited by: Pat Flickner ]
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8997
    
    9

I'm gonna be honest. I'm not going to read all that code. Please try to Isolate The Problem. Also Use Code Tags to preserve your formatting. Makes for an easier read.
As for this error:

if the user decides to cancel the download of an Excel report, I get a socket error: java.net.SocketException: Connection reset by peer: socket write error.

That sounds like your code is functioning correctly. The user closes the client end of the socket and the server side can't write to a closed socket. I have the same thing going on with a PDF conversion servlet and I just catch that particular exception and ignore it. Are you sure that in such cases you are cleaning up all your resources properly?
Pat Flickner
Ranch Hand

Joined: Nov 17, 2003
Posts: 173
Thanks, and no, I'm not sure I'm catching all the errors properly. I know this is something I can ignore, but I don't know how to catch the write (right) exception. I didn't think you'd read all the code, and my brain isn't functioning -- I have refridgerator symdrome (my son calls it that) where I can look right at something and not see it . I appreciate the help.
Pat Flickner
Ranch Hand

Joined: Nov 17, 2003
Posts: 173
Thanks for the heads-up. I caught the one problem, which generated what I thought was a javax.servlet.ServletException, but it was actually the original program that I inherited throwing that exception. I just added some code to check to see if the exception was caused by a connection reset by peer and then bypass. Anything else will throw the error, in which case, I'll check that. Cool! Thanks very much for the clues.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Using POI - Connection reset by peer