locked
Export a .net System.Web.UI.WebControls.Table to Excel and retaining formtting RRS feed

  • Question

  • User777441072 posted

    I have an application that generates a couple of instances of System.Web.UI.WebControls.Table dynamically.  The TableCells in each Table are formatted.  I would like to export each Table to a different sheet of an Excel Workbook whilst till retaining formatting.

    Please assist

    Thursday, December 6, 2012 1:16 AM

Answers

All replies

  • User-759277397 posted

    I am afraid you may need a third party library since MS Office control can not be directly used in Web and it needs complex configurariuons. So normally speaking it is not suggested if you use it on Web. While other MS Excel components can do that directly. What I am familiar is a .NET Excel component, it can directly used to export binding data to Excel by below code and remain original format. However, if there are images in your data, you want to export it to excel and remain format, that is ok, but the code maybe changed in another way. Since I never try in this case, you can ask for support. Hope you good luck.

     Private Sub Run_Click(ByVal sender As Object, ByVal e As EventArgs)
            Dim workbook As Workbook = New Workbook
            Dim sheet As Worksheet = workbook.Worksheets(0)
            sheet.InsertDataTable(CType(Me.dataGridView1.DataSource,DataTable), true, 2, 1, -1, -1)
            workbook.SaveToFile(sample.xls)
            ExcelDocViewer(workbook.FileName)
        End Sub
        
        Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs)
            Dim workbook As Workbook = New Workbook
            workbook.LoadFromFile(D:, michelle, my, file, FandH.xls)
            Dim sheet As Worksheet = workbook.Worksheets(0)
            Me.dataGridView1.DataSource = sheet.ExportDataTable
        End Sub




    Thursday, December 6, 2012 10:16 PM
  • User-489377026 posted

    Hi eweggelaar,

    First thank you Pandalin provided solution.

    Export table data to excel and retaining formatting, please refer this blog:

    http://www.aspdotnet-suresh.com/2011/04/how-to-export-gridview-data-to-excel-or.html

    Best Regards
     

    Wednesday, December 12, 2012 9:15 PM
  • User777441072 posted

    Thank you Mark. 

    This article however does not help much unfortunately.  It does show one how to export to Excel but it does not show how to export say 4 System.Web.UI.WebControls.Table controls on a web form, each one to a different worksheet in an excel workbook whilst applying the save formatting as the Table Controls. 

    Since the table controls on my web page are already formatted, I dont want to reinvent the wheel by writing code to format the data that I am exporting.  The method in this article does take the formatting of a table with it to Excel although it does not demonstrate how to export formatted Tables to different worksheets in excel.

    I have the following tables for example and need code to export each of these tables to Excel on a different worksheet.  Preferably I dont want to code the formatting when the export button is pressed (what ever formatting has been applied to these tables, should be exported): 


    <asp:Table ID="Table1" runat="server" BackColor="#FFFFCC" BorderColor="#0066FF" borderStyle="Solid" BorderWidth="1px" Font-Names="Arial" Font-Size="Small">

           <asp:TableRow runat="server">

    <asp:TableCell runat="server">Date</asp:TableCell>

    <asp:TableCell runat="server">Production</asp:TableCell>

           </asp:TableRow>

           <asp:TableRow runat="server">

           <asp:TableCell runat="server" HorizontalAlign="Right">2012/12/01</asp:TableCell>

           <asp:TableCell runat="server" HorizontalAlign="Right">10</asp:TableCell>

           </asp:TableRow>

           <asp:TableRow runat="server">

           <asp:TableCell runat="server">2012/12/02</asp:TableCell>

           <asp:TableCell runat="server" HorizontalAlign="Right">11</asp:TableCell>

           </asp:TableRow>

           <asp:TableRow runat="server">

           <asp:TableCell runat="server">2012/12/03</asp:TableCell>

           <asp:TableCell runat="server" HorizontalAlign="Right">11</asp:TableCell>

           </asp:TableRow>

           <asp:TableRow runat="server">

           <asp:TableCell runat="server">2012/12/04</asp:TableCell>

           <asp:TableCell runat="server" HorizontalAlign="Right">12</asp:TableCell>

           </asp:TableRow>

           <asp:TableRow runat="server" HorizontalAlign="Right">

           <asp:TableCell runat="server">2012/12/05</asp:TableCell>

           <asp:TableCell runat="server">9</asp:TableCell>

           </asp:TableRow>

     </asp:Table>

    <asp:Table ID="Table2" runat="server" BackColor="#FFCC66" BorderColor="#0066FF"

    BorderStyle="Solid" BorderWidth="1px" Font-Names="Arial" Font-Size="Small">

    <asp:TableRow ID="TableRow1" runat="server">

             <asp:TableCell ID="TableCell1" runat="server">Date</asp:TableCell>

              <asp:TableCell ID="TableCell2" runat="server">Downtime</asp:TableCell>

           </asp:TableRow>

    <asp:TableRow ID="TableRow2" runat="server">

               <asp:TableCell ID="TableCell3" runat="server" HorizontalAlign="Right">2012/12/01</asp:TableCell>

             <asp:TableCell ID="TableCell4" runat="server" HorizontalAlign="Right">0</asp:TableCell>

           </asp:TableRow>

           <asp:TableRow ID="TableRow3" runat="server">

           <asp:TableCell ID="TableCell5" runat="server">2012/12/02</asp:TableCell>

               <asp:TableCell ID="TableCell6" runat="server" HorizontalAlign="Right">2.5</asp:TableCell>

           </asp:TableRow>

           <asp:TableRow ID="TableRow4" runat="server">

           <asp:TableCell ID="TableCell7" runat="server">2012/12/03</asp:TableCell>

                   <asp:TableCell ID="TableCell8" runat="server" HorizontalAlign="Right">11</asp:TableCell>

           </asp:TableRow>

           <asp:TableRow ID="TableRow5" runat="server">

           <asp:TableCell ID="TableCell9" runat="server">2012/12/04</asp:TableCell>

               <asp:TableCell ID="TableCell10" runat="server" HorizontalAlign="Right">1</asp:TableCell>

           </asp:TableRow>

           <asp:TableRow ID="TableRow6" runat="server" HorizontalAlign="Right">

           <asp:TableCell ID="TableCell11" runat="server">2012/12/05</asp:TableCell>

              <asp:TableCell ID="TableCell12" runat="server">0</asp:TableCell>

           </asp:TableRow>

       </asp:Table>

       

    Thursday, December 13, 2012 1:13 AM
  • User777441072 posted

    I found this:  http://forums.asp.net/t/1860267.aspx/1 which shows how to change the Mime type and using OpenXML to create multiple worksheets.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 14, 2012 4:52 AM