Results 1 to 3 of 3

Thread: Exporting Excel with Multiple worksheets

  1. #1
    Sencha Premium User
    Join Date
    May 2014
    Posts
    5

    Default Exporting Excel with Multiple worksheets

    Today, I planned to export the store data with the ability to create Multiple worksheets within a single excel download.

    Surprisingly, I could find no references to accomplish this. The closest that I could find is:
    ExcelExport by sdruckerfig, which gives us the ability to export the data from the grid
    and
    the flash plugin Ext.ux.Exporter.ExcelFormatter.

    What if I dont want to create a grid to export my data?
    What if I want to download the data from my store directly?
    What if I dont want to use any flash plugin like Ext.ux.Exporter.ExcelFormatter?

    All that we need is the correctly processed XML content being passed on as 'to-be-exported' data to the browser.

    Say we have a store which has the Name, State, Country. and we need to create two worksheets displaying all the users - for Canada and for India.

    The structure of Json from the server is :
    {
    "total": 2,
    "canada": [
    {
    "name": "Bob",
    "state": "Ontario",
    "occupation": "Doctor"
    },
    {
    "name": "Sobers",
    "state": "PEI",
    "occupation": "Plumber"
    }
    ],
    "india": [
    {
    "name": "Natasha",
    "state": "Maharashtra",
    "occupation": "Mechanic"
    },
    {
    "name": "Reese",
    "state": "Delhi state",
    "occupation": "Doctor"
    }
    ],
    "success": true
    }

    To display the above code we need to be clear that we need to just generate the right XML and export it as follows:

    <Worksheet ss:Name="Data">
    <Table>
    <Column ss:AutoFitWidth="1" ss:Width="400" />
    <Column ss:AutoFitWidth="1" ss:Width="400" />
    <Row>
    <Cell>
    <Data ss:Type="String">
    Bob
    </Data>
    </Cell>
    <Cell>
    <Data ss:Type="String">
    Ontario
    </Data>
    </Cell>
    <Cell>
    <Data ss:Type="String">
    Doctor
    </Data>
    </Cell>
    </Row>
    <Row>
    <Cell>
    <Data ss:Type="String">
    Sobers
    </Data>
    </Cell>
    <Cell>
    <Data ss:Type="String">
    PEI
    </Data>
    </Cell>
    <Cell>
    <Data ss:Type="String">
    Plumber
    </Data>
    </Cell>
    </Row>
    </Table>
    </Worksheet>


    I have just created one worksheet, we need to automate the XML building code and create two worksheets - one for Canada and the other for India using the following function:

    Code:
    function fetchXML(stores){
        var wrkbookXML = '';
        for(i=0; i<stores.length; i++){
            var worksheetName = 'Data';
            i == 0 ? worksheetName = 'Canada' : worksheetName = 'India';
            wrkbookXML += '<Worksheet ss:Name="' + worksheetName + '"><Table>';
            var rowXML = '<Column ss:AutoFitWidth="1" ss:Width="450" /><Column ss:AutoFitWidth="1" ss:Width="450" />';
            
            for(j=0; j<stores[i].length; j++){
                rowXML += '<Row>';
                rowXML += '<Cell><Data ss:Type="String">' + stores[i][j].name + "</Data></Cell>";
                rowXML += '<Cell><Data ss:Type="String">' + stores[i][j].state + "</Data></Cell>";
                rowXML += '<Cell><Data ss:Type="String">' + stores[i][j].occupation + "</Data></Cell>";
                rowXML += '</Row>';     
            }
            wrkbookXML += rowXML;
            wrkbookXML += '</Table></Worksheet>';    
        }
        return wrkbookXML;
    }
    In the "Download" button handler() function, we could invoke the above function as follows:
    Code:
                                        var store = Ext.data.StoreManager.lookup('Sencha.Countries');
                                        var json = store.getData().items[0].data;
        
                                        var canadaData = json.canada;
                                        var indiaData = json.india;
                                        var xmls = fetchXML([canadaData, indiaData]);
        
                                        var ctx = {
                                            created : (new Date()).getTime(),
                                            worksheets : xmls
                                        };
        
                                        var uri = 'data:application/vnd.ms-excel;base64,',
                                            tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">' + '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>' + '<Styles>' + '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>' + '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>' + '</Styles>' + '{worksheets}</Workbook>',
                                            tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>',
                                            tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>',
                                            base64 = function(s) {
                                            return window.btoa(unescape(encodeURIComponent(s)));
                                        },
                                            format = function(s, c) {
                                            return s.replace(/{(\w+)}/g, function(m, p) {
                                                return c[p];
                                            });
                                        };
                                        var workbookXML = format(tmplWorkbookXML, ctx);
                                        var link = document.createElement("A");
                                        link.href = uri + base64(workbookXML);
                                        link.download = "Data" + "-" + Ext.Date.format(new Date(), 'Y-m-d H:i:s') + '.xls';
                                        //'Workbook.xls';
                                        link.target = '_blank';
                                        document.body.appendChild(link);
                                        link.click();
                                        document.body.removeChild(link);

    This way we can create any many worksheets as possible. as of now, I have created two of them, extending the above function will give us more options to create multiple worksheets in a single excel download

  2. #2
    Sencha User
    Join Date
    Feb 2013
    Location
    California
    Posts
    11,985
    Answers
    506

    Default

    Thanks for sharing your solution with the community. In Ext JS 6, another option is the new exporter:
    http://docs.sencha.com/extjs/6.0/6.0...exporter.Excel

  3. #3
    Sencha Premium Member
    Join Date
    Jun 2014
    Posts
    16

    Default

    Hi,

    I tried to use above approach to download the export file but i get the following error when i try to open the file

    "The file format and extension don't match. The file could be corrupted and unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?"


    Can you please help me understand why i get this error?

    Thank you

Similar Threads

  1. Code: Exporting Grid to Excel, PDF, etc.
    By jmhwhite2001 in forum Ext GWT: Discussion
    Replies: 3
    Last Post: 15 Mar 2012, 12:36 AM
  2. Exporting to Excel
    By lukefowell89 in forum Ext 3.x: Help & Discussion
    Replies: 2
    Last Post: 20 Jan 2011, 10:56 AM
  3. Recommendations for exporting to Excel
    By jdobrowski in forum Ext 2.x: Help & Discussion
    Replies: 3
    Last Post: 27 Sep 2010, 11:11 PM
  4. Exporting GXT Grid to Excel, PDF
    By myworld100us in forum Ext GWT: Discussion
    Replies: 3
    Last Post: 1 Sep 2009, 3:32 AM
  5. Need Help for Exporting to Excel
    By friendlymahi in forum Ext 2.x: Help & Discussion
    Replies: 6
    Last Post: 18 Oct 2008, 4:46 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •