editprice_price_2.aspx.cs

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

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Drawing;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class EditPrice_Price_2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            string strVersion = Request.QueryString["version"];
            string strModels = Request.QueryString["models"];
            string strItems = Request.QueryString["items"];

            hidVersion.Value = strVersion;
            hidModels.Value = strModels;
            hidItems.Value = strItems;

            string strSQL_TableName = "SELECT 数据表名称 FROM priceInfo WHERE 价格版本号 LIKE '" + strVersion + "'";
            DBConnector dbConn = new DBConnector();
            hidTableName.Value = dbConn.QueryString(strSQL_TableName);

            BindPriceData();
        }
    }

    /// <summary>
    /// 绑定数据对象
    /// </summary>
    private void BindPriceData()
    {
        string[] aryVersons = hidItems.Value.Split(',');
        string[] aryTables = new string[aryVersons.Length];     // 数据表名称

        ////////////////////////////////////////////////////////////////////////////////////////////
        // 查询价格数据表
        string strVersionLst = "(";
        string strSQL_Tables = "SELECT 价格版本号,数据表名称 FROM priceInfo WHERE 价格版本号 IN ";
        for (int i = 0; i < aryVersons.Length; i++)
        {
            aryVersons[i] = "'" + aryVersons[i] + "',";
            strVersionLst += aryVersons[i];
        }
        strVersionLst = strVersionLst.Remove(strVersionLst.Length - 1, 1);
        strSQL_Tables += strVersionLst + ")";
        ////////////////////////////////////////////////////////////////////////////////////////////


        ////////////////////////////////////////////////////////////////////////////////////////////
        // 查询车系列表
        string[] aryModels = hidModels.Value.Split(',');
        string strModelLst = "(";
        for (int i = 0; i < aryModels.Length; i++)
        {
            aryModels[i] = "'" + aryModels[i] + "',";
            strModelLst += aryModels[i];
        }
        strModelLst = strModelLst.Remove(strModelLst.Length - 1, 1);
        strModelLst += ")";
        ////////////////////////////////////////////////////////////////////////////////////////////

        DBConnector dbConn = new DBConnector();

        DataSet dsTables = dbConn.Query(strSQL_Tables);

        string strSQL_PrcItems = "SELECT PartNum as 零件编号,";
        for (int i = 0; i < aryTables.Length; i++)
        {
            aryTables[i] = dsTables.Tables[0].Rows[i]["数据表名称"].ToString();
            strSQL_PrcItems += aryTables[i] + ".单价,";
        }
        strSQL_PrcItems += (hidTableName.Value + ".单价 as 导入价格,Part.Remark as 备注 FROM Part ");

        for (int i = 0; i < aryTables.Length; i++)
        {
            strSQL_PrcItems += ("INNER JOIN " + aryTables[i] + " ON " + aryTables[i] + ".零件编号=Part.PartNum ");
        }
        strSQL_PrcItems += "INNER JOIN " + hidTableName.Value + " ON " + hidTableName.Value + ".零件编号=Part.PartNum ";
        strSQL_PrcItems += "WHERE Part.ModelCode IN " + strModelLst;

        if (null != txtPartNum.Text && string.Empty != txtPartNum.Text)
        {
            string strWhere = txtPartNum.Text;
            if (chbBlur.Checked)
            {
                strWhere = "%" + strWhere + "%";
            }
            strSQL_PrcItems += " AND Part.PartNum LIKE '" + strWhere + "'";
        }

        if ( "已导入" == ddlQueryRange.SelectedValue)
        {
            strSQL_PrcItems += " AND " + hidTableName.Value + ".导入标记=1";
        }
        else if ("未导入" == ddlQueryRange.SelectedValue)
        {
            strSQL_PrcItems += " AND " + hidTableName.Value + ".导入标记=0";
        }

        DataSet dsPriceData = dbConn.Query(strSQL_PrcItems);

        gvPriceItems.DataSource = dsPriceData;
        gvPriceItems.DataBind();

        // gvPriceItems.Columns[0].ItemStyle.HorizontalAlign = HorizontalAlign.Left;
    }

    /// <summary>
    /// 分页操作
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvPriceItems_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView theGrid = this.gvPriceItems;
        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();
    }

    protected void btnQuery_Click(object sender, EventArgs e)
    {
        BindPriceData();
    }

    /// <summary>
    /// 数据绑定
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvPriceItems_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            float fPrice = Convert.ToSingle(DataBinder.Eval(e.Row.DataItem, "导入价格"));
            if (fPrice > 0)
                e.Row.BackColor = Color.Yellow;

            for (int i = 0; i < e.Row.Cells.Count; i++)
            {
                if ("-1" == e.Row.Cells[i].Text)
                {
                    e.Row.Cells[i].Text = "无";
                }
            }
        }
    }

    protected void btnMatch_Click(object sender, EventArgs e)
    {
        DBConnector dbConn = new DBConnector();

        string[] aryItems = hidItems.Value.Split(',');
        if (rbMax.Checked)
        {
            for (int z = 0; z < gvPriceItems.PageCount; z++)
            {
                gvPriceItems.PageIndex = z;
                BindPriceData();
                for (int i = 0; i < gvPriceItems.Rows.Count; i++)
                {
                    float fMax = float.MinValue;
                    for (int j = 0; j < aryItems.Length; j++)
                    {
                        float fPrice;
                        if ("无" == gvPriceItems.Rows[i].Cells[j + 1].Text)
                            fPrice = -1;
                        else
                            fPrice = Convert.ToSingle(gvPriceItems.Rows[i].Cells[j + 1].Text);
                        fMax = fPrice < 0 ? fMax : (fPrice < fMax ? fMax : fPrice);
                    }

                    if (fMax > 0)
                    {
                        string strSQL_Update = "UPDATE " + hidTableName.Value + " SET 导入标记=1,单价=" + fMax +
                                               " WHERE 零件编号 LIKE '" + gvPriceItems.Rows[i].Cells[0].Text.ToString() + "'";
                        dbConn.Execute(strSQL_Update);
                    }
                }
            }
        }
        else if (rbMin.Checked)
        {
            for (int z = 0; z < gvPriceItems.PageCount; z++)
            {
                gvPriceItems.PageIndex = z;
                BindPriceData();
                for (int i = 0; i < gvPriceItems.Rows.Count; i++)
                {
                    float fMin = float.MaxValue;
                    for (int j = 0; j < aryItems.Length; j++)
                    {
                        float fPrice;
                        if ("无" == gvPriceItems.Rows[i].Cells[j + 1].Text)
                            fPrice = -1;
                        else
                            fPrice = Convert.ToSingle(gvPriceItems.Rows[i].Cells[j + 1].Text);
                        fMin = fPrice < 0 ? fMin : (fPrice > fMin ? fMin : fPrice);
                    }

                    if (fMin < float.MaxValue - 1)
                    {
                        string strSQL_Update = "UPDATE " + hidTableName.Value + " SET 导入标记=1,单价=" + fMin +
                                           " WHERE 零件编号 LIKE '" + gvPriceItems.Rows[i].Cells[0].Text.ToString() + "'";
                        dbConn.Execute(strSQL_Update);
                    }
                }
            }
        }
        else if (rbAvg.Checked)
        {
            for (int z = 0; z < gvPriceItems.PageCount; z++)
            {
                gvPriceItems.PageIndex = z;
                BindPriceData();
                for (int i = 0; i < gvPriceItems.Rows.Count; i++)
                {
                    float fSum = 0;
                    int iCount = 0;
                    for (int j = 0; j < aryItems.Length; j++)
                    {
                        float fPrice;
                        if ("无" == gvPriceItems.Rows[i].Cells[j + 1].Text)
                            fPrice = -1;
                        else
                            fPrice = Convert.ToSingle(gvPriceItems.Rows[i].Cells[j + 1].Text);
                        if (fPrice > 0)
                        {
                            fSum += fPrice;
                            iCount++;
                        }
                    }
                    string strSQL_Update = "UPDATE " + hidTableName.Value + " SET 导入标记=1,单价=" + fSum / iCount +
                                           " WHERE 零件编号 LIKE '" + gvPriceItems.Rows[i].Cells[0].Text.ToString() + "'";
                    dbConn.Execute(strSQL_Update);
                }
            }
        }
    }
   
}