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

Сводка

Вы можете перенести содержимое набора записей ADO на лист Microsoft Excel, автоматизируя Excel. Способ, который можно использовать, зависит от версии Excel, для которой выполняется Автоматизация. Excel 97, Excel 2000 и Excel 2002 имеют метод Копифромрекордсет, который можно использовать для переноса объекта Recordset в диапазон. Копифромрекордсет в Excel 2000 и 2002 можно использовать для копирования либо объекта DAO, либо набора записей ADO. Однако Копифромрекордсет в 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, используется метод Копифромрекордсет, так как он эффективен и требует меньше кода. Однако если обнаруживается Excel 97 или более ранняя версия, набор записей сначала копируется в массив с помощью метода GetRows объекта Recordset объекта ADO. Затем массив передается таким образом, чтобы записи направляются в первое измерение (в строках), а поля — во второе измерение (в столбцах). Затем массив копируется на лист Excel путем присвоения массива диапазону ячеек. (Массив копируется за один шаг, а не циклически по каждой ячейке на листе.)

В примере кода используется пример базы данных Northwind, которая входит в состав Microsoft Office. Если вы выбрали папку по умолчанию при установке Microsoft Office, база данных находится в:

\Program Files\Microsoft Оффице\оффице\самплес\норсвинд.МДБ

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

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

Note (Примечание ) База данных "Борей" не устанавливается при установке 2007 Microsoft Office. Чтобы получить базу данных Northwind 2007, посетите следующий веб-сайт корпорации Майкрософт:

Темы & шаблонов Office

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

  1. Запустите Visual Basic и создайте новый стандартный проект EXE. По умолчанию создается форма Form1.

  2. Добавьте CommandButton в Form1.

  3. Выберите Референцесфром меню проект. Добавьте ссылку на библиотеку Microsoft ActiveX Data Objects 2,1.

  4. Вставьте приведенный ниже код в раздел Code формы 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
    
    ' Set the string to the path of your Northwind database
        strDB ="c:\program files\Microsoft office\office11\samples\Northwind.mdb"
    
    ' Open connection to the database
        cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & strDB & ";"
    
    ''When using the Access 2007 Northwind database
        ''comment the previous code and uncomment the following code.
        'cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        '    "Data Source=" & strDB & ";"
    
    ' Open recordset based on Orders table
        rst.Open "Select * From Orders", cnt
    
    ' Create an instance of Excel and add a workbook
        Set xlApp = CreateObject("Excel.Application")
        Set xlWb = xlApp.Workbooks.Add
        Set xlWs = xlWb.Worksheets("Sheet1")
    
    ' Display Excel and give user control of Excel's lifetime
        xlApp.Visible = True
        xlApp.UserControl = True
    
    ' Copy field names to the first row of the worksheet
        fldCount = rst.Fields.Count
        For iCol = 1 To fldCount
            xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
        Next
    
    ' Check version of Excel
        If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
            'EXCEL 2000,2002,2003, or 2007: Use CopyFromRecordset
    
    ' Copy the recordset to the worksheet, starting in cell A2
            xlWs.Cells(2, 1).CopyFromRecordset rst
            'Note: CopyFromRecordset will fail if the recordset
            'contains an OLE object field or array data such
            'as hierarchical recordsets
    
    Else
            'EXCEL 97 or earlier: Use GetRows then copy array to Excel
    
    ' Copy recordset to an array
            recArray = rst.GetRows
            'Note: GetRows returns a 0-based array where the first
            'dimension contains fields and the second dimension
            'contains records. We will transpose this array so that
            'the first dimension contains records, allowing the
            'data to appears properly when copied to Excel
    
    ' Determine number of records
    
    recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array
    
    ' Check the array for contents that are not valid when
            ' copying the array to an Excel worksheet
            For iCol = 0 To fldCount - 1
                For iRow = 0 To recCount - 1
                    ' Take care of Date fields
                    If IsDate(recArray(iCol, iRow)) Then
                        recArray(iCol, iRow) = Format(recArray(iCol, iRow))
                    ' Take care of OLE object fields or array fields
                    ElseIf IsArray(recArray(iCol, iRow)) Then
                        recArray(iCol, iRow) = "Array Field"
                    End If
                Next iRow 'next record
            Next iCol 'next field
    
    ' Transpose and Copy the array to the worksheet,
            ' starting in cell A2
            xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
                TransposeDim(recArray)
        End If
    
    ' Auto-fit the column widths and row heights
        xlApp.Selection.CurrentRegion.Columns.AutoFit
        xlApp.Selection.CurrentRegion.Rows.AutoFit
    
    ' Close ADO objects
        rst.Close
        cnt.Close
        Set rst = Nothing
        Set cnt = Nothing
    
    ' Release Excel references
        Set xlWs = Nothing
        Set xlWb = Nothing
    
    Set xlApp = Nothing
    
    End Sub
    
    Function TransposeDim(v As Variant) As Variant
    ' Custom Function to Transpose a 0-based array (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. Нажмите CommandButton на Form1 и обратите внимание, что содержимое таблицы Заказы отображается в новой книге Excel.

Использование Копифромрекордсет

Для повышения эффективности и производительности Копифромрекордсет является предпочтительным методом. Так как Excel 97 поддерживает только наборы записей DAO с Копифромрекордсет, при попытке передать набор записей ADO Копифромрекордсет с помощью Excel 97 появляется следующее сообщение об ошибке:

Ошибка во время выполнения 430: класс не поддерживает автоматизацию или не поддерживает ожидаемый интерфейс. В этом примере кода можно избежать этой ошибки путем проверки версии Excel, чтобы не использовать Копифромрекордсет для версии 97.

Note (Примечание ) При использовании Копифромрекордсет следует иметь в виду, что используемый набор записей ADO или DAO не может содержать поля объекта OLE или массивы данных, такие как иерархические наборы записей. Если вы включаете поля любого типа в набор записей, метод Копифромрекордсет завершается со следующей ошибкой:

Ошибка во время выполнения — 2147467259: сбой метода Копифромрекордсет в диапазоне объектов.

Использование GetRows

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

При назначении массива объекту диапазона Excel применяются следующие ограничения:

  • Массив не может содержать поля объекта OLE или массив данных, например, иерархические наборы записей. Обратите внимание, что пример кода проверяет это условие и отображает "поле массива", чтобы пользователь знал, что это поле невозможно отобразить в Excel.

  • Массив не может содержать поля даты, которые имеют дату, предшествующую 1900 году. (Обратитесь к разделу "ссылки" для ссылки на статью базы знаний Майкрософт). Обратите внимание на то, что пример кода форматирует поля даты как строки Variant, чтобы избежать возникновения этой проблемы.

Обратите внимание на использование функции Транспоседим () для переставит массив перед копированием массива на лист Excel. Вместо того чтобы создавать собственную функцию для пересчета массива, можно использовать функцию транспонировать Excel, изменив пример кода, чтобы назначить массив ячейкам, как показано ниже:

   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)

Если вы решили использовать метод транспонировать Excel вместо функции Транспоседим () для перебытия массива, следует помнить о следующих ограничениях с помощью метода транспонировать:

  • Массив не может содержать элемент, длина которого превышает 255 символов.
  • Массив не может содержать значения NULL.
  • Число элементов в массиве не может превышать 5461.

Если приведенные выше ограничения не учитываются при копировании массива на лист Excel, может возникнуть одна из следующих ошибок во время выполнения:

Ошибка во время выполнения 13: несоответствие типов

Ошибка во время выполнения 5: недопустимая процедура

Ошибка во время выполнения вызова или аргумента 1004: ошибка, определенная приложением или объектом

Ссылки

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

177991 XL: ограничения передаваемых массивов в Excel с помощью автоматизации

247412 Info: методы переноса данных в Excel из Visual Basic