PDA

View Full Version : [CLOSED] how to Import excel to Datagrid?



tobros
Aug 21, 2013, 3:58 AM
hi ,ext.net team.
how to Import excel to Datagrid?
I notice there is a plugin of DataDrop which can drag data from excel to grid, but it can be used in ext.net 1.x version.
if the plugin works, could you please give me an example in ext.net 2.x version?
if can not work, could you please give me a advice of how to do it?

Daniil
Aug 21, 2013, 11:42 AM
Hi @tobros,

I am a bit confused what you mean under import. Do you need drag&drop from Excel to a browser? Or do you have an Excel file, should read it and create a GridPanel accordingly?

tobros
Aug 22, 2013, 2:10 AM
Hi @tobros,

I am a bit confused what you mean under import. Do you need drag&drop from Excel to a browser? Or do you have an Excel file, should read it and create a GridPanel accordingly?

I have read the following thread:http://forums.ext.net/showthread.php?12102-Import-excel-to-Datagrid
I want to import excel like this if there is no other better method

I mean that there is a gridpanel on the page, when I drag the excel file to the web page, the data show in the gridpanel . then I click save button, then the data save to the database.

or can I upload the excel file , then read the excel file and place the data to the gridpanel?

which method is quick and easy to do?

Daniil
Aug 22, 2013, 12:15 PM
As for Ext.ux.DataDrop.js.

As far as I can see there is no version for ExtJS 4. It means there is no version for Ext.NET v2.



or can I upload the excel file , then read the excel file and place the data to the gridpanel?


Yes, you could do that. Unfortunately, we have no an example, but I am sure it is a common topic in the Web how to read an Excel file.

Daniil
Aug 22, 2013, 1:14 PM
As for Ext.ux.DataDrop.js.

As far as I can see there is no version for ExtJS 4. It means there is no version for Ext.NET v2.


We found the version for ExtJS 4. You can try it.
https://github.com/VinylFox/ExtJS.ux.DataDrop

tobros
Aug 23, 2013, 2:31 AM
We found the version for ExtJS 4. You can try it.
https://github.com/VinylFox/ExtJS.ux.DataDrop

ext.net support extjs plugin?

Daniil
Aug 23, 2013, 5:54 AM
Yes, it can be used by means of GenericPlugin. In a similar way as here:
http://forums.ext.net/showthread.php?9964&p=40382&viewfull=1#post40382

tobros
Aug 23, 2013, 8:30 AM
Yes, it can be used by means of GenericPlugin. In a similar way as here:
http://forums.ext.net/showthread.php?9964&p=40382&viewfull=1#post40382

I try this example, but when I add


<Plugins>
<ext:GenericPlugin ID="GenericPlugin1"
runat="server"
InstanceName="Ext.ux.grid.DataDrop"
Singleton="true"
Path="Override.js,Ext.ux.plugins.DataDrop.js" />
</Plugins>

the page show a blank page, and some js error:


Failed to load resource: the server responded with a status of 404 (Not Found) http://localhost:5431/test/Ext.ux.plugins.DataDrop.js
Failed to load resource: the server responded with a status of 404 (Not Found) http://localhost:5431/extjs/ext-all-js/src/ux/grid/DataDrop.js?_dc=1377242497532
Uncaught TypeError: object is not a function WebForm3.aspx:2


maybe can not find DataDrop.js
my whole test case is:


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm3.aspx.cs" Inherits="TobrosCWT.test.WebForm3" %>



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

<script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
if (!X.IsAjaxRequest)
{
var store = this.GridPanel1.GetStore();

store.DataSource = new object[]
{
new object[] { "3m Co", 71.72, 0.02, 0.03, "2010-09-01" },
new object[] { "Alcoa Inc", 29.01, 0.42, 1.47, "2010-09-01" },
new object[] { "Altria Group Inc", 83.81, 0.28, 0.34, "2010-09-01" },
new object[] { "American Express Company", 52.55, 0.01, 0.02, "2010-09-01" },
new object[] { "American International Group, Inc.", 64.13, 0.31, 0.49, "2010-09-01" },
new object[] { "AT&T Inc.", 31.61, -0.48, -1.54, "2010-09-01" },
new object[] { "Boeing Co.", 75.43, 0.53, 0.71, "2010-09-01" },
new object[] { "Caterpillar Inc.", 67.27, 0.92, 1.39, "2010-09-01" },
new object[] { "Citigroup, Inc.", 49.37, 0.02, 0.04, "2010-09-01" },
new object[] { "E.I. du Pont de Nemours and Company", 40.48, 0.51, 1.28, "2010-09-01" },
new object[] { "Exxon Mobil Corp", 68.1, -0.43, -0.64, "2010-09-01" },
new object[] { "General Electric Company", 34.14, -0.08, -0.23, "2010-09-01" },
new object[] { "General Motors Corporation", 30.27, 1.09, 3.74, "2010-09-01" },
new object[] { "Hewlett-Packard Co.", 36.53, -0.03, -0.08, "2010-09-01" },
new object[] { "Honeywell Intl Inc", 38.77, 0.05, 0.13, "2010-09-01" },
new object[] { "Intel Corporation", 19.88, 0.31, 1.58, "2010-09-01" },
new object[] { "International Business Machines", 81.41, 0.44, 0.54, "2010-09-01" },
new object[] { "Johnson & Johnson", 64.72, 0.06, 0.09, "2010-09-01" },
new object[] { "JP Morgan & Chase & Co", 45.73, 0.07, 0.15, "2010-09-01" },
new object[] { "McDonald\"s Corporation", 36.76, 0.86, 2.40, "2010-09-01" },
new object[] { "Merck & Co., Inc.", 40.96, 0.41, 1.01, "2010-09-01" },
new object[] { "Microsoft Corporation", 25.84, 0.14, 0.54, "2010-09-01" },
new object[] { "Pfizer Inc", 27.96, 0.4, 1.45, "2010-09-01" },
new object[] { "The Coca-Cola Company", 45.07, 0.26, 0.58, "2010-09-01" },
new object[] { "The Home Depot, Inc.", 34.64, 0.35, 1.02, "2010-09-01" },
new object[] { "The Procter & Gamble Company", 61.91, 0.01, 0.02, "2010-09-01" },
new object[] { "United Technologies Corporation", 63.26, 0.55, 0.88, "2010-09-01" },
new object[] { "Verizon Communications", 35.57, 0.39, 1.11, "2010-09-01" },
new object[] { "Wal-Mart Stores, Inc.", 45.45, 0.73, 1.63, "2010-09-01" }
};

store.DataBind();
}
}
</script>

<!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">
var template = '<span style="color:{0};">{1}';

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 + "%");
};
</script>
</head>
<body>
<form id="Form1" runat="server">
<ext:ResourceManager ID="ResourceManager1" runat="server" />

<ext:GridPanel
ID="GridPanel1"
runat="server"
StripeRows="true"
Title="Array Grid"
TrackMouseOver="true"
Width="600"
Height="350"
AutoExpandColumn="company">
<Store>
<ext:Store ID="Store1" runat="server">
<Model>
<ext:Model runat="server">
<Fields>
<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="yyyy-MM-dd" />
</Fields>
</ext:Model>
</Model>
</ext:Store>
</Store>
<ColumnModel ID="ColumnModel1" runat="server">
<Columns>
<ext:Column ColumnID="Company" Text="Company" DataIndex="company" />
<ext:Column Header="Price" DataIndex="price">
<Renderer Format="UsMoney" />
</ext:Column>
<ext:Column ColumnID="Change" Text="Change" DataIndex="change">
</ext:Column>
<ext:Column Text="Change" DataIndex="pctChange">
</ext:Column>
<ext:DateColumn Text="Last Updated" DataIndex="lastChange" />
</Columns>
</ColumnModel>
<SelectionModel>
<ext:RowSelectionModel ID="RowSelectionModel1" runat="server" SingleSelect="true" />
</SelectionModel>
<Plugins>
<ext:GenericPlugin ID="GenericPlugin1"
runat="server"
InstanceName="Ext.ux.grid.DataDrop"
Singleton="true"
Path="Override.js,Ext.ux.plugins.DataDrop.js" />
</Plugins>
</ext:GridPanel>
</form>
</body>
</html>



where to place DataDrop.js ? just place it into "http://localhost:5431/extjs/ext-all-js/src/ux/grid/DataDrop.js"
and rename and place it into "http://localhost:5431/test/Ext.ux.plugins.DataDrop.js"
??

Daniil
Aug 23, 2013, 1:14 PM
where to place DataDrop.js ? just place it into "http://localhost:5431/extjs/ext-all-js/src/ux/grid/DataDrop.js"
and rename and place it into "http://localhost:5431/test/Ext.ux.plugins.DataDrop.js"
??

According to a GenericPlugin's Path property. If

Path="Override.js,Ext.ux.plugins.DataDrop.js" />
then you should place files into the same folder where the page is.

It could be:

Path="resources/js/Override.js, resources/js/DataDrop.js"

In that case you should put the files into the resources/js folder.

However, the Path property with several resources doesn't work currently. You can register it in a common way - via script tags.

Also what is the Override.js? There is no such a file in the plugin for ExtJS 4.

Here is a working example.

Example

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

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

<script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
if (!X.IsAjaxRequest)
{
var store = this.GridPanel1.GetStore();

store.DataSource = new object[]
{
new object[] { "3m Co", 71.72, 0.02, 0.03, "2010-09-01" },
new object[] { "Alcoa Inc", 29.01, 0.42, 1.47, "2010-09-01" },
new object[] { "Altria Group Inc", 83.81, 0.28, 0.34, "2010-09-01" },
};
}
}
</script>

<!DOCTYPE html>

<html>
<head runat="server">
<title>Ext.NET v2 Example</title>

<script>
var template = '<span style="color:{0};">{1}';

var change = function (value) {
return Ext.String.format(template, (value > 0) ? "green" : "red", value);
};

var pctChange = function (value) {
return Ext.String.format(template, (value > 0) ? "green" : "red", value + "%");
};
</script>

<script src="resources/js/MouseEventForwarding.js"></script>
<script src="resources/js/DataDrop.js"></script>
</head>
<body>
<form runat="server">
<ext:ResourceManager runat="server" />

<ext:GridPanel
ID="GridPanel1"
runat="server"
Title="Array Grid"
Width="600"
Height="350">
<Store>
<ext:Store runat="server">
<Model>
<ext:Model runat="server">
<Fields>
<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="yyyy-MM-dd" />
</Fields>
</ext:Model>
</Model>
</ext:Store>
</Store>
<ColumnModel runat="server">
<Columns>
<ext:Column runat="server" Text="Company" DataIndex="company" Flex="1" />
<ext:Column runat="server" Text="Price" DataIndex="price">
<Renderer Format="UsMoney" />
</ext:Column>
<ext:Column runat="server" Text="Change" DataIndex="change">
<Renderer Fn="change" />
</ext:Column>
<ext:Column runat="server" Text="Change" DataIndex="pctChange">
<Renderer Fn="pctChange" />
</ext:Column>
<ext:DateColumn runat="server" Text="Last Updated" DataIndex="lastChange" />
</Columns>
</ColumnModel>
<Plugins>
<ext:GenericPlugin
runat="server"
InstanceName="Ext.ux.grid.DataDrop"
Singleton="true"
/>
</Plugins>
</ext:GridPanel>
</form>
</body>
</html>

Daniil
Aug 23, 2013, 4:03 PM
It could be:

Path="resources/js/Override.js, resources/js/DataDrop.js"

In that case you should put the files into the resources/js folder.

However, the Path property with several resources doesn't work currently. You can register it in a common way - via script tags.


Fixed in SVN. Now this works:

Path="resources/js/MouseEventForwarding.js,resources/js/DataDrop.js"

Here is a full example.
http://forums.ext.net/showthread.php?9964&p=116092&viewfull=1#post116092

tobros
Aug 26, 2013, 4:31 AM
Fixed in SVN. Now this works:

Path="resources/js/MouseEventForwarding.js,resources/js/DataDrop.js"

Here is a full example.
http://forums.ext.net/showthread.php?9964&p=116092&viewfull=1#post116092

I have chaged the path to script, now it can show gridpanel in the page, but I write some data the the excel , then open the excel, select the data , then drop it to the gripdpanel in the page, unfortunately can not drag data to the page , why? it needs ms excel? now I use wps excel, is it the reason why can not drag? or my drag way is wrong?

my whole code is


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm4.aspx.cs" Inherits="TobrosCWT.test.WebForm4" %>



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

<script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
if (!X.IsAjaxRequest)
{
var store = this.GridPanel1.GetStore();

store.DataSource = new object[]
{
new object[] { "3m Co", 71.72, 0.02, 0.03, "2010-09-01" },
new object[] { "Alcoa Inc", 29.01, 0.42, 1.47, "2010-09-01" },
new object[] { "Altria Group Inc", 83.81, 0.28, 0.34, "2010-09-01" },
};
}
}
</script>

<!DOCTYPE html>

<html>
<head id="Head1" runat="server">
<title>Ext.NET v2 Example</title>

<script>
var template = '<span style="color:{0};">{1}';

var change = function (value) {
return Ext.String.format(template, (value > 0) ? "green" : "red", value);
};

var pctChange = function (value) {
return Ext.String.format(template, (value > 0) ? "green" : "red", value + "%");
};
</script>
</head>
<body>
<form id="Form1" runat="server">
<ext:ResourceManager ID="ResourceManager1" runat="server" />

<ext:GridPanel
ID="GridPanel1"
runat="server"
Title="Array Grid"
Width="600"
Height="350">
<Store>
<ext:Store ID="Store1" runat="server">
<Model>
<ext:Model ID="Model1" runat="server">
<Fields>
<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="yyyy-MM-dd" />
</Fields>
</ext:Model>
</Model>
</ext:Store>
</Store>
<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" DataIndex="price">
<Renderer Format="UsMoney" />
</ext:Column>
<ext:Column ID="Column3" runat="server" Text="Change" DataIndex="change">
<Renderer Fn="change" />
</ext:Column>
<ext:Column ID="Column4" runat="server" Text="Change" DataIndex="pctChange">
<Renderer Fn="pctChange" />
</ext:Column>
<ext:DateColumn ID="DateColumn1" runat="server" Text="Last Updated" DataIndex="lastChange" />
</Columns>
</ColumnModel>
<Plugins>
<ext:GenericPlugin ID="GenericPlugin1"
runat="server"
InstanceName="Ext.ux.grid.DataDrop"
Singleton="true"
Path="~/Script/MouseEventForwarding.js,~/Script/DataDrop.js"
/>
</Plugins>
</ext:GridPanel>
</form>
</body>
</html>

6804

Daniil
Aug 26, 2013, 7:45 AM
why? it needs ms excel? now I use wps excel, is it the reason why can not drag?


I don't know it is going to work with "wps excel". I do not have it to check.

Could you demonstrate what kind of data you are dragging?

tobros
Aug 26, 2013, 9:30 AM
I don't know it is going to work with "wps excel". I do not have it to check.

Could you demonstrate what kind of data you are dragging?

the data is
6805

Daniil
Aug 26, 2013, 1:45 PM
Please try to replace:

<ext:ModelField Name="company" />
with

<ext:ModelField Name="company" Type="String" />

Does it now work?