D7QUANTAI/DSWeb/box/BoxBusinessExcel.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using WebSqlHelper;
using System.Data.OleDb;
using System.Collections;
using System.Data;

namespace DSWeb.box
{
    public partial class BoxBusinessExcel : System.Web.UI.Page
    {
        private string username;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["SHOWNAME"] != null)
            {
                username = Session["SHOWNAME"].ToString();
            }
            if (!IsPostBack)
            {
                HttpHelper.BindDropDownList(Vessel, BaseClass.db.GetSqlStrTable("select * from code_vessel order by VESSEL"), "vessel", "vessel", 1);
                bindVessel();
            }

            Page.ClientScript.RegisterStartupScript(this.GetType(), "key0", "<script>initComboPort();</script>");
        }

        protected void bindVessel()
        {
            //DropDownList dd = Position;
            //dd.Items.Clear();
            //dd.Items.Add(new ListItem("--请选择--", ""));
            //DataTable dt = BaseClass.db.GetSqlStrTable("select * from code_disport order by port asc");
            //foreach (DataRow dr in dt.Rows)
            //{
            //    dd.Items.Add(new ListItem(dr["port"].ToString() + " | " + dr["cname"].ToString(), dr["PORTID"].ToString()));
            //}
        }
        protected void ibexcel_Click(object sender, EventArgs e)
        {
           
            if (string.IsNullOrEmpty(Vessel.SelectedValue))
            {
                Alert("请选择船名");
                return;
            }
            if (string.IsNullOrEmpty(Voyage.Text))
            {
                Alert("请输入航次");
                return;
            }
            if (string.IsNullOrEmpty(Etd.Text))
            {
                Alert("请输入ETD");
                return;
            }
            if (string.IsNullOrEmpty(ReachTime.Text))
            {
                Alert("请输入到达日期");
                return;
            }
            if (string.IsNullOrEmpty(Request["ddlPORT0"].ToString()))
            {
                Alert("请选择起运港");
                return;
            }
            if (string.IsNullOrEmpty(Request["ddlPORT"].ToString()))
            {
                Alert("请选择目的港");
                return;
            }

            if (!string.IsNullOrEmpty(file.FileName))
            {

                string fileurl = Server.MapPath("/Reports/Import/" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
                file.PostedFile.SaveAs(fileurl);

                DataSet ds = LoadDataFromExcel(fileurl, ExcelSheetName(fileurl)[0].ToString());
                // B/LNO  CNTR NO   
                //Response.Write(ds.Tables[0].Rows[0]["B/LNO"].ToString());

                Alert2(business.importBoxBusiness(ds.Tables[0], Vessel.SelectedValue, Voyage.Text, Etd.Text, Request["ddlPORT"].ToString(), hd_comboPORT.Value, ReachTime.Text, Request["ddlPORT0"].ToString(), username.ToString()));
            }
            else
            {
                Alert("请选择要导入的文件");
                return;
            }

        }
        public DataSet LoadDataFromExcel(string filePath, string table)
        {
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;IMEX=1;'";
            using (OleDbConnection OleConn = new OleDbConnection(strConn))
            {
                OleConn.Open();
                String sql = "SELECT * FROM  [" + table + "$]";//可是更改Sheet名称，比如sheet2，等等 

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle);
                //OleConn.Close();
                return OleDsExcle;
            }
        }
        public ArrayList ExcelSheetName(string filepath)//获取excel的sheet的名字列表 注意：名字后不带$
        {
            ArrayList al = new ArrayList();
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1;'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable sheetNames = conn.GetOleDbSchemaTable
            (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();
            foreach (DataRow dr in sheetNames.Rows)
            {
                al.Add(dr[2].ToString().TrimEnd('$'));
            }
            return al;
        }
        // Methods
        public void Alert(string msg)
        {
            ScriptManager.RegisterClientScriptBlock(this, base.GetType(), WebSqlHelper.Common.RndNum(8), "alert('" + msg + "');", true);
        }
        public void Alert2(string msg)
        {
            ScriptManager.RegisterClientScriptBlock(this, base.GetType(), WebSqlHelper.Common.RndNum(8), "alert('" + msg + "');window.parent.opener.location.href = window.parent.opener.location.href;", true);
        }

        public void Run(string script)
        {
            ScriptManager.RegisterClientScriptBlock(this, base.GetType(), WebSqlHelper.Common.RndNum(8), script, true);
        }
        public void ShowMessageAndRedirect(string Message, string Redirect)
        {
            ScriptManager.RegisterClientScriptBlock(this, base.GetType(), WebSqlHelper.Common.RndNum(8), "alert('" + Message + "');" + (string.IsNullOrEmpty(Redirect) ? "" : "location='" + Redirect + "';"), true);
        }

       
    }
}