Excel._Worksheet.Columns.Autofit() method is hanging

Question

My sqldatareader is returning ~200 rows of data. About half of those rows have a column that contains an entire xml document. I'm assuming this column is causing the autofit() method to hang. How can I go about getting this to either throw an exception, or complete successfully. I don't care which one, I just need this [automated] program to finish.

Excel.Application xl = null;
            Excel._Workbook wb;
            Excel._Worksheet ws;

            try
            {
                xl = new Microsoft.Office.Interop.Excel.Application();
                xl.Visible = false;

                while (reader.HasRows && !done)
                {
                    wb = (Excel._Workbook)(xl.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet));
                    ws = (Excel._Worksheet)wb.ActiveSheet;

                    // Insert column headers
                    for (int counter = 0; counter < reader.FieldCount; counter++)
                        ws.Cells[1, counter + 1] = reader.GetName(counter);

                    // Write the data to the excel file
                    while (reader.Read())
                    {
                        for (int counter = 1; counter <= reader.FieldCount; counter++)
                        {
                            // Need to format this column so excel doesn't change how it looks
                            if (report.ReportName == "RPTAAMVANetBatch" && reader.GetName(counter - 1) == "CorrelationID")
                                ws.Cells[rowCounter, counter] = String.Format("''{0}'", reader.GetValue(counter - 1));
                            else
                                ws.Cells[rowCounter, counter] = reader.GetValue(counter - 1);
                        }
                        rowCounter++;
                    }

                    RecordsProcessed = rowCounter - 1;

                    // Format the excel file to liking
                    ws.get_Range(ws.Cells[1, 1], ws.Cells[rowCounter - 1, reader.FieldCount]);
                    ws.Columns.AutoFit();

how about showing some code? are you using Excel via Interop from C# ? — Davide Piras, Apr 05 '13 at 18:16
not complete, but the relevant code was added from the method — ganders, Apr 05 '13 at 18:23
you should do more google searching here is a similar post looks like you are missing something in the Range.Columns.AutoFit here http://stackoverflow.com/questions/2884356/how-do-i-auto-size-columns-through-the-excel-interop-objects — MethodMan, Apr 05 '13 at 18:30
@RBarryYoung yes everything works fine when that column is not there, or empty — ganders, Apr 05 '13 at 18:42
@ganders: Yes it will hang (depending on the data in each Excel Cell) but if you give it time then it will finish. The best way to reproduce this is to copy approx 32000 characters and paste them in 200 cells in a column and then manually do an autofit in Excel. I am aware of this because couple of days ago I wrote an App which extracted the email body and pasted them in a cell one below the other (approx 50 cells) and it hung when I did an autofit manually. — Siddharth Rout, Apr 05 '13 at 19:41
@SiddharthRout so is it expected that it hangs for multiple hours? — ganders, Apr 05 '13 at 19:43

score 2 · Accepted Answer · answered Apr 05 '13 at 20:13

Further to my comment above, I am using this particular sample file which I created couple of days ago in which there are 100 rows and in Col E, I have the entire body of several emails (I have put a red box to protect the identity and the contents of the email)

enter image description here

This is a simple VBA code that I ran to check the time it takes to Autofit the E Column

Sub Test()
    Dim startTime As String
    Dim endTime As String

    startTime = Now

    Columns("E:E").EntireColumn.AutoFit

    endTime = Now

    Debug.Print "The process started at " & startTime & " and got over at " & endTime
End Sub

ScreenShot:

enter image description here

Your program is taking more time because there are 200 rows and maybe you have more data in each Excel Cell than compared to mine.

So What is the Solution?

The best solution that I can think of is to widen the column to it's MAX (254.86) before you write your huge data to it. Something like this

        Excel.Range Rng = ws.get_Range("E:E",System.Type.Missing);

        Rng.EntireColumn.ColumnWidth = 254;

Note: Don't use Autofit for that column or for the entire worksheet. If you do need to use Autofit then break it up. For example, In my case, I would do an Autofit From Columns 1 to 4 and then from 6 to last Column

Excel._Worksheet.Columns.Autofit() method is hanging

1 Answers1