You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Alexander Hörnlein <al...@uni-wuerzburg.de> on 2017/10/18 12:39:28 UTC
CellUtil setting fonts not working?
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
Re: CellUtil setting fonts not working?
Posted by Javen O'Neal <on...@apache.org>.
Whoops. I missed a line of code. I expect that all 4 cells are bold. I'll
test this out later today to see what's up.
On Oct 18, 2017 06:34, "Javen O'Neal" <ja...@gmail.com> wrote:
> Reading your code, I would expect foo, bar, and baz would be bold and bat
> should not be bold. Are you getting the opposite of this?
>
> On Oct 18, 2017 05:39, "Alexander Hörnlein" <alexander.hoernlein@uni-
> wuerzburg.de> wrote:
>
>
> 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
>
>
>
Re: CellUtil setting fonts not working?
Posted by Javen O'Neal <ja...@gmail.com>.
Reading your code, I would expect foo, bar, and baz would be bold and bat
should not be bold. Are you getting the opposite of this?
On Oct 18, 2017 05:39, "Alexander Hörnlein" <
alexander.hoernlein@uni-wuerzburg.de> wrote:
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
Re: CellUtil setting fonts not working?
Posted by Javen O'Neal <on...@apache.org>.
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 <
alexander.hoernlein@uni-wuerzburg.de> 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 • casetrain@uni-wuerzburg.de
>
> Rechenzentrum der Universität Würzburg • 1U10
> Mail hoernlein@uni-wuerzburg.de
> Tel. +49 931 31-*86738*
> Mobil +49 176 49887115
> WWW http://casetrain.uni-wuerzburg.de/hoernlein
> Map http://casetrain.uni-wuerzburg.de/map
>
Re: CellUtil setting fonts not working?
Posted by Alexander Hörnlein <al...@uni-wuerzburg.de>.
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/bugzilla/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 • casetrain@uni-wuerzburg.de
Rechenzentrum der Universität Würzburg • 1U10
Mail hoernlein@uni-wuerzburg.de
Tel. +49 931 31-*86738*
Mobil +49 176 49887115
WWW http://casetrain.uni-wuerzburg.de/hoernlein
Map http://casetrain.uni-wuerzburg.de/map