Старт работы с Excel на C#29.10.2020 01:32

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

1bf0f99dd93fcebab5465b0c2aa9966f

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

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

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

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

Задача

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

93682db039753d8e81e8014a84099369

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

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

53871c52ba2b3b520c4dbf2e3ef8ecd1.png

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

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

ea45dbbd9ed019a4ac84d0b210537046

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

506922e325b9a5818ccc1be1552dceb9

В методе 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, чтобы записать переданное в ячейки.

Стилизация

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

Как это выглядит

d07a3fcec4949579aea07ec7407f0cda.png

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

Да, на все эти красивости у нас уйдет больше года кода, чем на сам вывод данных, и, в конечном тоге, получившаяся каша из логики вывода данных и разметки заставит некоторых усомниться в их компетентности… но, мы же 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;

Заключение

О чем говорит финальная версия метода Generate? 

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+ просмотров, то во второй части мы поговорим о том, как можно отделить стилизацию от логики заполнения данными, упростить манипуляции над ячейками и в целом сделаем код боле поддерживаемым.

© Habrahabr.ru