Apache POI自动调整百分比格式的单元格

时间:2022-08-27 20:25:37

I'm having some issues with autosizing cells. After my program populates and formats each accordingly, it autosizes the cells to make it look visually acceptable. The issue is, when the cell is formatted as percentage, it auto-sizes the cell without taking in consideration the fact that there's a '%' character, so we end up getting #### on the cells until you expand the cell. Is there a way to autosize it WHILE taking in consideration that extra '%' character?

我在自动调整细胞方面遇到了一些问题。在我的程序相应地填充和格式化每个程序后,它会自动调整单元格以使其看起来在视觉上可接受。问题是,当单元格格式化为百分比时,它会自动调整单元格大小而不考虑“%”字符这一事实,因此我们最终会在单元格上获得####,直到展开单元格为止。有没有办法在考虑额外的'%'字符时自动调整它?

EDIT:

编辑:

Apache POI自动调整百分比格式的单元格

So this is what happens, the left VAR has been autosized correctly for whatever reasons, but the VAR on the right hasn't.

所以这就是发生的事情,左边的VAR已经因任何原因而被正确地自动调整,但是右边的VAR没有。

EDIT2:

EDIT2:

I noticed that this ONLY happens when the cell value is 0.00% So all the values in the column for VAR that has #### were 0s and some of the values in the left column for VAR were non-zeros.

我注意到,仅当单元格值为0.00%时才会发生这种情况。因此,具有####的VAR列中的所有值均为0,而VAR列左侧列中的某些值为非零。

2 个解决方案

#1


1  

While this may not be an ideal solution, one possibility is to grab the size of the string you are inserting into the cell (ie. "50.00%" = size of 6), and set the cell width based manually based on that.

虽然这可能不是一个理想的解决方案,但有一种可能性是获取您插入单元格的字符串的大小(即“50.00%”=大小为6),并根据该值手动设置单元格宽度。

#2


1  

I'm afraid this is not supported in Apache POI, as it disregards the custom cell formatting when trying to calculate column width (it only takes font characteristics and rotation into consideration, but no DataFormat).

我担心Apache POI不支持这一点,因为它在尝试计算列宽时忽略了自定义单元格格式(它只考虑字体特征和旋转,但没有DataFormat)。

As a workaround you can try to write your custom autoSizeColumn method as a modification of the one currently implemented, i.e.:

作为一种解决方法,您可以尝试编写自定义autoSizeColumn方法作为当前实现的方法的修改,即:

   public void autoSizeColumn(Sheet sheet, int column, int plusMinusChars) {
        double width = SheetUtil.getColumnWidth(sheet, column, false);

        if (width != -1) {
            width += plusMinusChars;
            width *= 256;
            int maxColumnWidth = 255*256; // The maximum column width for an individual cell is 255 characters
            if (width > maxColumnWidth) {
                width = maxColumnWidth;
            }
            sheet.setColumnWidth(column, (int)(width));
        }

    } 

Then you can call it with an extra '%' character to be printed out:

然后您可以使用额外的'%'字符调用它来打印出来:

//4th column autosized + 1 character in width to accomodate for '%'
autoSizeColumn(sheet, 3, 1);

#1


1  

While this may not be an ideal solution, one possibility is to grab the size of the string you are inserting into the cell (ie. "50.00%" = size of 6), and set the cell width based manually based on that.

虽然这可能不是一个理想的解决方案,但有一种可能性是获取您插入单元格的字符串的大小(即“50.00%”=大小为6),并根据该值手动设置单元格宽度。

#2


1  

I'm afraid this is not supported in Apache POI, as it disregards the custom cell formatting when trying to calculate column width (it only takes font characteristics and rotation into consideration, but no DataFormat).

我担心Apache POI不支持这一点,因为它在尝试计算列宽时忽略了自定义单元格格式(它只考虑字体特征和旋转,但没有DataFormat)。

As a workaround you can try to write your custom autoSizeColumn method as a modification of the one currently implemented, i.e.:

作为一种解决方法,您可以尝试编写自定义autoSizeColumn方法作为当前实现的方法的修改,即:

   public void autoSizeColumn(Sheet sheet, int column, int plusMinusChars) {
        double width = SheetUtil.getColumnWidth(sheet, column, false);

        if (width != -1) {
            width += plusMinusChars;
            width *= 256;
            int maxColumnWidth = 255*256; // The maximum column width for an individual cell is 255 characters
            if (width > maxColumnWidth) {
                width = maxColumnWidth;
            }
            sheet.setColumnWidth(column, (int)(width));
        }

    } 

Then you can call it with an extra '%' character to be printed out:

然后您可以使用额外的'%'字符调用它来打印出来:

//4th column autosized + 1 character in width to accomodate for '%'
autoSizeColumn(sheet, 3, 1);