editprice_file.aspx.cs

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

        /////////////////////////////////////////////////////////////////////////
        // 读取文件
        string strFileName = fUpload.Value;
        if (strFileName == string.Empty)
            return;

        // DataSet dsXls = new DataSet();
        DataSet dsXls = new DataSet();
        try
        {
            string strExlConn = "provider=Microsoft.Jet.OLEDB.4.0;data source=" +
                                strFileName +
                                ";Extended Properties = Excel 8.0";
            OleDbConnection XlsConn = new OleDbConnection(strExlConn);
            XlsConn.Open();
            OleDbDataAdapter adpXls = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", XlsConn);
            adpXls.Fill(dsXls, "[Sheet1$]");            
            XlsConn.Close();

            ///////////////////////////////////
            // 将数据保存到会话中
            Session["XlsData"] = dsXls;
            ///////////////////////////////////
        }
        catch (Exception ex)
        {
            MessageBox.Show(this, "文件打开错误.");
            return;
        }

        txtHeader1.Visible = false;
        txtHeader2.Visible = false;
        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)
    {
        DataSet dsXls = Session["XlsData"] as DataSet;

        SaveTemporaryData( dsXls );     // 将价格保存到临时表中

        string strSQL_Update = "UPDATE " + hidTableName.Value + " SET "+hidTableName.Value+".单价=(SELECT 单价 FROM tempPrice WHERE "+hidTableName.Value+".零件编号= tempPrice.零件编号)";

        DBConnector dbConn = new DBConnector();
        int iRow = dbConn.Execute( strSQL_Update );

        string strMsg = "已更新价格" + iRow + "行";
        MessageBox.Show( this, strMsg );

        string strSQL_Clear = "DELETE FROM tempPrice";
        iRow = dbConn.Execute( strSQL_Clear );
    }

    #endregion

    #region 用户自定义函数

    /// <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>
    /// 过滤字符串中的特定字符 +1
    /// </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>
    /// 过滤字符串中的特定字符 + 1
    /// </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

}