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:apa...@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-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org