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


Обзор


В образце ExcelADO.exe показано, как использовать объекты данных ActiveX (ADO) с помощью Microsoft Jet OLE DB 4.0 поставщика для чтения и записи данных в книге Microsoft Excel.

Дополнительные сведения


Зачем использовать ADO?

Использование ADO для передачи данных или извлечения данных из книги Excel дает вам, разработчик, несколько преимуществ по сравнению с автоматизации в Excel:
  • Производительность. Microsoft Excel является сервером ActiveX out-of-process. ADO работает в работе и экономит ресурсы дорогостоящих вызовов out-of-process.
  • Масштабируемость. Для веб-приложений не всегда желательно для автоматизации приложения Microsoft Excel. ADO предоставляет гибкий решения для обработки данных в книге.
ADO может использоваться исключительно для передачи необработанных данных в книге. Нельзя использовать ADO для применения форматов и формул в ячейки. Тем не менее можно перенести данные в книгу, предварительно отформатированы и формат сохраняется. Если требуется «условное» форматирование после вставки данных, можно выполнить форматирование с помощью автоматизации или макросов в книге.

Особенности поставщика Jet OLE DB для книг Excel

Ядро базы данных Microsoft Jet может использоваться для доступа к данным в других форматах файлов базы данных, например, книгам Excel через устанавливаемые драйверы индексно-последовательного метода доступа (ISAM). Для открытия внешних форматов, поддерживаемых Microsoft Jet 4.0 OLE DB поставщик, указать тип базы данных в расширенных свойствах подключения. Поставщик данных Jet OLE DB поддерживает следующие типы базы данных для книги Microsoft Excel:
  • Excel 3.0
  • Excel 4.0
  • Excel 5.0
  • Excel 8.0
Примечание: используйте тип базы данных источника Excel 5.0 для книги Microsoft Excel версии 5.0 и 7.0 (95) и использовать тип базы данных источника Excel 8.0 для книги Microsoft Excel (97) 8.0 и 9.0 (2000). В примере ExcelADO.exe используется книг Excel в формате Excel 97 и Excel 2000.


В следующих примерах демонстрируется книги Excel 97 (или 2000) ADO:
Dim oConn As New ADODB.Connection
With oConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open "C:\Book1.xls"
'....
.Close
End With
–ИЛИ-
Dim oConn As New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;" & _
"Extended Properties=""Excel 8.0;"""
oConn.Close
Соглашения об именах таблиц

Существует несколько способов, которые могут ссылаться на таблицу (или диапазон) в книге Excel:
  • Имя листа, а затем знак доллара (например, [Лист1$] или [Мой лист $]). Такой ссылки таким образом состоит из всего используемый диапазон листа.
    oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic
  • Используйте диапазон с определенным именем (например, ["Table1"]).
    oRS.Open "Select * from Table1", oConn, adOpenStatic
  • Использовать диапазон с определенным адресом (например, [Лист1$ A1: B10]).
    oRS.Open "Select * from [Sheet1$A1:B10]", oConn, adOpenStatic
Заголовки таблицы

С книгами Excel первая строка диапазона считается строкой заголовков (или имена полей) по умолчанию. Если первый диапазон не содержит заголовков, можно указать HDR = нет в расширенные свойства строки подключения. Если первая строка содержит заголовки, поставщик OLE DB автоматически имена полей для вас (где первое поле представляет F1, F2 бы представляют второе поле и т. д).

Типы данных

В отличие от традиционных баз данных нет не предусмотрена возможность указывать типы данных для столбцов в таблицах Excel. Вместо этого поставщик OLE DB сканирует ограниченное число строк в столбце Тип данных для поля «догадаться». Число строк для просмотра по умолчанию для восьми (8) строк; можно изменить количество строк для проверки, указав значение от 1 (один) до шестнадцати (16) для параметра MAXSCANROWS в расширенные свойства строки подключения.

Файлы, включенные в образце

Файл ExcelADO.exe содержит проект Visual Basic стандартный exe-ФАЙЛ ASP (ASP), Excel 97 и Excel 2000 книги, выступать в роли шаблонов и базы данных Microsoft Access 2000. Ниже перечислены файлы, включенные.

Файлы проекта Visual Basic стандартный проект EXE
  • ExcelADO.vbp
  • Form1.frm
  • Form1.frx
ASP
  • EmpData.asp
  • Orders.asp
Книги Microsoft Excel
  • OrdersTemplate.xls
  • EmpDataTemplate.xls
  • ProductsTemplate.xls
  • SourceData.xls
База данных Microsoft Access
  • Data.mdb

Использование образца

Извлеките содержимое файла .exe в папку.

Использование проекта Visual Basic.
  1. В Visual Basic откройте файл ExcelADO.vbp.
  2. В меню проект выберите пункт ссылкии затем задать ссылки на Библиотеки объектов данных ActiveX Microsoftи Microsoft ADO Ext. для DDL и безопасности . Этот пример кода работает с ADO 2.5 и 2.6, ADO, поэтому выберите версию, подходящую для вашего компьютера.
  3. Нажмите клавишу F5 для запуска программы. Откроется форма для демонстрации.
  4. Выберите Пример 1. В этом примере создается копия OrdersTemplate.xls. Затем он использует ADO для подключения к книге и открывается набор записей в таблице, которая представляет собой определенный диапазон в книге. Имя диапазона является Orders_Table. Он использует ADO AddNew/обновление методов для добавления записей (или строк) для определенного диапазона в книге. После завершения добавления строк ADO подключение закрывается и книга отображается в Microsoft Excel. Выполните следующие действия, чтобы сделать это.
    1. В меню Вставка в Excel выберите именаи выберите Определение.
    2. В список определенных имен выберите Orders_Table. Обратите внимание, что для включения новых добавленных записей достиг определенного имени. Определенное имя используется, в сочетании с Microsoft Excel функции СМЕЩ, для вычисления суммы с данными на листе.
    3. Выйти из Microsoft Excel и вернуться в приложение Visual Basic.
  5. Щелкните Образец 2. В этом примере создается копия EmpDataTemplate.xls. Он использует ADO для подключения к книге и использует метод Execute подключения ADO для вставки данных (INSERT INTO в SQL) в книге. Данные будут добавлены в определенных диапазонов (или таблиц) в книге. При передаче данных, подключение закрывается и книга, результаты отображаются в Excel. После изучения книги выйти из Microsoft Excel, а затем вернитесь в приложение Visual Basic.

  6. Щелкните Образец 3. В этом примере создается копия ProductsTemplate.xls. Он использует 2.1 расширения Microsoft ADO для DDL и безопасности библиотеки объектов (ADOX) для добавления новой таблицы (или новый лист) в книге. Затем получается набор записей ADO для новой таблицы и добавления данных с помощью метода AddNew/Update методы. После завершения добавления строк ADO подключение закрывается и книга отображается в Excel. Книга содержит Visual Basic для приложений макроса (VBA) в события Open для книги. Запуск макроса при открытии книги; Если в книгу новый лист «Продукты», код макроса форматирует лист и затем удаляется код макроса. Этот метод предоставляет способ для веб-разработчиков переместить код форматирования от веб-сервера и на стороне клиента. Приложение может поток форматированного книги, которая содержит данные для клиента и разрешить код макроса, который будет выполнять любые «условное» форматирование, веб-узел не удастся в шаблон отдельно для запуска на клиенте.

    Примечание: чтобы проверить код макроса, просматривать модуле ThisWorkbook в VBAProject по ProductsTemplate.xls.


  7. Щелкните Образец 4. Этот пример создает те же результаты, как пример 1, но применяется несколько иной метод, используемый для передачи данных. В примере 1 записи (или строки) добавляются на лист один за один раз. Пример 4 добавляет записи в пакетном режиме путем присоединения таблицы Excel к базе данных и выполнения запроса на добавление (или INSERT INTO... SELECT FROM) для добавления записей в таблицу Excel из таблицы в таблицу Access. После завершения передачи данных таблицы Excel отсоединяется от базы данных Microsoft Access и книгу, результаты отображаются в Excel. Выйти из Excel и вернуться в приложение Visual Basic.


  8. Последний пример иллюстрирует, как можно прочитать данные из книги Excel. Выберите таблицу в раскрывающемся списке и щелкните Образец 5. Открывшееся окно отображает содержимое выбранной таблицы. При выборе целого листа («Лист1$» или «Лист2$») в таблице окна «Интерпретация» отображает содержимое диапазона используется для листа. Обратите внимание, что используемый диапазон не обязательно начинается в строке 1, столбец 1 листа. Используемый диапазон начинается с верхней левой ячейки в лист, содержащий данные.


    При выборе определенного диапазона адресов или определенного диапазона, открывшееся окно отображает содержимое только диапазон на листе.
Чтобы использовать ASP (ASP).
  1. Создайте новую папку с именем ExcelADO в домашнем каталоге веб-сервера. Обратите внимание, что путь по умолчанию для основного каталога C:\InetPut\WWWRoot.
  2. Скопируйте следующие файлы в папку, созданную на предыдущем шаге:
    • EmpData.asp
    • Orders.asp
    • Data.mdb
    • EmpDataTemplate.xls
    • OrdersTemplate.xls

  3. В этом сценарии ASP образец создание копий книги шаблонов с метод Copy FileSystemObject. Для успешного выполнения метода Copy клиент, который использует сценарий необходимо доступ на запись в папку, содержащую ASP.
  4. Перейдите к Orders.asp (то есть, http://YourServer/ExcelADO/Orders.ASP) и обратите внимание, что обозреватель книги Excel, аналогичный показанному на 1 образец приложения Visual Basic.
  5. Перейдите к EmpData.asp (то есть, http://YourServer/ExcelADO/EmpData.ASP) и обратите внимание, что обозреватель книги Excel, аналогичный показанному на 2 образца приложения Visual Basic.

(c) Корпорация Майкрософт (Microsoft Corporation), 2000. Все права защищены. Взносы в Лори б. Тернер, корпорация Майкрософт.

Ссылки


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

Методические УКАЗАНИЯ 195951 : запрос и обновление данных Excel с помощью ADO с ASP

194124 PRB: Excel значения, возвращаемые как NULL с помощью DAO OpenRecordset
Методические УКАЗАНИЯ 193998 : чтение и отображение двоичных данных в ASP
247412 информация: методы для передачи данных в Excel из Visual Basic
257819 методические УКАЗАНИЯ: Использование ADO с данными Excel из Visual Basic или VBA