How to get the ColumnWidth in the same units than the RowHeight RRS feed

  • Question

  • If I am right, the _WorkSheet.ColumnWidth is a value indicating the number of '0's character that will fit in the column using the default style for the sheet or worksheet, while the RowHeight indicates the number of points.

    Using C# I tried the following, but is not working properly:

    public static double ColumnWidthToPixels(this Excel._Worksheet worksheet, int column)        {            var result = ((Excel.Range)worksheet.Cells[1, column]).ColumnWidth * worksheet.DefaultFont().WidthToPixels();            return result;        } public static Font DefaultFont(this Excel._Worksheet worksheet)        {            var excelFont = ((Excel._Workbook) worksheet.Parent).Styles["Normal"].Font;            var converter = System.ComponentModel.TypeDescriptor.GetConverter(typeof(Font));            var font = (Font) converter.ConvertFromInvariantString($"{excelFont.Name}, {excelFont.Size}");            return font;        }public static double WidthToPixels(this Font font)        {            SizeF size = Graphics.FromImage(new Bitmap(1, 1)).MeasureString("0", font);            return size.Width;        }		

    I will appreciate any advice.

    Wednesday, June 19, 2019 9:56 PM

All replies

  • Juan,
    Re:  columnwidth in points

    You will have to translate to C#; below is VBA...
      Columns(1).Width              'returns width in points
      Columns(1).ColumnWidth  'returns width in characters

    'Note, you cannot set the width using width, you must use ColumnWidth

    Custom_Functions add-in (19 new excel functions)
    Download from MediaFire...

    Thursday, June 20, 2019 11:53 AM
  • RowHeight is returned in Points so the direct answer to your question "get column width in same units as RowHeight" is simply return the cell or column's Width property, which is also returned in Points.

    However your function as named seems aimed at getting the column's width in pixels. To convert any Points value to pixels one way is get the the screen's 'points-per-pixel' value using the GetDeviceCaps API (search those terms for examples), which in a typical 100% display will be 72/96 or 0.75, then
    pixels = points / ppp

    Your description for ColumnWidth is sort of right, it's the number including any fraction of standard width characters (like 0s) of the 'Normal' font that will fit into the width. The default width of a column is 8 plus a bit of padding, differs slightly with respective Excel versions.

    Thursday, June 20, 2019 1:13 PM