OpenXML: Auto Size column width in Excel

Question

I have written a code to generate Excel file using OpenXML. Below is the code which generates the Columns in the Excel.

Worksheet worksheet = new Worksheet();
Columns columns = new Columns();
int numCols = dt1.Columns.Count;
for (int col = 0; col < numCols; col++)
{
    Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, 20.42578125D);

    columns.Append(c);
}
worksheet.Append(columns);

Also, I tried below line to create columns.

Column c = new Column
{
    Min = (UInt32Value)1U,
    Max = (UInt32Value)1U,
    Width = 25.42578125D,
    BestFit = true,
    CustomWidth = true
};

I thought using BestFit it should work. But it doesn't set the auto size.

Bookmaker

Answer 1

The BestFit property is an information property (possibly for optimisation by Excel). You still need to provide the Width for the Column. This means you have to actually calculate the column width depending on the cell contents. Open XML SDK doesn't do this automatically for you, so it's better that you use a third-party library for this.

Julijulia

Answer 2

You have to calculate it your self unfortunately

This is what I've got. It works for my data that's tabular with some extra code in to take care of some styles I have set. Its not perfect by any means but works for what I need it for.

 private WorksheetPart mySheetPart;
 private void WriteToTable()
 {
      //Get your sheet data - write Rows and Cells
      SheetData sheetData = GetSheetData();

      //get your columns (where your width is set)
      Columns columns = AutoSize(sheetData);

      //add to a WorksheetPart.WorkSheet
      mySheetPart.Worksheet = new Worksheet();
      mySheetPart.Worksheet.Append(columns);
      mySheetPart.Worksheet.Append(sheetData);
 }

 private Columns AutoSize(SheetData sheetData)
 {
        var maxColWidth = GetMaxCharacterWidth(sheetData);

        Columns columns = new Columns();
        //this is the width of my font - yours may be different
        double maxWidth = 7;
        foreach (var item in maxColWidth)
        {
            //width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
            double width = Math.Truncate((item.Value * maxWidth + 5) / maxWidth * 256) / 256;

            //pixels=Truncate(((256 * {width} + Truncate(128/{Maximum Digit Width}))/256)*{Maximum Digit Width})
            double pixels = Math.Truncate(((256 * width + Math.Truncate(128 / maxWidth)) / 256) * maxWidth);

            //character width=Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100
            double charWidth = Math.Truncate((pixels - 5) / maxWidth * 100 + 0.5) / 100;

            Column col = new Column() { BestFit = true, Min = (UInt32)(item.Key + 1), Max = (UInt32)(item.Key + 1), CustomWidth = true, Width = (DoubleValue)width };
            columns.Append(col);
        }

        return columns;
  }


  private Dictionary<int, int> GetMaxCharacterWidth(SheetData sheetData)
    {
        //iterate over all cells getting a max char value for each column
        Dictionary<int, int> maxColWidth = new Dictionary<int, int>();
        var rows = sheetData.Elements<Row>();
        UInt32[] numberStyles = new UInt32[] { 5, 6, 7, 8 }; //styles that will add extra chars
        UInt32[] boldStyles = new UInt32[] { 1, 2, 3, 4, 6, 7, 8 }; //styles that will bold
        foreach (var r in rows)
        {
            var cells = r.Elements<Cell>().ToArray();

            //using cell index as my column
            for (int i = 0; i < cells.Length; i++)
            {
                var cell = cells[i];
                var cellValue = cell.CellValue == null ? string.Empty : cell.CellValue.InnerText;
                var cellTextLength = cellValue.Length;

                if (cell.StyleIndex != null && numberStyles.Contains(cell.StyleIndex))
                {
                    int thousandCount = (int)Math.Truncate((double)cellTextLength / 4);

                    //add 3 for '.00' 
                    cellTextLength += (3 + thousandCount);
                }

                if (cell.StyleIndex != null && boldStyles.Contains(cell.StyleIndex))
                {
                    //add an extra char for bold - not 100% acurate but good enough for what i need.
                    cellTextLength += 1;
                }

                if (maxColWidth.ContainsKey(i))
                {
                    var current = maxColWidth[i];
                    if (cellTextLength > current)
                    {
                        maxColWidth[i] = cellTextLength;
                    }
                }
                else
                {
                    maxColWidth.Add(i, cellTextLength);
                }
            }
        }

        return maxColWidth;
    }

Flor

You have not defined your "GetSheetData()" method. – Concision

wait a minute, i see what you mean, you just meant write your own implementation of setting all the cell values and then continue with setting column widths, my bad. – Concision

the charWidth variable in AutoSize() is not used? – Liveryman

@Liveryman the variables width, pixels and charWidth should probably have been functions instead. They directly mirror the 3 formulas listed on MSDN. – Propinquity

if you're using a SharedStringTable, you'll need to get the string after you get the cellValue in GetMaxCharacterWidth: if (cell.DataType == CellValues.SharedString) cellValue = sharedStringTable.ChildElements[int.Parse(cellValue)].InnerText; – Trevatrevah

Answer 3

I finally found a solution that uses the graphics engine GDI+ to do this accurately. avg. char width * number of chars doesn't cut it ever.

There's a caveat here in that you will need a couple of libraries if your target platform is linux - one for GDI+ interop and the other to add the open microsoft web fonts.

It uses a base unit of pixel, considering excel fonts are pt and column width INCHES! (lol)

Here is the method. It takes a JSchema to map to columns but you get the gist.

private static Columns CreateColumnDefs(JSchema jsonSchema)
{
    const double cellPadding = .4;
    const double minCellWidth = 10;

    var columnDefs = new Columns();
    var columnIndex = 0U;

    // set up graphics for calculating column width based on heading text width
    var bmp = new Bitmap(1, 1);

    // todo: ensure linux host has gdi and "Microsoft core fonts" libs installed
    using var graphics = Graphics.FromImage(bmp);
    graphics.TextRenderingHint = TextRenderingHint.AntiAlias;
    graphics.PageUnit = GraphicsUnit.Pixel;

    // excel fonts are in points - Arial 10pt matches the excel default of Calibri 11pt pretty well...
    using var font = new Font("Arial", 10F, FontStyle.Bold, GraphicsUnit.Point);

    // currently only handles 1 level (no nested objects)
    foreach (var (_, value) in jsonSchema.Properties)
    {
        var pixelWidth = graphics.MeasureString(value.Title, font).Width;

        // see: https://mcmap.net/q/662572/-formula-to-convert-net-pixels-to-excel-width-in-openxml-format/7902415
        var openXmlWidth = (pixelWidth - 12 + 5) / 7d + 1; // from pixels to inches.

        columnIndex++;
        var column = new Column
        {
            BestFit = true,
            CustomWidth = true,
            Width = Math.Max(openXmlWidth + cellPadding, minCellWidth),
            Min = columnIndex,
            Max = columnIndex
        };
        columnDefs.Append(column);
    }

    return columnDefs;
}

Edit

To run on linux install e.g.:

RUN apt update && apt-get install -y libgdiplus

(I didn't need any extra font libs, so perhaps system font works)

Edythedythe

Answer 4

I haven't had the time to look into it, but instead of just leaving a comment and a link, I thought I'd share a comment from somebody who has seemingly done some research on this.

I personally had issues getting the official formulas to fit with reality. I.e. Short strings got too small cells, longer strings got too big cells and most of all, the value presented in Excel was proportionally smaller than the value I inserted into the DocumentFormat.OpenXml.Spreadsheet.Column's Width-property. My quick solution was just to have a minimum width.

Anyway, here's the comment:

I had to do this in the end because the xlsx files I am interested in are auto generated and should look nice as soon as they are opened so I looked into this a little further and found there are a couple of issues to accurately sizing columns in Excel.

1. Need to use accurate character sizing, which means that instead of using MeasureString you need to use MeasureCharacterRanges, see http://groups.google.com/group/microsoft.public.office.developer.com.add_ins/browse_thread/thread/2fc33557feb72ab4/adaddc50480b8cff?lnk=raot

2. Despite the spec saying to add 5 pixels (1 for border and 2 for each side margin) Excel seems to use 9 – 1 for the border, 5 for the leading space and 3 for the trailing space – I only found this by using the accessibility app. Magnifier and counting the pixels after using Excel to auto fit the columns

Actually I was basing my calculations on underlying font metrics so I don’t actually use either MeasureCharacterRanges or MeasureString. If anyone is interested in doing this from font metrics then:

Width = Truncate( {DesiredWidth} + 9 / {MaxDigitWidth} ) / 256

{MaxDigitWidth} is an integer rounded to the nearest pixel of any of the 0..9 digits at 96 dpi {DesiredWidth} is the sum of adding all character widths together where each character width is the width of the character at 96 dpi rounded to the nearest integer. Note that each character is rounded not the overall sum

Propinquity

Answer 5

Here the possible formula width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] / {Maximum Digit Width} * 256) / 256

Katiekatina

Answer 6

@Mittal Patel take a look at my answer. Posted code is in DOM method, but I created a solution with SAX approach too and results are outstanding - no memory drain (writing directly from DataReader) and much faster than Interop library. Only down-side is autofit feature - you ave to read same data twice.

Sternutation