Friday 10 October 2014

Exporting datagrid to CSV using Save Dialog in C#.NET

Below is the code for exporting the data of a data grid in C#.Net

Datagrid is as follows:


System.Windows.Forms.SaveFileDialog saveDlg = new System.Windows.Forms.SaveFileDialog();
                saveDlg.InitialDirectory = @"C:\";
                saveDlg.Filter = "CSV files (*.csv)|*.csv";
                saveDlg.FilterIndex = 0;
                saveDlg.RestoreDirectory = true;
                saveDlg.Title = "Export csv File To";
                if (saveDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    string CsvFpath = saveDlg.FileName;
                    System.IO.StreamWriter csvFileWriter = new StreamWriter(CsvFpath, false);
                    string columnHeaderText = "";
                    int countColumn = comparisonGrid.Columns.Count - 1;
                    if (countColumn >= 0)
                    {
                        columnHeaderText = (comparisonGrid.Columns[0].Header).ToString();
                    }

                    // Writing column headers
                    for (int i = 1; i <= countColumn; i++)
                    {
                        columnHeaderText = columnHeaderText + ',' + (comparisonGrid.Columns[i].Header).ToString();
                    }
                    csvFileWriter.WriteLine(columnHeaderText);

                    // Writing values row by row
                    for (int i = 0; i <= comparisonGrid.Items.Count - 2; i++)
                    {
                        string dataFromGrid = "";
                        for (int j = 0; j <= comparisonGrid.Columns.Count - 1; j++)
                        {
                            if (j == 0)
                            {
                                dataFromGrid = ((DataRowView)comparisonGrid.Items[i]).Row.ItemArray[j].ToString();
                            }
                            else
                            {
                                dataFromGrid = dataFromGrid + ',' + ((DataRowView)comparisonGrid.Items[i]).Row.ItemArray[j].ToString();
                            }
                        }
                        csvFileWriter.WriteLine(dataFromGrid);
                    }
                    csvFileWriter.Flush();
                    csvFileWriter.Close();
                }

The content of output csv file looks like:

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