I have a code to export data to excel spreadsheet and using autofit to adjust the columns width. Here is the code.

ws.Columns.AutoFit();

The problem is in some cases the information in cells can be long and width of the column exceed 255 so I am getting "Unable to set the ColumnWidth property of the Range class" error. I just wanted to know what is the best way to avoid this error when you have long text.

Assigning the width of the column based on length of string that someone else suggested is not something that I am looking for. I am looking for more reliable way to deal with it.

I have a code to export data to excel spreadsheet and using autofit to adjust the columns width. Here is the code.

ws.Columns.AutoFit();

The problem is in some cases the information in cells can be long and width of the column exceed 255 so I am getting "Unable to set the ColumnWidth property of the Range class" error. I just wanted to know what is the best way to avoid this error when you have long text.

Assigning the width of the column based on length of string that someone else suggested is not something that I am looking for. I am looking for more reliable way to deal with it.

• c#
• excel

2 Answers 2

If the max value of the column width is 255, then wrap your AutoFit method call in a try-catch statement. If Autofit won't work at the Worksheet level, then loop at the column level. If there's a failure at the column level, set the width to 255.

Sort-of-code:

try {
    ws.Columns.AutoFit();
}
catch {
   //step through each column individually and perform autofit
   foreach(var column in [appropriate range function]){
       try {
           column.AutoFit();
       }
       catch {
           column.Width = 255;
       }
   }
}

This might be the best you can do.

Here is my final piece of code if someone needs it. It also extends the width of the column a little bit after Autofit.

        int i = 1;
        while (i <= ws.UsedRange.Columns.Count)
        {
            try
            {
                ws.Columns[i].AutoFit();
                ws.Columns[i].ColumnWidth = ws.Columns[i].ColumnWidth + 5;                 
            }
            catch
            {
                try
                {
                    ws.Columns[i].AutoFit();
                    ws.Columns[i].ColumnWidth = ws.Columns[i].ColumnWidth + 5;                     
                }
                catch
                {
                    ws.Columns[i].ColumnWidth = 255;                       
                }
            }
            i++;
        }