Hi, I think there is a related bug entry at https://bz.apache.org/bugzilla/show_bug.cgi?id=54593 where we did some investigation and discussed caching things, but we could not create a complete fix yet... Ideas/Patches welcome!
Thanks... Dominik. On Wed, Nov 4, 2015 at 3:18 PM, 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]
