winform导入excel到access数据库中



   项目中用到了此功能。把大概做法跟大家共享下，希望对大家有所帮助。也给自己总结一下，激励自己再接再厉。下面中部分代码被汉字替换了，主要考虑到公司的产品权，希望谅解。

        /// <summary>
        /// 点击打开按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnOpen_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Filter = "Excel文件(*.xls)|*.xls";
                //标题
                ofd.Title = "打开";
                //多选
                ofd.Multiselect = false;
                //默认值
                ofd.FilterIndex = 1;
                ofd.InitialDirectory = System.AppDomain.CurrentDomain.BaseDirectory;
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    this.txtFilePath.Clear(); 
                    this.txtFilePath.Text = ofd.FileName;
                    
                }
            }
            catch(Exception ex)
            {
                //错误
                this.lblMsg.Text = ex.Message;
                return;
            }
        }

        /// <summary>
        /// 点击导入按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnImp_Click(object sender, EventArgs e)
        {
            string filePath = this.txtFilePath.Text.Trim();
            if (filePath.Length == 0)
            {
                this.lblMsg.Text = "请选择要导入数据的Excel文件!";
                return;
            }
            string fileType = filePath.Substring(filePath.LastIndexOf('.'));
            if (!fileType.Equals(".xls"))
            {
                this.lblMsg.Text = "导入文件类型为Excel文件!";
                return;
            }
            //this.lblMsg.Text = "正在导入,请稍候...";//这个地方要用多线程，唉。。。
                FileImportNY();
        }
        /// <summary>
        /// 导入能源数据
        /// </summary>
        private void FileImportNY()
        {
            try
            {
                OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder();
                connectStringBuilder.DataSource = this.txtFilePath.Text.Trim();
                connectStringBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";

                //HDR=NO没有表头;IMEX=1特殊格式处理
                connectStringBuilder.Add("Extended Properties", "Excel 8.0;HDR=No;IMEX=1");
                using (OleDbConnection connection = new OleDbConnection(connectStringBuilder.ConnectionString))
                {
                    DataSet ds = new DataSet();
                    string strSql = "select * from [sheet页名称$]";
                    OleDbCommand oleCmd = new OleDbCommand(strSql, connection);
                    connection.Open();
                    using (OleDbDataReader dataReader = oleCmd.ExecuteReader())
                    {
                        ds.Load(dataReader, LoadOption.OverwriteChanges, new string[] { "Sheet1" });
                        System.Data.DataTable dt = ds.Tables["Sheet1"];
                        if (dt.Rows.Count > 0)
                        {
                            string [] strColumn = new string[12];
                            for (int i = 2; i < dt.Rows.Count; i++)
                            {
                                for (int j = 0; j < 12;j++)
                                {
                                    //写入数据库数据
                                    strColumn[j] = dt.Rows[i][j].ToString();

                                }

                                //空行处理
                                bool isTrue = false;
                                for (int k = 0; k < strColumn.Length; k++)
                                {
                                    if (strColumn[k].ToString().Trim() != "")
                                    {
                                        isTrue = true;
                                        break;
                                    }
                                }
                                if (isTrue == false)
                                {
                                    continue;
                                }
                                string strColumn1 = strColumn[1].ToString().Trim();
                                if(strColumn1 == "")
                                {
                                    this.lblMsg.Text = "Excel文件中" + (i+1).ToString() + "行数据产品代码不能为空!";
                                    return;
                                }
                                //投入产出类型
                                string isTR = "";
                                string strColumn6 = strColumn[6].ToString().Trim();
                                if(strColumn6 == "投入")
                                {
                                    isTR = "1";
                                }
                                else if(strColumn6 == "产出")
                                {
                                    isTR = "2";
                                }
                                string strSF1 = "";
                                string strSF2 = "";
                                string strSF3 = "";
                                //是否轻收
                                string strColumn8 = strColumn[8].ToString().Trim();
                                if(strColumn8 == "否")
                                {
                                    strSF1 = "0";
                                }
                                else if(strColumn8 == "是")
                                {
                                    strSF1 = "1";
                                }
                                //是否高附
                                string strColumn9 = strColumn[9].ToString().Trim();
                                if(strColumn9 == "否")
                                {
                                    strSF2 = "0";
                                }
                                else if(strColumn9 == "是")
                                {
                                    strSF2 = "1";
                                }
                                //是否综合商品
                                string strColumn10 = strColumn[10].ToString().Trim();
                                if(strColumn10 == "否")
                                {
                                    strSF3 = "0";
                                }
                                else if(strColumn8 == "是")
                                {
                                    strSF3 = "1";
                                }

                                 //判断数据库中是否已经存在这条数据
                                string strCount = ExecuteSQL("select count(*) from 导入表 where 唯一键 = '" + strColumn[1].ToString().Trim() + "'").ToString();
                                string strSqlExc = "";
                                if(strCount == "0")
                                {

                                    //insert
                                    strSqlExc = "insert";
                                }
                                else
                                {

                                    //update
                                    strSqlExc = "update";
                                }

                                 //执行SQL
                                string strTS = ExecuteSQL(strSqlExc);
                                if (strTS != "")
                                {
                                    this.lblMsg.Text = strTS;
                                    return;
                                }
                            }
                            this.lblMsg.Text = "数据导入成功!";
                        }
                        else
                        {
                            this.lblMsg.Text = "导入数据失败!";
                        }
                    }
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                this.lblMsg.Text = ex.Message.ToString();
                return;
            }
            finally
            {
                
            }
            //刷新
        }