[CLOSED] Export To Excel with multiple tabs

  1. #1

    [CLOSED] Export To Excel with multiple tabs

    Hi,
    I need to export multiple Store of data to one Excel file. Each Store data gets exported to a different tab within an Excel file. My code below only exports one Store of data. Can you please show me how to export Store1 and Store2 to the "submittedData.xls" file with Store1's data goes into the first tab and Store2 goes into the second tab? Also, how do you assign names to the tabs? I would like to name it something other than the word "record".

    I attached my code for your review. Thank you very much in advance for your help!

    Below is my code:
    <%@ Page Language="C#" %>
    
    <%@ Import Namespace="System.Xml.Xsl" %>
    <%@ Import Namespace="System.Xml" %>
    
    <%@ Register Assembly="Ext.Net" Namespace="Ext.Net" TagPrefix="ext" %>
    
    <script runat="server">
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.Store1.DataSource = new object[]
                {
                    new object[] { 1, "3m Co", 71.72, 0.02, 0.03, "9/1 12:00am" },
                    new object[] { 2, "Alcoa Inc", 29.01, 0.42, 1.47, "9/1 12:00am" },
                    new object[] { 3, "Altria Group Inc", 83.81, 0.28, 0.34, "9/1 12:00am" },
                    new object[] { 4, "American Express Company", 52.55, 0.01, 0.02, "9/1 12:00am" },
                    new object[] { 5, "American International Group, Inc.", 64.13, 0.31, 0.49, "9/1 12:00am" },
                    new object[] { 6, "AT&T Inc.", 31.61, -0.48, -1.54, "9/1 12:00am" },
                    new object[] { 7, "Boeing Co.", 75.43, 0.53, 0.71, "9/1 12:00am" },
                    new object[] { 8, "Caterpillar Inc.", 67.27, 0.92, 1.39, "9/1 12:00am" },
                    new object[] { 9, "Citigroup, Inc.", 49.37, 0.02, 0.04, "9/1 12:00am" },
                    new object[] { 10, "E.I. du Pont de Nemours and Company", 40.48, 0.51, 1.28, "9/1 12:00am" },
                    new object[] { 11, "Exxon Mobil Corp", 68.1, -0.43, -0.64, "9/1 12:00am" },
                    new object[] { 12, "General Electric Company", 34.14, -0.08, -0.23, "9/1 12:00am" }
                };
    
                this.Store2.DataSource = new object[]
                {
                    new object[] { 13, "General Motors Corporation", 30.27, 1.09, 3.74, "9/1 12:00am" },
                    new object[] { 14, "Hewlett-Packard Co.", 36.53, -0.03, -0.08, "9/1 12:00am" },
                    new object[] { 15, "Honeywell Intl Inc", 38.77, 0.05, 0.13, "9/1 12:00am" },
                    new object[] { 16, "Intel Corporation", 19.88, 0.31, 1.58, "9/1 12:00am" },
                    new object[] { 17, "International Business Machines", 81.41, 0.44, 0.54, "9/1 12:00am" },
                    new object[] { 18, "Johnson & Johnson", 64.72, 0.06, 0.09, "9/1 12:00am" },
                    new object[] { 19, "JP Morgan & Chase & Co", 45.73, 0.07, 0.15, "9/1 12:00am" },
                    new object[] { 20, "McDonald\"s Corporation", 36.76, 0.86, 2.40, "9/1 12:00am" },
                    new object[] { 21, "Merck & Co., Inc.", 40.96, 0.41, 1.01, "9/1 12:00am" },
                    new object[] { 22, "Microsoft Corporation", 25.84, 0.14, 0.54, "9/1 12:00am" },
                    new object[] { 23, "Pfizer Inc", 27.96, 0.4, 1.45, "9/1 12:00am" },
                    new object[] { 24, "The Coca-Cola Company", 45.07, 0.26, 0.58, "9/1 12:00am" },
                    new object[] { 25, "The Home Depot, Inc.", 34.64, 0.35, 1.02, "9/1 12:00am" },
                    new object[] { 26, "The Procter & Gamble Company", 61.91, 0.01, 0.02, "9/1 12:00am" },
                    new object[] { 27, "United Technologies Corporation", 63.26, 0.55, 0.88, "9/1 12:00am" },
                    new object[] { 28, "Verizon Communications", 35.57, 0.39, 1.11, "9/1 12:00am" },
                    new object[] { 29, "Wal-Mart Stores, Inc.", 45.45, 0.73, 1.63, "9/1 12:00am" }
                };
                
                this.Store1.DataBind();
                this.Store2.DataBind();
            }
        }
    
        protected void ToExcel(object sender, EventArgs e)
        {
            string json = GridData.Value.ToString();
            StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
            XmlNode xml = eSubmit.Xml;
    
            this.Response.Clear();
            this.Response.ContentType = "application/vnd.ms-excel";
            this.Response.AddHeader("Content-Disposition", "attachment; filename=submittedData.xls");
            XslCompiledTransform xtExcel = new XslCompiledTransform();
            xtExcel.Load(Server.MapPath("Excel.xsl"));
            xtExcel.Transform(xml, null, this.Response.OutputStream);
            this.Response.End();
        }
    
      
    </script>
    
    <!DOCTYPE html>
    
    <html>
    <head id="Head1" runat="server">
        <title>Export Data  to Excel from Ext.NET 2.0 Examples</title> 
        
        <script type="text/javascript">
            var saveData = function () {
                App.GridData.setValue(Ext.encode(App.Store1.getRecordsValues({ selectedOnly: false })));
            };
        </script>
    </head>
    <body>
        <form id="Form1" runat="server">
            <ext:ResourceManager ID="ResourceManager1" runat="server" />
            
            <h1>Export Data to Excel in multiple Tabs</h1>
            
            <ext:Hidden ID="GridData" runat="server" />
            
            <ext:Store ID="Store1" runat="server">
                <Model>
                    <ext:Model ID="Model1" runat="server">
                        <Fields>
                            <ext:ModelField Name="id" Type="Int" />
                            <ext:ModelField Name="company" />
                            <ext:ModelField Name="price" Type="Float" />
                            <ext:ModelField Name="change" Type="Float" />
                            <ext:ModelField Name="pctChange" Type="Float" />
                            <ext:ModelField Name="lastChange" Type="Date" DateFormat="M/d hh:mmtt" />
                        </Fields>
                    </ext:Model>
                </Model>
            </ext:Store>        
            
             <ext:Store ID="Store2" runat="server">
                <Model>
                    <ext:Model ID="Model2" runat="server">
                        <Fields>
                            <ext:ModelField Name="Deptid" Type="Int" />
                            <ext:ModelField Name="CostCenter" />
                            <ext:ModelField Name="Cost" Type="Float" />
                            <ext:ModelField Name="Costchange" Type="Float" />
                            <ext:ModelField Name="pctChange" Type="Float" />
                            <ext:ModelField Name="lastChange" Type="Date" DateFormat="M/d hh:mmtt" />
                        </Fields>
                    </ext:Model>
                </Model>
            </ext:Store>    
            <ext:GridPanel 
                ID="GridPanel1" 
                runat="server" 
                StoreID="Store1"
                Title="Export Data"
                Width="600" 
                Height="350">
                <ColumnModel ID="ColumnModel1" runat="server">
                    <Columns>
                        <ext:Column ID="Column1" runat="server" Text="Company" DataIndex="company" Flex="1" />
                        <ext:Column ID="Column2" runat="server" Text="Price" Width="75" DataIndex="price">
                            <Renderer Format="UsMoney" />
                        </ext:Column>
                        <ext:Column ID="Column3" runat="server" Text="Change" Width="75" DataIndex="change" />
                        <ext:Column ID="Column4" runat="server" Text="Pct.Change" Width="75" DataIndex="pctChange" />
                        <ext:DateColumn ID="DateColumn1" runat="server" Text="Last Updated" Width="85" DataIndex="lastChange" />
                    </Columns>
                </ColumnModel>
                <SelectionModel>
                    <ext:RowSelectionModel ID="RowSelectionModel1" runat="server" Mode="Multi" />
                </SelectionModel>
                <View>
                    <ext:GridView ID="GridView1" runat="server" StripeRows="true" TrackOver="true" />
                </View>
                <TopBar>
                    <ext:Toolbar ID="Toolbar1" runat="server">
                        <Items>
                            <ext:ToolbarFill ID="ToolbarFill1" runat="server" />
                            
                            <ext:Button ID="Button2" runat="server" Text="To Excel" AutoPostBack="true" OnClick="ToExcel" Icon="PageExcel">
                                <Listeners>
                                    <Click Fn="saveData" />
                                </Listeners>
                            </ext:Button>
                       
                        </Items>
                    </ext:Toolbar>
                </TopBar>
            </ext:GridPanel>  
        </form>
    </body>
    </html>
    Last edited by Daniil; Jun 26, 2012 at 9:19 PM. Reason: [CLOSED]
  2. #2
    Hi,

    There are two tasks:

    1. Get the two Stores data on server.

    You should call the getRecordsValues method for the second Store as well and combine two results of calling the getRecordsValues for the both Stores. This way you can get the Stores data on server.

    Here is some example how to use the GridPanel getRowsValues for the same task.
    https://examples2.ext.net/#/GridPane...mit_Two_Grids/

    2. Convert these data to Excel format.

    Generally, it is out of our competence. There are no any special things in Ext.NET to convert to Excel format. We just created the example to demonstrate some basics and the fact that it's possible. Unfortunately, we are not experts in this area. I would try to look it up on the internet or ask on some specialized forums like some Microsoft .NET forums or stackoverflow.
  3. #3
    Hello

    Here my own solution :

    Public Sub ExportToExcel(sender As Object, e As Ext.Net.DirectEventArgs)
            Dim grd As Ext.Net.GridPanel = Ext.Net.X.GetCmp(e.ExtraParams("Grid"))
            Dim ColumnModel As GridHeaderContainer = grd.ColumnModel
            Dim LST_Field As List(Of String) = New List(Of String)
            Dim LST_FieldName As List(Of String) = New List(Of String)
            Dim LST_Lines As List(Of strucData) = JsonConvert.DeserializeObject(Of List(Of strucData))(e.ExtraParams("data"))
            Dim Line As String = ""
            Dim FileName As String = ""
    
            For i As Integer = 5 To ColumnModel.Columns.Count - 1
                LST_Field.Add(ColumnModel.Columns(i).DataIndex)
                LST_FieldName.Add(ColumnModel.Columns(i).Text)
            Next
    
    
            Select Case grd.ID
                Case "grdOSA"
                    FileName = "OSA Data"
                Case "grdMASK"
                    FileName = "Mask Data"
                Case "grdVEN"
                    FileName = "Ventilation Data"
                Case "grdTPP"
                    FileName = "Third Party Product Data"
                Case "grdATS"
                    FileName = "Activity Time Split Data"
                Case "grdTRS"
                    FileName = "Time Repair Split Data"
                Case "grdPS"
                    FileName = "Product Split Data"
                Case "grdFCC"
                    FileName = "Call Center Data"
                Case "grdLO_OM"
                    FileName = "Logistic Order Metrics Data"
                Case "grdLO_AVA"
                    FileName = "Logistic availability Data"
                Case "grdFI"
                    FileName = "Financial Data"
            End Select
    
            'Collumns heading
            Dim row As New TableRow
            Dim cell(LST_Field.Count - 1) As TableCell
            For i As Integer = 0 To LST_Field.Count - 1
                cell(i) = New TableCell
                cell(i).Text = LST_FieldName(i)
                row.Cells.Add(cell(i))
            Next
            CType(Page.FindControl("tblExport"), Table).Rows.Add(row)
    
            For i As Integer = 0 To LST_Lines.Count - 1
                row = New TableRow
                Line = JsonConvert.SerializeObject(LST_Lines(i))
                For j As Integer = 0 To LST_Field.Count - 1
                    cell(j) = New TableCell
                    cell(j).Text = GlobalFunction.DecodeJSON(Line, LST_Field(j), LST_Field(j).Substring(0, 3))
                    row.Cells.Add(cell(j))
                Next
                CType(Page.FindControl("tblExport"), Table).Rows.Add(row)
            Next
    
            'renseignement de l'objet response / Response object filling
            Response.Clear()
            Response.Buffer = True
            Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName + " Export.xls")
            Response.ContentEncoding = System.Text.Encoding.Default
            Response.ContentType = "application/vnd.ms-excel"
            Response.Write(GenHtmlTable())
            Response.End()
        End Sub
    e.ExtraParams("Grid")
    is the grid name

    (e.ExtraParams("data")
    are the grid data

    The call :
                                                                        <ext:Button ID="btnExportOSA" runat="server" Text="To Excel" Icon="PageExcel">
                                                                            <DirectEvents >
                                                                                <Click OnEvent="ExportToEXcel" IsUpload="true">
                                                                                    <ExtraParams>
                                                                                        <ext:Parameter Name="data" Value="#{grdOSA}.getRowsValues({selectedOnly : false})" Mode="Raw" Encode="true"></ext:Parameter>
                                                                                        <ext:Parameter Name="Grid" Value="grdOSA" Mode="Value" Encode="False"></ext:Parameter>
                                                                                     </ExtraParams>
                                                                                </Click>
                                                                            </DirectEvents>
                                                                        </ext:Button>
    I didn't use the way you choose because it exports all the data from the grid even some I didn't want user to see, so I made my own routine.

    I imagine by using the same method, you could choose to export several store.
  4. #4
    Hi,
    Thank you for your reply.
    I have no problem getting the data on the server. I just have problem figuring out how to start a new tab and putting data into that each tab.
    Any suggestion in that area?

    Thank you.


    Quote Originally Posted by Daniil View Post
    Hi,

    There are two tasks:

    1. Get the two Stores data on server.

    You should call the getRecordsValues method for the second Store as well and combine two results of calling the getRecordsValues for the both Stores. This way you can get the Stores data on server.

    Here is some example how to use the GridPanel getRowsValues for the same task.
    https://examples2.ext.net/#/GridPane...mit_Two_Grids/

    2. Convert these data to Excel format.

    Generally, it is out of our competence. There are no any special things in Ext.NET to convert to Excel format. We just created the example to demonstrate some basics and the fact that it's possible. Unfortunately, we are not experts in this area. I would try to look it up on the internet or ask on some specialized forums like some Microsoft .NET forums or stackoverflow.
  5. #5
    Thank you Feanor91 for the great idea on getting the data.! Do you have any idea on putting the data into different tabs?

    Quote Originally Posted by feanor91 View Post
    Hello

    Here my own solution :

    Public Sub ExportToExcel(sender As Object, e As Ext.Net.DirectEventArgs)
            Dim grd As Ext.Net.GridPanel = Ext.Net.X.GetCmp(e.ExtraParams("Grid"))
            Dim ColumnModel As GridHeaderContainer = grd.ColumnModel
            Dim LST_Field As List(Of String) = New List(Of String)
            Dim LST_FieldName As List(Of String) = New List(Of String)
            Dim LST_Lines As List(Of strucData) = JsonConvert.DeserializeObject(Of List(Of strucData))(e.ExtraParams("data"))
            Dim Line As String = ""
            Dim FileName As String = ""
    
            For i As Integer = 5 To ColumnModel.Columns.Count - 1
                LST_Field.Add(ColumnModel.Columns(i).DataIndex)
                LST_FieldName.Add(ColumnModel.Columns(i).Text)
            Next
    
    
            Select Case grd.ID
                Case "grdOSA"
                    FileName = "OSA Data"
                Case "grdMASK"
                    FileName = "Mask Data"
                Case "grdVEN"
                    FileName = "Ventilation Data"
                Case "grdTPP"
                    FileName = "Third Party Product Data"
                Case "grdATS"
                    FileName = "Activity Time Split Data"
                Case "grdTRS"
                    FileName = "Time Repair Split Data"
                Case "grdPS"
                    FileName = "Product Split Data"
                Case "grdFCC"
                    FileName = "Call Center Data"
                Case "grdLO_OM"
                    FileName = "Logistic Order Metrics Data"
                Case "grdLO_AVA"
                    FileName = "Logistic availability Data"
                Case "grdFI"
                    FileName = "Financial Data"
            End Select
    
            'Collumns heading
            Dim row As New TableRow
            Dim cell(LST_Field.Count - 1) As TableCell
            For i As Integer = 0 To LST_Field.Count - 1
                cell(i) = New TableCell
                cell(i).Text = LST_FieldName(i)
                row.Cells.Add(cell(i))
            Next
            CType(Page.FindControl("tblExport"), Table).Rows.Add(row)
    
            For i As Integer = 0 To LST_Lines.Count - 1
                row = New TableRow
                Line = JsonConvert.SerializeObject(LST_Lines(i))
                For j As Integer = 0 To LST_Field.Count - 1
                    cell(j) = New TableCell
                    cell(j).Text = GlobalFunction.DecodeJSON(Line, LST_Field(j), LST_Field(j).Substring(0, 3))
                    row.Cells.Add(cell(j))
                Next
                CType(Page.FindControl("tblExport"), Table).Rows.Add(row)
            Next
    
            'renseignement de l'objet response / Response object filling
            Response.Clear()
            Response.Buffer = True
            Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName + " Export.xls")
            Response.ContentEncoding = System.Text.Encoding.Default
            Response.ContentType = "application/vnd.ms-excel"
            Response.Write(GenHtmlTable())
            Response.End()
        End Sub
    e.ExtraParams("Grid")
    is the grid name

    (e.ExtraParams("data")
    are the grid data

    The call :
                                                                        <ext:Button ID="btnExportOSA" runat="server" Text="To Excel" Icon="PageExcel">
                                                                            <DirectEvents >
                                                                                <Click OnEvent="ExportToEXcel" IsUpload="true">
                                                                                    <ExtraParams>
                                                                                        <ext:Parameter Name="data" Value="#{grdOSA}.getRowsValues({selectedOnly : false})" Mode="Raw" Encode="true"></ext:Parameter>
                                                                                        <ext:Parameter Name="Grid" Value="grdOSA" Mode="Value" Encode="False"></ext:Parameter>
                                                                                     </ExtraParams>
                                                                                </Click>
                                                                            </DirectEvents>
                                                                        </ext:Button>
    I didn't use the way you choose because it exports all the data from the grid even some I didn't want user to see, so I made my own routine.

    I imagine by using the same method, you could choose to export several store.
  6. #6
    You'r welcome.

    As you can see by reading the code, I generate an HTML table to export the sheet. Not sure you could make more than one sheet in that way. I do know play wirh excel to make several sheets, even charts, but by using ole automation, and it is very difficult to play with OLE automation on server side (it is prohibited by Microsoft itself for security reasons). It is possible but very difficult to put in place (I abandaon that way long time ago as I have only simple grid to export). Meanwhile, if you really need something complicated you could try to play this way, or you export on one sheet you grid one after the other...But it is up to you to know what you want.

Similar Threads

  1. Export to Excel
    By JonC in forum 1.x Help
    Replies: 9
    Last Post: Nov 13, 2012, 4:59 AM
  2. Export to excel: Grid Panel with multiple pages.
    By breakyoheart in forum 2.x Help
    Replies: 0
    Last Post: Aug 02, 2012, 8:09 PM
  3. vb example of export to Excel
    By grosenbrock in forum 1.x Help
    Replies: 2
    Last Post: Aug 27, 2010, 6:22 PM
  4. [CLOSED] Excel Export
    By speedstepmem3 in forum 1.x Legacy Premium Help
    Replies: 2
    Last Post: Sep 25, 2009, 3:50 AM
  5. [CLOSED] Excel export
    By speedstepmem3 in forum 1.x Legacy Premium Help
    Replies: 1
    Last Post: Sep 24, 2009, 5:35 AM

Tags for this Thread

Posting Permissions