.net系列-ClosedXML，ExcelWorksheet,Workbook.Worksheets或ExcelPackage如何导出表格？如何自动宽度，根据内容调整行高和列宽

tangdou369098655

于 2024-09-27 01:29:19 发布

阅读量325

点赞数 7

分类专栏： C# 文章标签： .net 后端 c#

本文链接：https://blog.csdn.net/tangdou369098655/article/details/142581282

版权

C# 专栏收录该内容

15 篇文章 4 订阅

订阅专栏

问题描述

1. 本文介绍两种导出表格的方案
1. 在我们使用.net的表格工具的时候，导出表格的宽度和高度默认的总是很窄，字体都挤在一起，如何让它自适应呢？下面介绍两个库的自适应代码写法

使用ClosedXML也就是XLWorkbook

github文档: https://github.com/ClosedXML/ClosedXML

using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Linq;


var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Adjust To Contents");

// Set some values with different font sizes
ws.Cell(1, 1).Value = "Tall Row";
ws.Cell(1, 1).Style.Font.FontSize = 30;
ws.Cell(2, 1).Value = "Very Wide Column";
ws.Cell(2, 1).Style.Font.FontSize = 20;

// Adjust column width
ws.Column(1).AdjustToContents(); // 自适应关键代码

// Adjust row heights
ws.Rows(1, 2).AdjustToContents(); // 自适应关键代码

// You can also adjust all rows/columns in one shot
// ws.Rows().AdjustToContents();
// ws.Columns().AdjustToContents();

// We'll now select which cells should be used for calculating the
// column widths (same method applies for row heights)

// Set the values
ws.Cell(4, 2).Value = "Width ignored because calling column.AdjustToContents(5, 7)";
ws.Cell(5, 2).Value = "Short text";
ws.Cell(6, 2).Value = "Width ignored because it's part of a merge";
ws.Range(6, 2, 6, 4).Merge();
ws.Cell(7, 2).Value = "Width should adjust to this cell";
ws.Cell(8, 2).Value = "Width ignored because calling column.AdjustToContents(5, 7)";

// Adjust column widths only taking into account rows 5-7
// (merged cells will be ignored)
ws.Column(2).AdjustToContents(5, 7);

// You can also specify the row to start calculating the widths:
// e.g. ws.Column(3).AdjustToContents(9);

wb.SaveAs("AdjustToContents.xlsx");

使用XLWorkbook实战案例(支持多个子表)

模拟传参

{
  "fileName": "aaaa.xlsx",
  "data": [
    {
      "sheetName": "1111111",
      "sheetTitle": {
        "additionalProp1": "qqq",
        "additionalProp2": "www",
        "additionalProp3": "eee"
      },
      "SheetData": [
        {
          "additionalProp1": "111",
          "additionalProp2": "222",
          "additionalProp3": "333"
        },
        {
          "additionalProp1": "111111111",
          "additionalProp2": "22222222222222",
          "additionalProp3": "33333333333333"
        }
      ]
    }
  ]
}

类型定义

 public class ExportSheetModel
 {
     public string SheetName { get; set; }
     public Dictionary<string, string> SheetTitle { get; set; }
     public List<Dictionary<string, string>> SheetData { get; set; }
 }
 public class ExportDataModel
 {
     public string FileName { get; set; }
     public List<ExportSheetModel> Data { get; set; }
 }

方法函数

  public Stream ExportData(List<ExportSheetModel> fileDataModel)
  {
      var stream = new MemoryStream();
          using (var workbook = new XLWorkbook())
          {
              foreach (var filesheetData in fileDataModel)
              {
                  var worksheet = workbook.Worksheets.Add(filesheetData.SheetName);

                  int colIndex = 1;
                  foreach (var title in filesheetData.SheetTitle.Values)
                  {
                      worksheet.Cell(1, colIndex++).Value = title;
                  }

                  int rowIndex = 2;
                  foreach (var record in filesheetData.SheetData)
                  {
                      colIndex = 1;
                      foreach (var item in record)
                      {
                          worksheet.Cell(rowIndex, colIndex++).Value = item.Value ?? "";
                          worksheet.Column(colIndex).AdjustToContents(); // 关键代码，这个是设置了自定义宽度
                      }
                      rowIndex++;
              }
              }
                  workbook.SaveAs(stream);
                  stream.Seek(0, SeekOrigin.Begin);
                  return stream;
          }
      
  }

使用ExcelPackage

文档：http://excelpackage.codeplex.com/
安装EPPlus NuGet程序包，使用ExcelPackage类

using System.Data;
using System.IO;
using System.Reflection;
using OfficeOpenXml;


 public IActionResult Export()
 {
     try
     {
         MemoryStream stream = new MemoryStream();
         using (ExcelPackage package = new ExcelPackage())
         {
             ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Data Attribute Type");
                 for (int c = 0; c < 5; c++)
                 {
                    int r = 0;
                    sheet.Cells[r + 2, c + 1].Value = colValue;
                    sheet.Cells.AutoFitColumns(); // 关键代码：自适应宽度
                    sheet.Cells[r + 2, c + 1].Hyperlink = new Uri(colValue.ToString());
                    r++;
                 }
             package.SaveAs(stream);
             stream.Seek(0, SeekOrigin.Begin);
         }
         string fileName = "test.xlsx";
         return File(stream, "application/octet-stream", fileName);
     }
     catch (Exception ex)
     {
         // this._logger.LogError(ex.Message);
     }
     return NotFound();
 }