C# read excel and CSV file

Selenium RC simulate keypress

作者: Max.Bai

时间: 2014/05

1. Read CSV file:

                string connString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="
                    + FileFolder + ";Extensions=csv;";
                DataTable dt = new DataTable();
                try
                {
                    using (OdbcConnection odbcConn = new OdbcConnection(connString))
                    {
                        odbcConn.Open();
                        OdbcCommand oleComm = new OdbcCommand();
                        oleComm.Connection = odbcConn;
                        oleComm.CommandText = "select * from [" + sFileName.Split(new char[] { '.' })[0] + "#csv]";
                        OdbcDataAdapter adapter = new OdbcDataAdapter(oleComm);
                        DataSet ds = new DataSet();
                        adapter.Fill(ds, sFileName.Split(new char[] { '.' })[0]);
                        dt = ds.Tables[0];
                        odbcConn.Close();
                    }
                }
                catch (Exception ex)
                {
                    Assert.Fail("Failed to read the CSV file is downloaded at path {0}. Error: {1}", FileFolder , ex.ToString());
                }

2. Read Excel File:

Note need install dirver, http://www.microsoft.com/en-us/download/details.aspx?id=13255

string connString = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source='"
                                + sExcelFilePath.Replace(@"\", "/")
                                + "';Extended Properties='Excel 12.0; HDR="
                                + (bHaveHeadRecord ? "YES" : "NO") + "; IMEX=1;'";
            DataTable dt = new DataTable();

            try
            {
                using (OleDbConnection odbcConn = new OleDbConnection(connString))
                {
                    odbcConn.Open();
                    OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format(@"SELECT * FROM [{0}$]", sSheetName), odbcConn);
                    adapter.Fill(dt);
                    odbcConn.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("Failed to read the excel file at path {0}. Error: {1}", sExcelFilePath, ex.Message));
            }