commimportexcel.aspx.cs

通过配置表中的各列支持绝大部分的基础数据

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Configuration;
namespace comm
{
    /// <summary>
    /// CommImportExcel 的摘要说明。
    /// </summary>
    public class CommImportExcel : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.ValidationSummary Validationsummary1;
        protected System.Web.UI.WebControls.Label Label3;
        protected System.Web.UI.WebControls.Label Label4;
        protected System.Web.UI.WebControls.ImageButton ImageButton1;
        protected System.Web.UI.WebControls.ImageButton Imagebutton2;
        protected System.Web.UI.WebControls.ImageButton Imagebutton3;
        protected System.Web.UI.WebControls.ImageButton Imagebutton4;
        protected System.Web.UI.HtmlControls.HtmlForm Form2;
        protected System.Web.UI.HtmlControls.HtmlInputFile File2;
        protected System.Web.UI.HtmlControls.HtmlForm Form3;
        protected System.Web.UI.WebControls.DataGrid dg;
        protected System.Web.UI.WebControls.Label txt_msg;
        protected System.Web.UI.WebControls.DropDownList ddl_table;
        protected System.Web.UI.WebControls.Label txt_tabtitle;
        #region 自定义变量
        include.Db db=new include.Db();
        #endregion 
        private void Page_Load(object sender, System.EventArgs e)
        { 
            if(Request["system"]!=null)
            {
                this.Session["system"]=Request["system"].Trim();
                this.Session["IP"]="";
                this.Session["usr"]=ConfigurationSettings.AppSettings["usr"];
                this.Session["pwd"]=ConfigurationSettings.AppSettings["pwd"];
            }
            db.DataBase =this.Session["system"].ToString();
            db.IP =  this.Session["IP"].ToString();
            db.Usr=   this.Session["usr"].ToString();
            db.Pwd= this.Session["pwd"].ToString();
            if(!this.IsPostBack)
            {
               
                // 在此处放置用户代码以初始化页面
                db.LoadDdl(this.ddl_table,"select table_name,table_namecn from dict_table",false);
                if(Request["tablename"]!=null)
                {
                    this.ddl_table.SelectedIndex=this.ddl_table.Items.IndexOf(this.ddl_table.Items.FindByValue(Request["tablename"]));
                    this.ddl_table.Visible =false;
                    this.txt_tabtitle.Visible =true;
                    this.txt_tabtitle.Text=this.ddl_table.SelectedItem.Text;
                }
            }
        }

        #region Web 窗体设计器生成的代码
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
            //
            InitializeComponent();
            base.OnInit(e);
        }
		
        /// <summary>
        /// 设计器支持所需的方法 - 不要使用代码编辑器修改
        /// 此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {    
            this.Imagebutton4.Click += new System.Web.UI.ImageClickEventHandler(this.Imagebutton4_Click);
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion

        private void Imagebutton4_Click(object sender, System.Web.UI.ImageClickEventArgs e)
        {
            txt_msg.Text="";
//            this.SetSaveCancel(false);
//            this.btnImport.Visible=true;
            string filename=db.SaveFile(this.File2,this);
            ViewState["filename"]=filename;
            if( filename=="" )
            {
//                txt_msg.ForeColor=Color.Red;
//                txt_msg.Text="操作失败，请选择文件上传，大小不要超过20M";
                db.Msgbox("操作失败，请选择文件上传，大小不要超过20M！","导入错误",this.Page);
                return;
            }
            string sql="SELECT * from  ["+this.ddl_table.SelectedItem.Text.Trim()+"$] ";//ORDER BY "+ViewState["sort"].ToString();
            ViewState["imp_sql"]=sql;
            DataSet ds=db.GetDataSetFromExcel(sql,filename);
            if(ds==null)
            {
//                cdts.err_msg("导入文件错误！",this.txt_msg);
                db.Msgbox("无法读取数据！","导入错误",this.Page);
                return;
            }
            string err="";

            for(int i=0;i<ds.Tables[0].Rows.Count;i++)
            {
                DataRow dr=ds.Tables[0].Rows[i];
                try
                {
                    //					this.Insert_Data(dr);
                    this.Insert_Data(dr,i,this.ddl_table.SelectedValue.Trim());
                }
                catch(Exception ce)
                {
//                    cdts.err_msg(ce.Message.ToString(),this.txt_msg );
                    db.Msgbox("数据无法进入数据库！","导入错误",this.Page);
                    return;
                }		
            }
//            this.CreateSQL();
//            ds=db.GetDataSet(ViewState["ds_sql"].ToString());
//            if(ds==null)
//            {
////                cdts.err_msg("数据库联接错误！",this.txt_msg);
//                return;
//            }
//            this.txt_msg.ForeColor=Color.Blue;
//            this.txt_msg.Text+=err;
            this.txt_msg.Text+="导入成功！";
            this.Response.Redirect("basedata.aspx?tablename="+this.ddl_table.SelectedValue.Trim());
        }


        #region 插入ds到数据库
        void Insert_Data(DataRow dr,int cnt,string tablename)
        {
            string sql="INSERT INTO "+tablename+" ( ";
            string str_updatecol="";
            string str_col="";
            int colcnt;
            try
            {
                foreach(Hashtable hkey in db.GetColumnInfo(this.ddl_table.SelectedValue.Trim(),this.Page))
                {
                    string key=hkey["NAME_CN"].ToString();
                    if(hkey["ISIDENTITY"].ToString()=="1")
                    {
                        continue;
                    }
                    
                        if(dr.Table.Columns.Contains(key))
                        {
                            string val=db.SetCompValue(tablename,hkey["NAME_EN"].ToString().Trim(),dr[key].ToString().Trim(),true,this.Page);
                            str_updatecol+=","+val;
                            str_col+=",["+hkey["NAME_EN"].ToString().Trim()+"]";
                        }
                        
                 
                }
                str_updatecol=str_updatecol.Substring(1);//
                str_col=str_col.Substring(1);
                sql+=str_col+") values("+str_updatecol+")";
                db.ExecSQL(sql);
            }
            catch(Exception ce)
            {
					db.Msgbox(ce.Message.Trim(),"导入错误！",this.Page);
                    return;
            }
        }

        #endregion

        #region 获得ｅｘｃｅｌ
        public DataSet GetDataSetFromExcel(string sql,string filename)
        {
            string strconn="Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+filename+";"+ "Extended Properties=Excel 8.0;";
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strconn);
            System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(sql,conn);
            DataSet ds = new DataSet();
            try
            {
                myCommand.Fill(ds);
                conn.Close();
            }
            catch(Exception e)
            {
                conn.Close();
                return null;
            }
            return ds;
        }
        #endregion
    }



}