none
Exporting datagridview to csv file RRS feed

  • Question

  • I'm trying to export my datagridview to Excel csv file with a button click event. The export works fine, but the file is not saved in the right format. I need it to be saved as "CSV (Comma delimited) (*.csv)", but it saves as "Excel-workbook (*.xlsx)". What am I doing wrong?

            private void export_Btn_Click(object sender, EventArgs e)
            {
                saveFileDialog.InitialDirectory = "C";
                saveFileDialog.Title = "Save as excel file";
                saveFileDialog.FileName = "";
                saveFileDialog.Filter = "CSV (Comma delimited)|*.csv";
                if (saveFileDialog.ShowDialog() != DialogResult.Cancel)
                {
                    Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                    ExcelApp.Application.Workbooks.Add(Type.Missing);
    
                    ExcelApp.Columns.ColumnWidth = 20;
                    //header
                    for (int i = 1; i < dataGridView3.Columns.Count + 1; i++)
                    {
                        ExcelApp.Cells[1, i] = dataGridView3.Columns[i - 1].HeaderText;
                    }
                    //storing each row and column value for excel sheet
                    for (int i = 0; i < dataGridView3.Rows.Count; i++)
                    {
                        for (int j = 0; j < dataGridView3.Columns.Count; j++)
                        {
                            if (dataGridView3.Rows[i].Cells[j].Value != null)
                            {
                                ExcelApp.Cells[i + 2, j + 1] = dataGridView3.Rows[i].Cells[j].Value.ToString();
                            }
    
                        }
                    }
                    ExcelApp.ActiveWorkbook.SaveCopyAs(saveFileDialog.FileName.ToString());
                    ExcelApp.ActiveWorkbook.Saved = true;
                    ExcelApp.Quit();
                }
            }

     
    Friday, April 19, 2019 2:48 PM

Answers

  • Hello,

    Here is an alternate to Excel automation. 

    Usage

    dataGridView3.ExportCsv(saveFileDialog.FileName);

    Add the class below to your project. Does not do header but the one below does and is a similar version.

    using System;
    using System.IO;
    using System.Linq;
    using System.Windows.Forms;
    
    namespace YourNamespaceName
    {
        public static class DataGridViewExtensions
        {
            /// <summary>
            /// Create a string array from DataGridView rows using by default a comma
            /// between cell data.
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="delimitor"></param>
            /// <returns></returns>
            public static string[] CreateRowsArray(this DataGridView sender, string delimitor = ",")
            {
                return
                (
                    from row in sender.Rows.Cast<DataGridViewRow>()
                    where !((DataGridViewRow)row).IsNewRow
                    let rowItem = string.Join(delimitor, Array.ConvertAll(((DataGridViewRow)row).
                        Cells.Cast<DataGridViewCell>().ToArray(),
                        (DataGridViewCell c) => ((c.Value == null) ? "" : c.Value.ToString())))
                    select rowItem
                ).ToArray();
            }
            /// <summary>
            /// Export row data comma delimited to a text file
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="fileName">path combined with file name and extension</param>
            public static void ExportCsv(this DataGridView sender, string fileName)
            {
                string[] rowData = sender.CreateRowsArray();
                File.WriteAllLines(fileName,rowData);
            }
        }
    }
    Simple version with header.
    public static void ExportRows(this DataGridView sender, string fileName, string delimiter = ",")
    {
        if (sender.RowCount > 0)
        {
            var sb = new StringBuilder();
    
            var headers = sender.Columns.Cast<DataGridViewColumn>();
            sb.AppendLine(string.Join(delimiter, headers.Select(column => column.HeaderText)));
    
            foreach (DataGridViewRow row in sender.Rows)
            {
                if (!row.IsNewRow == true)
                {
                    var cells = row.Cells.Cast<DataGridViewCell>();
                    sb.AppendLine(string.Join(delimiter, cells.Select(cell => cell.Value)));
                }
            }
            File.WriteAllText(fileName, sb.ToString());
        }
        else
        {
            // Do nothing
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by okrus Sunday, April 21, 2019 11:05 AM
    Friday, April 19, 2019 3:25 PM
    Moderator

All replies

  • Hello,

    Here is an alternate to Excel automation. 

    Usage

    dataGridView3.ExportCsv(saveFileDialog.FileName);

    Add the class below to your project. Does not do header but the one below does and is a similar version.

    using System;
    using System.IO;
    using System.Linq;
    using System.Windows.Forms;
    
    namespace YourNamespaceName
    {
        public static class DataGridViewExtensions
        {
            /// <summary>
            /// Create a string array from DataGridView rows using by default a comma
            /// between cell data.
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="delimitor"></param>
            /// <returns></returns>
            public static string[] CreateRowsArray(this DataGridView sender, string delimitor = ",")
            {
                return
                (
                    from row in sender.Rows.Cast<DataGridViewRow>()
                    where !((DataGridViewRow)row).IsNewRow
                    let rowItem = string.Join(delimitor, Array.ConvertAll(((DataGridViewRow)row).
                        Cells.Cast<DataGridViewCell>().ToArray(),
                        (DataGridViewCell c) => ((c.Value == null) ? "" : c.Value.ToString())))
                    select rowItem
                ).ToArray();
            }
            /// <summary>
            /// Export row data comma delimited to a text file
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="fileName">path combined with file name and extension</param>
            public static void ExportCsv(this DataGridView sender, string fileName)
            {
                string[] rowData = sender.CreateRowsArray();
                File.WriteAllLines(fileName,rowData);
            }
        }
    }
    Simple version with header.
    public static void ExportRows(this DataGridView sender, string fileName, string delimiter = ",")
    {
        if (sender.RowCount > 0)
        {
            var sb = new StringBuilder();
    
            var headers = sender.Columns.Cast<DataGridViewColumn>();
            sb.AppendLine(string.Join(delimiter, headers.Select(column => column.HeaderText)));
    
            foreach (DataGridViewRow row in sender.Rows)
            {
                if (!row.IsNewRow == true)
                {
                    var cells = row.Cells.Cast<DataGridViewCell>();
                    sb.AppendLine(string.Join(delimiter, cells.Select(cell => cell.Value)));
                }
            }
            File.WriteAllText(fileName, sb.ToString());
        }
        else
        {
            // Do nothing
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by okrus Sunday, April 21, 2019 11:05 AM
    Friday, April 19, 2019 3:25 PM
    Moderator
  • Thank you!!
    Sunday, April 21, 2019 8:19 PM