Community discussion forum

Back to discussion

Export Datagrid to Excel with same formatting

Last post was 09 Jul 2009 at 13:53
Tags: asp.net, Datagrid, Excel India
  • 4 months ago

    Hi, I need to know that how to export the datagrid to excel with [B]same formatting and color where i used in datagrid[/B]. Here I have used different color for different data in my datagrid.

    I need to excel sheet that should be copy of datagrid.

    Post was edited on 04/07/2009 07:03:50 Report abuse
  • 4 months ago

    Hi saranram,

    Best way to do that is to use 3rd party component like GemBox spreadsheet component which is free for commercial use if you need less then 150 rows.

    Filip GemBox.Spreadsheet for .NET - Easily read and write Excel (XLS, XLSX or CSV) files or export to HTML files from your .NET apps.

    Report abuse
  • 4 months ago

    'This takes in a GridView, converts it to Excel, prompts the user to download it 'FileName - The name of the file with no extension (ie: "filename") Private Sub ExcelDownloadFromGridView(ByVal FileName As String, ByVal WorkBookTitle As String)

        'Add the xls to the FileName of the Excel File
    FileName = FileName + ".xls"

    Dim Grid As New DataGrid 'Used to convert to excel

    Grid.HeaderStyle.Font.Bold = True 'Make the columns bold
    Grid.DataSource = Cache("GridTable") '"myGridView.DataSource 'Set in the table to the grid
    Grid.DataMember = Cache("GridTable").TableName 'myGridView.DataMember
    Grid.DataBind()

    'Push the excel file out to the user
    Response.Clear()
    Response.Write("<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">")
    Response.Write("<head>")
    Response.Write("<!--[if gte mso 9]><xml>")
    Response.Write("<x:ExcelWorkbook>")
    Response.Write("<x:ExcelWorksheets>")
    Response.Write("<x:ExcelWorksheet>")
    Response.Write("<x:Name>" & WorkBookTitle & "</x:Name>")
    Response.Write("<x:WorksheetOptions>")
    Response.Write("<x:Print>")
    Response.Write("<x:ValidPrinterInfo/>")
    Response.Write("</x:Print>")
    Response.Write("</x:WorksheetOptions>")
    Response.Write("</x:ExcelWorksheet>")
    Response.Write("</x:ExcelWorksheets>")
    Response.Write("</x:ExcelWorkbook>")
    Response.Write("</xml>")
    Response.Write("<![endif]--> ")
    Response.Write("</head>")
    Response.Write("<body>")
    Response.AddHeader("content-disposition", "attachment;filename=" & FileName)
    Response.Charset = ""
    Response.Cache.SetCacheability(HttpCacheability.NoCache)
    Response.ContentType = "application/vnd.xls"
    Dim stringWrite As System.IO.StringWriter = New System.IO.StringWriter
    Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)
    Grid.RenderControl(htmlWrite)
    Response.Write(stringWrite.ToString)
    Response.Write("</body>")
    Response.Write("</html>")
    Response.End()
End Sub

    To write out your formatting use Grid.items(index).cells(index).Backcolor = or any of the other ones; If already set it will convert over to the Excel Doc

    Report abuse

Post a reply

Enter your message below

Sign in or Join us (it's free).

Ask a question

Related discussion

Quick links

Recent activity

Languages

Web Development

Frameworks & Architecture

Other major sections

Cancel
Want to stay in touch with what's going on? Follow us on twitter!