在Apache POI中设置列宽度。

I am writing a tool in Java using Apache POI API to convert an XML to MS Excel. In my XML input, I receive the column width in points. But the Apache POI API has a slightly queer logic for setting column width based on font size etc. (refer API docs)

我正在用Java编写一个工具，使用Apache POI API将XML转换为MS Excel。在XML输入中，我接收以点为单位的列宽度。但是Apache POI API在根据字体大小等设置列宽度方面有一种有点奇怪的逻辑(请参阅API文档)。

Is there a formula for converting points to the width as expected by Excel? Has anyone done this before?

是否有公式将点转换为Excel所期望的宽度?以前有人这样做过吗?

There is a setRowHeightInPoints() method though :( but none for column.

但是有一个setrowheight tinpoints()方法:(但是对于列没有。

P.S.: The input XML is in ExcelML format which I have to convert to MS Excel.

注::输入XML是ExcelML格式，我必须将其转换为MS Excel。

3 个解决方案

#1

Unfortunately there is only the function setColumnWidth(int columnIndex, int width) from class Sheet; in which width is a number of characters in the standard font (first font in the workbook) if your fonts are changing you cannot use it. There is explained how to calculate the width in function of a font size. The formula is:

不幸的是，类表中只有函数setColumnWidth(int columnIndex, int width);宽度是标准字体中的若干字符(工作簿中的第一种字体)，如果字体正在改变，就不能使用它。解释了如何计算字体大小函数的宽度。这个公式是:

width = Truncate([{NumOfVisibleChar} * {MaxDigitWidth} + {5PixelPadding}] / {MaxDigitWidth}*256) / 256

You can always use autoSizeColumn(int column, boolean useMergedCells) after inputting the data in your Sheet.

在将数据输入到工作表之后，您总是可以使用autoSizeColumn(int列，boolean useMergedCells)。

Hope it helped.

希望它帮助。

#2

Please be carefull with the usage of autoSizeColumn(). It can be used without problems on small files but please take care that the method is called only once (at the end) for each column and not called inside a loop which would make no sense.

请注意使用autoSizeColumn()。它可以在小文件上使用而不存在问题，但是请注意，对于每个列，该方法只被调用一次(在末尾)，而不会在循环中调用，这是没有意义的。

Please avoid using autoSizeColumn() on large Excel files. The method generates a performance problem.

请避免在大型Excel文件上使用autoSizeColumn()。该方法会产生性能问题。

We used it on a 110k rows/11 columns file. The method took ~6m to autosize all columns.

我们在110k行/11列文件中使用它。该方法对所有列进行了6米的自动大小。

For more details have a look at: How to speed up autosizing columns in apache POI?

有关更多细节，请查看:如何加速apache POI中的自动调整列?

#3

You can use also util methods mentioned in this blog: Getting cell witdth and height from excel with Apache POI. It can solve your problem.

您还可以使用本文中提到的util方法:使用Apache POI从excel中获取cell witdth和height。它可以解决你的问题。

Copy & paste from that blog:

复制和粘贴从那个博客:

public class PixelUtil {

  public static final short EXCEL_COLUMN_WIDTH_FACTOR = 256; 
  public static final short EXCEL_ROW_HEIGHT_FACTOR = 20; 
  public static final int UNIT_OFFSET_LENGTH = 7; 
  public static final int[] UNIT_OFFSET_MAP = new int[] { 0, 36, 73, 109, 146, 182, 219 };

  public static short pixel2WidthUnits(int pxs) {
    short widthUnits = (short) (EXCEL_COLUMN_WIDTH_FACTOR * (pxs / UNIT_OFFSET_LENGTH)); 
    widthUnits += UNIT_OFFSET_MAP[(pxs % UNIT_OFFSET_LENGTH)];  
    return widthUnits; 
  } 

  public static int widthUnits2Pixel(short widthUnits) {
    int pixels = (widthUnits / EXCEL_COLUMN_WIDTH_FACTOR) * UNIT_OFFSET_LENGTH; 
    int offsetWidthUnits = widthUnits % EXCEL_COLUMN_WIDTH_FACTOR; 
    pixels += Math.floor((float) offsetWidthUnits / ((float) EXCEL_COLUMN_WIDTH_FACTOR / UNIT_OFFSET_LENGTH));   
    return pixels; 
  }

  public static int heightUnits2Pixel(short heightUnits) {
    int pixels = (heightUnits / EXCEL_ROW_HEIGHT_FACTOR); 
    int offsetWidthUnits = heightUnits % EXCEL_ROW_HEIGHT_FACTOR; 
    pixels += Math.floor((float) offsetWidthUnits / ((float) EXCEL_ROW_HEIGHT_FACTOR / UNIT_OFFSET_LENGTH));   
    return pixels; 
  }

}

So when you want to get cell width and height you can use this to get value in pixel, values are approximatelly.

所以当你想要得到单元格的宽度和高度你可以用它来得到像素的值，值是近似的。

PixelUtil.heightUnits2Pixel((short) row.getHeight())
PixelUtil.widthUnits2Pixel((short) sh.getColumnWidth(columnIndex));

#1

width = Truncate([{NumOfVisibleChar} * {MaxDigitWidth} + {5PixelPadding}] / {MaxDigitWidth}*256) / 256