(updated on March 19, 2014 to support grouped grids)
Recently I was tasked with building an application whereby the user could export report data contained within a grid 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.
Much to my surprise, after spending a good amount of time googling for a solution, there didn’t seem to be a really dead-simple implementation of a Grid to Excel method for Ext JS 4.
There were several Ext 3 examples available, however. So I ported and enhanced one that I found out there for your enjoyment.
Here’s the result, which I implemented as an override to the grid control. Calling it’s rather quite simple. Just invoke the grid’s downloadExcelXml() method.
xtype: 'button',
flex: 1,
text: 'Download to Excel',
handler: function(b, e) {
b.up('grid').downloadExcelXml();
Converting over the Ext 3 code and enhancing it to supply a suggested filename using a new Google Chrome trick, wasn’t quite so simple:
Ext.define('MyApp.view.override.Grid', {
override: 'Ext.grid.GridPanel',
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) {
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 {
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.columnManager.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>',
'</Styles>',
worksheet.xml,
'</Workbook>'
Support function to return field info from store based on fieldname
getModelField: function(fieldName) {
var fields = this.store.model.getFields();
for (var i = 0; i < fields.length; i++) {
if (fields[i].name === fieldName) {
return fields[i];
Convert store into Excel Worksheet
generateEmptyGroupRow: function(dataIndex, value, cellTypes, includeHidden) {
var cm = this.columnManager.columns;
var colCount = cm.length;
var rowTpl = '<Row ss:AutoFitHeight="0"><Cell ss:StyleID="groupSeparator" ss:MergeAcross="{0}"><Data ss:Type="String"><html:b>{1}</html:b></Data></Cell></Row>';
var visibleCols = 0;
// rowXml += '<Cell ss:StyleID="groupSeparator">'
for (var j = 0; j < colCount; j++) {
if (cm[j].xtype != 'actioncolumn' && (cm[j].dataIndex != '') && (includeHidden || !cm[j].hidden)) {
// rowXml += '<Cell ss:StyleID="groupSeparator"/>';
visibleCols++;
// rowXml += "</Row>";
return Ext.String.format(rowTpl, visibleCols - 1, value);
createWorksheet: function(includeHidden, theTitle) {
// Calculate cell data types and extra class names which affect formatting
var cellType = [];
var cellTypeClass = [];
var cm = this.columnManager.columns;
var totalWidthInPixels = 0;
var colXml = '';
var headerXml = '';
var visibleColumnCountReduction = 0;
var colCount = cm.length;
for (var i = 0; i < colCount; i++) {
if (cm[i].xtype != 'actioncolumn' && (cm[i].dataIndex != '') && (includeHidden || !cm[i].hidden)) {
var w = cm[i].getEl().getWidth();
totalWidthInPixels += w;
if (cm[i].text === "") {
cellType.push("None");
cellTypeClass.push("");
++visibleColumnCountReduction;
} else {
colXml += '<Column ss:AutoFitWidth="1" ss:Width="' + w + '" />';
headerXml += '<Cell ss:StyleID="headercell">' +
'<Data ss:Type="String">' + cm[i].text + '</Data>' +
'<NamedCell ss:Name="Print_Titles"></NamedCell></Cell>';
var fld = this.getModelField(cm[i].dataIndex);
switch (fld.type.type) {
case "int":
cellType.push("Number");
cellTypeClass.push("int");
break;
case "float":
cellType.push("Number");
cellTypeClass.push("float");
break;
case "bool":
case "boolean":
cellType.push("String");
cellTypeClass.push("");
break;
case "date":
cellType.push("DateTime");
cellTypeClass.push("date");
break;
default:
cellType.push("String");
cellTypeClass.push("");
break;
var visibleColumnCount = cellType.length - visibleColumnCountReduction;
var result = {
height: 9000,
width: Math.floor(totalWidthInPixels * 30) + 50
// Generate worksheet header details.
// determine number of rows
var numGridRows = this.store.getCount() + 2;
if (!Ext.isEmpty(this.store.groupField) || this.store.groupers.items.length > 0) {
numGridRows = numGridRows + this.store.getGroups().length;
// 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 = "";
var groupField = "";
if (this.store.groupers.keys.length > 0) {
groupField = this.store.groupers.keys[0];
for (var i = 0, it = this.store.data.items, l = it.length; i < l; i++) {
if (!Ext.isEmpty(groupField)) {
if (groupVal != this.store.getAt(i).get(groupField)) {
groupVal = this.store.getAt(i).get(groupField);
t += this.generateEmptyGroupRow(groupField, groupVal, cellType, includeHidden);
t += '<Row>';
var cellClass = (i & 1) ? 'odd' : 'even';
r = it[i].data;
var k = 0;
for (var j = 0; j < colCount; j++) {
if (cm[j].xtype != 'actioncolumn' && (cm[j].dataIndex != '') && (includeHidden || !cm[j].hidden)) {
var v = r[cm[j].dataIndex];
if (cellType[k] !== "None") {
t += '<Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><Data ss:Type="' + cellType[k] + '">';
if (cellType[k] == '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/17j
Go check it out!
Hi, I ‘ve implemented this UX but I have a big problem it just doesn’t work in IE at all not even IE10.
The message error is: “the data area passed to a system call is too small”
And the lines that seems to cause the error are :
el.click();
Ext.fly(el).destroy();
Any ideas ?
↓
That would be really awesome!
But I’m not sure it would be possible due to this restriction:
http://stackoverflow.com/questions/19713912/extjs4-generating-an-excel-file-from-javascript-in-ie-gets-methe-data-area-pas
↓
Sdrucker,
I have a doubt about the ux…
I have a ‘datecloumn ‘ in my grid with a ‘format’ config to render dates in a specific way, but that is breaking the generation of the Excel file …
Are ‘datecolum’ supported ? is ‘format’ config supported ?
Thank you in advance.
↓
Steve,
It works great, one suggestion would be to change this.columns to this.getView().getGridColumns() so excel would match the user’s current view (moved/hidden columns or sorting) instead of the default view.
Thanks for the help!
↓
Thanks for sharing the code. Do you know what is the alternative to ‘download’ attribute so it would work in IE?
var el = Ext.DomHelper.append(gridEl, {
I am assuming it is due to ‘download’ attribute, or some one else pointed out it was a limitation on how much data that we can put in ‘href in IE.
↓
I’ve abondoned trying to generate an Excel from an ExtJs grid but I think there are some work arounds pretty helpful (IMO):
1.- Copy the content of your STORE (not grid) to clipboard and paste it to Excel.
http://www.rahulsingla.com/blog/2010/03/extjs-copy-gridpanel-content-to-clipboard
2.- Print your Grid to an html page (for printting porpouses) and copy & paste to Excel
http://loianegroner.com/2011/09/extjs-4-grid-printer-plugin/
3.- Simply make your server generate a CSV
best regards
↓
Yes, I’ve implemented it in Architect.
All you need to do is add an application loader config to point to an Ext.ux folder, e.g.:
Ext.Loader.setConfig({
And then reference the override from your application’s REQUIRES property.
↓
Thanks for the share Steve. It works.
One thing I noticed is that the grouping name always takes what is defined in the STORE (groupfield). I have a 40-column grid with 5 fields are group-enabled. Whatever field I choose to be the GROUPING field, the xsl file always uses the one defined in the STORE.
↓
Hi Steve,
I’ve created some changes in the createWorksheet().
Here is the complete createWorksheet() code:
// Calculate cell data types and extra class names which affect formatting
var totalWidthInPixels = 0;
// Arnold – for Group Label correction (1)
for (var i = 0; i < colCount; i++) {
//Arnold – for Group Label correction (2)
if (cm[i].xtype != 'actioncolumn' && (cm[i].dataIndex !== '') && (includeHidden || !cm[i].hidden)) {
if (cm[i].text === "") {
var fld = this.getModelField(cm[i].dataIndex);
case “bool”:
case “boolean”:
var result = {
// Generate worksheet header details.
// determine number of rows
//Arnold – for Group Label correction (3)
// create header for worksheet
// Generate the data rows from the data in the Store
for (var i = 0, it = this.store.data.items, l = it.length; i < l; i++) {
//Arnold – for Group Label correction (4)
//if (groupVal != this.store.getAt(i).get(this.store.groupField)) {
//t += this.generateEmptyGroupRow(this.store.groupField, groupVal, cellType, includeHidden);
result.xml = t.concat(
↓
if (!Ext.isEmpty(this.store.groupers.first().property)) {
with:
if (!Ext.isEmpty(this.store.groupers.first())) {
The first one is undefined (.property)
Thanks.
Hi. sometimes, I couldn’t connect with internet. in that time, impossible download excel file in IE10.
How can I download excel file, when I couldn’t connect internet.
↓
can we import the renderer function value in excel ??
e.g.:-
↓
Hi Sdrucker, Can you tell me how to export to excel with multi grouping columns format, please help me
http://ateodorescu.github.io/mzExt/examples/multilevelgrouping/index.html
https://github.com/ateodorescu/mzExt
↓
If you want to download excel with column order in view(screen) need change like below.
↓
I finally was able to implement your code and is working great!
Only thing I need is GroupSummary part. Would you be able to guide me where to modify so GroupSummary is included in this plugin?
Btw, thanks for ExtJS 5 meetup!
↓
.xslx is essentially a .zip file that contains xml documents. It’s not something that you can really create very easily using client-side JavaScript. If I had that as a client requirement, I’d implement a server-side solution using functionality built into ColdFusion (cfspreadsheet/cfzip) or POI (
http://poi.apache.org/
)
↓
Double click the generated .xls file from MSFT Excel 2007, gives a initial message “The file you are trying to open .. is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source….” Click Yes, the file opens file. Is there a way to get around with this? Thanks! It’s a beautiful implementation, saved me a lot of time.
↓
Double click the generated xls file, get a warning message. “The file you are trying to open, ‘name.ext’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?” Click Yes, opens fine. Is there a way to get around with it? Thanks!
↓
The ColdFusion code has been provided. It looks like WordPress had escaped some of the code, so I’ve fixed that.
↓
315,131 hits
October 2013
