完整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("导入文件失败,请确认文件格式再重试一次！");

}
}

巴特西

完整ASP.Net Excel导入

最新文章

热门文章