Экспорт данных из VB6 в Excel: запись ячеек, форматирование и сохранение

Как устроена автоматизация Excel

Программа на VB6 управляет Excel как COM-сервером: запускает процесс EXCEL.EXE и вызывает методы его объектной модели. Поэтому Excel должен быть установлен на компьютере, где работает программа. Создавать файл xls «вручную» без Excel тоже можно, но это другая техника.

Объектная модель иерархическая, и для работы нужны три уровня:

• Application: сам процесс Excel, у него есть свойства Visible, DisplayAlerts, ScreenUpdating;
• Workbook: книга, то есть один файл на диске, создаётся через Workbooks.Add или открывается через Workbooks.Open;
• Worksheet: лист книги, данные пишутся в его ячейки через Cells или Range.

Запись массивом вместо цикла по ячейкам

Самый очевидный код перебирает коллекцию и пишет значение в каждую ячейку через Cells(i, 1) = значение. Каждое такое присваивание это межпроцессный вызов. На сотне строк разницы не видно, а на двадцати тысячах выгрузка растягивается на минуты.

Правильный приём: собрать данные в двумерный массив Variant и записать его в диапазон одной операцией через свойство Value. Excel получает весь блок за один вызов, и выгрузка ускоряется в десятки раз. Массив должен быть именно двумерным, даже если столбец один.

' Требуется ссылка: Microsoft Excel XX.0 Object Library
Public Sub ExportToExcel(ByVal colInput As Collection, ByVal strPath As String)
    Dim xlApp As Excel.Application, wb As Excel.Workbook, ws As Excel.Worksheet
    Dim arr() As Variant, i As Long
    Dim lngErr As Long, strErr As String
    On Error GoTo ErrTrap
    If colInput.Count = 0 Then Exit Sub
    ReDim arr(1 To colInput.Count, 1 To 1)       ' строки x один столбец
    For i = 1 To colInput.Count
        arr(i, 1) = colInput(i)
    Next
    Set xlApp = New Excel.Application
    xlApp.Visible = False                         ' работаем без окна
    xlApp.DisplayAlerts = False                   ' без вопроса о перезаписи
    Set wb = xlApp.Workbooks.Add
    Set ws = wb.Worksheets(1)
    ws.Range("A1").Resize(colInput.Count, 1).Value = arr  ' одна запись
    ws.Columns(1).AutoFit
    wb.SaveAs strPath
    wb.Close False
ErrTrap:
    lngErr = Err.Number: strErr = Err.Description
    Set ws = Nothing: Set wb = Nothing
    If Not xlApp Is Nothing Then xlApp.Quit       ' всегда закрываем Excel
    Set xlApp = Nothing
    If lngErr <> 0 Then Err.Raise lngErr, "ExportToExcel", strErr
End Sub

Выгрузка из базы через CopyFromRecordset

Если данные приходят из базы через ADO, массив собирать не нужно. Метод Range.CopyFromRecordset сам переносит все строки Recordset на лист, начиная с указанной ячейки. Заголовки столбцов он не пишет, их берём из коллекции Fields.

Метод работает быстро даже на десятках тысяч записей. Ограничение одно: поля с двоичными данными и очень длинным текстом лучше исключить из запроса.

Public Sub RecordsetToSheet(ByVal rs As ADODB.Recordset, ByVal ws As Excel.Worksheet)
    Dim i As Long
    For i = 0 To rs.Fields.Count - 1              ' заголовки в первую строку
        ws.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next
    With ws.Range(ws.Cells(1, 1), ws.Cells(1, rs.Fields.Count))
        .Font.Bold = True
        .Interior.Color = RGB(221, 235, 247)
    End With
    ws.Range("A2").CopyFromRecordset rs           ' данные со второй строки
    ws.Range("A1").CurrentRegion.Columns.AutoFit
End Sub

Форматы ячеек и типичные искажения

Excel пытается угадать тип каждого значения, и иногда угадывает неудачно. Коды товаров вроде 00125 теряют ведущие нули, номера счетов из 16 цифр превращаются в экспоненциальную запись, а строки вида 1-2 становятся датами. Лечится это заданием формата до записи данных.

Учтите, что свойство NumberFormat принимает формат в английской нотации независимо от языка Office, а NumberFormatLocal в региональной. Если код будет работать на разных локалях, используйте NumberFormat.

• ws.Columns(2).NumberFormat = "@" делает столбец текстовым, ведущие нули сохранятся;
• ws.Columns(3).NumberFormat = "#,##0.00" даст разделитель тысяч и два знака после запятой;
• ws.Columns(1).NumberFormat = "dd.mm.yyyy" выводит даты в привычном виде;
• даты передавайте значением типа Date, а не строкой, иначе на английской Windows 03.04 станет 4 марта.

Сохранение: формат файла и версия Excel

Метод SaveAs без параметра FileFormat сохраняет книгу в формате по умолчанию для установленной версии. В Excel 2007 и новее это xlsx. Если при этом указать путь с расширением .xls, получится файл xlsx с неправильным расширением, и при открытии пользователь увидит предупреждение о несоответствии формата.

Поэтому формат лучше задавать явно: 56 (xlExcel8) для классического xls в Excel 2007 и новее, 51 (xlOpenXMLWorkbook) для xlsx, а в Excel 2003 и старше значение -4143 (xlWorkbookNormal). Версию можно узнать из свойства Application.Version: строка 12.0 соответствует Excel 2007.

Параметр DisplayAlerts = False подавляет вопрос о перезаписи существующего файла. Без него невидимый Excel покажет диалог, который никто не увидит, и программа повиснет в ожидании ответа.

Почему EXCEL.EXE остаётся в памяти

Классическая жалоба: после выгрузки в диспетчере задач висит процесс Excel, а повторный экспорт падает с ошибкой 462 «The remote server machine does not exist or is unavailable». Причина почти всегда в неявной ссылке на объект Excel.

При раннем связывании VB6 позволяет писать Range("A1") или ActiveSheet без указания родительского объекта. Такой вызов создаёт скрытую глобальную ссылку на Application, которую код не может освободить. Excel не завершится, пока жива программа, а следующий New Excel.Application будет работать уже с другим процессом, и скрытая ссылка указывает в пустоту.

Правила, которые снимают проблему:

• каждое обращение начинайте с явной переменной: ws.Range, wb.Worksheets, xlApp.Workbooks;
• не используйте ActiveSheet, ActiveWorkbook, Selection в коде автоматизации;
• в обработчике ошибок обязательно вызывайте Quit и присваивайте Nothing всем объектным переменным;
• если выгрузок много подряд, держите один экземпляр Excel на всю серию, а не запускайте его на каждый файл.

Как это делают сегодня

Вопросы и ответы