打开EXCel表，并转成TABLE

1.打开excel文件

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog file1 = new OpenFileDialog();
            //file1.ShowDialog();
            file1.Filter = " Microsoft Excel files(*.xls)|*.xls;*.xlsx";
            file1.InitialDirectory = "f:\\wenbin\\工作文件\\KPI";
            file1.RestoreDirectory = true;
            file1.CheckPathExists = true;
            file1.CheckFileExists = true;
            if (file1.ShowDialog() == DialogResult.OK)
            {
                fileName = file1.FileName;
                textBox1.Text = fileName;
            }
        }

2.整理数据，录入数据库

private void button2_Click(object sender, EventArgs e)
        {
            if (fileName == "")
            {
                MessageBox.Show("请选择文件!");
            }
            else
            {
                //此连接只能操作Excel2007之前(.xls)文件
                //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
                //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
                //"HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反。
                //"IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。 
                string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + fileName + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'";
                OleDbConnection _conn = new OleDbConnection(strConn);
                try
                {
                    _conn.Open();
                    DataTable datatable = _conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string ExcelTableName = datatable.Rows[0]["TABLE_NAME"].ToString().Trim();
                    DataTable dt = new DataTable();
                    OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [" + ExcelTableName + "]", _conn);
                    adapter.Fill(dt);
                    string sql = "";
                    for (int i = 2; i < dt.Rows.Count; i++)
                    {
                        for (int j = 1; j < 6; j++)
                        {
                            var a = dt.Rows[i][j].ToString();
                            if (dt.Rows[i][j].ToString() != "")
                            {
                                switch (j)
                                {
                                    case 1:
                                        sql = "update ots_issue_hander set KPI_ACTUAL_DUTYPARTY ='承运商' WHERE OMS_NO =?";
                                        pValue.Add(dt.Rows[i][0].ToString().Trim());
                                        break;
                                    case 2:
                                        sql = "update ots_issue_hander h set h.Kpi_Actual_Return_Date =to_date('2099-1-1','yyyy-mm-dd') where oms_no =?";
                                        pValue.Add(dt.Rows[i][0].ToString().Trim());
                                        break;
                                    case 3:
                                        sql = "update ots_issue_hander h set h.DISCREPANCY_REMARK = '承运商' where oms_no =?";
                                        pValue.Add(dt.Rows[i][0].ToString().Trim());
                                        break;
                                    case 4:
                                        sql = "update ots_issue_hander h set kpi_return_intime_flag='承运商' where oms_no =?";
                                        pValue.Add(dt.Rows[i][0].ToString().Trim());
                                        break;
                                    case 5:
                                        sql = "update ots_issue_hander h set kpi_return_intime_flag=? where (kpi_return_intime_flag<>'承运商' OR kpi_return_intime_flag is null) and oms_no =?";
                                        pValue.Add(dt.Rows[i][j]);
                                        pValue.Add(dt.Rows[i][0].ToString().Trim());
                                        break;
                                }
                                //do st
                                if (sql != "")
                                {
                                    //实际导入请注销下面一行
                                    //if (sql=="update ots_issue_hander h set kpi_return_intime_flag=? where kpi_return_intime_flag<>'承运商' and oms_no =?")
                                    //{
                                    //    conn.DoExecuteSql(sql, (string[])pValue.ToArray(typeof(string)));
                                    //}
                                    conn.DoExecuteSql(sql, (string[])pValue.ToArray(typeof(string)));
                                    isSuccess = true;
                                    r = i - 1;
                                }
                                pValue.Clear();

                            }
                        }
                    }
                    if (isSuccess == true)
                    {
                        MessageBox.Show("'" + r + "'行全部导入成功！");
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                    //sendEmail("471478656@qq.com;", "", "KPI_RDC_DATA_BASE出错了", "错误原因：" + ex.ToString());
                }
                finally
                {
                    _conn.Close();
                }
            }
        }

3.

        /// <summary>
        /// 执行update/delete/insert语句
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="parValue"></param>
        /// <returns></returns>
        public int DoExecuteSql(string strSql, string[] parValue)
        {
            int num = 0;
            try
            {
                Open();
                OleDbCommand cmd = new OleDbCommand(strSql, connection);
                for (int i = 0; i < parValue.Length; i++)
                {
                    cmd.Parameters.Add(new OleDbParameter("@para" + i.ToString(), OleDbType.VarChar, 2000));
                    cmd.Parameters["@para" + i.ToString()].Value = parValue[i].ToString();
                }
                num = cmd.ExecuteNonQuery();
                return num;
            }
            catch (Exception e)
            {
                throw new Exception("执行 update/delete/insert 出错,当前语句:" + strSql + "  错误原因：" + e.Message);
            }
        }

 

4.全局变量

        private string fileName = "";
        private Conn conn = new Conn();
        ArrayList pValue = new ArrayList();
        bool isSuccess = false;
        int r = 0;