winform导入excel

winfrom导入excel内容，要求能够excel中多个工作簿的内容。代码如下：

 

#region 导入excel数据
        private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
             openFileDialog.Filter = "表格文件 (*.xls)|*.xls";
             openFileDialog.RestoreDirectory = true;
             openFileDialog.FilterIndex = 1;
             if (openFileDialog.ShowDialog() == DialogResult.OK)
             {
                 Import(openFileDialog.FileName);
             }
        }

        /// <summary>
        /// 导入excel数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static bool Import(string filePath)
        {
            try
            {
                //Excel就好比一个数据源一般使用
                //这里可以根据判断excel文件是03的还是07的，然后写相应的连接字符串
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection con = new OleDbConnection(strConn);
                con.Open();
                string[] names = GetExcelSheetNames(con);
                if (names.Length > 0)
                {
                    foreach (string name in names)
                    {
                        OleDbCommand cmd = con.CreateCommand();
                        cmd.CommandText = string.Format(" select * from [{0}]", name);//[sheetName]要如此格式
                        OleDbDataReader odr = cmd.ExecuteReader();
                        while (odr.Read())
                        {
                            if (odr[0].ToString() == "序号")//过滤列头  按你的实际Excel文件
                                continue;
                           //数据库添加操作
                            /*进行非法值的判断
                             * 添加数据到数据表中
                             * 添加数据时引用事物机制，避免部分数据提交
                             * Add(odr[1].ToString(), odr[2].ToString(), odr[3].ToString());//数据库添加操作，Add方法自己写的
                             * */
                           
                        }
                        odr.Close();
                    }
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
         /// 查询表名
         /// </summary>
         /// <param name="con"></param>
         /// <returns></returns>
         public static string[] GetExcelSheetNames(OleDbConnection con)
         {
             try
             {
                System.Data.DataTable  dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new[] { null, null, null, "Table" });//检索Excel的架构信息
                 var sheet = new string[dt.Rows.Count];
                 for (int i = 0, j = dt.Rows.Count; i < j; i++)
                 {
                     //获取的SheetName是带了$的
                     sheet[i] = dt.Rows[i]["TABLE_NAME"].ToString();
                 }
                 return sheet;
             }
             catch
             {
                 return null;
             }
         }

        //下面这种方法获取excel Worksheets Name时，提示无法访问该exceL文件，所以改为上面获取工作簿名的方式

        ///// <summary>
        ///// 获得excel sheet所有工作簿名字
        ///// </summary>
        ///// <param name="filePath"></param>
        ///// <returns></returns>
        //public static string[] GetExcelSheetNames(string filePath)
        //{
        //    Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
        //    Microsoft.Office.Interop.Excel.Workbooks wbs = excelApp.Workbooks;
        //    Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        //    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        //    Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        //    int count = wb.Worksheets.Count;
        //    string[] names = new string[count];
        //    for (int i = 1; i <= count; i++)
        //    {
        //        names[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Name;
        //    }
        //    return names;
        //}
        #endregion

 

转载于:https://www.cnblogs.com/bianlan/archive/2012/12/13/2816353.html