Обмен данными в excel
Данные, хранящиеся в БД, могут быть использованы для создания формальных писем, адресных наклеек на конвертах и других атрибутов почты, но их можно вставить в виде таблицы в документ Word.
I.Обмен между Access и Word
Упражнение 1.Импортировать данные в виде таблицы в документ Word .
1.Запустить Word и открыть нужный документ.
2. Установить курсор на место вставки документа.
3. В меню “Вставка” выбрать “База данных”, появится диалоговое окно “База данных”.
4. Активизировать кнопку “Получить данные”, появится окно “Открыть источник данных”. В списке типов файлов выбрать Microsoft Access и открыть нужный файл.
5. Активизировать кнопку ”Запросы” или “Таблицы”.
6. Активизировать кнопку “Автоформатирование”.
7. Выбрать формат таблицы, нажать ОК.
8. Появится диалоговое окно “Базы данных”, активизировать команду “Вставить данные”.
9. В диалоговое окно “Вставка данных” выбрать “Все”, чтобы вставить все записи или указать диапазон записей. которые необходимо вставить, нажать ОК.
Упражнение 2. Скопировать данные из Word в поле базы данных Access.
Аналогично копированию из Word в Excel. Используется, если есть данные в документе Word, которые требуется вставить в Excel.
Упражнение 3. Конвертировать документы Word в таблицу базы данных Access.
Конвертирование Word в Access может быть осуществлено, если документ Word является разделяемым текстовым файлом, то есть данные для каждого из полей базы данных отделены друг от друга некоторым символом (например, запятой или символом табуляции). Пример: текстовый файл имеет вид: "Игорь","Смирнов","прос.Ленина,48","Иваново" ,"26-76-33".
Разделяемые текстовые файлы создаются обычно автоматически, путем форматирования стандартной корреспонденции. И вместо того , чтобы заново набивать информацию, ее можно импортировать в БД Ассеss.
Для этого необходимо :
1.Открыть или создать в Wordе разделяемый текстовый файл;
2.Выбрать команду “Файл /Сохранить как” и заполнить (указать диск, каталог и имя файла);
3.В списке “тип” выбрать команду “только текст с концами строк” и нажать ОК;
4.Переключиться в Access и открыть нужный файл базы данных. Появится окно базы данных;
5.Из меню “Файл” выбрать команду “Импорт”, появится диалоговое окно;
6.Показать логическое устройство, каталог, имя разделяемого текстового файла;
7.Активизировать кнопку “Импорт” и указать нужные параметры импорта;
8.Открыть раздел и внести корректировки.
II.Обмен между Excel и Access
Существует 2 способа копирования из базы данных Access в рабочий лист Excel:
Упражнение 1. Скопировать и вставить данные из каждого поля записи Access.
1. Запустить Access и открыть нужный файл базы данных.
2. Выбрать необходимые таблицу, запрос, отчет.
3. Выделить текст в одном из полей базы (так же, как в Word).
4. Выполнить команду "Правка"/"Копировать" и переключиться в Excel .
5. Установить курсор в нужную ячейку.
6. Выполнить команду "Правка"/"Вставить".
Упражнение 2. Преобразовать всю таблицу запроса( отчета) базы данных Access в формат Excel: конвертировать информацию из базы данных Excel.
1. Запустить Access и открыть нужный файл базы данных.
2. Выбрать необходимые таблицу, запрос, отчет.
3. Выполнить “Файл”/ “Вывести в формате”.
4. В диалоговом окне указать тип формата Microsoft Excel(.xls).
5. Указать путь (каталог, директорию, файл) и нажать ОК.
6. Переключиться в Excel и открыть этот файл.
7. Можно осуществлять редактирование этой таблицы.
Упражнение 3. Cкопировать данные Excel в поле базы данных Access (аналогично копированию из Access в Excel ), изучить самостоятельно.
Упражнение 4. Конвертировать электронную таблицу Excel в таблицу базы данных Access:
1.Открыть Excel и открыть нужный файл.
2.Провести все изменения в таблице Excel и сохранить файл.
3.Переключиться в Access, выполнить команду “Файл”/ “Открыть базу данных”.
4.Указать необходимый файл базы данных, в котором будет таблица.
5.Выполнить команду“Файл”/ “Импорт”.
6.В диалоговом окне указать тип ( формат данных) Microsoft Excel .
7.Указать файл, где были сохранены данные Excel .
8.Активизировать кнопку “Импорт”.
Каким образом размещается таблица Access в документе Word?
Каким образом размещается документ Word в таблице данных Access?
В чем отличие импортирования данных от конвертирования данных?
Как размещается таблица Access при копировании Excel как таблицы и каким образом редактировать ячейки?
Тут вы можете оставить комментарий к выбранному абзацу или сообщить об ошибке.
Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.
Все таблицы в книге указываются в списках полей сводной таблицы и Power View.
При импорте связанных таблиц из реляционной базы данных Excel часто может создавать эти связи в модели данных, формируемой в фоновом режиме. В других случаях необходимо создавать связи вручную.
Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
Вы можете отформатировать данные как таблицу или импортировать внешние данные в виде таблицы на новом.
Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор > Имя таблицы и введите имя.
Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения.
Например, чтобы связать продажи клиента с логикой операций со временем, обе таблицы должны включать дату в одинаковом формате (например, 01.01.2012) и по крайней мере в одной таблице (логика операций со временем) должны быть перечислены все даты только один раз в столбце.
Щелкните Данные> Отношения.
Если команда Отношения недоступна, значит книга содержит только одну таблицу.
В окне Управление связями нажмите кнопку Создать.
В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи "один ко многим" эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
Дополнительные сведения о связях между таблицами в Excel
Примечания о связях
Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
Создание связей аналогично использованию VLOOKUP: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel могли ссылаться на строки в одной таблице с строками из другой таблицы. В примере со временем в таблице Customer должны быть значения дат, которые также существуют в таблице аналитики времени.
В модели данных связи таблиц могут быть типа "один к одному" (у каждого пассажира есть один посадочный талон) или "один ко многим" (в каждом рейсе много пассажиров), но не "многие ко многим". Связи "многие ко многим" приводят к ошибкам циклической зависимости, таким как "Обнаружена циклическая зависимость". Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью "многие ко многим" или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением "один ко многим", но между первой и последней образуется отношение "многие ко многим"). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
Вы можете узнать о связях обеих таблиц и логики операций со временем с помощью свободных данных на Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики, и для их загрузки за разумное время необходимо быстрое подключение к Интернету.
Нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.
В разделе Price (Цена) нажмите Free (Бесплатно).
В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).
Найдите DateStream и нажмите кнопку Subscribe (Подписаться).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите Закрыть.
Чтобы импортировать второй набор данных, нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace.
В разделе Type (Тип) нажмите Data Данные).
В разделе Price (Цена) нажмите Free (Бесплатно).
Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 15 минут. После выполнения вы увидите отчет о состоянии перемещения 2 427 284 строк. Нажмите Закрыть. Теперь у вас есть две таблицы в модели данных. Чтобы связать их, нужны совместимые столбцы в каждой таблице.
Убедитесь, что значения в столбце DateKey в таблице BasicCalendarUS указаны в формате 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одинакового типа и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. В следующих действиях вы будете использовать эти столбцы, чтобы связать таблицы.
В окне Power Pivot нажмите Сводная таблица, чтобы создать сводную таблицу на новом или существующем листе.
В списке полей разверните таблицу On_Time_Performance и нажмите ArrDelayMinutes, чтобы добавить их в область значений. В сводной таблице вы увидите общее время задержанных рейсов в минутах.
Разверните таблицу BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.
Обратите внимание, что теперь в сводной таблице перечислены месяцы, но количество минут одинаковое для каждого месяца. Нужны одинаковые значения, указывающие на связь.
В списке полей, в разделе "Могут потребоваться связи между таблицами" нажмите Создать.
В поле "Связанная таблица" выберите On_Time_Performance, а в поле "Связанный столбец (первичный ключ)" — FlightDate.
В поле "Таблица" выберитеBasicCalendarUS, а в поле "Столбец (чужой)" — DateKey. Нажмите ОК для создания связи.
Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.
В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.
Теперь вы можете разделить задержки прибытия по годам и месяцам, а также другим значениям в календаре.
Советы: По умолчанию месяцы перечислены в алфавитном порядке. С помощью надстройки Power Pivot вы можете изменить порядок сортировки так, чтобы они отображались в хронологическом порядке.
Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.
В главной таблице нажмите Сортировка по столбцу.
В поле "Сортировать" выберите MonthInCalendar.
В поле "По" выберите MonthOfYear.
Сводная таблица теперь сортирует каждую комбинацию "месяц и год" (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.
"Могут потребоваться связи между таблицами"
По мере добавления полей в сводную таблицу вы получите уведомление о необходимости связи между таблицами, чтобы разобраться с полями, выбранными в сводной таблице.
Хотя Excel может подсказать вам, когда необходима связь, он не может подсказать, какие таблицы и столбцы использовать, а также возможна ли связь между таблицами. Чтобы получить ответы на свои вопросы, попробуйте сделать следующее.
Шаг 1. Определите, какие таблицы указать в связи
Если ваша модель содержит всего лишь несколько таблиц, понятно, какие из них нужно использовать. Но для больших моделей вам может понадобиться помощь. Один из способов заключается в том, чтобы использовать представление диаграммы в надстройке Power Pivot. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С помощью него вы можете быстро определить, какие таблицы отделены от остальной части модели.
Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
В этой статье рассматриваются многочисленные методы передачи данных в 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 имеют ограничение в 64 КБ на запросы интерфейса. Если вы достигнете или превысите это ограничение в 64 КБ для запросов интерфейса, сервер автоматизации (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 и передать содержимое таблицы Orders в образце базы данных Northwind с помощью метода CopyFromRecordset.
Примечание При использовании Office 2007 базы данных Northwind необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Excel 97 также предоставляет метод CopyFromRecordset, но его можно использовать только с набором записей DAO. CopyFromRecordset с Excel 97 не поддерживает ADO.
Дополнительные сведения об использовании ADO и метода CopyFromRecordset см. в статье о передаче данных из набора записей 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 с помощью данных из образца базы данных Northwind:
Использование буфера обмена
Буфер обмена Windows также можно использовать в качестве механизма передачи данных на лист. Чтобы вставить данные в несколько ячеек на листе, можно скопировать строку, в которой столбцы разделены символами табуляции, а строки разделены символами каретки. В следующем коде показано, Visual Basic использовать объект буфера обмена для передачи данных в Excel:
Создание текстового файла с разделителями, который Excel анализировать по строкам и столбцам
Excel могут открывать файлы с разделителями-табуляции или запятыми и правильно анализировать данные в ячейках. Эту функцию можно использовать, если вы хотите перенести большой объем данных на лист, используя небольшой объем (при наличии) службы автоматизации. Это может быть хорошим подходом для клиент-серверного приложения, так как текстовый файл может быть создан на стороне сервера. Затем можно открыть текстовый файл на клиенте с помощью службы автоматизации, где это необходимо.
В следующем коде показано, как создать текстовый файл с разделителями-запятыми из набора записей ADO:
Обратите внимание, что при использовании Office 2007 базы данных Northwind необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Если текстовый файл имеет расширение .CSV, Excel открывает файл без отображения мастера импорта текста и автоматически предполагает, что файл разделен запятыми. Аналогичным образом, если файл имеет расширение .TXT, Excel автоматически анализировать файл с помощью разделителей табуляции.
В предыдущем примере кода Excel с помощью инструкции Оболочки, а имя файла использовался в качестве аргумента командной строки. В предыдущем примере автоматизация не использовалась. Однако при необходимости можно использовать минимальный объем автоматизации, чтобы открыть текстовый файл и сохранить его в Excel формате книги:
Передача данных на лист с помощью ADO
С помощью поставщика Microsoft Jet OLE DB можно добавлять записи в таблицу в существующей Excel книге. Таблица в Excel является просто диапазоном с определенным именем. Первая строка диапазона должна содержать заголовки (или имена полей), а все последующие строки — записи. Ниже показано, как создать книгу с пустой таблицей с именем MyTable.
Excel 97, Excel 2000 и Excel 2003
Запустите новую книгу в Excel.
Добавьте следующие заголовки в ячейки A1:B1 листа Sheet1:
A1: FirstName B1: LastName
Отформатировать ячейку B1 как выровненную по правому краю.
В меню "Вставка" выберите "Имена", а затем выберите "Определить". Введите имя MyTable и нажмите кнопку "ОК".
Сохраните новую книгу как C:\Book1.xls и закройте Excel.
Чтобы добавить записи в MyTable с помощью ADO, можно использовать следующий код:
Excel 2007
В Excel 2007 запустите новую книгу.
Добавьте следующие заголовки в ячейки A1:B1 листа Sheet1:
A1: FirstName B1: LastName
Отформатировать ячейку B1 как выровненную по правому краю.
На ленте откройте вкладку "Формулы " и выберите команду "Определить имя". Введите имя MyTable и нажмите кнопку "ОК".
Сохраните новую книгу как C:\Book1.xlsx и закройте Excel.
Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, похожий на следующий пример кода.
При добавлении записей в таблицу таким образом форматирование в книге сохраняется. В предыдущем примере новые поля, добавленные в столбец B, форматируются с выравниванием по правому краю. Каждая запись, добавляемая в строку, заимствует формат из строки над ней.
Обратите внимание, что при добавлении записи в ячейку или ячейки листа она перезаписывает все данные, которые ранее были в этих ячейках. Другими словами, строки на листе не "помещаются вниз" при добавлении новых записей. Это следует учитывать при проектировании макета данных на листах.
Метод обновления данных на листе Excel с помощью ADO или DAO не работает в среде Visual Basic for Application в Access после установки Office 2003 с пакетом обновления 2 (SP2) или после установки обновления для Access 2002, включенного в статью базы знаний Майкрософт 904018. Этот метод хорошо работает в Visual Basic приложений из других Office приложений, таких как Word, Excel и Outlook.
Дополнительные сведения см. в следующей статье:
Дополнительные сведения об использовании ADO для доступа к книге Excel см. в статье "Как запрашивать и обновлять данные 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 форме:
При использовании LinkPoke с Excel указывается диапазон в нотации столбца строк (R1C1) для LinkItem. Если данные разделяются на несколько ячеек, можно использовать строку, в которой столбцы разделяются вкладками, а строки разделяются символами возврата каретки.
При использовании LinkExecute для Excel выполнения команды необходимо Excel в синтаксисе языка макросов Excel (XLM). Документация по XLM не входит в Excel 97 и более поздних версий.
DDE не рекомендуется для взаимодействия с Excel. Автоматизация обеспечивает максимальную гибкость и предоставляет больше доступа к новым функциям, Excel может предложить.
Существуют различные способы переноса данных из книги Excel в базы данных Access. Можно скопировать данные с открытого листа и вставить их в таблицу Access, импортировать лист в новую или существующую таблицу либо связать лист с базой данных Access.
В этой статье приведено подробное описание процедуры импорта или связывания данных Excel с классическими базами данных Access.
В этой статье
Общее представление об импорте данных из Excel
Если требуется сохранить данные одного или нескольких листов Excel в Access, следует импортировать содержимое листа в новую или существующую базу данных Access. При импорте данных в Access создается их копия в новой или существующей таблице, а исходный лист Excel не изменяется.
Стандартные сценарии импорта данных Excel в Access
Опытному пользователю Excel требуется использовать Access для работы с данными. Для этого необходимо переместить данные из листов Excel в одну или несколько новых таблиц Access.
В отделе или рабочей группе используется Access, но иногда данные поступают в формате Excel, и их необходимо объединять с базами данных Access. Требуется выполнить импорт полученных листов Excel в базу данных.
Пользователь применяет Access для управления данными, однако получает еженедельные отчеты от остальных участников команды в виде книг Excel. Требуется организовать процесс импорта таким образом, чтобы данные импортировались в базу данных каждую неделю в заданное время.
Первый импорт данных из Excel
Сохранить книгу Excel в виде базы данных Access невозможно. В Excel не предусмотрена функция создания базы данных Access с данными Excel.
При открытии книги Excel в Access (для этого следует открыть диалоговое окно Открытие файла, выбрать в поле со списком Тип файлов значение Файлы Microsoft Office Excel и выбрать файл) создается ссылка на эту книгу, но данные из нее не импортируются. Связывание с книгой Excel кардинально отличается от импорта листа в базу данных. Дополнительные сведения о связывании см. ниже в разделе Связывание с данными Excel.
Импорт данных из Excel
В этом разделе описано, как подготовиться к операции импорта, выполнить ее и как сохранить параметры импорта в виде спецификации для повторного использования. Помните, что данные можно одновременно импортировать только из одного листа. Импортировать все данные из книги за один раз невозможно.
Подготовка листа
Найдите исходный файл и выделите лист с данными, которые требуется импортировать в Access. Если необходимо импортировать лишь часть данных листа, можно задать именованный диапазон, содержащий только те ячейки, которые требуется импортировать.
Определение именованного диапазона (необязательно)
Перейдите в Excel и откройте лист, данные из которого нужно импортировать.
Выделите диапазон ячеек, содержащих данные, которые необходимо импортировать.
Щелкните выделенный диапазон правой кнопкой мыши и выберите пункт Имя диапазона или Определить имя.
В диалоговом окне Создание имени укажите имя диапазона в поле Имя и нажмите кнопку ОК.
Имейте в виду, что в ходе одной операции импорта можно импортировать лишь один лист. Чтобы импортировать данные нескольких листов, операцию импорта следует повторить для каждого листа.
Просмотрите исходные данные и выполните необходимые действия в соответствии с приведенной ниже таблицей.
Число исходных столбцов, которые необходимо импортировать, не должно превышать 255, т. к. Access поддерживает не более 255 полей в таблице.
Пропуск столбцов и строк
В исходный лист или именованный диапазон рекомендуется включать только те строки и столбцы, которые требуется импортировать.
Смещ_по_строкам В ходе операции импорта невозможно фильтровать или пропускать строки.
Столбцы. В ходе операции экспорта невозможно пропускать столбцы, если данные добавляются в существующую таблицу.
Убедитесь, что ячейки имеют табличный формат. Если лист или именованный диапазон включает объединенные ячейки, их содержимое помещается в поле, соответствующее крайнему левому столбцу, а другие поля остаются пустыми.
Пустые столбцы, строки и ячейки
Удалите все лишние пустые столбцы и строки из листа или диапазона. При наличии пустых ячеек добавьте в них отсутствующие данные. Если планируется добавлять записи к существующей таблице, убедитесь, что соответствующие поля таблицы допускают использование пустых (отсутствующих или неизвестных) значений. Поле допускает использование пустых значений, если свойство Обязательное поле (Required) имеет значение Нет, а свойство Условие на значение (ValidationRule) не запрещает пустые значения.
Чтобы избежать ошибок при импорте, убедитесь, что каждый исходный столбец содержит данные одного типа в каждой строке. Access сканирует первые восемь исходных строк, чтобы определить тип данных полей таблицы. Настоятельно рекомендуем убедиться в том, что первые восемь исходных строк не смешивают значения с разными типами данных ни в каких столбцах. В противном случае Access может не назначить столбецу правильный тип данных.
Рекомендуется также отформатировать все исходные столбцы в Excel и назначить им определенный формат данных перед началом операции импорта. Форматирование является необходимым, если столбец содержит значения с различными типами данных. Например, столбец "Номер рейса" может содержать числовые и текстовые значения, такие как 871, AA90 и 171. Чтобы исключить отсутствующие или неверные значения, выполните указанные ниже действия.
Щелкните заголовок столбца правой кнопкой мыши и выберите пункт Формат ячеек.
На вкладке Числовой в группе Категория выберите формат. Для столбца "Номер рейса" лучше выбрать значение Текстовый.
Если исходные столбцы отформатированы, но все же содержат смешанные значения в строках, следующих за восьмой строкой, в ходе операции импорта значения могут быть пропущены или неправильно преобразованы. Сведения о разрешении этих вопросов см. в разделе Разрешение вопросов, связанных с отсутствующими и неверными значениями.
Если первая строка листа или именованного диапазона содержит имена столбцов, в Access можно указать, что данные первой строки должны рассматриваться в ходе операции импорта как имена полей. Если исходный лист или диапазон не содержит имен, рекомендуется добавить их в исходные данные до операции импорта.
Примечание: Если планируется добавить данные в существующую таблицу, убедитесь, что имя каждого столбца в точности соответствует имени поля. Если имя столбца отличается от имени соответствующего поля в таблице, операция импорта завершится неудачей. Чтобы просмотреть имена полей, откройте таблицу в Access в режиме конструктора.
Закройте исходную книгу, если она открыта. Если исходный файл остается открытым в ходе операции импорта, могут возникнуть ошибки преобразования данных.
Подготовка конечной базы данных
Откройте базу данных Access, в которой будут храниться импортируемые данные. Убедитесь, что база данных доступна не только для чтения и что есть права на ее изменение.
Если ни одна из существующих баз данных не подходит для хранения импортируемых данных, создайте пустую базу данных. Для этого выполните указанные ниже действия.
Откройте вкладку Файл, нажмите кнопку Создать и выберите пункт Пустая база данных.
Перед началом операции импорта следует определить, в какой таблице будут храниться данные: в новой или существующей.
Создание новой таблицы. Если необходимо сохранить данные в новой таблице, в Access создается таблица, в которую добавляются импортируемые данные. Если таблица с указанным именем уже существует, содержимое существующей таблицы перезаписывается импортируемыми данными.
Добавление в существующую таблицу. При добавлении данных в существующую таблицу строки из листа Excel добавляются в указанную таблицу.
Следует помнить, что ошибки в ходе операции добавления зачастую объясняются тем, что исходные данные не соответствуют структуре и параметрам полей в конечной таблице. Чтобы избежать таких ошибок, откройте таблицу в режиме конструктора и проверьте указанные ниже параметры.
Первая строка. Если первая строка исходного листа или диапазона не содержит заголовки столбцов, убедитесь, что расположение и тип данных каждого столбца соответствуют нужному полю таблицы. Если же первая строка содержит заголовки столбцов, совпадение порядка следования столбцов и полей необязательно, но имя и тип данных каждого столбца должны в точности совпадать с именем и типом данных соответствующего поля.
Отсутствующие или лишние поля. Если одно или несколько полей исходного листа отсутствуют в конечной таблице, их следует добавить до начала операции импорта. Если же таблица содержит поля, которые отсутствуют в исходном файле, их не требуется удалять из таблицы при условии, что они допускают использование пустых значений.
Совет: Поле допускает использование пустых значений, если его свойство Обязательное поле (Required) имеет значение Нет, а свойство Условие на значение (ValidationRule) не запрещает пустые значения.
Индексированные поля. Если свойство Индексировано поля таблицы имеет значение Да (без повторов), соответствующий столбец исходного листа или диапазона должен содержать уникальные значения.
Для выполнения операции импорта перейдите к указанным ниже действиям.
Запуск операции импорта
Расположение мастера импорта или связывания зависит от используемой версии Access. Выполните действия, которые соответствуют вашей версии Access.
Если вы используете последнюю версию Access или Access 2019, доступную по подписке на Microsoft 365, на вкладке "Внешние данные" в группе "Импорт & Связь" нажмите кнопку "Новый источник данных > из файла > Excel".
Если вы используете Access 2016, Access 2013 или Access 2010, на вкладке Внешние данные в группе Импорт и связи нажмите кнопку Excel.
Примечание: Вкладка Внешние данные доступна только в том случае, если открыта база данных.
В диалоговом окне Внешние данные - Электронная таблица Excel в поле Имя файла укажите имя файла Excel, содержащего данные, которые необходимо импортировать.
Чтобы указать импортируемый файл, нажмите кнопку Обзор и воспользуйтесь диалоговым окном Открытие файла.
Укажите способ сохранения импортируемых данных.
Чтобы сохранить данные в новой таблице, выберите вариант Импортировать данные источника в новую таблицу в текущей базе данных. Позднее будет предложено указать имя этой таблицы.
Чтобы добавить данные в существующую таблицу, выберите параметр Добавить копию записей в конец таблицы и выберите таблицу в раскрывающемся списке. Этот параметр недоступен, если база данных не содержит таблиц.
Сведения о связывании с источником данных путем создания связанной таблицы см. ниже в разделе Связывание с данными Excel.
Будет запущен мастер импорта электронных таблиц, который поможет выполнить импорт. Перейдите к дальнейшим действиям.
Использование мастера импорта электронных таблиц
На первой странице мастера выберите лист, содержащий данные, которые необходимо импортировать, и нажмите кнопку Далее.
На второй странице мастера щелкните элемент листы или именованные диапазоны, выберите лист или именованный диапазон, который необходимо импортировать, и нажмите кнопку Далее.
Если первая строка исходного листа или диапазона содержит имена полей, выберите вариант Первая строка содержит заголовки столбцов и нажмите кнопку Далее.
Если данные импортируются в новую таблицу, заголовки столбцов используются в Access в качестве имен полей в таблице. Эти имена можно изменить в ходе операции импорта или после ее завершения. Если данные добавляются к существующей таблице, убедитесь, что заголовки столбцов исходного листа в точности соответствуют именам полей конечной таблицы.
Если данные добавляются к существующей таблице, перейдите к действию 6. Если данные добавляются в новую таблицу, выполните оставшиеся действия.
Мастер предложит просмотреть свойства полей. Щелкните столбец в нижней части страницы, чтобы отобразить свойства нужного поля. При необходимости выполните указанные ниже действия.
Просмотрите и измените имя и тип данных конечного поля.
Чтобы создать индекс для поля, присвойте свойству Индексировано (Indexed) значение Да.
Чтобы пропустить весь исходный столбец, установите флажок Не импортировать (пропустить) поле.
Настроив параметры, нажмите кнопку Далее.
На следующем экране задайте первичный ключ для таблицы. При выборе варианта автоматически создать ключ Access добавляет поле счетчика в качестве первого поля конечной таблицы и автоматически заполняет его уникальными значениями кодов, начиная с 1. После этого нажмите кнопку Далее.
Сведения о том, как запустить сохраненную спецификацию импорта или экспорта, см. в статье Запуск сохраненной спецификации импорта или экспорта.
Сведения о том, как запланировать выполнение задач импорта и связывания в определенное время, см. в статье Планирование спецификации импорта или экспорта.
Разрешение вопросов, связанных с отсутствующими и неверными значениями
Откройте целевую таблицу в режиме таблицы, чтобы убедиться, что в таблицу были добавлены все данные.
Откройте таблицу в режиме конструктора, чтобы проверить типы данных и другие свойства полей.
В приведенной ниже таблице описаны действия по разрешению проблем, связанных с отсутствующими или неверными значениями.
Графические элементы, такие как логотипы, диаграммы и рисунки, не импортируются. Их следует добавить в базу данных вручную после завершения операции импорта.
Импортируются результаты вычисляемого столбца или ячейки, но не базовая формула. В ходе операции импорта можно указать тип данных, совместимый с результатами формулы, например числовой.
Значения TRUE или FALSE и -1 или 0
Если исходный лист или диапазон включает столбец, который содержит только значения TRUE или FALSE, в Access для этого столбца создается логическое поле, в которое вставляется значение -1 или 0. Если же исходный лист или диапазон включает столбец, который содержит только значения -1 и 0, в Access для этого столбца по умолчанию создается числовое поле. Чтобы избежать этой проблемы, можно изменить в ходе импорта тип данных поля на логический.
При импорте данных в новую или существующую таблицу приложение Access не поддерживает многозначные поля, даже если исходный столбец содержит список значений, разделенных точками с запятой (;). Список значений обрабатывается как одно значение и помещается в текстовое поле.
В случае усечения данных в столбце таблицы Access попытайтесь увеличить ширину столбца в режиме таблицы. Если не удается решить проблему с помощью этого способа, это означает, что объем данных в числовом столбце Excel слишком велик для конечного поля в Access. Например, в базе данных Access свойство FieldSize конечного поля может иметь значение Байт, а исходные данные могут содержать значение больше 255. Исправьте значения в исходном файле и повторите операцию импорта.
Чтобы обеспечить правильное отображение значений в режиме таблицы, может потребоваться изменить свойство Формат некоторых полей в режиме конструктора. Ниже приведены примеры.
После завершения импорта в логическом поле в режиме таблицы отображаются значения -1 и 0. Чтобы устранить эту проблему, необходимо после завершения импорта изменить значение свойства Формат этого поля на Да/Нет для отображения флажков.
Даты в длинном и среднем форматах отображаются в Access как краткие даты. Чтобы устранить эту проблему, откройте конечную таблицу в Access в режиме конструктора и измените свойство Формат поля даты на Длинный формат даты или Средний формат даты.
Примечание: Если исходный лист содержит элементы форматирования RTF, например полужирный шрифт, подчеркивание или курсив, текст импортируется без форматирования.
Повторяющиеся значения (нарушение уникальности ключа)
Импортируемые записи могут содержать повторяющиеся значения, которые невозможно сохранить в поле первичного ключа в конечной таблице или в поле, для которого свойству Индексировано присвоено значение Да (без повторов). Удалите повторяющиеся значения в исходном файле и повторите операцию импорта.
Значения дат, сдвинутые на 4 года
Значения полей дат, импортированных с листа Excel, оказываются сдвинуты на четыре года. В Excel для Windows используется система дат 1900, в которой даты представляются целыми числами от 1 до 65 380, соответствующими датам от 1 января 1900 г. до 31 декабря 2078 г. В Excel для Macintosh используется система дат 1904, в которой даты представляются целыми числами от 0 до 63 918, соответствующими датам от 1 января 1904 г. до 31 декабря 2078 г.
Прежде чем импортировать данные, измените систему дат для книги Excel или выполните после добавления данных запрос на обновление, используя выражение [имя поля даты] + 1462 для корректировки дат.
Отформатируйте исходные столбцы.
Переместите строки таким образом, чтобы первые восемь строк каждого столбца не содержали значения с разными типами данных.
В ходе операции импорта выберите подходящий тип данных для каждого поля. Если тип данных указан неправильно, после завершения операции весь столбец может содержать пустые или неверные значения.
Описанные выше действия позволяют свести к минимуму количество пустых значений. В приведенной ниже таблице представлены ситуации, в которых пустые значения все же будут появляться.
Обмен данными через буфер производится для всех приложений Windows. Информация заносится в буфер по команде Правка/Копировать. Находящиеся в буфере данные в Excel могут вставляться в другой рабочий лист, другую рабочую книгу. Можно таблицу Excel вставить и в другое приложение Windows, например текстовый редактор Word. В этом случае после копирования данных в буфер обмена нужно переключиться (или загрузить) в редактор Word, задать команду Правка/Вставка. Данные скопируются в виде таблицы Word.
Динамический обмен данными DDE
Динамический обмен данными (Dynamic Data Exchange — DDE) позволяет установить постоянную связь по обмену данными между двумя программами Windows, например, Excel с текстовым редактором Word. Обмен данными в этом случае происходит автоматически, т.е. при каждом изменении цифр в таблице Excel такое же изменение происходит и в таблице, находящейся в текстовом редакторе. Обмен будет происходить только, если обе программы открыты в среде Windows одновременно. Для установки такой связи надо таблицу (или фрагмент таблицы) Excel скопировать в буфер обмена, переключиться в текстовый редактор, задать команду Правка/Специальная вставка, выбрать пункт Форматированный текст (rtf) и включить опцию Связать. Если вместо изображения таблицы в документе показывается строка символов в фигурных скобках, следует задать команду Сервис/Опции и во вкладке Просмотр сбросить переключатель Коды полей. Excel может выступать и как импортер, например, текста из редактора Word. В этом случае текст Word копируется в буфер обмена, затем нужно переключиться (или загрузить) в Excel, задать команду Правка/Вставка/Специальная вставка и включить опцию Связать. Текст вставляется в виде объекта. Аналогичным образом можно вставить диаграмму Excel на слайд PowerPoint.
Связь и внедрение объектов OLE
Функция связи и внедрения объектов (Object Linking And Embedding — OLE) позволяет организовать динамический обмен данными, с помощью объектов. Для установки такой связи таблица (или фрагмент таблицы) Excel копируется в буфер обмена, затем нужно переключиться в текстовый редактор, установить курсор в нужное место задать команду Вставка/Объект, вкладку Создать новый, в поле Тип объекта выбрать строку Лист Microsoft Excel. После этого в документ будет вставлена пустая таблица Excel. Выделить начальную ячейку таблицы, задать команду Правка/Вставить, чтобы перенести данные из буфера обмена в новую таблицу. Вставленную таблицу Excel можно редактировать в состоянии, когда она обрамлена штриховой рамкой. Чтобы продолжить работу с текстом, достаточно щелкнуть мышью в области текста вне таблицы. При необходимости повторного редактирования таблицы нужно дважды щелкнуть внутри нее мышью.
Читайте также: