Передача данных в excel из visual basic
Для большинства практических задач вполне хватает возможностей объектов Application, Workbook, Worksheet и Range. Например, для вставки информации из базы данных вы можете пройти циклом по объекту ADO.Recordset и вставить все нужные записи в лист Excel, а затем средствами VBA прописать в строки внизу итоги по вставленным данным. Однако в Excel встроено еще несколько важных специальных объектов, которые могут сильно упростить работу в различных ситуациях. Например, ту же операцию с по вставке информации из базы данных удобнее будет провести при помощи специального объекта QueryTable, который рассматривается в этом разделе. Еще два таких специальных объекта — объекты PivotTable и Chart рассматриваются в следующих разделах.
Основное назначение объекта QueryTable — работа с набором значений, возвращаемых из базы данных. Этот объект доступен в Excel и при помощи графического интерфейса через меню Данные -> Импорт внешних данных -> Импортировать данные. При помощи объектов QueryTable вы можете разместить набор записей, полученных с источника данных, на листе Excel для выполнения с ним различных операций (например, анализа). QueryTable удобно использовать для "односторонней" работы с источником данных, когда данные только скачиваются с источника в Excel, но изменять их с сохранением изменений на источнике не нужно. В Excel такую возможность синхронизации изменений реализовать можно (например, при помощи перехвата события Change объекта Worksheet), но намного проще (и правильнее) использовать для этой цели возможности Access. Обычно данные помещаются в Excel для проведения анализа (при помощи богатой библиотеки функций), для построения диаграмм, иногда — отчетов и т.п. В этом разделе мы будем рассматривать только такую "однонаправленную" передачу данных из базы данных в Excel.
Как обычно, для того, чтобы создать объект QueryTable и разместить его на листе, нужно использовать специальную коллекцию QueryTables, которая принадлежит рабочему листу (объекту Worksheet) и доступна через его одноименное свойство. Свойства и методы объекта QueryTables — стандартные, как у большинства рассмотренных нами коллекций. Подробного рассмотрения заслуживает только метод Add(), при помощи которого и создается объект QueryTable (с одновременным добавлением в коллекцию). Этот метод принимает три параметра:
- Connection — источник данных для QueryTable (в виде объекта типа Variant). В качестве источника данных можно использовать:
- строку подключения OLE DB или ODBC (строка подключения ODBC должна начинаться с " ODBC;", а в остальном — все точно так же, как в главе про ADO);
- готовый объект Recordset, созданный стандартными средствами ADO или DAO. При этом можно изменять Recordset, на который ссылается QueryTable и обновлять QueryTable. По многим причинам это — самый удобный вариант при работе с QueryTable;
- другой объект QueryTable (вместе со строкой подключения и текстом запроса);
- текстовый файл;
- результаты Web-запроса или запроса Microsoft Query (в виде файла *. dqy или *. iqy). Создать такой файл запроса можно при помощи графических средств Excel: меню Данные -> Импорт внешних данных -> Создать запрос.
Конечно, правильнее всего при создании QueryTable использовать готовый объект Recordset. В этом случае у нас — и самые полные возможности настройки подключения и курсора, и возможность очень эффективного промежуточного хранения данных в оперативной памяти (в объекте Recordset), куда можно вносить изменения, и все очень удобные свойства и методы объекта Recordset. Код на создание объекта QueryTable на листе Excel может выглядеть так (мы используем тот же Recordset на основе таблицы Northwind.Customers, что и в модуле про ADO):
Dim cn As ADODB.Connection
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionString = "User Source = LONDON1;" _
& "Initial Catalog = Northwind"
Dim rs As ADODB.Recordset
Set rs = CreateObject("ADODB.Recordset")
rs.Open "select * from dbo.customers", cn
Dim QT1 As QueryTable
Set QT1 = QueryTables.Add(rs, Range("A1"))
Непосредственно помещение объекта QueryTable на лист производится при помощи метода QueryTable.Refresh(). Без него объект QueryTable будет создан только в оперативной памяти.
Теперь — о самых важных свойствах и методах объекта QueryTable:
- BackgroundQuery — может ли выполнение запроса производится в фоновом режиме, пока пользователь выполняет в Excel другие действия. По умолчанию true, в false следует переводить только тогда, когда пользователь действиями в Excel может как-то помешать нормальной работе приложения.
- CommandText — текст команды SQL, то есть текст запроса, который передается на источник. Сосуществует совместно с аналогичным свойством SQL (которое оставлено для обратной совместимости) и имеет перед ним приоритет. При передаче QueryTable готового Recordset недоступно.
- CommandType — тип передаваемой в CommandText команды (вся таблица, SQL-запрос, имя куба и т.п.). При работе с готовым Recordset также недоступно.
- Connection — строка подключения, та самая, которую можно передать при вызове метода Add() коллекции QueryTables. Опять-таки при работе с готовым Recordset недоступно.
- Destination — второй параметр, который передавался методу Add(). Возвращает объект Range, представляющий первую (верхнюю левую ячейку) диапазона, занимаемого на листе объектом QueryTable. После создания QueryTable доступен только на чтение.
- EnableEditing — может ли пользователь изменять на графическом экране свойства объекта QueryTable. Если перевести в false (по умолчанию true), то пользователь сможет только обновлять QueryTable.
- EnableRefresh — может ли пользователь обновлять QueryTable, получая заново данные (с источника или Recordset);
- FetchedRowOverflow — это свойство принимает значение true, если записи, полученные с источника, не уместились на листе Excel (было скачано больше, чем 65536 записей). Ошибки в такой ситуации не возникает, поэтому если вы работаете с большими наборами записей, то есть смысл реализовать соответствующие проверки.
- FieldNames — очень полезное свойство. Позволяет отключить вставку полученных с источника названий столбцов в первую строку QueryTable. По умолчанию true (вставлять названия столбцов).
- MaintainConnection — это свойство определяет, будет ли соединение с источником открыто все время до закрытия листа. По умолчанию true — оптимизировано под выполнение частых обновлений. Если переставить в false, можно сэкономить оперативную память на клиенте за счет скорость обновления данных.
- Name — имя объекта QueryTable (на графическом экране его можно просмотреть, если в панели управления Внешние данные нажать на кнопку Свойства диапазона данных). По умолчанию — ExternalData_номер.
- Parameters — возможность получить доступ к коллекции Parameters, набору параметров запроса. Возможности практически такие же, как для работы с параметрами объекта Recordset.
- PreserveColumnInfo и PreserveFormatting — сохранять ли информацию о столбцах (сортировке, фильтрации и т.п.) и форматировании после обновления QueryTable. По умолчанию — все сохранять.
- QueryType — возможность выяснить (свойство доступно только на чтение), что использовалось при создании QueryTable — Recordset, прямой доступ к таблице, SQL-запрос и т.п.
- Recordset — возможность получить ссылку на объект Recordset, который использовался для создания QueryTable или сменить его для объекта QueryTable (изменения вступят в силу только после вызова метода Refresh()).
- Refreshing — это свойство принимает значение true на момент выполнения фонового запроса к источнику. Если выполнение запроса слишком затянулось, его можно прервать при помощи метода CancelRefresh().
- RefreshOnFileOpen — обновлять ли данные автоматически при открытии листа или можно обойтись уже скачанными значениями (по умолчанию).
- RefreshPeriod — через какие интервалы времени автоматически обновлять информацию в QueryTable данными с источника. По умолчанию 0 — то есть автоматическое обновление отключено.
- RefreshStyle — определить, что делать с существующими ячейками, на место которых вставляются ячейки QueryTable при обновлении.
- ResultRange — пожалуй, самое важное свойство объекта QueryTable. Как правило, данные из базы данных перекачиваются в Excel для дальнейшей обработки. Это свойство позволяет получить диапазон, который включает в себя все ячейки, вставленные на лист из объекта QueryTable, чтобы потом применить к ним различные функции (обычно по столбцам или по строкам). Чтобы этот метод сработал, обязательно нужно провести вставку данных QueryTable на лист при помощи метода Refresh. После этого можно использовать то, что возвращает это свойство, как обычный диапазон. Самый простой способ продемонстрировать работу эту метода — воспользоваться кодом
А такой пример генерирует под первым столбцом QueryTable формулу с суммированием значений этого первого столбца:
Set c1 = Sheets("Лист1").QueryTables(1).ResultRange.Columns(1)
c1.End(xlDown).Offset(1, 0).Formula = "=SUM(Column1)"
- RowNumbers — свойство, которое может сильно упростить работу с данными, полученными при помощи QueryTable. Позволяет сгенерировать еще один столбец в QueryTable (слева), который будет состоять из номеров записей, полученных через QueryTable.
- SaveData — сохранять ли данные, полученные через QueryTable, вместе с книгой Excel. По умолчанию True. В False есть смысл переводить для того, чтобы изначально гарантировать работу пользователя только с самыми последними данными, полученными из источника.
- SavePassword — сохранять ли пароль вместе со строкой подключения (это свойство можно использовать только для источников ODBC). Если переставить его в False, можно повысить уровень безопасности вашего приложения.
- SourceDataFile — полный путь и имя файла источника (для Access, DBF и прочих настольных СУБД). Для клиент-серверных систем (таких, как SQL Server), возвращает Null.
- многочисленные свойства, которые начинаются на Text…, определяют параметры текстового файла, если этот файл выбран в качестве источника для QueryTable.
- свойства Web… определяют параметры данных, получаемых от запроса к Web-источнику.
Методы объекта QueryTable (Refresh(), CancelRefresh(), Delete()) очевидны и каких-либо комментариев не требуют. Метод ResetTimer() позволяет обнулить таймер автоматического обновления, а метод SaveAsODC() позволяет сохранить определение источника данных в виде файла Microsoft Query (если источником был объект Recordset, то этот метод вернет ошибку).
У объекта QueryTable есть также два события: BeforeRefresh и AfterRefresh. Они срабатывают соответственно перед началом загрузки данных с источника и после окончания загрузки.
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Аннотация
В этой статье обсуждаются многочисленные методы передачи данных в 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 имеют ограничение в 64K для запросов интерфейса. Если вы достигнете или превысите это ограничение в 64k для запросов интерфейса, сервер автоматизации (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 г. и передать содержимое таблицы заказов в базе данных образцов Northwind с помощью метода CopyFromRecordset.
Примечание Если вы используете Office 2007 года в базе данных Northwind, необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Excel 97 также предоставляет метод CopyFromRecordset, но использовать его можно только с набором записей DAO. CopyFromRecordset с Excel 97 не поддерживает ADO.
Дополнительные сведения об использовании ADO и метода CopyFromRecordset см. в статьи How to transfer data from an ADO recordset to 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 использовать объект Clipboard для передачи данных в Excel:
Создание разнонародных текстовых файлов, Excel можно разрезать на строки и столбцы
Excel могут открывать файлы с запятой или запятой и правильно разбора данных в ячейки. Вы можете воспользоваться этой функцией, если вы хотите передать большой объем данных на таблицу при использовании малого, если таково, автоматизации. Это может быть хорошим подходом для клиентского приложения-сервера, так как текстовый файл может быть создан на стороне сервера. Затем можно открыть текстовый файл на клиенте, используя автоматизацию там, где это необходимо.
В следующем коде показано, как создать текстовый файл с запятой из наборов записей ADO:
Обратите внимание, что Office версии базы данных Northwind 2007 года необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Если в текстовом файле есть расширение .CSV, Excel открывает файл без отображения мастера импорта текста и автоматически предполагает, что файл запятой. Аналогично, если в вашем файле .TXT расширение, Excel автоматически разборите файл с помощью делимитеров вкладок.
В предыдущем примере кода Excel был запущен с помощью заявления Shell, а имя файла использовалось в качестве аргумента командной строки. Автоматизация не использовалась в предыдущем примере. Однако при желании можно использовать минимальное количество автоматизации для открытия текстового файла и сохранения его в формате Excel книги:
Передача данных на таблицу с помощью ADO
С помощью поставщика DB Microsoft Jet OLE можно добавить записи в таблицу в существующей Excel книге. "Таблица" в Excel — это просто диапазон с определенным именем. Первая строка диапазона должна содержать заглавные (или имена полей), а все последующие строки содержат записи. Ниже показано, как создать книгу с пустой таблицей MyTable.
Excel 97, Excel 2000 и Excel 2003 г.
Запустите новую книгу в Excel.
Добавьте следующие заглавные таблицы в ячейки A1:B1 листа1:
A1: FirstName B1: LastName
Формат ячейки B1 в виде правой выровненной.
В меню Insert выберите Имена, а затем выберите Определение. Введите имя MyTable и нажмите кнопку ОК.
Сохраните новую книгу как C:\Book1.xls и Excel.
Чтобы добавить записи в MyTable с помощью ADO, можно использовать код, аналогичный следующему:
Excel 2007
В Excel 2007 г. запустите новую книгу.
Добавьте следующие заглавные таблицы в ячейки A1:B1 листа1:
A1: FirstName B1: LastName
Формат ячейки B1 в виде правой выровненной.
На ленте нажмите вкладку Формулы и нажмите кнопку Определить имя. Введите имя MyTable и нажмите кнопку ОК.
Сохраните новую книгу как C:\Book1.xlsx, а затем Excel.
Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, похожий на следующий пример кода.
При добавлении записей в таблицу таким образом форматирование в книге сохраняется. В предыдущем примере новые поля, добавленные в столбец B, форматированы с правильным выравниванием. Каждая запись, добавляемая в строку, заимствует формат из строки над ней.
Следует отметить, что при добавлении записи в ячейку или ячейки в таблицу она переописывание любых данных, ранее в этих ячейках; Другими словами, строки в таблице не "сдвинуты" при добавлении новых записей. Это следует иметь в виду при разработке макета данных на ваших таблицах.
Метод обновления данных в Excel таблице с помощью ADO или с помощью DAO не работает в Visual Basic для среды приложения в access после установки Office 2003 Пакет обновления 2 (SP2) или после установки обновления для Access 2002, включенного в статью Microsoft Knowledge Base 904018. Метод хорошо работает в Visual Basic среде приложений из других Office приложений, таких как Word, Excel и Outlook.
Дополнительные сведения см. в следующей статье:
Дополнительные сведения об использовании ADO для доступа к книге Excel см. в книге How To Query and Update Excel Data Using ADO from 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 представляет собой управление текстовым полем на Visual Basic форме:
При использовании LinkPoke с Excel, вы указываете диапазон в строке-столбце (R1C1) для LinkItem. Если вы подтыкаете данные к нескольким ячейкам, можно использовать строку, в которой столбцы делимитированы вкладками, а строки делимитированы возвращаемой каретой.
При использовании LinkExecute для Excel выполнения команды необходимо Excel команду в синтаксисе Excel макроса (XLM). Документация XLM не включается в Excel версии 97 и более поздней версии.
DDE не рекомендуется для общения с Excel. Автоматизация обеспечивает наибольшую гибкость и предоставляет дополнительный доступ к новым функциям, которые Excel предложить.Способы передачи данных из Visual Basic в Excel
Применимо к: Microsoft Office Excel 2007Microsoft Office Excel 2003Excel 2010
В данной статье рассматриваются способы передачи данных в Microsoft Excel из приложения Microsoft Visual Basic. В статье также представлены преимущества и недостатки каждого из способов, что позволяет пользователю выбрать наиболее подходящий способ для конкретной ситуации.
Чаще всего для передачи данных в книгу Excel используется программирование объектов (автоматизация). Этот способ обладает наибольшим спектром возможностей для указания местоположения данных в книге Excel, а также обеспечивает возможность форматирования книги и настройки различных параметров во время выполнения. Программирование объектов позволяет использовать для передачи данных несколько подходов:
Передача данных по одной ячейке
Передача массива данных в диапазон ячеек
Передача набора записей ADO в диапазон ячеек с помощью способа CopyFromRecordset
Создание в листе Excel объекта QueryTable, содержащего результаты запроса по источнику данных ODBC или OLEDB
Передача данных в буфер обмена с последующей вставкой содержимого буфера обмена в лист Excel
Также существуют способы передачи данных в Excel, не требующие программирования объектов. При работе с серверным приложением рекомендуется освободить клиентов от большого объема обрабатываемых данных. Ниже приведены способы передачи данных, не использующие программирование объектов.
Передача данных в текстовый файл, использующий запятые или знаки табуляции в качестве разделителей, который Excel впоследствии может разобрать на ячейки листа
Передача данных на лист Excel с помощью ADO
Передача данных в Excel с помощью динамического обмена данными (DDE)
В следующих разделах приведены дополнительные сведения о каждом решении.
Примечание. При использовании Microsoft Office Excel 2007 для сохранения книги Excel 2007 можно использовать новый формат файла (XSLX). Для этого найдите следующую строку кода в приведенных ниже примерах:
Замените этот код следующей строкой кода:
Кроме того, база данных «Борей» не входит в состав Office 2007 по умолчанию. Вы можете скачать базу данных «Борей» из Microsoft Office Online.
Для передачи данных между ячейкой используйте службу автоматизации.
С помощью службы автоматизации вы сможете передавать данные на лист по одной ячейке за раз:
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Add data to cells of the first worksheet in the new workbook
Set oSheet = oBook.Worksheets(1)
'Save the Workbook and Quit Excel
Таким образом, передача данных по одной ячейке допустима только для небольших объемов данных. Для передачи больших объемов данных в Excel следует использовать один из способов, описанных ниже.
Примеры сценариев для автоматизации Excel см. в следующей статье базы знаний Майкрософт:
219151 Как использовать Visual Basic для автоматизации Microsoft Excel
Использование автоматизации для передачи массива данных на диапазон на листе
Массив данных может передаваться одновременно на диапазон из нескольких ячеек:
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Create an array with 3 columns and 100 rows
Dim DataArray(1 To 100, 1 To 3) As Variant
Dim r As Integer
For r = 1 To 100
DataArray(r, 2) = Rnd() * 1000
DataArray(r, 3) = DataArray(r, 2) * 0.7
'Add headers to the worksheet on row 1
Set oSheet = oBook.Worksheets(1)
'Transfer the array to the worksheet starting at cell A2
oSheet.Range("A2").Resize(100, 3).Value = DataArray
'Save the Workbook and Quit Excel
Передача большого объема данных с помощью массива происходит значительно быстрее, чем передача данных по одной ячейке. Обратите внимание на строку из приведенного выше сценария, которая одновременно передает данные в 300 ячеек листа:
oSheet.Range("A2").Resize(100, 3).Value = DataArray
Эта строка представляет всего два запроса к интерфейсу (один для объекта Range, возвращаемого методом Range, и один для объекта Range, возвращаемого методом Resize). При этом при передаче данных по одной ячейке потребовалось бы 300 запросов к интерфейсу для объектов Range. Поэтому передачу данных необходимо по возможности осуществлять с помощью массива, чтобы сократить число запросов к интерфейсу.
Передача набора записей ADO с помощью программирования объектов
В Excel 2000 появился метод CopyFromRecordset, позволяющий передавать наборы данных ADO (или DAO) в диапазон ячеек листа. Приведенный ниже сценарий является примером автоматизации Excel 2000, Excel 2002 или Office Excel 2003 для переноса содержимого таблицы Orders образца базы данных «Борей» с помощью метода CopyFromRecordset.
'Create a Recordset from all the records in the Orders table
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Set rs = conn.Execute("Orders", , adCmdTable)
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Transfer the data to Excel
'Save the Workbook and Quit Excel
'Close the connection
Примечание. При использовании версии базы данных «Борей» для Office 2007 необходимо заменить в примере следующую строку кода:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
Замените эту строку кода следующей строкой:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"
В Excel 97 также имеется метод CopyFromRecordset, однако его можно использовать только для набора записей DAO. CopyFromRecordset в Excel 97 не поддерживает ADO.
Дополнительные сведения об использовании ADO и метода CopyFromRecordset см. в следующей статье базы знаний Майкрософт:
246335 Как передавать данные из набора записей 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 с данными из образца базы данных «Борей».
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Create the QueryTable
Dim sNWind As String
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim oQryTable As Object
Set oQryTable = oSheet.QueryTables.Add( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
'Save the Workbook and Quit Excel
Использование буфера обмена
Буфер обмена Windows также может использоваться как механизм передачи данных на лист Excel. Чтобы вставить данные в несколько ячеек листа, можно скопировать строку, в которой столбцы разделены знаками табуляции, а строки – символами возврата каретки. В приведенном ниже сценарии показано, как Visual Basic может использовать буфер обмена для передачи данных в Excel:
'Copy a string to the clipboard
Dim sData As String
sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Save the Workbook and Quit Excel
Создание текстового файла с разделителями, который Excel может разобрать по строкам и столбцам
Excel может открывать файлы с разделителями – знаками табуляции и запятыми – и правильно распределять данные по ячейкам. Этим можно воспользоваться при необходимости передачи большого объема данных в лист Excel с минимальным использованием автоматизации. Этот подход рекомендуется для приложений типа клиент-сервер, поскольку текстовый файл может генерироваться серверным приложением. Затем текстовый файл можно открыть с помощью клиентского приложения, при необходимости используя автоматизацию.
Ниже приведен сценарий, иллюстрирующий создание текстового файла с разделителями-запятыми из набора записей ADO:
'Create a Recordset from all the records in the Orders table
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim sData As String
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Set rs = conn.Execute("Orders", , adCmdTable)
'Save the recordset as a tab-delimited file
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
'Close the connection
'Open the new text file in Excel
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _
Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus
Примечание. При использовании версии базы данных «Борей» для Office 2007 необходимо заменить в примере следующую строку кода:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Замените эту строку кода следующей строкой:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
Если файл имеет расширение CSV, Excel открывает его без отображения мастера импорта текста и по умолчанию принимает, что в файле используются разделители-запятые. Если же файл имеет расширение TXT, Excel автоматически разбирает его, используя в качестве разделителей знаки табуляции.
В приведенном выше примере запуск Excel осуществлялся с помощью оператора Shell, а имя файла использовалось как аргумент командной строки. А в этом примере автоматизация не использовалась. Однако при желании можно применить минимум автоматизации, чтобы открыть текстовый файл и сохранить его в формате книги Excel:
'Create a new instance of Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
'Open the text file
Set oBook = oExcel.Workbooks.Open("C:\Test.txt")
'Save as Excel workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal
Передача данных на лист Excel с помощью ADO
С помощью Microsoft Jet OLE DB Provider можно добавлять записи в таблицу существующей книги Excel. «Таблицей» в Excel считается диапазон с заданным именем. Первая строка диапазона содержит заголовки (или имена полей), а все последующие строки – записи. Ниже приведен пример пошагового создания книги с пустой таблицей
Excel 97, Excel 2000 и Excel 2003
Откройте новую книгу Excel.
Добавьте следующие заголовки в ячейки A1:B1 листа 1:
A1: FirstName B1: LastName
Выровняйте ячейку B1 по правому краю.
Выделите диапазон A1:B1.
В меню Вставка выберите пункт Имя, а затем выберите команду Присвоить. Введите имя MyTable и нажмите кнопку ОК.
Сохраните новую книгу как C:\Book1.xls и закройте Excel.
Чтобы добавить записи в таблицу MyTable с помощью ADO, можно воспользоваться примерно следующим сценарием:
'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
В Excel 2007 создайте книгу.
Добавьте следующие заголовки в ячейки A1:B1 листа 1:
A1: FirstName B1: LastName
Выровняйте ячейку B1 по правому краю.
Выделите диапазон A1:B1.
На ленте откройте вкладку Формулы и выберите элемент Присвоить имя. Введите имя MyTable и нажмите кнопку ОК.
Сохраните новую книгу как C:\Book1.xlsx и закройте Excel.
Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, подобный приведенному ниже.
'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
При подобном добавлении записей в таблицу форматирование книги сохраняется. В приведенном выше примере новые поля, добавляемые в столбец B, выравниваются по правому краю. Каждая запись, добавляемая в строку, форматируется так же, как предыдущая.
Обратите внимание на то, что при добавлении в ячейку или ячейки листа запись заменяет любые данные, находившиеся в этих ячейках ранее; другими словами, строки листа не сдвигаются вниз при добавлении новых записей. Это следует иметь в виду при планировании размещения данных на листе.
Примечание. Обновление данных на листе Excel с помощью ADO или DAO невозможно в среде Visual Basic for Application в Access после установки пакета обновлений 2 (SP2) для Office 2003 или обновления для Access 2002, описанного в статье 904018 базы знаний Майкрософт. Однако этот способ можно использовать в среде Visual Basic for Application в других приложениях Office, то есть в Word, Excel и Outlook.
Для получения дополнительной информации щелкните приведенные ниже номера статей базы знаний Майкрософт:
904953 Невозможно вносить изменения, добавлять или удалять данные в таблицах, источником которых являются книги Excel в Office Access 2003 или в Access 2002
904018 Описание обновления для Access 2002: от 18 октября 2005 г.
Дополнительные сведения об использовании ADO для доступа к книгам Excel см. в следующих статьях базы знаний Майкрософт:
195951 Как создать запросы и выполнить обновление данных 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:
'Initiate a DDE communication with Excel
'Poke the text in Text1 to the R1C1:R2C3 in MyBook.xls
'Execute commands to select cell A1 (same as R1C1) and change the font
'Terminate the DDE communication
При использовании метода LinkPoke с Excel необходимо указать диапазон в формате строка-столбец (R1C1) для LinkItem. Если данные вставляются в несколько ячеек, можно использовать строку, в которой столбцы разделены символами табуляции, а строки – символами возврата каретки.
Если метод LinkExecute используется для выполнения команды в Excel, синтаксис команды должен соответствовать языку Excel Macro Language (XLM). Документация по XLM не входит в состав Excel 97 и более поздних версий.
DDE не является рекомендуемым способом связи с Excel. Программирование объектов предоставляет больше возможностей и обеспечивает лучший доступ к новым функциям Excel.
Для получения дополнительной информации щелкните приведенный ниже номер статьи базы знаний Майкрософт:
Весь процесс написания экспорта данных из программы в Excel можно свести к нескольким очень простым этапам: Подключения ссылки (Reference) на Excel в IDE студии; организации функции экспорта с объявлением соответствующих объектов, строками вывода и стилизации; эксперименты в самом Excel, а точнее с макросами, для получения значений необходимых констант и отыскания необходимых методов и свойств. Теперь по порядку.
Теперь поговорим о том, как можно написать функции или процедуру для вывода данных в Excel. Я приведу простой пример, который будет заполнять лист Excel абстрактными данными. В Вашем случае вы можете передавать данные в виде параметров метода или получать их прям в самом методе использую расчеты или запросы к базе данных. Содержимое метода выглядит следующим образом (комментарии ниже):
В результате выполнения этих строк перед вами, на экране монитора, появиться окно Excel, с открытой книгой содержащей одни лист. В ячейке A1 будет написано «Дарова мир. ».
Рассмотрим детали кода. Первые три строки объявляют необходимые три объекта для работы с документами Excel. Первая фактически создаёт и запускает копию самого Excel без каких либо открытых книг. Вторая строка создает объект, который является новой книгой в нашей копии программы. Ну и третья строк добавляет один единственный лист в книгу и объявляет объект, при помощи которого мы будем заносить в лист информацию. Четвертая строка, это самая простейшая манипуляция над листом – внесение текстовой информации в ячейку. Последней строкой мы делаем нашу копию Excel видимой. До этого момента она является не видимой с той целью, чтобы пользователи не пугались видя процесс внесения информации (при больших объёмах данных он может быть заметен не вооруженным глазом) и не могли помешать работе функции экспорта.
Многих могут устроить именно эти строки, так как их достаточно для написания собственной функции простейшего вывода. Но более искушенный программист, а что еще хуже заказчик, может иметь желание видеть не просто набор серых данных, а хорошо отформатированную и оформленную структуру, с заголовками, выравниванием, полужирными начертаниями, заданной шириной столбцов, наличием формул и прочего, на что способен настоящий документ Excel. Если описывать все возможности Excel и, соответственно, вашего когда, понадобиться очень много времени, и в итоге получиться большая книга – а это не цель моей статьи. Я расскажу вам как можно, используя макросы Excel получить именно тот результат, которого вы хотите в довольно короткий срок.
Допустим Вы хотите выделить текст одной из ячеек при экспорте данных полужирным начертанием. Делам следующее. Открываем Excel. Пишем в одной из ячеек произвольный текст, кликаем по другой ячейке. После этого необходимо начать запись макроса. Например в 2007 офисе это можно сделать в меню Вид->Макросы->Запись макроса, после чего появиться окно, в котором необходимо поставить любую букву в поле «Сочетание клавиш» и в списке «Сохранить в» выбрать «Эта книга», нажать кнопку Ок. После этого Excel начнет записывать в макрос в виде строк кода на языке VBA (Visual Basic for Application) все то, что Вы будете проделывать с листами и книгой. Выделите ячейку с произвольным текстом, при помощи меню сделайте ее начертание полужирным. Excel моментально запишит строчки кода в макрос. Чтобы сильно не мусорить в макросе по возможности больше ничего не делайте с ячейками, листами и книгой. Пройдите в меню Вид->Макросы->Остановить запись. Далее в меню Вид->Макросы[->Макросы] выберете последнее из списка и нажмите кнопку изменить. Откроется окно с кодом макроса примерно такого содержания:
Используя этот код можно модифицировать нашу первую функцию экспорта. Теперь она будет выглядеть следующим образом:
Таким образом, мы можем получить любые интересующие нас фрагменты кода.
Чтобы не затруднять ваши поиски элементарных, обычно используемых вещей я приведу некоторые методы, своийства и значения констант.
Читайте также: