Всем привет! Я Александр Родов, ведущий разработчик в компании «БАРС Груп», автор и руководитель разработки сервиса генерации печатных форм 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 — наш новый продукт, уже входящий в реестр. Он помогает разработчикам и аналитикам с печатью документов по заданным шаблонам. А ещё благодаря ему увидела свет эта статья.