C вставить массив в excel

Обновлено: 07.07.2024

В современном мире разработки приложений нередко встает необходимость работы с Excel документами. Чаще всего это разного рода отчеты, но иногда xls/x файлы используются в качестве хранилища данных. Например, если пользователь должен иметь возможность загрузить данные в приложение или выгрузить, в человеко-читаемом виде, 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 документ не может существовать без страниц, должна быть хотя бы одна. Добавляем ее, все интуитивно понятно:

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

Вывод данных

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

Полный код вывода шапки.

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

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

Стилизация

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

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

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

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

Размер ячеек

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

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

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

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

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

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

Стиль текста

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

Границы

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

График

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

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

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

На этом все, репозиторий с рабочим приложением находится здесь.

Заключение

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

Версия этой статьи для Microsoft Visual Basic 6,0 приведена в статье 247412.

Обзор

Метод, наиболее часто используемый для передачи данных в книгу Excel, является автоматизацией. С помощью автоматизации можно вызывать методы и свойства, относящиеся к задачам Excel. Автоматизация предоставляет максимальную гибкость для указания расположения данных в книге, форматирования книги и создания различных параметров во время выполнения.

С помощью автоматизации вы можете использовать различные методы для переноса данных:

Перемещение ячейки данных по ячейке.
Передача данных в массиве в диапазон ячеек.
Перенесите данные из набора записей ADO в диапазон ячеек с помощью метода Копифромрекордсет.
Создайте объект QueryTable на листе Excel, который содержит результат запроса в источнике данных ODBC или OLEDB.
Перенесите данные в буфер обмена, а затем вставьте содержимое буфера обмена в лист Excel.
Вы также можете использовать несколько методов, которые не требуют автоматизации для передачи данных в Excel. Если вы используете серверную программу, это может быть хорошим подходом к отходящей обработке данных от клиентов.

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

Способ

Использование автоматизации для передачи ячейки данных по ячейкам

С помощью автоматизации можно переносить данные на лист по одной ячейке за раз:

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

Использование автоматизации для переноса массива данных в диапазон листа

Вы можете перенести массив данных в диапазон из нескольких ячеек за один раз:

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

Этот код представляет два запроса интерфейса: один для объекта Range, возвращаемого методом Range, и другой для объекта Range, который возвращает метод Resize. В отличие от переноса ячейки данных по ячейке, необходимо запросить интерфейсы 300 для объектов Range. Если это возможно, вы можете воспользоваться преимуществами для массового переноса данных и уменьшения количества запросов к интерфейсу.

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

Использование автоматизации для переноса набора записей ADO в диапазон листа

Объектные модели для Excel 2000, Excel 2002 и Excel 2003 предоставляют метод Копифромрекордсет для переноса набора записей ADO в диапазон листа. В приведенном ниже коде показано, как автоматизировать Excel для переноса содержимого таблицы Orders в образце базы данных Northwind с помощью метода Копифромрекордсет:

Использование автоматизации для создания объекта QueryTable на листе

Объект QueryTable представляет таблицу, созданную на основе данных, возвращаемых из внешнего источника данных. При автоматизации Excel можно создать QueryTable, предоставив строку подключения к OLE DB или источнику данных ODBC, а также строку SQL. Excel создает набор записей и вставляет набор записей на лист в указанном расположении. Объекты QueryTable имеют следующие преимущества по сравнению с методом Копифромрекордсет:

Excel обрабатывает создание набора записей и его расположение на листе.
Вы можете сохранить запрос с помощью объекта QueryTable и обновить его позже, чтобы получить обновленный набор записей.
Когда на лист добавляется новый QueryTable, вы можете указать, что данные, которые уже существуют в ячейках листа, будут сдвинуты для обработки новых данных (Дополнительные сведения см. в свойстве Рефрешстиле).

В приведенном ниже коде показано, как автоматизировать Excel 2000, Excel 2002 или Excel 2003 для создания нового QueryTable на листе Excel с помощью данных из учебной базы данных Northwind:

Использование буфера обмена Windows

Создание текстового файла с разделителями, который Excel может проанализировать по строкам и столбцам

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

Приведенный выше код не использует автоматизацию. Однако при желании можно использовать автоматизацию для открытия текстового файла и сохранения файла в формате книги Excel, как показано ниже:

С помощью поставщика OLE DB для Microsoft Jet можно добавлять записи в таблицу из существующей книги Excel. Таблица в Excel — это всего лишь диапазон ячеек; диапазон может иметь определенное имя. Как правило, первая строка диапазона содержит заголовки (или имена полей), а все последующие строки в диапазоне содержат записи.

Приведенный ниже код добавляет две новые записи в таблицу в Book7. xls. В этом случае таблицей является Лист1:

Для получения дополнительных сведений об использовании поставщика OLEDB для Jet с источниками данных Excel щелкните номера статей ниже, чтобы просмотреть статьи базы знаний Майкрософт:

278973 пример: в ексцеладо показано, как использовать ADO для чтения и записи данных в книгах Excel

257819 практическое руководство: использование ADO с данными Excel из Visual Basic или VBA

Передача XML-данных (Excel 2002 и Excel 2003)

Excel 2002 и 2003 могут открыть любой XML-файл с правильным форматом. XML-файлы можно открыть непосредственно с помощью команды открыть в меню файл или программным путем с помощью методов Open и OpenXML коллекции книги. Если вы создаете XML-файлы для использования в Excel, вы также можете создать таблицы стилей для форматирования данных.

Создание новой папки с именем К:\ексцелдата. В этом примере программа будет хранить книги Excel в этой папке.

Создайте новую книгу для примера, в который необходимо выполнить запись:

Создайте новую книгу в Excel.
На листе Sheet1 новой книги введите FirstName в ячейке a1 и LastName в ячейке B1.
Выберите a1: B1.
В меню Вставка выберите пункт имя, а затем — команду определить. Введите имя MyTable и нажмите кнопку ОК.
Сохранение книги в виде C:\Exceldata\Book7.xls.
Закройте Excel.

Добавьте ссылку на библиотеку объектов Excel и основную сборку взаимодействия ADODB. Для этого выполните следующие действия:

On the Project menu, click Add Reference.
На вкладке Сеть найдите ADODB и нажмите кнопку Выбрать.

Обратите внимание, что в Visual Studio 2005 нет необходимости щелкать кнопку выбрать.
3. На вкладке COM найдите объектная Библиотека Microsoft Excel 10,0 или библиотека объектов Microsoft Excel 11,0, а затем нажмите кнопку Выбрать.

Обратите внимание, что в Visual Studio 2005 нет необходимости щелкать кнопку выбрать.

Примечание Если вы используете Microsoft Excel 2002, а вы еще не сделали это, корпорация Майкрософт рекомендует скачать и установить основные сборки взаимодействия Microsoft Office XP (PIA).

В диалоговом окне Добавление ссылок нажмите кнопку ОК, чтобы принять выбранные параметры.

Добавление элемента управления "поле со списком" и элемента управления "Кнопка" в форму Form1.

Добавьте обработчики событий для события загрузки формы и событий Click элемента управления Button:

В представлении конструктора для Form1.cs дважды щелкните элемент Form1.

Обработчик события Load для формы создан и отображается в Form1.cs.
2. В меню Вид выберите конструктор, чтобы переключиться в режим конструктора.
3. Дважды щелкните элемент Button1.

Обработчик события нажатия кнопки создается и отображается в Form1.cs.

В Form1.cs замените приведенный ниже код.

Добавьте следующие директивы using в директивы using в Form1.cs:

Нажмите клавишу F5 для сборки и запуска примера.

Ссылки

Для получения дополнительных сведений посетите следующий веб-сайт Майкрософт:

Как можно передать данные из этого 2-х мерного массива в таблицу Excel (то есть в файл формата .xlsx), чтобы эти табличные данные можно было потом обрабатывать в программе Excel ?

P.S. При этом файл таблицы (.xlsx) еще не создан.

Вопрос задан более трёх лет назад
1073 просмотра

Мне необходимо получить эти данные в табличном виде в файле программы Excel, для дальнейшей обработки этих данных в самой программе Excel.
Что касается "сериализации", то я это слово только сейчас узнал, и теперь читаю что это такое. Алексей Смирнов: Тогда потыкайте соседние с предоставленой ссылкой страницы в MSDN. Там взаимодействие с COM-объектом Excel Это слишком сложно для данной задачи. Проще работать с Excel через COM SaNNy32: ЧЕМ проще? Ничем. Просто у некоторых синдром утенка. Не, ну ладно бы сидели себе говнокодили молча, так ведь спорить лезут. COM Excel дрянь. У Excel еще поди разберись, какую библиотеку подключать, а зависимости от версии Office. Еще с завершением какой-то баг был (а если не завершать, то excel.exe останется в диспетчере висеть). Ну и чисто по количество строк, вызовов методов, аргументов функций - COM Excel навряд ли проще будет. На архитектурном уровне - тем более не проще, слоев абстракции с COM аж несколько, а NPOI напрямую с бинарником работает. SaNNy32: Вот я вам подробно перечислил, почему не проще, а сложнее, и почему багов можно хватануть именно с COM (проблема с версиями COM-сборки), + один баг даже вкратце вам описал, а вы опять своё прёте. И кто вы после этого? Век живи - век учись, и осваивай новое. Динозавры - вымирают.

SaNNy32: ну, первое, что бросилось в глаза - а завершать Application кто будет? Выполните такой вот код раз 20 и гляньте в диспетчер задач. Это норм?
Ну и да, почему мы окно эксела не скрываем? Когда окна на экране мелькают - это норм?

Второе (и главное) - где этот ваш Microsoft.Office.Interop.Excel?
Вот у меня Office 2003 стоит, а сборки такой я что-то не вижу. А раз ее нет, то придется добавлять ссылку непосредственно на COM-сборку, где и объектная модель другая (причем, в разных версиях вполне может быть разная) и вообще совместимость версий надо проверять.

Третье - какой нафиг ActiveSheet, если вы еще Workbook не открыли? Или у вас и без того работает, и после этого вы спрашиваете о подводных камнях?
Вы, видимо, сами впервые увидели этот COM, раз такое пишете.

Четвертое - вот здесь:
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
Когда в одной и той же библиотек один класс называется _Worksheet, а другой Worksheet, вот и запоминай и смотри не перепутай - это вообще норм библиотека?
Да я даже в Java такого не видал.

Пятое - это быстродействие.
Вся объектная модель офиса, неважно в Word или Excel, неважно откуда мы ее юзаем (из VBA или извне) - это всегда просто жуткие тормоза.
При каждом таком обращении к Cells, там внутри выполняется куча лишних действий.
Попробуйте хотя бы 1000x1000 ячеек так изменить и посмотрите, сколько это займет времени.

VZVZ: Это все отговорки.
1. Это лишь пример кода, а не законченная программа
2. Interop ставится вместе со студией
3. см п.1
4. Ничего помнить не надо, на то есть подсказки в IDE
5. Быстродействия достаточно для задачи ТС, думаю и для 1000х10000 тоже. Хотите, можете сравнить быстродействие с вашей библиотекой, обсудим.
В конце концов, не нужно выдавать неумение работать с объектной моделью Excel за сильную сторону библиотеки NPOI.
Кстати, по поводу вашей библиотеки NPOI. Она имеет право на жизнь. Возможно ее использовать на системе, где неустановлен Microsoft Office или для каких-то задач, где ненужно завязываться на библиотеки от Microsoft Office. Но для простых задач проще использовать объектную модель Excel с доступом через COM.

SaNNy32: 1. Так какого же черта вы незаконченную, кривую и вообще нерабочую программу приводите в пример?
Так нечестно! По моей ссылке с codeproject, есть рабочий исходник с NPOI без кривизны. Скачал - запустил.

2. Нет, не ставится со студией. Не лгите. У меня аж несколько студий стоит, однако в окне добавления ссылки на вкладке .NET Framework этой ссылки нету.
Можно, конечно, нажать кнопочку "Обзор" и поискать самому, ну так может вы подскажете, где поискать, если вы такой эксперт по COM-обертке Excel?

3. Если не открыть Workbook, то оно ВООБЩЕ работать не будет.

4. Подсказки подскажут, что есть классы _Worksheet и Worksheet, но разве они подскажут, какой где использовать?
Приходится реально пробовать методом тыка.

5. Можно и сравнить. Мог бы и на миллион поспорить. У меня - опыт и с вордом, и с экселем, и я прекрасно помню, как у меня все тормозило, и даже могу объяснить, почему
Может даже на codeproject напишу такую статью, чтоб все видели, какой убогий антиквариат этот COM. На досуге как-нибудь. Сейчас я другим занят.

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

Фактически это группа функций, которые одновременно обрабатывают группу данных и сразу выдают результат. Рассмотрим подробно работу с массивами функций в Excel.

Виды массивов функций Excel

Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:

В зависимости от расположения элементов различают массивы:

одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
двумерные (НЕСКОЛЬКО строк и столбцов, матрица).

Одномерные массивы бывают:

горизонтальными (данные – в строке);
вертикальными (данные – в столбце).

Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).

Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.

С помощью формул массива реально:

подсчитать количество знаков в определенном диапазоне;
суммировать только те числа, которые соответствуют заданному условию;
суммировать все n-ные значения в определенном диапазоне.

Когда мы используем формулы массива, Excel видит диапазон значений не как отдельные ячейки, а как единый блок данных.

Синтаксис формулы массива

Используем формулу массива с диапазоном ячеек и с отдельной ячейкой. В первом случае найдем промежуточные итоги для столбца «К оплате». Во втором – итоговую сумму коммунальных платежей.

Формула после нажатия Ctrl + Shift + Enter оказалась в фигурных скобках. Она подставилась автоматически в каждую ячейку выделенного диапазона.

Если попытаться изменить данные в какой-либо ячейке столбца «К оплате» - ничего не выйдет. Формула в массиве защищает значения диапазона от изменений. На экране появляется соответствующая запись:

Рассмотрим другие примеры использования функций массива Excel – рассчитаем итоговую сумму коммунальных платежей с помощью одной формулы.

Выделяем ячейку Е9 (напротив «Итого»).
Вводим формулу вида: =СУММ(C3:C8*D3:D8).
Нажимаем сочетание клавиш: Ctrl + Shift + Enter. Результат:

Формула массива в данном случае заменила две простые формулы. Это сокращенный вариант, вместивший всю необходимую информацию для решения сложной задачи.

Аргументы для функции – одномерные массивы. Формула просматривает каждый из них по отдельности, совершает заданные пользователем операции и генерирует единый результат.

Рассмотрим ее синтаксис:

Функции работы с массивами Excel

Предположим, в следующем месяце планируется увеличение коммунальных платежей на 10%. Если мы введем обычную формулу для итога =СУММ((C3:C8*D3:D8)+10%), то вряд ли получим ожидаемый результат. Нам нужно, чтобы каждый аргумент увеличился на 10%. Чтобы программа поняла это, мы используем функцию как массив.

Посмотрим, как работает оператор «И» в функции массива . Нам нужно узнать, сколько мы платим за воду, горячую и холодную. Функция: . Итого – 346 руб.
Функция «Сортировки» в формуле массива. Отсортируем суммы к оплате в порядке возрастания. Для списка отсортированных данных создадим диапазон. Выделим его. В строке формул вводим . Жмем сочетание Ctrl + Shift + Enter.
Транспонированная матрица. Специальная функция Excel для работы с двумерными массивами. Функция «ТРАНСП» возвращает сразу несколько значений. Преобразует горизонтальную матрицу в вертикальную и наоборот. Выделяем диапазон ячеек, где количество строк = числу столбцов в таблице с исходными данными. А количество столбцов = числу строк в исходном массиве. Вводим формулу: . Получается «перевернутый» массив данных.
Поиск среднего значения без учета нулей. Если мы воспользуемся стандартной функцией «СРЗНАЧ», то получим в результате «0». И это будет правильно. Поэтому вставляем в формулу дополнительное условие: 0;A1:A8))' >. Получаем:

Распространенная ошибка при работе с массивами функций – НЕ нажатие кодового сочетания «Ctrl + Shift + Enter» (никогда не забывайте эту комбинацию клавиш). Это самое главное, что нужно запомнить при обработке больших объемов информации. Правильно введенная функция выполняет сложнейшие задачи.

Читайте также: