Export a .net System.Web.UI.WebControls.Table to Excel and retaining formtting RSS

• • Reply
  

  eweggelaar

  0 Points

  3 Posts

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

  Dec 06, 2012 05:16 AM|LINK

  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

• • Reply
  

  Pandalin

  Member

  34 Points

  6 Posts

  Re: Export a .net System.Web.UI.WebControls.Table to Excel and retaining formtting

  Dec 07, 2012 02:16 AM|LINK

  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

  
  
  

  
  

  • Edited by Pandalin on Dec 07, 2012 02:23 AM
• • Reply
  

  Mark - MSFT

  Contributor

  7071 Points

  435 Posts

  Microsoft

  Re: Export a .net System.Web.UI.WebControls.Table to Excel and retaining formtting

  Dec 13, 2012 01:15 AM|LINK

  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
   

  Please mark the replies as answers if they help or unmark if not.
  If you have any feedback about my replies, please contact msdnmg@microsoft.com
  Microsoft One Code Framework
• • Reply
  

  eweggelaar

  0 Points

  3 Posts

  Re: Export a .net System.Web.UI.WebControls.Table to Excel and retaining formtting

  Dec 13, 2012 05:13 AM|LINK

  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>

     

• • Reply
  

  eweggelaar

  0 Points

  3 Posts

  Re: Export a .net System.Web.UI.WebControls.Table to Excel and retaining formtting

  Dec 14, 2012 08:52 AM|LINK

  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 eweggelaar on Dec 14, 2012 08:53 AM