ASP.NET Excel导出数据

我就是我 2022-04-24 04:40 201阅读 0赞

# [ASP.NET Excel导出数据][ASP.NET Excel] #

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

protected void LinkButton1_Click(object sender, EventArgs e)
            {
                string strsql = " 1=1 ";
                int type = Convert.ToInt32(droptype.SelectedValue);
                string zyzids = String.Empty;
                if (type != 0)
                {
                    List<zyz_zyztype> ztlist = zyz_zyztypeBLL.GetModelList(" Type_id=" + type);
                    if (ztlist.Count > 0)
                    {
                        foreach (zyz_zyztype model in ztlist)
                        {
                            zyzids += model.zyz_ID + ",";
                        }
                        zyzids = zyzids.Substring(0, zyzids.Length - 1);
                        strsql += " and zyz.id in(" + zyzids + ") ";
                    }
                    else
                    {
                        strsql += " and zyz.id in(" + 0 + ") ";
                    }
                }
    
    
                int channelid = 0;
                string type1 = ddltype1.SelectedValue;
                string type2 = ddltype2.SelectedValue;
                string type3 = ddltype3.SelectedValue;
    
                if (type1 != "" && type2 != "" && type3 != "")
                {
                    channelid = Convert.ToInt32(type3);
                }
                else if (type1 != "" && type2 != "" && type3 == "")
                {
                    channelid = Convert.ToInt32(type2);
                }
                else if (type1 != "" && type2 == "" && type3 == "")
                {
                    channelid = Convert.ToInt32(type1);
                }
                else
                {
                    channelid = 0;
                }
    
                if (channelid != 0)
                {
                    List<int> list = Community.BLL.BUSER_GROUPExtBLL.GetAllPerGROUP(channelid);
                    string ids = string.Empty;
                    if (list.Count > 0)
                    {
                        foreach (int n in list)
                        {
                            ids += n + ",";
                        }
                        ids = ids.Substring(0, ids.Length - 1);
                        strsql += " and Unit in (" + ids + ") ";
                    }
    
    
                }
    
                    strsql += " and  peopletype=1 ";
    
                    DataSet ds = BLL.zyzBLL.GetListReport(strsql);
    
    
    
    
    
                    Comm.DBOperation.ExportUploadFile euf = new Comm.DBOperation.ExportUploadFile();
                    string desFilePath = euf.CreateDownloadExecl("能者为师列表");
    
                    if (desFilePath == "")
                    {
                        EventMessage.MessageBox(1, "操作无效", "您刚导出的信息，还在生成，请等待片刻后再导出...", Icon_Type.Error, "right.aspx");
                        return;
                    }
                    // 姓名 、性别、类别、联系方式，所在区县、申请时间、是否审核
                    List<string> lsnc = new List<string>();
                    lsnc.Add("姓名");
                    lsnc.Add("性别");
                    lsnc.Add("类别");
                    lsnc.Add("联系方式");
                    lsnc.Add("地址");
                    lsnc.Add("所在区县");
                    lsnc.Add("申请时间");
                    lsnc.Add("是否审核");
                    DataTable NewDt = new DataTable();
                    NewDt.Columns.Add("zyz_name", typeof(System.String));
                    NewDt.Columns.Add("sex", typeof (System.String));
                    NewDt.Columns.Add("typename", typeof (System.String));
                    NewDt.Columns.Add("lx", typeof (System.String));
                    NewDt.Columns.Add("Unit", typeof (System.String));
                    NewDt.Columns.Add("USER_GROUP_NAME", typeof (System.String));
                    NewDt.Columns.Add("createtime", typeof (System.String));
                    NewDt.Columns.Add("sh", typeof (System.String));
    
                    Community.BLL.Execl dal = new Community.BLL.Execl(desFilePath);
    
                    int Count = 0;
                    int step = 1;
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        Count++;
                        if (Count > 60000)
                        {
                            if (NewDt.Rows.Count > 0)
                            {
                                dal.CreateNewTable("能者为师列表" + step.ToString(), dal.ConnectionExeclString, lsnc);
    
                                dal.InsertToTable("能者为师列表" + step.ToString(), dal.ConnectionExeclString, NewDt, lsnc);
                            }
                            NewDt.Rows.Clear();
                            Count = 0;
                            step++;
                        }
                        DataRow NewDr = NewDt.NewRow();
                        NewDr["zyz_name"] = dr["zyz_name"];
                        NewDr["sex"] = dr["sex"];
                        NewDr["typename"] = TypeName(dr["id"].ToString());
                        NewDr["lx"] = dr["lx"];
                        NewDr["Unit"] = GetUserGroup(dr["Unit"].ToString());
                        NewDr["USER_GROUP_NAME"] = dr["USER_GROUP_NAME"];
                        NewDr["createtime"] = dr["createtime"];
                        NewDr["sh"] = dr["sh"];
    
                        NewDt.Rows.Add(NewDr);
                    }
                    if (NewDt.Rows.Count > 0)
                    {
                        dal.CreateNewTable("能者为师列表", dal.ConnectionExeclString, lsnc);
    
                        dal.InsertToTable("能者为师列表", dal.ConnectionExeclString, NewDt, lsnc);
                    }
                    string DownloadUrl = "~/Execl/" + DateTime.Now.ToString("yyyyMMdd") + "/" +
                                         Comm.DBOperation.Comm.CurrentUser.USER_ID + "/" + "能者为师列表.xls";
                    Response.Redirect(DownloadUrl);
    
    
                }

后台逻辑代码

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

public class ExportUploadFile
        {
            #region 限制上传文件的后缀名
            public static bool IsAllowedExtension(HtmlInputFile hifile)
            {
    
                string strOldFilePath = string.Empty, strExtension = string.Empty;
                //允许上传的扩展名，可以改成从配置文件中读出
                string[] arrExtension = { ".doc", ".pdf", ".jpg", ".txt" };
                if (hifile.PostedFile.FileName != string.Empty)
                {
                    strOldFilePath = hifile.PostedFile.FileName;
                    //取得上传文件的扩展名
                    strExtension = strOldFilePath.Substring(strOldFilePath.LastIndexOf("."));
                    //判断该扩展名是否合法
                    for (int i = 0; i < arrExtension.Length; i++)
                    {
                        if (strExtension.Equals(arrExtension[i]))
                        {
                            return true;
                        }
                    }
                }
                return false;
            }
            # endregion
    
            #region 限制上传文件的大小
            /// <summary>
            /// 判断上传文件大小是否超过最大值
            /// </summary>
            /// <param name="hifile">HtmlInputFile控件</param>
            /// <returns>超过最大值返回false,否则返回true.</returns>
    
            public static bool IsAllowedLength(HtmlInputFile hifile)
            {
                //允许上传文件大小的最大值,单位为KB
                int i = 20;
                //如果上传文件的大小超过最大值,返回flase,否则返回true.
                if (hifile.PostedFile.ContentLength > i * 1024)
                {
                    return false;
                }
                return true;
            }
    
            #endregion
    
            #region 覆盖文件（删除原有文件）
    
            /// <summary>
            /// 重新上传文件，删除原有文件
            /// </summary>
            /// <param name="ffFile">HtmlInputFile控件</param>
            /// <param name="strAbsolutePath">绝对路径.  </param>
            /// <param name="strOldFileName">旧文件名</param>
    
            public static void CoverFile(HtmlInputFile ffFile, string strAbsolutePath, string strOldFileName)
            {
                //获得新文件名
                //string strNewFileName = GetUniqueString();
                if (ffFile.PostedFile.FileName != string.Empty)
                {
                    //旧图片不为空时先删除旧图片
                    if (strOldFileName != string.Empty)
                    {
                        DeleteFile(strAbsolutePath, strOldFileName);
                    }
                    //SaveFile(ffFile, strAbsolutePath);
                }
            }
    
            #endregion
    
            #region 删除文件
            /// <summary>
            /// 删除指定文件
            /// </summary>
            /// <param name="strAbsolutePath">文件绝对路径</param>
            /// <param name="strFileName">文件名</param>
            public static void DeleteFile(string strAbsolutePath, string strFileName)
            {
                if (strAbsolutePath.LastIndexOf("\\") == strAbsolutePath.Length)
                {
                    if (File.Exists(strAbsolutePath + strFileName))
                    {
                        File.Delete(strAbsolutePath + strFileName);
                    }
                }
                else
                {
                    if (File.Exists(strAbsolutePath + "\\" + strFileName))
                    {
                        File.Delete(strAbsolutePath + "\\" + strFileName);
                    }
                }
            }
    
            #endregion
    
            #region 与以前不重复的文件名
    
            /// <summary>
            /// 获取一个不重复的文件名
            /// </summary>
            /// <returns></returns>
    
            public static string GetUniqueString()
            {
                return DateTime.Now.ToString("yyyyMMddhhmmss");
            }
    
            #endregion
    
    
            /// <summary>
            /// 删除文件
            /// </summary>
            /// <param name="name"></param>
            public static void del(string name)
            {
                if (name.Length > 0)
                    if (File.Exists(Path.Combine("Save", name)))
                        File.Delete(Path.Combine("Save", name));
            }
    
    
            /// <summary>
            /// 下载文件
            /// </summary>
            /// <param name="UrlPath"></param>
            public static void FileDownload(string UrlPath)
            {
                try
                {
                    FileInfo DownloadFile = new FileInfo(UrlPath);
                    if (File.Exists(UrlPath))
                    {
                        HttpContext.Current.Response.Clear();
                        HttpContext.Current.Response.ClearHeaders();
                        HttpContext.Current.Response.Buffer = false;
                        HttpContext.Current.Response.ContentType = "application/octet-stream";
                        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename="
                         + HttpUtility.UrlEncode(DownloadFile.FullName, System.Text.Encoding.ASCII));
    
                        HttpContext.Current.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
                        HttpContext.Current.Response.WriteFile(DownloadFile.FullName);
                        HttpContext.Current.Response.Flush();
    
                        HttpContext.Current.Response.End();
                    }
                    else
                    {
                        HttpContext.Current.Response.Write("<script language=javascript>alert('文件不存在');</script>");
                    }
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
    
    
            /// <summary>
            /// 
            /// </summary>
            /// <param name="PostedFile"></param>
            /// <param name="b"></param>
            /// <returns></returns>
            public static string GetName(HttpPostedFile PostedFile, bool b)
            {
                
    
                string Ret = "";
                string CurrentPath = HttpContext.Current.Server.MapPath("Save");
                Log.WriteLog("CurrentPath:" + CurrentPath, 1);
    
                //Community.UtilityFun.Log.WriteLog("CurrentPath=" + CurrentPath, 1);
    
                string FilePath = PostedFile.FileName;
                string ExtensionName = Path.GetExtension(FilePath).Replace(".", "").ToLower();
                List<Community.Model.UploadeFileManager> lnc = Community.BLL.UploadeFileManagerBLL.UploadeFileManager_select();
                if (b == false)
                {
                    //Community.UtilityFun.Log.WriteLog("b=false", 1);
                    foreach (Community.Model.UploadeFileManager nc in lnc)
                    {
                        //Community.UtilityFun.Log.WriteLog("ExtensionName=" + ExtensionName + " nc.ExtensionName.ToLower()= " + nc.ExtensionName.ToLower() + "  nc.IsActive=" + nc.IsActive, 1); 
    
                        if (nc.ExtensionName.ToLower() == ExtensionName && nc.IsActive != 0)
                        {
                            //Community.UtilityFun.Log.WriteLog("nc.IsActive=" + nc.IsActive, 1);
                            b = true;
                            break;
                        }
                    }
                }
                if (b)
                {
                    //Community.UtilityFun.Log.WriteLog("b=true" , 1);
                    string TimeNow = Guid.NewGuid().ToString();
    
                    ExtensionName = TimeNow + "." + ExtensionName;
    
                    if (Directory.Exists(CurrentPath) == false)
                    {
                        try
                        {
                            Directory.CreateDirectory(CurrentPath);
                        }
                        catch(Exception ex) 
                        {
                            //Community.UtilityFun.Log.WriteLog(" ex=" + ex, 1);
                        }
                    }
                    string SaveFilePath = Path.Combine(CurrentPath, ExtensionName);
                    while (File.Exists(SaveFilePath) == true)
                    {
                        ExtensionName = Guid.NewGuid().ToString() + "." + ExtensionName;
    
                        SaveFilePath = Path.Combine(CurrentPath, ExtensionName);
    
                    }
                    try
                    {                   
                        Ret = Path.GetFileName(SaveFilePath);
                        PostedFile.SaveAs(SaveFilePath);
                    }
                    catch(Exception ex)
                    {
                        Log.WriteLog("ex:" + ex.Message, 1);
                        //Community.UtilityFun.Log.WriteLog("ex1=" + ex, 1);
                        Ret = "";
                    }
                    //Log.WriteLog("Ret:" + Ret, 1);
                    return Ret;
                }
                else
                {
    
                    //Community.UtilityFun.Log.WriteLog("kong", 1);
                    return "";
                }
            }
    
    
    
            /// <summary>
            /// 
            /// </summary>
            /// <param name="PostedFile"></param>
            /// <param name="b"></param>
            /// <returns></returns>
            public static string GetName(HttpPostedFile PostedFile, bool b, string path)
            {
    
                string Ret = "";
                string CurrentPath = path;// HttpContext.Current.Server.MapPath("Save");
                string FilePath = PostedFile.FileName;
                string ExtensionName = Path.GetExtension(FilePath).Replace(".", "").ToLower();
                List<Community.Model.UploadeFileManager> lnc = Community.BLL.UploadeFileManagerBLL.UploadeFileManager_select();
                if (b == false)
                {
                    foreach (Community.Model.UploadeFileManager nc in lnc)
                    {
                        if (nc.ExtensionName.ToLower() == ExtensionName && nc.IsActive != 0)
                        {
                            b = true;
                            break;
                        }
                    }
                }
                if (b)
                {
                    string TimeNow = Guid.NewGuid().ToString();
    
                    ExtensionName = TimeNow + "." + ExtensionName;
    
                    if (Directory.Exists(CurrentPath) == false)
                    {
                        Directory.CreateDirectory(CurrentPath);
                    }
                    string SaveFilePath = Path.Combine(CurrentPath, ExtensionName);
                    while (File.Exists(SaveFilePath) == true)
                    {
                        ExtensionName = Guid.NewGuid().ToString() + "." + ExtensionName;
    
                        SaveFilePath = Path.Combine(CurrentPath, ExtensionName);
                    }
                    try
                    {
                        Ret = Path.GetFileName(SaveFilePath);
                        PostedFile.SaveAs(SaveFilePath);
                    }
                    catch
                    {
                        Ret = "";
                    }
                    return Ret;
                }
                else
                {
                    return "";
                }
            }
    
    
    
            /**/
            /// <summary>
            /// 生成缩略图
            /// </summary>
            /// <param name="originalImagePath">源图路径（物理路径）</param>
            /// <param name="thumbnailPath">缩略图路径（物理路径）</param>
            /// <param name="width">缩略图宽度</param>
            /// <param name="height">缩略图高度</param>
            /// <param name="mode">生成缩略图的方式</param>    
            public static void MakeThumbnail(string originalImagePath, string thumbnailPath, int width, int height, string mode)
            {
                System.Drawing.Image originalImage = System.Drawing.Image.FromFile(originalImagePath);
                int towidth = width;
                int toheight = height;
    
                int x = 0;
                int y = 0;
                int ow = originalImage.Width;
                int oh = originalImage.Height;
    
                switch (mode)
                {
                    case "HW"://指定高宽缩放（可能变形）                
                        break;
                    case "W"://指定宽，高按比例                    
                        toheight = originalImage.Height * width / originalImage.Width;
                        break;
                    case "H"://指定高，宽按比例
                        towidth = originalImage.Width * height / originalImage.Height;
                        break;
                    case "Cut"://指定高宽裁减（不变形）                
                        if ((double)originalImage.Width / (double)originalImage.Height > (double)towidth / (double)toheight)
                        {
                            oh = originalImage.Height;
                            ow = originalImage.Height * towidth / toheight;
                            toheight = originalImage.Height * toheight / originalImage.Width;
                            y = 0;
                            x = (originalImage.Width - ow) / 2;
                        }
                        else
                        {
                            ow = originalImage.Width;
                            oh = originalImage.Width * height / towidth;
                            towidth = originalImage.Width * towidth / originalImage.Height;
                            x = 0;
                            y = (originalImage.Height - oh) / 2;
                        }
                        break;
                    default:
                        break;
                }
                //新建一个bmp图片
                System.Drawing.Image bitmap = new System.Drawing.Bitmap(towidth, toheight);
                //新建一个画板
                Graphics g = System.Drawing.Graphics.FromImage(bitmap);
                //设置高质量插值法
                g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.Default;
                //设置高质量,低速度呈现平滑程度
                g.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality;
                //清空画布并以透明背景色填充
                g.Clear(Color.Transparent);
                //在指定位置并且按指定大小绘制原图片的指定部分
    
                g.DrawImage(originalImage, new Rectangle(0, 0, towidth, toheight),
                    new Rectangle(0, 0, originalImage.Width, originalImage.Height),
                    GraphicsUnit.Pixel);
                try
                {
                    //以jpg格式保存缩略图
                    bitmap.Save(thumbnailPath, System.Drawing.Imaging.ImageFormat.Jpeg);
                }
                catch (System.Exception e)
                {
                    throw e;
                }
                finally
                {
                    originalImage.Dispose();
                    bitmap.Dispose();
                    g.Dispose();
                }
            }
    
            public string CreateDownloadExecl(string ExeclName)
            {
                string ExeclModenPath = HttpContext.Current.Server.MapPath("~/");
                ExeclModenPath = System.IO.Path.Combine(ExeclModenPath, "Execl");
                Community.UtilityFun.Log.WriteLog("执行1" + ExeclModenPath, 1);
                //生成的execl文件在根目录execl下，之后再对应的日期下：yyyyMMdd,之后再对应的管理员的User_ID下。
                //如Execl--->yyyyMMdd--->User_ID---->正在学习的用户
                string desFilePathByDay = System.IO.Path.Combine(ExeclModenPath, DateTime.Now.ToString("yyyyMMdd"));
                Community.UtilityFun.Log.WriteLog("执行2" + desFilePathByDay, 1);
                if (System.IO.Directory.Exists(desFilePathByDay) == false)
                    System.IO.Directory.CreateDirectory(desFilePathByDay);
    
                string desFilePathByUserID = System.IO.Path.Combine(desFilePathByDay, Comm.CurrentUser.USER_ID);
                Community.UtilityFun.Log.WriteLog("执行3" + desFilePathByUserID, 1);
                if (System.IO.Directory.Exists(desFilePathByUserID) == false)
                    System.IO.Directory.CreateDirectory(desFilePathByUserID);
    
                string desFilePath = System.IO.Path.Combine(desFilePathByUserID, ExeclName + ".xls");
                Community.UtilityFun.Log.WriteLog("执行4" + desFilePath, 1);
                //删除今天的历史导出记录
                try
                {
                    if (System.IO.File.Exists(desFilePath) == true)
    
                        System.IO.File.Delete(desFilePath);
    
    
                    //删除以往的所有导出记录
                    string[] OldDirectorys = System.IO.Directory.GetDirectories(ExeclModenPath);
                    foreach (string s in OldDirectorys)
                    {
                        string[] temps = s.Split('\\');
                        string DirectorysName = temps[temps.Length - 1];
                        if (DirectorysName == DateTime.Now.ToString("yyyyMMdd") || DirectorysName == DateTime.Now.AddDays(-1).ToString("yyyyMMdd"))
                        {
                            continue;
                        }
                        else
                        {
                            //删除文件
                            string[] DirectorysTemp = System.IO.Directory.GetDirectories(s);
    
                            string[] FileTemp = System.IO.Directory.GetFiles(s);
    
                            foreach (string Files in FileTemp)
                            {
                                System.IO.File.Delete(Files);
                            }
    
                            foreach (string Directorys in DirectorysTemp)
                            {
    
                                string[] NewFileTemp = System.IO.Directory.GetFiles(Directorys);
    
                                foreach (string Files in NewFileTemp)
                                {
                                    System.IO.File.Delete(Files);
                                }
                                System.IO.Directory.Delete(Directorys);
    
                            }
    
                            System.IO.Directory.Delete(s);
    
                        }
                    }
                }
                catch
                {
                    return "";
                }
                string SourceFilePath = System.IO.Path.Combine(ExeclModenPath, "model.xls");
                try
                {
    
                    System.IO.File.Copy(SourceFilePath, desFilePath);
                    return desFilePath;
    
                }
                catch
                {
                    return "";
                }
            }
    
    
    
        }

文件操作类公共方法

posted @ 2017-06-16 13:40 [波霸38][38] 阅读( ...) 评论( ...) [编辑][Link 1] 收藏

[ASP.NET Excel]: https://www.cnblogs.com/sharing1986687846/p/7027115.html
[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20220219/7dca64efa03740349592ddc19e32fab2.png
[38]: https://www.cnblogs.com/sharing1986687846/
[Link 1]: https://i.cnblogs.com/EditPosts.aspx?postid=7027115