Back to discussion

Export Datagrid to Excel with same formatting

Last post was 09 Jul 2009 at 13:53
asp.net , Excel , Datagrid India
  • 11 years 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.

    Report abuse
  • 11 years 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
  • 11 years 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).

Excel forum discussion

Quick links

Recent activity

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Web Development

Developer Jobs

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

Cancel
“Anyone who considers arithmetic methods of producing random digits is, of course, in a state of sin.” - John von Neumann