Similarly, if you want to set a foreground or background color, it must be an indexed color. bug: https://bz.apache.org/bugzilla/show_bug.cgi?id=59442
And conditional formatting: https://bz.apache.org/bugzilla/show_bug.cgi?id=60895 Those are the only 2 open bugs for CellUtil right now. Mark Murphy, one of the devs, and I are working on expanding a similar utility to be able to efficiently change cell styles without creating as many intermediate cell styles. See bugs 58787 and 54593. You could also use CellUtil.setFont, which will search your workbook for cell styles that match the cell's current style with the desired font. If no such cell style exists, it will create a new cell style. This is significantly better than creating a new cell style for every cell. However, if you making multiple modifications to a cell and don't use CellUtil.setCellStyleProperties to set all the properties at once, then some unnecessary styles may be created. If it becomes an issue (most likely running out of RAM due to creating too many styles), you could probably defragment and remove unused styles. Consider the scenario: Workbook wb = new XSSFWorkbook(); Cell cell = wb.createSheet().createRow(0).createCell(0); //wb has 1 style, the default cell style XSSFFont font = new XSSFFont(); font.setBold(true); CellUtil.setFont(cell, font); // now the workbook has 2 styles: default and default+boldfont CellUtil.setAlignment(cell, HorizontalAlignment.RIGHT); // now the workbook has 3 styles: default, default+boldfont, default+boldfont+rightalignment. The default+boldfont is no longer referenced and should be removed from the workbook. Anyway, sounds like you have it figured out. Let us know if you have any more questions. On Wed, Oct 18, 2017 at 7:37 AM, Alexander Hörnlein < [email protected]> wrote: > > Hi, > > I found out how to make them all bold. Instead of > > props.put(CellUtil.FONT, f); > > or > > CellUtil.setCellStyleProperty(c, CellUtil.FONT, f); > > I had to use > > ... CellUtil.Font, Short.valueOf(f.getIndex())); > > This is similar to bug #49254 (https://bz.apache.org/bugzill > a/show_bug.cgi?id=49254) where they use > > CellUtil.setCellStyleProperty(cell, workbook, CellUtil.FONT, > Short.valueOf(fontIndex)); > > as a workaround. > > > > > Am 18.10.2017 um 14:39 schrieb Alexander Hörnlein: > >> >> Hi all, >> >> this is my "minimal" code: >> >> final Workbook wb = new XSSFWorkbook(); >> final Sheet s = wb.createSheet(); >> final Row r = s.createRow(0); >> final Cell c1 = r.createCell(0); >> c1.setCellValue("foo"); >> final Cell c2 = r.createCell(1); >> c2.setCellValue("bar"); >> final Cell c3 = r.createCell(2); >> c3.setCellValue("baz"); >> final Cell c4 = r.createCell(3); >> c4.setCellValue("bat"); >> final Font f = wb.createFont(); >> f.setBold(true); >> {// 1 >> final Map<String, Object> props = new HashMap<>(); >> props.put(CellUtil.ALIGNMENT, HorizontalAlignment.RIGHT); >> props.put(CellUtil.BORDER_BOTTOM, BorderStyle.DOUBLE); >> props.put(CellUtil.FONT, f); >> CellUtil.setCellStyleProperties(c1, props); >> } >> { // 2 >> CellUtil.setCellStyleProperty(c2, CellUtil.ALIGNMENT, >> HorizontalAlignment.RIGHT); >> CellUtil.setCellStyleProperty(c2, CellUtil.BORDER_BOTTOM, >> BorderStyle.DOUBLE); >> CellUtil.setCellStyleProperty(c2, CellUtil.FONT, f); >> } >> { // 3 >> final CellStyle cs = wb.createCellStyle(); >> cs.setAlignment(HorizontalAlignment.RIGHT); >> cs.setBorderBottom(BorderStyle.DOUBLE); >> cs.setFont(f); >> c3.setCellStyle(cs); >> } >> { // 4 >> { >> final Map<String, Object> props = new HashMap<>(); >> props.put(CellUtil.ALIGNMENT, HorizontalAlignment.RIGHT); >> CellUtil.setCellStyleProperties(c4, props); >> } >> { >> final Map<String, Object> props = new HashMap<>(); >> props.put(CellUtil.BORDER_BOTTOM, BorderStyle.DOUBLE); >> CellUtil.setCellStyleProperties(c4, props); >> } >> { >> final Map<String, Object> props = new HashMap<>(); >> props.put(CellUtil.FONT, f); >> CellUtil.setCellStyleProperties(c4, props); >> } >> } >> final OutputStream os = new FileOutputStream(new >> File("D:\\_scratch\\exceloutput_test\\res_" + System.currentTimeMillis() >> + ".xlsx")); >> wb.write(os); >> os.close(); >> wb.close(); >> >> In 1 one (bis) setCellStyleProperties is used, in 2 three >> setCellStyleProperty calls are used, in 3 a CellStyle is created and in 4 >> each part of the style is set with one setCellStyleProperties. >> >> What I /should/ get: All cells are right aligned with border on top and >> bold. >> >> What I get is foo, bar, baz, bat are right aligned with a border on top, >> but only baz is bold. >> >> POI 3.17 is used. >> >> Is this a bug or am I doing something wrong here? >> >> cheers >> Alexander >> >> >> > -- > Beste Grüße > Alexander Hörnlein > > -- > *CaseTrain* • Fakultätsübergreifendes Blended Learning Projekt • > http://casetrain.uni-wuerzburg.de • [email protected] > > Rechenzentrum der Universität Würzburg • 1U10 > Mail [email protected] > Tel. +49 931 31-*86738* > Mobil +49 176 49887115 > WWW http://casetrain.uni-wuerzburg.de/hoernlein > Map http://casetrain.uni-wuerzburg.de/map >
