Передача данных из excel в excel
В данной статье рассматриваются способы передачи данных в Microsoft Excel из приложения Microsoft Visual Basic. В статье также представлены преимущества и недостатки каждого из способов, что позволяет пользователю выбрать наиболее подходящий способ для конкретной ситуации.
- Передача данных по одной ячейке
- Передача массива данных в диапазон ячеек
- Передача набора записей ADO в диапазон ячеек с помощью способа CopyFromRecordset
- Создание в листе Excel объекта QueryTable, содержащего результаты запроса по источнику данных ODBC или OLEDB
- Передача данных в буфер обмена с последующей вставкой содержимого буфера обмена в лист Excel
- Передача данных в текстовый файл, использующий запятые или знаки табуляции в качестве разделителей, который Excel впоследствии может разобрать на ячейки листа
- Передача данных на лист Excel с помощью ADO
- Передача данных в Excel с помощью динамического обмена данными (DDE)
Примечание. При использовании Microsoft Office Excel 2007 для сохранения книги Excel 2007 можно использовать новый формат файла (XSLX). Для этого найдите следующую строку кода в приведенных ниже примерах:
Замените этот код следующей строкой кода:
Кроме того, база данных "Борей" не входит в состав Office 2007 по умолчанию. Тем не менее базу данных "Борей" можно загрузить с веб-узла русской версии Microsoft Office Online.
Перенос данных по одной ячейке с помощью автоматизации
Автоматизация позволяет передавать данные на лист Excel по одной ячейке:
216400 Автоматизация COM может привести к зависанию клиентского приложения в Win 95/98 (Эта ссылка может указывать на содержимое полностью или частично на английском языке).
Таким образом, передача данных по одной ячейке допустима только для небольших объемов данных. Для передачи больших объемов данных в Excel следует использовать один из способов, описанных ниже.
Примеры сценариев для автоматизации Excel см. в следующей статье базы знаний Майкрософт:
219151 Использование Visual Basic для автоматизации Microsoft Excel
Передача массива данных в диапазон ячеек листа с помощью программирования объектов
Массив данных можно одновременно передать в диапазон ячеек листа:
Передача большого объема данных с помощью массива происходит значительно быстрее, чем передача данных по одной ячейке. Обратите внимание на строку из приведенного выше сценария, которая одновременно передает данные в 300 ячеек листа:
Эта строка представляет всего два запроса к интерфейсу (один для объекта Range, возвращаемого методом Range, и один для объекта Range, возвращаемого методом Resize). При этом при передаче данных по одной ячейке потребовалось бы 300 запросов к интерфейсу для объектов Range. Поэтому по возможности рекомендуется выполнять массовый перенос данных, чтобы сократить число запросов к интерфейсу.
Перенос набора записей ADO в диапазон листа с помощью автоматизации
В Excel 2000 появился метод CopyFromRecordset, позволяющий переносить наборы данных ADO (или DAO) в диапазон ячеек листа. Приведенный ниже сценарий является примером автоматизации Excel 2000, Excel 2002 или Office Excel 2003 для переноса содержимого таблицы Orders образца базы данных "Борей" с помощью метода CopyFromRecordset.
Примечание. При использовании версии базы данных "Борей" для Office 2007 необходимо заменить в примере следующую строку кода:
Замените эту строку кода следующей строкой:
В Excel 97 также имеется метод CopyFromRecordset, однако его можно использовать только для набора записей DAO. CopyFromRecordset в Excel 97 не поддерживает ADO.
Дополнительные сведения об использовании ADO и метода CopyFromRecordset см. в следующей статье базы знаний Майкрософт:
246335 Использование программирования объектов для передачи данных из набора записей в Excel
(Эта ссылка может указывать на содержимое полностью или частично на английском языке)
Создание объекта QueryTable с помощью программирования объектов
- Созданием набора записей и его размещением на листе управляет Excel.
- Запрос можно сохранить в объекте QueryTable таким образом, чтобы в дальнейшем его можно было обновить и получить обновленный набор записей.
- При добавлении нового объекта QueryTable к листу можно переместить данные, уже находящиеся в ячейках листа, чтобы свободно разместить новые данные (см. свойство RefreshStyle).
Использование буфера обмена
Буфер обмена Windows также может использоваться как механизм передачи данных на лист Excel. Чтобы вставить данные в несколько ячеек листа, можно скопировать строку, в которой столбцы разделены знаками табуляции, а строки – символами возврата каретки. В приведенном ниже сценарии показано, как Visual Basic может использовать буфер обмена для передачи данных в Excel:
Создание текстового файла с разделителями, который Excel может разобрать на строки и столбцы
Excel может открывать файлы с разделителями-запятыми и знаками табуляции и разбирать данные по ячейкам. Этим можно воспользоваться при необходимости передачи большого объема данных в лист Excel с минимальным использованием автоматизации. Этот подход рекомендуется для приложений типа клиент-сервер, поскольку текстовый файл может генерироваться серверным приложением. Затем текстовый файл можно открыть с помощью клиентского приложения, при необходимости используя автоматизацию.
Ниже приведен сценарий, иллюстрирующий создание текстового файла с разделителями-запятыми из набора записей ADO:
Примечание. При использовании версии базы данных "Борей" для Office 2007 необходимо заменить в примере следующую строку кода:
Замените эту строку кода следующей строкой:
Если файл имеет расширение CSV, Excel открывает его без отображения мастера импорта текста и по умолчанию принимает, что в файле используются разделители-запятые. Если же файл имеет расширение TXT, Excel автоматически разбирает его, используя в качестве разделителей знаки табуляции.
В приведенном выше примере запуск Excel осуществлялся с помощью оператора Shell, а имя файла использовалось как аргумент командной строки. А в этом примере автоматизация не использовалась. Однако при желании можно применить минимум автоматизации, чтобы открыть текстовый файл и сохранить его в формате книги Excel:
Дополнительные сведения об операциях ввода-вывода файлов из приложения Visual Basic см. в следующей статье базы знаний Майкрософт:
172267 RECEDIT.VBP демонстрирует ввод-вывод файлов в Visual Basic recedit.rar
(Эта ссылка может указывать на содержимое полностью или частично на английском языке)
Передача данных на лист Excel с помощью ADO
С помощью Microsoft Jet OLE DB Provider можно добавлять записи в таблицу существующей книги Excel. «Таблицей» в Excel считается диапазон с заданным именем. Первая строка диапазона содержит заголовки (или имена полей), а все последующие строки – записи. Ниже приведен пример создания книги с пустой таблицей MyTable.
Excel 97, Excel 2000 и Excel 2003
1. Откройте новую книгу Excel.
2. Добавьте следующие заголовки в ячейки A1:B1 листа Sheet1:
A1: FirstName B1: LastName
3. Выровняйте ячейку B1 по правому краю.
4. Выделите A1:B1.
5. В меню Вставка выберите Имя, а затем Присвоить. Введите имя MyTable и нажмите кнопку OK.
6. Сохраните новую книгу как C:\Book1.xls и закройте Excel.
Чтобы добавить записи в таблицу MyTable с помощью ADO, понадобится сценарий приблизительно следующего вида:
Excel 2007
1. В Excel 2007 создайте книгу.
2. Добавьте следующие заголовки в ячейки A1:B1 листа "Лист1":
A1: FirstName B1: LastName
3. Выровняйте ячейку B1 по правому краю.
4. Выделите диапазон A1:B1.
5. На ленте откройте вкладку Формулы и выберите пункт Определить имя. Введите имя MyTable и нажмите кнопку ОК.
6. Сохраните новую книгу как C:\Book1.xlsx и закройте Excel.
Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, подобный приведенному ниже.
При подобном добавлении записей в таблицу производится форматирование книги. В приведенном выше примере новые поля, добавляемые в столбец B, выравниваются по правому краю. Каждая запись, добавляемая в строку, форматируется так же, как предыдущая.
Обратите внимание на то, что при добавлении в ячейку или ячейки листа запись заменяет любые данные, находившиеся в этих ячейках ранее; другими словами, строки листа не сдвигаются вниз при добавлении новых записей. Это следует иметь в виду при планировании размещения данных на листе.
Примечание. Обновление данных на листе Excel с помощью ADO или DAO невозможно в среде Visual Basic для приложений в Access после установки пакета обновления 2 (SP2) для Office 2003 или обновления для Access 2002, описанного в статье 904018 базы знаний Майкрософт. Однако этот способ можно использовать в среде Visual Basic для приложений в других приложениях Office, например в Word, Excel и Outlook. Дополнительные сведения см. в следующих статьях базы знаний Майкрософт:
904953 Запрещается вносить изменения, добавлять или удалять данные, источником которых являются книги Excel в Office Access 2003 или в Access 2002
904018 Описание обновления для Access 2002: от 18 октября 2005 г.
Дополнительные сведения об использовании ADO для доступа к книгам Excel см. в следующих статьях базы знаний Майкрософт:
195951 Создание запросов и обновление данных Excel с помощью ADO со страниц ASP
(Эта ссылка может указывать на содержимое полностью или частично на английском языке)
Передача данных в Excel с помощью DDE
Наряду с программированием объектов DDE является способом связи с Excel и передачи данных; однако, в противоположность автоматизации и COM, DDE больше не является часто используемым способом связи с другими приложениями и должен использоваться только при отсутствии других решений.
- LinkPoke для вставки данных в указанный диапазон ячеек
. - LinkExecute для отправки команд, которые будет выполнять Excel.
Примечание. При использовании Excel 2007 для сохранения книг можно использовать новый формат файла (XLSX). Обязательно обновите имя файла в приведенном ниже примере кода.
Примечание. В данном примере Text1 представляет элемент управления Text Box формы Visual Basic:
При использовании метода LinkPoke с Excel необходимо указать диапазон в формате строка-столбец (R1C1) для LinkItem. Если данные вставляются в несколько ячеек, можно использовать строку, в которой столбцы разделены символами табуляции, а строки – символами возврата каретки.
Если метод LinkExecute используется для выполнения команды в Excel, синтаксис команды должен соответствовать языку Excel Macro Language (XLM). Документация по XLM не входит в состав Excel 97 и более поздних версий. Дополнительные сведения о получении документации по XLM см. в следующей статье базы знаний Майкрософт:
143466 Файл Macro97.exe доступен для загрузки. macro97.rar
(Эта ссылка может указывать на содержимое полностью или частично на английском языке)
DDE не является рекомендуемым способом связи с Excel. Программирование объектов предоставляет больше возможностей и обеспечивает лучший доступ к новым функциям Excel.
Существуют различные способы переноса данных из книги Excel в базы данных Access. Можно скопировать данные с открытого листа и вставить их в таблицу Access, импортировать лист в новую или существующую таблицу либо связать лист с базой данных Access.
В этой статье приведено подробное описание процедуры импорта или связывания данных Excel с классическими базами данных Access.
В этой статье
Общее представление об импорте данных из Excel
Если требуется сохранить данные одного или нескольких листов Excel в Access, следует импортировать содержимое листа в новую или существующую базу данных Access. При импорте данных в Access создается их копия в новой или существующей таблице, а исходный лист Excel не изменяется.
Стандартные сценарии импорта данных Excel в Access
Опытному пользователю Excel требуется использовать Access для работы с данными. Для этого необходимо переместить данные из листов Excel в одну или несколько новых таблиц Access.
В отделе или рабочей группе используется Access, но иногда данные поступают в формате Excel, и их необходимо объединять с базами данных Access. Требуется выполнить импорт полученных листов Excel в базу данных.
Пользователь применяет Access для управления данными, однако получает еженедельные отчеты от остальных участников команды в виде книг Excel. Требуется организовать процесс импорта таким образом, чтобы данные импортировались в базу данных каждую неделю в заданное время.
Первый импорт данных из Excel
Сохранить книгу Excel в виде базы данных Access невозможно. В Excel не предусмотрена функция создания базы данных Access с данными Excel.
При открытии книги Excel в Access (для этого следует открыть диалоговое окно Открытие файла, выбрать в поле со списком Тип файлов значение Файлы Microsoft Office Excel и выбрать файл) создается ссылка на эту книгу, но данные из нее не импортируются. Связывание с книгой Excel кардинально отличается от импорта листа в базу данных. Дополнительные сведения о связывании см. ниже в разделе Связывание с данными Excel.
Импорт данных из Excel
В этом разделе описано, как подготовиться к операции импорта, выполнить ее и как сохранить параметры импорта в виде спецификации для повторного использования. Помните, что данные можно одновременно импортировать только из одного листа. Импортировать все данные из книги за один раз невозможно.
Подготовка листа
Найдите исходный файл и выделите лист с данными, которые требуется импортировать в Access. Если необходимо импортировать лишь часть данных листа, можно задать именованный диапазон, содержащий только те ячейки, которые требуется импортировать.
Определение именованного диапазона (необязательно)
Перейдите в Excel и откройте лист, данные из которого нужно импортировать.
Выделите диапазон ячеек, содержащих данные, которые необходимо импортировать.
Щелкните выделенный диапазон правой кнопкой мыши и выберите пункт Имя диапазона или Определить имя.
В диалоговом окне Создание имени укажите имя диапазона в поле Имя и нажмите кнопку ОК.
Имейте в виду, что в ходе одной операции импорта можно импортировать лишь один лист. Чтобы импортировать данные нескольких листов, операцию импорта следует повторить для каждого листа.
Просмотрите исходные данные и выполните необходимые действия в соответствии с приведенной ниже таблицей.
Число исходных столбцов, которые необходимо импортировать, не должно превышать 255, т. к. Access поддерживает не более 255 полей в таблице.
Пропуск столбцов и строк
В исходный лист или именованный диапазон рекомендуется включать только те строки и столбцы, которые требуется импортировать.
Смещ_по_строкам В ходе операции импорта невозможно фильтровать или пропускать строки.
Столбцы. В ходе операции экспорта невозможно пропускать столбцы, если данные добавляются в существующую таблицу.
Убедитесь, что ячейки имеют табличный формат. Если лист или именованный диапазон включает объединенные ячейки, их содержимое помещается в поле, соответствующее крайнему левому столбцу, а другие поля остаются пустыми.
Пустые столбцы, строки и ячейки
Удалите все лишние пустые столбцы и строки из листа или диапазона. При наличии пустых ячеек добавьте в них отсутствующие данные. Если планируется добавлять записи к существующей таблице, убедитесь, что соответствующие поля таблицы допускают использование пустых (отсутствующих или неизвестных) значений. Поле допускает использование пустых значений, если свойство Обязательное поле (Required) имеет значение Нет, а свойство Условие на значение (ValidationRule) не запрещает пустые значения.
Чтобы избежать ошибок при импорте, убедитесь, что каждый исходный столбец содержит данные одного типа в каждой строке. Access сканирует первые восемь исходных строк, чтобы определить тип данных полей таблицы. Настоятельно рекомендуем убедиться в том, что первые восемь исходных строк не смешивают значения с разными типами данных ни в каких столбцах. В противном случае Access может не назначить столбецу правильный тип данных.
Рекомендуется также отформатировать все исходные столбцы в Excel и назначить им определенный формат данных перед началом операции импорта. Форматирование является необходимым, если столбец содержит значения с различными типами данных. Например, столбец "Номер рейса" может содержать числовые и текстовые значения, такие как 871, AA90 и 171. Чтобы исключить отсутствующие или неверные значения, выполните указанные ниже действия.
Щелкните заголовок столбца правой кнопкой мыши и выберите пункт Формат ячеек.
На вкладке Числовой в группе Категория выберите формат. Для столбца "Номер рейса" лучше выбрать значение Текстовый.
Если исходные столбцы отформатированы, но все же содержат смешанные значения в строках, следующих за восьмой строкой, в ходе операции импорта значения могут быть пропущены или неправильно преобразованы. Сведения о разрешении этих вопросов см. в разделе Разрешение вопросов, связанных с отсутствующими и неверными значениями.
Если первая строка листа или именованного диапазона содержит имена столбцов, в Access можно указать, что данные первой строки должны рассматриваться в ходе операции импорта как имена полей. Если исходный лист или диапазон не содержит имен, рекомендуется добавить их в исходные данные до операции импорта.
Примечание: Если планируется добавить данные в существующую таблицу, убедитесь, что имя каждого столбца в точности соответствует имени поля. Если имя столбца отличается от имени соответствующего поля в таблице, операция импорта завершится неудачей. Чтобы просмотреть имена полей, откройте таблицу в Access в режиме конструктора.
Закройте исходную книгу, если она открыта. Если исходный файл остается открытым в ходе операции импорта, могут возникнуть ошибки преобразования данных.
Подготовка конечной базы данных
Откройте базу данных Access, в которой будут храниться импортируемые данные. Убедитесь, что база данных доступна не только для чтения и что есть права на ее изменение.
Если ни одна из существующих баз данных не подходит для хранения импортируемых данных, создайте пустую базу данных. Для этого выполните указанные ниже действия.
Откройте вкладку Файл, нажмите кнопку Создать и выберите пункт Пустая база данных.
Перед началом операции импорта следует определить, в какой таблице будут храниться данные: в новой или существующей.
Создание новой таблицы. Если необходимо сохранить данные в новой таблице, в Access создается таблица, в которую добавляются импортируемые данные. Если таблица с указанным именем уже существует, содержимое существующей таблицы перезаписывается импортируемыми данными.
Добавление в существующую таблицу. При добавлении данных в существующую таблицу строки из листа Excel добавляются в указанную таблицу.
Следует помнить, что ошибки в ходе операции добавления зачастую объясняются тем, что исходные данные не соответствуют структуре и параметрам полей в конечной таблице. Чтобы избежать таких ошибок, откройте таблицу в режиме конструктора и проверьте указанные ниже параметры.
Первая строка. Если первая строка исходного листа или диапазона не содержит заголовки столбцов, убедитесь, что расположение и тип данных каждого столбца соответствуют нужному полю таблицы. Если же первая строка содержит заголовки столбцов, совпадение порядка следования столбцов и полей необязательно, но имя и тип данных каждого столбца должны в точности совпадать с именем и типом данных соответствующего поля.
Отсутствующие или лишние поля. Если одно или несколько полей исходного листа отсутствуют в конечной таблице, их следует добавить до начала операции импорта. Если же таблица содержит поля, которые отсутствуют в исходном файле, их не требуется удалять из таблицы при условии, что они допускают использование пустых значений.
Совет: Поле допускает использование пустых значений, если его свойство Обязательное поле (Required) имеет значение Нет, а свойство Условие на значение (ValidationRule) не запрещает пустые значения.
Индексированные поля. Если свойство Индексировано поля таблицы имеет значение Да (без повторов), соответствующий столбец исходного листа или диапазона должен содержать уникальные значения.
Для выполнения операции импорта перейдите к указанным ниже действиям.
Запуск операции импорта
Расположение мастера импорта или связывания зависит от используемой версии Access. Выполните действия, которые соответствуют вашей версии Access.
Если вы используете последнюю версию Access или Access 2019, доступную по подписке на Microsoft 365, на вкладке "Внешние данные" в группе "Импорт & Связь" нажмите кнопку "Новый источник данных > из файла > Excel".
Если вы используете Access 2016, Access 2013 или Access 2010, на вкладке Внешние данные в группе Импорт и связи нажмите кнопку Excel.
Примечание: Вкладка Внешние данные доступна только в том случае, если открыта база данных.
В диалоговом окне Внешние данные - Электронная таблица Excel в поле Имя файла укажите имя файла Excel, содержащего данные, которые необходимо импортировать.
Чтобы указать импортируемый файл, нажмите кнопку Обзор и воспользуйтесь диалоговым окном Открытие файла.
Укажите способ сохранения импортируемых данных.
Чтобы сохранить данные в новой таблице, выберите вариант Импортировать данные источника в новую таблицу в текущей базе данных. Позднее будет предложено указать имя этой таблицы.
Чтобы добавить данные в существующую таблицу, выберите параметр Добавить копию записей в конец таблицы и выберите таблицу в раскрывающемся списке. Этот параметр недоступен, если база данных не содержит таблиц.
Сведения о связывании с источником данных путем создания связанной таблицы см. ниже в разделе Связывание с данными Excel.
Будет запущен мастер импорта электронных таблиц, который поможет выполнить импорт. Перейдите к дальнейшим действиям.
Использование мастера импорта электронных таблиц
На первой странице мастера выберите лист, содержащий данные, которые необходимо импортировать, и нажмите кнопку Далее.
На второй странице мастера щелкните элемент листы или именованные диапазоны, выберите лист или именованный диапазон, который необходимо импортировать, и нажмите кнопку Далее.
Если первая строка исходного листа или диапазона содержит имена полей, выберите вариант Первая строка содержит заголовки столбцов и нажмите кнопку Далее.
Если данные импортируются в новую таблицу, заголовки столбцов используются в Access в качестве имен полей в таблице. Эти имена можно изменить в ходе операции импорта или после ее завершения. Если данные добавляются к существующей таблице, убедитесь, что заголовки столбцов исходного листа в точности соответствуют именам полей конечной таблицы.
Если данные добавляются к существующей таблице, перейдите к действию 6. Если данные добавляются в новую таблицу, выполните оставшиеся действия.
Мастер предложит просмотреть свойства полей. Щелкните столбец в нижней части страницы, чтобы отобразить свойства нужного поля. При необходимости выполните указанные ниже действия.
Просмотрите и измените имя и тип данных конечного поля.
Чтобы создать индекс для поля, присвойте свойству Индексировано (Indexed) значение Да.
Чтобы пропустить весь исходный столбец, установите флажок Не импортировать (пропустить) поле.
Настроив параметры, нажмите кнопку Далее.
На следующем экране задайте первичный ключ для таблицы. При выборе варианта автоматически создать ключ Access добавляет поле счетчика в качестве первого поля конечной таблицы и автоматически заполняет его уникальными значениями кодов, начиная с 1. После этого нажмите кнопку Далее.
Сведения о том, как запустить сохраненную спецификацию импорта или экспорта, см. в статье Запуск сохраненной спецификации импорта или экспорта.
Сведения о том, как запланировать выполнение задач импорта и связывания в определенное время, см. в статье Планирование спецификации импорта или экспорта.
Разрешение вопросов, связанных с отсутствующими и неверными значениями
Откройте целевую таблицу в режиме таблицы, чтобы убедиться, что в таблицу были добавлены все данные.
Откройте таблицу в режиме конструктора, чтобы проверить типы данных и другие свойства полей.
В приведенной ниже таблице описаны действия по разрешению проблем, связанных с отсутствующими или неверными значениями.
Графические элементы, такие как логотипы, диаграммы и рисунки, не импортируются. Их следует добавить в базу данных вручную после завершения операции импорта.
Импортируются результаты вычисляемого столбца или ячейки, но не базовая формула. В ходе операции импорта можно указать тип данных, совместимый с результатами формулы, например числовой.
Значения TRUE или FALSE и -1 или 0
Если исходный лист или диапазон включает столбец, который содержит только значения TRUE или FALSE, в Access для этого столбца создается логическое поле, в которое вставляется значение -1 или 0. Если же исходный лист или диапазон включает столбец, который содержит только значения -1 и 0, в Access для этого столбца по умолчанию создается числовое поле. Чтобы избежать этой проблемы, можно изменить в ходе импорта тип данных поля на логический.
При импорте данных в новую или существующую таблицу приложение Access не поддерживает многозначные поля, даже если исходный столбец содержит список значений, разделенных точками с запятой (;). Список значений обрабатывается как одно значение и помещается в текстовое поле.
В случае усечения данных в столбце таблицы Access попытайтесь увеличить ширину столбца в режиме таблицы. Если не удается решить проблему с помощью этого способа, это означает, что объем данных в числовом столбце Excel слишком велик для конечного поля в Access. Например, в базе данных Access свойство FieldSize конечного поля может иметь значение Байт, а исходные данные могут содержать значение больше 255. Исправьте значения в исходном файле и повторите операцию импорта.
Чтобы обеспечить правильное отображение значений в режиме таблицы, может потребоваться изменить свойство Формат некоторых полей в режиме конструктора. Ниже приведены примеры.
После завершения импорта в логическом поле в режиме таблицы отображаются значения -1 и 0. Чтобы устранить эту проблему, необходимо после завершения импорта изменить значение свойства Формат этого поля на Да/Нет для отображения флажков.
Даты в длинном и среднем форматах отображаются в Access как краткие даты. Чтобы устранить эту проблему, откройте конечную таблицу в Access в режиме конструктора и измените свойство Формат поля даты на Длинный формат даты или Средний формат даты.
Примечание: Если исходный лист содержит элементы форматирования RTF, например полужирный шрифт, подчеркивание или курсив, текст импортируется без форматирования.
Повторяющиеся значения (нарушение уникальности ключа)
Импортируемые записи могут содержать повторяющиеся значения, которые невозможно сохранить в поле первичного ключа в конечной таблице или в поле, для которого свойству Индексировано присвоено значение Да (без повторов). Удалите повторяющиеся значения в исходном файле и повторите операцию импорта.
Значения дат, сдвинутые на 4 года
Значения полей дат, импортированных с листа Excel, оказываются сдвинуты на четыре года. В Excel для Windows используется система дат 1900, в которой даты представляются целыми числами от 1 до 65 380, соответствующими датам от 1 января 1900 г. до 31 декабря 2078 г. В Excel для Macintosh используется система дат 1904, в которой даты представляются целыми числами от 0 до 63 918, соответствующими датам от 1 января 1904 г. до 31 декабря 2078 г.
Прежде чем импортировать данные, измените систему дат для книги Excel или выполните после добавления данных запрос на обновление, используя выражение [имя поля даты] + 1462 для корректировки дат.
Отформатируйте исходные столбцы.
Переместите строки таким образом, чтобы первые восемь строк каждого столбца не содержали значения с разными типами данных.
В ходе операции импорта выберите подходящий тип данных для каждого поля. Если тип данных указан неправильно, после завершения операции весь столбец может содержать пустые или неверные значения.
Описанные выше действия позволяют свести к минимуму количество пустых значений. В приведенной ниже таблице представлены ситуации, в которых пустые значения все же будут появляться.
В этой статье приводится информация о подключении, которую вы должны предоставить, и необходимые настройки подключения для импорта данных из Excel и экспорта данных в Excel с помощью SQL Server Integration Services (SSIS).
В следующих разделах содержится вся информация, которая потребуется для успешной работы с приложением Excel из служб SSIS, а также для понимания и устранения распространенных проблем:
Средства, которые можно использовать.
Файлы, которые вам потребуются.
Сведения о подключении, которые необходимо предоставить, и параметры, которые необходимо настроить для загрузки данных в/из Excel с использованием SSIS.
-
в качестве источника данных.
- Укажите имя файла Excel и путь к нему.
- Выберите версию Excel.
- Укажите, содержит ли первая строка имена столбцов.
- Укажите лист или диапазон, который содержит данные.
Известные проблемы и ограничения.
- Проблемы с типами данных.
- Проблемы с импортом.
- Проблемы с экспортом.
Средства, которые можно использовать
Вы можете импортировать и экспортировать данные в Excel с использованием SSIS при помощи любого из следующих инструментов.
SQL Server Integration Services (SSIS) . Создайте пакет SSIS, настроив для него Excel в качестве источника или назначения с помощью диспетчера подключений Excel. (В этой статье не рассматривается создание пакетов SSIS.)
Получение файлов, необходимых для подключения к Excel
Прежде чем импортировать или экспортировать данные в Excel с использованием SSIS, вам может потребоваться скачать компоненты подключения для Excel, если они еще не установлены. По умолчанию компоненты подключений для Excel не устанавливаются.
Используйте таблицу из статьи Не удалось использовать интерфейсы Access ODBC, OLEDB или DAO за пределами Office Click-to-Run, чтобы понять, нужны ли дополнительные компоненты для вашей среды.
Примечание. Системные драйверы Office поддерживаются только в определенных случаях, конкретные руководства см. в статье Рекомендации по автоматизации Office на стороне сервера.
Назначение Excel в качестве источника данных
Сначала необходимо указать, что вам требуется установить подключение к Excel.
В службах SSIS
В службах SSIS создайте диспетчер соединений Excel для подключения к файлу источника или назначения Excel. Создать диспетчер соединений можно несколькими способами:
Щелкните правой кнопкой в области Диспетчеры соединений и выберите команду Создать соединение. В диалоговом окне Добавление диспетчера соединений со службами SSIS выберите EXCEL и затем Добавить.
В меню Службы SSIS выберите Создать соединение. В диалоговом окне Добавление диспетчера соединений со службами SSIS выберите EXCEL и затем Добавить.
Диспетчер соединений необходимо создавать в то время, как вы настраиваете источник Excel или назначение Excel на странице Диспетчер соединений в редакторе источника Excel или в редакторе назначения Excel.
В мастере импорта и экспорта SQL Server
В мастере импорта и экспорта на странице Выбор источника данных или Выбор назначения выберите Microsoft Excel в списке Источник данных.
Если вы не видите Excel в списке источников данных, убедитесь, что используется 32-разрядная версия мастера. Компоненты подключений для Excel обычно представляют собой 32-разрядные файлы и не отображаются в 64-разрядной версии мастера.
Файл Excel и путь к нему
В первую очередь необходимо предоставить имя файла Excel и путь к нему. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS либо на странице Выбор источника данных или Выбор назначения в мастере импорта и экспорта.
Введите имя файла и путь к нему в следующем формате:
Для файла на локальном компьютере: C:\TestData.xlsx.
Для файла в общей сетевой папке: \\Sales\Data\TestData.xlsx.
Также можно нажать кнопку Обзор и выбрать электронную таблицу в диалоговом окне Открыть.
Подключить защищенный паролем файл Excel нельзя.
Версия Excel
Далее следует предоставить сведения о версии файла Excel. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS либо на странице Выбор источника данных или Выбор назначения в мастере импорта и экспорта.
Выберите версию Microsoft Excel, в которой был создан файл, или другую совместимую версию. Например, если вам не удалось установить компоненты подключений для версии 2016, вы можете установить компоненты для версии 2010 и выбрать в этом списке вариант Microsoft Excel 2007-2010.
Если у вас установлены старые версии компонентов подключений, вы не сможете выбрать более новые версии Excel в этом списке. Список Версия Excel содержит все версии Excel, поддерживаемые службами SSIS. Наличие элементов в этом списке не означает, что необходимые компоненты подключений были установлены. Например, вариант Microsoft Excel 2016 будет присутствовать в этом списке даже в том случае, если у вас не установлены компоненты подключений для версии 2016.
Первая строка содержит имена столбцов
Если вы импортируете данные из Excel, далее необходимо указать, содержит ли первая строка данных имена столбцов. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS или на странице Выбор источника данных в мастере импорта и экспорта.
- Если этот параметр отключен, поскольку в данных источника отсутствуют имена столбцов, мастер будет использовать в качестве заголовков столбцов значения F1, F2 и т. д.
- Если данные содержат имена столбцов, но этот параметр отключен, мастер импортирует строку имен столбцов как первую строку данных.
- Если данные не содержат имена столбцов, но этот параметр включен, мастер использует первую строку данных источника как имена столбцов. В этом случае первая строка в данных источника не включается в сами данные.
Если вы экспортируете данные из Excel и включили этот параметр, первая строка экспортированных данных будет включать имена столбцов.
Листы и диапазоны
В качестве источника или назначения для данных можно использовать три типа объектов Excel: лист, именованный диапазон или неименованный диапазон ячеек, который задается с помощью адреса.
Лист Чтобы указать лист, добавьте в конец имени листа символ $ и окружите строку разделителями, например [Листt1$] . Также можно выполнить поиск имени, заканчивающегося символом $ , в списке существующих таблиц и представлений.
Именованный диапазон Чтобы указать именованный диапазон, используйте имя диапазона, например Мой_диапазон. Также можно выполнить поиск имени, не заканчивающегося символом $ , в списке существующих таблиц и представлений.
Неименованный диапазон Чтобы указать диапазон ячеек, которым не были заданы имена, добавьте символ $ после имени листа, добавьте спецификацию диапазона и окружите строку разделителями, например [Лист1$A1:B4] .
Чтобы выбрать или указать тип объекта Excel, который вы хотите использовать в качестве источника или назначения для данных, выполните одно из следующих действий:
В службах SSIS
В службах SSIS на странице Диспетчер соединений в редакторе источника Excel или редакторе назначения Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, выберите Таблица или представление в разделе Режим доступа к данным. Затем выберите лист или именованный диапазон в списке Имя листа Excel.
Чтобы использовать неименованный диапазон, который задается по адресу, выберите Команда SQL в разделе Режим доступа к данным. Затем в поле Текст команды SQL введите запрос, аналогичный представленному ниже примеру:
В мастере импорта и экспорта SQL Server
В мастере импорта и экспорта выполните одно из следующих действий:
При импорте из Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, на странице Выбор копирования таблицы или запроса выберите Скопировать данные из одной или нескольких таблиц или представлений. Затем на странице Выбор исходных таблиц и представлений в столбце Источник укажите исходные листы и именованные диапазоны.
Чтобы использовать неименованный диапазон, который задается по адресу, на странице Выбор копирования таблицы или запроса выберите Написать запрос, указывающий данные для передачи. Затем на странице Определение исходного запроса введите запрос, аналогичный показанному ниже:
При экспорте в Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, на странице Выбор исходных таблиц и представлений в столбце Назначение выберите целевые листы и именованные диапазоны.
Чтобы использовать неименованный диапазон, который задается по адресу, на странице Выбор исходных таблиц и представлений в столбце Назначение укажите диапазон в следующем формате без использования разделителей: Sheet1$A1:B5 . Мастер автоматически добавит разделители.
После выбора или ввода объектов Excel для импорта или экспорта вы также можете выполнить следующие действия на странице Выбор исходных таблиц и представлений мастера:
Проверить сопоставления столбцов между источником и назначением, выбрав команду Изменить сопоставления.
Выполнить предварительный просмотр данных, чтобы убедиться в правильности выбора, с помощью команды Предварительный просмотр.
Проблемы с типами данных
Типы данных
Драйвер Excel распознает только ограниченный набор типов данных. Например, все числовые столбцы воспринимаются как тип double (DT_R8), а все строковые столбцы (кроме столбцов типа memo) воспринимаются как строки в Юникоде длиной 255 символов (DT_WSTR). Службы SSIS сопоставляют типы данных Excel следующим образом:
Числовой — с плавающей запятой двойной точности (DT_R8)
Денежный — денежный (DT_CY)
Логический — логический (DT_BOOL)
Дата и время — datetime (DT_DATE)
Строковый — строка в Юникоде длиной в 255 символов (DT_WSTR)
Memo — текстовый поток в Юникоде (DT_NTEXT)
Преобразования типов данных и длины по умолчанию
В службах SSIS неявное преобразование типов данных не выполняется. В результате, возможно, потребуется использовать преобразование "Производный столбец" или "Преобразование данных" для явного преобразования данных Excel до их загрузки в назначение, отличное от Excel, либо для преобразования данных из источника, отличного от Excel, до их загрузки в назначение Excel.
Ниже приведены некоторые примеры преобразований, которые могут потребоваться:
Преобразование между строковыми столбцами Excel в Юникоде и строковыми столбцами в формате с конкретными кодовыми страницами, отличными от Юникода.
Преобразование между строковыми столбцами Excel длиной в 255 символов и строковыми столбцами другой длины.
Преобразование между числовыми столбцами Excel с плавающей запятой двойной точности и числовыми столбцами других типов.
Если вы используете мастер импорта и экспорта и вам требуется выполнить некоторые из этих преобразований данных, мастер выполнит настройку необходимых преобразований автоматически. В результате, даже если вам требуется использовать пакет служб SSIS, часто бывает полезно создать первичный пакет с помощью мастера импорта и экспорта. В таком случае мастер создаст и настроит диспетчеры соединений, источники, преобразования и назначения автоматически.
Проблемы с импортом
Пустые строки
Если в качестве источника указан лист или диапазон, драйвер считывает непрерывный блок ячеек, начиная с первой непустой ячейки в верхнем левом углу листа или диапазона. В результате данные могут начинаться не со строки 1, однако в данных источника не должны присутствовать пустые строки. Например, пустые строки не допускаются между заголовками столбцов и строками данных, а также после названия в верхней части листа.
Если над данными располагаются пустые строки, вы не сможете выполнить их запрос в качестве листа. В Excel необходимо выбрать диапазон данных, присвоить ему имя и выполнить запрос к именованному диапазону, а не к листу.
Отсутствующие значения
Драйвер Excel считывает определенное количество строк (по умолчанию 8 строк) в указанном источнике для определения типа данных каждого столбца. Если столбец содержит смешанные типы данных, особенно если числовые данные смешаны с текстовыми данными, драйвер принимает решение в пользу того типа данных, которого больше, и возвращает значения NULL в ячейки, содержащие данные другого типа. (В случае равенства преимущество получает числовой тип.) Большинство параметров форматирования ячеек в листе Excel не затрагивает это определение типа данных.
Можно изменить поведение драйвера Excel, указав режим импорта для импорта всех значений в виде текста. Чтобы указать режим импорта, добавьте IMEX=1 к значению расширенных свойств в строке соединения диспетчера соединений с Excel в окне "Свойства".
Усеченный текст
Когда драйвер определяет, что столбец Excel содержит текстовые данные, он выбирает тип данных (строковый или memo) на основании самого длинного значения. Если драйвер не обнаруживает значений длиннее 255 символов в выбираемых строках, он считает, что столбец является строковым с длиной 255 символов, а не столбцом типа memo. Поэтому значения длиннее 255 символов могут быть усечены.
Чтобы импортировать данные из столбца типа memo без усечения, можно воспользоваться любым из двух способов:
Убедитесь, что столбец типа memo как минимум в одной из выбранных строк содержит значение длиной более 255 символов.
Увеличьте число строк в выборке драйвера, чтобы включить такую строку. Чтобы увеличить количество строк, включаемых в выборку, достаточно увеличить значение TypeGuessRows в следующем разделе реестра:
Проблемы с экспортом
Создание нового файла назначения
В службах SSIS
Создайте диспетчер соединений Excel с именем создаваемого файла Excel и путем к нему. Затем в редакторе назначения Excel в разделе Имя листа Excel выберите Создать, чтобы создать лист назначения. В этот момент службы SSIS создают новый файл Excel с указанным листом.
В мастере импорта и экспорта SQL Server
На странице Выбор назначения нажмите кнопку Обзор. В диалоговом окне Открыть перейдите в папку, в которой требуется создать новый файл Excel, укажите имя этого файла и выберите Открыть.
Экспорт в достаточно большой диапазон
Экспорт длинных текстовых значений
Чтобы успешно сохранять в столбцы Excel строки, имеющие длину более 255 символов, драйвер должен распознать тип данных целевого столбца как memo , а не как string.
- Если в существующей целевой таблице уже содержатся строки данных, то в столбце типа memo в первых нескольких строках, которые проверит драйвер, должен содержаться, по крайней мере, один экземпляр значения, имеющего длину более 255 символов.
См. также
Дополнительные сведения о компонентах и процедурах, описываемых в этой статье, см. в следующих статьях:
В этой статье рассматриваются многочисленные методы передачи данных в Microsoft Excel из приложения Microsoft Visual Basic. В этой статье также представлены преимущества и недостатки каждого метода, чтобы можно было выбрать оптимальное решение.
Дополнительная информация
Чаще всего для передачи данных в Excel используется автоматизация. Автоматизация обеспечивает максимальную гибкость при указании расположения данных в книге, а также возможность форматирования книги и создания различных параметров во время выполнения. С помощью службы автоматизации можно использовать несколько подходов для передачи данных:
- Передача ячейки данных по ячейкам
- Передача данных в массиве в диапазон ячеек
- Передача данных из набора записей ADO в диапазон ячеек с помощью метода CopyFromRecordset
- Создание таблицы QueryTable на Excel, содержащей результат запроса к источнику данных ODBC или OLEDB
- Передача данных в буфер обмена, а затем вставка содержимого буфера обмена в Excel листа
Существуют также методы, которые можно использовать для передачи данных в Excel, которые не обязательно требуют автоматизации. Если вы работаете на стороне сервера приложений, это может быть хорошим подходом для того, чтобы отойдите от клиентов к массовой обработке данных. Для передачи данных без автоматизации можно использовать следующие методы:
- Передача данных в текстовый файл с разделителями-табуляции или запятыми, который Excel позже можно проанализировать в ячейки на листе.
- Передача данных на лист с помощью ADO
- Передача данных в Excel с помощью динамических Exchange данных (DDE)
В следующих разделах приведены более подробные сведения о каждом из этих решений.
Примечание При использовании Microsoft Office Excel 2007 при сохранении книг можно использовать новый формат Excel книги 2007 (*.xlsx). Для этого найдите следующую строку кода в следующих примерах кода:
Замените этот код следующей строкой кода:
Кроме того, база данных Northwind не включена в Office 2007 по умолчанию. Однако базу данных Northwind можно скачать из Microsoft Office Online.
Использование службы автоматизации для передачи данных по ячейкам
С помощью службы автоматизации можно передавать данные на лист по одной ячейке за раз:
Передача ячеек данных по ячейкам может быть вполне приемлемым подходом, если объем данных невелик. Вы можете размещать данные в любом месте книги и условно форматировать ячейки во время выполнения. Однако этот подход не рекомендуется, если у вас есть большой объем данных для передачи в Excel книге. Каждый объект Range, полученный во время выполнения, приводит к запросу интерфейса, чтобы передача данных таким образом была медленной. Кроме того, Microsoft Windows 95 и Windows 98 имеют ограничение в 64 КБ на запросы интерфейса. Если вы достигнете или превысите это ограничение в 64 КБ для запросов интерфейса, сервер автоматизации (Excel) может перестать отвечать на запросы или могут возникнуть ошибки, указывающие на нехватку памяти.
Еще раз перенос ячеек данных допускается только для небольших объемов данных. Если необходимо перенести большие наборы данных в Excel, следует рассмотреть одно из решений, представленных позже.
Дополнительные примеры кода для автоматизации Excel см. в статье об автоматизации Microsoft Excel из Visual Basic.
Использование автоматизации для передачи массива данных в диапазон на листе
Массив данных можно передать в диапазон из нескольких ячеек одновременно:
При передаче данных с помощью массива, а не ячейки по ячейкам вы можете реализовать огромное повышение производительности с большим объемом данных. Рассмотрим следующую строку из приведенного выше кода, который передает данные в 300 ячеек листа:
Эта строка представляет два запроса интерфейса (один для объекта Range, возвращаемого методом Range, и другой для объекта Range, возвращаемого методом Resize). С другой стороны, для передачи ячейки данных по ячейкам требуются запросы 300 интерфейсов к объектам Range. По возможности вы можете воспользоваться преимуществами массовой передачи данных и сокращения количества запросов интерфейса.
Использование автоматизации для передачи набора записей ADO в диапазон листа
Excel 2000 г. появился метод CopyFromRecordset, который позволяет передавать набор записей ADO (или DAO) в диапазон на листе. В следующем коде показано, как автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 и передать содержимое таблицы Orders в образце базы данных Northwind с помощью метода CopyFromRecordset.
Примечание При использовании Office 2007 базы данных Northwind необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Excel 97 также предоставляет метод CopyFromRecordset, но его можно использовать только с набором записей DAO. CopyFromRecordset с Excel 97 не поддерживает ADO.
Дополнительные сведения об использовании ADO и метода CopyFromRecordset см. в статье о передаче данных из набора записей ADO в Excel с помощью автоматизации.
Использование автоматизации для создания таблицы QueryTable на листе
Объект QueryTable представляет таблицу, созданную на основе данных, возвращаемых из внешнего источника данных. При автоматизации Microsoft Excel можно создать таблицу QueryTable, просто указав строку подключения для OLEDB или источника данных ODBC вместе с SQL строкой. Excel несет ответственность за создание набора записей и его вставку на лист в указанном расположении. Использование таблиц QueryTables дает несколько преимуществ по сравнению с методом CopyFromRecordset:
- Excel обрабатывает создание набора записей и его размещение на листе.
- Запрос можно сохранить с помощью таблицы QueryTable, чтобы его можно было обновить позже, чтобы получить обновленный набор записей.
- При добавлении новой таблицы QueryTable на лист можно указать, что данные, уже существующие в ячейках листа, будут сдвинуты в соответствии с новыми данными (дополнительные сведения см. в свойстве RefreshStyle).
В следующем коде показано, как автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 для создания новой таблицы QueryTable на листе Excel с помощью данных из образца базы данных Northwind:
Использование буфера обмена
Буфер обмена Windows также можно использовать в качестве механизма передачи данных на лист. Чтобы вставить данные в несколько ячеек на листе, можно скопировать строку, в которой столбцы разделены символами табуляции, а строки разделены символами каретки. В следующем коде показано, Visual Basic использовать объект буфера обмена для передачи данных в Excel:
Создание текстового файла с разделителями, который Excel анализировать по строкам и столбцам
Excel могут открывать файлы с разделителями-табуляции или запятыми и правильно анализировать данные в ячейках. Эту функцию можно использовать, если вы хотите перенести большой объем данных на лист, используя небольшой объем (при наличии) службы автоматизации. Это может быть хорошим подходом для клиент-серверного приложения, так как текстовый файл может быть создан на стороне сервера. Затем можно открыть текстовый файл на клиенте с помощью службы автоматизации, где это необходимо.
В следующем коде показано, как создать текстовый файл с разделителями-запятыми из набора записей ADO:
Обратите внимание, что при использовании Office 2007 базы данных Northwind необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Если текстовый файл имеет расширение .CSV, Excel открывает файл без отображения мастера импорта текста и автоматически предполагает, что файл разделен запятыми. Аналогичным образом, если файл имеет расширение .TXT, Excel автоматически анализировать файл с помощью разделителей табуляции.
В предыдущем примере кода Excel с помощью инструкции Оболочки, а имя файла использовался в качестве аргумента командной строки. В предыдущем примере автоматизация не использовалась. Однако при необходимости можно использовать минимальный объем автоматизации, чтобы открыть текстовый файл и сохранить его в Excel формате книги:
Передача данных на лист с помощью ADO
С помощью поставщика Microsoft Jet OLE DB можно добавлять записи в таблицу в существующей Excel книге. Таблица в Excel является просто диапазоном с определенным именем. Первая строка диапазона должна содержать заголовки (или имена полей), а все последующие строки — записи. Ниже показано, как создать книгу с пустой таблицей с именем MyTable.
Excel 97, Excel 2000 и Excel 2003
Запустите новую книгу в Excel.
Добавьте следующие заголовки в ячейки A1:B1 листа Sheet1:
A1: FirstName B1: LastName
Отформатировать ячейку B1 как выровненную по правому краю.
В меню "Вставка" выберите "Имена", а затем выберите "Определить". Введите имя MyTable и нажмите кнопку "ОК".
Сохраните новую книгу как C:\Book1.xls и закройте Excel.
Чтобы добавить записи в MyTable с помощью ADO, можно использовать следующий код:
Excel 2007
В Excel 2007 запустите новую книгу.
Добавьте следующие заголовки в ячейки A1:B1 листа Sheet1:
A1: FirstName B1: LastName
Отформатировать ячейку B1 как выровненную по правому краю.
На ленте откройте вкладку "Формулы " и выберите команду "Определить имя". Введите имя MyTable и нажмите кнопку "ОК".
Сохраните новую книгу как C:\Book1.xlsx и закройте Excel.
Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, похожий на следующий пример кода.
При добавлении записей в таблицу таким образом форматирование в книге сохраняется. В предыдущем примере новые поля, добавленные в столбец B, форматируются с выравниванием по правому краю. Каждая запись, добавляемая в строку, заимствует формат из строки над ней.
Обратите внимание, что при добавлении записи в ячейку или ячейки листа она перезаписывает все данные, которые ранее были в этих ячейках. Другими словами, строки на листе не "помещаются вниз" при добавлении новых записей. Это следует учитывать при проектировании макета данных на листах.
Метод обновления данных на листе Excel с помощью ADO или DAO не работает в среде Visual Basic for Application в Access после установки Office 2003 с пакетом обновления 2 (SP2) или после установки обновления для Access 2002, включенного в статью базы знаний Майкрософт 904018. Этот метод хорошо работает в Visual Basic приложений из других Office приложений, таких как Word, Excel и Outlook.
Дополнительные сведения см. в следующей статье:
Дополнительные сведения об использовании ADO для доступа к книге Excel см. в статье "Как запрашивать и обновлять данные Excel с помощью ADO из ASP".
Использование DDE для передачи данных в Excel
DDE — это альтернатива автоматизации в качестве средства взаимодействия с Excel и передачи данных. Однако с появлением автоматизации и COM DDE больше не является предпочтительным методом для взаимодействия с другими приложениями и должен использоваться только в том случае, если вам не доступно другое решение.
Чтобы передать данные в Excel с помощью DDE, можно использовать метод LinkPoke для передачи данных в определенный диапазон ячеек или метод LinkExecute для отправки команд, которые Excel будут выполняться.
В следующем примере кода показано, как установить диалог DDE с Excel, чтобы можно было перенести данные в ячейки на листе и выполнить команды. В этом примере для успешной установки диалога DDE в LinkTopic Excel|MyBook.xls книга с именем MyBook.xls должна быть открыта в работающем экземпляре Excel.
При использовании Excel 2007 можно использовать новый формат .xlsx для сохранения книг. Убедитесь, что имя файла обновлено в следующем примере кода. В этом примере Text1 представляет элемент управления Text Box в Visual Basic форме:
При использовании LinkPoke с Excel указывается диапазон в нотации столбца строк (R1C1) для LinkItem. Если данные разделяются на несколько ячеек, можно использовать строку, в которой столбцы разделяются вкладками, а строки разделяются символами возврата каретки.
При использовании LinkExecute для Excel выполнения команды необходимо Excel в синтаксисе языка макросов Excel (XLM). Документация по XLM не входит в Excel 97 и более поздних версий.
DDE не рекомендуется для взаимодействия с Excel. Автоматизация обеспечивает максимальную гибкость и предоставляет больше доступа к новым функциям, Excel может предложить.
Читайте также: