[zz]winform导入excel

来源：互联网发布：深入理解java虚拟机pdf 编辑：程序博客网时间：2024/06/12 00:07
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
阅读全文
0 0