Exporting CSV data

1

Personal someone would have some example of how to create a web data export in .CSV? I already have one in my system in .XLS, however I need to modify it, can anyone 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();
        }
    
asked by anonymous 11.10.2017 / 16:08

2 answers

0

This code, I believe is correct, I only find Replace unnecessary in the column name, and use , or ; to separate the fields, not the | pipe, but ok. >

    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, and 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 path of the file you will save, and then make it available for download.

  

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

     

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

    
11.10.2017 / 20:38
0

First advice I give you, try adapting your code so that it gets a typed list in place of a DataTable. However the example below will take into account that you will continue with the DataTable.

On another point, writing a CSV file involves dozens of small details, so doing it manually will make your code quite prone to errors, so I advise you to use 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();
        }
    }
}
    
11.10.2017 / 21:12