How to generalize a code for exporting DataGridView to Excel file?

Question

I want to generalize the following code which exports DataGriveView content into an Excel file. My problem is in "dataGridView1" part of the code. I can make change in the following code and generalize file path, File name, Worksheet name, ... . But there is no DataGriView in C# class mode.
private void btnExport_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
        Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
        worksheet = workbook.Sheets["Sheet1"];
        worksheet = workbook.ActiveSheet;
        worksheet.Name = "pmInfo";
        for (int i = 1; i < dataGridView1.Columns.Count+1; i++)
        {
            worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
        }
        for (int i = 0; i < dataGridView1.Rows.Count; i++)
        {
            for (int j = 0; j < dataGridView1.Columns.Count; j++)
            {
                worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
            }
        }
        var saveFileDialoge = new SaveFileDialog();
        saveFileDialoge.FileName = "Output";
        saveFileDialoge.DefaultExt = ".xlsx";
        if (saveFileDialoge.ShowDialog()==DialogResult.OK)
        {
            workbook.SaveAs(saveFileDialoge.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

}
    }

I used using System.Windows.Forms; as reference but the problem exists.
Note that I fill DGV directly, not using DataTable.

arconaut · Accepted Answer

Not sure if I understood the problem correctly, but based on your comment I would recommend to gather the data from the textboxes in a separate collection, before feeding it to the DataGridView. Then you can use that collection for both the DataGridView and the excel export.

You can model each row in the grid with a class:

public class MyData
{
   public string Value1 { get; set; }
   public string Value2 { get; set; }
}

Then, in the code that does this gathering of 'all the information in those textboxes'. You can initialize a list that will be used later:

// this is where you gather the data from the textboxes
var dataCollection = new List<MyData>();

// execute this in a loop, to populate your 'dataCollection'
dataCollection.Add(new MyData {
   Value1 = "value1", // depends on how your data is populated...
   Value2 = "value2"
});

// use dataCollection to populate your DataGridView, more info here: https://docs.microsoft.com/en-us/dotnet/api/system.windows.forms.datagridview.datasource?view=net-5.0

And then you can make a reusable function that does the export to excel:

private void btnExport_Click(object sender, EventArgs e)
{
    ExportToExcel(dataCollection);    
}

private void ExportToExcel(List<MyData> data) // -> reusable piece
{
    // your worksheet and workbook code...

    // add column headers like this
    worksheet.Cells[1, 1] = "Value1";

    // add rows like this
    for (int i = 0; i < data.Count; i++)
    {
       worksheet.Cells[i+2, 1] = data.Value1;
       worksheet.Cells[i+2, 2] = data.Value2;
    }

    // your file saving code...
}

kiafiore · Answer

Try creating an extension on DataGridView, for example like this one:
public static class DataGridViewExtensions
        {
            public static void ExportViewToWorksheet(this DataGridView dgv, Microsoft.Office.Interop.Excel._Worksheet worksheet)
            {
                if (dgv == null || worksheet == null) return;

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

After that, you can change your code like this:
private void btnExport_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
            worksheet = workbook.Sheets["Sheet1"];
            worksheet = workbook.ActiveSheet;
            worksheet.Name = "pmInfo";

dataGridView1.ExportViewToWorksheet(worksheet);

var saveFileDialoge = new SaveFileDialog();
            saveFileDialoge.FileName = "Output";
            saveFileDialoge.DefaultExt = ".xlsx";
            if (saveFileDialoge.ShowDialog() == DialogResult.OK)
            {
                workbook.SaveAs(saveFileDialoge.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

}
        }

How to generalize a code for exporting DataGridView to Excel file?

2 Answers

Add your own answers!

Ask a Question