Jan 10, 2011, 3:04 PM
Export to excel/csv etc by posting to another page
Hi,
There are some great examples of exporting grid to excel/csv/xml via postback and ajax, e.g. here:
https://examples1.ext.net/#/GridPane...ort_Data_Ajax/
However, in my case, I am often generating a GridPanel (and associated Data Store) in an ashx handler (and rendering to a panel or tab panel etc). E.g. I have a tab panel, when a non-visible tab is activated, I generate a GridPanel on demand and render it to that tab.
This means using things like submitValues() as in the above Ext.Net example does not work for me (it relies a post back -- ajax or normal, and in my case on the post back it won't find the Store because it was generated dynamically in the ashx, not the Page).
So I've been looking at alternative ideas and come up with this approach:
It seems to work quite nicely, but would appreciate any advice on improving it. Of course the code is just sample, so checking variables, arguments etc should all be added in. If I have just re-invented some ExtJs or Ext.Net wheel, please let me know the simpler ways of achieving this.
Here's the various bits of code:
1) The aspx to start things off (note the main thing here is the JavaScript doExport method and the call to the ashx to build the grid panel inside the second tab, when activated)
There are some great examples of exporting grid to excel/csv/xml via postback and ajax, e.g. here:
https://examples1.ext.net/#/GridPane...ort_Data_Ajax/
However, in my case, I am often generating a GridPanel (and associated Data Store) in an ashx handler (and rendering to a panel or tab panel etc). E.g. I have a tab panel, when a non-visible tab is activated, I generate a GridPanel on demand and render it to that tab.
This means using things like submitValues() as in the above Ext.Net example does not work for me (it relies a post back -- ajax or normal, and in my case on the post back it won't find the Store because it was generated dynamically in the ashx, not the Page).
So I've been looking at alternative ideas and come up with this approach:
- Handle an export button click or some other such event on the client side
- JavaScript dynamically renders a temporary form element (so that existing form element(s) are not affected)
- This form element has its target set to _blank (so it will open in new tab - most browsers seem to close the tab when it is a downloaded file, so this is the key thing. IE6 seems to leave new browser window open but I can live with that)
- This form will also contain the store data to post, the format to convert to, and a page (or ashx in my example) to post to.
- The ashx will then convert the data into the desired format.
It seems to work quite nicely, but would appreciate any advice on improving it. Of course the code is just sample, so checking variables, arguments etc should all be added in. If I have just re-invented some ExtJs or Ext.Net wheel, please let me know the simpler ways of achieving this.
Here's the various bits of code:
1) The aspx to start things off (note the main thing here is the JavaScript doExport method and the call to the ashx to build the grid panel inside the second tab, when activated)
<%@ Page Language="C#" %>
<%@ Register Assembly="Ext.Net" Namespace="Ext.Net" TagPrefix="ext" %>
<!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 id="Head1" runat="server">
<title>Ext.NET Example</title>
<script type="text/javascript">
function doExport(e, exportFormat, records) {
var form = document.createElement('form');
form.method = 'post';
form.action = 'export.ashx';
form.target='_blank';
var format = document.createElement('input');
format.type = 'hidden';
format.name = 'exportFormat';
format.value = exportFormat;
form.appendChild(format);
var hidden = document.createElement('input');
hidden.type = 'hidden';
hidden.name = 'storeRecords';
hidden.value = records;
form.appendChild(hidden);
document.body.appendChild(form);
form.submit();
form.removeChild(format);
form.removeChild(hidden);
document.body.removeChild(form);
format = null;
hidden = null;
form = null;
}
// formatting functions for the grid panel once it is rendered
var template = '<span style="color:{0};">{1}</span>';
function submitValue(formatType) {
this.ownerCt.ownerCt.submitData(false);
}
var change = function (value) {
return String.format(template, (value > 0) ? "green" : "red", value);
};
var pctChange = function (value) {
return String.format(template, (value > 0) ? "green" : "red", value + "%");
};
var buildTabPanel = function () {
var tabs = new Ext.TabPanel({
height: 500,
activeItem : 0,
renderTo : Ext.getBody(),
items : [
{
title : "Tab1",
html : "<p>Click on second tab to generate grid on demand.</p>"
},
{
id : "tab2",
title : "Tab2",
layout : "fit",
listeners : {
activate : {
fn : function () {
Ext.net.DirectEvent.request({
url : "GridGenerator.ashx",
cleanRequest : true,
extraParams : { container : "tab2" },
eventMask : { showMask:true }
});
},
single : true
}
}
}
]
});
};
</script>
</head>
<body>
<ext:ResourceManager ID="ResourceManager1" runat="server">
<Listeners>
<DocumentReady Handler="buildTabPanel();" />
</Listeners>
</ext:ResourceManager>
</body>
</html>using System.Web;
namespace Ext.Net.Tests.Grid.Export.ViaHandler
{
public class GridGenerator : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
new DirectResponse(BuildGridPanel().ToScript(RenderMode.AddTo, context.Request["container"])).Return();
}
public bool IsReusable
{
get { return false; }
}
private GridPanel BuildGridPanel()
{
var gridPanel = new GridPanel
{
Border = false,
StripeRows = true,
TrackMouseOver = true,
AutoExpandColumn = "Company",
Store = { BuildStore() },
SelectionModel = { new RowSelectionModel { SingleSelect = true } },
ColumnModel =
{
Columns =
{
new Column { ColumnID = "Company", Header = "Company", DataIndex = "company" },
new Column { Header = "Price", DataIndex = "price", Renderer = {Format = RendererFormat.UsMoney} },
new Column { Header = "Change", DataIndex = "change", Renderer = {Fn = "change"} },
new Column { Header = "Change", DataIndex = "pctChange", Renderer = {Fn = "pctChange"} },
new DateColumn { Header = "Last Updated", DataIndex = "lastChange" }
}
},
TopBar =
{
new Toolbar
{
Items =
{
new Button
{
Text = "To Excel",
Icon = Icon.PageExcel,
Listeners =
{
Click =
{
Handler = "doExport('excel', Ext.encode(#{Store1}.getRecordsValues()));"
}
}
},
new Button
{
Text = "To Csv",
Icon = Icon.PageAttach,
Listeners =
{
Click =
{
Handler = "doExport('csv', Ext.encode(#{Store1}.getRecordsValues()));"
}
}
}
}
}
}
};
return gridPanel;
}
private Store BuildStore()
{
var store = new Store
{
ID = "Store1", // <-- ID is Required
Reader =
{
new ArrayReader
{
Fields =
{
new RecordField("company"),
new RecordField("price", RecordFieldType.Float),
new RecordField("change", RecordFieldType.Float),
new RecordField("pctChange", RecordFieldType.Float),
new RecordField("lastChange", RecordFieldType.Date, "M/d hh:mmtt")
}
}
},
DataSource = Data
};
store.DataBind();
return store;
}
private static object[] Data
{
get
{
return new object[]
{
new object[] {"3m Co", 71.72, 0.02, 0.03, "9/1 12:00am"},
new object[] {"Alcoa Inc", 29.01, 0.42, 1.47, "9/1 12:00am"},
new object[] {"Altria Group Inc", 83.81, 0.28, 0.34, "9/1 12:00am"},
new object[] {"American Express Company", 52.55, 0.01, 0.02, "9/1 12:00am"},
new object[] {"American International Group, Inc.", 64.13, 0.31, 0.49, "9/1 12:00am"},
new object[] {"AT&T Inc.", 31.61, -0.48, -1.54, "9/1 12:00am"},
new object[] {"Boeing Co.", 75.43, 0.53, 0.71, "9/1 12:00am"},
new object[] {"Caterpillar Inc.", 67.27, 0.92, 1.39, "9/1 12:00am"},
new object[] {"Citigroup, Inc.", 49.37, 0.02, 0.04, "9/1 12:00am"},
new object[] {"E.I. du Pont de Nemours and Company", 40.48, 0.51, 1.28, "9/1 12:00am"},
new object[] {"Exxon Mobil Corp", 68.1, -0.43, -0.64, "9/1 12:00am"},
new object[] {"General Electric Company", 34.14, -0.08, -0.23, "9/1 12:00am"},
new object[] {"General Motors Corporation", 30.27, 1.09, 3.74, "9/1 12:00am"},
new object[] {"Hewlett-Packard Co.", 36.53, -0.03, -0.08, "9/1 12:00am"},
new object[] {"Honeywell Intl Inc", 38.77, 0.05, 0.13, "9/1 12:00am"},
new object[] {"Intel Corporation", 19.88, 0.31, 1.58, "9/1 12:00am"},
new object[] {"International Business Machines", 81.41, 0.44, 0.54, "9/1 12:00am"},
new object[] {"Johnson & Johnson", 64.72, 0.06, 0.09, "9/1 12:00am"},
new object[] {"JP Morgan & Chase & Co", 45.73, 0.07, 0.15, "9/1 12:00am"},
new object[] {"McDonald\"s Corporation", 36.76, 0.86, 2.40, "9/1 12:00am"},
new object[] {"Merck & Co., Inc.", 40.96, 0.41, 1.01, "9/1 12:00am"},
new object[] {"Microsoft Corporation", 25.84, 0.14, 0.54, "9/1 12:00am"},
new object[] {"Pfizer Inc", 27.96, 0.4, 1.45, "9/1 12:00am"},
new object[] {"The Coca-Cola Company", 45.07, 0.26, 0.58, "9/1 12:00am"},
new object[] {"The Home Depot, Inc.", 34.64, 0.35, 1.02, "9/1 12:00am"},
new object[] {"The Procter & Gamble Company", 61.91, 0.01, 0.02, "9/1 12:00am"},
new object[] {"United Technologies Corporation", 63.26, 0.55, 0.88, "9/1 12:00am"},
new object[] {"Verizon Communications", 35.57, 0.39, 1.11, "9/1 12:00am"},
new object[] {"Wal-Mart Stores, Inc.", 45.45, 0.73, 1.63, "9/1 12:00am"}
};
}
}
}
}using System.Web;
using System.Xml;
using System.Xml.Xsl;
namespace Ext.Net.Tests.Grid.Export.ViaHandler
{
public class Export : IHttpHandler
{
private HttpContext _context;
private XmlNode _exportNode;
public void ProcessRequest(HttpContext context)
{
_context = context;
DoExport();
}
private void DoExport()
{
string format = _context.Request["exportFormat"];
if (string.IsNullOrEmpty(format) || format.Trim().Length == 0)
{
DoEndWithBadResponseStatus();
return;
}
string export = _context.Request["storeRecords"];
string exportWithRoot = string.Format("{{ records: {{ record: {0} }} }}", export);
_exportNode = JSON.DeserializeXmlNode(exportWithRoot);
switch (format)
{
case "excel":
ExportToExcel();
break;
case "csv":
ExportToCsv();
break;
default:
DoEndWithBadResponseStatus();
return;
}
}
private void DoEndWithBadResponseStatus()
{
_context.Response.StatusCode = 400;
_context.Response.StatusDescription = "Bad Request";
_context.Response.End();
}
private void ExportToExcel()
{
DoTransform("application/vnd.ms-excel", "submittedData.xls", "Excel.xsl");
}
private void ExportToCsv()
{
DoTransform("text/csv", "submittedData.csv", "Csv.xsl");
}
private void DoTransform(string contentType, string fileName, string xsltPath)
{
_context.Response.Clear();
_context.Response.ContentType = contentType;
_context.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
var xtExcel = new XslCompiledTransform();
xtExcel.Load(_context.Server.MapPath(xsltPath));
xtExcel.Transform(_exportNode, null, _context.Response.OutputStream);
_context.Response.End();
}
public bool IsReusable
{
get { return false; }
}
}
}
