importing from excel to gridview

[Morris Neuman]

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.

 

[Jialiang Ge [MSFT]]

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/GridView_ImportExport.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 ([email protected], 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 address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#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/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Morris Neuman]

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\Copies\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

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/GridView_ImportExport.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 ([email protected], 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 address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#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/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jialiang Ge [MSFT]]

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 ([email protected], 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 address removed).

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

 

[Morris Neuman]

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\Copies\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

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 ([email protected], 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 address removed).

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

 

[Jialiang Ge [MSFT]]

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/database/adonet_datareader.aspx

Have a nice week!

Regards,
Jialiang Ge ([email protected], 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 address removed).

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

 

[Jialiang Ge [MSFT]]

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 ([email protected], 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 address removed).

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

 

[Morris Neuman]

I found a detailed walkthrough of exactly what I was looking to do so am
trying to work with that example.

 

[Jialiang Ge [MSFT]]

OK. If you meet with any problem with that example, please feel free to
tell me.

Regards,
Jialiang Ge ([email protected], 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 address removed).

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

 

[Blessy]

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

 

[KurtWM]

:
Morris,

Could you post that detailed walkthrough here, please?

Thanks
KurtWM

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

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 ([email protected], 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 address removed).

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