根据导入Excel文件批量修改Oracle数据库中订单信息

最新推荐文章于 2021-04-05 11:50:43 发布

zkvistor

最新推荐文章于 2021-04-05 11:50:43 发布

阅读量2k

点赞数

分类专栏： ASP.NET 文章标签： excel oracle 数据库 dataset string file

本文链接：https://blog.csdn.net/zkvistor/article/details/4470507

版权

ASP.NET 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

Excel文件里是返回的订单相关信息，如:运单号，状态，发货时间......... 页面布局如下：

页面有个html input上传控件还有个修改按钮

点击浏览，选择要导入的文件然后点修改就可以批量修改

思路就是把Excel表当做数据源进行读取,Excel中的数据保存到DataSet中

using System.Data.OleDb;

。。。。。

/// <summary>
    /// 返回DataSet数据集
    /// </summary>
    /// <param name="fileName"></param>
    /// <param name="sql"></param>
    /// <param name="srcTable"></param>
    /// <returns></returns>
    public static DataSet ReturnDataSet(string fileName)
    {
        DataSet ds;
        try
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1/"";
            OleDbConnection conn = new OleDbConnection(strConn);
            ds = new DataSet();
            conn.Open();
            DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            string[] TableNames = new string[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {

                TableNames[i] = dt.Rows[i]["TABLE_NAME"].ToString();
                if (TableNames[i] != "深邮发$_" && TableNames[i] != "深宅发$_" && TableNames[i] != "深邮拒$_" && TableNames[i] != "深宅拒$_")
                {
                    string sql = "select * from[" + TableNames[i] + "]"+"where 订单号 not like '%[a-z][A-Z]%'and len(订单号)<=7 ";
                    OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
                    DataTable dt2 = new DataTable();
                    ds.Tables.Add(dt2);
                    adapter.Fill(ds);
                }
            }
            conn.Close();

        }
        catch (SqlException on)
        {
            throw on;
        }
       return ds;

btn_update click事件代码

/// <summary>
    /// 根据上传的Excel文件批量修改
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btn_update_Click(object sender, EventArgs e)
    {
        //判断FileUpload控件是否包含文件
        if (File1.Value != "")
        {
            int fileSize = File1.PostedFile.ContentLength;
            string fullName=File1.PostedFile.FileName;
            string fileName = fullName.Substring(fullName.LastIndexOf("//") + 1);
            string type=fileName .Substring (fileName.LastIndexOf (".")+1);

            if(type=="xls" || type=="xlsx")
            {
                if (fileSize <= 1024*1024*4)
                {
                    this.File1.PostedFile.SaveAs(Server.MapPath("File") + "//" + fileName);
                    string name = Server.MapPath("File") + "//" + fileName;
                    DataSet ds = Help.ReturnDataSet(name);
                    foreach (DataTable dt in ds.Tables)
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            string orderID = row["订单号"].ToString();
                            string mailID = row["快递单号"].ToString();
                            if (orderID != "" && mailID != "")
                            {
                                Regex rx = new Regex(("^[a-zA-Z][a-z0-9A-Z]"));

bool isMatchMailID = rx.IsMatch(mailID);
bool IsExist = Help.IsExist(orderID);

                                if (IsExist)
                                {
                                    bool flag;
                                    if (dt.Columns.Contains("出库日期") || dt.Columns.Contains("回货日期"))
                                    {
                                        if (isMatchMailID)
                                            flag = Help.IsUpdateOrderHist(orderID, 6, mailID, "01");
                                        else
                                            flag = Help.IsUpdateOrderHist(orderID, 6, mailID, "03");
                                    }
                                    else
                                    {
                                        if (isMatchMailID)
                                            flag = Help.IsUpdateOrderHist(orderID, 9, mailID, "01");
                                        else
                                            flag = Help.IsUpdateOrderHist(orderID, 9, mailID, "03");
                                    }
                                    if (!flag)
                                    {
                                        ClientScript.RegisterStartupScript(Page.GetType(), "onclick", "<script>alert('修改失败!')</script>");
                                        break;
                                    }
                                }
                                else
                                    continue;
                            }
                        }
                    }
                }
                else
                    ClientScript.RegisterStartupScript(Page.GetType(), "onclick", "<Script>alert('文件大小不能超过4M!')</Script>");
            }
            else
            {
                ClientScript.RegisterStartupScript(Page.GetType(), "onclick", "<Script>alert('类型错误!')</Script>");
            }
            BindGridView(Help.Select_OrderHist, null);
        }
        else
        {
            ClientScript.RegisterStartupScript(Page.GetType(), "onclick", "<Script>alert('上传文件不能为空!')</Script>");

        }