ASP.NET Excel导出数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.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); }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.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 ""; } } }