editprice_file_1.aspx.cs

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

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
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 : System.Web.UI.Page
{

    #region 事件处理函数
    protected void Page_Load(object sender, EventArgs e)
    {
        if ( !Page.IsPostBack )
        {
            string strVersion = Request.QueryString["version"];
            string strModels = Request.QueryString["models"];

            hidVersion.Value = strVersion;      // 价格版本号
            hidModels.Value = strModels;        // 查询的车系序列

            string strSQL_PriceInfo = "SELECT * FROM priceInfo INNER JOIN level ON priceInfo.价格层次=level.级别层次 WHERE 价格版本号 LIKE '" + strVersion + "'";
            DBConnector dbConn = new DBConnector();
            DataSet dsPriceInfo = dbConn.Query( strSQL_PriceInfo );

            txtPriceName.Text = dsPriceInfo.Tables[0].Rows[0]["价格版本号"].ToString(); // 价格名称
            txtLevel.Text = dsPriceInfo.Tables[0].Rows[0]["级别名称"].ToString();       // 价格层次
            int iStatus = Convert.ToInt32( dsPriceInfo.Tables[0].Rows[0]["状态"] );     // 价格状态
            if (1 == iStatus)
                txtStatus.Text = "制作中";
            else if (2 == iStatus)
                txtStatus.Text = "已完成";
            else if (3 == iStatus)
                txtStatus.Text = "已发布";
            else
                txtStatus.Text = "已废弃";

            txtImpUpper.Text = dsPriceInfo.Tables[0].Rows[0]["导入上限"].ToString();    // 价格导入上限
            txtImpLower.Text = dsPriceInfo.Tables[0].Rows[0]["导入下限"].ToString();    // 价格导入下限

            txtPrcUpper.Text = dsPriceInfo.Tables[0].Rows[0]["波动上限"].ToString();    // 价格波动上限
            txtPrcLower.Text = dsPriceInfo.Tables[0].Rows[0]["波动下限"].ToString();    // 价格波动下限

            txtRemark.Text = dsPriceInfo.Tables[0].Rows[0]["备注"].ToString();          // 价格备注

            hidTableName.Value = dsPriceInfo.Tables[0].Rows[0]["数据表名称"].ToString();    // 价格信息所在的表名称
        }
    }
    /*
    
    */
    /// <summary>
    /// 检查文件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnCheck_Click(object sender, EventArgs e)
    {
        /*
        /////////////////////////////////////////////////////////////////////////
        // 过滤符号
        const string strDefualtFilter = " -";   // 默认过滤符号
        ////////////////////////////////////
        string strFilterCharacter = null;              // 过滤符号字符串

        if (txtFilter.Text.Length > 0)
        {
            strFilterCharacter = txtFilter.Text.Replace(",", "");
        }
        strFilterCharacter += strDefualtFilter;
        /////////////////////////////////////////////////////////////////////////
        */

        /////////////////////////////////////////////////////////////////////////
        // 读取文件
        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;

        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;
        }
        SaveTemporaryData(dsXls, strFilter);

        string[] aryModels = hidModels.Value.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_PEDS_RowCount = "SELECT count(*) FROM " + hidTableName.Value + "WHERE 所属车系 IN " + strQueryCondition;
        int iPEDSRowCount = dbConn.QueryIntValue( strSQL_PEDS_RowCount );

        string strModels = hidModels.Value;             // 导入的车系列表
        string strScript = "<script language='javascript'>" +
                           "parent.frames['Main_Bottom_MainFrame'].location = 'EditPrice_File_2.aspx?version=" + hidVersion.Value + "&models=" + strModels +"&rows="+ iPEDSRowCount +"';" +
                           "</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");

        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["零件编号"] = Utility.FilterString(drTemp["零件编号"].ToString(), strFilter);
            drTemp["导入标记"] = "0";
            drTemp["匹配标记"] = "0";
            dsTemp.Tables[0].Rows.Add(drTemp);
        }

        try
        {
            SqlCommandBuilder custCB = new SqlCommandBuilder(sqlAdp);
            sqlAdp.Update(dsTemp, "temp");
        }
        catch (SqlException ex) { }
    }

 
    #endregion

}