郑德才博客

今天又遇到一个问题，我使用OleDbConnection导入Excel到数据库中，但是只能导入2003版本Excel，在查找后发现，可以也可以使用2007版本的，代码如下：

            string mystring = "Provider = getExcelPathName(path);

            OleDbConnection cnnxls = new OleDbConnection(mystring);
            OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);

        /// <summary>
        /// 获取Excel文件路径
        /// </summary>
        /// <param name="FilePathName"></param>
        /// <returns></returns>
        private static string getExcelPathName(string FilePathName)
        {
            int nExcel = FilePathName.LastIndexOf(".");
            string ExcelType = FilePathName.Substring(nExcel + 1);
            string ExcelPathName = "";
            if (ExcelType == "xls")
            {
                ExcelPathName = getStrCon(FilePathName, ExceclType.Excecl2003);
            }
            else if (ExcelType == "xlsx")
            {
                ExcelPathName = getStrCon(FilePathName, ExceclType.Excecl2007);
            }
            else
            {
                MessageBox.Show("文件格式错误！");
            }
            return ExcelPathName;
        }

        /// <summary>
        /// 枚举Excel文件类型
        /// </summary>
        public enum ExceclType { Excecl2003, Excecl2007, Excecl2000 };

        /// <summary>
        /// Excel文件类型判断
        /// </summary>
        /// <param name="strPath"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private static string getStrCon(string strPath, ExceclType type)
        {
            string strConn2007 = "MICROSOFT.Ace.OleDb.12.0','Excel 12.0;HDR=YES;DATABASE=" + strPath + "'";
            string strCon2003 = "MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=" + strPath + "'";
            if (type == ExceclType.Excecl2003)
            {
                return strCon2003;
            }
            else
            {
                return strConn2007;
            }
        }