How to download DataTable in Excel format in C#

By FoxLearn 11/6/2024 2:15:29 PM   67
To download tabular data such as DataTable data in Excel CSV/TSV format in C#, you can follow these steps.

To add a tabular data download feature to a web page.

You need to create CSV or TSV content, then convert your tabular data (e.g., from a DataTable) into a CSV or TSV formatted string.

Next, Send the CSV/TSV Data to the Web Client by using Response.Write() to output plain text data (CSV/TSV) to the browser.

If the data needs to be sent as a file download (e.g., *.csv or *.tsv), ensure to set the appropriate headers and use Response.BinaryWrite() to send a byte array for the file content.

private void ExportToTsv(DataTable dt)
{
    // Create tsv content
    var sb = new StringBuilder();
    // Add tsv header
    var columnNames = dt.Columns.Cast().Select(column => column.ColumnName).ToArray();
    var headers = string.Join("\t", columnNames);
    sb.AppendLine(headers);
    // Add tsv rows
    var rows = dt.AsEnumerable().Select(row => string.Join("\t", row.ItemArray));
    foreach (var r in rows)
        sb.AppendLine(r);
    string tsvContent = sb.ToString();
    // Send data to web client            
    Response.Clear();
    Response.AddHeader("content-disposition", "attachment;filename=data.tsv");
    Response.ContentType = "application/octet-stream";
    Response.Write(tsvContent);
    Response.End();
}