网站开发专业课程,软件工程师发展前景,专业的外贸行业网站设计,最新长沙招聘信息为了方便代码编写和测试#xff0c;把很多代码都放在一个class里面#xff0c;实际开发根据需要放到对应的目录下即可。
1.使用nuget下载安装miniexcel#xff1b;
2.编写对应的测试接口#xff0c;具体代码如下:
using Microsoft.AspNetCore.Authorization;
using Micr…为了方便代码编写和测试把很多代码都放在一个class里面实际开发根据需要放到对应的目录下即可。
1.使用nuget下载安装miniexcel
2.编写对应的测试接口具体代码如下:
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.IO;
using System.Threading.Tasks;
using System;
using AutoMapper;
using MiniExcelLibs.Attributes;
using MiniExcelLibs;
using MiniExcelLibs.OpenXml;
using System.Linq;namespace YY.Webapi.Controllers
{/// summary/// miniexcel测试/// /summary[Route(api/[controller])][ApiController][Produces(application/json)][AllowAnonymous]public class MiniExcelController : ControllerBase{private readonly IMapper _mapper;public MiniExcelController(IMapper mapper){_mapper mapper;}/// summary/// 固定列导出/// /summary/// param nameinput/param/// returns/returns/// exception crefException/exception[HttpPost(Export)]public async TaskIActionResult Export(){try{var models new Custome().GetProducts();var exportDtos _mapper.MapListCustomeExportDto(models);var memoryStream new MemoryStream();memoryStream.SaveAs(exportDtos);memoryStream.Seek(0, SeekOrigin.Begin);return new FileStreamResult(memoryStream, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet){FileDownloadName $固定列报表导出-{DateTime.Now.ToString(yyyyMMddHHmmss)}.xlsx};}catch (Exception ex){throw new Exception($固定列报表导出出现错误:{ex.Message});}}/// summary/// 动态列导出(指定列导出)/// /summary/// param namecolumnParams/param/// returns/returns/// exception crefException/exception[HttpPost(ExportByAssignColumn)][AllowAnonymous]public async TaskIActionResult ExportByAssignColumn(ListCustomeParam columnParams){try{if (columnParams null || !columnParams.Any()) throw new Exception(请选择需要导出的列!);var dtos new Custome().GetProducts();#region 配置var config new OpenXmlConfiguration { };ListDynamicExcelColumn objs new ListDynamicExcelColumn();int index 0;foreach (var columnParam in columnParams){objs.Add(new DynamicExcelColumn(columnParam.ColumnDisplayName) { Index index, Width columnParam.ColumnWidth });}config.DynamicColumns objs.ToArray();#endregion#region 获取值var values new ListDictionarystring, object();foreach (var dto in dtos){var dic new Dictionarystring, object();foreach (var columnParam in columnParams){dic.Add(columnParam.ColumnDisplayName, GetModelValue(columnParam.ColumnName, dto));}values.Add(dic);}#endregionvar memoryStream new MemoryStream();memoryStream.SaveAs(values, configuration: config);memoryStream.Seek(0, SeekOrigin.Begin);return new FileStreamResult(memoryStream, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet){FileDownloadName $动态列报表导出-{DateTime.Now.ToString(yyyyMMddHHmmss)}.xlsx};}catch (Exception ex){throw new Exception($动态列报表导出错误:{ex.Message});}}/// summary/// 按模板导出/// /summary/// param nameid/param/// returns/returns/// exception crefException/exception[HttpPost({id}/ExportByTemplate)]public async TaskIActionResult ExportByTemplate([FromRoute] int id){try{var entity new Custome() { Id 1, Code Code, Name Test, Price 12, CreateTime DateTime.Now };string templatePath $C:\Users\Administrator\Desktop\报表模板.xlsx;var value new{Code entity.Code,Name entity.Name};byte[] bytes System.IO.File.ReadAllBytes(templatePath);var memoryStream new MemoryStream();await memoryStream.SaveAsByTemplateAsync(bytes, value);memoryStream.Seek(0, SeekOrigin.Begin);return new FileStreamResult(memoryStream, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet){FileDownloadName $按模板报表导出-{DateTime.Now.ToString(yyyyMMddHHmmss)}.xlsx};}catch (Exception ex){throw new Exception($按模板报表导出错误:{ex.Message});}}#region 私有方法/// summary/// 根据字段名获取对应的值/// /summary/// param namefieldName/param/// param nameobj/param/// returns/returnsprivate string GetModelValue(string fieldName, object obj){try{object o obj.GetType().GetProperty(fieldName).GetValue(obj, null);string Value Convert.ToString(o);if (string.IsNullOrEmpty(Value)) return ;return Value;}catch{return ;}}#endregion}public class Custome{/// summary/// 产品Id/// /summarypublic int Id { get; set; }/// summary/// 产品编码/// /summarypublic string Code { get; set; }/// summary/// 产品名称/// /summarypublic string Name { get; set; }/// summary/// 价格/// /summarypublic int Price { get; set; }/// summary/// 创建时间/// /summarypublic DateTime CreateTime { get; set; }public ListCustome GetProducts(){var products new ListCustome();for (int i 0; i 1000; i){products.Add(new Custome{Id i 1,Code $Code-{(i 1).ToString()},Name $Name-{(i 1).ToString()},Price Random.Shared.Next(10, 100),CreateTime DateTime.Now});}return products;}}public class CustomeExportDto{/// summary/// 产品编码/// /summary[ExcelColumn(Name 产品编码, Width 12)]public string Code { get; set; }/// summary/// 产品名称/// /summary[ExcelColumn(Name 产品名称, Width 12)]public string Name { get; set; }/// summary/// 价格/// /summary[ExcelColumn(Name 价格, Width 12)]public int Price { get; set; }/// summary/// 创建时间/// /summary[ExcelColumn(Name 创建时间, Width 12, Format yyyy-MM-dd HH:mm:ss)]public DateTime CreateTime { get; set; }}public class CustomeParam{/// summary/// 列名/// /summarypublic string ColumnName { get; set; }/// summary/// 列显示名/// /summarypublic string ColumnDisplayName { get; set; }/// summary/// 列宽/// /summarypublic double ColumnWidth { get; set; }}
}
3.固定列和按模板导出Excel都比较常规其中的动态列导出是根据前端传进来的参数进行选择性的动态列导出,swagger测试效果如下: 导出的文件如下:
