using System;
using System.Data;
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;
using System.IO;
using System.Text;
using System.Data.OleDb;


public partial class excelimport : WebPage
{
    

    protected void Page_Load(object sender, EventArgs e)
    {
        this.VerifyPage();
    }

    protected void btnload_Click(object sender, EventArgs e)
    {
        WebKey wk = new WebKey();
        if (!btn_file.HasFile)
        {
            WebPage.ShowMsg("请选择要导入的Excel号码文件。", Page);
        }
        else
        {
            double iFileSizeLimit = Convert.ToInt32(wk.UploadFileMaxSize());

            if (btn_file.PostedFile.ContentLength > iFileSizeLimit)
            {
                WebPage.ShowMsg("文件超过最大限制500K，不能上传此文件。", Page);
            }
            else
            {
                string FileName = btn_file.PostedFile.FileName;
                string MyFileName = System.IO.Path.GetFileNameWithoutExtension(btn_file.PostedFile.FileName);
                string ExtFileName = System.IO.Path.GetExtension(btn_file.PostedFile.FileName).ToLower();


                bool fileAllow = false; //设定允许上载的扩展文件名类型  
                string[] allowExtensions = { ".xls" };

                for (int i = 0; i < allowExtensions.Length; i++)
                {
                    if (ExtFileName == allowExtensions[i])
                    {
                        fileAllow = true;
                    }
                }

                if (!fileAllow)
                {
                    WebPage.ShowMsg("不是有效的xls类型文件，请检查后重新上传!", Page);
                }
                else
                {
                    string SaveFolder = wk.UploadFileFolder();

                    string SavePath = Server.MapPath(SaveFolder + this.CurrentUser.UserName + "/"); //得到上传目录  

                    if (!System.IO.Directory.Exists(SavePath))
                    {
                        System.IO.Directory.CreateDirectory(SavePath);
                    }

                    //string NewFileName = DateTime.Now.ToString("d") + " " + DateTime.Now.Hour.ToString() + "-" + DateTime.Now.Minute.ToString() + "-" + DateTime.Now.Second.ToString() + " ";
                    
                    System.IO.File.Delete(SavePath + MyFileName + ExtFileName);//如果上次的文件还存在,先删除文件   
                    btn_file.PostedFile.SaveAs(SavePath + MyFileName + ExtFileName);

                    string name = MyFileName + ExtFileName;
                    string filepath = SavePath + name;


                    DataSet ds = ExcelDataSource(filepath, ExcelSheetName(filepath)[0].ToString());
                    DataTable dt = new DataTable();
                    dt = ds.Tables["table1"];


                    string strText = "";

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        strText += dt.Rows[i][0].ToString() + "\r\n";
                    }

                    strText = strText.Replace("\r\n","\\r\\n");
                    WebPage.ShowMsgAndFreshParentMobile("msgmobile", strText, Page);
                }
            }
        }

    }

   //从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径， sheetname为excel文件中的表名   
   public DataSet ExcelDataSource(string filepath, string sheetname)
   {
       string strConn;
       strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
       OleDbConnection conn = new OleDbConnection(strConn);
       OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
       DataSet ds = new DataSet();
       //oada.Fill(ds);
       oada.Fill(ds, "table1");
       conn.Close();
       return ds;
   }

   //获得Excel中的所有sheetname。
   public ArrayList ExcelSheetName(string filepath)
   {
       ArrayList al = new ArrayList();
       string strConn;
       strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
       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]);
       }
       return al;
   } 
}