• 利用 NUget包 EPPlus 实现数据导出到Excel(适用于MVC)


    第一步:为项目安装Epplus Nuget包。

    Install-Package EPPlus 

    第二步:在data项目中新建Repository,命名为ExcelWriter.cs.

    将以下代码粘入,修改项目引用名字。

    using System;
    using OfficeOpenXml;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.IO;
    using System.Linq;
    
    namespace ****.Data.Repositories
    {
        public class ExcelWriter<TEntity>  where TEntity :class 
        {
            public void WriteToFileStream(IEnumerable<TEntity> list, out MemoryStream memoryStream)
            {
                var pack = new ExcelPackage();
                var ws = pack.Workbook.Worksheets.Add("预约时间表");
    
                var col = 1;
                var row = 1;
                foreach (var propertyInfo in typeof(TEntity).GetProperties())
                {
                    var displayName = propertyInfo.Name;
                    ws.Cells[row, col].Value = displayName;
                    col++;
                }
    
                row++;
    
                foreach (var coachBatchPayoutExcelModel in list)
                {
    
                    for (var i = 1; i < col; i++)
                    {
                        var colName = ws.Cells[1, i].Value.ToString();
                        ws.Cells[row, i].Value = coachBatchPayoutExcelModel.GetType().GetProperty(colName).GetValue(coachBatchPayoutExcelModel, null);
                    }
                    row++;
                }
    
                foreach (var propertyInfo in typeof(TEntity).GetProperties())
                {
                    var attribute = propertyInfo.GetCustomAttributes(typeof(DisplayNameAttribute), true).Cast<DisplayNameAttribute>().SingleOrDefault();
                    if (attribute != null)
                    {
                        ws.Cells.First(item => item.GetValue<string>() == propertyInfo.Name).Value = attribute.DisplayName;
                    }
                } 
                ws.Column(6).Style.Numberformat.Format = "yyyy年MM月dd日";
                ws.Column(1).Width = 20;
                ws.Column(2).Width = 15;
                ws.Column(3).Width = 20;
                ws.Column(4).Width = 25;
                ws.Column(5).Width = 6;
                ws.Column(6).Width = 15;
                memoryStream = new MemoryStream(pack.GetAsByteArray());
            }
        }
    }

    以上为一泛型,如何使用请参考以下使用样例。

      #region 下载数据
    
                Mapper.CreateMap<OnlineBookingInfo, ExcelModel>();
                var excels = Mapper.Map<List<OnlineBookingInfo>, List<ExcelModel>>(onlineBookingInfos);
    
                MemoryStream memoryStream;
                var excelWriter = new ExcelWriter<ExcelModel>();
                excelWriter.WriteToFileStream(excels, out memoryStream);
                return File(memoryStream, "application/vnd.ms-excel", "郑州科技馆预约参观名单" + DateTime.Now.Date.ToString("yyyy_MM_dd") + ".xls");
      #endregion

    先将需要导出的数据使用mapper,映射到新的实体类型(只需要导出的实体,此处的可以为新建)

    excel的title为新建实体的dispalyname的名字。

    使用的时候先

    MemoryStream memoryStream;

    var excelWriter = new ExcelWriter<ExcelModel>();

    然后

    excelWriter.WriteToFileStream(excels, out memoryStream);

    最后输出

    return File(memoryStream, "application/vnd.ms-excel", "郑州科技馆预约参观名单" + DateTime.Now.Date.ToString("yyyy_MM_dd") + ".xls");

     
  • 相关阅读:
    jar包和war包的区别:
    tail
    redis
    查看Linux操作系统版本
    CentOS 7.0 systemd代替service
    周刊(三月最后一期)
    周刊第四期
    周刊第三期
    周刊第二期
    周刊(第一期)
  • 原文地址:https://www.cnblogs.com/WZH75171992/p/4562494.html
Copyright © 2020-2023  润新知