Export of CSV data

Question

Personal someone would have some example of how to create a web data export on . CSV? I already have one on my system on . XLS, but I need to modify it, someone can help?

Controller

private void ExportacaoDados(DataSet data, string fileName, int type = 0)
        {
            if (type == 0)
            {
                var gv = new GridView();
                gv.DataSource = data;
                gv.DataBind();

                Response.Clear();
                Response.ClearContent();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment; filename=" + fileName + ".xls");
                Response.ContentType = "application/vnd.ms-excel";
                Response.Charset = "";
                var sw = new StringWriter();
                var htw = new HtmlTextWriter(sw);
                gv.RenderControl(htw);
                Response.Output.Write(sw.ToString());

                Response.Flush();
                Response.Close();
                Response.End();
            }
            else
            {
                var sw = new StringWriter();
                sw.Write(ExportToCSVFile(data.Tables[0]));

                Response.Clear();
                Response.ClearContent();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment; filename=" + fileName + ".txt");
                Response.ContentType = "text/plain";
                Response.Charset = "";
                var htw = new HtmlTextWriter(sw);
                Response.Output.Write(sw.ToString());

                Response.Flush();
                Response.Close();
                Response.End();
            }
        }

        private string ExportToCSVFile(DataTable dtTable)
        {
            var sbldr = new StringBuilder();

            foreach (DataColumn c in dtTable.Columns)
            {
                sbldr.Append(Regex.Replace(c.ColumnName, @"\n|\t|\r", "") + "|");
            }

            sbldr.Append("\r\n");
            foreach (DataRow row in dtTable.Rows)
            {
                foreach (DataColumn column in dtTable.Columns)
                {
                    sbldr.Append(Regex.Replace(row[column].ToString(), @"\n|\t|\r", "") + "|");
                }
                sbldr.AppendLine();
            }

            return sbldr.ToString();
        }

Answer 1

This code of yours, I believe is correct, I just find it unnecessary to replace the column name, and, use , or ; to separate the fields, not the pipe | but, okay.

    private string ExportToCSVFile(DataTable dtTable)
    {
        var sbldr = new StringBuilder();

        foreach (DataColumn c in dtTable.Columns)
        {
            sbldr.Append(c.ColumnName+ ";");
        }

        sbldr.Append("\r\n");
        foreach (DataRow row in dtTable.Rows)
        {
            foreach (DataColumn column in dtTable.Columns)
            {
                sbldr.Append(Regex.Replace(row[column].ToString(), @"\n|\t|\r", "") + ";");
            }
            sbldr.AppendLine();
        }

        return sbldr.ToString();
    }

If you want to continue with it, then write the return in a text file:

string csv = ExportToCSVFile(dtTable);
using (TextWriter tw = new StreamWriter(arquivo, false, Encoding.Default))
{
   tw.Write(csv);
   tw.Close();
}

where: arquivo is a string with the file path that you will save, and then make available for download.

Depending on the size of the content, I think it will be faster if you open Writer within the method that generates csv and write to the file instead of saving it in a string.

If possible, use the library ready for this, certainly the result will be better, avoid loss of line breaks, tab and escape characters inside strings.

Answer 2

First piece of advice I give you, try adapting your code so it gets a typed list instead of a Datatable. However the example below will lead to count that it will continue with Datatable.

Another point, writing a CSV file involves dozens of small details, so doing it manually will make your code quite error-prone, so I advise you to use the CsvHelper.

private void ExportacaoDados(DataSet data, string fileName, int type = 0)
{
    Response.Clear();
    Response.ClearContent();
    Response.AddHeader("content-disposition", "attachment; filename=" + fileName + ".txt");
    Response.ContentType = "text/csv";
    ExportToCSVFile(Response.OutputStream, data.Tables[0]);
    Response.Flush();
    Response.Close();
    Response.End();
}

private void ExportToCSVFile(Stream stream, DataTable table)
{
    var encoding = Encoding.GetEncoding("ISO-8859-15");
    using (var writer = new StreamWriter(stream, encoding, 1024, true))
    {
        using (var csv = new CsvHelper.CsvWriter(writer))
        {
            foreach (var column in table.Columns)
            {
                csv.WriteField(column.ColumnName);
            }
            csv.NextRecord();
            foreach (var row in table.Rows)
            {
                for (var i = 0; i < table.Columns.Count; i++)
                {
                    csv.WriteField(row[i]);
                }
                csv.NextRecord();
            }
            writer.Flush();
        }
    }
}