A2oz

How to Export GridView to Excel in C# Windows Application?

Published in Programming 2 mins read

Exporting data from a GridView to Excel in a C# Windows application is a common task for developers. Here's a step-by-step guide:

1. Add References:

  • Add a reference to the Microsoft.Office.Interop.Excel assembly to your project. This allows you to interact with Excel from your C# code.

2. Create an Excel Application:

  • Use the following code to create an instance of Excel and a new workbook:
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.Workbooks.Add(Type.Missing);

3. Access the Worksheet:

  • Get the first worksheet in the workbook:
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Worksheets[1];

4. Populate the Worksheet:

  • Loop through the rows and columns of your GridView and write the data to the corresponding cells in the worksheet:
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    for (int j = 0; j < dataGridView1.Columns.Count; j++)
    {
        worksheet.Cells[i + 1, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
    }
}

5. Save the Workbook:

  • Save the workbook to a file:
workbook.SaveAs("C:\\exported_data.xlsx");

6. Release Resources:

  • Release the resources used by Excel:
excelApp.Quit();
workbook = null;
excelApp = null;

Example:

using Microsoft.Office.Interop.Excel;

// ... your code ...

// Create Excel application and workbook
Application excelApp = new Application();
Workbook workbook = excelApp.Workbooks.Add(Type.Missing);

// Get the worksheet
Worksheet worksheet = workbook.Worksheets[1];

// Populate the worksheet with data from GridView
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    for (int j = 0; j < dataGridView1.Columns.Count; j++)
    {
        worksheet.Cells[i + 1, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
    }
}

// Save the workbook
workbook.SaveAs("C:\\exported_data.xlsx");

// Release resources
excelApp.Quit();
workbook = null;
excelApp = null;

Considerations:

  • Make sure that the user has Microsoft Excel installed on their system.
  • Consider using a library like EPPlus for a more lightweight and efficient solution, especially for large datasets.
  • Implement error handling to gracefully handle any issues that may occur during the export process.

Related Articles