Dagnon, William
2018-03-02 15:22:38 UTC
Hello, long time fan, but long time since I last used POI.
I've downloaded POI 3.17 and I'm generating XLSX files from Groovy within Eclipse IDE backed by Java 8, and started at:
https://poi.apache.org/spreadsheet/quick-guide.html#FillsAndFrills
Though looked at the API when there were problems and updated my code slightly to use XSSFColors instead of IndexedColors.
However some of the formatting I'm trying is failing to display in MS Office 365 ProPlus. Unfortunately I don't have anything else to test it in:
1. LEFT BORDER NEVER DISPLAYS in "A" Column
I'm putting a medium border around a group of cells: the top, right, and bottom cells' borders display fine, but the left border (in the left-most column, "A") never displays. This is for left-only, top-left, and bottom-left cells: for top- and bottom-, their other border DOES display.
When I go into the cell style for it, the medium border is there in their graphic, and I can remove and re-add it. It simply isn't displaying in the GUI.
If I go into another left-most cell - either above (one I've defined) or below (one I never did a createCell(0) call for) and put the same border in there through Excel, it also does NOT display!?
Still in the Excel dialog: if I click on the top preset "Outline".. I can click all day and the format never toggles 'off' - so the left border seems to be corrupted for the whole worksheet's "A" column so much that the GUI cannot overwrite the corruption.
I can set the left border on any non-"A" column cells through Excel just fine.
My Groovy code looks like:
Workbook wb = new XSSFWorkbook();
XSSFColor black = new XSSFColor(new java.awt.Color(0, 0, 0));
CellStyle vLeft = wb.createCellStyle() // I only make 1 style for all (only) left-bordered cells. Another for the top-left corner, etc.
vLeft.setBorderLeft(BorderStyle.MEDIUM) // same style I use for all the working cells
vLeft.setLeftBorderColor(black); // same color I use for correctly-displaying border cells
XSSFSheet newSheet = wb.createSheet(name)
row = newSheet.createRow(ii)
Cell content = row.createCell(jj++)
content.setCellStyle(vLeft)
1. BACKGROUND COLOR DISPLAYS AS BLACK
I'm adding a blue-gray background color to certain lines, but it changes the cell to fill completely black.
XSSFColor bluegray = new XSSFColor(new java.awt.Color(217, 225, 242));
CellStyle codeFill = wb.createCellStyle();
codeFill.setFillBackgroundColor(bluegray); // seems to set Foreground color instead
codeFill.setFillPattern(FillPatternType.SOLID_FOREGROUND); // As the Quick Guide directs: leave it out and NO Fill styles get set!
cell.setCellStyle(codeFill) // No other styles on these cells... so far.
Going into Format Cells.. > Fill, the pattern color is correct. Pattern style is empty, and under Background Color, the button at top "No Color" is depressed. So it seems like the underlying code is confused about what it is setting.
1. ROW STYLE FILL = BLACK, ONLY to the REST of the ROW
If I instead try:
row.setRowStyle(codeFill)
The bad styles described above in #2 are applied to all columns of the row AFTER the current one instead of to ALL cells in the row - why??
I don't have the time to delve into the details of why these fail to work, so I'm asking here in case someone has answers/alternatives so I can get the same styles quickly.
What am I doing incorrectly?
Are these 3.17-specific bugs?
Thanks!
P.S. Apologies: sent from Outlook - I pray the content is still legible...
CONFIDENTIALITY NOTICE: This e-mail, including any attachments, may contain confidential, privileged and/or proprietary information which is solely for the use of the intended recipient(s). Any review, use, disclosure, or retention by others is strictly prohibited. If you are not an intended recipient, please contact the sender and delete this e-mail, any attachments, and all copies.
I've downloaded POI 3.17 and I'm generating XLSX files from Groovy within Eclipse IDE backed by Java 8, and started at:
https://poi.apache.org/spreadsheet/quick-guide.html#FillsAndFrills
Though looked at the API when there were problems and updated my code slightly to use XSSFColors instead of IndexedColors.
However some of the formatting I'm trying is failing to display in MS Office 365 ProPlus. Unfortunately I don't have anything else to test it in:
1. LEFT BORDER NEVER DISPLAYS in "A" Column
I'm putting a medium border around a group of cells: the top, right, and bottom cells' borders display fine, but the left border (in the left-most column, "A") never displays. This is for left-only, top-left, and bottom-left cells: for top- and bottom-, their other border DOES display.
When I go into the cell style for it, the medium border is there in their graphic, and I can remove and re-add it. It simply isn't displaying in the GUI.
If I go into another left-most cell - either above (one I've defined) or below (one I never did a createCell(0) call for) and put the same border in there through Excel, it also does NOT display!?
Still in the Excel dialog: if I click on the top preset "Outline".. I can click all day and the format never toggles 'off' - so the left border seems to be corrupted for the whole worksheet's "A" column so much that the GUI cannot overwrite the corruption.
I can set the left border on any non-"A" column cells through Excel just fine.
My Groovy code looks like:
Workbook wb = new XSSFWorkbook();
XSSFColor black = new XSSFColor(new java.awt.Color(0, 0, 0));
CellStyle vLeft = wb.createCellStyle() // I only make 1 style for all (only) left-bordered cells. Another for the top-left corner, etc.
vLeft.setBorderLeft(BorderStyle.MEDIUM) // same style I use for all the working cells
vLeft.setLeftBorderColor(black); // same color I use for correctly-displaying border cells
XSSFSheet newSheet = wb.createSheet(name)
row = newSheet.createRow(ii)
Cell content = row.createCell(jj++)
content.setCellStyle(vLeft)
1. BACKGROUND COLOR DISPLAYS AS BLACK
I'm adding a blue-gray background color to certain lines, but it changes the cell to fill completely black.
XSSFColor bluegray = new XSSFColor(new java.awt.Color(217, 225, 242));
CellStyle codeFill = wb.createCellStyle();
codeFill.setFillBackgroundColor(bluegray); // seems to set Foreground color instead
codeFill.setFillPattern(FillPatternType.SOLID_FOREGROUND); // As the Quick Guide directs: leave it out and NO Fill styles get set!
cell.setCellStyle(codeFill) // No other styles on these cells... so far.
Going into Format Cells.. > Fill, the pattern color is correct. Pattern style is empty, and under Background Color, the button at top "No Color" is depressed. So it seems like the underlying code is confused about what it is setting.
1. ROW STYLE FILL = BLACK, ONLY to the REST of the ROW
If I instead try:
row.setRowStyle(codeFill)
The bad styles described above in #2 are applied to all columns of the row AFTER the current one instead of to ALL cells in the row - why??
I don't have the time to delve into the details of why these fail to work, so I'm asking here in case someone has answers/alternatives so I can get the same styles quickly.
What am I doing incorrectly?
Are these 3.17-specific bugs?
Thanks!
P.S. Apologies: sent from Outlook - I pray the content is still legible...
CONFIDENTIALITY NOTICE: This e-mail, including any attachments, may contain confidential, privileged and/or proprietary information which is solely for the use of the intended recipient(s). Any review, use, disclosure, or retention by others is strictly prohibited. If you are not an intended recipient, please contact the sender and delete this e-mail, any attachments, and all copies.