I have a DataGridView in WinForms 2.0 C# that is populated from a DataTable (from SQL) and with already created column headers in DataGridView:

        OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, conn.ConnectionString);
        OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
        tableMainGrid = new DataTable();
        dAdapter.Fill(tableMainGrid);
        ...
        dataGridView1.DataSource = tableMainGrid;
        dataGridView1.AutoGenerateColumns = false;

The problem is, when I use the following command:

worksheet.ImportDataGridView(dgv, 1, 1,true,false);

I get the header from the SQL command / DataTable and not from the Grid.

I use this code to export:

public static void ExportToExcel(DataGridView dgv, string lang, string tablename)
{
    using (ExcelEngine excelEngine = new ExcelEngine())
    {
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Excel2013;
        IWorkbook workbook = application.Workbooks.Create(1);
        IWorksheet worksheet = workbook.Worksheets[0];


        worksheet.ImportDataGridView(dgv, 1, 1,true,false);


        worksheet.AutoFilters.FilterRange = worksheet.Range;
        worksheet.Range.AutofitColumns();
        worksheet.Range.AutofitRows();

        //...more code - styling header and cells

        SaveFileDialog saveFileDialog1 = new SaveFileDialog();
        saveFileDialog1.Filter = "Excel XLS|*.xls";
        saveFileDialog1.ShowDialog();


        if (saveFileDialog1.FileName != "")
        {
            workbook.SaveAs(saveFileDialog1.FileName, ExcelSaveType.SaveAsXLS);


            System.Diagnostics.Process.Start(saveFileDialog1.FileName);
        }
    }
}

Syncfusion Version : 16.3.0.21

Visual Studio: 2005

CodePudding user response：

Instead of using:

worksheet.ImportDataGridView(dgv, 1, 1,true,false);

OR

worksheet.ImportDataTable(table, true, 1, 1);

I'm using for loops:

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2013;
    
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];

    for (int i = 1; i < dgv.Columns.Count   1; i  )
    {
        worksheet.Range[1, i].Text = dgv.Columns[i - 1].HeaderText;
    }

    for (int i = 0; i < dgv.Rows.Count; i  )
    {
        for (int j = 0; j < dgv.Columns.Count; j  )
        {
            worksheet.Range[i   2, j   1].Text = dgv.Rows[i].Cells[j].Value.ToString();
        }
    }

    worksheet.AutoFilters.FilterRange = worksheet.Range;
    worksheet.Range.AutofitColumns();
    worksheet.Range.AutofitRows();
    worksheet.Range.IgnoreErrorOptions = ExcelIgnoreError.All;
    

    SaveFileDialog saveFileDialog1 = new SaveFileDialog();
    saveFileDialog1.Filter = "Excel XLS|*.xls";
    saveFileDialog1.ShowDialog();

    if (saveFileDialog1.FileName != "")
    {
        workbook.SaveAs(saveFileDialog1.FileName, ExcelSaveType.SaveAsXLS);

        System.Diagnostics.Process.Start(saveFileDialog1.FileName);
    }
}

this way I get the header and also all the rows

@jdweng and @housheng: thanks for the help