Старт работы с 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 = $", " + $", " + $""; 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 []>); var row = 9; var column = 2; foreach (var item in report.History)
Предлагаю обратить внимание на метод 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;На этом все, репозиторий с рабочим приложением находится здесь.
Заключение
О чем говорит финальная версия метода 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 = $", " + $", " + $""; 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 []>); 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+ просмотров, то во второй части мы поговорим о том, как можно отделить стилизацию от логики заполнения данными, упростить манипуляции над ячейками и в целом сделаем код боле поддерживаемым.
Экспорт dataGridView в Excel
Добрый день.
Пытаюсь сделать экспорт таблицы в эксель. Вроде бы и ссылку подключила using xcelapp = Microsoft.Office.Interop.Excel;
И код вроде бы правильный, но экспорт все равно не осуществляется, появляется ошибка. Помогите, пожалуйста.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Microsoft.Office.Interop.Excel.Application xcelapp = new Microsoft.Office.Interop.Excel.Application(); xcelapp.Application.Workbooks.Add(Type.Missing); for (int i=1; i товарDataGridView.Columns.Count +1; i++) { xcelapp.Cells[1, i] = товарDataGridView.Columns[i - 1].HeaderText; } for (int i=0; iтоварDataGridView.Rows.Count; i++) { for (int j=0; jтоварDataGridView.Columns.Count; j++) { xcelapp.Cells[i + 2, j + 1] = товарDataGridView.Rows[i].Cells[j].Value.ToString(); } } xcelapp.Columns.AutoFit(); xcelapp.Visible = true;
И еще вопрос, как сделать выборочно отчет по столбцам?
Как отобразить таблицу Excel в WinForm?
Рекомендую использовать сторонние библиотеки, поддерживающие формат xlsx. Использовать Interop не рекомендую (в основе его лежит медленный COM и требуется наличие MS Office).
Использовал библиотеку EPPlus. Она бесплатна для некоммерческого использования. Позволяет читать ячейки, а можно загрузить диапазон ячеек в DataTable.
Добавьте nuget пакет EPPlus в ваше приложение, а далее используйте вышеуказанные примеры кода для чтения Excel.
Как создать отчет с таблицей из собственного кода
Вспомним довольно частую ситуацию, когда вам нужно сделать что-либо очень быстро и буквально “на коленках”. Но не будем забывать, что структура и содержание отчета зависит от внешних факторов. Генератор отчетов FastReport.NET очень гибкий продукт и дает вам два способа решения этой проблемы.
Способ 1. Создавать структуру отчета в коде пользовательского приложения.
Способ 2. Управлять поведением отчета внутри, с помощью скрипта.
Есть довольно много информации о том, как реализовывать в скрипте отчета те или иные особенности поведения. Но довольно мало встречается примеров создания отчета из кода. Поэтому в этой статье мы создадим отчет с таблицей из кода приложения, а также рассмотрим, как заполнять таблицу данными и поместим объект в ячейку таблицы. О способе 2 подробнее будет написано в другой статье.
Объект таблица (Table) может быть заполнен статическими данными, или динамическими. В первом случае мы знаем конкретные размеры таблицы и сразу же вносим данные в ячейки из фиксированного набора.
Во-втором случае таблица строится динамически, строки (столбцы) добавляются в зависимости от данных в источнике. Впрочем, вы также можете ограничить размер таблицы и сделать ее фиксированной.
Давайте создадим WinForms приложение. Подключим в ссылках библиотеку FastReport.dll, которую вы можете найти в папке с установленным генератором отчетов. Добавим на форму кнопку, которая будет запускать построение отчета. И не забываем создать для нее обработчик нажатия.
Прежде всего подключаем библиотеки FastReport.
using FastReport; using FastReport.Data; using FastReport.Table; using FastReport.Utils; private void button1_Click(object sender, EventArgs e) using (Report report = new Report()) //Создаем объект отчета ReportPage page = new ReportPage(); //Создаем объект страницы отчета page.Name = "Page1"; //Задаем имя страницы report.Pages.Add(page); //Добавляем страницу в коллекцию страниц отчета DataSet ds = new DataSet(); //Создаем источник данных ds.ReadXml("~/../../../App_Data/nwind.xml"); //Загружаем в него базу данных xml report.RegisterData(ds); //Регистрируем источник данных в отчете report.GetDataSource("Products").Enabled = true; //Включаем источник данных DataBand dataBand = new DataBand(); //Создаем бэнд с данными dataBand.Name = "DataBand"; //Задаем имя бэнда page.Bands.Add(dataBand); //Добавляем бэнд в коллекцию бэндов страницы TableObject table = new TableObject(); //Создаем объект таблицы table.Name = "Table1"; //Задаем имя объекта table.RowCount = 10; //Задаем количество строк table.ColumnCount = 2; //Задаем количество колонок //В цикле заполняем все ячейки какими-либо данными for (int i = 0; i 10; i++) for (int j = 0; j 2; j++) table[j, i].Text = (10 * i + j + 1).ToString(); table[j, i].Border.Lines = BorderLines.All; > dataBand.Objects.Add(table); //dataBand.Objects.Add(picture); if (report.Prepare()) report.ShowPrepared(); >
А теперь рассмотрим случай, когда таблицу нужно заполнить данными из источника. Заменим цикл выше на другой код:
table.Columns[0].AutoSize = true; //table.Columns[1].AutoSize = true; DataSourceBase data = report.GetDataSource("Products"); data.Init(); //Инициализируем источник данных data.First(); //Получаем первую запись for (int i = 0; i 10; i++) table[0, i].Text = data["ProductName"].ToString(); table[0, i].Border.Lines = BorderLines.All; // Задаем рамки table[1, i].Text = data["UnitPrice"].ToString(); table[1, i].Border.Lines = BorderLines.All; data.Next(); >
В результате мы получим таблицу, с данными из базы:
Без заголовка таблица выглядит неполноценно. Давайте добавим его:
table.RowCount = 11; … table[0, 0].Text ="Product Name"; table[0, 0].Border.Lines = BorderLines.All; table[1, 0].Text = "Unit Price"; table[1, 0].Border.Lines = BorderLines.All; for (int i = 1; i 10; i++) table[0, i].Text = data["ProductName"].ToString(); table[0, i].Border.Lines = BorderLines.All; table[1, i].Text = data["UnitPrice"].ToString(); table[1, i].Border.Lines = BorderLines.All; data.Next(); >
Уточню, что в первой строке таблицы мы задали заголовки, значит цикл начинаем не с первого, а со второго элемента.
Рассмотрим последний на сегодня случай — как поместить объект в ячейку таблицы. Например, картинку:
PictureObject picture = new PictureObject(); //Создаем объект картинки picture.Bounds = new RectangleF(40, 0, Units.Centimeters * 0.5f, Units.Centimeters * 0.5f);
//Задаем размер объекта picture.CreateUniqueName(); //Задаем произвольное имя picture.Image = Image.FromFile("C:/Users/FR/Downloads/28.png"); //Задаем путь к изображению picture.LoadImage(); //Загружаем изображение picture.Parent = table[1, 1]; //Задаем родительский объект для изображения
Именно свойство Parent влияет на то, что картинка будет отображена в ячейке. Давайте посмотрим, как она будет выглядеть:
Таким образом, мы рассмотрели способ создания таблицы в отчете из кода приложения и два варианта её заполнения — статическим данными и динамическими. Кроме того, теперь вы знаете о возможностях программного добавления объекта в ячейку таблицы.