问题描述
-
- 本文介绍两种.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();}
待补充其他方案
结语
- 今天就写到这里啦~
- 小伙伴们，(￣ω￣(￣ω￣〃 (￣ω￣〃)ゝ我们明天再见啦~~
- 大家要天天开心哦
欢迎大家指出文章需要改正之处~
学无止境，合作共赢
