mvc Dapper_Report_Down_ExcelFile

首页 > 代码库 > mvc Dapper_Report_Down_ExcelFile

mvc Dapper_Report_Down_ExcelFile

2024-09-11 05:09:42 218人阅读

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个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > mvc Dapper_Report_Down_ExcelFile

mvc Dapper_Report_Down_ExcelFile

看完仍有疑问？有类似问题直接问程序猿