importprice_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 ImportPrice_File2 : 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();
            /* BindProviderData(); */
            /* BindPEDSData(); */
        }
    }

    #region 过滤符号
    protected void btnFilterMark_1_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting(strMark);
    }
    protected void btnFilterMark_2_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting(strMark);
    }
    protected void btnFilterMark_3_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting(strMark);
    }
    protected void btnFilterMark_4_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting(strMark);
    }
    protected void btnFilterMark_5_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting(strMark);
    }
    protected void btnFilterMark_6_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting(strMark);
    }
    protected void btnFilterMark_7_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting(strMark);
    }
    protected void btnFilterMark_8_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting(strMark);
    }
    protected void btnFilterMark_9_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting(strMark);
    }
    protected void btnFilterMark_10_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting(strMark);
    }
    protected void btnFilterMark_11_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting(strMark);
    }
    protected void btnFilterMark_12_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting(strMark);
    }
    protected void btnFilterMark_13_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting(strMark);
    }
    protected void btnFilterMark14_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting( strMark );
    }
    protected void btnFilterMark15_Click(object sender, EventArgs e)
    {
        string strMark = ((Button)sender).Text;
        ManageFilterSting( strMark );
    }

    /// <summary>
    /// 在过滤文本框中添加或删除过滤符号
    /// </summary>
    /// <param name="strMark"></param>
    private void ManageFilterSting(string strMark)
    {
        int iIndex = txtFilter.Text.IndexOf( strMark );
        if (iIndex < 0)
            txtFilter.Text += strMark;
        else
            txtFilter.Text = txtFilter.Text.Remove(iIndex, 1);
    }
    #endregion

    /*
    /// <summary>
    /// 配件商价格数据分页
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvFilePrice_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView theGrid = this.gvFilePrice;
        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;

        BindProviderData();
    }
    */
    /*
    /// <summary>
    /// PEDS价格数据分页
    /// </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;
        
        BindPEDSData();
    }
    */
    /// <summary>
    /// 查询
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnQuery_Click(object sender, EventArgs e)
    {
        /*if ("-1" == ddlQueryObj.SelectedValue)
        {
            BindProviderData();
            BindPEDSData();
        }
        else if ( "0" == ddlQueryObj.SelectedValue )        // 配件商价格
        {
            BindProviderData();
        }
        else if ( "1" == ddlQueryObj.SelectedValue )        // PEDS价格
        {
            BindPEDSData();
        }*/
        BindPriceData();
    }


    /// <summary>
    /// 更新单元格背景颜色
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    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;
        }
    }

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