ASP.Net to Excel export - column width and alternate row coloring

Question

I have the following method that eventually creates a CSV file and returns the file as a response to a client request.

How can I change the column width to autofit and have alternate coloring of rows?

public static void ExportToCSV(DataTable dt, HttpResponseBase response, string filename)
{
    response.AddHeader("content-disposition", "attachment; filename=" + filename);
    response.ClearContent();
    response.ContentType = "application/vnd.ms-excel";
    response.Charset = "UTF-8";
    response.ContentEncoding = System.Text.Encoding.Unicode;
    response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble()); 

    string tab = "";
    foreach (DataColumn dc in dt.Columns)
    {
        if (!dc.ColumnName.StartsWith("_"))
        {
            response.Write(tab + dc.ColumnName);
            tab = "\t";
        }
    }
    response.Write("\n");

    int i;
    foreach (DataRow dr in dt.Rows)
    {
        tab = "";
        for (i = 0; i < dt.Columns.Count; i++)
        {
            if (!dr.Table.Columns[i].ColumnName.StartsWith("_"))
            {
                response.Write(Utils.StripHTML(tab + dr[i].ToString().Replace("\n", " ").Replace("\t", " "))); 
                tab = "\t";
            }
        }
        response.Write("\n");
    }

    response.End();
}

What in this method makes it CSV? I cannot tell. The only thing I recognize is the `response.ContentType = "application/vnd.ms-excel";`. — hermann, Mar 21 '14 at 12:59
Could I change the format to Excel and then make my changes? — hermann, Mar 21 '14 at 12:59
Whatever you do don't use interop. You're creating a tab-delimited file here. I think the best course would be to create an Excel file in XML format instead. One way is to create a sample file in Excel and then save it out in the desired XML format. Then look at the structure and mimic it. — mikey, Mar 21 '14 at 13:57

Fedor Hajdu · Answer 1 · 2014-03-24T15:35:45.567

CSV file is just plain text where values are separated by an agreed separator (usually comma, hence the name, in your case it's TAB character).

Excel has a support for CSV files in such way it represent every value in separate column. This is only visual representation but the file has no actual columns, and cannot contain any form of formatting.

You'd need to change the format of the file to Excel format, and create it that way. You could use Excel interop or writing it as an excel XML file. I'd recommend second option. For a working example of Excel XML you can check this CodeProject example.

Kami · Answer 2 · 2014-03-24T17:36:44.620

You are currently creating a delimited text file.

As the file is a normal text file, it can not have any formatting applied.

Your requirements are to generate an excel file with formatting and certain width, as such this can only be achieved by producing an excel file directly.

This can be done using interop or by outputting the xml required, but some kind of excel library is probably easier to implement without worrying about the details. I personally prefer EPPlus. It is a useful library for creating Excel spreadsheet for excel 2007 onwards. You can install the library using nugget or via a download from the website.

The following gives a quick overview of how to achieve this.

public static void ExportToCSV(DataTable dt, HttpResponseBase response, string filename)
{
    response.AddHeader("content-disposition", "attachment; filename=" + filename);
    response.ClearContent();
    response.ContentType = "application/vnd.ms-excel";
    response.Charset = "UTF-8";
    response.ContentEncoding = System.Text.Encoding.Unicode;
    response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble()); 

    ExcelPackage package = new ExcelPackage();
    package.Workbook.Properties.Comments = "Demo Excel Generation";
    package.Workbook.Worksheets.Add("DemoSheet");

    ExcelWorksheet sheet = package.Workbook.Worksheets["DemoSheet"];

    bool altColour = false;
    for (int i = 1; i < 10; i++)
    {
        for (int j = 1; j < 10; j++)
        {
            sheet.Cells[i, j].Value = string.Format("{0} - {1}", i, j);
            sheet.Row(j).Style.Fill.PatternType = ExcelFillStyle.Solid;
            sheet.Row(j).Style.Fill.BackgroundColor.SetColor(altColour ? Color.Gold : Color.Goldenrod);
            altColour = !altColour;
        }

        sheet.Column(i).AutoFit(5f); // Set minimum width to 5 points
    }

    //package.File = new System.IO.FileInfo(@"C:\test.xlsx");
    //package.Save();
    package.SaveAs(response.OutputStream);
    Response.End();
}

How would you incorporate this code into the method I posted? This method is a response to a web service. — hermann, Mar 24 '14 at 16:07
@hermann I have updated the code to wrap in the ExportToCSV method. It will require testing but this is how it should work. You will need to replace the loops with your own - to generate whatever you need. — Kami, Mar 24 '14 at 17:38

Raidri · Accepted Answer · 2018-10-26T23:33:33.500

1

Another library is ClosedXML which has (in my opinion) a nicer syntax than other possibilities by allowing to chain methods. For example:

XLWorkbook workbook = new XLWorkbook("test.xlsx");
IXLWorksheet worksheet = workbook.AddWorksheet("Sheet1");
worksheet.Cell(1, 1).SetValue("Test").Style.Font.SetBold(true);
worksheet.AdjustToContent(); // this changes the column width to fit the content
workbook.Save();

edited Oct 26 '18 at 23:33

answered Mar 24 '14 at 15:48

Raidri

17,258
9
62
65

How would you incorporate this code into the method I posted? This method is a response to a web service. – hermann Mar 24 '14 at 16:09

ASP.Net to Excel export - column width and alternate row coloring

3 Answers3