?
Solved

How do I read data from Excel?

Posted on 2007-10-07
3
Medium Priority
?
228 Views
Last Modified: 2013-12-17
I read and write data from Access all the time using:

OleDbConnection, OleDbCommand, OleDbDataReader, etc.

Can I do the same for Excel?

If so, what's different?

thanks,
newbieweb
0
Comment
Question by:newbieweb
3 Comments
 
LVL 22

Assisted Solution

by:JimBrandley
JimBrandley earned 600 total points
ID: 20031182
Bob (TheLearnedOne) shows how to do that here:

http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_22152693.html

Jim
0
 
LVL 30

Assisted Solution

by:anarki_jimbel
anarki_jimbel earned 600 total points
ID: 20031457
0
 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 800 total points
ID: 20032340
this function would give u the sheet names in an excel

public static string[] GetSheetNames(string FilePath)
        {
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                "Data Source=" + FilePath + ";" +
                                "Extended Properties=\"Excel 8.0;HDR=YES\"";
            OleDbConnection conn = null;
            string[] xlsSheets = null;
            DataTable dt = null;
            try
            {
                using (conn = new OleDbConnection(connString))
                {
                    if (conn.State != ConnectionState.Open) { conn.Open(); }
                    dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if ((dt != null) || (dt.Rows.Count > 0))
                    {
                        xlsSheets = new String[dt.Rows.Count];
                        int i = 0;
                        string sheetName;
                        foreach (DataRow row in dt.Rows)
                        {
                            sheetName = row["TABLE_NAME"].ToString();
                            if (sheetName.StartsWith("'")) { sheetName = sheetName.Remove(0, 1); }
                            if (sheetName.EndsWith("'")) { sheetName = sheetName.Remove(sheetName.Length - 1, 1); }
                            if (sheetName.EndsWith("$")) { sheetName = sheetName.Remove(sheetName.Length - 1, 1); }
                            xlsSheets[i] = sheetName;
                            i++;
                        }
                    }
                }
            }
            finally
            {
                if (conn != null)
                {
                    try
                    {
                        conn.Close();
                        conn.Dispose();
                        conn = null;
                    }
                    catch (OleDbException exOleDb)
                    {
                        System.Console.Write(exOleDb.Message);
                    }
                }

                if (dt != null)
                {
                    dt.Dispose();
                }
            }
            return xlsSheets;
        }



this would get the data from the above retrieved sheets

static DataTable GetData(string FilePath, string SheetName, bool FirstRowIsHeader)
        {
            string connString = null;
            if (FirstRowIsHeader)
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                             "Data Source=" + FilePath + ";" +
                             "Extended Properties=\"Excel 8.0;HDR=YES\"";
            }
            else
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Data Source=" + FilePath + ";" +
                            "Extended Properties=\"Excel 8.0;HDR=NO\"";
            }
            OleDbConnection conn = null;
            OleDbCommand cmd = new OleDbCommand();
            DataTable dt = null;
            IDataReader dr = null;
            try
            {
                using (conn = new OleDbConnection(connString))
                {
                    if (conn.State != ConnectionState.Open) { conn.Open(); }
                    cmd.Connection = conn;
                    cmd.CommandText = "SELECT * FROM [" + SheetName + "$]";
                    OleDbDataAdapter adapter = new OleDbDataAdapter();
                    adapter.SelectCommand = cmd;
                    dt = new DataTable(SheetName);
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }

                if (dr != null)
                {
                    dr.Close();
                    dr.Dispose();
                    dr = null;
                }

                if (conn != null)
                {
                    try
                    {
                        conn.Close();
                        conn.Dispose();
                        conn = null;
                    }
                    catch (OleDbException exOleDb)
                    {
                        System.Console.WriteLine(exOleDb.Message);
                    }
                }
            }
            return dt;
        }
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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 shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview
Suggested Courses

862 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