PDA

View Full Version : Export only visible columns - Gridpanel + Store



moth1
Jul 25, 2010, 11:10 PM
Hi All,
This Thread demonstrates how to export only the data from the Visible columns of a Gridpanel.

This uses Javascript(EXT) code to get access to the Store for the data and the Gridpanel to get columns showing.
I thought I'd post this so other users don't waste like 5+Hrs on it like I did :)

JAVASCRIPT


var exportData = function (grid) {
var store = grid.store;
store.ajaxEventConfig.isUpload = true;
var values = getValues(store, grid);
store.submitData(values);

store.ajaxEventConfig.isUpload = false;
}
function getValues(store, grid) {
var noRows = store.data.items.length;
var noColumns = grid.colModel.columns.length;
var xml = '';
for (rowIndex = 0; rowIndex < noRows; rowIndex++) {
xml = xml + '<record>';
for (colIndex = 0; colIndex < noColumns; colIndex++) {
var name = grid.getColumnModel().getDataIndex(colIndex);
xml = xml + '<' + name + '>';
var record = grid.store.getAt(rowIndex);
xml = xml + record.get(name);
xml = xml + '</' + name + '>';
}
xml = xml + '</record>';
}
return xml;
}


HTML used


<ext:Menurunat="server"ID="cntxMenu">
<Items>
<ext:MenuItemID="MenuItem1"runat="server"Text="Export to Excel"Icon="PageExcel">
<Listeners>
<ClickHandler="exportData(gridPanel1);"CausesValidation="false"/>
</Listeners>
</ext:MenuItem>
</Items>
</ext:Menu>
<formid="form1"runat="server">
<ext:ScriptManagerID="ScriptManager1"runat="server"/>
<ext:Storerunat="server"ID="Store1"OnSubmitData="Store1_SubmitData"OnRefreshData="Store1_RefreshData">
<Reader>
<ext:JsonReader>
<Fields>
<ext:RecordFieldName="Id"Type="String"/>
<ext:RecordFieldName="ShortDescription"Type="String"/>
<ext:RecordFieldName="Description"Type="String"/>
</Fields>
</ext:JsonReader>
</Reader>
<Proxy>
<ext:DataSourceProxy/>
</Proxy>
<AjaxEventConfigIsUpload="false"/>
</ext:Store>
<ext:ViewPortID="ViewPort1"runat="server">
<Body>
<ext:FitLayoutID="FitLayout1"runat="server">
<ext:GridPanelrunat="server"ID="gridPanel1"StoreID="Store1"Title="Items"BodyBorder="false"
Border="false"TrackMouseOver="true"AutoHeight="true"Icon="Table"AutoScroll="true">
<ColumnModelID="ColumnModel1"runat="server">
<Columns>
<ext:ColumnDataIndex="ShortDescription"Header="Short Description"Width="300">
</ext:Column>
<ext:ColumnDataIndex="Description"Header="Description"Width="300">
</ext:Column>
</Columns>
</ColumnModel>
<SelectionModel>
<ext:RowSelectionModelID="RowSelectionModel1"runat="server"SingleSelect="true">
</ext:RowSelectionModel>
</SelectionModel>
<LoadMaskShowMask="true"/>
<Listeners>
<RowContextMenuHandler="e.preventDefault(); cntxMenu.dataRecord = this.store.getAt(rowIndex); cntxMenu.showAt(e.getXY());"/>
</Listeners>
</ext:GridPanel>
</ext:FitLayout>
</Body>
</ext:ViewPort>
</form>


CSharp code used


publicpartialclassExportTest : System.Web.UI.Page
{
protectedvoid Page_Load(object sender, EventArgs e)
{if (!Ext.IsAjaxRequest)
{
this.Store1.DataSource = GetData();
this.Store1.DataBind();
}
}
publicvoid Store1_RefreshData(object sender, StoreRefreshDataEventArgs e)
{
this.Store1.DataSource = GetData();
this.Store1.DataBind();
}

publicvoid Store1_SubmitData(object sender, StoreSubmitDataEventArgs e)
{
XmlNode xml = e.Xml;
var xmlString = xml.InnerXml;
xml.InnerXml = Server.HtmlDecode(xml.InnerXml.Trim());
 
 
var xDoc = XDocument.Parse(xml.InnerXml);
//Skip first single <record>
var test = xDoc.Element("records").Element("record").Elements();
xmlString = "<records>";
xmlString = xmlString + string.Join(string.Empty, test.Select(i => i.ToString()).ToArray());
xmlString = xmlString + "</records>";
xmlString = xmlString.Replace("\r\n ", string.Empty);
xmlString = xmlString.Replace("\r\n ", string.Empty);
xmlString = xmlString.Replace("\r\n", string.Empty);
xmlString = xmlString.Replace(" ", string.Empty);
xml.InnerXml = xmlString;
this.Response.Clear();
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.AddHeader("Content-Disposition", "attachment; filename=submittedData.xls");
XslCompiledTransform xtExcel = newXslCompiledTransform();
xtExcel.Load(Server.MapPath("Excel.xsl"));
xtExcel.Transform(xml, null, this.Response.OutputStream);
this.Response.End();
}
publicList<Test> GetData()
{
return (from i inEnumerable.Range(1, 100)
selectnew
Test
{
Id = i,
Description = string.Concat(i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i),
ShortDescription = string.Concat(i, i, i, i, i, i, i, i, i, i, i)
}).ToList();
}
}
publicclassTest
{
public Test()
{
}
publicint Id { get; set; }
publicstring Description { get; set; }
publicstring ShortDescription { get; set; }
}

geoffrey.mcgill
Aug 04, 2010, 6:01 PM
Hi moth1,

Thanks for taking the time to share this example.

:)

voldamirin
Nov 10, 2010, 11:14 AM
this is good work. Thnks:o

anup
Jan 11, 2011, 5:25 PM
Thanks for that. You can now also use getRowsValues and getRecordsValues:


Ext.encode(gridPanel.store.getRecordsValues({ visibleOnly:true, excludeId:true, grid:gridPanel }))Or


Ext.encode(gridPanel.getRowsValues({ visibleOnly:true, excludeId:true }))The new property in the options called excludeId was added just today as I had a need to ensure the hidden id column was not exported. See this for more info:
http://forums.ext.net/showthread.php?11897-getRowsValues-and-getRecordsValues-always-return-id-column-even-when-visibleOnly-true

As an aside, I also had the need to export a grid that was generated via an ashx (so the normal post back, ajax or traditional, could not be used as the store/grid could not be regenerated on the server side). Dunno if that also helps:
http://forums.ext.net/showthread.php?11896-Export-to-excel-csv-etc-by-posting-to-another-page