PDA

View Full Version : [CLOSED] Problems Export to Excel



opendat2000
Jul 22, 2014, 10:40 PM
Hi-Team !!

I get the following problem when i try to export the records from the control gridpanel to MS excel, an error occurs when the header titles have a separation space.

Additionally, i want to incorporate a header for the generated report, but it does not work. In the following example i represent my problem.




<%@ Page Language="C#" %>

<%@ Import Namespace="System.Xml.Xsl" %>
<%@ Import Namespace="System.Xml" %>

<%@ Register Assembly="Ext.Net" Namespace="Ext.Net" TagPrefix="ext" %>

<script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.Store1.DataSource = new object[]
{
new object[] { 1, "3m Co", 71.72, 0.02, 0.03, "9/1 12:00am" },
new object[] { 2, "Alcoa Inc", 29.01, 0.42, 1.47, "9/1 12:00am" },
new object[] { 3, "Altria Group Inc", 83.81, 0.28, 0.34, "9/1 12:00am" },
new object[] { 4, "American Express Company", 52.55, 0.01, 0.02, "9/1 12:00am" },
new object[] { 5, "American International Group, Inc.", 64.13, 0.31, 0.49, "9/1 12:00am" },
new object[] { 6, "AT&T Inc.", 31.61, -0.48, -1.54, "9/1 12:00am" },
new object[] { 7, "Boeing Co.", 75.43, 0.53, 0.71, "9/1 12:00am" },
new object[] { 8, "Caterpillar Inc.", 67.27, 0.92, 1.39, "9/1 12:00am" },
new object[] { 9, "Citigroup, Inc.", 49.37, 0.02, 0.04, "9/1 12:00am" },
new object[] { 10, "E.I. du Pont de Nemours and Company", 40.48, 0.51, 1.28, "9/1 12:00am" },
new object[] { 11, "Exxon Mobil Corp", 68.1, -0.43, -0.64, "9/1 12:00am" },
new object[] { 12, "General Electric Company", 34.14, -0.08, -0.23, "9/1 12:00am" },
new object[] { 13, "General Motors Corporation", 30.27, 1.09, 3.74, "9/1 12:00am" },
new object[] { 14, "Hewlett-Packard Co.", 36.53, -0.03, -0.08, "9/1 12:00am" },
new object[] { 15, "Honeywell Intl Inc", 38.77, 0.05, 0.13, "9/1 12:00am" },
new object[] { 16, "Intel Corporation", 19.88, 0.31, 1.58, "9/1 12:00am" },
new object[] { 17, "International Business Machines", 81.41, 0.44, 0.54, "9/1 12:00am" },
new object[] { 18, "Johnson & Johnson", 64.72, 0.06, 0.09, "9/1 12:00am" },
new object[] { 19, "JP Morgan & Chase & Co", 45.73, 0.07, 0.15, "9/1 12:00am" },
new object[] { 20, "McDonald\"s Corporation", 36.76, 0.86, 2.40, "9/1 12:00am" },
new object[] { 21, "Merck & Co., Inc.", 40.96, 0.41, 1.01, "9/1 12:00am" },
new object[] { 22, "Microsoft Corporation", 25.84, 0.14, 0.54, "9/1 12:00am" },
new object[] { 23, "Pfizer Inc", 27.96, 0.4, 1.45, "9/1 12:00am" },
new object[] { 24, "The Coca-Cola Company", 45.07, 0.26, 0.58, "9/1 12:00am" },
new object[] { 25, "The Home Depot, Inc.", 34.64, 0.35, 1.02, "9/1 12:00am" },
new object[] { 26, "The Procter & Gamble Company", 61.91, 0.01, 0.02, "9/1 12:00am" },
new object[] { 27, "United Technologies Corporation", 63.26, 0.55, 0.88, "9/1 12:00am" },
new object[] { 28, "Verizon Communications", 35.57, 0.39, 1.11, "9/1 12:00am" },
new object[] { 29, "Wal-Mart Stores, Inc.", 45.45, 0.73, 1.63, "9/1 12:00am" }
};

this.Store1.DataBind();
}
}

protected void ToXml(object sender, EventArgs e)
{
string json = this.Hidden1.Value.ToString();
StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
XmlNode xml = eSubmit.Xml;

string strXml = xml.OuterXml;

this.Response.Clear();
this.Response.AddHeader("Content-Disposition", "attachment; filename=submittedData.xml");
this.Response.AddHeader("Content-Length", strXml.Length.ToString());
this.Response.ContentType = "application/xml";
this.Response.Write(strXml);
this.Response.End();
}

protected void ToExcel(object sender, EventArgs e)
{
try
{
string json = this.Hidden1.Value.ToString();
StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
XmlNode xml = eSubmit.Xml;

this.Response.Clear();
//this.Response.Buffer = true;
//this.Response.Write("<table cellpadding='0' cellspacing='0' border='1'>");
//this.Response.Write("<tr>");
//this.Response.Write("<td colspan='5' align ='center' valign='middle' bgcolor='#C0C0C0'>Export Data from GridPanel into XML, Excel or CSV using a full PostBack - Ext.NET Examples</td>");
//this.Response.Write("</tr>");
//this.Response.Write("</table>");

this.Response.ContentType = "application/vnd.ms-excel";
this.Response.AddHeader("Content-Disposition", "attachment; filename=submittedData.xls");

XslCompiledTransform xtExcel = new XslCompiledTransform();

xtExcel.Load(Server.MapPath("Excel.xsl"));
xtExcel.Transform(xml, null, this.Response.OutputStream);
this.Response.End();
}
catch (Exception ex)
{
Label1.Text = "Error : " + ex.Message;
}
}

protected void ToCsv(object sender, EventArgs e)
{
string json = this.Hidden1.Value.ToString();
StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
XmlNode xml = eSubmit.Xml;

this.Response.Clear();
this.Response.ContentType = "application/octet-stream";
this.Response.AddHeader("Content-Disposition", "attachment; filename=submittedData.csv");

XslCompiledTransform xtCsv = new XslCompiledTransform();

xtCsv.Load(Server.MapPath("Csv.xsl"));
xtCsv.Transform(xml, null, this.Response.OutputStream);
this.Response.End();
}
</script>

<!DOCTYPE html>

<html>
<head id="Head1" runat="server">
<title>Export Data from GridPanel into XML, Excel or CSV using a full PostBack - Ext.NET Examples</title>
<link href="/resources/css/examples.css" rel="stylesheet" />

<script>
var saveData = function ()
{
var data = App.GridPanel1.getRowsValues(),
columns = App.GridPanel1.getView().getGridColumns(),
headers = {};

Ext.each(columns, function (c)
{
headers[c.dataIndex] = c.text.replace(/ /g, "_");
headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "a");
headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "e");
headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "i");
headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "o");
headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "u");
headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "n");
});

Ext.each(data, function (record)
{
for (var field in record)
{
record[headers[field]] = record[field];
delete record[field];
}
});

App.Hidden1.setValue(Ext.encode(data));
};
</script>
</head>
<body>
<form id="Form1" runat="server">
<ext:ResourceManager ID="ResourceManager1" runat="server" />

<h1>Export Data from GridPanel into XML, Excel or CSV using a full PostBack</h1>

<ext:Hidden ID="Hidden1" runat="server" />

<ext:Store ID="Store1" runat="server">
<Model>
<ext:Model ID="Model1" runat="server">
<Fields>
<ext:ModelField Name="id" Type="Int" />
<ext:ModelField Name="company" />
<ext:ModelField Name="price" Type="Float" />
<ext:ModelField Name="change" Type="Float" />
<ext:ModelField Name="pctChange" Type="Float" />
<ext:ModelField Name="lastChange" Type="Date" DateFormat="M/d hh:mmtt" />
</Fields>
</ext:Model>
</Model>
</ext:Store>
<ext:Label ID="Label1" runat="server">
</ext:Label>
<ext:GridPanel
ID="GridPanel1"
runat="server"
StoreID="Store1"
Title="Export Data"
Width="600"
Height="350">
<ColumnModel ID="ColumnModel1" runat="server">
<Columns>
<ext:Column ID="Column1" runat="server" Text="Company" DataIndex="company" Flex="1" />
<ext:Column ID="Column2" runat="server" Text="Price" Width="75" DataIndex="price">
<Renderer Format="UsMoney" />
</ext:Column>
<ext:Column ID="Column3" runat="server" Text="Change" Width="75" DataIndex="change" />
<ext:Column ID="Column4" runat="server" Text="Pct.Change" Width="75" DataIndex="pctChange" />
<%--<ext:DateColumn ID="DateColumn1" runat="server" Text="Last_Updated" Width="85" DataIndex="lastChange" Format="dd-mm-yyyy" />--%>
<ext:DateColumn ID="DateColumn2" runat="server" Text="Last Updated" Width="85" DataIndex="lastChange" Format="dd-mm-yyyy" />
</Columns>
</ColumnModel>
<TopBar>
<ext:Toolbar ID="Toolbar1" runat="server">
<Items>
<ext:ToolbarFill ID="ToolbarFill1" runat="server" />

<ext:Button ID="Button1" runat="server" Text="To XML" AutoPostBack="true" OnClick="ToXml" Icon="PageCode">
<Listeners>
<Click Fn="saveData" />
</Listeners>
</ext:Button>

<ext:Button ID="Button2" runat="server" Text="To Excel" AutoPostBack="true" OnClick="ToExcel" Icon="PageExcel">
<Listeners>
<Click Fn="saveData" />
</Listeners>
</ext:Button>

<ext:Button ID="Button3" runat="server" Text="To CSV" AutoPostBack="true" OnClick="ToCsv" Icon="PageAttach">
<Listeners>
<Click Fn="saveData" />
</Listeners>
</ext:Button>
</Items>
</ext:Toolbar>
</TopBar>
</ext:GridPanel>
</form>
</body>
</html>



Watch for any help and / or suggestions

Daniil
Jul 23, 2014, 12:54 PM
Hi @opendat2000,

Unfortunately, the test case throws a JavaScript error for me on initial load.


an error occurs

What error? Please always provide more information about any errors. For example, an Exception's message and stack trace.

Also please read this thread. It contains some general statements about Ext.NET and exporting a data set to Excel.
http://forums.ext.net/showthread.php?17972 (http://forums.ext.net/showthread.php?17972-Export-the-grid-to-excel)

opendat2000
Jul 23, 2014, 2:07 PM
Hi Daniil

I modified the code so that you can see the error.

I attached a screeshot, as i said, this error occurs when the header titles from a gridpanel is composed of more than one word and a space separator.

For this case i use the Text="Last Updated", but if you correct the text to "Last_Updated", the error does not occurs.

13781

Daniil
Jul 24, 2014, 8:50 AM
Posting error messages in English is appreciated. At least, as raw text. I cannot even copy the message from the screenshot to put to an English translator. Also, I don't see the error's stack trace.

In any way, I guess this error is not related to Ext.NET.

opendat2000
Jul 24, 2014, 3:19 PM
Hi Daniil

The error message is "The ' ' character hexadecimal value 0x20, cannot be included in a name", i get this error when i run the following code line:


XmlNode xml = eSubmit.Xml;

Like i mention before, i modified the code so you can copy and run the aplication and get the indicated error.

According to your words: "In any way, I guess this error is not related to Ext.NET.", i differ on your comment, if the header text has a space, the export to excel does not works.

This works:

<%--<ext:DateColumn ID="DateColumn1" runat="server" Text="Last_Updated" Width="85" DataIndex="lastChange" Format="dd-mm-yyyy" />--%>

This does not works:

<ext:DateColumn ID="DateColumn2" runat="server" Text="Last Updated" Width="85" DataIndex="lastChange" Format="dd-mm-yyyy" />

Daniil
Jul 24, 2014, 3:53 PM
Ext.NET doesn't export the data to Excel. Using Ext.NET you just submit the data from a client to a server for exporting.

As for the Exception

The ' ' character, hexadecimal value 0x20, cannot be included in a name.

I tried to search it on the Internet, there is a lot of links. For example,
http://stackoverflow.com/questions/21154989/linq-to-xml-exception-the-character-hexadecimal-value-0x20-cannot-be-in

So, the Exception is a common one for XML. White spaces are not allowed in elements' names.

opendat2000
Jul 28, 2014, 9:28 PM
Hi Daniil

I modified the code to display the header gridpanel, replacing " " with "_", code attached.


Ext.each (columns, function (c)
{
headers[c.dataIndex] = c.text.replace(/ /g, "_");
headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "a");
headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "e");
headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "i");
headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "o");
headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "u");
headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "n");
});

How can I add a header to the Excel report?

Any idea or help will be appreciated.

Saludos
Mauricio.

Vladimir
Jul 29, 2014, 7:21 AM
Hi,

You need to change your XSLT file and include required changes, XSLT changes are out of Ext.Net scope
Please investigate the following threads
http://forums.ext.net/showthread.php?26140
http://forums.ext.net/showthread.php?26100-CLOSED-header-text-is-english-when-export-to-excel

opendat2000
Jul 29, 2014, 8:49 PM
Daniil and Vladimir

Thank you very much for your support and comments

Thread closed ....

Saludos
Mauricio.