editprice.aspx.cs

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

        txtHeader3.Visible = false;
        txtHeader4.Visible = false;
        txtHeader5.Visible = false;
        txtHeader6.Visible = false;
        txtHeader7.Visible = false;
        txtHeader8.Visible = false;
        txtHeader9.Visible = false;

        ddlHeader1.Visible = false;
        ddlHeader2.Visible = false;
        ddlHeader3.Visible = false;
        ddlHeader4.Visible = false;
        ddlHeader5.Visible = false;
        ddlHeader6.Visible = false;
        ddlHeader7.Visible = false;
        ddlHeader8.Visible = false;
        ddlHeader9.Visible = false;

        switch (dsXls.Tables[0].Columns.Count)
        {
            case 9:
                txtHeader9.Text = dsXls.Tables[0].Columns[8].ToString();
                txtHeader9.Visible = true;
                ddlHeader9.Visible = true;
                goto case 8;
            case 8:
                txtHeader8.Text = dsXls.Tables[0].Columns[7].ToString();
                txtHeader8.Visible = true;
                ddlHeader8.Visible = true;
                goto case 7;
            case 7:
                txtHeader7.Text = dsXls.Tables[0].Columns[6].ToString();
                txtHeader7.Visible = true;
                ddlHeader7.Visible = true;
                goto case 6;
            case 6:
                txtHeader6.Text = dsXls.Tables[0].Columns[5].ToString();
                txtHeader6.Visible = true;
                ddlHeader6.Visible = true;
                goto case 5;
            case 5:
                txtHeader5.Text = dsXls.Tables[0].Columns[4].ToString();
                txtHeader5.Visible = true;
                ddlHeader5.Visible = true;
                goto case 4;
            case 4:
                txtHeader4.Text = dsXls.Tables[0].Columns[3].ToString();
                txtHeader4.Visible = true;
                ddlHeader4.Visible = true;
                goto case 3;
            case 3:
                txtHeader3.Text = dsXls.Tables[0].Columns[2].ToString();
                txtHeader3.Visible = true;
                ddlHeader3.Visible = true;
                goto case 2;
            case 2:
                txtHeader2.Text = dsXls.Tables[0].Columns[1].ToString();
                txtHeader2.Visible = true;
                ddlHeader2.Visible = true;
                goto case 1;
            case 1:
                txtHeader1.Text = dsXls.Tables[0].Columns[0].ToString();
                txtHeader1.Visible = true;
                ddlHeader1.Visible = true;
                break;
        }        
    }

    /// <summary>
    /// 进行自动价格匹配
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnAutoMatch_Click(object sender, EventArgs e)
    {
        // 数据保存在临时表中
        SaveTemporaryData(dsXls);

        DBConnector dbConn = new DBConnector();

        string strSQL_TableName = "SELECT 数据表名称 FROM priceInfo WHERE 价格版本号 LIKE '" + txtVersion.Text + "'";
        string strTableName = dbConn.QueryString( strSQL_TableName );

        string strSQL_Match = "INSERT INTO " + strTableName + " (价格版本号,零件编号,单价,所属部位,零件类型,所属车系,所属车型,备注) " +
                              "SELECT '" + txtVersion.Text + "',零件编号,单价,所属部位,零件类型,所属车系,所属车系,备注 FROM tempPrice WHERE tempPrice.零件编号 IN " +
                              "(SELECT PartNum FROM Part)";
        int iRow = dbConn.Execute( strSQL_Match );

        string strRow = "共匹配数据:" + iRow + "行";
        MessageBox.Show( this, strRow );
        //string strSQL_Clear = "DELETE FROM tempPrice";
        //iRow = dbConn.Execute( strSQL_Clear );
    }

    #endregion

    #region 用户定义函数

    /// <summary>
    /// 将文件数据保存到临时表中
    /// </summary>
    /// <param name="dsXls"></param>
    private void SaveTemporaryData(DataSet dsXls, string strFilter)
    {
        DBConnector dbConn = new DBConnector();        

        SqlConnection sqlConn = new SqlConnection(dbConn.GetConnectString());
        sqlConn.Open();

        SqlDataAdapter sqlAdp = new SqlDataAdapter("SELECT * FROM tempPrice WHERE 1<>1", sqlConn);
        DataSet dsTemp = new DataSet();
        sqlAdp.Fill(dsTemp, "temp");

        for (int i = 0; i < dsXls.Tables[0].Rows.Count; i++)
        {
            DataRow drTemp = dsTemp.Tables[0].NewRow();

            switch (dsXls.Tables[0].Columns.Count)
            {
                case 9:
                    if ("-1" != ddlHeader9.SelectedValue)
                        drTemp[ddlHeader9.SelectedValue] = dsXls.Tables[0].Rows[i][8];
                    goto case 8;

                case 8:
                    if ("-1" != ddlHeader8.SelectedValue)
                        drTemp[ddlHeader8.SelectedValue] = dsXls.Tables[0].Rows[i][7];
                    goto case 7;

                case 7:
                    if ("-1" != ddlHeader7.SelectedValue)
                        drTemp[ddlHeader7.SelectedValue] = dsXls.Tables[0].Rows[i][6];
                    goto case 6;

                case 6:
                    if ("-1" != ddlHeader6.SelectedValue)
                        drTemp[ddlHeader6.SelectedValue] = dsXls.Tables[0].Rows[i][5];
                    goto case 5;

                case 5:
                    if ("-1" != ddlHeader5.SelectedValue)
                        drTemp[ddlHeader5.SelectedValue] = dsXls.Tables[0].Rows[i][4];
                    goto case 4;

                case 4:
                    if ("-1" != ddlHeader4.SelectedValue)
                        drTemp[ddlHeader4.SelectedValue] = dsXls.Tables[0].Rows[i][3];
                    goto case 3;

                case 3:
                    if ("-1" != ddlHeader3.SelectedValue)
                        drTemp[ddlHeader3.SelectedValue] = dsXls.Tables[0].Rows[i][2];
                    goto case 2;

                case 2:
                    if ("-1" != ddlHeader2.SelectedValue)
                        drTemp[ddlHeader2.SelectedValue] = dsXls.Tables[0].Rows[i][1];
                    goto case 1;

                case 1:
                    if ("-1" != ddlHeader1.SelectedValue)
                        drTemp[ddlHeader1.SelectedValue] = dsXls.Tables[0].Rows[i][0];
                    break;
            }

            //drTemp["零件编号"] = FiltString(drTemp["零件编号"].ToString(), strFilter);
            dsTemp.Tables[0].Rows.Add(drTemp);
        }
        SqlCommandBuilder custCB = new SqlCommandBuilder(sqlAdp);
        sqlAdp.Update(dsTemp, "temp");
    }

    /// <summary>
    /// 将文件数据保存到临时表中
    /// </summary>
    /// <param name="dsXls"></param>
    private void SaveTemporaryData(DataSet dsXls)
    {
        DBConnector dbConn = new DBConnector();

        SqlConnection sqlConn = new SqlConnection(dbConn.GetConnectString());
        sqlConn.Open();

        SqlDataAdapter sqlAdp = new SqlDataAdapter("SELECT * FROM tempPrice WHERE 1<>1", sqlConn);
        DataSet dsTemp = new DataSet();
        sqlAdp.Fill(dsTemp, "temp");

        for (int i = 0; i < dsXls.Tables[0].Rows.Count; i++)
        {
            DataRow drTemp = dsTemp.Tables[0].NewRow();

            switch (dsXls.Tables[0].Columns.Count)
            {
                case 9:
                    if ("-1" != ddlHeader9.SelectedValue)
                        drTemp[ddlHeader9.SelectedValue] = dsXls.Tables[0].Rows[i][8];
                    goto case 8;

                case 8:
                    if ("-1" != ddlHeader8.SelectedValue)
                        drTemp[ddlHeader8.SelectedValue] = dsXls.Tables[0].Rows[i][7];
                    goto case 7;

                case 7:
                    if ("-1" != ddlHeader7.SelectedValue)
                        drTemp[ddlHeader7.SelectedValue] = dsXls.Tables[0].Rows[i][6];
                    goto case 6;

                case 6:
                    if ("-1" != ddlHeader6.SelectedValue)
                        drTemp[ddlHeader6.SelectedValue] = dsXls.Tables[0].Rows[i][5];
                    goto case 5;

                case 5:
                    if ("-1" != ddlHeader5.SelectedValue)
                        drTemp[ddlHeader5.SelectedValue] = dsXls.Tables[0].Rows[i][4];
                    goto case 4;

                case 4:
                    if ("-1" != ddlHeader4.SelectedValue)
                        drTemp[ddlHeader4.SelectedValue] = dsXls.Tables[0].Rows[i][3];
                    goto case 3;

                case 3:
                    if ("-1" != ddlHeader3.SelectedValue)
                        drTemp[ddlHeader3.SelectedValue] = dsXls.Tables[0].Rows[i][2];
                    goto case 2;

                case 2:
                    if ("-1" != ddlHeader2.SelectedValue)
                        drTemp[ddlHeader2.SelectedValue] = dsXls.Tables[0].Rows[i][1];
                    goto case 1;

                case 1:
                    if ("-1" != ddlHeader1.SelectedValue)
                        drTemp[ddlHeader1.SelectedValue] = dsXls.Tables[0].Rows[i][0];
                    break;
            }

            drTemp["零件编号"] = (object)FiltString(drTemp["零件编号"].ToString());
            dsTemp.Tables[0].Rows.Add(drTemp);
        }
        SqlCommandBuilder custCB = new SqlCommandBuilder(sqlAdp);
        sqlAdp.Update(dsTemp, "temp");
    }

    /// <summary>
    /// 过滤字符串中的特定字符
    /// </summary>
    /// <param name="strSrc">原字符串</param>
    /// <param name="strFilter">过滤字符串</param>
    /// <returns>结果字符串</returns>
    private string FiltString(string strSrc, string strFilter)
    {
        for (int i = 0; i < strFilter.Length; i++ )
        {
            int iOffset = strSrc.IndexOf( strFilter.Substring(i,1) );
            if ( iOffset >= 0 )
            {
                strSrc = strSrc.Remove(iOffset, 1);
            }
        }
        return strSrc;
    }

    /// <summary>
    /// 过滤字符串中的特定字符
    /// </summary>
    /// <param name="strSrc"></param>
    /// <returns></returns>
    private string FiltString(string strSrc)
    {
        string strFilter = " -";
        for (int i = 0; i < strFilter.Length; i++)
        {            
            int iOffset;
            while ( ( iOffset = strSrc.IndexOf(strFilter.Substring(i, 1)) ) >= 0 )
            {
                strSrc = strSrc.Remove(iOffset, 1);
            }            
        }
        return strSrc;
    }

    #endregion
    static DataSet dsXls;
}