Копирование массива данных в эксель
Версия этой статьи для 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 для сборки и запуска примера.
Ссылки
Для получения дополнительных сведений посетите следующий веб-сайт Майкрософт:
Чтобы скопировать значения из диапазона ячеек в массив, необходимо объявить переменную универсального типа (As Variant) и присвоить ей значения диапазона ячеек с помощью оператора присваивания (=):
VBA Excel автоматически преобразует объявленную переменную в двумерный массив, соответствующий размерности диапазона ячеек, в нашем случае в массив — a(1 To 3, 1 To 3), и заполняет его значениями. Нумерация измерений массивов, созданных таким образом, начинается с единицы (1).
Можно, в этом случае, объявить сразу динамический массив, чтобы изначально указать, что эта переменная будет массивом. Так как свойством диапазона ячеек по-умолчанию в VBA Excel является значение (Value), его можно в коде явно не указывать, но, при желании, можно и указать. Получится такая конструкция, аналогичная первой:
Копирование значений из массива в диапазон ячеек
Значения в диапазон ячеек добавляются из массива с помощью оператора присваивания (=):
Обратите внимание, что вставить значения в диапазон ячеек можно только из двумерного массива. Размерность такого массива может начинаться с нуля (0). Количество элементов в измерениях массива должно совпадать с количеством строк и столбцов в диапазоне ячеек. Если вам нужно вставить значения в одну строку или в один столбец, укажите размерность единственной строки или единственного столбца как (0) или (1 To 1), если вы хотите использовать нумерацию измерений своего массива с единицы. Например, для записи десяти значений из массива в одну строку можно объявить такой массив — massiv(9, 0), или в один столбец — massiv(0, 9).
Для вставки значений в диапазон ячеек из массива идеально подойдет массив, созданный для копирования в него значений из диапазона. В этом случае, данные с рабочего листа Excel переносятся в массив, обрабатываются и, после обработки, вставляются обратно в ту же или другую таблицу на том же или другом рабочем листе.
Простейшие примеры обмена значениями
Эти примеры составлены так, чтобы вам не пришлось совершать лишних действий, просто скопируйте их в свой модуль любой книги Excel с поддержкой макросов и запустите по-очереди на выполнение.
Пример 1
Заполнение двумерного массива значениями и и их присвоение диапазону ячеек на рабочем листе Excel:
Приведу три способа Автоматического переноса данных с одного листа программы «Эксель» в другой.
Первый, самый простой и примитивный способ связи двух таблиц на разных листах документа -вставка данных при помощи опции специальной вставки.
Рассмотрим, как соединить две таблицы по шагам.
Первый шаг.
Необходимо выделить ту таблицу, из которой будут транслироваться данные во вторую таблицу.
Второй шаг.
Копировать информацию сочетанием клавиш ctrl+C или вызвав контекстное меню правой кнопкой мыши и кликнув по пункту меню «Копировать»
Третий шаг.
Перейти на лист документа «Excel», в который Вы планируете транслировать информацию из первой таблицы.
Четвертый шаг.
После вставки связи следует отформатировать вид ячеек – привести их к надлежащему виду.
При использовании данного метода роль второй таблицы («реципиента») играет сама сводная таблица.
Как обновить сводную таблицу
При клике правой кнопкой мыши по сводной таблице и нажатии на пункт «Обновить» сводная таблица автоматически перенесет все данные из связанного массива информации («таблицы донора»).
О том, как в «Эксель» создавать сводные таблицы подробно написано в статье:
Как делать сводные таблицы в программе «Excel» и для чего они нужны.
Правда нужно отметить, что этот способ подходит только пользователям Excel 2016 и пользователям Excel 2013и выше с установленной надстройкой «Power Query».
Смысл способа в следующем:
Из таблицы -Power Query
Источник данных для запроса Power Query
После выбора области данных появится окно настройки вида новой таблицы. В этом окне Вы можете настроить последовательность вывода столбцов и удалить ненужные столбцы.
После настройки вида таблицы нажмите кнопку «Закрыть и загрузить»
Обновление полученной таблицы происходит кликом правой кнопки мыши по названию нужного запроса в правой части листа (список «Запросы книги»). После клика правой кнопкой мыши в выпадающем контекстном меню следует нажать на пункт «Обновить»
Обновление запроса в PowerQuery
Предположим, у вас есть столбец, смешанный с повторяющимися и уникальными значениями, как показано на скриншоте ниже, как скопировать только уникальные значения без дубликатов и вставить их в новый диапазон в Excel? Эта статья может помочь вам решить эту проблему.
Копирование и вставка значений без дублирования с помощью расширенного фильтра
Вы можете использовать Расширенный фильтр функция для копирования и вставки значений пропускает дубликаты в Excel. Пожалуйста, сделайте следующее.
1. Выберите диапазон столбцов, в котором необходимо скопировать и вставить значения, пропуская дубликаты, и нажмите Данные > Фильтр, Затем нажмите Advanced как показано на скриншоте ниже.
2. в Расширенный фильтр в диалоговом окне выберите Скопировать в другое место вариант в Действие раздел, выберите ячейку, чтобы поместить уникальные значения в Скопировать в поле, отметьте Только уникальные записи поле и, наконец, щелкните OK кнопка. Смотрите скриншот:
Затем вы можете видеть, что только уникальные значения копируются и сразу же вставляются из выбранного диапазона в указанный новый диапазон.
Копирование и вставка значений без дублирования с помощью формулы массива
Вы также можете применить приведенную ниже формулу массива для извлечения только уникальных значений из указанного диапазона столбцов в Excel.
1. Выберите пустую ячейку для размещения первого уникального значения, введите в нее приведенную ниже формулу и нажмите кнопку Ctrl + Shift + Enter ключи одновременно.
=INDEX($A$2:$A$10, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$10), 0))
Заметки:
1). В формуле массива $ A $ 2: $ A $ 10 это диапазон, из которого вы будете копировать уникальные значения. 2). $ C $ 1: C1 это ячейка над ячейкой формулы.2. Продолжайте выбирать ячейку C2, затем перетащите ее маркер заполнения вниз, чтобы получить все уникальные значения, пока не отобразятся ошибки. Смотрите скриншот:
Скопируйте и вставьте значения, пропуская дубликаты с помощью Kutools for Excel
Этот раздел будет рекомендовать Выберите повторяющиеся и уникальные ячейки полезность Kutools для Excel. С помощью этой утилиты вы можете легко выбрать все значения, пропуская дубликаты в определенном диапазоне, а затем скопировать и вставить все уникальные значения в любое место, где вам нужно. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите диапазон, необходимый для получения только уникальных значений, затем нажмите Kutools > Выберите > Выберите повторяющиеся и уникальные ячейки. Смотрите скриншот:
2. в Выберите повторяющиеся и уникальные ячейки в диалоговом окне выберите Все уникальные (включая 1-е дубликаты) вариант в Правило раздел, а затем щелкните OK кнопка. Затем второй Выберите повторяющиеся и уникальные ячейки всплывает диалоговое окно, в котором указано, сколько уникальных ячеек выбрано, щелкните значок OK кнопка. Смотрите скриншот:
Ноты:
1). Помимо уникальных значений, с помощью этой утилиты вы также можете выбрать повторяющиеся значения; 2). Если вам нужно выделить все уникальные или повторяющиеся значения в выбранном диапазоне, установите флажок Заполните цвет фона or Цвет заливки шрифта пунктом.3. Теперь выбраны все значения, не повторяющиеся в указанном диапазоне. Скопируйте их, нажав Ctrl + C ключи, а затем вставьте в новый диапазон с помощью Ctrl + V ярлык.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: