Click here to Skip to main content
Click here to Skip to main content
Go to top

Export Data to Excel with formatting in web

, 11 Mar 2013
Rate this:
Please Sign up or sign in to vote.
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, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ravi Kumar Mishra
Software Developer (Senior)
India 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.

Comments and Discussions

 
QuestionA little query Pinmember007alok19-Mar-13 11:16 
What data it supposed to fetch you will not describe any sql query from which we want to fetch the data ?

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140922.1 | Last Updated 11 Mar 2013
Article Copyright 2013 by Ravi Kumar Mishra
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid