Thursday, 9 October 2014

Exporting data grid to Excel with Save Dialog box in C#.Net

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.Saved = true;
                    xlWorkBook.Close(true, misValue, misValue);

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 refrence “using Excel = Microsoft.Office.Interop.Excel;”

No comments:

Post a Comment