Export Data to Excel with formatting in web

Ravi Kumar Mishra

Rate me:

5.00/5 (1 vote)

11 Mar 2013CPOL

18.2K

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.

VB.NET

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, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Ravi Kumar Mishra

Software Developer (Senior)

India

I earned both a Bachelors and Masters of computer application from the University of UP Tech India. I have 7 years experience in software development on different technology.I have experience writing and deploying applications in vb.net, C#, ASP.net 1.0 to 4.0, Sql Server, Oracle, XML and JavaScript but primarily develops software in VB.net. I have developed and deployed many enterprise applications for Artech info system and UV Intl.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Export Data to Excel with formatting in web

Introduction

Using the code

Points of Interest

License

Comments and Discussions