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

王焜棟琦

已于 2024-07-26 11:38:42 修改

阅读量414

点赞数 2

分类专栏： C# Aspose.Cells 文章标签： c# excel .net Aspose.Cells

于 2024-07-26 11:37:46 首次发布

本文链接：https://blog.csdn.net/u011511086/article/details/140711008

版权

C# 同时被 2 个专栏收录

147 篇文章 6 订阅

订阅专栏

Aspose.Cells

7 篇文章 0 订阅

订阅专栏

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);

    #endregion

    object[,] 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) { }
    //});
}