Ah yes, Fills. This is a bit tricky, but there are really three color
levels in a cell. These are the Color (Font color), Foreground color (Fill
color), Background color (Fill color). The Fill provides what we normally
call the cell background, but the color terminology, Foreground Color /
Background Color provide the fill pattern with two colors to work with.
Most of the fills have two colors, but SOLID_FOREGROUND only uses the
Foreground color. So in order to set a Grey solid background for the cell
you really need to:
CellStyle codeFill = wb.createCellStyle();
codeFill.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
// seems to set Foreground color instead
codeFill.setFillPattern(FillPatternType.SOLID_FOREGROUND);
I have had issues with this turning the cell black when selected, so I also
like to add:
codeFill.setFillBackgroundColor(65);
Which seems to fix the issue.
On Fri, Mar 2, 2018 at 12:32 PM, Dagnon, William <william.dag...@wpsic.com>
wrote:
> 1. after more experimenting: Nope, Excel skips displaying any
> Medium-thickness borders on Column “A”, but will at least display the
> others. Which is idiotic and negates any WYSIWYG of Excel.
>
> 2. background fill: yes, that works fine through Excel. Except I am
> generating files and cannot go through every sheet of every workbook and
> manually make changes every time.
>
>
> Also: I cannot see either image you included.
>
>
> From: Mark Murphy [mailto:jmarkmur...@gmail.com]
> Sent: Friday, March 02, 2018 11:20 AM
> To: POI Users List <user@poi.apache.org>
> Subject: Re: POI 3.17 Problems with border and fill styles in generating
> XSSF/xlsx files, viewed in MS Office 365 ProPlus
>
> Can you do what you want in Excel?
>
> Here is what it looks like when I add borders in Excel.
>
> [cid:ii_jea7bvbr0_161e7b89dcdf1fa2]
> The borders are re there though. If I look in Print Preview, it looks like
> this:
>
> [cid:ii_jea7dai41_161e7b9a118b7e5a]
>
> So maybe what you are seeing is just the way Excel renders the borders you
> have created.
>
> On Fri, Mar 2, 2018 at 10:22 AM, Dagnon, William <william.dag...@wpsic.com
> <mailto:william.dag...@wpsic.com>> wrote:
> 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<
> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fpoi.
> apache.org%2Fspreadsheet%2Fquick-guide.html%23FillsAndFrills&data=02%7C01%
> 7CWilliam.Dagnon%40wpsic.com%7C7c13f919985c4262ad7708d58061e63d%
> 7C6b61581df3634ffb8f7fd517e2227a0c%7C0%7C0%7C636556080333189845&sdata=
> lUqZ8EdvLV0VWbC9TQQEJ0OsbfYn4x6yTKN8cyAt7To%3D&reserved=0>
> 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.
>
>
