完整ASP.Net Excel导入

  //把EXCEL文件上传到服务器并返回文件路径
        private String typename(FileUpload fileloads)
        {
            string fullfilename = fileloads.PostedFile.FileName;
            string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\") + 1);
            string type = fullfilename.Substring(fullfilename.LastIndexOf(".") + 1);
            string murl = "";
            if (type == "xls" || type == "xlsx")
            {
                fileloads.PostedFile.SaveAs(Server.MapPath(filename));
                //fileloads.PostedFile.SaveAs(Server.MapPath(Tools.GetAppSettings("ExcelPath")) + "\\" + filename);
                murl = Server.MapPath(filename);
            }
            else
            {
                Tools.ShowMsg("导入文件格式不对!");
                //Response.Write("<script language='javascript'>alert('导入文件格式不对!');</script>");
            }
            return murl;
        }

        public DataTable ImportToDataSet(string path) 
        {
            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + path + ";" + "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';";

            OleDbConnection conn = new OleDbConnection(strConn);  
            try 
            {  
                DataTable dt = new DataTable();  
                if (conn.State != ConnectionState.Open)  
                    conn.Open();  
                string strExcel = "select * from [Sheet1$]";  
                OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);  
                adapter.Fill(dt);  
                return dt;  
            }  
            catch (Exception ex)  
            {  
                throw new Exception(ex.Message);  
            }  
            finally 
            {  
                if (conn.State != ConnectionState.Closed)  
                    conn.Close();  
            }  
        } 

        protected void btnImport_Click(object sender, EventArgs e)
        {
            try
            {
                DataTable dt = ImportToDataSet(typename(selectfile));
                int j = 0;
                bool sec = true;
                
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ShippingCharge sc = new ShippingCharge();
                    string RegionID = dt.Rows[i][1].ToString();
                    if (!RegionID.IsNullOrEmpty() && Service.GetShippingRegion(dt.Rows[i][1].ToString()) != null)
                    {
                        sc.RegionID = Service.GetShippingRegion(dt.Rows[i][1].ToString()).RegionID;
                    }

                    sc.Status = Convert.ToByte(dt.Rows[i][2].ToString()=="否"?0:1);

                    sec = Service.AddShippingCharge(sc);
                    if (sec == false)
                    {
                        j++;
                    }
                }
                
                if(j > 0)
                {
                    Tools.ShowMsg("导入文件失败,请确认文件格式再重试一次!");
                }
                else
                {
                     //关闭,删除 文件  
                    string filepath = Server.MapPath(selectfile.PostedFile.FileName);
                    if (File.Exists(filepath))
                        File.Delete(filepath);  
                    Tools.ShowMsg("导入文件成功!", "ShippingCharges_List.aspx");
                }
            }
            catch (Exception ex)
            {
                Tools.ShowMsg("导入文件失败,请确认文件格式再重试一次!");

            }
        }

posted @ 2017-02-10 15:39  清空回声  阅读(316)  评论(0编辑  收藏  举报