Передача данных из набора записей ADO в Excel средствами автоматизации

Переводы статьи Переводы статьи
Код статьи: 246335 - Vizualiza?i produsele pentru care se aplic? acest articol.
Развернуть все | Свернуть все

В этой статье

Аннотация

Передать содержимое набора записей ADO в лист Microsoft Excel можно средствами автоматизации Excel. Конкретный подход зависит от автоматизируемой версии Excel. В Excel 97, Excel 2000 и Excel 2002 имеется метод CopyFromRecordset, который можно использовать для передачи данных из набора записей в диапазон ячеек. Метод CopyFromRecordset в Excel 2000 и 2002 можно использовать для копирования наборов записей как DAO, так и ADO. Однако метод CopyFromRecordset в Excel 97 поддерживает только наборы записей DAO. Для передачи данных из набора записей ADO в Excel 97 можно создать на основе набора записей массив, а затем заполнить диапазон ячеек содержимым этого массива.

В данной статье описаны оба подхода. Приведенный в статье образец кода иллюстрирует способ передачи данных из набора записей ADO в Excel 97, Excel 2000, Excel 2002, Excel 2003 или Excel 2007.

Дополнительная информация

В приведенном ниже примере кода показано, как скопировать содержимое набора записей ADO в лист Microsoft Excel средствами автоматизации языка Microsoft Visual Basic. В коде сначала проверяется версия Excel. Если определяется версия Excel 2000 или 2002, используется метод CopyFromRecordset, поскольку он работает эффективно и требует меньшего объема кода. Однако если определяется версия Excel 97 или более ранняя версия, набор записей сначала копируется в массив с помощью метода GetRows объекта набора записей ADO. Этот массив затем транспонируется таким образом, что записи располагаются в его первой размерности (в строках), а поля — во второй (в столбцах). Затем массив копируется в лист Excel путем присвоения массива диапазону ячеек. (Массив копируется за одно действие, а не в цикле по всем ячейкам листа.)

В образце кода используется база данных "Борей" (Northwind), поставляемая с Microsoft Office. Если при установке Microsoft Office была выбрана папка по умолчанию, то эта база данных находится в следующей папке:

\Program Files\Microsoft Office\Office\Samples\Northwind.mdb

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

Если база данных "Борей" не установлена на компьютере, ее можно установить с помощью функции добавления и удаления компонентов программы установки Microsoft Office.

Примечание. База данных "Борей" не устанавливается при установке выпуска 2007 системы Microsoft Office. Чтобы загрузить базу данных "Борей 2007", перейдите на следующий веб-узел Майкрософт:
http://office.microsoft.com/ru-ru/templates/TC012289971049.aspx
(на английском языке)

Действия по созданию примера

  1. Запустите Visual Basic и создайте проект Standard EXE. По умолчанию будет создана форма Form1.
  2. Добавьте в форму Form1 элемент управления CommandButton.
  3. Выберите в меню Project (проект) пункт References (ссылки). Добавьте ссылку на библиотеку объектов Microsoft ActiveX Data Objects 2.1 Library.
  4. Вставьте в раздел кода формы Form1 приведенный ниже код.
    Private Sub Command1_Click()
        Dim cnt As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        
        Dim xlApp As Object
        Dim xlWb As Object
        Dim xlWs As Object
    
        
        Dim recArray As Variant
        
        Dim strDB As String
        Dim fldCount As Integer
        Dim recCount As Long
        Dim iCol As Integer
        Dim iRow As Integer
        
        ' Присвоить строке путь к базе данных "Борей"
        strDB ="c:\program files\Microsoft office\office11\samples\Northwind.mdb"
      
        ' Открыть подключение к базе данных
        cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & strDB & ";"
        
        ''При использовании базы данных "Борей" для Access 2007
        ''закомментируйте предыдущий код и раскомментируйте приведенный ниже код.
        'cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        '    "Data Source=" & strDB & ";"
            
        ' Открыть набор записей на основе таблицы Orders (заказы)
        rst.Open "Select * From Orders", cnt
        
        ' Создать экземпляр Excel и добавить книгу
        Set xlApp = CreateObject("Excel.Application")
        Set xlWb = xlApp.Workbooks.Add
        Set xlWs = xlWb.Worksheets("Sheet1")
      
        ' Вывести Excel на экран позволить пользователю управлять временем работы Excel
        xlApp.Visible = True
        xlApp.UserControl = True
        
        ' Скопировать имена полей в первую строку листа
        fldCount = rst.Fields.Count
        For iCol = 1 To fldCount
            xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
        Next
            
        ' Проверить версию Excel
        If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
            'EXCEL 2000,2002,2003 или 2007: используется метод CopyFromRecordset
             
            ' Скопировать набор записей на лист, начиная с ячейки A2
            xlWs.Cells(2, 1).CopyFromRecordset rst
            'Примечание. При использовании метода CopyFromRecordset произойдет сбой, если набор записей
            'содержит поле объекта OLE или массив данных, таких как
            'иерархические наборы записей
            
        Else
            'EXCEL 97 или более ранней версии: Будет использоваться метод GetRows, а затем массив будет скопирован в Excel
        
            ' Скопировать набор данных в массив
            recArray = rst.GetRows
            'Примечание. Метод GetRows возвращает массив, индексируемый с 0, первая
            'размерность которого содержит поля, а вторая
            'содержит записи. Массив будет транспонирован таким образом, чтобы
            'первая размерность содержала записи, обеспечивая
            'правильное отображение данных при копировании в Excel
            
            ' Определить количество строк
    
            recCount = UBound(recArray, 2) + 1 '+ 1, поскольку массив индексируется с 0
            
    
            ' Проверить массив на наличие недопустимого содержимого при
            ' копировании массива в лист Excel
            For iCol = 0 To fldCount - 1
                For iRow = 0 To recCount - 1
                    ' Обработка полей Date (дата)
                    If IsDate(recArray(iCol, iRow)) Then
                        recArray(iCol, iRow) = Format(recArray(iCol, iRow))
                    ' Обработка полей объектов OLE или полей массивов
                    ElseIf IsArray(recArray(iCol, iRow)) Then
                        recArray(iCol, iRow) = "Array Field"
                    End If
                Next iRow 'следующая запись
            Next iCol 'следующее поле
                
            ' Транспонировать и скопировать массив в лист,
            ' начиная с ячейки A2
            xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
                TransposeDim(recArray)
        End If
    
        ' Автоматически подобрать ширину столбцов и высоту строк
        xlApp.Selection.CurrentRegion.Columns.AutoFit
        xlApp.Selection.CurrentRegion.Rows.AutoFit
    
        ' Закрыть объекты ADO
        rst.Close
        cnt.Close
        Set rst = Nothing
        Set cnt = Nothing
        
        ' Освободить ссылки на Excel
        Set xlWs = Nothing
        Set xlWb = Nothing
    
        Set xlApp = Nothing
    
    End Sub
    
    
    Function TransposeDim(v As Variant) As Variant
    ' Пользовательская функция для транспонирования массива, индексируемого с 0 (v)
        
        Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
        Dim tempArray As Variant
        
        Xupper = UBound(v, 2)
        Yupper = UBound(v, 1)
        
        ReDim tempArray(Xupper, Yupper)
        For X = 0 To Xupper
            For Y = 0 To Yupper
                tempArray(X, Y) = v(Y, X)
            Next Y
        Next X
        
        TransposeDim = tempArray
    
    
    End Function
    
  5. Для выполнения проекта нажмите клавишу F5. Появится форма Form1.
  6. Нажмите в форме Form1 кнопку CommandButton и обратите внимание на содержимое таблицы Orders (заказы), появившееся в новой книге Excel.
Использование метода CopyFromRecordset

Рекомендуется использовать метод CopyFromRecordset благодаря его высокой эффективности и производительности. Поскольку метод CopyFromRecordset в Excel 97 поддерживает только наборы записей DAO, при попытке передать методу CopyFromRecordset в Excel 97 набор записей ADO появится следующее сообщение об ошибке:
Run-time error 430:
Class does not support Automation or does not support expected interface (Ошибка времени выполнения 430: класс не поддерживает автоматизацию либо не поддерживает соответствующий интерфейс)
В данном примере кода этой ошибки можно избежать путем проверки версии Excel, чтобы не использовать метод CopyFromRecordset для версии Excel 97.

Примечание. При использовании метода CopyFromRecordset следует иметь в виду, что используемые наборы записей ADO и DAO не могут содержать поля объектов OLE или массивы данных, такие как иерархические наборы записей. Если в наборе записей имеются поля таких типов, при вызове метода CopyFromRecordset произойдет сбой и появится следующее сообщение об ошибке:
Run-time error -2147467259:
Method CopyFromRecordset of object Range failed (Ошибка времени выполнения -2147467259: сбой метода CopyFromRecordset объекта Range).
Использование метода GetRows

Если обнаружена версия Excel 97, для копирования набора записей в массив используется метод GetRows набора записей ADO. Если присвоить диапазону ячеек в листе значение массива, возвращенного методом GetRows, данные будут выведены по столбцам, а не по строкам. Например, если набор записей состоит из двух полей и десяти строк, массив будет состоять из двух строк и десяти столбцов. Следовательно, перед присвоением значения массива диапазону ячеек массив необходимо транспонировать с помощью функции TransposeDim(). При присвоении значения массива диапазону ячеек имеется ряд ограничений, которые следует иметь в виду.

Указанные ниже ограничения применяются при присвоении значения массива объекту Range (диапазон) Excel.
  • Массив не может содержать поля объектов OLE или массивы данных, такие как иерархические наборы записей. Обратите внимание на то, что в примере кода это условие проверяется, а в случае обнаружения подобного поля выводится сообщение "Array Field" (поле массива), чтобы уведомить пользователя о невозможности отобразить поле в Excel.

  • Массив не может содержать поля типа Date (дата), в которых установлена дата до 1900 г. (Ссылку на соответствующую статью базы знаний Майкрософт см. в разделе "Ссылки".) Отметим, что для обхода этой проблемы в данном примере поля типа Date форматируются как строки типа "Variant".
Перед копированием массива в лист Excel массив транспонируется с помощью функции TransposeDim(). Вместо создания собственной функции для транспонирования массива можно воспользоваться встроенной функцией Excel Transpose, изменив код таким образом, чтобы присвоить массив диапазону ячеек, как показано ниже.
   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)
				
Если для транспонирования массива вместо функции TransposeDim() будет использоваться метод Excel Transpose, следует учитывать ограничения, накладываемые на метод Transpose.
  • Массив не может содержать элементов, длина которых превышает 255 знаков.
  • Массив не может содержать пустые (Null) значения.
  • Количество элементов не может превышать 5461.
Если при копировании массива в лист Excel не принять во внимание приведенные выше ограничения, может возникнуть одна из указанных ниже ошибок времени выполнения.
Ошибка времени выполнения 13: несоответствие типов
Ошибка времени выполнения 5: недопустимый аргумент или вызов процедуры
Ошибка времени выполнения 1004: ошибка, определенная приложением или объектом

Ссылки

Дополнительные сведения об ограничениях на передачу массивов в различные версии Excel см. в следующей статье базы знаний Майкрософт:
177991 XL: Ограничения на передачу массивов в Excel средствами автоматизации (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
Для получения дополнительных сведений щелкните приведенные ниже номера статей базы знаний Майкрософт:
146406 XL: Загрузка таблицы из Access в Excel с помощью DAO (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
(эта ссылка может указывать на содержимое полностью или частично на английском языке)
215965 XL2000: Для дат, предшествующих 1900 г., отображается время 12:00:00 (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
243394 Использование MFC для копирования набора записей DAO в Excel средствами автоматизации (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
247412 Способы передачи данных из Visual Basic в Excel

Свойства

Код статьи: 246335 - Последний отзыв: 28 ноября 2007 г. - Revision: 5.0
Информация в данной статье относится к следующим продуктам.
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic 6.0 Enterprise Edition
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.5
Ключевые слова: 
kbhowto kbautomation kbexpertiseinter KB246335

Отправить отзыв

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com