You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Schene, Chris" <Ch...@experian.com> on 2015/11/04 16:16:05 UTC
Excel Files using XSSFWorkbook over a certain size display an excel
error when I open the .xls file in excel
Hi,
I am using the code below to create a very large spread sheet that is 47
rows wide.
There are a few very large strings in the rows, but for the most part the
data is fairly small.
If the .xls file is over about 1000 rows I get an error when I load the
.xls file in excel saying it needs to repair the file and I lose all the
row color and column width settings.
There is no apparent upper limit on how many rows the .xls can have, but
excel says it needs to repair after about 1000 rows.
I am using poi 3.11 and running on MAC OS>.
XSSFWorkbook wb = new XSSFWorkbook();
//add the headers
CellStyle style;
Sheet sheet = wb.createSheet("Certification");
wb.setSheetName(0, "Certification");
// addHeader(wb, 1, sheet);
//First create the header
Row headerRow = sheet.createRow((short) 0);
//for(int z = 0; z < columnNames.size(); ++z){
for(int z = 0; z < myColumnHeaders.size(); ++z){
createCell(wb, headerRow, z, CellStyle.ALIGN_CENTER,
CellStyle.VERTICAL_BOTTOM, myColumnHeaders.get(z));
}
Int myColCount = 47;
for(int i = 0; i < myColCount && i < 8000; ++i) {
ŠŠ Process each of the 47 columns
} //end for(int i = 0; ...
for(int z = 0; z < myColumnHeaders.size(); ++z){
System.out.println("sheet.autoSizeColumn(z); z = " + z);
try {
sheet.autoSizeColumn(z);
}
catch(Exception e) {}
}
} //end for z...
At the end of building up the spread sheet
Christopher Schene
Field Engineer
Global
Fraud and Identity Solutions
Experian
16260 N. 71st Street
Suite #400
Scottsdale
Arizona, USA
85254
+1 602.290.9792 mobile
+1 480.751.3928 office
chris.schene@experian.com
www.experian.com <http://www.experian.com/>
On 11/4/15, 7:18 AM, "Murphy, Mark" <mu...@metalexmfg.com> wrote:
>I do create a bunch of styles at the front, but they do not have borders.
>I would need 3 styles for column headers (just because of borders), and
>the data portion of the table would require at least 9 styles, just
>because of borders. And that assumes that the data in each cell is
>formatted the same way. Each different type of formatting, number
>formats, special highlighting, bolding, alignment would require multiple
>styles, and the program complexity needed to add table data location
>awareness to the mix (just to ensure the correct border goes with the
>correct cell) is unnecessary. The logic is much simpler, and you have to
>preload fewer styles, if you just leave the borders off, and draw them
>after the spreadsheet is built. Unfortunately that is a very slow option.
>It is POI that creates unnecessary styles due to its one property at a
>time processing of borders.
>
>What's easier to read and maintain (this is RPG that I am calling the POI
>methods from):
>
> for rowNum = 1 to something;
> row = ssSheet_createRow(sheet: rowNum);
> ss_num(row: 1: customerNumber: sty_num);
> ss_text(row: 2: customerName: sty_text);
> ss_text(row: 3: status: sty_code);
> ss_date(row: 4: createDate: *MDY): sty_date);
> endfor;
>
> ss_drawBorders(book: sheet: 1: rowNum-1: 1: 4:
> BORDER_THIN: EXTENT_INSIDE);
> ss_drawBorders(book: sheet: 1: rowNum-1: 1: 4:
> BORDER_MEDIUM: EXTENT_OUTSIDE);
>
>Or
>
> for rowNum = 1 to something;
> row = ssSheet_createRow(sheet: rowNum);
> if rowNum = 1;
> style = sty_num_tl;
> else;
> style = sty_num_l;
> endif;
> ss_num (row: 1: customerNumber: style);
> if rowNum = 1;
> style = sty_text_t;
> else;
> style = sty_text;
> endif;
> ss_text(row: 2: customerName: sty_text);
> if rowNum = 1;
> style = sty_code_t;
> else;
> style = sty_code;
> endif;
> ss_text(row: 3: status: sty_code);
> if rowNum = 1;
> style = sty_date_tr;
> else;
> style = sty_date_r;
> endif;
> ss_date(row: 4: createDate: *MDY): sty_date);
> endfor;
>
> // Still need more code to replace the styles on the last row of
>cells to get the borders right
>
>This is the simplest of examples. Most of my spreadsheets are far more
>complex with some having styles set based on the data, is the part
>discontinued, is the process late, etc. This just makes for nested if's
>in trying to decide which style should be applied to each cell. The first
>code is simpler, and if all the border properties could be set for a cell
>all at once, much quicker. Just a single search, just a single
>setCellStyleProperty, and no unused intermediate styles created.
>
>-----Original Message-----
>From: Nick Burch [mailto:apache@gagravarr.org]
>Sent: Tuesday, November 03, 2015 3:59 PM
>To: POI Users List
>Subject: Re: Drawing Borders is SLOW
>
>On Tue, 3 Nov 2015, Murphy, Mark wrote:
>> I am sure you all know this. But the problem increases as the number
>> of styles grows. In looking at the code, I am convinced that the
>> problem can be found in the fact that when borders are drawn, the cell
>> style is retrieved, the border is applied, and all styles are searched
>> for a matching style. I one is not found, then a new one is created
>
>This is the bit where I'm loosing you. Surely you create a dozen or so
>styles at the start of creating your workbook, with the various colours
>and borders that you want, then you simply apply them to your cells as
>you work through creating your workbook. You shouldn't need to be
>creating styles as you go, adding various bits of borders in to them.
>
>Styles in Excel are, due to how the file format works, scoped at the
>workbook level and not the cell level. You shouldn't therefore be
>creating styles as you go, or you'll run out of available styles!
>
>Nick
>
>---------------------------------------------------------------------
>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
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Excel Files using XSSFWorkbook over a certain size display an
excel error when I open the .xls file in excel
Posted by Dominik Stadler <do...@gmx.at>.
Yes, correct, you only need the setCellStyle() call on the cell itself then.
Dominik.
On Thu, Nov 5, 2015 at 4:30 PM, Schene, Chris <Ch...@experian.com> wrote:
> Hi,
>
> This is my logic below for creating a cell. I have several overloads: one
> for string, one for integer, one for double
>
> The getColorBasedOnStatus returns a color based on the status of a given
> row (OK-black, ERROR-RED, WARNING-AQUA)
>
> So, I assume what I need to do is create three styles:Black, Red, AQUA and
> give those styles class level scope and just use those class level objects
> rather than creating a new one for each cell.
>
> Correct?
>
>
> private short getColorBasedOnStatus(String rowState){
> short result = IndexedColors.BLACK.getIndex();
> if(rowState.compareTo("ERROR") != 0) {
> result = IndexedColors.RED.getIndex();
> }
>
> if(rowState.compareTo("WARNING") != 0) {
> result = IndexedColors.AQUA.getIndex();
> }
> return result;
> }
>
>
> /**
> * Creates a cell and aligns it a certain way.
> *
> * @param wb the workbook
> * @param row the row to create the cell in
> * @param i the column number to create the cell in
> * @param halign the horizontal alignment for the cell.
> */
> private void createCell(XSSFWorkbook wb, Row row, int i, short halign,
> short valign, String value) {
> CreationHelper ch = wb.getCreationHelper();
>
> Cell cell = row.createCell(i);
> cell.setCellValue(ch.createRichTextString(value));
> CellStyle cellStyle = wb.createCellStyle();
>
> try {
> String rowState = RowState.get(row.getRowNum()-1);
>
>
> if (rowState.compareToIgnoreCase("ok") != 0 ) {
> XSSFFont myFont= wb.createFont();
> myFont.setFontHeightInPoints((short)10);
> myFont.setFontName("Arial");
> myFont.setColor(getColorBasedOnStatus(rowState));
> myFont.setBold(true);
> myFont.setItalic(false);
> cellStyle.setFont(myFont);
> }
> } catch(Exception e) {}
>
> cellStyle.setAlignment(halign);
> cellStyle.setVerticalAlignment(valign);
>
> cell.setCellStyle(cellStyle);
> }
>
>
>
> Christopher Schene
> Field Engineer
> Global
> Fraud and Identity Solutions
>
> Experian
> 16260 N. 71st Street
> Suite #400
> Scottsdale
> Arizona, USA
> 85254
> +1 602.290.9792 mobile
> +1 480.751.3928 office
> chris.schene@experian.com
> www.experian.com <http://www.experian.com/>
>
>
>
>
>
> On 11/4/15, 10:03 AM, "Schene, Chris" <Ch...@experian.com> wrote:
>
>>"Make sure you create all of your styles outside of the loop, and re-use
>>them.
>>³
>>
>>Ah! Makes sense! I actually create a new style for each and every cell.
>>Since I can have up to 50,000 rows that is a total of 2.35 million
>>separate styles.
>>
>>Yikes!
>>
>>Thanks,
>>
>>Chris
>>
>>Christopher Schene
>>Field Engineer
>>Global
>>Fraud and Identity Solutions
>>
>>Experian
>>16260 N. 71st Street
>>Suite #400
>>Scottsdale
>> Arizona, USA
>> 85254
>>+1 602.290.9792 mobile
>>+1 480.751.3928 office
>>chris.schene@experian.com
>>www.experian.com <http://www.experian.com/>
>>
>>
>>
>>
>>
>>On 11/4/15, 9:55 AM, "Nick Burch" <ap...@gagravarr.org> wrote:
>>
>>>On Wed, 4 Nov 2015, Schene, Chris wrote:
>>>> I am using the code below to create a very large spread sheet that is
>>>>47
>>>> rows wide.
>>>>
>>>> There are a few very large strings in the rows, but for the most part
>>>>the
>>>> data is fairly small.
>>>>
>>>> If the .xls file is over about 1000 rows I get an error when I load
>>>>the
>>>> .xls file in excel saying it needs to repair the file and I lose all
>>>>the
>>>> row color and column width settings.
>>>
>>>Cell Styles are workbook scoped, not cell scoped. Make sure you create
>>>all
>>>of your styles outside of the loop, and re-use them. If you create one
>>>style per cell, you'll use too many and Excel will sulk....
>>>
>>>Nick
>>>
>>>---------------------------------------------------------------------
>>>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: Excel Files using XSSFWorkbook over a certain size display an
excel error when I open the .xls file in excel
Posted by "Schene, Chris" <Ch...@experian.com>.
Hi,
This is my logic below for creating a cell. I have several overloads: one
for string, one for integer, one for double
The getColorBasedOnStatus returns a color based on the status of a given
row (OK-black, ERROR-RED, WARNING-AQUA)
So, I assume what I need to do is create three styles:Black, Red, AQUA and
give those styles class level scope and just use those class level objects
rather than creating a new one for each cell.
Correct?
private short getColorBasedOnStatus(String rowState){
short result = IndexedColors.BLACK.getIndex();
if(rowState.compareTo("ERROR") != 0) {
result = IndexedColors.RED.getIndex();
}
if(rowState.compareTo("WARNING") != 0) {
result = IndexedColors.AQUA.getIndex();
}
return result;
}
/**
* Creates a cell and aligns it a certain way.
*
* @param wb the workbook
* @param row the row to create the cell in
* @param i the column number to create the cell in
* @param halign the horizontal alignment for the cell.
*/
private void createCell(XSSFWorkbook wb, Row row, int i, short halign,
short valign, String value) {
CreationHelper ch = wb.getCreationHelper();
Cell cell = row.createCell(i);
cell.setCellValue(ch.createRichTextString(value));
CellStyle cellStyle = wb.createCellStyle();
try {
String rowState = RowState.get(row.getRowNum()-1);
if (rowState.compareToIgnoreCase("ok") != 0 ) {
XSSFFont myFont= wb.createFont();
myFont.setFontHeightInPoints((short)10);
myFont.setFontName("Arial");
myFont.setColor(getColorBasedOnStatus(rowState));
myFont.setBold(true);
myFont.setItalic(false);
cellStyle.setFont(myFont);
}
} catch(Exception e) {}
cellStyle.setAlignment(halign);
cellStyle.setVerticalAlignment(valign);
cell.setCellStyle(cellStyle);
}
Christopher Schene
Field Engineer
Global
Fraud and Identity Solutions
Experian
16260 N. 71st Street
Suite #400
Scottsdale
Arizona, USA
85254
+1 602.290.9792 mobile
+1 480.751.3928 office
chris.schene@experian.com
www.experian.com <http://www.experian.com/>
On 11/4/15, 10:03 AM, "Schene, Chris" <Ch...@experian.com> wrote:
>"Make sure you create all of your styles outside of the loop, and re-use
>them.
>³
>
>Ah! Makes sense! I actually create a new style for each and every cell.
>Since I can have up to 50,000 rows that is a total of 2.35 million
>separate styles.
>
>Yikes!
>
>Thanks,
>
>Chris
>
>Christopher Schene
>Field Engineer
>Global
>Fraud and Identity Solutions
>
>Experian
>16260 N. 71st Street
>Suite #400
>Scottsdale
> Arizona, USA
> 85254
>+1 602.290.9792 mobile
>+1 480.751.3928 office
>chris.schene@experian.com
>www.experian.com <http://www.experian.com/>
>
>
>
>
>
>On 11/4/15, 9:55 AM, "Nick Burch" <ap...@gagravarr.org> wrote:
>
>>On Wed, 4 Nov 2015, Schene, Chris wrote:
>>> I am using the code below to create a very large spread sheet that is
>>>47
>>> rows wide.
>>>
>>> There are a few very large strings in the rows, but for the most part
>>>the
>>> data is fairly small.
>>>
>>> If the .xls file is over about 1000 rows I get an error when I load
>>>the
>>> .xls file in excel saying it needs to repair the file and I lose all
>>>the
>>> row color and column width settings.
>>
>>Cell Styles are workbook scoped, not cell scoped. Make sure you create
>>all
>>of your styles outside of the loop, and re-use them. If you create one
>>style per cell, you'll use too many and Excel will sulk....
>>
>>Nick
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>For additional commands, e-mail: user-help@poi.apache.org
>>
>
Re: Excel Files using XSSFWorkbook over a certain size display an
excel error when I open the .xls file in excel
Posted by "Schene, Chris" <Ch...@experian.com>.
"Make sure you create all of your styles outside of the loop, and re-use
them.
³
Ah! Makes sense! I actually create a new style for each and every cell.
Since I can have up to 50,000 rows that is a total of 2.35 million
separate styles.
Yikes!
Thanks,
Chris
Christopher Schene
Field Engineer
Global
Fraud and Identity Solutions
Experian
16260 N. 71st Street
Suite #400
Scottsdale
Arizona, USA
85254
+1 602.290.9792 mobile
+1 480.751.3928 office
chris.schene@experian.com
www.experian.com <http://www.experian.com/>
On 11/4/15, 9:55 AM, "Nick Burch" <ap...@gagravarr.org> wrote:
>On Wed, 4 Nov 2015, Schene, Chris wrote:
>> I am using the code below to create a very large spread sheet that is 47
>> rows wide.
>>
>> There are a few very large strings in the rows, but for the most part
>>the
>> data is fairly small.
>>
>> If the .xls file is over about 1000 rows I get an error when I load the
>> .xls file in excel saying it needs to repair the file and I lose all the
>> row color and column width settings.
>
>Cell Styles are workbook scoped, not cell scoped. Make sure you create
>all
>of your styles outside of the loop, and re-use them. If you create one
>style per cell, you'll use too many and Excel will sulk....
>
>Nick
>
>---------------------------------------------------------------------
>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: Excel Files using XSSFWorkbook over a certain size display an
excel error when I open the .xls file in excel
Posted by Nick Burch <ap...@gagravarr.org>.
On Wed, 4 Nov 2015, Schene, Chris wrote:
> I am using the code below to create a very large spread sheet that is 47
> rows wide.
>
> There are a few very large strings in the rows, but for the most part the
> data is fairly small.
>
> If the .xls file is over about 1000 rows I get an error when I load the
> .xls file in excel saying it needs to repair the file and I lose all the
> row color and column width settings.
Cell Styles are workbook scoped, not cell scoped. Make sure you create all
of your styles outside of the loop, and re-use them. If you create one
style per cell, you'll use too many and Excel will sulk....
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org