一、基于Aspose.Cells、Dapper导出Excel
Dapper的Query返回要不是对象的IEnumerable，要不是Dynamic的IEnumerable，都不适合不用反射就能够动态获取各个属性值得情况，而反射浪费性能是大伙儿共同认知的，Down下来Dapper源码发现返回Dynamic的IEnumerable，是返回一个Dapper内部的对象DapperRow的Dynamic的IEnumerable，而DapperRow是可以通过属性索引的，当然需要做一下小的改变：
这就好办了，如下是基于Aspose.Cells
关于Aspose.Cells可以基于excel模板来生成Excel，这点解释如下：
- &=DataSource.Field，&=[DataSource].[Field]是对DataTable或集合类型的引用，将会从当前行开始竖直向下生成多行数据。
- &=$data:是对变量或数组的引用。数组存在skip，horizontal等属性
- &=&=动态公式计算；{r}当前行，{c}当前列，{-n}，{n}当前行或列的偏移量前n或后n。
- &==是动态计算，如excel，if等语句。（if（logic_test,true_value,false_value））
public struct KeyValueDic { public KeyValueDic(string key,string value) { this.Key = key; this.Value = value; this.IsMutilText = false; this.MatchText = null; this.MatchTextDic = null; } public KeyValueDic(string key, string value,bool isMutilText) { this.Key = key; this.Value = value; this.IsMutilText = isMutilText; this.MatchText = null; this.MatchTextDic = null; } public KeyValueDic(string key, string value, string matchText) { Key = key; Value = value; IsMutilText = false; MatchText = matchText; MatchTextDic = null; MatchTextDic = GetDic(matchText); } public KeyValueDic(string key, string value, bool isMutilText, string matchText) { Key = key; Value = value; IsMutilText = isMutilText; MatchText = matchText; MatchTextDic = null; MatchTextDic = GetDic(matchText); } public string Key { get; set; }//列名 public string Value { get; set; }//列中文名 public bool IsMutilText { get; set; }//是否是大字段 public string MatchText { get; set; }//需要匹配的文本，例sex=0；1，0;男,1;女,other;未知 public Dictionary<string, string> MatchTextDic { get; private set; } private Dictionary<string, string> GetDic(string text) { if (string.IsNullOrEmpty(text)) return null; string[] _strs = text.Split(','); if (_strs.Length == 1) return null; Dictionary<string, string> _dic = new Dictionary<string, string>(); foreach (string item in _strs) { string[] _strItems = item.Split(';'); if (_strItems.Length != 2||string.IsNullOrEmpty(_strItems[0])) return null; _dic.Add(_strItems[0], _strItems[1]); } return _dic; } } public class ExcelUtils { private static Style HeadStyle(Workbook workbook)//头部样式 { //为单元格添加样式 Aspose.Cells.Style headStyle = workbook.Styles[workbook.Styles.Add()]; headStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; headStyle.Pattern = BackgroundType.Solid; headStyle.Font.IsBold = true; headStyle.ForegroundColor = System.Drawing.Color.Blue; headStyle.Font.Color = System.Drawing.Color.White; headStyle.Font.IsSubscript = true; headStyle.Font.Size = 16; headStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; headStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; headStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; headStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; return headStyle; } private static Style BodyStyle(Workbook workbook)//文本样式 { Aspose.Cells.Style bodyStyle = workbook.Styles[workbook.Styles.Add()]; bodyStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; bodyStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; bodyStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; bodyStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; bodyStyle.IsTextWrapped = true; return bodyStyle; } public static Stream SaveToStream(IEnumerable<DapperRow> list, List<KeyValueDic> listColumns, string sheetName) { if (list == null || list.Count() == 0 || listColumns == null || listColumns.Count == 0 || listColumns.Count > list.ElementAt(0).Count()) throw new Exception("Excel数据源不匹配导致无法保存！"); Workbook workbook = new Workbook(); Worksheet sheet = (Worksheet)workbook.Worksheets[0]; sheet.Name = sheetName; for (int i = 0; i < listColumns.Count; i++) { sheet.Cells[0, i].PutValue(listColumns[i].Value); if (listColumns[i].IsMutilText) sheet.Cells.SetColumnWidth(i, 50); } sheet.Cells.CreateRange(0, 0, 1, listColumns.Count).SetStyle(HeadStyle(workbook)); int row = 0; foreach (var item in list) { row++; for (int i = 0; i < listColumns.Count; i++) { object val = item[listColumns[i].Key]; if (val == null) continue; if (val.GetType() == typeof(DateTime))//时间格式 { DateTime dtime = (DateTime)val; if (dtime.Hour == 0 || dtime.Minute == 0 || dtime.Second == 0) sheet.Cells[row, i].PutValue(dtime.ToString("yyyy-MM-dd")); else sheet.Cells[row, i].PutValue(dtime.ToString("yyyy-MM-dd HH:mm:ss")); } else if (listColumns[i].MatchText != null && listColumns[i].MatchTextDic != null)//是否有需要匹配的文本 { string _val = val.ToString(); if (listColumns[i].MatchTextDic.ContainsKey(_val)) sheet.Cells[row, i].PutValue(listColumns[i].MatchTextDic[_val]); else sheet.Cells[row, i].PutValue(listColumns[i].MatchTextDic["other"]); } else sheet.Cells[row, i].PutValue(val.ToString()); } } sheet.Cells.CreateRange(1, 0, row, listColumns.Count).SetStyle(BodyStyle(workbook)); for (int i = 0; i < listColumns.Count; i++) { if (!listColumns[i].IsMutilText) sheet.AutoFitColumn(i); } sheet.AutoFitRows(); MemoryStream ms = new MemoryStream(); workbook.Save(ms, SaveFormat.Excel97To2003); ms.Position = 0; return ms; } }
logic:
public static class Utils { private static log4net.ILog log = log4net.LogManager.GetLogger("FMDS"); /// <summary> /// 将指定的字符串信息写入日志。 /// </summary> /// <param name="message">需要写入日志的字符串信息。</param> public static void Log(string message) { log.Info(message); } /// <summary> /// 将指定的异常实例详细信息写入日志 /// </summary> public static void Log(Exception ex) { log.Error("Exception:", ex); } /// <summary> /// 将指定的异常实例详细信息写入日志。 /// </summary> public static void Log(object message, Exception e) { log.Error(message, e); } public static string TrimOrNull(this string str) { return str == null ? null : str.Trim(); } } public class DbConnection { public static SqlConnection OpenConnection() { if (string.IsNullOrEmpty(ConfigUtils.ConStr)) { Utils.Log("数据库连接失败：没有读取到FMDS链接字符串。"); return null; } SqlConnection connection = new SqlConnection(ConfigUtils.ConStr); try { connection.Open(); return connection; } catch (Exception ex) { Utils.Log("数据库FMDS连接失败：" + ex.Message); return null; } } } public Stream Export() { string sql = @""; var param = new DynamicParameters(); param.Add("mydate", DateTime.Today, System.Data.DbType.DateTime); using (SqlConnection conn = DbConnection.OpenFMJConnection()) { if (conn == null) return null; var list=conn.Query<SqlMapper.DapperRow>(sql, param); List<KeyValueDic> listKeyValue = new List<KeyValueDic>() { new KeyValueDic("Column1","列1"), new KeyValueDic("Column2","列2","0;男,1;女,other;未知"), new KeyValueDic("Column3","列3",true) }; System.IO.Stream stream = ExcelUtils.SaveToStream(list, listKeyValue, "excelSheetName"); return stream; } } Action中使用： public FileResult Export() { Stream stream = logic.Export(); return File(stream, "application/vnd.ms-excel", DateTime.Now.ToString("yyyyMMddHHmmss") + "—excelName.xls"); }//"application/ms-excel", View中使用： window.location.href = "@Url.Action("Export")"; 二、基于System.Web.UI.WebControls.DataGrid、Ado.net导出Excel public void ExportExcel(DataTable dt, string filename) { StringWriter stringWriter = new StringWriter(); HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter); //使用System.Web.UI.WebControls的DataGrid导出excel DataGrid excel = new DataGrid(); System.Web.UI.WebControls.TableItemStyle headerStyle = new TableItemStyle();//标题行样式 headerStyle.BackColor = System.Drawing.Color.LightGray; headerStyle.Font.Bold = true; headerStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center; excel.HeaderStyle.MergeWith(headerStyle); System.Web.UI.WebControls.TableItemStyle alternatingStyle = new TableItemStyle();//隔行样式 alternatingStyle.BackColor = System.Drawing.Color.LightGray; excel.AlternatingItemStyle.MergeWith(alternatingStyle); System.Web.UI.WebControls.TableItemStyle itemStyle = new TableItemStyle();//单元格样式 itemStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center; excel.ItemStyle.MergeWith(itemStyle); excel.GridLines = GridLines.Both; excel.HeaderStyle.Font.Bold = true; excel.DataSource = dt.DefaultView;//输出DataTable的内容 excel.DataBind(); excel.RenderControl(htmlWriter); MemoryStream stream = new MemoryStream(); System.IO.StreamWriter sw = new StreamWriter(stream, System.Text.Encoding.UTF8); sw.Write(stringWriter.ToString()); sw.Flush(); DownResult(stream, filename); } public ActionResult DownResult(Stream stream,string fileName) { stream.Position = 0; const int ChunkSize = 1024000; byte[] buffer = new byte[ChunkSize]; long dataLengthToRead = stream.Length; Response.Clear(); Response.Charset = "UTF-8"; Response.ContentType = "application/octet-stream"; Response.AppendHeader("content-disposition", string.Format("attachment;filename={0}.xls", fileName)); Response.AppendHeader("Content-Length", dataLengthToRead.ToString()); Response.Filter.Close(); while (dataLengthToRead > 0 && Response.IsClientConnected) { int lengthRead = stream.Read(buffer, 0, ChunkSize); Response.OutputStream.Write(buffer, 0, lengthRead); Response.Flush(); dataLengthToRead = dataLengthToRead - lengthRead; } stream.Close(); stream.Dispose(); Response.Flush(); /***** 这里可以做一些自己的处理，比如把谁下载的文件信息存到库里，但是这个时候，就不能用Response.Close();了，否则报下载失败，网络错误。这里可以使用如下(需放在自己的处理程序之后)：
参考：https://blogs.msdn.microsoft.com/aspnetue/2010/05/25/response-end-response-close-and-how-customer-feedback-helps-us-improve-msdn-documentation/
http://stackoverflow.com/questions/5533068/httpcontext-throws-httpexception
http://stackoverflow.com/questions/1087777/is-response-end-considered-harmful
if (Response.IsClientConnected) { Response.SuppressContent = true;//Prevents any other content from being sent to the browser HttpContext.ApplicationInstance.CompleteRequest();//Directs the thread to finish, bypassing additional processing } GC.Collect(); *****/ Response.Close();//不要直接用Response.End(); return new EmptyResult(); }
附：以上代码偶尔仍回抛如下异常“System.Web.HttpException (0x80070057): 远程主机关闭了连接。错误代码是 0x80070057。”
突然在异常错误日志中看到这个错误，虽然在测试中发现不影响流的传输，但是不代表没错误，解决方法如下： Response.Flush(); Response.End();//结束文件下载，但是程序在运行时会报出“正在中止线程”的错误，可以确定的是引起错误的代码就是Response.End()，所以需要删除此方法。 在IIS 5.5下这样做问题就解决了，不过在IIS 7.0 下又会报出另外一个错误“与远程主机通信时发生错误。错误代码是 0x800704CD。” 在网上翻查了一翻资料后，终于找到了解决方法： 就是将Response.End();换成Response.Close(); 总结一下，帮助对Response.Close的解释是关闭到客户端的连接。对Response.End的解释是停止该页的执行，并引发Application_EndRequest。也就说用Response.End程序就直接停止后面的工作了转而触发Application_EndRequest，那么当程序在后面还有代码需要运行时，程序就会抛出ThreadAbortException的异常。还有需要了解的就是end方法在不抛出异常时还会调用flush的方法。接着来，close方法是关闭了连接，也就说程序顺利执行完了所有代码后关闭了连接。对于只运行flush后报出的“与远程主机通信时发生错误”，我的理解是当执行了flush后在底层马上开始向客户端发送数据，但是flush貌似只能指示 程序开始连接发送，却没有停止关闭的标识，导致程序报出异常。当然这是在IIS 7.0下会出错（可能在IIS 7.0 开始在调用flush后需要显示关闭socket连接）。所以以后再在用完flush后最好加上close。就像数据库连接一样，用完就关闭连接。
如下是Response.End()的source code：
public void End() { if (this._context.IsInCancellablePeriod) { InternalSecurityPermissions.ControlThread.Assert(); Thread.CurrentThread.Abort(new HttpApplication.CancelModuleException(false)); } else if (!this._flushing) { this.Flush(); this._ended = true; if (this._context.ApplicationInstance != null) { this._context.ApplicationInstance.CompleteRequest(); } } }
也就是说使用Response.End()是包含了Response.Flush()的功能的。
附：Response.End()同样是报（偶尔情况下）：
at System.Web.Hosting.IIS7WorkerRequest.RaiseCommunicationError(Int32 result, Boolean throwOnDisconnect)
at System.Web.Hosting.IIS7WorkerRequest.ExplicitFlush()
at System.Web.HttpResponse.Flush(Boolean finalFlush)
at System.Web.HttpResponse.Flush()
修改为如下（待测试吧）：
//Response.End(); HttpContext.ApplicationInstance.CompleteRequest();