Старт работы с Excel на C

В современном мире разработки приложений нередко встает необходимость работы с Excel документами. Чаще всего это разного рода отчеты, но иногда xls/x файлы используются в качестве хранилища данных. Например, если пользователь должен иметь возможность загрузить данные в приложение или выгрузить, в человеко-читаемом виде, Excel де-факто является стандартом. Относительно дружелюбный интерфейс, прозрачная структура, в купе с его распространенностью... трудно навскидку назвать решение лучше.

Однако, у многих Excel до сих пор ассоциируется с чем-то тяжелым, неповоротливым и сложным. Давайте посмотрим, как мы - обычные C# разработчики, можем легко сформировать простой Excel документ, на примере табличного отчета.

Историческая справка

Времена, когда доминировал проприетарный формат .xls(Excel Binary File Format) давно прошли и сейчас мы имеем только .xlsx(Excel Workbook), в рамках Office Open XML. Последний представляет собой обычный .zip архив с XML файлами. Не будем углубляться в его структуру, я искренне надеюсь что вам это никогда не понадобится:)

На github, и не только, можно найти ряд библиотек, бесплатных и не только. Пожалуй самой популярной является EPPlus. До определенной степени, она довольно хорошо отражает концепцию Excel, именно по этому я всегда использую EPPlus. Версия 4 полностью бесплатна, начиная с 5й версии вам потребуется приобрести лицензию для коммерческого использования.

Задача

Итак, предположим, продукт-мэнеджеру ударила в голову идея того, что возможность выгружать некий отчет в форматеExcel увеличит кол-во пользователей на 100500%. Проджет-менеджер решает выкатить эту киллер-фичу как хотфикс прямо сегодня ведь работы всего на пару часов.

Сам по себе, отчет содержит краткое описание компании и историю изменения некоторых экономических показателей. Для простоты все свойства компании строки. Экономические показатели большие целые числа и числа с плавающей точкой, а также даты. Предположим, что где-то в недрах микросервисного backend-да есть сервис-генератор подобных отчетов, например по id компании. Однако, поскольку id нет смысла выводить пользователю, идентификатор отсутствует в самой модели отчета.

Аналитик, в свою очередь, выдает задачу с феноменально точнымописанием - "Сгенерироватьexcel отчет на базе данных MarketReport". Что ж, для нашего примера, создадим заглушку генератор фейковых данных:

Первый запуск

Подключим EPPlus версии 4.5.3.3 и создадим базовую обвязку для будущего генератора.

Сердцем генератора будет метод Generate. ExcelPackage это модель документа, через которую мы и будем осуществлять все взаимодействияс ним. Также имеется конструктор для передачи пути к файлу или потока.

В методе main создается генератор отчетов, а также генератор Excel файлов. Далее полученный файл просто записывается на диск.

При попытке запустить приложение, получаем exception:InvalidOperationException: The workbook must contain at least one worksheet

Все правильно, Excel документ не может существовать без страниц, должна быть хотя бы одна. Добавляем ее, все интуитивно понятно:

var sheet = package.Workbook.Worksheets    .Add("Market Report");

Запускаем снова и... вот оно! Теперь наше приложение генерирует документ и, хотя там еще ничего нет, он уже весит 2,5KB - значит мы работаем с Excel правильно и все идет как надо.

Вывод данных

Давайте выведем основную информацию по компании в шапку. Для доступа к конкретной ячейки объект Cells на странице пакета снабжен удобным индексатором. При этом, до конкретной ячейки можно достучаться как через номер строки и столбца, так и по привычному всем буквенно-числовому коду:

sheet.Cells["B2"].Value = "Company:";sheet.Cells[2, 3].Value = report.Company.Name;

sheet.Cells["B2"].Value = "Company:";sheet.Cells[2, 3].Value = report.Company.Name;sheet.Cells["B3"].Value = "Location:";sheet.Cells["C3"].Value = $"{report.Company.Address}, " +  $"{report.Company.City}, " +                            $"{report.Company.Country}";sheet.Cells["B4"].Value = "Sector:";sheet.Cells["C4"].Value = report.Company.Sector;sheet.Cells["B5"].Value = report.Company.Description;

Для вывода исторических данных понадобится как минимум шапка таблицы и цикл по массиву History:

sheet.Cells[8, 2, 8, 4].LoadFromArrays(new object[][]{ new []{"Capitalization", "SharePrice", "Date"} });var row = 9;var column = 2;foreach (var item in report.History){  sheet.Cells[row, column].Value = item.Capitalization;  sheet.Cells[row, column + 1].Value = item.SharePrice;  sheet.Cells[row, column + 2].Value = item.Date;      row++;}

Предлагаю обратить внимание на метод LoadFromArrays, который заполняет диапазон ячеек рваным(зубчатым) массивом. Здесь мы можем видеть, что типизация теряется и передавая массив object мы ожидаем что EPPlus в конечном итоге использует ToString, чтобы записать переданное в ячейки.

Стилизация

Если вы прямо сейчас откроете документ, то вы возможно увидите не то, что хотелось бы отдать в продакшн в пятницу вечером.

Во-первых, шапка никак не выделяется, во-вторых таблица не имеет границ... выравнивание пляшет, даты отображаются магическимичислами, а капитализация "уходит в какую-то математику" - как это прокомментировал аналитик.

Да, на все эти красивости у нас уйдет больше года кода, чем на сам вывод данных, и, в конечном тоге, получившаяся каша из логики вывода данных и разметки заставит некоторых усомниться в их компетентности... но, мы же backend разработчики, так давайте сверстаем Excel Sheet!

Размер ячеек

Из коробки у нас есть возможность сделать автофит а так же вручную выставить ширину в соответствии с нашей ситуацией. А ситуация у нас не самая хорошая по задумке аналитика в шапке у ячеек должен быть автофит, а у ячеек таблицы тоже автофит. Так в чем же подвох?

Если вы когда-нибудь до этого открывали Excel, то возможно знаете, что ширина ячеек не может отличаться в рамках столбца и автофит будет по самому широкому контенту ячейки. Однако, простые вещи бывает нетак то просто объяснить... Но если вы справитесь, то вот как это будет выглядеть в коде:

sheet.Cells[1, 1, row, column + 2].AutoFitColumns();sheet.Column(2).Width = 14;sheet.Column(3).Width = 12;

Формат данных

Как и большая часть стиля ячейки, он задается через одноименное свойство Style. Обратите внимание на вычисление 3-го аргумента индексатора. Это звоночек некачественного кода, но к этому мы вернемся в позже...

sheet.Cells[9, 4, 9 + report.History.Length, 4].Style.Numberformat.Format = "yyyy";sheet.Cells[9, 2, 9 + report.History.Length, 2].Style.Numberformat.Format =  "### ### ### ##0";

Выравнивание

Его можно задать как на ячейке, так и на диапазоне. На самом деле, для EPPlus, это одна и та же сущность некий ExcelRange, описывающий диапазон ячеек, в том числе и со всего 1 ячейкой.

sheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;sheet.Cells[8, 3, 8 + report.History.Length, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

Стиль текста

Также легко задается, используя Style.Font, кстати, здесь, на 2-й строчке, мы впервые указываем диапазон так, как привыкли его видеть пользователи Excel:

sheet.Cells[8, 2, 8, 4].Style.Font.Bold = true;sheet.Cells["B2:C4"].Style.Font.Bold = true;

Границы

Задаем стиль линии, а также ее толщину. К этому моменту от кол-ва магических чисел-параметров индексатора уже рябит в глазах, но мы уже на финишной прямой... не так ли?

sheet.Cells[8, 2, 8 + report.History.Length, 4].Style.Border.BorderAround(ExcelBorderStyle.Double);sheet.Cells[8, 2, 8, 4].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

График

"Ну что за отчет без графиков, верно, Карл?" - ловко подметит специалист по тестированию, и не важно, что этого не было в ТЗ а на часах уже половина 9-го...

Хотя график как сущность сам по себе сложнее таблиц и с графиками мы не работаем каждый день, EPPlus предоставляет довольно понятный API. Давайте добавим простейший график, отражающий рост капитализации:

var capitalizationChart = sheet.Drawings.AddChart("FindingsChart", OfficeOpenXml.Drawing.Chart.eChartType.Line);capitalizationChart.Title.Text = "Capitalization";capitalizationChart.SetPosition(7, 0, 5, 0);capitalizationChart.SetSize(800, 400);var capitalizationData = (ExcelChartSerie)(capitalizationChart.Series.Add(sheet.Cells["B9:B28"], sheet.Cells["D9:D28"]));capitalizationData.Header = report.Company.Currency;

Еще, может понадобиться защитить страницу от редактирования:

sheet.Protection.IsProtected = true;

Заключение

public byte[] Generate(MarketReport report){      var package = new ExcelPackage();        var sheet = package.Workbook.Worksheets            .Add("Market Report");          sheet.Cells["B2"].Value = "Company:";      sheet.Cells[2, 3].Value = report.Company.Name;      sheet.Cells["B3"].Value = "Location:";      sheet.Cells["C3"].Value = $"{report.Company.Address}, " +    $"{report.Company.City}, " +                                 $"{report.Company.Country}";      sheet.Cells["B4"].Value = "Sector:";      sheet.Cells["C4"].Value = report.Company.Sector;      sheet.Cells["B5"].Value = report.Company.Description;        sheet.Cells[8, 2, 8, 4].LoadFromArrays(new object[][]{ new []{"Capitalization", "SharePrice", "Date"} });      var row = 9;      var column = 2;      foreach (var item in report.History)      {            sheet.Cells[row, column].Value = item.Capitalization;           sheet.Cells[row, column + 1].Value = item.SharePrice;           sheet.Cells[row, column + 2].Value = item.Date;            row++;      }        sheet.Cells[1, 1, row, column + 2].AutoFitColumns();      sheet.Column(2).Width = 14;      sheet.Column(3).Width = 12;            sheet.Cells[9, 4, 9+ report.History.Length, 4].Style.Numberformat.Format = "yyyy";      sheet.Cells[9, 2, 9+ report.History.Length, 2].Style.Numberformat.Format =  "### ### ### ##0";        sheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;      sheet.Cells[8, 3, 8 + report.History.Length, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;      sheet.Column(4).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;        sheet.Cells[8, 2, 8, 4].Style.Font.Bold = true;      sheet.Cells["B2:C4"].Style.Font.Bold = true;    sheet.Cells[8, 2, 8 + report.History.Length, 4].Style.Border.BorderAround(ExcelBorderStyle.Double);      sheet.Cells[8, 2, 8, 4].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;         var capitalizationChart = sheet.Drawings.AddChart("FindingsChart", OfficeOpenXml.Drawing.Chart.eChartType.Line);      capitalizationChart.Title.Text = "Capitalization";      capitalizationChart.SetPosition(7, 0, 5, 0);      capitalizationChart.SetSize(800, 400);      var capitalizationData = (ExcelChartSerie)(capitalizationChart.Series.Add(sheet.Cells["B9:B28"], sheet.Cells["D9:D28"]));      capitalizationData.Header = report.Company.Currency;           sheet.Protection.IsProtected = true;        return package.GetAsByteArray();}

Во-первых, прежде всего, о том, что мы успешно справились с задачей, а именно, сгенерировали свой первый Excel отчет, поработали со стилями и даже решили пару попутных проблем.

Во-вторых, возможно имеет смысл искать новоюработу, но, забегая вперед, я бы с этим не спешил... Если данная публикация наберет 1+ просмотров, то во второй части мы поговорим о том, как можно отделить стилизациюот логики заполнения данными, упростить манипуляции над ячейками и в целом сделаем код боле поддерживаемым.