Generate an Excel File from a Tree Panel / Tree Grid!

Recently I was tasked with building an application whereby the user could export report data contained within a  treegrid to Microsoft Excel. Under normal circumstances, I would have used a server-side approach using ColdFusion’s robust functionality. However, in this particular case, we were using .NET and frankly, I wanted the middleware developer on the project to stay focused on building the core .NET CRUD webservices that were required for the project.

treegrid

Here’s the first-pass at a solution, which I implemented as an override to the tree panel control. Calling it’s rather quite simple. Just invoke the tree panel’s downloadExcelXml() method.

 {
   xtype: 'button',
   flex: 1,
   text: 'Download to Excel',
   handler: function(b, e) {
     b.up('treepanel').downloadExcelXml();
   }
 }

And here’s the conversion of the tree store data to an Excel spreadshet…

Ext.define('MyApp.view.override.TreePanel', {
	override: 'Ext.tree.Panel',
	requires: 'Ext.form.action.StandardSubmit',

	/*
        Kick off process
    */

	downloadExcelXml: function(includeHidden, title) {

		if (!title) title = this.title;

		var vExportContent = this.getExcelXml(includeHidden, title);

		var location = 'data:application/vnd.ms-excel;base64,' + Base64.encode(vExportContent);

		/* 
          dynamically create and anchor tag to force download with suggested filename 
          note: download attribute is Google Chrome specific
        */

		if (Ext.isChrome || Ext.isGecko || Ext.isSafari) { // local download
			var gridEl = this.getEl();

			var el = Ext.DomHelper.append(gridEl, {
				tag: "a",
				download: title + "-" + Ext.Date.format(new Date(), 'Y-m-d Hi') + '.xls',
				href: location
			});

			el.click();

			Ext.fly(el).destroy();

		} else { // remote download

			var form = this.down('form#uploadForm');
			if (form) {
				form.destroy();
			}
			form = this.add({
				xtype: 'form',
				itemId: 'uploadForm',
				hidden: true,
				standardSubmit: true,
				url: 'http://webapps.figleaf.com/dataservices/Excel.cfc?method=echo&mimetype=application/vnd.ms-excel&filename=' + escape(title + ".xls"),
				items: [{
					xtype: 'hiddenfield',
					name: 'data',
					value: vExportContent
				}]
			});

			form.getForm().submit();
		}
	},

	/*

        Welcome to XML Hell
        See: http://msdn.microsoft.com/en-us/library/office/aa140066(v=office.10).aspx
        for more details

    */
	getExcelXml: function(includeHidden, title) {

		var theTitle = title || this.title;

		var worksheet = this.createWorksheet(includeHidden, theTitle);
		var totalWidth = this.columns.length;

		return ''.concat(
			'<?xml version="1.0"?>',
			'<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">',
			'<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Title>' + theTitle + '</Title></DocumentProperties>',
			'<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"><AllowPNG/></OfficeDocumentSettings>',
			'<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">',
			'<WindowHeight>' + worksheet.height + '</WindowHeight>',
			'<WindowWidth>' + worksheet.width + '</WindowWidth>',
			'<ProtectStructure>False</ProtectStructure>',
			'<ProtectWindows>False</ProtectWindows>',
			'</ExcelWorkbook>',

			'<Styles>',

			'<Style ss:ID="Default" ss:Name="Normal">',
			'<Alignment ss:Vertical="Bottom"/>',
			'<Borders/>',
			'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>',
			'<Interior/>',
			'<NumberFormat/>',
			'<Protection/>',
			'</Style>',

			'<Style ss:ID="title">',
			'<Borders />',
			'<Font ss:Bold="1" ss:Size="18" />',
			'<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1" />',
			'<NumberFormat ss:Format="@" />',
			'</Style>',

			'<Style ss:ID="headercell">',
			'<Font ss:Bold="1" ss:Size="10" />',
			'<Alignment ss:Horizontal="Center" ss:WrapText="1" />',
			'<Interior ss:Color="#A3C9F1" ss:Pattern="Solid" />',
			'</Style>',


			'<Style ss:ID="even">',
			'<Interior ss:Color="#CCFFFF" ss:Pattern="Solid" />',
			'</Style>',


			'<Style ss:ID="evendate" ss:Parent="even">',
			'<NumberFormat ss:Format="yyyy-mm-dd" />',
			'</Style>',

			'<Style ss:ID="evenint" ss:Parent="even">',
			'<Numberformat ss:Format="0" />',
			'</Style>',

			'<Style ss:ID="evenfloat" ss:Parent="even">',
			'<Numberformat ss:Format="0.00" />',
			'</Style>',

			'<Style ss:ID="odd">',
			'<Interior ss:Color="#CCCCFF" ss:Pattern="Solid" />',
			'</Style>',

			'<Style ss:ID="groupSeparator">',
			'<Interior ss:Color="#D3D3D3" ss:Pattern="Solid" />',
			'</Style>',

			'<Style ss:ID="odddate" ss:Parent="odd">',
			'<NumberFormat ss:Format="yyyy-mm-dd" />',
			'</Style>',

			'<Style ss:ID="oddint" ss:Parent="odd">',
			'<NumberFormat Format="0" />',
			'</Style>',

			'<Style ss:ID="oddfloat" ss:Parent="odd">',
			'<NumberFormat Format="0.00" />',
			'</Style>',

			'<Style ss:ID="indent1even" ss:Parent="odd">',
			'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:Indent="1"/>',
			'</Style>',

			'<Style ss:ID="indent2even" ss:Parent="odd">',
			'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:Indent="2"/>',
			'</Style>',

			'<Style ss:ID="indent3even" ss:Parent="odd">',
			'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:Indent="3"/>',
			'</Style>',

			'<Style ss:ID="indent4even" ss:Parent="odd">',
			'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:Indent="4"/>',
			'</Style>',

			'<Style ss:ID="indent1odd" ss:Parent="odd">',
			'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:Indent="1"/>',
			'</Style>',

			'<Style ss:ID="indent2odd" ss:Parent="odd">',
			'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:Indent="2"/>',
			'</Style>',

			'<Style ss:ID="indent3odd" ss:Parent="odd">',
			'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:Indent="3"/>',
			'</Style>',

			'<Style ss:ID="indent4odd" ss:Parent="odd">',
			'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:Indent="4"/>',
			'</Style>',


			'</Styles>',
			worksheet.xml,
			'</Workbook>'
		);
	},


	getData: function() {

		var pnl = this;
		var cols = [];
		var maxDepth = 0;

		// get columns

		for (var i = 0; i < pnl.columns.length; i++) {
			cols.push({
				dataIndex: pnl.columns[i].dataIndex,
				title: pnl.columns[i].text,
				xtype: pnl.columns[i].xtype
			});
		}

		var aResult = [];
		var rootNode = pnl.getRootNode();

		rootNode.cascadeBy(function(node) {
			var rec = {};
			for (var j = 0; j < cols.length; j++) {
				rec[cols[j].dataIndex] = node.get(cols[j].dataIndex);
			}
			rec.depth = node.getDepth();
			if (rec.depth > maxDepth) {
				maxDepth = rec.depth;
			}
			aResult.push(rec);
		}, this);


		return {
			cols: cols,
			maxDepth: maxDepth,
			data: aResult
		}


	},



	createWorksheet: function(includeHidden, theTitle) {
		// Calculate cell data types and extra class names which affect formatting

		var data = this.getData();

		var cellType = [];
		var cellTypeClass = [];

		var totalWidthInPixels = 0;
		var colXml = '';
		var headerXml = '';
		var visibleColumnCountReduction = 0;

		for (var i = 0; i < data.cols.length; i++) {
			colXml += '<Column ss:AutoFitWidth="1"/>';
			headerXml += '<Cell ss:StyleID="headercell">' + '<Data ss:Type="String">' + data.cols[i].title + '</Data>' + '<NamedCell ss:Name="Print_Titles"></NamedCell></Cell>';
			switch (data.cols[i].xtype) {
				case "numbercolumn":
					cellType.push("Number");
					cellTypeClass.push("int");
					break;
				case "booleancolumn":
					cellType.push("String");
					cellTypeClass.push("");
					break;
				case "datecolumn":
					cellType.push("DateTime");
					cellTypeClass.push("date");
					break;
				default:
					cellType.push("String");
					cellTypeClass.push("");
					break;
			}
		}


		var visibleColumnCount = data.cols.length;

		var result = {
			height: 9000,
			// width: Math.floor(totalWidthInPixels * 30) + 50
			width: 1000
		};

		// Generate worksheet header details.

		// determine number of rows
		var numGridRows = data.data.length + 1;



		// create header for worksheet
		var t = ''.concat(
			'<Worksheet ss:Name="' + theTitle + '">',

			'<Names>',
			'<NamedRange ss:Name="Print_Titles" ss:RefersTo="=\'' + theTitle + '\'!R1:R2">',
			'</NamedRange></Names>',

			'<Table ss:ExpandedColumnCount="' + (visibleColumnCount + 2),
			'" ss:ExpandedRowCount="' + numGridRows + '" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="65" ss:DefaultRowHeight="15">',
			colXml,
			'<Row ss:Height="38">',
			'<Cell ss:MergeAcross="' + (visibleColumnCount - 1) + '" ss:StyleID="title">',
			'<Data ss:Type="String" xmlns:html="http://www.w3.org/TR/REC-html40">',
			'<html:b>' + theTitle + '</html:b></Data><NamedCell ss:Name="Print_Titles">',
			'</NamedCell></Cell>',
			'</Row>',
			'<Row ss:AutoFitHeight="1">',
			headerXml,
			'</Row>'
		);

		// Generate the data rows from the data in the Store
		var groupVal = "",
			cellClass = null,
			v = null;
		
		for (var i = 1; i < data.data.length; i++) {

			cellClass = (i & 1) ? 'odd' : 'even';
			t += '<Row>';
			

			for (var j = 0; j < data.cols.length; j++) {
				v = data.data[i][data.cols[j].dataIndex];

				if (j == 0 && data.data[i].depth > 1) {
					// first col might be indented
				    t += '<Cell ss:StyleID="indent' + data.data[i].depth + cellClass + '"><Data ss:Type="' + cellType[j] + '">';
				} else {
					t += '<Cell ss:StyleID="' + cellClass + cellTypeClass[j] + '"><Data ss:Type="' + cellType[j] + '">';
				}

				
				if (cellType[j] == 'DateTime') {
					t += Ext.Date.format(v, 'Y-m-d');
				} else {
					t += v;
				}
				t += '</Data></Cell>';


			}
			t += '</Row>';
		}

		result.xml = t.concat(
			'</Table>',
			'<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">',
			'<PageLayoutZoom>0</PageLayoutZoom>',
			'<Selected/>',
			'<Panes>',
			'<Pane>',
			'<Number>3</Number>',
			'<ActiveRow>2</ActiveRow>',
			'</Pane>',
			'</Panes>',
			'<ProtectObjects>False</ProtectObjects>',
			'<ProtectScenarios>False</ProtectScenarios>',
			'</WorksheetOptions>',
			'</Worksheet>'
		);

		return result;
	}
});

The server-side code used to echo the generated spreadsheet back to the browser and force a “download” operation is the following:

<cfcomponent>

 <cffunction name="echo" access="remote" returntype="void">
  
  <cfargument name="mimetype" type="string" required="no" default="text/html">
  <cfargument name="filename" type="string" required="yes">
  <cfargument name="data" type="string" required="no" default="">

  <cfif isdefined("form.data")>
   <cfset arguments.data = form.data>
  </cfif>

  <cfheader name="Content-Disposition" value="attachment; filename=#arguments.filename#">
  
  <cfcontent type="#arguments.mimetype#"><cfoutput>#arguments.data#</cfoutput>

 </cffunction>

</cfcomponent>

Note that the ColdFusion-based “echo” webservice is available for evaluation/testing purposes only. No warranty or level of service is expressed or implied.

You can play around with the code on Sencha Fiddle:
https://fiddle.sencha.com/#fiddle/4pr

Go check it out!

6 thoughts on “Generate an Excel File from a Tree Panel / Tree Grid!

  1. Moe

    Very Nice !!

    I have just one problem! I need to use the renderer return value instead of the dataIndex.

    Can u have any ideas ??

    Thanks

    Reply
    1. sdrucker Post author

      Honestly, I’m not a huge fan of using renderers. I prefer instead to create calculated fields in the model and display those in the grid. Using calculated fields helps avoid potential grid sorting problems.

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s