Exporting Data Grid to Excel with Save Dialog Box in C#.NET

Introduction

I’ve the following data grid (say comparisonGrid) and I'll export this data into Excel by following the below steps:

Step 1

Include the following namespace to your code behind file (.cs file):

using Excel = Microsoft.Office.Interop.Excel;

Step 2

Now you would need to instantiate the instances of Excel application, Excel workbook and worksheet as below (I'll do that in the export button click event):

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

Step 3

Fill in the Excel sheet with the values of the data grid (cell by cell) as follows:

for (int i = 0; i <= comparisonGrid.Items.Count - 1; i++)
{
	for (int j = 0; j <= comparisonGrid.Columns.Count - 1; j++)
	{
		xlWorkSheet.Cells[i + 1, j + 1] =  
		( (DataRowView)comparisonGrid.Items[i]).Row.ItemArray[j].ToString();
	}
}

Step 4

For saving the file using “Save dialog” box, you would add the following code:

System.Windows.Forms.SaveFileDialog saveDlg = new System.Windows.Forms.SaveFileDialog();
saveDlg.InitialDirectory = @"C:\";
saveDlg.Filter = "Excel files (*.xls)|*.xls";
saveDlg.FilterIndex = 0;
saveDlg.RestoreDirectory = true;
saveDlg.Title = "Export Excel File To";
if (saveDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
	string path = saveDlg.FileName;
	xlWorkBook.SaveCopyAs(path);
	xlWorkBook.Saved = true;
	xlWorkBook.Close(true, misValue, misValue);
	xlApp.Quit();
}

You may encounter the below error:

Error 1 Interop type 'Microsoft.Office.Interop.Excel.ApplicationClass' cannot be embedded.
Use the applicable interface instead.

Fix

In your Project, expand the "References", find the Microsoft Office Interop reference. Right click it and select properties, and change "Embed Interop Types" to false. Add the following reference:

using Excel = Microsoft.Office.Interop.Excel;

CodeProject