Reading and Excel file from C#

  • Thread starter Thread starter Marco Rispoli
  • Start date Start date
M

Marco Rispoli

I successfully opened an excel file, and displayed it into a datagrid.

This is the code:

string sConnectionString =@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
Server.MapPath("example.xls") + @";Extended Properties=Excel 8.0;";

OleDbConnection objConn=new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand oCmd=new OleDbCommand("SELECT * FROM DisplayView", objConn);
OleDbDataAdapter oAdapter=new OleDbDataAdapter();
oAdapter.SelectCommand = oCmd;

DataSet ds=new DataSet();
oAdapter.Fill(ds, "XLData");
ddgXls.DataSource=ds;
ddgXls.DataBind();


where I created the DisplayView in SELECT * FROM DisplayView by manually
following these steps:

*Highlight the rows and columns where the data resides
*On the Insert menu, point to Name, and then click Define.
*In the Names in workbook text box, type DisplayView, and then click OK.*
*On the File menu, click Save.
*On the File menu, click Exit.

I would like to access excel spreadsheets uploaded to a site and I don't
expect the users to follow these 5 steps to make my life easier.

I noticed that in the "define" menu there's already a name defined called
"Print_Area" that already encompasses the cells that I would like to select
from. I tried to write "SELECT * FROM Print_Area" but the code bombs
complaining that Print_Area doesn't exist.

Any idea how to get around this?

I would really like to select data from the excel spreadsheet without
forcing the users to define and assign a name to the area.

Please help ... I am not that strong with the office object model.

Thank you!
 
Hi Marco,

Rest all stuff is right you have to just change the select query. Instead of "select * from displayview" use "Select * from [Sheet1$]" or whatever is the name of the sheet in which the data resides. One caveat is that the sheet name should already exist for the Select query to work else it will throw and exception.
 
Thank you for your suggestion. It worked!

I am not too happy with this whole solution thought. It will work for now
.... but is there a better way to load an excel spreadsheet into a dataset?

Namely I would prefer to not have to specify a select statement but instead
I would like the dataset to simply load the first sheet, whatever the name
is ...

Thanks!

--
Marco Rispoli - NJ, USA / PP-ASEL
My on-line aviation community -> http://www.thepilotlounge.com
Bharat Biyani said:
Hi Marco,

Rest all stuff is right you have to just change the select query. Instead
Click to expand...
of "select * from displayview" use "Select * from [Sheet1$]" or whatever is
the name of the sheet in which the data resides. One caveat is that the
sheet name should already exist for the Select query to work else it will
throw and exception.
 
Hi Marco,

Ya...that one is sure not 100%.
Here's a good URL that will help u to get a reference to the first sheet in a workbook and then read. It is basically a Excel object model tutorial from Microsoft from .NET Developer's Perspective
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/ExcelObj.asp

Hope this solves most of your queries
--
Bharat Biyani ([email protected])
http://www.orcim.com



Marco Rispoli said:
Thank you for your suggestion. It worked!

I am not too happy with this whole solution thought. It will work for now
.... but is there a better way to load an excel spreadsheet into a dataset?

Namely I would prefer to not have to specify a select statement but instead
I would like the dataset to simply load the first sheet, whatever the name
is ...

Thanks!

--
Marco Rispoli - NJ, USA / PP-ASEL
My on-line aviation community -> http://www.thepilotlounge.com
Bharat Biyani said:
Hi Marco,

Rest all stuff is right you have to just change the select query. Instead
Click to expand...
of "select * from displayview" use "Select * from [Sheet1$]" or whatever is
the name of the sheet in which the data resides. One caveat is that the
sheet name should already exist for the Select query to work else it will
throw and exception.
Click to expand...
 
Thank you for your help!!

--
Marco Rispoli - NJ, USA / PP-ASEL
My on-line aviation community -> http://www.thepilotlounge.com
Bharat Biyani said:
Hi Marco,

Ya...that one is sure not 100%.
Here's a good URL that will help u to get a reference to the first sheet
Click to expand...
in a workbook and then read. It is basically a Excel object model tutorial
from Microsoft from .NET Developer's Perspective
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/ExcelObj.asp

Hope this solves most of your queries
--
Bharat Biyani ([email protected])
http://www.orcim.com



Marco Rispoli said:
Thank you for your suggestion. It worked!

I am not too happy with this whole solution thought. It will work for now
.... but is there a better way to load an excel spreadsheet into a dataset?

Namely I would prefer to not have to specify a select statement but instead
I would like the dataset to simply load the first sheet, whatever the name
is ...

Thanks!

--
Marco Rispoli - NJ, USA / PP-ASEL
My on-line aviation community -> http://www.thepilotlounge.com
Hi Marco,

Rest all stuff is right you have to just change the select query.
Click to expand...
Instead
of "select * from displayview" use "Select * from [Sheet1$]" or whatever is
the name of the sheet in which the data resides. One caveat is that the
sheet name should already exist for the Select query to work else it will
throw and exception.
--
Bharat Biyani ([email protected])
http://www.orcim.com



:

I successfully opened an excel file, and displayed it into a datagrid.

This is the code:

string sConnectionString =@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
Server.MapPath("example.xls") + @";Extended Properties=Excel 8.0;";

OleDbConnection objConn=new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand oCmd=new OleDbCommand("SELECT * FROM DisplayView", objConn);
OleDbDataAdapter oAdapter=new OleDbDataAdapter();
oAdapter.SelectCommand = oCmd;

DataSet ds=new DataSet();
oAdapter.Fill(ds, "XLData");
ddgXls.DataSource=ds;
ddgXls.DataBind();


where I created the DisplayView in SELECT * FROM DisplayView by manually
following these steps:

*Highlight the rows and columns where the data resides
*On the Insert menu, point to Name, and then click Define.
*In the Names in workbook text box, type DisplayView, and then click OK.*
*On the File menu, click Save.
*On the File menu, click Exit.

I would like to access excel spreadsheets uploaded to a site and I don't
expect the users to follow these 5 steps to make my life easier.

I noticed that in the "define" menu there's already a name defined called
"Print_Area" that already encompasses the cells that I would like to select
from. I tried to write "SELECT * FROM Print_Area" but the code bombs
complaining that Print_Area doesn't exist.

Any idea how to get around this?

I would really like to select data from the excel spreadsheet without
forcing the users to define and assign a name to the area.

Please help ... I am not that strong with the office object model.

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top