C вставить массив в excel
В современном мире разработки приложений нередко встает необходимость работы с 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 отчет, поработали со стилями и даже решили пару попутных проблем.
При выполнении в каждой ячейке выделенной области получается значение "ИСТИНА". Как я понимаю функция OlePropertySet воспринимает SortArr как значение. При этом на запись SortArr[][] ругается компилятор. Чего я не знаю?
Считывание данных в двумерный массив из Excel файла
Здравствуйте дорогие форумчане! У меня возник вопрос ответ на который я сам не смог найти поэтому.
Считывание большой двумерной excel матрицы в статический массив
смотрел темы и так толком и не разобрался. суть задачи: имеется файл excel в котором тупо матрица.
Поиск быстрого способа передачи значений Excel-массив-Excel
Всем доброго дня! Не судите строго, так как я только начинаю изучать VB. Пишу программу для.
Excel: чтение в массив полей таблицы Excel?
Добрый день, появилась необходимость написать некий макрос, с VBA столкнулся впервые. Нагуглить.
Многие спрашивают - как ускорить вывод в Excel больших массивов данных,
слишком медленно все работает.
Наиболее идеальный вариант - воспользоваться компонентами XLSReadWrite2 или аналогичными.
Также, в проекте есть пример, как можно немного ускорить такой вывод,
для этого при выводе данных можно не выводить данные последовательно в каждую ячейку
в цикле, а лучше сформировать вариантный массив, и выполнить присвоение области (Range)
этого массива, только после этого делая Excel видимым.
Также более быстрая работа происходит при использовании библиотеки типов.
Из excel в массив
Здравствуйте, возник вопрос: как считать данные из Excel в массив? Когда столбец строка известны.
Чтение из Excel в массив
Доброго времени суток! Есть следующий класс: public class Data < public.
Запись из Excel в массив
Помогите, пожалуйста, написать программу, которая считывает из экселя данные (числа) и заносит в.
Считывание из Excel в массив
Помогите плиз со следующим вопросом. Необходимо считать данные из 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 для сборки и запуска примера.
Ссылки
Для получения дополнительных сведений посетите следующий веб-сайт Майкрософт:
Сводка
Создание клиента автоматизации для Excel
Чтобы заполнить диапазон, включающий несколько ячеек, без заполнения ячеек по одному, можно задать для свойства Value объекта Range двухмерный массив. Аналогичным образом можно получить двухмерный массив значений для нескольких ячеек одновременно, используя свойство Value. Приведенные ниже действия иллюстрируют этот процесс как для установки, так и для получения данных с помощью двумерных массивов.
Чтобы создать базовый клиент автоматизации, выполните действия, описанные в разделе "Создание клиента автоматизации" следующей статьи базы знаний Майкрософт:
На шаге 3 Добавьте в форму вторую кнопку и флажок. Измените идентификатор кнопки на IDC_GETVALUES и заголовок для получения значений. Измените Идентификатор флажка на IDC_CHECK и заголовок для заполнения строками.
На шаге 4 статьи выберите "Библиотека объектов Microsoft Excel 10,0", если вы автоматизируем Excel 2002 из Office XP. Расположение по умолчанию для Excel 2002 — C:\Program Files\Microsoft Office\Office10\Excel.exe. Если вы Автоматизируйте Microsoft Office Excel 2003, выберите пункт "Библиотека объектов Microsoft Excel 11,0". Расположение по умолчанию для Excel 2003 — C:\Program Files\Microsoft Office\Office11\Excel.exe. Выберите следующие интерфейсы Microsoft Excel:
- _Application
- _Workbook
- _Worksheet
- Диапазон
- Книги
- Листы
Добавьте следующие две общедоступные переменные члена в класс Каутопрожектдлг:
В диалоговом окне щелкните правой кнопкой мыши элемент IDC_CHECK и выберите команду Добавить переменную. Назовите переменную m_bFillWithStrings и нажмите кнопку Готово.
В диалоговом окне дважды щелкните Выполнить и замените приведенный ниже код.
Note (Примечание ) В Visual C++ 2005 необходимо добавить параметр компилятора поддержки общеязыковой среды выполнения (/CLR: oldSyntax) для успешной компиляции предыдущего примера кода. Чтобы добавить параметр компилятора для поддержки общеязыковой среды выполнения, выполните указанные ниже действия.
В меню проектвыберите пункт Свойства ИмяПроекта.
Note — заполнитель для имени проекта.
Разверните узел Свойства конфигурациии выберите Общие.
В области справа выберите поддержку общеязыковой среды выполнения, Старый синтаксис (/CLR: oldSyntax) в параметрах проекта Поддержка общеязыковой среды выполнения .
Чтобы выполнить поиск абонентской группы для пользователя в поле Абонентская группа (телефонный контекст), нажмите кнопку Обзор.
Для получения дополнительных сведений о параметрах компилятора для общеязыковой среды выполнения посетите следующий веб-сайт Microsoft Developer Network (MSDN):
Тестирование клиента автоматизации
Устранение неполадок
предупреждение C4003: недостаточно фактических параметров для макроса "Диалогбокса"
Ссылки
Дополнительные сведения о том, как использовать массивы для установки и извлечения данных Excel с помощью более ранних версий Visual Studio, можно найти в следующей статье базы знаний Майкрософт:
В настоящее время я пытаюсь записать данные из массива объектов в диапазон в Excel, используя следующий код, где objData - это просто массив строк:
Это почти работает, проблема в том, что диапазон заполняется, но каждая ячейка получает значение первого элемента в objData .
Обратное работает, т.е.
Вернет массив, содержащий все значения из рабочего листа, поэтому я не уверен, почему чтение и назначение работают по-разному.
Кто-нибудь когда-нибудь делал это успешно? В настоящее время я пишу массив ячейка за ячейкой, но он должен обрабатывать большое количество (> 50 000) строк, и поэтому это требует очень много времени.
Выбранный ответ не делает его сразу очевидным, поэтому, если у кого-то еще когда-либо возникла эта проблема, короче говоря, решение состоит в том, что вам нужно назначить 2D-массив ( object[,] ), даже если ваши данные могут уместиться только на одном измерение.
Это отрывок из моего метода, который преобразует DataTable (переменную dt ) в массив, а затем записывает массив в Range на листе ( wsh вар). Вы также можете изменить переменную topRow на любую строку, в которую вы хотите поместить массив строк.
Конечно, вам не нужно использовать промежуточный DataTable , как это сделал я, фрагмент кода предназначен только для демонстрации того, как массив может быть записан на рабочий лист за один вызов.
Интересно - это именно то, что я хочу сделать, но я не могу получить доступ к свойству Value объекта диапазона - я могу получить доступ только к Value2, и я думаю, что это может быть проблемой. Есть идеи, как я могу получить доступ к свойству Value? Какие прерывания вы используете?
Импортировано из библиотеки типов Excel (в моем случае это Office 2003). То есть я не использую основные сборки взаимодействия. Я только что добавил ссылку на Excel COM tlb в VS. Однако сейчас я не могу вспомнить точную причину отказа от использования PIA.
Я использую range.set_Value (Missing.Value, objectArray); Вам нужно убедиться, что массив имеет тот же размер, что и диапазон.
Это зависит от версии Office, для которой импортируются прерывания. Я использовал Office 2000 (а не 2003, как я сказал в предыдущем комментарии), в котором свойство Value не имеет дополнительных параметров. Начиная с версии 2003, у него есть один необязательный параметр в TLB, поэтому свойство транслируется в
Однако можно выполнить назначение через свойство Value2, что предпочтительнее, поскольку документация по взаимодействию рекомендует не использовать методы get_Value и set_Value по причинам, выходящим за рамки моего понимания.
Ключевым моментом, похоже, является размер массива объектов. Чтобы вызов работал, массив должен быть объявлен как двумерный, даже если вы назначаете только одномерные данные.
Я объявил свой массив данных как object[NumberofRows,1] , и вызов присваивания сработал.
Спасибо, похоже, что задан одномерный массив, он всегда будет записывать его по горизонтали (даже с вертикальным диапазоном, и в этом случае он повторно записывает первый элемент). чтобы заставить массив писать вертикально, вы должны сделать, как вы сказали, и превратить его в массив [n, 1] . Большое спасибо, потому что я, вероятно, никогда не обнаружил бы этого сам.
Вы можете поместить свои данные в набор записей и использовать метод Excel CopyFromRecordset - это намного быстрее, чем заполнение ячейки за ячейкой.
Вы можете создать набор записей из набора данных, используя этот код. Вам нужно будет провести несколько проб, чтобы увидеть, работает ли этот метод быстрее, чем то, что вы делаете сейчас.
Читайте также: