Всем привет! Я Александр Родов, ведущий разработчик в компании «БАРС Груп», автор и руководитель разработки сервиса генерации печатных форм Sprinter. Этой статьёй мы продолжаем рассказ о возможностях использования библиотек DocumentFormat.OpenXml для генерации печатных файлов «офисных» форматов.
В первой и второй частях речь шла о разработке печати сведений о заказе в интернет-магазине в формате docx. В третьей и заключительной части мы поговорим о печати xlsx, необходимость которой встречается в требованиях к реализации ПО не реже, а, быть может, даже чаще, чем docx. Электронные таблицы дают пользователю больше возможностей по просмотру и анализу данных, позволяют вычислять агрегационные значения и строить простые аналитические модели. В нашем примере мы рассмотрим заполнение ячеек таблицы, их стилизацию и настройку формул для вычислений.
Постановка задачи
Возьмём за основу ту же предметную область и те исходные данные, которые были у нас в предыдущих статьях, а именно данные интернет-заказа некоторого абстрактного онлайн-магазина. Теперь нам нужно вывести их в xlsx-таблицу следующего вида:
Большинство столбцов таблицы содержат готовые данные из заказа. Столбец F (Стоимость) является расчётным: значения в ячейках равны произведению количества и стоимости за единицу товара. Последняя строка таблицы содержит две расчётные ячейки в столбцах D и F — суммы значений в этих столбцах. Также нам необходимо будет применить настройки границ таблицы, размеров столбцов, объединения ячеек и форматов значений в ячейках.
Создание пустого документа
Как и в случае с docx, xlsx-файл содержит множество разделов (Parts), отвечающих за определённую часть содержимого документа, например, стили, медиафайлы, и т.д. Некоторые из этих разделов являются обязательными для корректного открытия документа, другие же опциональны. Для нашего документа мы заполним базовую структуру, как показано ниже:
private const int IntFormatId = 101; private const int DecimalFormatId = 102; private const int DateFormatId = 103; public async Task<MemoryStream> Print(PurchaseOrder order) { var stream = new MemoryStream(); using var document = InitDocument(stream, order); PrintPurchase(document, order); document.Save(); return stream; } private SpreadsheetDocument InitDocument(MemoryStream stream, PurchaseOrder order) { var doc = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true); var workbookPart = doc.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); #region раздел стилей var stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>(); stylesPart.Stylesheet = new Stylesheet { Fonts = new Fonts { Count = 1 }, Fills = new Fills { Count = 2 }, Borders = new Borders { Count = 1 }, CellFormats = new CellFormats { Count = 1 } }; stylesPart.Stylesheet.Fonts.AppendChild(new Font()); stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } }); stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } }); stylesPart.Stylesheet.Borders.AppendChild(new Border()); stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat()); stylesPart.Stylesheet.NumberingFormats = new NumberingFormats( new NumberingFormat { NumberFormatId = IntFormatId, FormatCode = "#" }, new NumberingFormat { NumberFormatId = DecimalFormatId, FormatCode = "#.00" }, new NumberingFormat { NumberFormatId = DateFormatId, FormatCode = "dd.mm.yyyy" }); #endregion #region общие строки var sharedStringTablePart = workbookPart.AddNewPart<SharedStringTablePart>(); sharedStringTablePart.SharedStringTable = new SharedStringTable(); #endregion #region Тело и листы документа var currentSheetsCount = workbookPart!.WorksheetParts.Count(); var worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); worksheetPart.Worksheet.AppendChild(new Columns()); worksheetPart.Worksheet.AppendChild(new SheetData()); worksheetPart.Worksheet.AppendChild(new MergeCells()); var sheets = workbookPart.Workbook.GetFirstChild<Sheets>(); if (sheets == null) { sheets = workbookPart.Workbook.AppendChild(new Sheets()); } sheets.Append(new Sheet { Name = $"Заказ №{order.PurchaseNumber}", Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = (uint)currentSheetsCount + 1 }); #endregion return doc; }Среди прочих элементов мы настроили базовые форматы данных в свойстве Stylesheet.NumberingFormats. Это форматы дат, а также целых и вещественных чисел. Ссылки на них будут использованы далее при выводе данных в ячейки таблицы.
В конце метода InitDocument мы настроили главный (и единственный) лист и заполнили его имя номером заказа из данных.
Вывод данных
Перед выводом данных в лист Excel зададим размеры столбцов. Единица измерения размера — так называемые символы excel, равные 7 пикселям при значении dpi=96.
private float CmToExcelCharacters(float cmSize) { return cmSize / 2.54f * 96 / 7; } private void PrintPurchase(SpreadsheetDocument document, PurchaseOrder order) { #region Настройка параметров листа var rowCounter = 0u; var worksheet = document.WorkbookPart!.WorksheetParts.First().Worksheet; var sheet = worksheet.GetFirstChild<SheetData>()!; var mergeCells = worksheet.GetFirstChild<MergeCells>(); var columns = worksheet.GetFirstChild<Columns>(); var stylesDict = new Dictionary<StyleParams, uint>(); columns.Append( new Column { Min = 1, Max = 1, Width = CmToExcelCharacters(1), CustomWidth = true }, new Column { Min = 2, Max = 2, Width = CmToExcelCharacters(2), CustomWidth = true }, new Column { Min = 3, Max = 3, Width = CmToExcelCharacters(5), CustomWidth = true }, new Column { Min = 4, Max = 4, Width = CmToExcelCharacters(2.2f), CustomWidth = true }, new Column { Min = 5, Max = 5, Width = CmToExcelCharacters(2), CustomWidth = true }, new Column { Min = 6, Max = 6, Width = CmToExcelCharacters(2), CustomWidth = true }); #endregion // ... }Далее заполним шапку таблицы. Она заполняется текстовыми значениями, которые мы разместим в разделе общих строк (SharedStrings) документа. В значениях ячеек будут даны ссылки на эти строки по их индексу в разделе, а также будет указан тип значения ячейки — «общий». Альтернативный способ задания текста в xlsx — встраивание в таблицу. Его мы применим ниже при выводе текстовых данных из заказа.
В листинге ниже, помимо вывода шапки, также проводится настройка стилей ячеек. Детали работы со стилями в xlsx будут описаны ниже в соответствующем разделе.
/// <summary> /// Добавить общую строку в документ /// </summary> private int AppendSharedString(SpreadsheetDocument document, string sharedString) { OpenXmlElement siContent; var textEl = new Text(sharedString) { Space = SpaceProcessingModeValues.Preserve }; siContent = textEl; var sharedStringTablePart = document.WorkbookPart!.GetPartsOfType<SharedStringTablePart>().Single(); sharedStringTablePart.SharedStringTable.AppendChild(new SharedStringItem(siContent)); return sharedStringTablePart.SharedStringTable.ChildElements.Count - 1; } private void PrintPurchase(SpreadsheetDocument document, PurchaseOrder order) { // ... #region Заполнение шапки документа var titleStyle = basicStyle with { FontSize = 12, Bold = true }; AddTextRow($"Заказ №{order.PurchaseNumber} от {order.PurchaseDate.ToLongDateString()}", titleStyle); AddTextRow($"Клиент: {order.CustomerName}", titleStyle); AddTextRow($"Адрес доставки: {order.CustomerAddress}", titleStyle); var tableHeaderStyle = basicStyle with { Bold = true, Fill = "DDDDDD" }; AddTextRow($"Состав заказа", tableHeaderStyle with { FontSize = 12, HorizontalAlignment = HorizontalAlignmentValues.Center }); var tableTitlesRow = GetNextRow(); var tableTitles = new[] { "№", "Код", "Наименование", "Количество", "Цена", "Стоимость" }; var cellIdx = 0; foreach (var title in tableTitles) { var cell = CreateCell(tableTitlesRow, cellIdx++, tableHeaderStyle); cell.CellValue = new CellValue(AppendSharedString(document, title)); cell.DataType = CellValues.SharedString; } #endregion // ... Row GetNextRow() { var r = new Row { RowIndex = ++rowCounter }; sheet.Append(r); return r; } Cell CreateCell(Row row, int cellIndex, StyleParams styleParams, uint colSpan = 1) { var columnCode = Convert.ToChar('A' + cellIndex % 26); var cellCode = $"{columnCode}{rowCounter}"; var cell = new Cell { CellReference = cellCode }; var styleId = FindOrAppendStyle(styleParams); cell.StyleIndex = styleId; row.Append(cell); if (colSpan > 1) { var endCol = Convert.ToChar(columnCode + colSpan - 1); mergeCells!.AppendChild(new MergeCell { Reference = $"{columnCode}{row.RowIndex}:{endCol}{row.RowIndex}" }); for (var i = 1; i < colSpan; i++) { columnCode++; cellCode = $"{columnCode}{rowCounter}"; row.Append(new Cell { CellReference = cellCode, StyleIndex = styleId }); } } return cell; } void AddTextRow(string text, StyleParams styleParams) { var textRow = GetNextRow(); var textCell = CreateCell(textRow, 0, styleParams, 6); textCell.CellValue = new CellValue(text); textCell.DataType = CellValues.String; } uint FindOrAppendStyle(StyleParams styleParams) { if (!stylesDict.TryGetValue(styleParams, out var styleId)) { styleId = stylesDict[styleParams] = AppendTextStyle(document, styleParams); } return styleId; } }Как видно из кода, документ заполняется последовательно строками (Rows), которые, в свою очередь, последовательно заполняются ячейками (Cells). Каждой строке устанавливается её номер (RowIndex), начиная с 1. Каждой ячейке устанавливается код (CellCode), равный номеру строки и буквенному коду столбца, например, A1. Здесь приведена упрощённая логика формирования буквенного кода, не учитывающая возможность присутствия в документе многобуквенных кодов столбцов, начинающихся в обычной таблице после столбца Z. Это сделано для того, чтобы не усложнять алгоритмы, в реальном же коде эта возможность должна быть учтена.
Далее циклически заполним данные заказа и итоговые ячейки. Процедура заполнения строк и столбцов таблицы будет аналогична вышеприведённым примерам, нюансы настройки отдельных областей данных, такие как заполнение формул, объединённых ячеек и т.д. поясним ниже.
private void PrintPurchase(SpreadsheetDocument document, PurchaseOrder order) { // ... #region Заполнение данных заказа var rn = 0; foreach (var item in order.Items) { rn++; cellIdx = 0; var itemRow = GetNextRow(); var numCell = CreateCell(itemRow, cellIdx++, basicStyle with { HorizontalAlignment = HorizontalAlignmentValues.Center, NumFormatId = IntFormatId }); numCell.CellValue = new CellValue(rn); numCell.DataType = CellValues.Number; var codeCell = CreateCell(itemRow, cellIdx++, basicStyle); codeCell.CellValue = new CellValue(item.ProductCode); codeCell.DataType = CellValues.String; var nameCell = CreateCell(itemRow, cellIdx++, basicStyle); nameCell.CellValue = new CellValue(item.ProductName); nameCell.DataType = CellValues.String; var cntCell = CreateCell(itemRow, cellIdx++, basicStyle with { HorizontalAlignment = HorizontalAlignmentValues.Center, NumFormatId = IntFormatId }); cntCell.CellValue = new CellValue(item.Count); cntCell.DataType = CellValues.Number; var priceCell = CreateCell(itemRow, cellIdx++, basicStyle with { HorizontalAlignment = HorizontalAlignmentValues.Center, NumFormatId = DecimalFormatId }); priceCell.CellValue = new CellValue(item.UnitPrice); priceCell.DataType = CellValues.Number; var sumCell = CreateCell(itemRow, cellIdx, basicStyle with { HorizontalAlignment = HorizontalAlignmentValues.Center, NumFormatId = DecimalFormatId }); sumCell.CellFormula = new CellFormula($"{cntCell.CellReference}*{priceCell.CellReference}"); } #endregion #region Заполнение итогов var totalsRow = GetNextRow(); var totalsStyle = basicStyle with { Bold = true, HorizontalAlignment = HorizontalAlignmentValues.Center }; var totalsTitle = CreateCell(totalsRow, 0, totalsStyle with { HorizontalAlignment = HorizontalAlignmentValues.Right }, 3); totalsTitle.CellValue = new CellValue("ИТОГО:"); totalsTitle.DataType = CellValues.String; var totalCountCell = CreateCell(totalsRow, 3, totalsStyle with { NumFormatId = IntFormatId }); totalCountCell.CellFormula = new CellFormula($"SUM(D{totalsRow.RowIndex! - order.Items.Length}:D{totalsRow.RowIndex! - 1})"); var emptyCell = CreateCell(totalsRow, 4, totalsStyle); var totalSumCell = CreateCell(totalsRow, 5, totalsStyle with { NumFormatId = DecimalFormatId }); totalSumCell.CellFormula = new CellFormula($"SUM(F{totalsRow.RowIndex! - order.Items.Length}:F{totalsRow.RowIndex! - 1})"); #endregion // ... }Объединение ячеек
Для объединения диапазона ячеек в таблице необходимо внести соответствующую запись в раздел листа MergeCells, который в листе предшествует основному содержимому SheetData. Запись MergeCell содержит свойство Reference, значение которого включает коды ячеек левого верхнего и правого нижнего углов диапазона объединяемых ячеек, например: A1:C3. Важно, чтобы массив MergeCells не содержал пересекающиеся диапазоны объединенных ячеек, т.к. это вызывает ошибки при открытии xlsx-файла в различных редакторах.
if (colSpan > 1) { var endCol = Convert.ToChar(columnCode + colSpan - 1); mergeCells!.AppendChild(new MergeCell { Reference = $"{columnCode}{row.RowIndex}:{endCol}{row.RowIndex}" }); for (var i = 1; i < colSpan; i++) { columnCode++; cellCode = $"{columnCode}{rowCounter}"; row.Append(new Cell { CellReference = cellCode, StyleIndex = styleId }); } }Кроме того, необходимо заполнить в документе все ячейки, входящие в диапазон, и оставить их пустыми. Без них, например, не будет полностью отрисована граница объединённой ячейки.
Настройка формул
Для заполнения ячеек расчётными формулами вместо свойства Cell.CellValue используется свойство Cell.CellFormula. Оно заполняется строкой с выражением формулы, например:
var sumCell = CreateCell(itemRow, cellIdx, basicStyle with { HorizontalAlignment = HorizontalAlignmentValues.Center, NumFormatId = DecimalFormatId }); sumCell.CellFormula = new CellFormula($"{cntCell.CellReference}*{priceCell.CellReference}");или
var totalCountCell = CreateCell(totalsRow, 3, totalsStyle with { NumFormatId = IntFormatId }); totalCountCell.CellFormula = new CellFormula($"SUM(D{totalsRow.RowIndex! - order.Items.Length}:D{totalsRow.RowIndex! - 1})");Другие параметры, такие как тип данных ячейки, заполнять не требуется. Тип будет выставлен автоматически.
Настройка стилей
Стили документа хранятся в отдельном zip-архивном файле, который описывается частью документа StylesheetPart. Каждый стиль включает в себя параметры шрифта, заливки, формата ячейки и границы ячейки. В разделе стилей следует заводить новый, если вам потребовалось внести изменения в любой из этих параметров. Привязка стиля к ячейке указывается в свойстве StyleId, значение которого равно индексу стиля в таблице:
var styleId = FindOrAppendStyle(styleParams); cell.StyleIndex = styleId;Сами стили в нашем примере заполняются в методе AppendTextStyle. Для более оптимального заполнения используется тип записи StyleParams, по экземплярам которого строится словарь уже добавленных в документ стилей. В StyleParams перечислены только те параметры стиля, которые меняются в нашем примере, в других документах их может быть больше.
private record StyleParams { public string FontFamily { get; set; } public int FontSize { get; set; } public bool Bold { get; set; } public string Fill { get; set; } public HorizontalAlignmentValues HorizontalAlignment { get; set; } public uint? NumFormatId { get; set; } } private uint AppendTextStyle(SpreadsheetDocument document, StyleParams styleParams) { var stylesheet = document.WorkbookPart.WorkbookStylesPart.Stylesheet; stylesheet.Fonts.AppendChild(new Font { FontName = new FontName { Val = styleParams.FontFamily }, FontSize = new FontSize { Val = styleParams.FontSize }, Bold = new Bold { Val = styleParams.Bold } }); var fontId = stylesheet.Fonts.Count++; var fillId = 0u; var border = new Border(); border.Append(new LeftBorder { Style = BorderStyleValues.Thin, Color = new Color { Rgb = "000000" } }); border.Append(new RightBorder { Style = BorderStyleValues.Thin, Color = new Color { Rgb = "000000" } }); border.Append(new TopBorder { Style = BorderStyleValues.Thin, Color = new Color { Rgb = "000000" } }); border.Append(new BottomBorder { Style = BorderStyleValues.Thin, Color = new Color { Rgb = "000000" } }); stylesheet.Borders.AppendChild(border); var borderId = stylesheet.Borders.Count++; if (!string.IsNullOrEmpty(styleParams.Fill)) { stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor { Rgb = styleParams.Fill } } }); fillId = stylesheet.Fills.Count++; } var alignment = new Alignment { Horizontal = styleParams.HorizontalAlignment, Vertical = VerticalAlignmentValues.Center, WrapText = true }; stylesheet.CellFormats.AppendChild(new CellFormat { BorderId = borderId, ApplyBorder = true, FillId = fillId, ApplyFill = true, FontId = fontId, ApplyFont = true, NumberFormatId = styleParams.NumFormatId, ApplyNumberFormat = true, Alignment = alignment, ApplyAlignment = true }); return stylesheet.CellFormats.Count++; }В нашем примере мы везде используем полные границы ячеек, поэтому все стили содержат одинаковые настройки границ. В других случаях может изменяться состав границы (лево/право/верх/низ), а также цвет, толщина и рисунок границы.
Настройка форматов ячеек
Как было упомянуто выше, формат ячейки задаётся с помощью ссылки на один из преднастроенных в документе форматов.
stylesPart.Stylesheet.NumberingFormats = new NumberingFormats( new NumberingFormat { NumberFormatId = IntFormatId, FormatCode = "#" }, new NumberingFormat { NumberFormatId = DecimalFormatId, FormatCode = "#.00" }, new NumberingFormat { NumberFormatId = DateFormatId, FormatCode = "dd.mm.yyyy" });Сама ссылка сохраняется в стиле, применённом к ячейке.
stylesheet.CellFormats.AppendChild(new CellFormat { NumberFormatId = styleParams.NumFormatId, });Кроме того, нужно корректно задать тип данных ячейки, к которой применён стиль с заданным форматом, например:
numCell.DataType = CellValues.Number;Стандарт OpenXml содержит свой язык масок форматов чисел и дат, позволяющих задавать, например, число знаков после запятой у чисел, символы-разделители дробной части и порядков, или форматы вывода дат. Однако этот язык содержит некоторые ограничения, например, на возможные используемые символы-разделители у чисел.
Заключение
В этой статье мы продемонстрировали логику печати документа в формате таблицы xlsx. Код демонстрационного проекта ещё может быть улучшен в части оптимального заполнения повторяющихся элементов документа, в примерах же приоритет был отдан простоте кода и наглядности того, что он делает. В примере затронуто большинство базовых функций xlsx, таких как объединение ячеек и заполнение расчётных формул. С API для настройки более специфических элементов таблиц xlsx можно познакомиться в онлайн-документации.
С полным исходным кодом всего проекта можно ознакомиться по ссылке. Данная статья завершает наш цикл обзора возможностей работы с документами «офисных» форматов в .NET.
Мы в «БАРС Груп» разрабатываем цифровые решения для государства, бизнеса и людей. Принимаем активное участие в реализации Национального проекта «Цифровая экономика» и создаем цифровые решения для импортозамещения программного обеспечения —88 решений компании зарегистрировано в реестре российского ПО. Рассказываем о наших продуктах и ИТ-трендах в Telegram-канале. Сервис печати Sprinter — наш новый продукт, уже входящий в реестр. Он помогает разработчикам и аналитикам с печатью документов по заданным шаблонам. А ещё благодаря ему увидела свет эта статья.
ссылка на оригинал статьи https://habr.com/ru/articles/918798/
Добавить комментарий