Attention: We are retiring the ASP.NET Community Blogs. Learn more >

Exporting Dataset as CSV

Hi Everyone,

Below given an easy way to export data from a dataset as CSV(comma seperated values) . At first, it converts the datatable to html table format and then writes data as output stream. We need to set the Content-Type of Response object as Excel format and add the filename to be streamed on the client browser in a dialog box

Check this snippet

private void ExportToCsvFromDataSet(DataSet dsExport) {

bool IsOutputStreamed = false;

try {

StringBuilder dataToExport = new StringBuilder();

foreach (DataTable dtExport in dsExport.Tables) {

string headerToExport= string.Empty;

foreach (DataColumn dCol in dtExport.Columns)

headerToExport = (char)34 + dCol.ColumnName + (char)34 + (char)44;

headerToExport.Remove(headerToExport.Length - 1, 1);

headerToExport = headerToExport + Environment.NewLine + Environment.NewLine;

dataToExport.Append(headerToExport);

string bodyToExport = string.Empty;

foreach (DataRow dRow in dtExport.Rows) {

foreach (object obj in dRow.ItemArray)

bodyToExport = bodyToExport + obj.ToString() + (char)44;

bodyToExport.Remove(bodyToExport.Length - 1, 1);

bodyToExport = bodyToExport + Environment.NewLine;

}

dataToExport.Append(bodyToExport);

dataToExport.Append(Environment.NewLine);

dataToExport.Append(Environment.NewLine);

if (string.IsNullOrEmpty(dataToExport.ToString())) {

Response.Clear();

Response.ContentType = "Text/vnd.ms-excel";

Response.AddHeader("Content-Disposition", "attachment;filename=report.csv");

Response.Write(dataToExport.ToString());

IsOutputStreamed = true;

}

}

}

catch { }

finally {

if (IsOutputStreamed)

Response.End();

}

}

Download the source code

5 Comments

  • i like it n want to use it

  • Here's an alternative that returns the CSV as a string:

    public static string ConvertDataSetToCSV(DataSet ds)
    {
    const string LC_COMMA = ",";
    const string LC_DBLQUOTE = "\"";
    const string LC_DBLQUOTE_ESC = "\"\"";

    StringBuilder csv = new StringBuilder();

    foreach (DataTable tbl in ds.Tables)
    {
    // Append the table's column headers.
    foreach (DataColumn col in tbl.Columns)
    {
    csv.Append(LC_DBLQUOTE + col.ColumnName + LC_DBLQUOTE + LC_COMMA);
    }
    csv.Length -= 1;
    csv.Append(Environment.NewLine);

    // Append the table's data.
    foreach (DataRow row in tbl.Rows)
    {
    foreach (object val in row.ItemArray)
    {
    csv.Append(LC_DBLQUOTE + val.ToString().Replace(LC_DBLQUOTE, LC_DBLQUOTE_ESC)
    + LC_DBLQUOTE + LC_COMMA);
    }
    csv.Length -= 1;
    csv.Append(Environment.NewLine);
    }

    // Add an empty line between this and the next table.
    csv.Append(Environment.NewLine);
    }

    return csv.ToString();
    }

  • тема не раскрыта.. может есть ещё информация по этому поводу?

  • nice. this works well. I'd call replace on the cell strings to make sure no extra commas get into the csv:

    foreach (object curRow in tempArray)
    { bodyToExport.Append(curRow.ToString().Replace(",", " ")); bodyToExport.Append((char)44);
    }

  • what is 'response' ?
    my code is not works with this variable

Comments have been disabled for this content.