/// <summary>
/// 导出列表。
/// </summary>
/// <typeparam name="TModel">当前模型实例类型。</typeparam>
/// <param name="models">模型列表。</param>
/// <param name="fileName">文件名称。</param>
/// <param name="sheetName">工作表名称。</param>
/// <param name="sheetId">索引Id。</param>
/// <returns>返回物理路径试图结果。</returns>
public PhysicalFileResult Export <TModel>(IEnumerable <TModel> models, string fileName, string sheetName = "sheet1", uint sheetId = 1) where TModel : class, new()
{
const string extension = ".xlsx";
if (!fileName.EndsWith(extension, StringComparison.OrdinalIgnoreCase))
{
fileName += extension;
}
var storageDirectory = _serviceProvider.GetRequiredService <IStorageDirectory>();
var excels = new ExcelEnumerable <TModel>(models);
var path = storageDirectory.GetTempPath(Guid.NewGuid().ToString("N"));
Save(excels, path, sheetName, sheetId);
var result = new PhysicalFileResult(path, extension.GetContentType());
result.FileDownloadName = fileName;
return(result);
}
/// <summary>
/// 需要先写入样式，可以得到没列样式Id。
/// </summary>
private void WriteStylesheet <TModel>(WorkbookPart workbookPart, ExcelEnumerable <TModel> models) where TModel : class, new()
{
WriteStylesheet(workbookPart, stylesheet =>
{
var fontIndex = 1U; //默认字体啥都没有
var formatId = 1U; //格式Id
var numberId = 1U; //数值格式
foreach (var descriptor in models.Descriptors)
{
descriptor.CellFormat.FormatId = formatId++;
stylesheet.CellFormats.AppendChild(descriptor.CellFormat);
descriptor.HeadCellFormat.FormatId = formatId++;
stylesheet.CellFormats.AppendChild(descriptor.HeadCellFormat);
if (descriptor.Font != null)
{
stylesheet.Fonts.AppendChild(descriptor.Font);
descriptor.CellFormat.FontId = fontIndex;
descriptor.CellFormat.ApplyFont = true;
fontIndex++;
}
if (descriptor.HeadFont != null)
{
stylesheet.Fonts.AppendChild(descriptor.HeadFont);
descriptor.HeadCellFormat.FontId = fontIndex;
descriptor.HeadCellFormat.ApplyFont = true;
fontIndex++;
}
if (descriptor.NumberingFormat != null)
{
stylesheet.NumberingFormats.AppendChild(descriptor.NumberingFormat);
descriptor.NumberingFormat.NumberFormatId = numberId;
descriptor.CellFormat.NumberFormatId = numberId;
descriptor.CellFormat.ApplyNumberFormat = true;
numberId++;
}
}
});
}
/// <summary>
/// 加载Excel文件。
/// </summary>
/// <param name="path">Excel文件物理路径。</param>
/// <returns>返回Excel内容。</returns>
public IEnumerable <TModel> Load <TModel>(string path) where TModel : class, new()
{
var model = new ExcelEnumerable <TModel>();
using var document = SpreadsheetDocument.Open(path, false);
var sheet = document.WorkbookPart
.WorksheetParts
.Select(x => x.Worksheet)
.FirstOrDefault();
if (sheet == null)
{
throw new Exception("Excel中没有存在任何工作表。");
}
var shared = document.WorkbookPart.SharedStringTablePart?.SharedStringTable;
var data = sheet.GetFirstChild <SheetData>();
model.SheetName = sheet.LocalName;
Load(model, shared, data);
return(model);
}
private void Load <TModel>(ExcelEnumerable <TModel> models, SharedStringTable shared, SheetData sheet) where TModel : class, new()
{
var isFirst = true;
var dic = new Dictionary <string, Action <object, string> >();
foreach (var row in sheet.Descendants <Row>())
{
if (isFirst)
{//第一行，描述
var cells = row.Descendants <Cell>().ToList();
foreach (var cell in cells)
{
var reference = _regex.Replace(cell.CellReference, String.Empty);
var descriptor = models.Descriptors.SingleOrDefault(x =>
reference.Equals(x.Reference, StringComparison.OrdinalIgnoreCase));
if (descriptor == null)
{
var name = GetCellValue(cell, shared)?.Trim();
if (string.IsNullOrWhiteSpace(name))
{
continue;
}
descriptor = models.Descriptors.SingleOrDefault(x =>
name.Equals(x.ColumnName, StringComparison.OrdinalIgnoreCase));
if (descriptor == null)
{
continue;
}
}
dic.Add(reference, descriptor.Set);
}
isFirst = false;
}
else
{
models.Add(Load <TModel>(dic, row, shared));
}
}
}
