importprice_file_1.aspx.cs

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

        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 btnNextStep_Click(object sender, EventArgs e)
    {
        /*
        // 数据保存在临时表中
        DataSet dsXls = Session["XlsData"] as DataSet;
        SaveTemporaryData( dsXls );

        DBConnector dbConn = new DBConnector();

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

        string strModels = hidModels.Value;

        string[] aryModels = strModels.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_DupRes = "SELECT 零件编号,单价 FROM tempPrice WHERE 零件编号 " + 
                               "IN "+
                               "(SELECT 零件编号 as [Count] FROM tempPrice GROUP BY 零件编号 HAVING count(*)>1)";
        DataSet dsRes = dbConn.Query( strSQL_DupRes );

        if ( dsRes.Tables[0].Rows.Count > 0 )
        {
            string strFileName = strTableName + ".txt";
            StreamWriter sw = new StreamWriter("c:\\" + strFileName);
            sw.WriteLine("零件编号\t零件价格");
            for (int i = 0; i < dsRes.Tables[0].Rows.Count; i++ )
            {
                string line = dsRes.Tables[0].Rows[i]["零件编号"].ToString();
                line += "\t";
                line += dsRes.Tables[0].Rows[i]["单价"].ToString();
                sw.WriteLine( line );
            }
            sw.Close();
            // MessageBox.Show(this, "已在\"C:\\"+strFileName+"\"中保存重复零件信息");
        }        
                
        string strSQL_Match = "INSERT INTO " + strTableName + " (价格版本号,零件编号,单价,所属部位,零件类型,所属车系,所属车型,备注) " +
                              "SELECT '" + txtVersion.Text + "',零件编号,单价,所属部位,零件类型,所属车系,所属车型,备注 FROM tempPrice WHERE tempPrice.零件编号 IN " +
                              "(SELECT PartNum FROM Part WHERE ModelCode IN " + strQueryCondition + ")";
        string strSQL_Row = "SELECT count(*) FROM tempPrice";

        int iMatched = dbConn.Execute( strSQL_Match );
        int iSum = dbConn.QueryIntValue( strSQL_Row );


        string strMsg = "共有数据:" + iSum + "行;" +
                        "匹配数据:" + iMatched + "行";
       
        MessageBox.Show( this, strMsg );

        string strSQL_Clear = "DELETE FROM tempPrice";
        dbConn.Execute( strSQL_Clear );
        */

        // 数据保存在临时表中
        DataSet dsXls = Session["XlsData"] as DataSet;

        DBConnector dbConn = new DBConnector();
        string strModel = hidModels.Value.Split(',')[0];

        string strSQL_MakeCode = "SELECT Filter FROM Make INNER JOIN Model ON Make.MakeCode=Model.MakeCode WHERE ModelCode LIKE '" + strModel + "'";
        string strFilterSrc = dbConn.QueryString( strSQL_MakeCode );

        string[] aryFilter = strFilterSrc.Split(',');
        string strFilter = null;
        for (int i = 0; i < aryFilter.Length; i++ )
        {
            char cItem = (char)Convert.ToInt32( aryFilter[i] );
            strFilter += cItem;
        }

        try
        {
            SaveTemporaryData(dsXls, strFilter);            // 保存文件信息到临时表
        }
        catch (ArgumentException ex)
        {
            MessageBox.Show(this, "数据列配置错误,过程已终止");

            ddlHeader1.SelectedValue = "-1";
            ddlHeader2.SelectedValue = "-1";
            ddlHeader3.SelectedValue = "-1";
            ddlHeader4.SelectedValue = "-1";
            ddlHeader5.SelectedValue = "-1";
            ddlHeader6.SelectedValue = "-1";
            ddlHeader7.SelectedValue = "-1";
            ddlHeader8.SelectedValue = "-1";
            ddlHeader9.SelectedValue = "-1";

            return;
        }

        string strModels = hidModels.Value;             // 导入的车系列表
        string[] aryModels = strModels.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_Ins = "INSERT INTO " + hidTableName.Value + " (价格版本号,零件编号,厂牌编号,所属车系,单价,备注,导入标记,匹配标记) " +
                            "SELECT '" + hidVersion.Value + "',PartNum,MakeCode,Model.ModelCode,-1,'',0,0 FROM Part INNER JOIN Model ON Part.ModelCode=Model.ModelCode WHERE Part.ModelCode IN "+strQueryCondition;        
        int iPEDS_RowCount = dbConn.Execute( strSQL_Ins );

        string strSQL_Ins2 = "INSERT INTO MatchStatus (Version,MakeCode,ModelCode,UpperLimit,LowerLimit,MatchStatus) "+
                             "SELECT '" + hidVersion.Value + "',MakeCode,Model.ModelCode,波动上限,波动下限,0 FROM Model,priceInfo WHERE ModelCode IN " + strQueryCondition + " AND 价格版本号 LIKE '" + hidVersion.Value + "'";
        int iModelCount = dbConn.Execute( strSQL_Ins2 );

        string strScript = "<script language='javascript'>" +
                           "parent.frames['Main_Bottom_MainFrame'].location = 'ImportPrice_File_2.aspx?version=" + hidVersion.Value + "&models=" + strModels + "&rows="+iPEDS_RowCount+"';" +
                           "</script>";
        ClientScript.RegisterClientScriptBlock(this.GetType(), "clientScript", strScript);
    }

    #endregion

    #region 用户定义函数

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

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

        try
        {
            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["原始编号"] = drTemp["零件编号"].ToString();
                drTemp["零件编号"] = Utility.FilterString(drTemp["零件编号"].ToString(), strFilter);    // 按照默认过滤符号过滤
                drTemp["导入标记"] = "0";
                drTemp["匹配标记"] = "0";
                dsTemp.Tables[0].Rows.Add(drTemp);
            }
        }
        catch (ArgumentException ex)
        {
            throw new ArgumentException("数据列配置错误");
        }        

        SqlCommandBuilder custCB = new SqlCommandBuilder(sqlAdp);
        sqlAdp.Update(dsTemp, "temp");
            
    }

    #endregion        
}