获取Excel表中各个Sheet的方法

获取Excel表中各个Sheet的方法

 private void simpleButton2_Click(object sender, EventArgs e)

        {

            OfdBOM.Filter = "Excel文件|*.xls;*.xlsx";

            OfdBOM.Title = "选择BOM文件";

            OfdBOM.Multiselect = false;

            string strConn = "";

            if (OfdBOM.ShowDialog() == DialogResult.OK)

            {

                OleDbConnection olecon = new OleDbConnection();

                try

                {

                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + OfdBOM.FileName + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";

                    olecon = new OleDbConnection(strConn);

                    olecon.Open();

                }

                catch

                {

                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + OfdBOM.FileName + ";Extended Properties='Excel 12.0;HDR=NO;IMEX=1';";

                    olecon = new OleDbConnection(strConn);

                    olecon.Open();

                }

                //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等

                DataTable dtSheetName = olecon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

                //包含excel中表名的字符串数组

                string[] strTableNames = new string[dtSheetName.Rows.Count];

                for (int k = 0; k < dtSheetName.Rows.Count; k++)

                {

                    strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();  //获取各个Sheet名称

                }

                OleDbDataAdapter myCommand = null;

                DataTable dt = new DataTable();

                //从指定的表明查询数据,可先把所有表明列出来供用户选择

                string strExcel = "select * from [" + strTableNames[0] + "]";

                myCommand = new OleDbDataAdapter(strExcel, strConn);

                dt = new DataTable();

                myCommand.Fill(dt);

            }

        }