C# WebApi导出Excel 直接返回一个远程Excel地址 链接访问下载

本文介绍了一种将方案列表导出至Excel的方法,包括创建Excel文件、转换数据格式及生成下载链接等步骤。
        /// <summary>
        /// 导出方案列表
        /// </summary>
        [HttpGet, HttpPost]
        public Object ExportProgramme(ExportProgrammeReq req)
        {
            var virtualPath = "/UploadFile/Export/";
            var path = HttpContext.Current.Server.MapPath(virtualPath);
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            var name = "快报名方案列表.xlsx"; //Excel名字
            path += name;
            var models = ProgrammerService.ExportProgramme(req);
            var viewModels = new ExportProgrammeViewModel().GetViewModels(models);
            var dataTable = ExcelHelp.ListToDataTable(viewModels);
            //datatable转成字节流            
            var bytes = ExcelHelp.GetExcelForXLSX(dataTable, GetColumnName);
            var stream = new MemoryStream(bytes);

            using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                byte[] data = stream.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
                data = null;
            }
            stream.Close();
            stream.Dispose();
            
            return PictureHelper.ConcatPicUrl(virtualPath + name); ;

        }

        /// <summary>
        /// 根据字段名称获取对应的列名
        /// </summary>
        public static string GetColumnName(string columnName)
        {
            var convertColumnName = string.Empty;
            switch (columnName)
            {
                case "ProgrammeName":
                    convertColumnName = "方案名称";
                    break;
                case "TypeName":
                    convertColumnName = "方案类型";
                    break;
                case "PassDate":
                    convertColumnName = "过期时间";
                    break;
                case "Status":
                    convertColumnName = "状态";
                    break;
                case "State":
                    convertColumnName = "是否启用";
                    break;
                case "ViewCount":
                    convertColumnName = "浏览次数";
                    break;
                case "EnrollCount":
                    convertColumnName = "报名人数";
                    break;
                case "ToOtherCount":
                    convertColumnName = "转赠次数";
                    break;
            }
            return convertColumnName;
        }


 public static DataTable ListToDataTable<T>(List<T> entitys)
        {
            //生成DataTable的structure
            //生产代码中,应将生成的DataTable结构Cache起来,此处略
            var dt = new DataTable();

            //检查实体集合不能为空 todo:
            if (entitys == null || entitys.Count < 1)
            {
                return dt;
            }
            //取出第一个实体的所有Propertie
            var entityType = entitys[0].GetType();
            var entityProperties = entityType.GetProperties();

            for (var i = 0; i < entityProperties.Length; i++)
            {
                //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
                dt.Columns.Add(entityProperties[i].Name);
            }
            //将所有entity添加到DataTable中
            foreach (object entity in entitys)
            {
                //检查所有的的实体都为同一类型
                if (entity.GetType() != entityType)
                {
                    throw new Exception("要转换的集合元素类型不一致");
                }
                var entityValues = new object[entityProperties.Length];
                for (var i = 0; i < entityProperties.Length; i++)
                {
                    entityValues[i] = entityProperties[i].GetValue(entity, null);
                }
                dt.Rows.Add(entityValues);
            }
            return dt;

        }

        //获取列名委托方法
        public delegate string GetColumnName(string columnName);

public static byte[] GetExcelForXLSX(DataTable dt, GetColumnName getColumnName)
        {
            var xssfworkbook = new XSSFWorkbook();
            var sheet = xssfworkbook.CreateSheet("Sheet");
            //表头
            var row = sheet.CreateRow(0);

            for (var i = 0; i < dt.Columns.Count; i++)
            {
                var cell = row.CreateCell(i);
                //列名称,数据库中字段
                var columnName = dt.Columns[i].ColumnName;
                var convertColumnName = getColumnName(columnName);
                cell.SetCellValue(convertColumnName);
            }

            //数据
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                var row1 = sheet.CreateRow(i + 1);
                for (var j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            //转为字节数组
            var stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();
            return buf;

        }

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值