how to c# to read excel file?

I just need a example to how to c# to read excel file into form, like maybe hit the button like upload excel
and c# will read excel file? THis is web development.
1jawsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

silemoneCommented:
good example opening xls file

http://blog.brezovsky.net/en-text-38.html 

or is it a cvs file?

this link shows how to do it for cvs file

http://www.codeproject.com/KB/cs/Excel_Application_in_C_.aspx
0
1jawsAuthor Commented:
i actually had this example from internet
but giving me errors

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
 
 
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
       

    }
    protected void Button1_Click(object sender, EventArgs e)
    {

       



        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended    Properties=""Excel 8.0;HDR=YES;""";

        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

        DbDataAdapter adapter = factory.CreateDataAdapter();

        DbCommand selectCommand = factory.CreateCommand();
        selectCommand.CommandText = "SELECT ID,City,State FROM [Cities$]";

        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = connectionString;

        selectCommand.Connection = connection;

        adapter.SelectCommand = selectCommand;

        DataSet cities = new DataSet();

        adapter.Fill(cities);

        gridEX1.SetDataBinding(cities.Tables[0], "");
        gridEX1.RetrieveStructure();

    }
}

i am sure not including something thats why
<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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 runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="gridEX1" runat="server">
        </asp:GridView>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" /></div>
    </form>
</body>
</html>
0
1jawsAuthor Commented:
these are the errors


Error      1      The type or namespace name 'DbProviderFactory' could not be found (are you missing a using directive or an assembly reference?)      c:\inetpub\wwwroot\testforexcel\Default.aspx.cs      28      9      http://localhost/testforexcel/
Error      2      The name 'DbProviderFactories' does not exist in the current context      c:\inetpub\wwwroot\testforexcel\Default.aspx.cs      28      37      http://localhost/testforexcel/
Error      3      The type or namespace name 'DbDataAdapter' could not be found (are you missing a using directive or an assembly reference?)      c:\inetpub\wwwroot\testforexcel\Default.aspx.cs      30      9      http://localhost/testforexcel/
Error      4      The type or namespace name 'DbCommand' could not be found (are you missing a using directive or an assembly reference?)      c:\inetpub\wwwroot\testforexcel\Default.aspx.cs      32      9      http://localhost/testforexcel/
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

silemoneCommented:
yep...i though that would be the error...where is the library for DbProviderFactory?


i in fact think DbProviderFactory is a DataSet object created by the DataSet designer...is this the same link i sent you?  if not, send me the link of your example.
0
1jawsAuthor Commented:
http://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx
i took the Read Excel Spreadsheet using ADO.NET and DataSet one
0
silemoneCommented:
ok...DbProviderFactory does exist...it's in the common library...hmmm..
0
silemoneCommented:
ok...

try

using System.Data.OleDb;   at top...see how many errors...
0
1jawsAuthor Commented:
same errors

Error      1      The type or namespace name 'DbProviderFactory' could not be found (are you missing a using directive or an assembly reference?)      c:\inetpub\wwwroot\testforexcel\Default.aspx.cs      28      9      http://localhost/testforexcel/
Error      2      The name 'DbProviderFactories' does not exist in the current context      c:\inetpub\wwwroot\testforexcel\Default.aspx.cs      28      37      http://localhost/testforexcel/
Error      3      The type or namespace name 'DbDataAdapter' could not be found (are you missing a using directive or an assembly reference?)      c:\inetpub\wwwroot\testforexcel\Default.aspx.cs      30      9      http://localhost/testforexcel/
Error      4      The type or namespace name 'DbCommand' could not be found (are you missing a using directive or an assembly reference?)      c:\inetpub\wwwroot\testforexcel\Default.aspx.cs      32      9      http://localhost/testforexcel/
0
silemoneCommented:
add this...I thought this would automatically be included...but I guess not...so I said try using System.Data.Common; when in fact you should have


added this:  
using System.Data.Common;
0
1jawsAuthor Commented:
Error      1      'System.Web.UI.WebControls.GridView' does not contain a definition for 'SetDataBinding'      c:\inetpub\wwwroot\testforexcel\Default.aspx.cs      46      17      http://localhost/testforexcel/
Error      2      'System.Web.UI.WebControls.GridView' does not contain a definition for 'RetrieveStructure'      c:\inetpub\wwwroot\testforexcel\Default.aspx.cs      47      17      http://localhost/testforexcel/
0
1jawsAuthor Commented:
ignore above errors, i got this one

Could not find installable ISAM.+            Cities      {System.Data.DataSet}      System.Data.DataSet
+            adapter      {System.Data.OleDb.OleDbDataAdapter}      System.Data.Common.DbDataAdapter {System.Data.OleDb.OleDbDataAdapter}
+            this      {ASP.default_aspx}      _Default {ASP.default_aspx}
protected void Button1_Click(object sender, EventArgs e)
    {

       



        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended    Properties=""Excel 8.0;HDR=YES;""";

        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

        DbDataAdapter adapter = factory.CreateDataAdapter();

        DbCommand selectCommand = factory.CreateCommand();
        selectCommand.CommandText = "SELECT ID,City,State FROM [Cities$]";

        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = connectionString;

        selectCommand.Connection = connection;

        adapter.SelectCommand = selectCommand;

        DataSet Cities = new DataSet();

        adapter.Fill(Cities);

       
        gridEX1.DataSource = Cities.Tables[0].DefaultView;


    }
}


error shows here  adapter.Fill(Cities);
0
silemoneCommented:
ok...did you create a excel workbook called Cities?
0
silemoneCommented:
your connection string is wrong...
0
silemoneCommented:
because I don't think you created the Excel Book called Book1 with workbook Cities
0
1jawsAuthor Commented:
i do have excel book called Book1  saved on my documents and workbook Cities
0
silemoneCommented:
well that was my wishful thinking...well that error has to do with an incorrect connection string
0
1jawsAuthor Commented:
ok, what can i do the correct that then?
any idea?
0
silemoneCommented:
try all 4...on yours, I replace """" with ' instead and removed ; at the end of line one one under...

These work for EXCEL except for excel 2007

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties='Excel 8.0;HDR=YES;'";
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties='Excel 8.0;HDR=YES'";



this is the alternative if the above doesn't work:

string connectionString = @"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";

string connectionString = @"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
0
silemoneCommented:
by the way, if you ever have problems with connections strings, this is an awesome site i always go to:

http://www.connectionstrings.com/

look up connecting to excel..
0
1jawsAuthor Commented:
hmm i need this for excel 2007, what i need to change for that?
0
silemoneCommented:
is the extension to the file xlsx?
0
silemoneCommented:
if so, this would be the way:

string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";


by the way, i meant to mention also:  

"" "" is only for VB as in your original connectstring...

0
1jawsAuthor Commented:
yes, this is the extension .. i need this on C# though, it wouldnt work this in c# ??
0
silemoneCommented:
did you use the string i gave?  ok...first, DataSource should match where your file is kept and the name of your file...
0
silemoneCommented:
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\Book1.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";


string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\Book1.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';";



keep everything else the same....except  c:\myFolder\Book1.xlsx  <--- replace with correct filepath for Book1.xlsx
0
1jawsAuthor Commented:
i am sorry but which one i need to use, top or buttom?
0
1jawsAuthor Commented:
Source=C:\My Documents\Book1.xlsx;  i changed this because where book1.xlsx is there still saying not valid path..

why you gave me two connection string?
0
1jawsAuthor Commented:
this is the connection string i have listed
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\My Documents\Book1.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';";

and still getting path is not correct.. i dont know why?
0
silemoneCommented:
Data Source=C:\My Documents\Book1.xlsx  <---did you place this folder on your C drive in your My Documents folder?
0
1jawsAuthor Commented:
it is no folder, just the Book1.xlsx file i have in My documents..
0
1jawsAuthor Commented:
oh my god , it is mess, because this might be not correct because, person before me in this company, which is user, i am using his user account, so under documents and settings it is his folder and name on it, but i changed it windows user name manually by going from User accounts, so when i log in to windows i can see my name on the start up but if i explore and see the folder under documents and settings i still see his folder with the name, i couldnit change the folder name, didnt let me, so now when i put this link as a datasource it is saying someonelse using this blah blah.. i dont know i am kind of stuck, maybe thiats why i get this error...
0
1jawsAuthor Commented:
ok i replace this right under C drive and path is correct now, but i get this error still
The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

Am i using access database, i need sql server database...
0
silemoneCommented:
oh yea...remove the last ;

string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\My Documents\Book1.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES'";

last my not least (last option i know of...)
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\My Documents\Book1.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';IMEX=1";
0
silemoneCommented:
ok...different error...so forget my last post i suppose..i guess connection string is working now...

now the last thing is you need rights to view or write to the folder...correct?
0
silemoneCommented:
what is your exact error please...word for word...
0
1jawsAuthor Commented:
this is the exact error message
The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

it is highliging
adapter.Fill(Cities);
0
1jawsAuthor Commented:
i am hoping connection stiring is working fine but getting this error..
0
1jawsAuthor Commented:
when i put stop to dataset cities, it is saying null, so now i am totally confused.. wouldnt be full?
0
silemoneCommented:
ok...test it by putting a fake name in the connection string and see what error you get......
instead of Book1.xlsx...change it to Book.xlsx and see what happens...but i think its just you don't have the permissions to this file...meaning your credentials don't allow you to go into this file.  if that's that case, then you dbadmin/network admin will have to fix...

tell me if you get the same error or different error if you add a fictitious file name in the connect string instead of the real xlsx filename...
0
1jawsAuthor Commented:
i did that i get different error
The Microsoft Office Access database engine could not find the object 'Cities$'.  Make sure the object exists and that you spell its name and the path name correctly.
0
1jawsAuthor Commented:
ok can u do me a favor and see if will be ok with yours this is exact my files

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.Common;
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
       

    }
    protected void Button1_Click(object sender, EventArgs e)
    {





        string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\excelfolder\Book1.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';";
       

        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

        DbDataAdapter adapter = factory.CreateDataAdapter();

        DbCommand selectCommand = factory.CreateCommand();
        selectCommand.CommandText = "SELECT ID,City,State FROM [Cities$]";

        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = connectionString;

        selectCommand.Connection = connection;

        adapter.SelectCommand = selectCommand;

        DataSet Cities = new DataSet();

        adapter.Fill(Cities);

       
        gridEX1.DataSource = Cities.Tables[0].DefaultView;


    }
}


and aspx file is

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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 runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="gridEX1" runat="server">
        </asp:GridView>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" /></div>
    </form>
</body>
</html>

and excel is saved this location under C:\excelfolder\Book1.xlsx so see when u clicked on button what will happen on yours...
0
silemoneCommented:
It works for me...
0
silemoneCommented:
it is your credentials...you have to set permissions so that the file is accessible for you...
0
silemoneCommented:
i did get one error but it has nothing to do with what's your problems...as the dataadapter filled the dataset, it found that my column names were wrong, so since I'm not a guru at EXCEL, i was unable to fix that error, but the dataadapter did open the file, otherwise it wouldn't have known my Column Names were incorrect...
0
silemoneCommented:
ok...my program worked completed...since i don't know how to change column names

i changed select statement so that it didn't give me specific column names....i.e.


i Change to :   Select * from [Cities$]; and i got no errors...
0
1jawsAuthor Commented:
ok if i open up a new question asking how to change the folder name on the documetns and settings do u know how to do that so you can get the points
0
silemoneCommented:
I'll definitely start looking it up...:0)
0
1jawsAuthor Commented:
i named it Cannot move or rename the Documents and Settings folder
0
1jawsAuthor Commented:
i put it under network window security..
found this link for it but didnt understand what i need to do
http://support.microsoft.com/kb/236621
0
silemoneCommented:
i apologize...this issue is still that you don't have rights to the folder...try this:

when you get to the excel icon, try using Run As and log in as an administrator...that should give you all the privileges you need to copy the file...
0
1jawsAuthor Commented:
ok, i created a new user and created book1 again in that user and now,so now i have right to this file.. i dont get any errors when i hit button after running the application. when i put break to dataset i can see the dataset but it is not filling the grid or anything... what i need to do?
0
silemoneCommented:
add line below


gridEX1.DataSource = Cities.Tables[0].DefaultView;
gridEX1.DataBind();      <-----add this line
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
1jawsAuthor Commented:
Thank you so much for sticking with me on this and now finally working.... Well deserved points!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.