Export Datagrid to Excel with same formatting

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.

  • 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.

  • 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

Post a reply

Enter your message below

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

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!

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.

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