Export DataGrid into CSV or Excel

Question

How can i export my DataGrid information into an .csv or excel file? I'm using Microsoft Visual Studio 2010 with .NET Framework 4.0.

Answer 1

You can use the following code which converts datatable to csv. The code is very flexible like you can change the column name and can select number of columns.

To export to csv you need to call this

ExportFile.ExportCSV(dt, "id,name", "Product ID,Name","order.csv");

here is the code below:

public class ExporFile
{
    /// <summary>
    /// Export to CSV
    /// </summary>
    /// <param name="exportTable">Export table</param>
    /// <param name="showColumns">Columns needs to show in CSV</param>
    /// <param name="changedColumnName">Changed Column Names in CSV</param>
    /// <param name="fileName">File Name</param>
    public static void ExportCSV(DataTable exportTable, string showColumns, string changedColumnName, string fileName)
    {
        DataTable filterTable = FilterColumn(exportTable, showColumns, changedColumnName);
        string dataCSV = DataTable2CSV(filterTable, "\t", "\"");
        dataCSV = System.Web.HttpContext.Current.Server.HtmlDecode(dataCSV);
        System.Web.HttpContext.Current.Response.Charset = "";
        System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Unicode;
        System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
        System.Web.HttpContext.Current.Response.Write(dataCSV);
        System.Web.HttpContext.Current.Response.Flush();
        try
        {
            System.Web.HttpContext.Current.Response.End();
        }
        catch { };
    }

    /// <summary>
    /// Filter Columns
    /// </summary>
    /// <param name="exportTable"></param>
    /// <param name="showColumns"></param>
    /// <param name="changedColumnName"></param>
    /// <returns></returns>
    private static DataTable FilterColumn(DataTable exportTable, string showColumns, string changedColumnName)
    {
        DataView filterDataView = exportTable.DefaultView;
        //filterDataView.Sort = "AutoID";
        DataTable filterTable = filterDataView.ToTable(false, showColumns.Split(','));
        return ChangedExportDataColumnName(filterTable, changedColumnName);
    }

    /// <summary>
    /// Changed Column Datatable
    /// </summary>
    /// <param name="filterTable"></param>
    /// <param name="changedColumnName"></param>
    /// <returns></returns>
    private static DataTable ChangedExportDataColumnName(DataTable filterTable, string changedColumnName)
    {
        string[] changedNames = changedColumnName.Split(',');

        for (int i = 0; i < changedNames.Length; i++)
        {
            if (!String.IsNullOrEmpty(changedNames[i]))
            {
                filterTable.Columns[i].ColumnName = changedNames[i];
            }
        }
        return filterTable;
    }

    /// <summary>
    /// Returns a CSV string corresponding to a datatable. However the separator can be defined and hence it can be any string separated value and not only csv.
    /// </summary>
    /// <param name="table">The Datatable</param>
    /// <param name="separator">The value separator</param>
    /// <param name="circumfix">The circumfix to be used to enclose values</param>
    /// <returns></returns>
    private static String DataTable2CSV(DataTable table, string separator, string circumfix)
    {

        StringBuilder builder = new StringBuilder(Convert.ToString((char)65279));
        foreach (DataColumn col in table.Columns)
        {
            builder.Append(col.ColumnName).Append(separator);
        }
        builder.Remove((builder.Length - separator.Length), separator.Length);
        builder.Append(Environment.NewLine);

        foreach (DataRow row in table.Rows)
        {
            foreach (DataColumn col in table.Columns)
            {
                builder.Append(circumfix).Append(row[col.ColumnName].ToString().Replace("\"", "\"\"")).Append(circumfix).Append(separator);
            }
            builder.Remove((builder.Length - separator.Length), separator.Length);
            builder.Append(Environment.NewLine);
        }
        return builder.ToString();
    }
}

Answer 2

try below sample

private void btnexport_Click(object sender, RoutedEventArgs e)
{
    ExportToExcel<Employee, Employees> s = new ExportToExcel<Employee, Employees>();
    s.dataToPrint = (Employees)dgEmployee.ItemsSource;
    s.GenerateReport();
}






/// <summary>
/// Class for generator of Excel file
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="U"></typeparam>
public class ExportToExcel<T, U>
    where T : class
    where U : List<T>
{
    public List<T> dataToPrint;
    // Excel object references.
    private Excel.Application _excelApp = null;
    private Excel.Workbooks _books = null;
    private Excel._Workbook _book = null;
    private Excel.Sheets _sheets = null;
    private Excel._Worksheet _sheet = null;
    private Excel.Range _range = null;
    private Excel.Font _font = null;
    // Optional argument variable
    private object _optionalValue = Missing.Value;

    /// <summary>
    /// Generate report and sub functions
    /// </summary>
    public void GenerateReport()
    {
        try
        {
            if (dataToPrint != null)
            {
                if (dataToPrint.Count != 0)
                {
                    Mouse.SetCursor(Cursors.Wait);
                    CreateExcelRef();
                    FillSheet();
                    OpenReport();
                    Mouse.SetCursor(Cursors.Arrow);
                }
            }
        }
        catch (Exception e)
        {
            MessageBox.Show("Error while generating Excel report");
        }
        finally
        {
            ReleaseObject(_sheet);
            ReleaseObject(_sheets);
            ReleaseObject(_book);
            ReleaseObject(_books);
            ReleaseObject(_excelApp);
        }
    }
    /// <summary>
    /// Make Microsoft Excel application visible
    /// </summary>
    private void OpenReport()
    {
        _excelApp.Visible = true;
    }
    /// <summary>
    /// Populate the Excel sheet
    /// </summary>
    private void FillSheet()
    {
        object[] header = CreateHeader();
        WriteData(header);
    }
    /// <summary>
    /// Write data into the Excel sheet
    /// </summary>
    /// <param name="header"></param>
    private void WriteData(object[] header)
    {
        object[,] objData = new object[dataToPrint.Count, header.Length];

        for (int j = 0; j < dataToPrint.Count; j++)
        {
            var item = dataToPrint[j];
            for (int i = 0; i < header.Length; i++)
            {
                var y = typeof(T).InvokeMember
        (header[i].ToString(), BindingFlags.GetProperty, null, item, null);
                objData[j, i] = (y == null) ? "" : y.ToString();
            }
        }
        AddExcelRows("A2", dataToPrint.Count, header.Length, objData);
        AutoFitColumns("A1", dataToPrint.Count + 1, header.Length);
    }
    /// <summary>
    /// Method to make columns auto fit according to data
    /// </summary>
    /// <param name="startRange"></param>
    /// <param name="rowCount"></param>
    /// <param name="colCount"></param>
    private void AutoFitColumns(string startRange, int rowCount, int colCount)
    {
        _range = _sheet.get_Range(startRange, _optionalValue);
        _range = _range.get_Resize(rowCount, colCount);
        _range.Columns.AutoFit();
    }
    /// <summary>
    /// Create header from the properties
    /// </summary>
    /// <returns></returns>
    private object[] CreateHeader()
    {
        PropertyInfo[] headerInfo = typeof(T).GetProperties();

        // Create an array for the headers and add it to the
        // worksheet starting at cell A1.
        List<object> objHeaders = new List<object>();
        for (int n = 0; n < headerInfo.Length; n++)
        {
            objHeaders.Add(headerInfo[n].Name);
        }

        var headerToAdd = objHeaders.ToArray();
        AddExcelRows("A1", 1, headerToAdd.Length, headerToAdd);
        SetHeaderStyle();

        return headerToAdd;
    }
    /// <summary>
    /// Set Header style as bold
    /// </summary>
    private void SetHeaderStyle()
    {
        _font = _range.Font;
        _font.Bold = true;
    }
    /// <summary>
    /// Method to add an excel rows
    /// </summary>
    /// <param name="startRange"></param>
    /// <param name="rowCount"></param>
    /// <param name="colCount"></param>
    /// <param name="values"></param>
    private void AddExcelRows
    (string startRange, int rowCount, int colCount, object values)
    {
        _range = _sheet.get_Range(startRange, _optionalValue);
        _range = _range.get_Resize(rowCount, colCount);
        _range.set_Value(_optionalValue, values);
    }       
    /// <summary>
    /// Create Excel application parameters instances
    /// </summary>
    private void CreateExcelRef()
    {
        _excelApp = new Excel.Application();
        _books = (Excel.Workbooks)_excelApp.Workbooks;
        _book = (Excel._Workbook)(_books.Add(_optionalValue));
        _sheets = (Excel.Sheets)_book.Worksheets;
        _sheet = (Excel._Worksheet)(_sheets.get_Item(1));
    }
    /// <summary>
    /// Release unused COM objects
    /// </summary>
    /// <param name="obj"></param>
    private void ReleaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show(ex.Message.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }
}

Answer 3

I think that the best and fastest option is to create a report and use reportViewer control to do the exporting for you (it has the option of exporting data into Excel, PDF and Word documents)