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.

Answer 1

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;
    }

Answer 2

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.