Append columns before Sheetdata

Question

I need to add columns with specific widths to Worksheet while creating new Excel file. According to everywhere I looked this has to be done before Sheetdata (one link here). However I tried numerious things but can't get It working. My code for creating Excel file is from official site (link here - It's meant for ASP.NET but works fine for me).

Here is my code and last attempt to get It working:

 public void Export_Datagridview(DataGridView dgv, string filename)
 {
            using (var workbook = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = workbook.AddWorkbookPart();

                workbook.WorkbookPart.Workbook = new Workbook();
                workbook.WorkbookPart.Workbook.Sheets = new Sheets();

                var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();

                var sheetData = new SheetData();
                //this part is giving me "object reference" error
                sheetPart.Worksheet.InsertBefore(AutoFit_Columns(dgv, sheetPart.Worksheet), sheetData);

  ...//and so on...

And code for adding columns :

 private Columns AutoFit_Columns(DataGridView dgv, Worksheet worksheet)
        {
            Columns cols = new Columns();

            for (int col = 0; col < dgv.ColumnCount; colc++)
            {
                double max_width = 14.5; //something like default width in Excel

                for (int row = 0; row < dgv.RowCount; row++)
                {
                    double cell_width = Text_width(dgv.Rows[row].Cells[col].Value.ToString(), new System.Drawing.Font("Arial", 12.0F));

                    if (cell_width > max_width)
                    {
                        max_width = cell_width;
                    }

                    if (row == dgv.RowCount - 1) //last iteration - here we allready have max width within column
                    {
                       Column c = new Column() { Min = Convert.ToUInt32(col), Max = Convert.ToUInt32(col), Width = max_width, CustomWidth = true };
                        cols.Append(c);
                        worksheet.Append(cols);
                    }
                }
            }
            return cols;

        }

As you see, this is my attempt to Autofit columns based on data that get's exported from Datagridview. But before I can test other code I need to add columns properly. Any help kindly appreciated !

Answer 1

Figured It out, wasn't so easy to solve and identify all problems. Firstly I needed to change upper code to this:

 public void Export_Datagridview(DataGridView dgv, string filename)
 {
            using (var workbook = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = workbook.AddWorkbookPart();

                workbook.WorkbookPart.Workbook = new Workbook();
                workbook.WorkbookPart.Workbook.Sheets = new Sheets();

                var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                //this part is new - I had to change method for autofit too
                 sheetPart.Worksheet = new Worksheet();
                sheetPart.Worksheet.Append(AutoFit_Columns(dgv));
                sheetPart.Worksheet.Append(sheetData);
  ...//and so on...

and then change Autofit method. It was still causing errors because loop started from 0, and there is no Column with index of 0. Changed It to 1:

 for (int col = 1; col < dgv.ColumnCount; colc++)
            {

Excel now opens with column widths set, though code for autofit needed adjustments. If anyone interested, here is my complete solution with autofit included.