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
[email protected]
www.experian.com <http://www.experian.com/>
On 11/4/15, 7:18 AM, "Murphy, Mark" <[email protected]> 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:[email protected]]
>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: [email protected] For additional
>commands, e-mail: [email protected]
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: [email protected]
>For additional commands, e-mail: [email protected]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
