editprice_file_2.aspx.cs

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

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Drawing;
using System.IO;
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_File_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 strPedsRowCount = Request.QueryString["rows"];
            
            hidVersion.Value = strVersion;              // 价格版本号
            hidModels.Value = strModels;                // 车系数据列
            hidPEDSRowCount.Value = strPedsRowCount;    // PEDS系统中对应的零件数

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

            string strSQL_Filter = "SELECT Filter FROM Make WHERE MakeCode = (SELECT DISTINCT MakeCode FROM Model WHERE ModelCode LIKE '" + strModels.Split(',')[0] + "')";
            string strSrcFilter = dbConn.QueryString(strSQL_Filter);
            string[] aryFilters = strSrcFilter.Split(',');

            string strFilter = null;
            for (int i = 0; i < aryFilters.Length; i++)
            {
                char cFilterCharecter = (char)Convert.ToInt32(aryFilters[i]);
                strFilter += cFilterCharecter;
            }
            txtFilter.Text = strFilter;

            BindPriceData();
        }
    }

    /// <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();
    }

    /// <summary>
    /// 查询
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnQuery_Click(object sender, EventArgs e)
    {
        BindPriceData();
    }

    /// <summary>
    /// 开始自动匹配
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnAutoMatch_Click(object sender, EventArgs e)
    {
        string strSQL_Match = "UPDATE " + hidTableName.Value +
                              " SET 单价=(SELECT 单价 FROM tempPrice t WHERE " + hidTableName.Value + ".零件编号=t.零件编号),导入标记=1 WHERE" +
                              " EXISTS (SELECT 1 FROM tempPrice t WHERE t.零件编号=" + hidTableName.Value + ".零件编号)";
        string strSQL_Match2 = "UPDATE tempPrice SET 导入标记=1 WHERE 零件编号 IN (SELECT " + hidTableName.Value + ".零件编号 FROM tempPrice t INNER JOIN " + hidTableName.Value + " ON t.零件编号=" + hidTableName.Value + ".零件编号)";
        string strSQL_FileRow = "SELECT count(*) FROM tempPrice";
        DBConnector dbConn = new DBConnector();
        int iRow = dbConn.Execute(strSQL_Match);
        int iRow2 = dbConn.Execute(strSQL_Match2);
        int iFileRowCount = dbConn.QueryIntValue(strSQL_FileRow);

        //////////////////////////////////////////////////////////////////////////////////////////////////////////////
        // TODO : 将未匹配价格写入文件
        string strSQL_UnMatched = "SELECT ID,原始编号,零件编号,单价 FROM tempPrice WHERE 导入标记=0";
        DataSet dsUnMatched = dbConn.Query(strSQL_UnMatched);

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

        string strFileName = hidTableName.Value + "_Edit.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();
        //////////////////////////////////////////////////////////////////////////////////////////////////////////////

        BindPriceData();
    }

    #region 用户定义函数

    #region 过滤符号的处理
    protected void btnFilter_Click(object sender, EventArgs e)
    {
        if (plFilter.Visible)
            btnFilter.Text = "显示";
        else
            btnFilter.Text = "隐藏";

        plFilter.Visible = !plFilter.Visible;
    }

    protected void btnFilterMark_1_Click(object sender, EventArgs e)
    {
        int iIndex = txtFilter.Text.IndexOf(btnFilterMark_1.Text);
        if (iIndex < 0)
        {
            txtFilter.Text += btnFilterMark_1.Text + ",";
        }
        else
        {
            txtFilter.Text = txtFilter.Text.Remove(iIndex, 2);
        }

    }
    protected void btnFilterMark_2_Click(object sender, EventArgs e)
    {
        int iIndex = txtFilter.Text.IndexOf(btnFilterMark_2.Text);
        if (iIndex < 0)
        {
            txtFilter.Text += btnFilterMark_2.Text + ",";
        }
        else
        {
            txtFilter.Text = txtFilter.Text.Remove(iIndex, 2);
        }
    }
    protected void btnFilterMark_3_Click(object sender, EventArgs e)
    {
        int iIndex = txtFilter.Text.IndexOf(btnFilterMark_3.Text);
        if (iIndex < 0)
        {
            txtFilter.Text += btnFilterMark_3.Text + ",";
        }
        else
        {
            txtFilter.Text = txtFilter.Text.Remove(iIndex, 2);
        }
    }
    protected void btnFilterMark_4_Click(object sender, EventArgs e)
    {
        int iIndex = txtFilter.Text.IndexOf(btnFilterMark_4.Text);
        if (iIndex < 0)
        {
            txtFilter.Text += btnFilterMark_4.Text + ",";
        }
        else
        {
            txtFilter.Text = txtFilter.Text.Remove(iIndex, 2);
        }
    }
    protected void btnFilterMark_5_Click(object sender, EventArgs e)
    {
        int iIndex = txtFilter.Text.IndexOf(btnFilterMark_5.Text);
        if (iIndex < 0)
        {
            txtFilter.Text += btnFilterMark_5.Text + ",";
        }
        else
        {
            txtFilter.Text = txtFilter.Text.Remove(iIndex, 2);
        }
    }
    protected void btnFilterMark_6_Click(object sender, EventArgs e)
    {
        int iIndex = txtFilter.Text.IndexOf(btnFilterMark_6.Text);
        if (iIndex < 0)
        {
            txtFilter.Text += btnFilterMark_6.Text + ",";
        }
        else
        {
            txtFilter.Text = txtFilter.Text.Remove(iIndex, 2);
        }
    }
    protected void btnFilterMark_7_Click(object sender, EventArgs e)
    {
        int iIndex = txtFilter.Text.IndexOf(btnFilterMark_7.Text);
        if (iIndex < 0)
        {
            txtFilter.Text += btnFilterMark_7.Text + ",";
        }
        else
        {
            txtFilter.Text = txtFilter.Text.Remove(iIndex, 2);
        }
    }
    protected void btnFilterMark_8_Click(object sender, EventArgs e)
    {
        int iIndex = txtFilter.Text.IndexOf(btnFilterMark_8.Text);
        if (iIndex < 0)
        {
            txtFilter.Text += btnFilterMark_8.Text + ",";
        }
        else
        {
            txtFilter.Text = txtFilter.Text.Remove(iIndex, 2);
        }
    }
    protected void btnFilterMark_9_Click(object sender, EventArgs e)
    {
        int iIndex = txtFilter.Text.IndexOf(btnFilterMark_9.Text);
        if (iIndex < 0)
        {
            txtFilter.Text += btnFilterMark_9.Text + ",";
        }
        else
        {
            txtFilter.Text = txtFilter.Text.Remove(iIndex, 2);
        }
    }
    protected void btnFilterMark_10_Click(object sender, EventArgs e)
    {
        int iIndex = txtFilter.Text.IndexOf(btnFilterMark_10.Text);
        if (iIndex < 0)
        {
            txtFilter.Text += btnFilterMark_10.Text + ",";
        }
        else
        {
            txtFilter.Text = txtFilter.Text.Remove(iIndex, 2);
        }
    }
    protected void btnFilterMark_11_Click(object sender, EventArgs e)
    {
        int iIndex = txtFilter.Text.IndexOf(btnFilterMark_11.Text);
        if (iIndex < 0)
        {
            txtFilter.Text += btnFilterMark_11.Text + ",";
        }
        else
        {
            txtFilter.Text = txtFilter.Text.Remove(iIndex, 2);
        }
    }
    protected void btnFilterMark_12_Click(object sender, EventArgs e)
    {
        int iIndex = txtFilter.Text.IndexOf(btnFilterMark_12.Text);
        if (iIndex < 0)
        {
            txtFilter.Text += btnFilterMark_12.Text + ",";
        }
        else
        {
            txtFilter.Text = txtFilter.Text.Remove(iIndex, 2);
        }

    }
    protected void btnFilterMark_13_Click(object sender, EventArgs e)
    {
        int iIndex = txtFilter.Text.IndexOf(btnFilterMark_13.Text);
        if (iIndex < 0)
        {
            txtFilter.Text += btnFilterMark_13.Text + ",";
        }
        else
        {
            txtFilter.Text = txtFilter.Text.Remove(iIndex, 2);
        }
    }
    #endregion
    /// <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

    protected void gvPEDSPrice_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            int iImportFlag = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "导入标记"));
            if (iImportFlag == 1)
                e.Row.BackColor = Color.Yellow;
        }
    }
}