What's New?

[CLOSED] Export grid to CSV/XLS/XML

  1. #1
    Jul 01, 2015, 6:47 PM

    [CLOSED] Export grid to CSV/XLS/XML

    Do you have XSL for CSV/XLS for TreePanel? They work fine with GridPanel but not on TreePanel.

    Thanks,
    /Z
    Last edited by Daniil; Jul 17, 2015 at 7:09 AM. Reason: [CLOSED]
  2. #2
    Jul 02, 2015, 2:00 PM
    Hi,

    The XSL will depend on how you convert hierarchical tree data to XML and your definition of a "record". It would be great if you could post a XML sample to work on.


    Hope it helps.
  3. #3
    Jul 02, 2015, 3:57 PM
    Why should it matter? A treepanel is just a collection of nodes. Shouldn't the XML be generic? Then the XSL is to format it for proper output?

    If not, I can get you example.
    /Z
  4. #4
    Jul 03, 2015, 11:08 AM
    Let's say that you serialize this tree to XML using a function like the one (tree2xml) demonstrated in http://forums.ext.net/showthread.php...port-TreePanel.

    Of course, an xsl can be used but exactly what is "proper output" depends on your idea of a "record". For example, in case of CSV, what data do you want delimited in each file row? Songs only? Or composer, music category and song titles, maybe?
  5. #5
    Jul 03, 2015, 12:57 PM
    That XML is perfect. I want every column in every record exported.
    /Z
  6. #6
    Jul 03, 2015, 9:44 PM
    The following sample demonstrates how to flatten tree data into a XML, CSV and XLS file.

    Web Form

    <%@ 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 ToXml(object sender, DirectEventArgs e)
    {
        string strXml = Server.HtmlDecode(Server.HtmlDecode(e.ExtraParams["nodes"]));

        this.Response.Clear();
        this.Response.AddHeader("Content-Disposition", "attachment; filename=submittedTree.xml");
        this.Response.AddHeader("Content-Length", strXml.Length.ToString());
        this.Response.ContentType = "application/xml";
        this.Response.Write(strXml);
        this.Response.End();        
    }

    protected void ToExcel(object sender, DirectEventArgs e)
    {
        string strXml = Server.HtmlDecode(Server.HtmlDecode(e.ExtraParams["nodes"]));

        XmlDocument doc = new XmlDocument();
        doc.LoadXml(strXml);

        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(doc.DocumentElement, null, this.Response.OutputStream);
        this.Response.End();        
    }

    protected void ToCsv(object sender, DirectEventArgs e)
    {
        string strXml = Server.HtmlDecode(e.ExtraParams["nodes"]);

        XmlDocument doc = new XmlDocument();
        doc.LoadXml(strXml);
        
        this.Response.Clear();
        this.Response.ContentType = "application/octet-stream";
        this.Response.AddHeader("Content-Disposition", "attachment; filename=submittedData.csv");
        XslCompiledTransform xtCsv = new XslCompiledTransform();
        xtCsv.Load(Server.MapPath("Csv.xsl"));
        xtCsv.Transform(doc.DocumentElement, null, this.Response.OutputStream);
        this.Response.End();
    }
</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript">
        function tree2xml(node) {
            var result;

            if (node.getDepth() === 0) {
                result = '<?xml version="1.0"?>';
            }
            else {
                result = '';
            }

            result = '<node';

            for (var key in node.attributes) {
                if (key !== 'children') {
                    result += ' ' + key + '="' + node.attributes[key] + '"';
                }
            }

            var children = node.childNodes;
            var clen = children.length;
            if (clen == 0) {
                result += '/>';
            } else {
                result += '>';
                for (var i = 0; i < clen; i++) {
                    result += this.tree2xml(children[i]);
                }
                result += '</node>';
            }
            return result;
        }
    </script>
</head>
<body>
    <form runat="server">
        <ext:ResourceManager runat="server" />       
        
        <ext:TreePanel 
            ID="TreePanel1" 
            runat="server" 
            Width="500" 
            Height="450" 
            Icon="BookOpen" 
            Title="Catalog" 
            RootVisible="false"
            AutoScroll="true">
            <TopBar>
                <ext:Toolbar runat="server">
                    <Items>
                        <ext:Button ID="Button1" runat="server" Text="Expand All">
                            <Listeners>
                                <Click Handler="#{TreePanel1}.expandAll();" />
                            </Listeners>
                        </ext:Button>
                        <ext:Button ID="Button2" runat="server" Text="Collapse All">
                            <Listeners>
                                <Click Handler="#{TreePanel1}.collapseAll();" />
                            </Listeners>
                        </ext:Button>
                        <ext:ToolbarFill ID="ToolbarFill1" runat="server" />
                        
                        <ext:Button runat="server" Text="To XML" Icon="PageCode">
                            <DirectEvents>
                                <Click OnEvent="ToXml" IsUpload="true">
                                    <ExtraParams>
                                        <ext:Parameter Name="nodes" Value="Ext.util.Format.htmlEncode(tree2xml(#{TreePanel1}.getRootNode()))" Mode="Raw" />
                                    </ExtraParams>
                                </Click>
                            </DirectEvents>
                        </ext:Button>
                        
                        <ext:Button runat="server" Text="To Excel" Icon="PageExcel">
                            <DirectEvents>
                                <Click OnEvent="ToExcel" IsUpload="true">
                                    <ExtraParams>
                                        <ext:Parameter Name="nodes" Value="Ext.util.Format.htmlEncode(tree2xml(#{TreePanel1}.getRootNode()))" Mode="Raw" />
                                    </ExtraParams>
                                </Click>
                            </DirectEvents>
                        </ext:Button>
                        
                        <ext:Button runat="server" Text="To CSV" Icon="PageAttach">
                            <DirectEvents>
                                <Click OnEvent="ToCsv" IsUpload="true">
                                    <ExtraParams>
                                        <ext:Parameter Name="nodes" Value="Ext.util.Format.htmlEncode(tree2xml(#{TreePanel1}.getRootNode()))" Mode="Raw" />
                                    </ExtraParams>
                                </Click>
                            </DirectEvents>
                        </ext:Button>
                    </Items>
                </ext:Toolbar>
            </TopBar>
            <Root>
                <ext:TreeNode Text="Composers" Expanded="true">
                    <Nodes>
                        <ext:TreeNode Text="Beethoven" Icon="UserGray">
                            <Nodes>
                                <ext:TreeNode Text="Concertos">
                                    <Nodes>
                                        <ext:TreeNode Text="No. 1 - C" Icon="Music" />
                                        <ext:TreeNode Text="No. 2 - B-Flat Major" Icon="Music" />
                                        <ext:TreeNode Text="No. 3 - C Minor" Icon="Music" />
                                        <ext:TreeNode Text="No. 4 - G Major" Icon="Music" />
                                        <ext:TreeNode Text="No. 5 - E-Flat Major" Icon="Music" />
                                    </Nodes>
                                </ext:TreeNode>
                                <ext:TreeNode Text="Quartets">
                                    <Nodes>
                                        <ext:TreeNode Text="Six String Quartets" Icon="Music" />
                                        <ext:TreeNode Text="Three String Quartets" Icon="Music" />
                                        <ext:TreeNode Text="Grosse Fugue for String Quartets" Icon="Music" />
                                    </Nodes>
                                </ext:TreeNode>
                                <ext:TreeNode Text="Sonatas">
                                    <Nodes>
                                        <ext:TreeNode Text="Sonata in A Minor" Icon="Music" />
                                        <ext:TreeNode Text="sonata in F Major" Icon="Music" />
                                    </Nodes>
                                </ext:TreeNode>
                                <ext:TreeNode Text="Symphonies">
                                    <Nodes>
                                        <ext:TreeNode Text="No. 1 - C Major" Icon="Music" />
                                        <ext:TreeNode Text="No. 2 - D Major" Icon="Music" />
                                        <ext:TreeNode Text="No. 3 - E-Flat Major" Icon="Music" />
                                        <ext:TreeNode Text="No. 4 - B-Flat Major" Icon="Music" />
                                        <ext:TreeNode Text="No. 5 - C Minor" Icon="Music" />
                                        <ext:TreeNode Text="No. 6 - F Major" Icon="Music" />
                                        <ext:TreeNode Text="No. 7 - A Major" Icon="Music" />
                                        <ext:TreeNode Text="No. 8 - F Major" Icon="Music" />
                                        <ext:TreeNode Text="No. 9 - D Minor" Icon="Music" />
                                    </Nodes>
                                </ext:TreeNode>
                            </Nodes>
                        </ext:TreeNode>
                        <ext:TreeNode Text="Brahms" Icon="UserGray">
                            <Nodes>
                                <ext:TreeNode Text="Concertos">
                                    <Nodes>
                                        <ext:TreeNode Text="Violin Concerto" Icon="Music" />
                                        <ext:TreeNode Text="Double Concerto - A Minor" Icon="Music" />
                                        <ext:TreeNode Text="Piano Concerto No. 1 - D Minor" Icon="Music" />
                                        <ext:TreeNode Text="Piano Concerto No. 2 - B-Flat Major" Icon="Music" />
                                    </Nodes>
                                </ext:TreeNode>
                                <ext:TreeNode Text="Quartets">
                                    <Nodes>
                                        <ext:TreeNode Text="Piano Quartet No. 1 - G Minor" Icon="Music" />
                                        <ext:TreeNode Text="Piano Quartet No. 2 - A Major" Icon="Music" />
                                        <ext:TreeNode Text="Piano Quartet No. 3 - C Minor" Icon="Music" />
                                        <ext:TreeNode Text="Piano Quartet No. 3 - B-Flat Minor" Icon="Music" />
                                    </Nodes>
                                </ext:TreeNode>
                                <ext:TreeNode Text="Sonatas">
                                    <Nodes>
                                        <ext:TreeNode Text="Two Sonatas for Clarinet - F Minor" Icon="Music" />
                                        <ext:TreeNode Text="Two Sonatas for Clarinet - E-Flat Major" Icon="Music" />
                                    </Nodes>
                                </ext:TreeNode>
                                <ext:TreeNode Text="Symphonies">
                                    <Nodes>
                                        <ext:TreeNode Text="No. 1 - C Minor" Icon="Music" />
                                        <ext:TreeNode Text="No. 2 - D Minor" Icon="Music" />
                                        <ext:TreeNode Text="No. 3 - F Major" Icon="Music" />
                                        <ext:TreeNode Text="No. 4 - E Minor" Icon="Music" />
                                    </Nodes>
                                </ext:TreeNode>
                            </Nodes>
                        </ext:TreeNode>
                        <ext:TreeNode Text="Mozart" Icon="UserGray">
                            <Nodes>
                                <ext:TreeNode Text="Concertos">
                                    <Nodes>
                                        <ext:TreeNode Text="Piano Concerto No. 12" Icon="Music"  />
                                        <ext:TreeNode Text="Piano Concerto No. 17" Icon="Music"  />
                                        <ext:TreeNode Text="Clarinet Concerto" Icon="Music"  />
                                        <ext:TreeNode Text="Violin Concerto No. 5" Icon="Music"  />
                                        <ext:TreeNode Text="Violin Concerto No. 4" Icon="Music"  />
                                    </Nodes>
                                </ext:TreeNode>
                            </Nodes>
                        </ext:TreeNode>
                    </Nodes>
                </ext:TreeNode>
            </Root>
            <BottomBar>
                <ext:StatusBar ID="StatusBar1" runat="server" AutoClear="1500" />
            </BottomBar>
            <Listeners>
                <Click 
                    Handler="#{StatusBar1}.setStatus({text: 'Node Selected: <b>' + node.text + '<br />', clear: true});" 
                    />
                <ExpandNode 
                    Handler="#{StatusBar1}.setStatus({text: 'Node Expanded: <b>' + node.text + '<br />', clear: true});" 
                    Delay="30" 
                    />
                <CollapseNode 
                    Handler="#{StatusBar1}.setStatus({text: 'Node Collapsed: <b>' + node.text + '<br />', clear: true});" 
                    />
            </Listeners>
        </ext:TreePanel>
    </form>
</body>
</html>
    CSV XSLT
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:output method="text" />

	<xsl:template match="//node[not(*)]">    
    <xsl:for-each select="ancestor::node[position()!=last()]">
      <xsl:text>"</xsl:text>
        <xsl:value-of select="@text"/>
      <xsl:text>",</xsl:text>
    </xsl:for-each>
    <xsl:text>"</xsl:text>
      <xsl:value-of select="@text" />
    <xsl:text>"</xsl:text>
    <xsl:text>
</xsl:text>
	</xsl:template>

</xsl:stylesheet>
    XLS XSLT
    <xsl:stylesheet version="1.0"
    xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
 xmlns:msxsl="urn:schemas-microsoft-com:xslt"
 xmlns:user="urn:my-scripts"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> 
 
<xsl:template match="/node">
  <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:html="http://www.w3.org/TR/REC-html40">
    <Worksheet>
      <xsl:attribute name="ss:Name">
        <xsl:value-of select="@text" />
      </xsl:attribute>
      <Table x:FullColumns="1" x:FullRows="1">
        <xsl:apply-templates />
      </Table>
    </Worksheet>
  </Workbook>
</xsl:template>

<xsl:template match="//node[not(*)]">
  <Row>
    <xsl:for-each select="ancestor::node[position()!=last()]">
      <Cell>
        <Data ss:Type="String">
          <xsl:value-of select="@text"/>
        </Data>
      </Cell>
    </xsl:for-each>
    <Cell>
      <Data ss:Type="String">
        <xsl:value-of select="@text" />
      </Data>
    </Cell>  
  </Row>
</xsl:template>
  
</xsl:stylesheet>

    Hope it helps.
  7. #7
    Jul 09, 2015, 2:11 AM
    thxs. i did implement it but i have some issue that the XML is invalid. gonna be a few days before i can get back to this and i will post my results when i do
    /Z

Similar Threads

  1. Export the grid to excel
    By Mr.Techno in forum 1.x Help
    Replies: 34
    Last Post: Aug 23, 2013, 11:39 AM
  2. Export Grid to PDF
    By NickBin in forum Examples and Extras
    Replies: 4
    Last Post: Dec 28, 2012, 5:02 AM
  3. Grid Panel Export
    By muralimk in forum 1.x Help
    Replies: 1
    Last Post: Jan 28, 2011, 12:13 PM
  4. [CLOSED] Grid-Date Export Example: Export whole data of remote Store
    By macap in forum 1.x Legacy Premium Help
    Replies: 1
    Last Post: Apr 19, 2010, 2:44 PM
  5. [CLOSED] grid export all
    By alexp in forum 1.x Legacy Premium Help
    Replies: 1
    Last Post: Jan 07, 2010, 6:50 AM

Posting Permissions