Autosize Excel column | Infragistics Forums

Upon testing, the widths were either WAY too big or just a tiny bit too small. So, I changed it to use the SetWidth() method and used the WorksheetColumnWidthUnit.Character since I knew the specific number of characters. I was also able to track down the too big problem to being that spaces take up a LOT smaller space than any other charcter so I cut the number of characters for spaces in half. And finally since Excel changes the width down by fractions of characters most likely for the grid lines and padding, I decided to just add 2 more to the final width to compensate. Here is the revised method with comments:

/// <summary>
/// Autosize the columns using their text as a measuring guide
/// </summary>
/// <param name="book">Excel book</param>
/// <param name="sheet">Excel sheet</param>
public static void AutoSizeColumns(Workbook book, Worksheet sheet)
{
Dictionary<int, int> colWidths = new Dictionary<int, int>();

using (Graphics g = Graphics.FromHwnd(IntPtr.Zero))
{
   // loops through all worksheets in the workbook
   foreach (var wksheet in book.Worksheets)
   {
      //
      if (sheet == null || (book.Worksheets.Contains(sheet) && wksheet == sheet))
      {
         // couldn't guarantee that the worksheet had columns every time
         // so had to loop through rows and cells instead to find the correct width
         foreach (var row in wksheet.Rows)
         {
            foreach (var cell in row.Cells)
            {
               // get width of cell value
               string value = cell.Value.ToString();

// width w/o spaces is initially retrieved
int width = value.Replace(" ", "").Length;

// reduces width to compensate for small size of spaces
width += (value.Length - width) / 2;

               // add width to collection for later reference
               if (!colWidths.ContainsKey(cell.ColumnIndex))
                  colWidths.Add(cell.ColumnIndex, width);
               else if (value.ToString().Length > colWidths[cell.ColumnIndex])
                  colWidths[cell.ColumnIndex] = width;
            }
         }
      }
      // loops through the new column widths and set each column to the new widths
      foreach (var colIndex in colWidths.Keys)
      {
         wksheet.Columns[colIndex].SetWidth(colWidths[colIndex] + 2, WorksheetColumnWidthUnit.Character);
      }
   }
}
}