importprice_file_2.aspx.cs

简单的Web平台。能够读取Excel文件

        string strSQL_MinIndex = "SELECT min(ID) FROM tempPrice WHERE 导入标记=0";
        int iMinIndex = dbConn.QueryIntValue( strSQL_MinIndex );

        string strFileName = hidTableName.Value + "_Import.txt";
        StreamWriter sw = new StreamWriter("c:\\" + strFileName);
        sw.WriteLine("序号\t零件编号\t零件价格");
        for (int i = 0; i < dsUnMatched.Tables[0].Rows.Count; i++ )
        {         
            string line = "" + (Convert.ToInt32( dsUnMatched.Tables[0].Rows[i]["ID"].ToString() ) - iMinIndex);
            line += "\t";
            line += dsUnMatched.Tables[0].Rows[i]["原始编号"].ToString();
            line += "\t";
            line += dsUnMatched.Tables[0].Rows[i]["单价"].ToString();
            sw.WriteLine(line);                        
        }
        sw.Close();
        //////////////////////////////////////////////////////////////////////////////////////////////////////////////

        string strMsg = "共匹配价格" + iRow + "条;PEDS中共有数据" + hidPEDSRowCount.Value + "条;匹配率为" + string.Format("{0:F}", 100 * iRow / Convert.ToDouble(hidPEDSRowCount.Value)) + "%" +
                        "配件商共有数据" + iFileRowCount + "条;匹配率为" + string.Format("{0:F}", 100 * iRow / Convert.ToDouble(iFileRowCount)) + "%";

        MessageBox.Show( this, strMsg );

        BindPriceData();
    }

    protected void btnBackMatch_Click(object sender, EventArgs e)
    {
        string strSQL_Cancel = "UPDATE " + hidTableName.Value +
                               " SET 单价=-1,导入标记=0 WHERE" +
                               " EXISTS (SELECT 1 FROM tempPrice t WHERE t.零件编号=" + hidTableName.Value + ".零件编号)";
        DBConnector dbConn = new DBConnector();
        int iRow = dbConn.Execute(strSQL_Cancel);

        if (iRow > 0)
        {
            MessageBox.Show(this, "已取消匹配信息" + iRow + "条");
            BindPriceData();
        }
        else
        {
            MessageBox.Show(this, "取消匹配失败");
        }
        
    }

    #region 用户定义函数
    /*
    /// <summary>
    /// 绑定配件商价格数据
    /// </summary>
    private void BindProviderData()
    {
        string strPartNum = txtPartNum.Text;

        string strSQL_ProvData = "SELECT * FROM tempPrice WHERE 零件编号 LIKE '";
        if (null == strPartNum || string.Empty == strPartNum)
        {
            strSQL_ProvData += "%%'";
        }
        else
        {
            if (chbBlur.Checked)
            {
                string strCondition = "%" + txtPartNum.Text + "%'";
                strSQL_ProvData += strCondition;
            }
            else
            {
                strSQL_ProvData += txtPartNum.Text + "'";
            }
        }

        if ("-1" != ddlQueryRange.SelectedValue)
        {
            if ("未导入" == ddlQueryRange.SelectedValue)
                strSQL_ProvData += "AND 导入标记=0";
            else
                strSQL_ProvData += "AND 导入标记=1";
        }

        DBConnector dbConn = new DBConnector();
        DataSet dsProvPrice = dbConn.Query(strSQL_ProvData);
        gvFilePrice.DataSource = dsProvPrice;
        gvFilePrice.DataBind();
    }

    /// <summary>
    /// 绑定原厂价格数据
    /// </summary>
    private void BindPEDSData()
    {
        string strPartNum = txtPartNum.Text;

        string[] aryModels = hidModels.Value.Split(',');
        string strQueryCondition = "(";
        for (int i = 0; i < aryModels.Length; i++)
        {
            aryModels[i] = "'" + aryModels[i] + "',";
            strQueryCondition += aryModels[i];
        }
        strQueryCondition = strQueryCondition.Remove(strQueryCondition.Length - 1, 1);
        strQueryCondition += ")";

        string strSQL_PEDS = "SELECT * FROM Part INNER JOIN Model ON Part.ModelCode=Model.ModelCode INNER JOIN Make ON Model.MakeCode=Make.MakeCode INNER JOIN " + hidTableName.Value + " as t ON t.零件编号=Part.PartNum WHERE Part.ModelCode IN " + strQueryCondition;
        if (null != strPartNum && string.Empty != strPartNum)
        {
            if (chbBlur.Checked)
            {
                strSQL_PEDS += " AND PartNum LIKE '%" + txtPartNum.Text + "%'";
            }
            else
            {
                strSQL_PEDS += " AND PartNum LIKE '" + txtPartNum.Text + "'";
            }
        }

        /*
        if ("-1" != ddlQueryRange.SelectedValue)
        {
            if ("未导入" == ddlQueryRange.SelectedValue)
                strSQL_ProvData += "AND 导入标记=0";
            else
                strSQL_ProvData += "AND 导入标记=1";
        }
        * /

        DBConnector dbConn = new DBConnector();
        DataSet dsPEDSPrice = dbConn.Query(strSQL_PEDS);
        gvPEDSPrice.DataSource = dsPEDSPrice;
        gvPEDSPrice.DataBind();
    }
    */
    /// <summary>
    /// 绑定列表数据
    /// </summary>
    private void BindPriceData()
    {
        string[] aryModels = hidModels.Value.Split(',');
        string strModels = "(";
        for (int i=0; i<aryModels.Length; i++)
        {
            aryModels[i] = "'" + aryModels[i] + "',";
            strModels += aryModels[i];
        }
        strModels = strModels.Remove( strModels.Length-1, 1 );
        strModels += ")";

        string strSQL_Price = "SELECT p.ID,p.零件编号,tempPrice.单价,Part.SysPrice,MakeName,ModelName,Remark,p.导入标记 FROM " + hidTableName.Value + " p INNER JOIN Part ON p.零件编号=Part.PartNum " +
                                                                                                "LEFT JOIN tempPrice ON p.零件编号=tempPrice.零件编号 " +
                                                                                                "INNER JOIN Make ON p.厂牌编号=Make.MakeCode " +
                                                                                                "INNER JOIN Model ON p.所属车系=Model.ModelCode " +                                                                                                
                              "WHERE p.所属车系 IN " + strModels;

        if ( "0" == ddlQueryRange.SelectedValue )       // 选择已匹配上
        {
            strSQL_Price += " AND p.导入标记=1";
        }
        else if ( "1" == ddlQueryRange.SelectedValue )  // 选择未匹配上
        {
            strSQL_Price += " AND p.导入标记=0";
        }
        
        string strPartNum = txtPartNum.Text;
        strPartNum = strPartNum.Trim();
        if ( string.Empty!=txtPartNum.Text )
        {
            if ( chbBlur.Checked )
            {
                strPartNum = "%" + strPartNum + "%";
            }
            strSQL_Price += " AND p.零件编号 LIKE '" + strPartNum + "'";
        }
          
        DBConnector dbConn = new DBConnector();
        DataSet dsPriceInfo = dbConn.Query( strSQL_Price );

        gvPEDSPrice.DataSource = dsPriceInfo;
        gvPEDSPrice.DataBind();
    }               

    #endregion

    /// <summary>
    /// 显示过滤符号设置对话框
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnFilter_Click(object sender, EventArgs e)
    {
        plFilter.Visible = !plFilter.Visible;

        if (plFilter.Visible)
            btnFilter.Text = "隐藏";
        else
            btnFilter.Text = "显示";
    }

    /// <summary>
    /// 编辑价格
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvPEDSPrice_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gvPEDSPrice.EditIndex = e.NewEditIndex;
        BindPriceData();
    }

    /// <summary>
    /// 取消编辑
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvPEDSPrice_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gvPEDSPrice.EditIndex = -1;
        BindPriceData();
    }

    /// <summary>
    /// 更新价格
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvPEDSPrice_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        string id = gvPEDSPrice.DataKeys[e.RowIndex].Values[0].ToString();
        string strPrice = ((TextBox)gvPEDSPrice.Rows[e.RowIndex].FindControl("txtEditPrice")).Text;
        string strSQL_Update = "UPDATE tempPrice SET 单价=" + strPrice + " WHERE 零件编号=(SELECT 零件编号 FROM "+hidTableName.Value+" WHERE ID="+id+")";

        DBConnector dbConn = new DBConnector();
        dbConn.Execute(strSQL_Update);

        gvPEDSPrice.EditIndex = -1;
        BindPriceData();
    }

    /// <summary>
    /// 分页处理
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvPEDSPrice_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView theGrid = this.gvPEDSPrice;
        int newPageIndex = 0;
        if (-2 == e.NewPageIndex)
        {
            TextBox txtNewPageIndex = null;
            GridViewRow pagerRow = theGrid.BottomPagerRow;
            if (null != pagerRow)
            {
                txtNewPageIndex = pagerRow.FindControl("txtNewPageIndex") as TextBox;   // refer to the TextBox with the NewPageIndex value
            }

            if (null != txtNewPageIndex)
            {
                newPageIndex = int.Parse(txtNewPageIndex.Text) - 1; // get the NewPageIndex
            }
        }
        else
        {
            newPageIndex = e.NewPageIndex;
        }

        newPageIndex = newPageIndex < 0 ? 0 : newPageIndex;
        newPageIndex = newPageIndex >= theGrid.PageCount ? theGrid.PageCount - 1 : newPageIndex;

        theGrid.PageIndex = newPageIndex;

        BindPriceData();
    }
    
}