Export Data to Excel with formatting in web

Ravi Kumar Mishra

5.00/5 (1 vote)

Mar 11, 2013

CPOL

18528

Data export from data table or dataset to Excel with formatting.

Introduction

On the web when developers are exporting data to Excel there is a problem with the formatting of cells. With the help of this code developers can easily do cell formatting.

Using the code

A brief description of how to use the code is given below. Use the below function in your web page in your code-behind. The function dynamically generates an Excel worksheet.

Private Sub ExporttoExcel(ByVal table As DataTable)
    HttpContext.Current.Response.Clear()
    HttpContext.Current.Response.ClearContent()
    HttpContext.Current.Response.ClearHeaders()
    HttpContext.Current.Response.Buffer = True
    HttpContext.Current.Response.ContentType = "application/ms-excel"
    HttpContext.Current.Response.Write("<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">")
    HttpContext.Current.Response.AddHeader("Content-Disposition", _
      "attachment;filename=""EmployeeSurvey_" + _
      Me.dtpdate.Text + ".xls""")
    HttpContext.Current.Response.Charset = "utf-8"
    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250")
    'sets font
    HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Verdana;'>")
    HttpContext.Current.Response.Write("<BR><BR><BR>")
    'sets the table border, cell spacing, border color,
    'font of the text, background, foreground, font height
    HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' " & _
      "borderColor='#000000' cellSpacing='0' cellPadding='0' " & _
      "style='font-size:10.0pt; font-family:Verdana; background:white;'> <TR>")
    'am getting my grid's column headers
    Dim columnscount As Integer = table.Columns.Count
    HttpContext.Current.Response.Write("<Td colspan='5' style='background-color:Maroon;border:solid " & _ 
      "1 #fff;color:#fff;'><B> Employee Appreciation Survey Report for the Month of " & _
      Format(Me.dtpdate.Text) & "</B>")
    HttpContext.Current.Response.Write("</Td>")
    HttpContext.Current.Response.Write("</TR>")
    HttpContext.Current.Response.Write("<TR>")
    For j As Integer = 0 To columnscount - 1
        'write in new column
        HttpContext.Current.Response.Write("<Td>")
        'Get column headers  and make it as bold in excel columns
        HttpContext.Current.Response.Write("<B>")
        HttpContext.Current.Response.Write(table.Columns(j).ToString())
        HttpContext.Current.Response.Write("</B>")
        HttpContext.Current.Response.Write("</Td>")
    Next
    HttpContext.Current.Response.Write("</TR>")
    For Each row As DataRow In table.Rows
        'write in new row
        HttpContext.Current.Response.Write("<TR>")
        For i As Integer = 0 To table.Columns.Count - 1
            HttpContext.Current.Response.Write("<Td>")
            HttpContext.Current.Response.Write(row(i).ToString())
            HttpContext.Current.Response.Write("</Td>")
        Next
        HttpContext.Current.Response.Write("</TR>")
    Next
    HttpContext.Current.Response.Write("</Table>")
    HttpContext.Current.Response.Write("</font>")
    HttpContext.Current.Response.Flush()
    HttpContext.Current.Response.[End]()
End Sub

Points of Interest

Once you understand the schema, the code can be manipulated to format the rows inside Excel.