Home > Enterprise >  Can not accurately set Excel column width Apache POI
Can not accurately set Excel column width Apache POI

Time:11-26

This is my code (Java)

sheet.setColumnWidth(0, 100 * 256);

I expect the width of column 1 of the generated excel to be 100. But the actual width in excel is 99.22 . The search engine did not provide a reasonable explanation, can anyone help.

CodePudding user response:

In Excel the column width 100 means that 100 default characters in default font and default font size fit into the cell. So how width 100 exactly is varies with default font and default font size. The default font and default font size is given by cell style Normal given in Styles group in Home tab.

In storage Excel stores the width having additional padding. So coulmn widt 100 is not exactly stored as 100 but as 100 padding. But also the padding differs for different default fonts and default font sizes used.

Apache POI using XSSF (Office Open XML format) takes font Calibri and font size 11 as the defaults. Those are the defaults used by Excel too. So Units.DEFAULT_CHARACTER_WIDTH of Apache POI is default character width of that default font in size 11.

So using following column width setting:

...
   int widthExcel = 100;
   int width256 = (int)Math.round((widthExcel*Units.DEFAULT_CHARACTER_WIDTH 5f)/Units.DEFAULT_CHARACTER_WIDTH*256f);
   sheet.setColumnWidth(0, width256);
...

should set column width exactly 100 in Excel if that Excel uses font Calibri and font size 11 as the defaults in cell style Normal.

Using other defaults the Units.DEFAULT_CHARACTER_WIDTH in the calculation must be replaced by other default character widths.

CodePudding user response:

I modified my code(I got it from here https://blog.csdn.net/ab411919134/article/details/17606399):

sheet.setColumnWidth(0, (int) ((100   0.73) * 256));

The post mentioned that it should be 0.72, but 0.73 is correct for me

This excel column width meets expectations, but I still don’t know why

  • Related