?
Solved

C# small GUI app to search an excel file for search request (sql)

Posted on 2007-07-25
2
Medium Priority
?
408 Views
Last Modified: 2008-02-01
I had a very nice found here on e-e when I was working on a Java coding exercise to read data from an Excel file. I am trying to learn C# and need to do the same thing. Basically a small GUI app that accepts a search request and then does  a simple sql query on an excel file. I am not having any luck - any help would be appreciated.

Karl66
0
Comment
Question by:Karl66
2 Comments
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 19564838
Look for OleDb examples for reading from Excel files.

Reading and Writing Excel using OLEDB
http://www.codeproject.com/office/excel_using_oledb.asp

Bob
0
 
LVL 29

Assisted Solution

by:Gautham Janardhan
Gautham Janardhan earned 500 total points
ID: 19565514
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month15 days, 20 hours left to enroll

850 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