PDA

View Full Version : Export to excel/csv etc by posting to another page



anup
Jan 10, 2011, 3:04 PM
Hi,

There are some great examples of exporting grid to excel/csv/xml via postback and ajax, e.g. here:
http://examples1.ext.net/#/GridPanel/Miscellaneous/Export_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>
2) The gridgenerator.ashx to generate the grid (using some dummy data), plus the export buttons:



using System.Web;

namespace Ext.Net.Tests.Grid.Export.ViaHandler
{
public class GridGenerator : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
new DirectResponse(BuildGridPanel().ToScript(RenderMod e.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"}
};
}
}
}
}3) Export.ashx



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; }
}
}
}
(Note, the Excel.xsl and Csv.xsl files are the same ones as per the Ext.Net example above)

geoffrey.mcgill
Jan 10, 2011, 4:12 PM
Hi Anup,

Thanks for sharing!

Dinni Hayyati
Feb 23, 2011, 6:54 AM
Hi, there.
I'd like to know if there is anyway i can export data from grid or textfield to Excel files which has been created before and place them on specified cells i want.
Thanks

anup
Feb 24, 2011, 4:05 PM
Dinni,

I don't think that is typically possible - the server is just sending back some text. The server happens to indicate that the text is a special type (i.e. via the MIME Type) and the client browser happens to know the text the server has returned can be treated as a file/attachment that can open with Excel.

Maybe there is a way to format the contents of the Excel file to do more fancy Excel-like stuff, but to open an existing spreadsheet and insert into a specific place I don't think that would be possible. It would probably be a security risk for a browser to be able to open an existing file on the user's system and put anything in there I would think. I could be wrong...

Maybe the Excel text can include a macro that could open another excel sheet an copy the values to that new sheet/file? But I don't know enough about Excel at that level.