还是EXCEL导入SQL的问题,有点头疼!!

Question

基本实现把excel导入到sql中,但别人要求我动态导入，比如在EXCEL的某张表中,如果表里含有标题的话，导入SQL后SQL表中的列名就会出现问题.在不对EXCEL表做修改的情况下，如何选择性的导入SQL?(EXCEL遇到标题或者空行就不读)?请说详细!不甚感激!

Answer 1

没有用过，但是我想，如果导入excel用的是sql语句，可以试看看用where排除掉

Answer 2

空行的还比较好判断，但是标题的话就有些问题了
程序并比知道Excel里的某一行是标题还是内容，除非事先约定好！

Answer 3

你先把EXCEL文件在处理程序中做成DataTable
再导入数据库就可以了.

Answer 4

建议LZ使用oledb进行操作,这样可以摆脱对office的依赖,就算客户机没有安装office也可以进行导出! 
例子: 
使用oledbconnection打开连接,连接语句 "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No';Data Source= "+路径;
选择语句为"select * from ["+工作表名称+"$"+开始位置+":"+结算位置+"]" 
例如: 
select * from ["+工资+"$A:W]":选择从A列到W列(行同样); 
select * from ["+工资+"$A3:W4]":选择从A列第三行到W列第四行;
选择好后从内存的DataTable导入到数据库

Answer 5

你所谓的动态导入，也就是说有可能每次导入的excel标题有可能不同？
你可以使用配置文件或者是界面输入方式，让操作者手动输入excel标题就可以了。
DialogResult X = MessageBox.Show("你确定要导入:"+comboBox1.Text.Trim()+"应收金额 的数据吗吗?", "导入确认", MessageBoxButtons.YesNo);
                if (X == DialogResult.Yes)
                {
                    OleDbConnection oled = new OleDbConnection("provider=microsoft.jet.OLEDB.4.0;Data source=d:\\mysqlserver\\dgmk.mdb");
                    string ConnectionString = "[sheet1$]";
                    OleDbConnection conn = new OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
                    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+label5.Text.Trim()+";Extended Properties='Excel 8.0;IMEX=1'";
                    OleDbConnection olconn = new OleDbConnection(strConn);
                    OleDbDataAdapter oada = new OleDbDataAdapter("select * from [sheet1$]", olconn);
                    DataSet ds = new DataSet();
                    oled.Open();
                try
                  {
                        oada.Fill(ds, "[sheet1$]");
                        OleDbCommand comm = new OleDbCommand("select * from 网吧名称", conn);
                        comm.CommandType = CommandType.StoredProcedure;
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            if (ds.Tables[0].Rows[0]["应结"].ToString() != " ")
                            {
                                string o = comboBox1.Text.Trim() + "应收金额";
                                OleDbCommand oleddata = new OleDbCommand("Update cattle Set " + o + "='" + ds.Tables[0].Rows[i]["应结"].ToString() + "' where 网吧名称='" + ds.Tables[0].Rows[i]["网吧名称"].ToString() + "'", oled);
                                oleddata.ExecuteNonQuery();
                            }
                        }
                        MessageBox.Show("导入完成");
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("excel表格格式不对，或者数据连接失败，检查后重试"+ex.ToString(), "导入失败");
                    }
                }上面的代码是将excel数据导入access，其中应结是excel标题，你所说的情况，只需要装excel标题动态就可以了。

Answer 6

protected void Button1_Click(object sender, System.EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                name = FileUpload1.PostedFile.FileName;
                //取得文件名(抱括路径)里最后一个"."的索引
                int i = name.LastIndexOf(".");
                //取得文件扩展名
                string newext = name.Substring(i);
                if (newext == ".xls"||newext ==".XLS")
                {
                    FileInfo file = new FileInfo(name);
                    fileName = file.Name;
                    webFilePath = Server.MapPath("~/Files/"+fileName);
                    FileUpload1.SaveAs(webFilePath);
                }
                else
                {
                    Response.Write("<script>alert('上傳文件必須為Excel文檔!')</script>");
                }
                
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + webFilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                string query = "select * from [Sheet1$]";
                OleDbCommand oleCommand = new OleDbCommand(query, new OleDbConnection(strConn));
                OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
                DataSet myDataSet = new DataSet();
                //将Excel的[Sheet1]表内容填充到DataSe对象 
                try
                {
                    oleAdapter.Fill(myDataSet, "[Sheet1$]");
                    this.GridView1.DataSource = myDataSet;
                    this.GridView1.DataMember = "[Sheet1$]";
                    this.GridView1.DataBind();
                    this.GridView1.Visible = true;
                    this.Button2.Visible = true;
                }
                catch (Exception exx)
                {
                    Response.Write(exx.Message);
                }
                finally
                {
                    if (File.Exists(webFilePath))
                    {
                        File.Delete(webFilePath);
                    }
                }
            }
            else
            {
                Response.Write("<script>alert('請選擇正確的路徑!')</script>");
            }
        }        protected void Button2_Click(object sender, System.EventArgs e)
        {
            string conn = ConfigurationManager.AppSettings["ConnectionString"];
            SqlConnection sqlConn = new SqlConnection(conn);
            {
                try
                {
                    if (sqlConn.State == ConnectionState.Closed)
                    {
                        sqlConn.Open();
                    }
                    for (int i = 0; i < GridView1.Rows.Count; i++)
                    {
                        string myyear = GridView1.Rows[i].Cells[0].Text.Trim().Replace("&nbsp;", " ");
                        string mymonth = GridView1.Rows[i].Cells[1].Text.Trim().Replace("&nbsp;", " ");
                        string workid = GridView1.Rows[i].Cells[2].Text.Trim().Replace("&nbsp;", " ");
                        string name = GridView1.Rows[i].Cells[3].Text.Trim().Replace("&nbsp;", " ");
                        string workday = GridView1.Rows[i].Cells[4].Text.Trim().Replace("&nbsp;", " ");
                        string offday = GridView1.Rows[i].Cells[5].Text.Trim().Replace("&nbsp;", " ");
                        string off1 = GridView1.Rows[i].Cells[6].Text.Trim().Replace("&nbsp;", " ");
                        string off2 = GridView1.Rows[i].Cells[7].Text.Trim().Replace("&nbsp;", " ");
                        string others = GridView1.Rows[i].Cells[8].Text.Trim().Replace("&nbsp;", " ");
                        string overwork = GridView1.Rows[i].Cells[9].Text.Trim().Replace("&nbsp;", " ");
                        string weekend = GridView1.Rows[i].Cells[10].Text.Trim().Replace("&nbsp;", " ");
                        string basic = GridView1.Rows[i].Cells[11].Text.Trim().Replace("&nbsp;", " ");
                        string life = GridView1.Rows[i].Cells[12].Text.Trim().Replace("&nbsp;", " ");
                        string overfee = GridView1.Rows[i].Cells[13].Text.Trim().Replace("&nbsp;", " ");
                        string weekendfee = GridView1.Rows[i].Cells[14].Text.Trim().Replace("&nbsp;", " ");
                        string bonus = GridView1.Rows[i].Cells[15].Text.Trim().Replace("&nbsp;", " ");
                        string allowance = GridView1.Rows[i].Cells[16].Text.Trim().Replace("&nbsp;", " ");
                        string sumsalary = GridView1.Rows[i].Cells[17].Text.Trim().Replace("&nbsp;", " ");
                        string eatfee = GridView1.Rows[i].Cells[18].Text.Trim().Replace("&nbsp;", " ");
                        string tax = GridView1.Rows[i].Cells[19].Text.Trim().Replace("&nbsp;", " ");
                        string insurance = GridView1.Rows[i].Cells[20].Text.Trim().Replace("&nbsp;", " ");
                        string offsalary = GridView1.Rows[i].Cells[21].Text.Trim().Replace("&nbsp;", " ");
                        string salary = GridView1.Rows[i].Cells[22].Text.Trim().Replace("&nbsp;", " ");
                        
                        if (myyear.Trim() != "" && mymonth.Trim() != "" && workid.Trim() != "" && name.Trim() != "")
                        {
                            StrSql = "insert into salary(myyear,mymonth,WorkId,name,WorkDay,OffDay,Off1,Off2,Others,OverWork,Weekend,Basic,Life,OverFee,WeekendFee,bonus,allowance,SumSalary,EatFee,Tax,Insurance,OffSalary,Salary)values('" + myyear + "','" + mymonth + "','" + workid + "','" + name + "','" + workday + "','" + offday + "','" + off1 + "','" + off2 + "','" + others + "','" + overwork + "','" + weekend + "','" + basic + "','" + life + "','" + overfee + "','" + weekendfee + "','" + bonus + "','" + allowance + "','" + sumsalary + "','" + eatfee + "','" + tax + "','" + insurance + "','" + offsalary + "','" + salary + "')";
                            cmd = new SqlCommand(StrSql, sqlConn);
                            try
                            {
                                cmd.ExecuteNonQuery();
                            }
                            catch
                            {
                                Response.Write("<script>window.alert('導入失敗!')</script>");
                            }
                        }
                    }
                    Response.Write("<script>window.alert('導入成功!')</script>");
                }
                catch (Exception exx)
                {
                    Response.Write(exx.Message);
                }
                finally
                {
                    if (sqlConn.State == ConnectionState.Open)
                    {
                        sqlConn.Close();
                    }
                }
            }
            this.GridView1.Visible = false;
            this.Button2.Visible = false;
        }
    }