首页 > 代码库 > mvc Dapper_Report_Down_ExcelFile
mvc Dapper_Report_Down_ExcelFile
Dapper的Query返回要不是对象的IEnumerable,要不是Dynamic的IEnumerable,都不适合不用反射就能够动态获取各个属性值得情况,而反射浪费性能是大伙儿共同认知的,Down下来Dapper源码发现返回Dynamic的IEnumerable,是返回一个Dapper内部的对象DapperRow的Dynamic的IEnumerable,而DapperRow是可以通过属性索引的,当然需要做一下小的改变:
这就好办了,如下是基于Aspose.Cells
public struct KeyValueDic { public KeyValueDic(string key,string value) { this.Key = key; this.Value =http://www.mamicode.com/ value; this.IsMutilText = false; this.MatchText = null; this.MatchTextDic = null; } public KeyValueDic(string key, string value,bool isMutilText) { this.Key = key; this.Value =http://www.mamicode.com/ 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 = http://www.mamicode.com/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 = http://www.mamicode.com/"@Url.Action("Export")";
mvc Dapper_Report_Down_ExcelFile
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。