当前位置： 首页 > news >正文

C#用Aspose.Cells导出Excel，.NET导出Excel

news 2025/8/5 20:18:18

ASP.NET MVC 控制器里面Action处理，下载文件，输出文件流

   public async Task<ActionResult> ExportNewsAuthorFee(string deptId, DateTime? startDate, DateTime? endDate){if (startDate == null){startDate = DateTime.Parse(DateTime.Now.Year + "-1-1");}string title = startDate.Value.ToString("yyyy-MM-dd");if (endDate.HasValue){title += "至"+endDate.Value.ToString("yyyy-MM-dd");}var list = await newsStatiscBll.GetUserChannelRoyaltiesListAsync(deptId, startDate, endDate);List<List<string>> excelData = new List<List<string>>();//标题设置,第一行List<string> titleList = new List<string>();           titleList.Add("姓名");titleList.Add("员工编号");titleList.Add("部门");titleList.Add("院");titleList.Add("");titleList.Add("小计");titleList.Add("分");titleList.Add("");titleList.Add("");titleList.Add("小计");titleList.Add("门");titleList.Add("网");for (int q = 0; q < 6; q++){titleList.Add("");}titleList.Add("小计");titleList.Add("电视");titleList.Add("");titleList.Add("");titleList.Add("小计");titleList.Add("绿色1");titleList.Add("绿色2");titleList.Add("绿色3");titleList.Add("西1");titleList.Add("西2");titleList.Add("西3");titleList.Add("川3");titleList.Add("川4");titleList.Add("川5");titleList.Add("铁6");titleList.Add("铁7");titleList.Add("铁8");titleList.Add("省9");titleList.Add("省12");titleList.Add("省11");titleList.Add("小计");titleList.Add("总计");excelData.Add(titleList);//第二行List<string> titleList2 = new List<string>();titleList2.Add("");titleList2.Add("");titleList2.Add("");titleList2.Add("摄1");titleList2.Add("文2");titleList2.Add("");titleList2.Add("气1");titleList2.Add("川2");titleList2.Add("摄3");titleList2.Add("");titleList2.Add("");titleList2.Add("地简讯");titleList2.Add("地非简讯");titleList2.Add("省简讯");titleList2.Add("省非简讯");titleList2.Add("简讯1");titleList2.Add("非简讯2");titleList2.Add("国");titleList2.Add("");titleList2.Add("地市1");titleList2.Add("省部2");titleList2.Add("国家3");titleList2.Add("");for (int n = 0; n< 17; n++){titleList2.Add("");}excelData.Add(titleList2);//数据封装foreach (var item in list){List<string> cellDataList  = new List<string>();// 小计decimal xj_yw1 = item.FeeAnPhotography + item.FeeAnLiterary;// 小计decimal xj_fgs = item.FeeFmGasField + item.FeeFmChuanyouFigures + item.FeeFmPhotography;//报刊小计decimal xj_wl = item.FeeNewspapers_disShort+ item.FeeNewspapers_disImgTxt+ item.FeeNewspapers_provinceShort+ item.FeeNewspapers_provinceShort+ item.FeeNewspapers_nationalShort+ item.FeeNewspapers_nationalImgTxt+ item.FeeNewspapers_PetroChina;//电视新闻，小计decimal xj_tv = item.FeeTV_dis + item.FeeTV_province + item.FeeTV_national;//【小计】  decimal xj_mt2 = item.FeeGreen_author+ item.FeeGreen_edit+ item.FeeGreen_img+ item.FeeXi_author+ item.FeeXi_edit+ item.FeeXi_img+ item.FeeZChuanyouFigures_author+ item.FeeZChuanyFigures_edit+ item.FeeZChuanyouFigures_img+ item.FeeZIronMan_author+ item.FeeZIronMan_edit+ item.FeeZIronMan_img+ item.FeeZProvinNewMedia_author+ item.FeeZProvinNewMedia_edit+ item.FeeZProvinNewMedia_img;decimal total = xj_yw1 + xj_fgs + xj_wl + xj_tv + xj_mt2;cellDataList.Add(item.UserRealName); /* 姓名*/cellDataList.Add(item.UserNo); /* 员工编号*/cellDataList.Add(item.DeptName); /* 部门*/cellDataList.Add(item.FeeAnPhotography.ToString()); /*  作品*/cellDataList.Add(item.FeeAnLiterary.ToString()); /* 作品*/cellDataList.Add(xj_yw1.ToString()); /*  小计*/cellDataList.Add(item.FeeFmGasField.ToString()); /*  气田风采*/cellDataList.Add(item.FeeFmChuanyouFigures.ToString()); /*  川油人物*/cellDataList.Add(item.FeeFmPhotography.ToString()); /*  摄影作品*/cellDataList.Add(xj_fgs.ToString()); /*  小计 */cellDataList.Add(item.FeeGpHome.ToString()); /*  门户  */cellDataList.Add(item.FeeNewspapers_disShort.ToString()); /*   刊_简讯*/cellDataList.Add(item.FeeNewspapers_disImgTxt.ToString()); /*  报刊_非简讯 */cellDataList.Add(item.FeeNewspapers_provinceShort.ToString()); /*  报刊_简讯 */cellDataList.Add(item.FeeNewspapers_provinceImgTxt.ToString()); /*  报刊_非简讯 */cellDataList.Add(item.FeeNewspapers_nationalShort.ToString()); /*  报刊_简讯 */cellDataList.Add(item.FeeNewspapers_nationalImgTxt.ToString()); /*  报刊_非简讯 */cellDataList.Add(item.FeeNewspapers_PetroChina.ToString()); /* 油*/cellDataList.Add(xj_wl.ToString()); /* 报刊小记 *///地cellDataList.Add(item.FeeTV_dis.ToString()); /* 地市级 */cellDataList.Add(item.FeeTV_province.ToString()); cellDataList.Add(item.FeeTV_national.ToString());cellDataList.Add(xj_tv.ToString()); /* 报刊小记 *///绿色cellDataList.Add(item.FeeGreen_author.ToString());cellDataList.Add(item.FeeGreen_edit.ToString());cellDataList.Add(item.FeeGreen_img.ToString());//西cellDataList.Add(item.FeeXi_author.ToString());cellDataList.Add(item.FeeXi_edit.ToString());cellDataList.Add(item.FeeXi_img.ToString());//川cellDataList.Add(item.FeeZChuanyouFigures_author.ToString());cellDataList.Add(item.FeeZChuanyFigures_edit.ToString());cellDataList.Add(item.FeeZChuanyouFigures_img.ToString());//铁cellDataList.Add(item.FeeZIronMan_author.ToString());cellDataList.Add(item.FeeZIronMan_edit.ToString());cellDataList.Add(item.FeeZIronMan_img.ToString());//省cellDataList.Add(item.FeeZProvinNewMedia_author.ToString());cellDataList.Add(item.FeeZProvinNewMedia_edit.ToString());cellDataList.Add(item.FeeZProvinNewMedia_img.ToString());cellDataList.Add(xj_mt2.ToString()); /*  小计 */cellDataList.Add(total.ToString()); /*  总计 */excelData.Add(cellDataList);}//单元格设置合并，标题第1行Action<Worksheet> ac = sheet =>{    sheet.Cells.Merge(0, 0, 2, 1); /*姓名，单元格合并*/sheet.Cells.Merge(0, 1, 2, 1); /*员工编号，单元格合并*/sheet.Cells.Merge(0, 2, 2, 1); /*部门，单元格合并*/sheet.Cells.Merge(0, 3, 1, 2); /*院网员工天地，单元格合并*/sheet.Cells.Merge(0, 5, 2, 1);/*小计，单元格合并*/sheet.Cells.Merge(0, 6, 1, 3);/*分公司门户，单元格合并*/sheet.Cells.Merge(0, 9,2, 1);/*小计，单元格合并*/sheet.Cells.Merge(0, 10,2, 1);/*集，单元格合并*/sheet.Cells.Merge(0, 11,1, 7);/*网，单元格合并*/sheet.Cells.Merge(0, 18,2,1);/*网，单元格合并*/sheet.Cells.Merge(0, 19,1,3);/*电，单元格合并*/sheet.Cells.Merge(0, 22,2,1);/*电，单元格合并*/sheet.Cells.Merge(0, 23,2,1);/*绿色，单元格合并*/for (int i = 24; i < 40; i++) {sheet.Cells.Merge(0, i, 2, 1);/* 单元格合并*/}};Stream stream = new MemoryStream();Export.ExportExcelHelpter.ExportAuthorExcelBig(excelData, ref stream, ac, null);string fileName = "作者稿费统计(" + title + ")" + Guid.NewGuid().ToString("N").Substring(0,4) + ".xlsx";return File(stream, "application/ocelet-stream", fileName);}

处理导出

using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Threading.Tasks;
using Aspose.Cells;/// <summary>
/// 导出Excel，ac传入委托设置Cells格式，比如合并；比如单元格宽度设置等。
/// </summary>
/// <param name="data">数据</param> 
/// <param name="stream"></param>
/// <param name="stream">委托，设置Cells格式，比如合并；比如单元格宽度设置等</param>
/// <param name="afterAc">委托，数据已经插入后，设置单元格样式，比如列宽、列高配置</param>
/// 创建时间：2024-7-25 17:41:56， xxx
public static void ExportAuthorExcelBig(List<List<string>> data , ref Stream stream, Action<Worksheet> ac, Action<Cells> afterAc)
{Workbook wb = new Workbook();Worksheet sheet = wb.Worksheets[0];sheet.Name = "作者统计分析";Cells cells = sheet.Cells;Style style = wb.Styles[wb.Styles.Add()];style.Font.Size = 12;cells.ApplyStyle(style, new StyleFlag() { All = true });int cols = data[0].Count;//int cols = colsCount;//标题样式Style styleTitle = wb.Styles[wb.Styles.Add()];styleTitle.Font.IsBold = true;styleTitle.Font.Size = 12;//styleTitle.Font.Color = Color.Blue;/* 字体颜色*/styleTitle.ForegroundColor = Color.SpringGreen;/* 背景色 */styleTitle.Pattern = BackgroundType.Solid;/* 背景色 *///边框设置styleTitle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;styleTitle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;styleTitle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;styleTitle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;//styleTitle.Borders.SetStyle(CellBorderType.Thick);/*单元格边框有线，中间被叉叉封起*/styleTitle.HorizontalAlignment = TextAlignmentType.Center;styleTitle.VerticalAlignment = TextAlignmentType.Center;Range range = cells.CreateRange(0, 0, 2, cols);range.ApplyStyle(styleTitle, new StyleFlag() { All = true });#region 合并表头单元格//调用传入委托，ac?.Invoke(sheet);第一行//sheet.Cells.Merge(0, 0, 3, 1);//合并第一行第一列开始 合并3行1列//sheet.Cells.Merge(0, 1, 3, 1);//sheet.Cells.Merge(0, 2, 3, 1);//sheet.Cells.Merge(0, 3, 3, 1);//sheet.Cells.Merge(0, 4, 3, 1);//sheet.Cells.Merge(0, 5, 3, 1);//sheet.Cells.Merge(0, 6, 1, 64);第二行//sheet.Cells.Merge(1, 6, 1, 4);//sheet.Cells.Merge(1, 10, 1, 4);//sheet.Cells.Merge(1, 14, 1, 4);//sheet.Cells.Merge(1, 18, 1, 4);//sheet.Cells.Merge(1, 22, 1, 4);//sheet.Cells.Merge(1, 26, 1, 4);//sheet.Cells.Merge(1, 30, 1, 4);//sheet.Cells.Merge(1, 34, 1, 4);//sheet.Cells.Merge(1, 38, 1, 4);//sheet.Cells.Merge(1, 42, 1, 4);//sheet.Cells.Merge(1, 46, 1, 4);//sheet.Cells.Merge(1, 50, 1, 4);//sheet.Cells.Merge(1, 54, 1, 4);//sheet.Cells.Merge(1, 58, 1, 4);//sheet.Cells.Merge(1, 62, 1, 4);//sheet.Cells.Merge(1, 66, 1, 4);#endregionobject[,] dataArr2 = new object[data.Count, cols];for (int n = 0; n < data.Count; n++){var rowLine = data[n];for (int j = 0; j < rowLine.Count; j++){dataArr2[n, j] = rowLine[j];}}cells.ImportTwoDimensionArray(dataArr2, 0, 0);//自适应宽sheet.AutoFitColumns();//自适应行高sheet.AutoFitRows();//cells.SetRowHeight(0, 20);//cells.SetRowHeight(1, 20);//cells.SetRowHeight(2, 20);//for (int i = 0; i < cols; i++)//{//    cells.SetColumnWidth(i, 15);//}afterAc?.Invoke(cells);//输出文件流          stream.Seek(0, SeekOrigin.Begin);wb.Save(stream, SaveFormat.Xlsx);stream.Seek(0, SeekOrigin.Begin);//保存文件到本地    //string fileName = Guid.NewGuid().ToString("N") + ".xls";//string filePath = AppDomain.CurrentDomain.BaseDirectory + fileName;//string fileFolderDeire = Path.GetDirectoryName(filePath);//目录信息//if (!Directory.Exists(fileFolderDeire))//{//    Directory.CreateDirectory(fileFolderDeire);//}   //如果是大文件，建议返回文件磁盘路径//wb.Save(filePath);//stream = new FileStream(filePath, FileMode.Open);//Task.Run(() =>//{//    //删除生成的文件//    System.Threading.Thread.Sleep(60000);//    try//    {//        File.Delete(filePath);//    }//    catch (Exception) { }//});
}
http://www.lryc.cn/news/409235.html

相关文章：

  • 天猫番茄品类TOP1，复购率超40%，「一颗大」如何策划极致产品力？
  • Docker搭建私有仓库harbor（docker 镜像仓库搭建）
  • 面试题：MySQL 索引
  • 云计算day13
  • 2024年孝感中级职称报名开始了吗？
  • RAG技术之Router
  • linux系统通过修改sudo文件使普通用户拥有类似root用户权限
  • 基于PyCharm在Windows系统上远程连接Linux服务器中Docker容器进行Python项目开发与部署
  • TypeScript学习篇-类型介绍使用、ts相关面试题
  • 超详细！Jmeter性能测试
  • C语言经典习题24
  • SQL labs-SQL注入（三，sqlmap使用）
  • 统一认证与单点登录：简明概述与应用
  • MSPM0G3507学习笔记1：开发环境_引脚认识与点灯
  • 使用法国云手机进行面向法国的社媒营销
  • C++学习笔记——模板
  • 财务分析，奥威BI行计算助力财务解放报表工作
  • 文件写入、读出-linux
  • 环境搭建-Windows系统搭建Docker
  • k8s零零散散问题
  • The Llama 3 Herd of Models.Llama 3 模型论文全文
  • ChatGPT的原理和成本
  • 无刷电机的ESC电子速度控制模块夹紧铁芯或更换镇流器
  • OpenAI发布AI搜索惨遭翻车？新老搜索的较量愈演愈烈！
  • SpringBoot整合阿里云短信业务
  • Kubernetes安全--securityContext介绍
  • 【React】通过实际示例详解评论列表渲染和删除
  • React 中 useState 语法详解
  • （面试必看！）一些和多线程相关的面试考点
  • 从零到一使用 Ollama、Dify 和 Docker 构建 Llama 3.1 模型服务