.net系列-ClosedXML，ExcelWorksheet,Workbook.Worksheets或ExcelPackage如何导出表格？如何自动宽度，根据内容调整行高和列宽
问题描述
-
- 本文介绍两种.net导出表格的方案
-
- 在我们使用.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();
}
待补充其他方案
结语
- 今天就写到这里啦~
- 小伙伴们，(￣ω￣(￣ω￣〃 (￣ω￣〃)ゝ我们明天再见啦~~
- 大家要天天开心哦
欢迎大家指出文章需要改正之处~
学无止境，合作共赢
