_automatching.aspx.cs

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

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
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 AutoMatching : System.Web.UI.Page
{
    #region 事件处理
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            DBConnector dbConn = new DBConnector();
            dbConn.Connect();

            // 价格层次
            DataSet dsLevel = dbConn.Query("SELECT * FROM level");
            ddlPriceLevel.DataTextField = "级别名称";
            ddlPriceLevel.DataValueField = "级别层次";
            ddlPriceLevel.DataSource = dsLevel;
            ddlPriceLevel.DataBind();

            // 厂牌
            DataSet dsMark = dbConn.Query("SELECT * FROM Make");
            ddlMark.DataTextField = "MakeName";
            ddlMark.DataValueField = "MakeCode";
            ddlMark.DataSource = dsMark;
            ddlMark.DataBind();

            dbConn.Close();

            // 有效期限
            string strToday = DateTime.Today.ToShortDateString();
            txtDataRange.Text = strToday;
           

        }       
       
    }

    /// <summary>
    /// 显示日历控件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnDate_Click(object sender, EventArgs e)
    {
        cldLimit.Visible = !cldLimit.Visible;
    }

    /// <summary>
    /// 选择日期范围
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void cldLimit_SelectionChanged(object sender, EventArgs e)
    {     
        txtDataRange.Text = cldLimit.SelectedDate.ToShortDateString();
        cldLimit.Visible = false;
    }

    /// <summary>
    /// 读取文件信息
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnLoadFile_Click(object sender, EventArgs e)
    {
        string strFileName = fUpload.Value;

        if (strFileName == string.Empty)
            return;        
        
        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(m_dsXls, "[Sheet1$]");
            XlsConn.Close();
        }
        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 (m_dsXls.Tables[0].Columns.Count)
        {
            case 9:
                txtHeader9.Text = m_dsXls.Tables[0].Columns[8].ToString();
                txtHeader9.Visible = true;
                ddlHeader9.Visible = true;
                goto case 8;
            case 8:
                txtHeader8.Text = m_dsXls.Tables[0].Columns[7].ToString();
                txtHeader8.Visible = true;
                ddlHeader8.Visible = true;
                goto case 7;
            case 7:
                txtHeader7.Text = m_dsXls.Tables[0].Columns[6].ToString();
                txtHeader7.Visible = true;
                ddlHeader7.Visible = true;
                goto case 6;
            case 6:
                txtHeader6.Text = m_dsXls.Tables[0].Columns[5].ToString();
                txtHeader6.Visible = true;
                ddlHeader6.Visible = true;
                goto case 5;
            case 5:
                txtHeader5.Text = m_dsXls.Tables[0].Columns[4].ToString();
                txtHeader5.Visible = true;
                ddlHeader5.Visible = true;
                goto case 4;
            case 4:
                txtHeader4.Text = m_dsXls.Tables[0].Columns[3].ToString();
                txtHeader4.Visible = true;
                ddlHeader4.Visible = true;
                goto case 3;
            case 3:
                txtHeader3.Text = m_dsXls.Tables[0].Columns[2].ToString();
                txtHeader3.Visible = true;
                ddlHeader3.Visible = true;
                goto case 2;
            case 2:
                txtHeader2.Text = m_dsXls.Tables[0].Columns[1].ToString();
                txtHeader2.Visible = true;
                ddlHeader2.Visible = true;
                goto case 1;
            case 1:
                txtHeader1.Text = m_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 ddlMark_SelectedIndexChanged(object sender, EventArgs e)
    {
        TreeRange.Nodes.Clear();

        string strMake = ddlMark.SelectedValue; // 厂牌编号

        DBConnector dbConn = new DBConnector();
        dbConn.Connect();

        string strSQL = "SELECT * FROM Model WHERE MakeCode LIKE '" + strMake + "'";
        DataSet dsModel = dbConn.Query( strSQL );

        for (int i = 0; i < dsModel.Tables[0].Rows.Count; i++ )
        {
            TreeNode tn = new TreeNode(dsModel.Tables[0].Rows[i][2].ToString(), dsModel.Tables[0].Rows[i][1].ToString());
            TreeRange.Nodes.Add(tn);
            PopulateSubMdlNodes(tn);
        }
                
        string strLevel = ddlPriceLevel.SelectedValue;
        string strSQL_1 = "SELECT 数据表名称 FROM priceInfo WHERE 厂牌编号 LIKE '" + strMake + "' AND 价格层次=" + strLevel;
        string strTableName = dbConn.QueryString(strSQL_1);


        string strSQL_2 = "SELECT 所属车型 FROM " + strTableName + " GROUP BY 所属车型";
        DataSet dsMatched = dbConn.Query(strSQL_2);

        for (int i = 0; i < dsMatched.Tables[0].Rows.Count; i++ )
        {
            for (int j = 0; j < TreeRange.Nodes.Count; j++ )