Winform导入excel（2007）到SQL SERVER2005

最新推荐文章于 2023-05-04 15:48:35 发布

iteye_2290

最新推荐文章于 2023-05-04 15:48:35 发布

阅读量100

点赞数

分类专栏：技术交流文章标签： WinForm Excel SQL Microsoft Office

技术交流专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1.下面的代码段中，对于xls和xlsx的区别

Normal ConnectionString : (work for xls files)

Provider=Microsoft.Jet.OLEDB.4 .0 ;Data Source={0 };Extended Properties=\" Excel 8.0;HDR=YES;\""

Office 2007 ConnectionString : (work for xlsx files)

Provider=Microsoft.ACE.OLEDB.12 .0 ;Data Source={0 };Extended Properties=\" Excel 12.0;HDR=YES;\""

2.如果出现“未在本地计算机上注册 Microsoft.ACE.OLEDB.12.0 提供程序”错误，请下载http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe 并安装

3.代码如下

//根据excel文件的地址，将文件转化成dataset格式
        public DataSet getXSLData(string filepath)
        {
            string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
            System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
            string strCom = "SELECT * FROM [Sheet1$]";
            Conn.Open();
            System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
            DataSet ds = new DataSet();
            myCommand.Fill(ds, "[Sheet1$]");
            Conn.Close();
            return ds;
        }

        public static int errorcount = 0;//记录错误信息条数
        public static int insertcount = 0;//记录插入成功条数
        public static int updatecount = 0;//记录更新信息条数

        public bool ImportXSL(string filepath)
        {
            try
            {
                DataSet ds = new DataSet();
                //取得数据集
                //调用上面的函数
                ds = getXSLData(filepath);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string Xuehao = ds.Tables[0].Rows[i][0].ToString();
                    string Xingming = ds.Tables[0].Rows[i][1].ToString();
                    string Mima = ds.Tables[0].Rows[i][2].ToString();
                    string Youxiang = ds.Tables[0].Rows[i][3].ToString();
                    string Xueyuan = ds.Tables[0].Rows[i][4].ToString();
                    string Dianhua = ds.Tables[0].Rows[i][5].ToString();
                    if (Xuehao != "" && Xingming != "" && Mima != "" && Xueyuan != "")
                    {
                        DataTable dt = _processManageDAL.IsStudentExisted(Xuehao);//
                        if (dt.Rows.Count > 0)
                        {
                            if (_processManageDAL.UpdateXueshengByID(Xuehao, Xingming, Mima, Youxiang, Xuehao, Dianhua))
                                updatecount++;
                        }
                        else
                        {
                            if (_processManageDAL.InsertXuesheng(Xuehao, Xingming, Mima, Youxiang, Xuehao, Dianhua))
                                insertcount++;
                        }
                    }
                    else
                    {
                        errorcount++;
                    }
                }
                if (errorcount + updatecount + insertcount == ds.Tables[0].Rows.Count)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }