Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Web Controls > importing from excel to gridview

Reply
Thread Tools

importing from excel to gridview

 
 
Morris Neuman
Guest
Posts: n/a
 
      12-01-2008
Hi,

Is there a command on a .net form that can be initiated to import a
spreadsheet to a gridview? I would like to have a button on a form that when
clicked, a spreadsheet is imported and displayed on a gridview.

If you can send link with info or provide info that I can use to research
further, I would appreciate it. If you can provide genric code that would be
great.
--
Thanks
Morris
 
Reply With Quote
 
 
 
 
Jialiang Ge [MSFT]
Guest
Posts: n/a
 
      12-02-2008
Hello Morris

In order to import a spreadsheet to a GridView, you may consider using
OLEDB. Here is the example code for your reference:
http://www.codeproject.com/KB/grid/G...ortExport.aspx

<quote>
string conn = ("Provider=Microsoft.Jet.OLEDB.4.0;" +
("Data Source=C:\\path\\test.xls;" +
// or use instead of Excel 8.0 - Excel 5.0
"Extended Properties=\"Excel 8.0;\""));
string SSQL = "SELECT name , dept, salary from [sheet1$]";

// here use oleDataReader
OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);

DataSet ds = new DataSet();
//oleDA.TableMappings.Add("Table","ExcelTest"); // Require
oleDA.Fill(ds);
GridView1.DataSource = ds.Tables[0].DefaultView; // or [ ds ]
GridView1.DataBind();
</quote>

The idea is to read the spreadsheet data into a DataTable, then bind the
table to GridView.

Please note:
You may get the suggestion of using Office automation to accomplish the
task. Office automation is not supported to be used in ASP.NET:
http://support.microsoft.com/kb/257757/
Thus, we cannot use Office automation in this case.

Please let me know if you have any other concerns, or need anything else.

Regards,
Jialiang Ge ((E-Mail Removed), remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
http://www.velocityreviews.com/forums/(E-Mail Removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subs...#notifications.

MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subs.../aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 
Reply With Quote
 
 
 
 
Morris Neuman
Guest
Posts: n/a
 
      12-02-2008
Thanks for the quick response.

I tried the code referenced in your email but get an error.

I am using a master page and the import form is on the content page. As I
do not have a code behind page but a single aspx page, I included the
following in my MasterPage1.master
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.IO" %>

I then copied your code on to a test.aspx page with the Button3_Click as
follows:
protected void Button3_Click(object sender, EventArgs e)
{
string conn = ("Provider=Microsoft.Jet.OLEDB.4.0;" +
("Data Source=C:\\Program Files\\CALLMaster\\Data\\test.xls;" +
// or use instead of Excel 8.0 - Excel 5.0
"Extended Properties=\"Excel 8.0;\""));
string SSQL = "SELECT name , dept, salary from [sheet1$]";

// here use oleDataReader
OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
DataSet ds = new DataSet();
//oleDA.TableMappings.Add("Table","ExcelTest"); // Require
oleDA.Fill(ds);
GridView2.DataSource = ds.Tables[0].DefaultView; // or [ ds ]
GridView2.DataBind();

}

When I run the code I am getting the error:
Compilation Error
Description: An error occurred during the compilation of a resource required
to service this request. Please review the following specific error details
and modify your source code appropriately.

Compiler Error Message: CS0246: The type or namespace name
'OleDbDataAdapter' could not be found (are you missing a using directive or
an assembly reference?)

Source Error:
Line 32:
Line 33: // here use oleDataReader
Line 34: OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
Line 35: DataSet ds = new DataSet();
Line 36: //oleDA.TableMappings.Add("Table","ExcelTest"); // Require
Source File:
c:\Inetpub\wwwroot\CMWebManager\SystemAdminOnly\Co pies\Test.aspx Line: 34


I have never worked with OleDbDataAdapter.

What am I doing wrong?
Do I need to have a code behind file?
--
Thanks for your time and help.
Morris


""Jialiang Ge [MSFT]"" wrote:

> Hello Morris
>
> In order to import a spreadsheet to a GridView, you may consider using
> OLEDB. Here is the example code for your reference:
> http://www.codeproject.com/KB/grid/G...ortExport.aspx
>
> <quote>
> string conn = ("Provider=Microsoft.Jet.OLEDB.4.0;" +
> ("Data Source=C:\\path\\test.xls;" +
> // or use instead of Excel 8.0 - Excel 5.0
> "Extended Properties=\"Excel 8.0;\""));
> string SSQL = "SELECT name , dept, salary from [sheet1$]";
>
> // here use oleDataReader
> OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
>
> DataSet ds = new DataSet();
> //oleDA.TableMappings.Add("Table","ExcelTest"); // Require
> oleDA.Fill(ds);
> GridView1.DataSource = ds.Tables[0].DefaultView; // or [ ds ]
> GridView1.DataBind();
> </quote>
>
> The idea is to read the spreadsheet data into a DataTable, then bind the
> table to GridView.
>
> Please note:
> You may get the suggestion of using Office automation to accomplish the
> task. Office automation is not supported to be used in ASP.NET:
> http://support.microsoft.com/kb/257757/
> Thus, we cannot use Office automation in this case.
>
> Please let me know if you have any other concerns, or need anything else.
>
> Regards,
> Jialiang Ge ((E-Mail Removed), remove 'online.')
> Microsoft Online Community Support
>
> Delighting our customers is our #1 priority. We welcome your comments and
> suggestions about how we can improve the support we provide to you. Please
> feel free to let my manager know what you think of the level of service
> provided. You can send feedback directly to my manager at:
> (E-Mail Removed).
>
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/en-us/subs...#notifications.
>
> MSDN Managed Newsgroup support offering is for non-urgent issues where an
> initial response from the community or a Microsoft Support Engineer within
> 2 business day is acceptable. Please note that each follow up response may
> take approximately 2 business days as the support professional working with
> you may need further investigation to reach the most efficient resolution.
> The offering is not appropriate for situations that require urgent,
> real-time or phone-based interactions. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/en-us/subs.../aa948874.aspx
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

 
Reply With Quote
 
Jialiang Ge [MSFT]
Guest
Posts: n/a
 
      12-03-2008
Hello Morris,

You need to add include the directives

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

in your test.aspx file. It is not necessary to add the above into the
master page if the master page itself does not use OleDbDataAdapater.

To help you better understand why we need to add the imports in the
test.aspx file, please have a look at this example:

Suppose that we have a C# Dll project. In the project, we have Container.cs
that defines a class "Container", and Component.cs that defines a class
"Component".

In Container, it stores a component:
class Container
{
Component component = new Component();
}

In Component, it has a method that reads data from a spreadsheet with OLEDB:

class Component
{
public void ProcessData()
{
string conn = ("Provider=Microsoft.Jet.OLEDB.4.0;" +
("Data Source=C:\\Program Files\\CALLMaster\\Data\\test.xls;" +
// or use instead of Excel 8.0 - Excel 5.0
"Extended Properties=\"Excel 8.0;\""));
string SSQL = "SELECT name , dept, salary from [sheet1$]";

// here use oleDataReader
OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
DataSet ds = new DataSet();
//oleDA.TableMappings.Add("Table","ExcelTest"); // Require
oleDA.Fill(ds);
}
}

In this example, if we add
using System.Data.OleDb;
using System.Data;
only to Container.cs, we will get several compilation errors saying that
OleDbDataAdapter could not be found, (are you missing a using directive or
an assembly reference?) as you saw.

However, as long as we add
using System.Data.OleDb;
using System.Data;
to Component.cs, the error is fixed.

This Container.cs - Component.cs example is just like the Masterpage.master
- test.aspx relationship in this case. Importing the namespaces solely in
the master page does not "inherit" to the component pages (test.aspx),
thus, we need to import the namespaces in the component pages.

Any more clear? I hope that I'm not confusing you more.

Regards,
Jialiang Ge ((E-Mail Removed), remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(E-Mail Removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================


 
Reply With Quote
 
Morris Neuman
Guest
Posts: n/a
 
      12-04-2008
Thanks. Including the import namespace in the aspx page worked.

Now I get this error:
Server Error in '/CMWebManager' Application.
--------------------------------------------------------------------------------

No value given for one or more required parameters.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: No value given for one
or more required parameters.

Source Error:


Line 44: oleDA = new OleDbDataAdapter(SSQL, conn);
Line 45: //oleDA.TableMappings.Add("Table","ExcelTest"); // Require
Line 46: oleDA.Fill(ds);
Line 47: GridView2.DataSource = ds.Tables[0].DefaultView; // or [ ds ]
Line 48: GridView2.DataBind();

Source File:
c:\Inetpub\wwwroot\CMWebManager\SystemAdminOnly\Co pies\Test.aspx Line: 46

As I do not want to have the gridview prefilled per your example, I did not
include the Page_Load or the fillGrid() events.

My Button3_click code is as follows:
protected void Button3_Click(object sender, EventArgs e)
{
string conn = ("Provider=Microsoft.Jet.OLEDB.4.0;" +
("Data Source=C:\\Program Files\\CALLMaster\\Data\\test.xls;" +
// or use instead of Excel 8.0 - Excel 5.0
"Extended Properties=\"Excel 8.0;\""));
string SSQL = "SELECT name , dept, salary from [sheet1$]";

// here use oleDataReader
OleDbDataAdapter oleDA = new OleDbDataAdapter();
DataSet ds = new DataSet();
oleDA = new OleDbDataAdapter(SSQL, conn);
//oleDA.TableMappings.Add("Table","ExcelTest"); // Require
oleDA.Fill(ds);
GridView2.DataSource = ds.Tables[0].DefaultView; // or [ ds ]
GridView2.DataBind();

}

The GridView2 is defined as per your sample code:
<asp:GridView ID="GridView2" runat="server" BackColor="White"
BorderColor="#E7E7FF"
BorderStyle="None" BorderWidth="1px" CellPadding="3"
GridLines="Horizontal">
<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True"
ForeColor="#F7F7F7" />
<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C"
HorizontalAlign="Right" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True"
ForeColor="#F7F7F7" />
<AlternatingRowStyle BackColor="#F7F7F7" />
</asp:GridView>

Three questions:
1) why am I getting the error?
2) where is the data table that is getting the data from the excel
spreadsheet?
3) The fillGrid() per your example initialiazes the gridview on pageload
from an xml file - correct? If yes then how would the fillGrid change to get
data from an Access table?

I really appreciate your help.
--
Thanks
Morris


""Jialiang Ge [MSFT]"" wrote:

> Hello Morris,
>
> You need to add include the directives
>
> <%@ Import Namespace="System.Data" %>
> <%@ Import Namespace="System.Data.OleDb" %>
>
> in your test.aspx file. It is not necessary to add the above into the
> master page if the master page itself does not use OleDbDataAdapater.
>
> To help you better understand why we need to add the imports in the
> test.aspx file, please have a look at this example:
>
> Suppose that we have a C# Dll project. In the project, we have Container.cs
> that defines a class "Container", and Component.cs that defines a class
> "Component".
>
> In Container, it stores a component:
> class Container
> {
> Component component = new Component();
> }
>
> In Component, it has a method that reads data from a spreadsheet with OLEDB:
>
> class Component
> {
> public void ProcessData()
> {
> string conn = ("Provider=Microsoft.Jet.OLEDB.4.0;" +
> ("Data Source=C:\\Program Files\\CALLMaster\\Data\\test.xls;" +
> // or use instead of Excel 8.0 - Excel 5.0
> "Extended Properties=\"Excel 8.0;\""));
> string SSQL = "SELECT name , dept, salary from [sheet1$]";
>
> // here use oleDataReader
> OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
> DataSet ds = new DataSet();
> //oleDA.TableMappings.Add("Table","ExcelTest"); // Require
> oleDA.Fill(ds);
> }
> }
>
> In this example, if we add
> using System.Data.OleDb;
> using System.Data;
> only to Container.cs, we will get several compilation errors saying that
> OleDbDataAdapter could not be found, (are you missing a using directive or
> an assembly reference?) as you saw.
>
> However, as long as we add
> using System.Data.OleDb;
> using System.Data;
> to Component.cs, the error is fixed.
>
> This Container.cs - Component.cs example is just like the Masterpage.master
> - test.aspx relationship in this case. Importing the namespaces solely in
> the master page does not "inherit" to the component pages (test.aspx),
> thus, we need to import the namespaces in the component pages.
>
> Any more clear? I hope that I'm not confusing you more.
>
> Regards,
> Jialiang Ge ((E-Mail Removed), remove 'online.')
> Microsoft Online Community Support
>
> =================================================
> Delighting our customers is our #1 priority. We welcome your comments and
> suggestions about how we can improve the support we provide to you. Please
> feel free to let my manager know what you think of the level of service
> provided. You can send feedback directly to my manager at:
> (E-Mail Removed).
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =================================================
>
>
>

 
Reply With Quote
 
Jialiang Ge [MSFT]
Guest
Posts: n/a
 
      12-05-2008
Hello Morris

> 1) why am I getting the error?


The error happens because the schema of the xls file is not right. It
cannot find the column name, dept, salary in sheet1. The workbook in the
example is created in this way:

Open Excel. In the default "Sheet1" of the newly created workbook, input
the data:

A B C
1 name dept salary
2 test1 dep1 111
3 test2 dep2 222

Then save it as a xls in the specified path.

> 2) where is the data table that is getting the data from the
> excel spreadsheet?


I'm not sure that I understand this question. The data is retrieved from
Sheet1 (SELECT ... FROM [sheet1$]), and it's filled into our DataSet
(DataSet ds = new DataSet().

> 3) The fillGrid() per your example initialiazes the gridview on
> pageload from an xml file - correct? If yes then how would the fillGrid
> change to get data from an Access table?


Yes, fillGrid is used to initiate the content of GridView when the page is
loaded (before the Button3 is clicked). To change it to read from an Access
table, the code is almost the same as reading from a spreadsheet. Here is
an example for your reference:

http://www.codeproject.com/KB/databa...atareader.aspx

Have a nice week!

Regards,
Jialiang Ge ((E-Mail Removed), remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(E-Mail Removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

 
Reply With Quote
 
Jialiang Ge [MSFT]
Guest
Posts: n/a
 
      12-10-2008
Hello Morris,

I am writing to check the status of the issue on your side. Would you mind
letting me know the result of the suggestions? If you need further
assistance, feel free to let me know. I will be more than happy to be of
assistance.

Have a great day!

Regards,
Jialiang Ge ((E-Mail Removed), remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(E-Mail Removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

 
Reply With Quote
 
Morris Neuman
Guest
Posts: n/a
 
      12-16-2008
I found a detailed walkthrough of exactly what I was looking to do so am
trying to work with that example.
--
Thanks for all your help.
Morris


""Jialiang Ge [MSFT]"" wrote:

> Hello Morris,
>
> I am writing to check the status of the issue on your side. Would you mind
> letting me know the result of the suggestions? If you need further
> assistance, feel free to let me know. I will be more than happy to be of
> assistance.
>
> Have a great day!
>
> Regards,
> Jialiang Ge ((E-Mail Removed), remove 'online.')
> Microsoft Online Community Support
>
> =================================================
> Delighting our customers is our #1 priority. We welcome your comments and
> suggestions about how we can improve the support we provide to you. Please
> feel free to let my manager know what you think of the level of service
> provided. You can send feedback directly to my manager at:
> (E-Mail Removed).
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =================================================
>
>

 
Reply With Quote
 
Jialiang Ge [MSFT]
Guest
Posts: n/a
 
      12-17-2008
OK. If you meet with any problem with that example, please feel free to
tell me.

Regards,
Jialiang Ge ((E-Mail Removed), remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(E-Mail Removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

 
Reply With Quote
 
Blessy
Guest
Posts: n/a
 
      12-17-2008
Hi,
I want to export and import excel from and to datagrid using asp.net.
Im able to export to excel without any issues. but when i try to import the
exported excel file, its showing the error 'External table is not in the
expected format.' Please help me.

Thanks
Blessy

""Jialiang Ge [MSFT]"" wrote:

> OK. If you meet with any problem with that example, please feel free to
> tell me.
>
> Regards,
> Jialiang Ge ((E-Mail Removed), remove 'online.')
> Microsoft Online Community Support
>
> =================================================
> Delighting our customers is our #1 priority. We welcome your comments and
> suggestions about how we can improve the support we provide to you. Please
> feel free to let my manager know what you think of the level of service
> provided. You can send feedback directly to my manager at:
> (E-Mail Removed).
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =================================================
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
GridView to Excel then Excel to database table. mohaaron@gmail.com ASP .Net 0 11-06-2007 06:40 PM
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
GridView Hierarchical View - Gridview in Gridview =?Utf-8?B?bWdvbnphbGVzMw==?= ASP .Net 1 05-09-2006 06:48 PM
How to keep a module with the same name as a module it is importing from importing itself? plb Python 2 02-08-2005 03:14 PM
exporting an excel file from database; making changes to excel file and updating the database by importing it back Luis Esteban Valencia ASP .Net 1 01-12-2005 12:28 AM



Advertisments