高分求Excel导入数据库

[fastrain]

如题，我用的是三层结构。我的想法分一下几步：
1. 选择要导入的文件，一个文件只导入第一个sheet.
2. 点击下一步或者读取数据按钮将excel的数据，在网页中显示出来。并在第一列加上checkBox，让用户选择是否导入。
3. 将所有选中的checkBox的行导入到数据库中。
最好有完整的代码，谢谢！

[yxzbest]

关注，顶

[ws_hgo]

 //先将Excel传到服务器上面
    protected void Button1_Click(object sender, EventArgs e)
    {
        string Excel = string.Empty;
        Boolean fileOK = false;
        String path = Server.MapPath("~/UploadExcel/");
        if (FileUpload1.HasFile)
        {
            String fileExtension =
                System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
            String[] allowedExtensions = 
                { ".xls" };
            for (int i = 0; i < allowedExtensions.Length; i++)
            {
                if (fileExtension == allowedExtensions[i])
                {
                    fileOK = true;
                }
            }
        }

        if (fileOK)
        {
            try
            {
                //FileUpload1.PostedFile.SaveAs(path
                //    + FileUpload1.FileName);
                //Excel = FileUpload1.PostedFile.FileName;
                Excel=path + FileUpload1.FileName;

                Excel = path + System.IO.Path.GetFileName(FileUpload1.FileName);
                //FileUpload1.PostedFile.SaveAs(Excel);

                
               // Response.Write(Excel);
                DataTableToDB(Excel);
                //Label1.Text = "File uploaded!";
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
                Label1.Text = "上传失败！";
            }
        }
        else
        {
            Label1.Text = "文件格式不对.";
        }

    }
    public static void DataTableToDB(string Ex)
    {
        ASP.admin_bjproduct_list_aspx list = new admin_bjproduct_list_aspx();
        BJProductCategory CC = new BJProductCategory();
       
        DataTable dtExcel = ExcelToDataTable(Ex, "Sheet1");
        DT = CreateParentTable();
        try
        {
            for (int i = 0; i < dtExcel.Rows.Count; i++)
            {
                if (dtExcel.Rows[i][0].ToString() != null || dtExcel.Rows[i][0].ToString() != "")
                {

                    DR = DT.NewRow();
                    int PID = CategoryID(dtExcel.Rows[i][2].ToString());
                    CC.ID = PID;
                    WebManager.GetInfo(CC);
                    if (CC.Title == "")
                    {
                        continue;
                    }
                    else
                    {
                        DR["Title"] = dtExcel.Rows[i][1].ToString();
                        DR["ParentID"] = PID;
                        DR["tmp1"] = dtExcel.Rows[i][3].ToString();
                        DR["tmp2"] = float.Parse(dtExcel.Rows[i][4].ToString());
                        DR["tmp3"] = dtExcel.Rows[i][5].ToString();

                    }
                    DT.Rows.Add(DR);

                }
                else
                {
                    list.AlertMsg("该行数据为空！");
                }
            }
            DataRow[] Last = DT.Select("tmp2>0");
            LastDT = Last[0].Table;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
         

        using (System.Data.SqlClient.SqlBulkCopy bulkCopy =
                           new System.Data.SqlClient.SqlBulkCopy(@"Data Source=PC-200906271415\WS_HGO;Initial Catalog=F:\我的单子\200904\项目\ZYT\APP_DATA\BJYZT.MDF;User ID=sa;Password=123456;max pool size=512;pooling=true;"))
        {
            bulkCopy.DestinationTableName =
                "BJProduct";
            bulkCopy.ColumnMappings.Add("Title", "Title");
            bulkCopy.ColumnMappings.Add("ParentID", "ParentID");
            bulkCopy.ColumnMappings.Add("tmp1", "tmp1");
            bulkCopy.ColumnMappings.Add("tmp2", "tmp2");
            bulkCopy.ColumnMappings.Add("tmp3", "tmp3");

            try
            {
                bulkCopy.WriteToServer(LastDT);
                list.AlertMsg("数据导入成功！");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                DT.Clear();
            }
        }
    }
    //将Excel中的内容插入到DataTable中
    public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
    {
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties=Excel 8.0;";
        string strExcel = string.Format("select * from [{0}$]", strSheetName);
        DataSet ds = new DataSet();

        using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn))
        {
            conn.Open();
            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strExcel, strConn);
            adapter.Fill(ds);
            conn.Close();
            conn.Dispose();
        }

        return ds.Tables[0];
    }
    //获取父类ID
    public static int CategoryID(string category)
    {
        int CID = 1;
        string s = "cn";
        System.Data.SqlClient.SqlConnection Conn = new System.Data.SqlClient.SqlConnection();

        Conn.ConnectionString = @"Data Source=PC-200906271415\WS_HGO;Initial Catalog=F:\我的单子\200904\项目\ZYT\APP_DATA\BJYZT.MDF;User ID=sa;Password=123456;";
        Conn.Open();
        string sqlcategory = "insert into BJProductCategory (Title,ParentID,Version) values('" + category + "'," + 1 + ",'" + s + "')";
        System.Data.SqlClient.SqlCommand Com = new System.Data.SqlClient.SqlCommand(sqlcategory, Conn);

        int i = Com.ExecuteNonQuery();
        if (i > 0)
        {
            //取出最大值就是当前插入的ID
            string str = "select max(ID) AS ID from BJProductCategory";
            System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(str, Conn);
            CID = Convert.ToInt32(Cmd.ExecuteScalar());
        }
        Conn.Close();
        Conn.Dispose();
        return CID;


    }
    //Title,ParentID,tmp1,tmp2,tmp3,Version
    public static DataTable CreateParentTable()
    {
        ParentTable.Columns.Clear();
        DC = new DataColumn();
        DC.ColumnName = "Title";
        DC.DataType = System.Type.GetType("System.String");
        ParentTable.Columns.Add(DC);
        DC = new DataColumn();
        DC.ColumnName = "ParentID";
        DC.DataType = System.Type.GetType("System.Int32");
        ParentTable.Columns.Add(DC);
        DC = new DataColumn();
        DC.ColumnName = "tmp1";
        DC.DataType = System.Type.GetType("System.String");
        ParentTable.Columns.Add(DC);
        DC = new DataColumn();
        DC.ColumnName = "tmp2";
        DC.DataType = System.Type.GetType("System.String");
        ParentTable.Columns.Add(DC);
        DC = new DataColumn();
        DC.ColumnName = "tmp3";
        DC.DataType = System.Type.GetType("System.String");
        ParentTable.Columns.Add(DC);
        DC = new DataColumn();
        DC.ColumnName = "Version";
        DC.DataType = System.Type.GetType("System.String");
        ParentTable.Columns.Add(DC);
        return ParentTable;
    }

[lyvscf]

    protected void Batch_ServerClick(object sender, EventArgs e)
    {
        //获取完整路径
        string thefullname = this.uploadFile.PostedFile.FileName;

        if (thefullname == "")
        {
            this.Label3.Text = "请选择要上传得Excel文件！";
            return;
        }

        int fileLength = this.uploadFile.PostedFile.ContentLength;
        if (fileLength > 512000)
        {
            this.Label3.Text = "文件已超过500K，无法上传！";
            return;
        }
        FileInfo info = new FileInfo(thefullname);

        string fileExt = info.Extension;
        if (fileExt.ToLower() != ".xls")
        {
            this.Label3.Text = "不是Excel文件，请使用正确的文件格式！";
            return;
        }

        string uploadPath = Page.MapPath(@"uploadfile\report.xls");
        bool upSuccess = Upload(uploadPath);
        if (!upSuccess)
        {
            this.Label3.Text = "文件上传失败！";
            return;
        }
        DataTable table = GetExcelTable(uploadPath);
        if (table == null)
        {
            this.Label3.Text = "文件读取失败！";
            return;
        }
        else
        {
            // 执行导入
            PanIntegralSetManager pm = new PanIntegralSetManager();
            DefPanIntegralSet model = new DefPanIntegralSet();
            PTM.BLL.ProductSpec ps = new PTM.BLL.ProductSpec();
            IList<DefProductSpec> list = new List<DefProductSpec>();
            list = ps.QueryProSpecByCidx(HttpContext.Current.Session[PTM.Common.Constant.SS_CID].ToString());
            model.CID = list[0].Cid;
            DataTable dt = GetExcelTable(uploadPath);

            #region 转换为二维数组
            int colsCount = dt.Rows.Count;
            string[] psid_array = new string[colsCount];
            int[] integral_arry = new int[colsCount];
            string[] stime_array = new string[colsCount];
            string[] etime_array = new string[colsCount];
            int[] price_arry = new int[colsCount];
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                if (string.IsNullOrEmpty(row[0].ToString()))
                {
                    this.Label3.Text = "导入失败！产品编号不能为空";
                    return;
                }
                if (!string.IsNullOrEmpty(row[2].ToString()))
                {
                    Match m = Regex.Match(row[2].ToString(), @"^[1-9]\d*$");
                    if (!m.Success)
                    {
                        this.Label3.Text = "查询分值的格式输入有误";
                        return;
                    }
                }
                else
                {
                    this.Label3.Text = "您导入的Excel文件有为空的项，请检查！";
                    return;
                }
                if (!string.IsNullOrEmpty(row[3].ToString()) && !string.IsNullOrEmpty(row[4].ToString()))
                {
                    Match m = Regex.Match(row[3].ToString(), @"^(\d{1,4})(-|\/)(\d{1,2})\2(\d{1,2})");
                    Match n = Regex.Match(row[4].ToString(), @"^(\d{1,4})(-|\/)(\d{1,2})\2(\d{1,2})");
                    if (!m.Success || !n.Success)
                    {
                        this.Label3.Text = "开始时间结束时间格式不正确！";
                        return;
                    }
                }
                else
                {
                    this.Label3.Text = " 开始时间结束时间不能为空！";
                    return;
                }

                psid_array[i] = row[0].ToString();//第一列
                integral_arry[i] = Convert.ToInt32(row[2].ToString());//第三列
                stime_array[i] = row[3].ToString();//第四列
                etime_array[i] = row[4].ToString();//第四列
                price_arry[i] = Convert.ToInt32(row[5].ToString());//第五列

                // 在产品编号的前面补零
                switch (psid_array[i].Length)
                {
                    case 1:
                        psid_array[i] = "000" + psid_array[i];
                        break;
                    case 2:
                        psid_array[i] = "00" + psid_array[i];
                        break;
                    case 3:
                        psid_array[i] = "0" + psid_array[i];
                        break;
                }

                i++;
            }
            #endregion
            try
            {
                // 产品批量入库
                int[] j = pm.AddProductVolumes(psid_array, integral_arry, stime_array, etime_array, model, price_arry);

                for (int m = 0; m < j.Length; m++)
                {
                    int n = m + 1;
                    switch (j[m])
                    {
                        case -1:
                            this.Label3.Text += "第" + n + "条添加失败，是系统中没有的产品</br>";
                            break;
                        case 0:
                            this.Label3.Text += "第" + n + "条添加失败，请检查您的格式是否正确</br>";
                            break;
                        case 1:
                            this.Label3.Text += "第" + n + "条为已添加产品，系统自动执行了修改操作</br>";
                            break;
                        case 2:
                            this.Label3.Text += "第" + n + "条插入成功</br>";
                            break;
                    }
                }
            }
            catch (Exception ex)
            {

            }
        }
    }

    // 保存Excel
    private bool Upload(string uploadPath)
    {
        try
        {
            this.uploadFile.PostedFile.SaveAs(uploadPath);    //上传Excel并保存，在这里判断是否保存成功
            return true;
        }
        catch
        {
            return false;
        }
    }

给你吧

[ws_hgo]

用bcp

[lyvscf]

    // 读取Excel
    private DataTable GetExcelTable(string uploadPath)
    {
        DataSet ds = new DataSet();
        string Xls_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";//HDR为yes 则第一数据行为列名,为no 则自动为列加列名F1  F2  F3
        OleDbConnection Conn = new OleDbConnection(Xls_ConnStr);
        try
        {
            Conn.Open();
            string sql_str = "select * from [Sheet1$]";
            OleDbDataAdapter da = new OleDbDataAdapter(sql_str, Conn);
            da.Fill(ds, "excel_data");
            Conn.Close();
        }
        catch
        {
            if (Conn.State == ConnectionState.Open)
            {
                Conn.Close();
            }
            return null;
        }
        finally
        {
            Conn.Dispose();
        }

        if (ds == null)
        {
            return null;
        }

        if (ds.Tables.Count < 1)
        {
            return null;
        }

        return ds.Tables[0];
    }

[wx23990915]

public DataSet GetDataFrowExcel(HtmlInputFile fileUp) 
        { 

            string filetype = fileUp.Value.ToString().Substring(m); 
            if (filetype != ".xls" && filetype != ".XLS") 
            { 
                Label1.Text = "导入文件格式不正确!"; 
                return; 
            } 
            string filestr = Server.MapPath("~/temp/" + Guid.NewGuid().ToString() + ".xls"); 
            System.IO.FileInfo fileInfo = new FileInfo(filestr); 
            if (fileInfo != null) 
            { 
                fileInfo.Delete(); 
            } 

            fileUp.PostedFile.SaveAs(filestr); 
            DataSet dataSet = new DataSet(); 
            string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileInfo.FullName + ";Extended Properties=Excel 8.0"; 

            OleDbConnection oleDbConn = new OleDbConnection(connExcel); 
            oleDbConn.Open(); 

            //获取excel表 

            DataTable dataTable = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 

            //获取sheet名，其中[0][1]...[N]: 按名称排列的表单元素 
            string tableName = dataTable.Rows[0][2].ToString().Trim(); 
            tableName = "[" + tableName.Replace("'", "") + "]"; 

            //利用SQL语句从Excel文件里获取数据 

            OleDbDataAdapter oleAdapter = new OleDbDataAdapter("select StudentNO ,KGTScore,CourseName  from " + tableName, connExcel); 
            oleAdapter.Fill(dataSet); 
            oleDbConn.Close(); 
            oleDbConn.Dispose(); 
            oleAdapter.Dispose(); 
            return dataSet; 
        }

[Hertz_liu]

楼上的回答思路都是这样，但是个人觉得有些小地方需要注意

比如读取Excel第一张表的记录，楼上有些是用“select * from [Sheet1$]”，但是个人觉得不妥

楼主虽然说每个Excel是读取第一张表的记录，但是并没有说第一张表的名称一定是Sheet1，如果我把Sheet1重新命名过后，上面的那条SQL就出错了

所以在这一块，应该先取出第一张Sheet表的名称，然后再使用上面的Select语句

[messi_yang]

來學習了···頂哈·
我最近也在做類似的·

[fastrain]

大家继续啊，有没有更好的

[terry100000]

bcp是每个数据库应用都有的工具么？

[yxzbest]

楼上的大多数代码都有自己的其他类，也没有给出，想借鉴都不行

[fastrain]

[Quote=引用 11 楼 yxzbest 的回复:]
楼上的大多数代码都有自己的其他类，也没有给出，想借鉴都不行
[/Quote]

同感，所以继续顶

[yanm7788]

网上挺多地,帮顶个吧

[lyvscf]

要求不要太高 伙计 这代码都出来了
还写不出来 就怪不得别人了
唯一没有写出的 是遍历你的 checkbox 这个你自己遍历一下转换成 datatable 不就完事ok了！

[xlj_2008]

ding~~~~````