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