Export DataTable to CSV file in C#

Create CSV file | C# | .NET | Quickest Way




public partial class ExportInExcel : System.Web.UI.Page
    {
        System.Data.DataTable table = null;
        protected void Page_Load(object sender, EventArgs e)
        {
            table = new System.Data.DataTable();
            table.Columns.Add("Dosage", typeof(int));
            table.Columns.Add("Drug", typeof(string));
            table.Columns.Add("Patient", typeof(string));
            table.Columns.Add("Date", typeof(DateTime));

            table.Rows.Add(25, "Indocin", "Sam", DateTime.Now);
            table.Rows.Add(27, "Indocin", "David", DateTime.Now);
            table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
            table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
            table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
            table.Rows.Add(23, "Combivent", "steav", DateTime.Now);
            table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
            GridView1.DataSource = table;
            GridView1.DataBind();
        }

protected void Button2_Click(object sender, EventArgs e)
        {
            ExportTableDataAsCSV(this.Page);
        }
        
        public void ExportTableDataAsCSV(System.Web.UI.Page _page)
        {
            if (table != null && table.Rows.Count > 0)
            {
                string GridViewDataExportedFileInfo = "CSharpeDataTableExportedAsCSV_File_" + DateTime.Now.ToUniversalTime() + ".csv";
                GridViewDataExportedFileInfo = GridViewDataExportedFileInfo.Replace(":", "_").Replace("/", "_").Replace(" ", "_");

                StringBuilder sb = new StringBuilder();
                string[] columnNames = table.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToArray();
                var header = string.Join(",", columnNames);
                sb.AppendLine(header);

                //List<string> valueLines = table.AsEnumerable().Select(row => string.Join(",", row.ItemArray)).ToList();
                //var rows = string.Join(Environment.NewLine, valueLines);
                //sb.AppendLine(rows);

                #region If above 3 lines not working as expected
                foreach (DataRow dr in table.Rows)
                {
                    foreach (DataColumn dc in table.Columns)
                        sb.Append(ValidateColumnData(dr[dc.ColumnName].ToString()) + ",");
                    sb.Remove(sb.Length - 1, 1);
                    sb.AppendLine();
                }
                #endregion

                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.ContentType = "application/octet-stream";
                HttpContext.Current.Response.AddHeader("content-length", sb.Length.ToString());
                HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + GridViewDataExportedFileInfo);
                HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
                HttpContext.Current.Response.Write(sb.ToString());
                HttpContext.Current.Response.End();
            }
            else if (_page != null)
            {
                _page.ClientScript.RegisterStartupScript(this.GetType(), "No Data Alert Box.", "alert('There are no records to Download.');", true);
            }
        }

        public static string ValidateColumnData(string input)
        {
            try
            {
                if (input == null)
                    return string.Empty;

                bool isQuote = false;
                bool isComma = false;
                int len = input.Length;
                for (int i = 0; i < len && (isComma == false || isQuote == false); i++)
                {
                    char ch = input[i];
                    if (ch == '"')
                        isQuote = true;
                    else if (ch == ',')
                        isComma = true;
                }

                if (isQuote)
                    input = input.Replace("\"", "\"\"");

                if (isComma)
                    return "\"" + input + "\"";
                else
                    return input;
            }
            catch
            {
                throw new Exception(string.Format("Data Parsing Error: Column Data : {0}", input));
            }
        }
    }

Watch The Video:


6 comments:

  1. Hi, can I define a date in the query before exporting?
    I say insert the date after login, as if it were a range of dates to extract a report looking for information in a SQL Azure database?

    ReplyDelete
    Replies
    1. Hi Bruno,
      Yes, you can definately do that. I strongly suggest you to take my code from GitHub and do the sample Implementation according to your need.
      Git Repo Link: https://github.com/TechWebDots/AspNetWebFormsApp.git

      Your feedback is important.
      Don't forget to Follow/Subscribe (if not yet done 🙂) & encourage me to create more such videos.

      If you like these Blogs/Videos, please click on the THUMBS UP👍 and share with others where this might be helpful.

      Happy Coding and Learning,
      Good Luck TechWebDots!

      Delete
  2. hi,is there reference I should add to project to use using Microsoft.Office.Interop.Excel ?

    ReplyDelete
    Replies
    1. Hi ,
      I strongly suggest you to take my code & Check all the required references from GitHub.
      Git Repo Link: https://github.com/TechWebDots/AspNetWebFormsApp.git

      Your feedback is important.
      Don't forget to Follow/Subscribe (if not yet done 🙂) & encourage me to create more such videos.

      If you like these Blogs/Videos, please click on the THUMBS UP👍 and share with others where this might be helpful.

      Happy Coding and Learning,
      Good Luck TechWebDots!

      Delete
  3. Replies
    1. Hi Mark,
      Thank you very much for taking time to give feedback. Your feedback is important.
      Don't forget to Follow/Subscribe (if not yet done 🙂) & encourage me to create more such videos.

      If you like these Blogs/Videos, please click on the THUMBS UP👍 and share with others where this might be helpful.

      Happy Coding and Learning,
      Good Luck TechWebDots!

      Delete

Your feedback is important.
Visit www.techwebdots.in