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]
