CODEWITHSUNDEEP
c#exceloffice-interop
SmarterThanYourAverageBear
SmarterThanYourAverageBear

Reputation: 71

Autofit Column Width in C#-generated Excel sheet

I'm trying to Autofit the columns and rows in an Excel sheet that I make by exporting data from a SQL Server database, using C#. I've done a bit of research, and the solutions I've found don't seem to have any affect.

//This code produces the correct Excel File with the expected content
Excel.Application ExcelApp = new Excel.Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);
for (int i = 1; i < myDataTable.Rows.Count + 2; i++)
{
   for (int j = 1; j < myDataTable.Columns.Count + 1; j++)
   {
      if (i == 1)
      {
         ExcelApp.Cells[i, j] = myColumnCollection[j - 1].ToString();
      }
      else
         ExcelApp.Cells[i, j] = myDataTable.Rows[i - 2][j - 1].ToString();
   }
}
ExcelApp.ActiveWorkbook.SaveCopyAs(Application.StartupPath + "\\test.xlsx");
ExcelApp.ActiveWorkbook.Saved = true;

//This code is where I tried to do the Autofit work
Excel.Worksheet ws = ExcelApp.ActiveWorkbook.Worksheets[1];
Excel.Range range = ws.UsedRange; 
//ws.Columns.ClearFormats();
//ws.Rows.ClearFormats();
//range.EntireColumn.AutoFit();
//range.EntireRow.AutoFit();
ws.Columns.AutoFit();
ws.Rows.AutoFit();

ExcelApp.Quit();

I've tried a few methods near the end. I think I understand the difference between Range.Columns.Autofit() and Range.EntireColumn.AutoFit(), and have tried using Worksheet.Columns.ClearFormat() before AND after getting the UsedRange. None of these have affected my file at all, as far as I can tell.

What do I need to do in order to have the columns and rows autofitted to their content?

Upvotes: 5

Views: 8645

Answers (1)

SmarterThanYourAverageBear
SmarterThanYourAverageBear

Reputation: 71

Thanks goes to Derek for pointing out the obvious. I needed to save the Workbook after I was done modifying it. I just had to move my Autofit() stuff above the save.

 Excel.Worksheet ws = ExcelApp.ActiveWorkbook.Worksheets[1];
        ws.Columns.AutoFit();
        ws.Rows.AutoFit();
        ExcelApp.ActiveWorkbook.SaveCopyAs(Application.StartupPath + "\\HaulerInfo.xlsx");
        ExcelApp.ActiveWorkbook.Saved = true;

Upvotes: 2

Related Questions