正题前的唠叨
本人是才出来工作不久的小白菜一颗，技术很一般，总是会有遇到一些很简单的问题却不知道怎么做，这些问题可能是之前解决过的。发现这个问题，想着提升一下自己的技术水平，将一些学的新的‘好’东西记录下来，一是加深印象；二是以后可以作为参考；三是希望博友们可以提出不足和可以优化的地方，一起讨论。
本方法是通过ajax直接下载，当然也可以直接button下载，本方法类已经实现导入导出，请执行扩展。谢谢
效果图：点击导出按钮
前端 AJAX
//导出ececl function btn_excel(ExeclID) { var myConfirmCode = ' <div id="downloaddiv" style="display: none"> <a id = "downloadRul" href = "" > <p> </p> </a > </div >'; $("body").append(myConfirmCode); $.ajax({ type: "GET", url: "../../tools/downloadExecl.ashx?action=GetExcel", data: { "ExeclID": ExeclID, "TempName":"", "Outname":"" }, dataType: "json", success: function (data) { if (data.status == 1) { $("#downloadRul").attr("href", data.msg); $('#downloadRul>p').trigger("click"); $("#downloaddiv").remove(); } else { $("#downloaddiv").remove(); alert("请登入系统"); } } }); // $("#downloaddiv").remove(); }
主要是导出excel并在浏览器下载下来。 但是会有不同的细微的需求差别。
进入正题
简单的需求描述
将查询出来已经转化好了的list<T> xxx 的数据的某些列，导入到excel之中，并在浏览器中下载下来。
public void GetExcel(HttpContext context)
{
string ExeclID = MXRequest.GetString("ExeclID");//导出模板ID，管理sql
BLL.InfoExecl infoExeclbll = new BLL.InfoExecl();
infoExecl= infoExeclbll.GetModel(MyCommFun.Obj2Int( ExeclID));
if (Outname != "") {
infoExecl.titlename = Outname;
}
if (TempName != "")
{
infoExecl.title = TempName;
}
DataTable dt = new DataTable(); // DataTable 数据源
string download = Manage.ExeclOUtAjax(infoExecl.Sql, infoExecl.title, infoExecl.titlename);
context.Response.Write("{"status":1, "msg": ""+ download + ""}");
}
/// <summary>
/// 导出Exec方法
/// </summary>
/// <param name="sql"></param>
/// <param name="temppathName"></param>
/// <param name="Outname"></param>
public string ExeclOUtAjax(string sql, string temppathName, string Outname)
{
string timeflie= DateTime.Now.ToString("yyyyMMddHHmmss");
DataSet ds = DBUtility.DbHelperSQL.Query(sql);
//导出数据
string temppath = Server.MapPath("\UpFile");
string filepath = temppath + "\ExportExcel\" + temppathName + ".xls";//临时文件,也作为模板文件
string downUrl = "\ExcelOut\" + timeflie+ Outname+ ".xls";//导出文件路径
string downUrlAjax = "/UpFile/ExcelOut/" + timeflie + Outname + ".xls";//下载文件路径
string outfilepath = temppath + downUrl;//导出文件路径
AsposeExcel asposeexcel = new AsposeExcel(outfilepath, filepath);
asposeexcel.DatatableToExcel(ds.Tables[0]);
//return DownFile("\UpFile" + downUrl, DateTime.Now.ToString("yyyyMMdd") + "零售品牌汇总导出.xls");
//获取项目绝对路径地址
string url = HttpContext.Current.Request.Url.AbsoluteUri.ToString().Split('/')[0] + "//" + HttpContext.Current.Request.Url.Authority.ToString();
var virtualPath = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;
string fileName = "";
if (virtualPath != "/")
{
//有子应用程序
fileName = virtualPath + "/UpFile/";
}
else
{
fileName = "/Document/TemporaryDocuments/" + tempExcelName;
}
//清除历史文件，避免历史文件越来越多，可进行删除
DirectoryInfo dyInfo = new DirectoryInfo(HttpContext.Current.Server.MapPath("/UpFile/ExcelOut/"));
//获取文件夹下所有的文件
foreach (FileInfo feInfo in dyInfo.GetFiles())
{
//判断文件日期是否小于两天前，是则删除
if (feInfo.CreationTime < DateTime.Today.AddDays(-2))
feInfo.Delete();
}
return url+ downUrlAjax;
}
后端 公共操作类
AsposeExcel.cs 类
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data; 6 using Aspose.Cells; 7 using System.IO; 8 using System.Web; 9 10 namespace Flysem.Common 11 { 12 public class AsposeExcel 13 { 14 private string outFileName = ""; 15 private string fullFilename = ""; 16 private Workbook book = null; 17 private Worksheet sheet = null; 18 /// <summary> 19 /// 20 /// </summary> 21 /// <param name="outfilename">导出文件路径</param> 22 /// <param name="tempfilename">模板文件路径</param> 23 public AsposeExcel(string outfilename, string tempfilename) //导出构造数 24 { 25 Aspose.Cells.License license = new Aspose.Cells.License(); 26 license.SetLicense("Aspose.Cells.lic"); 27 28 outFileName = outfilename; 29 book = new Workbook(); 30 //book.Open(tempfilename);//这里我们暂时不用模板 31 // 打开 Excel 模板 32 book = File.Exists(tempfilename) ? new Workbook(tempfilename) : new Workbook(); 33 sheet = book.Worksheets[0]; 34 } 35 /// <summary> 36 /// 保存文件 37 /// </summary> 38 39 /// <param name="name"></param> 40 public void SaveReprotFile( string name) 41 { 42 // book.Save(System.Web.HttpContext.Current.Response, "学生信息.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Xlsx)); 43 44 book.Save(System.Web.HttpContext.Current.Response, HttpUtility.UrlEncode(name, System.Text.Encoding.UTF8) + ".xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003)); 45 //try 46 //{ 47 // // filePath = Server.MapPath(filePath); 48 // if (File.Exists(name)) 49 // { 50 // FileInfo info = new FileInfo(name); 51 // long fileSize = info.Length; 52 // HttpContext.Current.Response.Clear(); 53 // HttpContext.Current.Response.ContentType = "application/octet-stream"; 54 // HttpContext.Current.Response.AddHeader("Content-Disposition", "attachement;filename=" + (info.FullName.)); 55 // //指定文件大小 56 // HttpContext.Current.Response.AddHeader("Content-Length", fileSize.ToString()); 57 // HttpContext.Current.Response.WriteFile(name, 0, fileSize); 58 // HttpContext.Current.Response.Flush(); 59 // } 60 //} 61 //catch 62 //{ } 63 64 //#region 输出到Excel 65 //using (MemoryStream ms = new MemoryStream()) 66 //{ 67 // // 生成的文件名称 68 // // string ReportFileName = string.Format("Excel_{0}.xls", DateTime.Now.ToString("yyyy-MM-dd") + name); 69 70 // book.Save(ms, new OoxmlSaveOptions(SaveFormat.Xlsx));//默认支持xls版，需要修改指定版本 71 // System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", name)); 72 // System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; 73 // System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray()); 74 // book = null; 75 // System.Web.HttpContext.Current.Response.End(); 76 //} 77 //#endregion 78 } 79 80 /// <summary> 81 /// 82 /// </summary> 83 /// <param name="outfilename">导出文件路径</param> 84 /// <param name="tempfilename">导入文件路径</param> 85 /// <param name="tempfilename">模板文件路径</param> 86 public AsposeExcel(string outfilename, string fullfilename, string tempfilename) //导出构造数 87 { 88 Aspose.Cells.License license = new Aspose.Cells.License(); 89 license.SetLicense("Aspose.Cells.lic"); 90 91 outFileName = outfilename; 92 fullFilename = fullfilename; 93 book = new Workbook(); 94 // book.Open(tempfilename);这里我们暂时不用模板 95 sheet = book.Worksheets[0]; 96 } 97 /// <summary> 98 /// 导入文件路径 99 /// </summary> 100 /// <param name="fullfilename"></param> 101 public AsposeExcel(string fullfilename) //导入构造数 102 { 103 Aspose.Cells.License license = new Aspose.Cells.License(); 104 license.SetLicense("Aspose.Cells.lic"); 105 106 fullFilename = fullfilename; 107 // book = new Workbook(); 108 // book.Open(tempfilename); 109 // sheet = book.Worksheets[0]; 110 } 111 private void AddTitle(string title, int columnCount) 112 { 113 sheet.Cells.Merge(0, 0, 1, columnCount); 114 sheet.Cells.Merge(1, 0, 1, columnCount); 115 Cell cell1 = sheet.Cells[0, 0]; 116 cell1.PutValue(title); 117 Style style = new Style(); 118 119 style.HorizontalAlignment = TextAlignmentType.Center; 120 style.Font.Name = "黑体"; 121 style.Font.Size = 14; 122 style.Font.IsBold = true; 123 Cell cell2 = sheet.Cells[1, 0]; 124 cell1.PutValue("查询时间：" + DateTime.Now.ToLocalTime()); 125 cell2.SetStyle(style); 126 } 127 private void AddHeader(DataTable dt) 128 { 129 Cell cell = null; 130 Style style = new Style(); 131 for (int col = 0; col < dt.Columns.Count; col++) 132 { 133 cell = sheet.Cells[0, col]; 134 cell.PutValue(dt.Columns[col].ColumnName); 135 style.Font.IsBold = true; 136 cell.SetStyle(style); 137 } 138 } 139 private void AddBody(DataTable dt) 140 { 141 Style style = new Style(); 142 for (int r = 0; r < dt.Rows.Count; r++) 143 { 144 for (int c = 0; c < dt.Columns.Count; c++) 145 { 146 sheet.Cells[r+1, c].PutValue(dt.Rows[r][c].ToString()); 147 style.Font.Name = "宋体"; 148 style.Font.Size = 11; 149 sheet.Cells[r + 1, c].SetStyle(style); 150 //r+数值,这个数值再加1表示从第几行开始 151 //sheet.Cells[r + 1, c].PutValue(dt.Rows[r][c].ToString()); 152 } 153 } 154 } 155 156 //导出------------下一篇会用到这个方法 157 public Boolean DatatableToExcel(DataTable dt) 158 { 159 Boolean yn = false; 160 try 161 { 162 //sheet.Name = sheetName; 163 //AddTitle(title, dt.Columns.Count); 164 //AddHeader(dt); 165 AddBody(dt); 166 sheet.AutoFitColumns(); 167 //sheet.AutoFitRows(); 168 book.Save(outFileName); 169 yn = true; 170 return yn; 171 } 172 catch (Exception e) 173 { 174 return yn; 175 // throw e; 176 } 177 } 178 public DataTable ExcelToDatatalbe()//导入 179 { 180 Workbook book = new Workbook(); 181 book.Open(fullFilename); 182 Worksheet sheet = book.Worksheets[0]; 183 Cells cells = sheet.Cells; 184 //获取excel中的数据保存到一个datatable中 185 DataTable dt_Import = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, false); 186 // dt_Import. 187 return dt_Import; 188 } 189 } 190 }