如何使用Apache-POI获取Excel表的默认列宽度?

Requirement

I need to get the default column width from a XSSFSheet.

我需要从XSSFSheet获得默认的列宽度。

Approach

XSSFSheet has a method called getDefaultColumnWidth():

XSSFSheet有一个名为getDefaultColumnWidth()的方法:

public int getDefaultColumnWidth() {
    CTSheetFormatPr pr = worksheet.getSheetFormatPr();
    return pr == null ? 8 : (int)pr.getBaseColWidth();
}

Problem

The method returns every time the same value, even if I change the default column width within Excel.

该方法每次返回相同的值，即使我更改了Excel中的默认列宽度。

Obviously this method should return the default column width but I think it actually doesn't return the correct value.

显然，这个方法应该返回默认的列宽度，但我认为它实际上没有返回正确的值。

Cause

I checked with Excel itself what exactly happens:

我用Excel检查了到底发生了什么:

1. I changed the default column width within Excel and saved the sheet.
2. 我更改了Excel中默认的列宽度，并保存了该表。
3. I then look up the changes Excel made in the corresponding xml file:
   • The attribute defaultColWidth got changed.
   • 属性defaultColWidth被更改。
   • The attribute baseColWidth stayed the same.
   • 属性baseColWidth保持不变。
4. 然后我查找在相应的xml文件中所做的更改:属性defaultColWidth被更改。属性baseColWidth保持不变。

So the 'real' and correct default column width is stored within the defaultColWidth attribute. Therefore the method should return that attribute but instead it returns the base column width (as you can see in the implementation above).

因此，“真实”和正确的默认列宽存储在defaultColWidth属性中。因此，该方法应该返回该属性，而不是返回基列宽度(如您在上面的实现中所看到的)。

Solution

In my humble opinion, the method from above should return pr.getDefaultColWidth() and not pr.getBaseColWidth().

根据我的拙见，上面的方法应该返回pr.getDefaultColWidth()，而不是pr.getBaseColWidth()。

I think either the method is wrongly implemented or I'm just too stupid to find the correct method which returns the correct value.

我认为要么这个方法被错误地实现了，要么就是我太愚蠢了，没有找到正确的方法来返回正确的值。

Question

How can I get the correct default column width from an Excel XSSFSheet with POI Apache?

如何使用POI Apache从Excel XSSFSheet获得正确的默认列宽度?

Regards winklerrr

问候winklerrr

1 个解决方案

#1

getDefaultColumnWidth

public int getDefaultColumnWidth()

Get the default column width for the sheet (if the columns do not define their own width) in characters.

Note, this value is different from getColumnWidth(int). The latter is always greater and includes 4 pixels of margin padding (two on each side), plus 1 pixel padding for the gridlines.

Specified by:
    getDefaultColumnWidth in interface Sheet
Returns:
    column width, default value is 8

The above clearly says that default shall be 8 if columns do not define their own. May be you are using the wrong API.

上面明确表示，如果列不定义自己的列，则默认值为8。可能是您使用了错误的API。