(untagged)

Export Data to Excel with formatting in web

Ravi Kumar Mishra

0.00/5 (No votes)

11 Mar 2013

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here