?
Solved

how to c# to read excel file?

Posted on 2008-11-12
53
Medium Priority
?
1,297 Views
Last Modified: 2013-12-17
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.
0
Comment
Question by:1jaws
  • 27
  • 26
53 Comments
 
LVL 21

Expert Comment

by:silemone
ID: 22943003
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
 

Author Comment

by:1jaws
ID: 22943265
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
 

Author Comment

by:1jaws
ID: 22943279
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 21

Expert Comment

by:silemone
ID: 22943375
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
 

Author Comment

by:1jaws
ID: 22943422
http://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx
i took the Read Excel Spreadsheet using ADO.NET and DataSet one
0
 
LVL 21

Expert Comment

by:silemone
ID: 22943465
ok...DbProviderFactory does exist...it's in the common library...hmmm..
0
 
LVL 21

Expert Comment

by:silemone
ID: 22943507
ok...

try

using System.Data.OleDb;   at top...see how many errors...
0
 

Author Comment

by:1jaws
ID: 22943529
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
 
LVL 21

Expert Comment

by:silemone
ID: 22944021
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
 

Author Comment

by:1jaws
ID: 22944291
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
 

Author Comment

by:1jaws
ID: 22944380
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
 
LVL 21

Expert Comment

by:silemone
ID: 22944916
ok...did you create a excel workbook called Cities?
0
 
LVL 21

Expert Comment

by:silemone
ID: 22944960
your connection string is wrong...
0
 
LVL 21

Expert Comment

by:silemone
ID: 22944970
because I don't think you created the Excel Book called Book1 with workbook Cities
0
 

Author Comment

by:1jaws
ID: 22945737
i do have excel book called Book1  saved on my documents and workbook Cities
0
 
LVL 21

Expert Comment

by:silemone
ID: 22946544
well that was my wishful thinking...well that error has to do with an incorrect connection string
0
 

Author Comment

by:1jaws
ID: 22947664
ok, what can i do the correct that then?
any idea?
0
 
LVL 21

Expert Comment

by:silemone
ID: 22951491
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
 
LVL 21

Expert Comment

by:silemone
ID: 22951575
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
 

Author Comment

by:1jaws
ID: 22952525
hmm i need this for excel 2007, what i need to change for that?
0
 
LVL 21

Expert Comment

by:silemone
ID: 22953092
is the extension to the file xlsx?
0
 
LVL 21

Expert Comment

by:silemone
ID: 22953271
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
 

Author Comment

by:1jaws
ID: 22953356
yes, this is the extension .. i need this on C# though, it wouldnt work this in c# ??
0
 
LVL 21

Expert Comment

by:silemone
ID: 22953426
did you use the string i gave?  ok...first, DataSource should match where your file is kept and the name of your file...
0
 
LVL 21

Expert Comment

by:silemone
ID: 22953474
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
 

Author Comment

by:1jaws
ID: 22954105
i am sorry but which one i need to use, top or buttom?
0
 

Author Comment

by:1jaws
ID: 22954184
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
 

Author Comment

by:1jaws
ID: 22954306
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
 
LVL 21

Expert Comment

by:silemone
ID: 22954714
Data Source=C:\My Documents\Book1.xlsx  <---did you place this folder on your C drive in your My Documents folder?
0
 

Author Comment

by:1jaws
ID: 22954746
it is no folder, just the Book1.xlsx file i have in My documents..
0
 

Author Comment

by:1jaws
ID: 22954958
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
 

Author Comment

by:1jaws
ID: 22955008
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
 
LVL 21

Expert Comment

by:silemone
ID: 22955067
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
 
LVL 21

Expert Comment

by:silemone
ID: 22955095
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
 
LVL 21

Expert Comment

by:silemone
ID: 22955101
what is your exact error please...word for word...
0
 

Author Comment

by:1jaws
ID: 22955150
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
 

Author Comment

by:1jaws
ID: 22955157
i am hoping connection stiring is working fine but getting this error..
0
 

Author Comment

by:1jaws
ID: 22955221
when i put stop to dataset cities, it is saying null, so now i am totally confused.. wouldnt be full?
0
 
LVL 21

Expert Comment

by:silemone
ID: 22955329
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
 

Author Comment

by:1jaws
ID: 22955401
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
 

Author Comment

by:1jaws
ID: 22955446
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
 
LVL 21

Expert Comment

by:silemone
ID: 22955575
It works for me...
0
 
LVL 21

Expert Comment

by:silemone
ID: 22955584
it is your credentials...you have to set permissions so that the file is accessible for you...
0
 
LVL 21

Expert Comment

by:silemone
ID: 22955599
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
 
LVL 21

Expert Comment

by:silemone
ID: 22955677
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
 

Author Comment

by:1jaws
ID: 22955838
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
 
LVL 21

Expert Comment

by:silemone
ID: 22956126
I'll definitely start looking it up...:0)
0
 

Author Comment

by:1jaws
ID: 22956167
i named it Cannot move or rename the Documents and Settings folder
0
 

Author Comment

by:1jaws
ID: 22956190
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
 
LVL 21

Expert Comment

by:silemone
ID: 22957115
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
 

Author Comment

by:1jaws
ID: 22957396
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
 
LVL 21

Accepted Solution

by:
silemone earned 2000 total points
ID: 22957731
add line below


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

Author Closing Comment

by:1jaws
ID: 31516072
Thank you so much for sticking with me on this and now finally working.... Well deserved points!!!!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question