Обмен данными с excel


Содержание

Способы переноса данных в Excel из Visual Basic

Описание

В этой статье рассказывается о многочисленных способах переноса данных в Microsoft Excel из приложения Microsoft Visual Basic. В этой статье также представлены преимущества и недостатки каждого метода, чтобы вы могли выбрать решение, которое лучше всего подходит для вас.

Дополнительные сведения

Наиболее распространенный подход, используемый для передачи данных в книгу Excel, — Автоматизация. Автоматизация предоставляет максимальную гибкость для указания расположения данных в книге, а также возможность форматирования книги и создания различных параметров во время выполнения. С помощью автоматизации вы можете использовать несколько подходов для переноса данных:

  • Перенос ячейки данных по ячейке
  • Передача данных в массиве в диапазон ячеек
  • Передача данных из набора записей ADO в диапазон ячеек с помощью метода Копифромрекордсет
  • Создание QueryTable на листе Excel, который содержит результат запроса в источнике данных ODBC или OLEDB
  • Перенесите данные в буфер обмена, а затем вставьте содержимое буфера обмена в лист Excel.

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

  • Перенос данных в текстовый файл с разделителями — табуляцией или запятыми, который Excel может выполнить в дальнейшем, разбить на ячейки листа
  • Передача данных на лист с помощью ADO
  • Передача данных в Excel с помощью динамического обмена данными (DDE)

В следующих разделах приводятся более подробные сведения о каждом из этих решений.

Note (Примечание ) При использовании Microsoft Office Excel 2007 вы можете использовать новый формат файла книги Excel 2007 (XLSX) при сохранении книг. Для этого откройте следующую строку кода в следующих примерах кода:

Замените этот код следующей строкой кода:

Кроме того, база данных «Борей» по умолчанию не включена в Office 2007. Тем не менее, вы можете скачать базу данных Northwind из Microsoft Office Online.

Использование автоматизации для передачи ячейки данных по ячейкам

С помощью автоматизации можно переносить данные на лист по одной ячейке за раз:

Передача данных по ячейкам может быть приемлемым способом, если объем данных мал. У вас есть гибкие возможности для размещения данных в любом месте книги и для условного форматирования ячеек во время выполнения. Однако этот подход не рекомендуется, если для передачи в книгу Excel требуется большой объем данных. Каждый объект диапазона, полученный во время выполнения, вызывает запрос интерфейса таким образом, что передача данных таким способом может замедлиться. Кроме того, Microsoft Windows 95 и Windows 98 имеют ограничение на 64 КБ для запросов интерфейса. Если вы достигли предельного значения 64 КБ на запросах интерфейса, сервер автоматизации (Excel) может перестать отвечать на запросы или могут возникать ошибки, указывающие на нехватку памяти.

Как раз, передача данных по ячейке допускается только для небольших объемов данных. Если вам нужно перенести большие наборы данных в Excel, следует рассмотреть одно из решений, представленных ниже.

Дополнительные примеры кода для автоматизации Excel приведены в статье Автоматизация Microsoft Excel в Visual Basic.

Использование автоматизации для переноса массива данных в диапазон листа

Массив данных можно переносить в диапазон нескольких ячеек одновременно:

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

Эта строка представляет два запроса интерфейса (один для объекта Range, возвращаемого методом Range, а другой для объекта Range, возвращаемого методом resize). С другой стороны, при передаче ячейки данных по ячейке потребуются запросы 300 интерфейсов к объектам Range. Если это возможно, вы можете воспользоваться преимуществами для массового переноса данных и уменьшения количества запросов к интерфейсу.

Использование автоматизации для переноса набора записей ADO в диапазон листа

В Excel 2000 появился метод Копифромрекордсет, позволяющий перенести набор записей ADO (или DAO) в диапазон на листе. В приведенном ниже коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 и перенести содержимое таблицы Orders в образце базы данных Northwind с помощью метода Копифромрекордсет.

Note (Примечание ) Если вы используете версию Office 2007 для базы данных Northwind, необходимо заменить следующую строку кода в примере кода:

Замените эту строку кода следующей строкой кода:

Excel 97 также предоставляет метод Копифромрекордсет, но его можно использовать только с набором записей DAO. Копифромрекордсет с Excel 97 не поддерживает ADO.

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

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

Использование автоматизации для создания QueryTable на листе

Объект QueryTable представляет таблицу, созданную на основе данных, возвращенных из внешнего источника данных. При автоматизации Microsoft Excel можно создать QueryTable, просто предоставив строку подключения к OLEDB или источнику данных ODBC, а также строку SQL. Excel предполагает ответственность за создание набора записей и вставку его на лист в указанном расположении. Использование QueryTables предоставляет ряд преимуществ по сравнению с методом Копифромрекордсет:

  • Excel обрабатывает создание набора записей и его расположение на листе.
  • Запрос можно сохранить вместе с QueryTable, чтобы его можно было обновить позже, чтобы получить обновленный набор записей.
  • Когда на лист добавляется новый QueryTable, вы можете указать, что данные, уже существующие в ячейках листа, будут смещены в соответствии с новыми данными (Дополнительные сведения см. в свойстве Рефрешстиле).

В приведенном ниже коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003, чтобы создать новый QueryTable на листе Excel с помощью данных из учебной базы данных Northwind:

Использование буфера обмена

Буфер обмена Windows также можно использовать в качестве механизма передачи данных на лист. Чтобы вставить данные в несколько ячеек листа, можно скопировать строку, в которой столбцы разделяются символами табуляции, а строки — символами возврата каретки. В приведенном ниже коде показано, как Visual Basic может использовать объект Clipboard для передачи данных в Excel:

Создание текстового файла с разделителями, который Excel может проанализировать по строкам и столбцам

Excel может открывать файлы с разделителями табуляцией и запятыми и правильно анализировать данные в ячейки. Вы можете использовать эту функцию, если вы хотите перенести большой объем данных на лист, используя практически любую автоматизацию. Это может быть хорошим подходом к приложению «клиент-сервер», так как текстовый файл может быть создан на стороне сервера. Затем можно открыть текстовый файл на клиенте, используя автоматизацию там, где это необходимо.

В приведенном ниже коде показано, как создать текстовый файл с разделителями запятыми из набора записей ADO.

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

Замените эту строку кода следующей строкой кода:

Если текстовый файл имеет расширение. Расширение CSV, Excel открывает файл без отображения мастера импорта текста и автоматически предполагает, что файл разделяются запятыми. Аналогично, если файл имеет расширение. TXT, Excel автоматически проанализирует файл с помощью разделителей табуляции.

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

Передача данных на лист с помощью ADO

С помощью поставщика OLE DB для Microsoft Jet можно добавлять записи в таблицу в существующей книге Excel. «Таблица» в Excel — это просто диапазон с определенным именем. Первая строка диапазона должна содержать заголовки (или имена полей), а все последующие строки содержат записи. В следующей процедуре показано, как создать книгу с пустой таблицей с именем MyTable.

Excel 97, Excel 2000 и Excel 2003

Создайте новую книгу в Excel.

Добавьте следующие заголовки в ячейки a1: B1 из Лист1:

A1: имя B1: LastName

Форматирование ячейки B1 в соответствии с выравниванием по правому краю.

В меню Вставка выберите пункт имена, а затем выберите команду определить. Введите имя MyTable и нажмите кнопку ОК.


Сохраните новую книгу как C:\Book1.xls и закройте Excel.

Чтобы добавить записи в MyTable с помощью ADO, можно использовать код, аналогичный приведенному ниже:

Excel 2007

В Excel 2007 создайте новую книгу.

Добавьте следующие заголовки в ячейки a1: B1 из Лист1:

A1: имя B1: LastName

Форматирование ячейки B1 в соответствии с выравниванием по правому краю.

На ленте щелкните вкладку формулы , а затем выберите команду задать имя. Введите имя MyTable и нажмите кнопку ОК.

Сохраните новую книгу как C:\Book1.xlsx, а затем закройте Excel.

Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, похожий на следующий пример кода.

При добавлении записей в таблицу таким образом форматирование в книге сохраняется. В предыдущем примере новые поля, добавленные в столбец B, отформатированы с использованием выравнивания по правому краю. Каждая запись, добавляемая в строку, помещается в формат из строки над ней.

Обратите внимание, что при добавлении записи к ячейке или ячейкам на листе он перезаписывает все данные, ранее находящиеся в этих ячейках; другими словами, при добавлении новых записей строки в рабочем листе не перемещаются. Это следует учитывать при проектировании макета данных на листах.

Метод обновления данных на листе Excel с помощью ADO или с помощью DAO не работает в Visual Basic для среды приложений в Access после установки пакета обновления 2 (SP2) для Office 2003 или после установки обновления для Access 2002, включенного в микропрограммы Статья базы знаний Майкрософт 904018. Этот метод хорошо работает в Visual Basic для приложений в среде приложений из других приложений Office, таких как Word, Excel и Outlook.

Для получения дополнительных сведений щелкните следующий номер статьи базы знаний Майкрософт: 904953 невозможно изменить, добавить или удалить данные в таблицах, связанных с книгой Excel в Office Access 2003 или в Access 2002

Дополнительную информацию об использовании ADO для доступа к книге Excel можно узнать, как запрашивать и обновлять данные Excel с помощью ADO из ASP.

Использование DDE для передачи данных в Excel

DDE является альтернативой автоматизации для связи с Excel и передачи данных; Однако с появлением автоматизации и COM-интерфейс DDE больше не является предпочтительным методом связи с другими приложениями и его следует использовать только в том случае, если вы не можете использовать другие решения.

Для передачи данных в Excel с помощью DDE можно использовать метод Линкпоке для обмена данными с определенным диапазоном ячеек или с помощью метода Линкексекуте для отправки команд, которые будут выполняться приложением Excel.

В приведенном ниже примере кода показано, как установить сеанс DDE с Excel, чтобы можно было выполнять ввод данных в ячейки листа и выполнять команды. С помощью этого примера можно успешно установить сеанс DDE в Линктопик Excel | Мибук. xls книга с именем Мибук. xls должна быть уже открыта в запущенном экземпляре Excel.

При использовании Excel 2007 можно сохранить книги, используя новый формат файла XLSX. Убедитесь, что вы обновляете имя файла в приведенном ниже примере кода.

Note (Примечание ) В этом примере Текст1 представляет элемент управления «текстовое поле» в форме Visual Basic:

При использовании Линкпоке с Excel необходимо указать диапазон в нотации Row-Column (R1C1) для Линкитем. При Покинг данных к нескольким ячейкам можно использовать строку, в которой столбцы разделяются символами табуляции, а строки — символами возврата каретки.

При использовании Линкексекуте для выполнения команды в Excel необходимо дать команду в виде синтаксиса в формате макросов Excel (XLM). Документация по XLM не входит в состав Excel версий 97 и более поздних версий. DDE не является рекомендуемым решением для связи с Excel. Автоматизация обеспечивает максимальную гибкость и предоставляет вам дополнительный доступ к новым функциям, которые предлагает Excel.

Обмен данными с excel

Уважаемы мастера, есть такой вопрос: есть DBGrid1 (пустой), связан с Table1, 3 столбца, 4 строки. Так же есть Excel таблица, в которой есть так же 3 столбца и 4 строки, но они начинаются не с первой ячейки (А1), а с С8, и заканчиваются соответственно с Е11. (Сама таблица начинается с А1). Так вот сам вопрос: как перенести эти данные из ячеек Excel в ячейки DBGrid, уже всю голову сломал, пока ни один метод не помог, может быть кто-то есть поопытней! Только не спрашивайте зачем именно в DBGrid, сам не понимаю – задание такое. Заранее благодарен!

А методы DBGrid никакого
Можно попробовать через BDE+ODBC (что тебе вероятно, и придется сделать, но я это не пробовал), а можно использовать ActiveX automation в обычный Grid.
Вот тебе фрагменты кода из одной моей программки:

var ExOle :OleVariant;
Sheet1, Sheet2, Cells:OleVariant;
.

ASD-SOFT

Программирование. Теория и практика.

Обмен данными с MS Excel в Delphi при помощи OLE.

Обмен данными с MS Excel в Delphi при помощи OLE.

Здравствуйте уважаемые коллеги!

Все мы рано или поздно сталкиваемся с задачами обмена данных с приложениями пакета MS Office. Одно из них — это MS Excel. И именно о взаимодействии с данным продуктом MS Office пойдет речь в данной статье.

Один из способов взаимодействия Delphi c MS Excel — это подключиться к нему как к OLE объекту.

Итак.
Прежде всего для работы с MS Excel и OLE добавим в секцию Uses модули ComObj и ActiveX.

И первое что нам нужно проверить, а установлен ли MS Excel на компьютере пользователя в принципе.
Для этого воспользуемся функцией CLSIDFromProgID, которая ищет в реестре CLSID для переданного ProgID:
Справка из MSDN: Метод CLSIDFromProgID
Параметры:
pszProgID: POleStr — Строка с именем объекта
clsid: TCLSID — Указатель на структуру TGUID в которую передается найденный объект;
Возвращает:
HRESULT — Результат, который может принимать значения:
S_OK — объект найден;
CO_E_CLASSSTRING — Зарегистрированный CLSID для ProgID является недействительным;
REGDB_E_WRITEREGDB — Ошибка записи CLSID в реестр.
Из перечисленных результатов нам нужен S_OK.
Напишем функию для определения наличия Excel у пользователя:

Если Excel установлен, тогда выполним подключение к нему. Сделать это можно двумя способами: GetActiveOleObject — Получить ссылку на уже запущенный экземпляр Excel или CreateOleObject — Создать новый экземпляр Excel.
Если у нас стоит задача получать данные из запущенного Excel, тогда мы должны использовать только первый вариант, в остальных случаях пробуем подключиться и если не получается, то создаем.
Напишем 2 функции, для подключения XlsConnect и запуска нового XlsStart:
Добавим переменную FXlsApp с типом Variant, которая будет содержать в себе ссылку на объект Excel.

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

По умолчанию окно Excel запускается в фоновом режиме. Строка FXlsApp.Visible := True; делает фоновое окно Excel видимым.

Окно Excel запускается пустое и в него нужно добавить рабочую книгу. Делается это при помощи метода WorkBooks.Add, который добавляет новую книгу или открывает ранее сохраненную, если указать путь к файлу.
Добавим процедуру, которая будет это делать:

Книга добавлена, теперь попробуем записать что-нибудь в неё.

Где Row — индекс строки, и Col — индекс столбца, которые начинаются с единицы.

Илон Маск рекомендует:  Что такое код gzdeflate

Где Range — массив ячеек, а А1 — привычные для Excel координаты ячейки.
В качестве координат может быть указан диапазон. Например, код

заполнит цифрой 5 все ячейки с А3 по А10, а код

выделит тот же диапазон светло-зеленым цветом.
В обратную сторону, то есть для получения данных из Excel, работает так же. Строка

Выведет сообщение с содержимым ячейки с координатами: Строка=5, Столбец=1.

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

Где ActiveWorkbook — текущая книга.
И закрыть приложение Excel командой:

Как понимаете этим возможности управления Excel из Delphi не ограничиваются. И есть один достаточной простой способ узнать, как выполнить необходимо действие с Excel из Delphi.
Называется оно — Макросы.

Представим, что нам необходимо выполнить объединение нескольких ячеек в одну и мы не знаем как это сделать. Но хотим узнать. Для этого выполняем следующие шаги:
1. Запускаем Excel и создаем пустую книгу.
2. Запускаем команду «Записать макрос», по умолчанию название макроса будет «Макрос1». (В разных версиях Excel данная команда находится в разных пунктах меню).
3. Выделяем некоторый диапазон ячеек и нажимаем кнопку «Объединить и поместить в центре».
4. Останавливаем запись макроса.
5. Вызываем список макросов и выбираем там свой записанный макрос.
6. Нажимаем кнопку «Изменить»
Запускается редактор Microsoft Visual Basic for Application в котором видим код проделанных действий:

Давайте разберем по подробнее, что же такого он нам тут написал:
With Selection — Для выделенного диапазона ячеек настраиваем свойства:
HorizontalAlignment = xlCenter — Горизонтальная ориентация = по центру.
VerticalAlignment = xlBottom — Вертикальная ориентация — по нижнему краю.
WrapText = False — Перенос текста по словам — выключен.
Orientation = 0 — Ориентация 0 градусов.
AddIndent = False — Использование автоматического отступа вкл/выкл.
IndentLevel = 0 — Уровень отступа в 0.
ShrinkToFit = False — Сжимать текст по размерам столбца вкл/выкл.
ReadingOrder = xlContext — Порядок чтения по контексту.
MergeCells = False — Объединенные ячейки вкл/выкл
End With — Конец секции работы с выделенным диапазоном.
Selection.Merge — Объединить выделенный диапазон.


Теперь попробуем объединить ячейки из Delphi:

Выделяем нужный нам диапазон.

Объединяем ячейки задав свойство. Или при помощи метода:

Таким способом можно получать код практически для любых необходимых манипуляций.
А если какое-то свойство или метод вызывает вопросы, то можно воспользоваться справкой на MSDN.

Обратите внимание на особенность работы с массивами в VBA. Индексы в массивах в Delphi оборачиваются в квадратные скобки, в то время как в VBA они будут в круглых. И код в Delphi

в VBA будет выглядеть как

Ниже приведу небольшой FAQ по вопросу взаимодействия с Excel из Delphi

Как определить значения констант в Excel для использования в Delphi?

В редакторе VBA ставим точку остановки напротив интересующей константы. Нажимаем выполнить и когда выполнение остановиться, наводим на интересующую константу:

Как отключить выводы сообщений в Excel?

Как получить список книг из Excel?

Как отключить отображение сетки?

Как вывести текущий лист на предпросмотр печати?

Как выделить жирным часть текста в ячейки?

Как выполнить автоподбор высоты строки для склеенной ячейки?

Как получить используемый диапазон ячеек?

Как получить букву столбца по индексу?

Обмен данными между листами книги данных Excel

Читайте также:

  1. C. Производят обработку данных.
  2. I. НОВАЯ КОНЦЕПЦИЯ МЕЖДУНАРОДНЫХ ОТНОШЕНИЙ
  3. I. О различии между чистым и эмпирическим познанием
  4. III. СССР В ПОСЛЕВОЕННЫХ МЕЖДУНАРОДНЫХ ОТНОШЕНИЯХ
  5. IV. О различии между аналитическими и синтетическими суждениями
  6. Kобмен опытом
  7. REGRESSION в MS Excel
  8. V Криволинейная зависимость между изучаемыми явлениями.
  9. VII. Отношения между князьями Рюрикова дома и порядок княже­ского владения
  10. А3. Ввести данные об отпуске материала в базу данных, распечатать оборотные ведомости.
  11. Августа 2014 г. вступили в силу изменения порядка оформления договора между организатором и участником игр
  12. Адиабатное расширение без теплообмена, всё изменение внутренней энергии газа преобразуется в механическую работу ( ), в результате газ охлаждается до температуры холодильника .

Практическая работа

Ход выполнения работы

Практическая работа

Расчет суммы платежа по кредиту, создание резюме (использование шаблонов)

Цель работы

Научиться работать с шаблонными формами Excel, Word.

Задание

1. Создать книгу данных Excel

2. При помощи шаблонов Excel создать файл «Рассрочка» (см. Рис.)

3. Заполнить поля (свободные данные)

4. Проанализировать зависимость изменения платежей от процента, суммы, количества лет.

5. Рассмотреть возможные шаблонные формы Excel, Word.

6. В Word создать изысканное резюме.

7. Написать отчет.

Выбор шаблонной формы

3. Шаблоны на моем компьютере

4. Вкладка Решения

5. Выбрать шаблон

Цель работы

Научиться использовать данные предыдущего периода в текущих расчетах

Задание

  1. Создать таблицу расходов не менее семи наименований и доходов за январь (см. рис.Лист 1)
  2. Обеспечить подсчет итогов по каждому виду расходов
  3. На втором листе создать таблицу расходов и доходов за февраль с учетом сумм января (см. рис.Лист 2)
  4. Обеспечить подсчет итогов февраля.

Примечание: При заполнении таблицы необходимо пользоваться:

  1. Средствами автозаполнения (поле дата)
  2. Автоматизированным суммированием значок автосумма
  3. Формулой расчета остатка месяца доходы-расходы
  4. Доходы февраля рассчитываются по формуле (остаток января + сумма дохода февраля (см. рис. Лист 2 строка формул)

Дата добавления: 2014-11-08 ; Просмотров: 599 ; Нарушение авторских прав? ;

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

Обмен данными между Excel и MySQL

Вряд ли мы откроем секрет, если скажем, что MySQL является на сегодняшний день одной из наиболее доступных реляционных СУБД. Она бесплатна, стабильна в работе и ко всему прочему отличается неплохим быстродействием. Не случайно эта система управления базами данных пользуется огромной популярностью у разработчиков веб-приложений, построенных на основе клиент-серверной технологии.
Конечно, время никогда не проходит даром, в том числе и для MySQL. Эволюционировав до 5й версии, она значительно возмужала и обзавелась отсутствующими ранее функциями (поддержкой триггеров, вложенных запросов и др.), но… «У «Жигулей» тоже четыре колеса, фары, руль и прочие атрибуты, но это не Mercedes. Точно так же и MySQL — это не Oracle», — процитировал мне знакомый программист, пытаясь объяснить разницу между существующими платформами для хранения данных. Глубокомысленное высказывание, ничего не скажешь. Ладно, долой философию, займемся делом, а именно — обменом данных в связке «Microsoft Excel — MySQL».

Матерым программистам баз данных наверняка по зубам эта операция, но мы облегчим свою участь при помощи специальной надстройки к табличному редактору Excel с длиннющим названием Excel to MySQL Import, Export & Convert 1.1 от компании Sobolsoft. Размер утилиты — 5,7 Мб, стоимость — 20 долларов США.


Установка приложения проходит без шума и пыли, единственное, необходимо понизить уровень безопасности Excel (Сервис -> Макрос -> Безопасность), разрешив ему выполнять макросы.

Перед началом работы нужно подключить базу данных при помощи специального мастера Setup MySQL Database Connection и установить соединение. А далее все как по маслу: при желании можно экспортировать выбранные ячейки в таблицу MySQL, либо, напротив, импортировать нужную информацию из базы данных. Естественно, без хотя бы минимальных знаний основ языка структурированных запросов SQL здесь не обойтись, но это не страшно — ведь документации навалом.

Что до области применения этой утилитки от Sobolsoft, то она воистину многогранна, начиная от использования в качестве удобного инструмента для операторов баз данных и заканчивая эксплуатацией в собственных программных разработках. На этой оптимистической ноте и поставим точку в нашей маленькой заметке.

Практикум: «Обмен данными между Microsoft Access и Microsoft Excel»

Использование внешних данных (импорт, экспорт и связывание)

При работе с Access зачастую возникает необходимость использования данных, сохраненных в других форматах (например, в файлах .xls) или расположенных в других местах (по сравнению с текущей базой данных). Для таких случаев используют одно из следующих средств:

1) импорт данных;

Часто приходится выполнять обратную операцию – передавать данные из Access в другие приложения – Word, Excel, Lotus или другую базу данных – MS Access, Microsoft SQL Server, Oracle. В таких случаях проводят процедуру 3) экспорта данных.

Импорт данных

В Access могут быть импортированы файлы следующих типов: файлы Microsoft Access (.mdb, можно импортировать таблицы, запросы и прочие объекты), файлы электронных таблиц(Microsoft Excel (.xls), Lotus Notes (.wks, wrk)), Microsoft Outlook, Текстовые файлы (.txt), вэб-страницы (html, xml), ODBC Databases (файлы других баз данных).

Для импорта нужного файла нужно выбрать вкладку Файл, затем Внешние данные, после чего выбираем Импорт (File, Get External Data, Import). Появится диалоговое окно Импорт (Import). Далее выбираем тип импортируемого файла (например, Microsoft Excel), находим нужный файл и нажимаем Импорт. Можно произвести импорт другим способом: в окне базы данных щелкнуть на свободном месте правой кнопкой мыши и в появившемся окне выбрать строку Импорт.

Импортировать данные можно только в таблицы – в новые или уже существующие.

Связывание

Если при импорте данные из исходного файла копировались в новую или существующую таблицу, то при связывании Access просто получает доступ к данным другой таблицы, при этом данные по-прежнему хранятся в исходном файле. Сначала данные в присоединенной таблице доступны только для чтения. Для того чтобы данные в присоединенной таблице можно было не только просматривать, но и изменять, эта таблица обязательно должна иметь ключевое поле.

Чтобы присоединить внешнюю таблицу к базе данных, выберите команду Файл, Внешние данные, Связь с таблицами (File, Get External Data, Link Tables). Появится диалоговое окно Связь, в котором нужно выбрать тип связываемого файла и путь к нему. Возможные типы файлов для связывания те же, что и для импорта.

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

Если изменить имя или месторасположение присоединенного файла, то для нормальной работы с этим файлом связь нужно обновить. Обновить связь с таблицей можно двумя способами.

Первый способ состоит в том, что нужно просто удалить присоединенную таблицу из базы данных, а потом присоединить вновь.

Второй способ обновления связи с внешней таблицей — это использование Диспетчера связанных таблиц (Linked Table Manager). Чтобы воспользоваться Диспетчером связанных таблиц: выберите команду меню Сервис, Служебные программы, Диспетчер связанных таблиц (Tools, Database Utilities, Linked Table Manager).

Экспорт данных

Экспортировать данные из Access можно в файлы различных типов (уже перечисленные + текстовый формат RTF). Экспортировать данные Access можно не только из таблицы, но и из запроса. Можно экспортировать не только данные, но и объекты Access — формы, отчеты, макросы. Для экспорта данных следует: 1) в окне базы данных выбрать экспортируемый объект (например, нужную таблицу), выделив его мышкой; 2) выбрать в строке меню команду Файл и в раскрывшемся меню выбрать Экспорт(или щелкнуть на нужном объекте правой кнопкой мыши и в появившемся окне выбрать Экспорт); 3) В окне появившемся окне Экспорт объектавыбрать тип файла и путь к нему, куда будет происходит экспорт.

Access позволяет быстро экспортировать данные из таблицы в файл Microsoft Excel. Выполняется это с помощью команды Связи с Office (Office Links). При этом можно экспортировать как всю таблицу, так и ее часть. Если нужно экспортировать всю таблицу, выделите эту таблицу в списке таблиц окна базы данных и и выберите Сервис ->Связи с Office->Анализ в Microsoft Office Excel (или нажмите стрелку справа от кнопки Связи с Office на панели инструментов). Access создаст новый файл XLS, скопирует в него данные из выделенной таблицы, запустит приложение Excel и откроет в нем этот файл. Аналогично можно экспортировать только часть таблицы Access, например подмножество строк или даже диапазон ячеек. Для этого необходимо открыть таблицу в режиме Таблицы, выбрать подмножество строк, например с помощью фильтра, или выделить диапазон ячеек, затем выполнить команду Сервис, Связи с Office, Анализ в MS Excel (Tools, Office Links, Analyze It with Microsoft Excel), и выбранные данные будут экспортированы в Excel.

Практикум: «Обмен данными между Microsoft Access и Microsoft Excel»

Цель работы:научиться производить обмен данными между MS Access и MS Excel. Данные будут храниться в базе данных Access, а расчеты проводиться в Excel.

Задача:расчет месячной премии сотрудников банка.

1)Запустите MS Access. Создайте новую или откройте уже существующую базу данных. Создайте таблицу «Сотрудники». Она должна иметь следующую структуру:

Занесите в таблицу данные сотрудников (5-10 человек). Сохраните и закройте таблицу.

2) Перейдите к окну базы данных. Выделите левой кнопкой мыши таблицу «Сотрудники» и произведите быстрый экспорт в MS Excel (Сервис->Связи с Office->Анализ в Microsoft Office Excel).

3) В открывшемся файле Excel вы увидите 5 заполненных столбцов (Код сотрудника, ФИО, Должность, Телефон, Зарплата) с введенными вами данными. Дополните таблицу следующими столбцами: Премия, Суммарный доход, Средняя зарплата и Ставка (рис.1).

4) В ячейке H2 рассчитайте среднюю зарплату сотрудников. В ячейках I2 и I3 введите значения процента премии: 20% и 10% соответственно.

Премия сотрудников (ячейки F2:F6) рассчитывается по формуле:

«Если зарплата сотрудника Внешние данные->Импорт. В строке Тип Файлов открывшегося окна Импорт выберите Microsoft Excel и найдите созданный вами файл «Сотрудники». Нажмите кнопку Импорт. Откроется окно «Импорт электронной таблицы» (рис.2), в котором должен стоять флажок «Первая строка содержит заголовки столбцов».

Нажмите далее. В следующем окне выберите пункт Данные необходимо сохранить в новой таблице.Нажмите Далее. Появится окно, позволяющее описывать каждое поле создаваемой таблицы. Щелкните на столбце Код сотрудника и в поле Индекс выберите Да (Совпадения не допускаются). (рис. 3) Нажмите Далее. В следующем окне выберите пункт Определить ключ (рис.4). Проверьте, что Access выбрал столбец Код сотрудника. Щелкните Далее.

В следующем окне в строке «Импорт в таблицу» наберите: Сотрудники (или оставьте без изменений, если это уже сделал Access). Нажмите готово. На вопрос «Переписать существующую таблицу или запрос?» ответьте Да.

Откройте таблицу Сотрудники. Она должна содержать новые столбцы: Премия, Суммарный доход, Средняя зарплата и Ставка (рис. 5).

6) Однако импорт данных обладает следующим недостатком: в случае каких-либо изменений (изменение размеров зарплаты сотрудников или изменение ставок премии) все вышеперечисленные действия придется повторить. Чтобы этого избежать, гораздо удобнее произвести процедуру связывания, т.е. связать экселевский файл «Сотрудники» с базой данных Access. Для этого вернемся к окну базы данных. Выберите Файл->Внешние данные->Связь с таблицами (или щелкните правой кнопкой мыши на свободном месте и в появившемся меню выберите строку «Связь с таблицами»). В открывшемся окне Связь выберите Тип файлов Microsoft Excel,найдите файл «Сотрудники» и нажмите кнопку Связь.В следующем окне выберите объект Листыи щелкните Далее. Следуйте указаниям Мастера, в последнем окне выберите имя связанной таблицы (например, «СотрудникиExc») и нажмите Готово. Откройте связанную таблицу и попробуйте изменить в ней данные, например, удалить какого-либо нового сотрудника. Если подобное действие не сработает, перейдите в Конструктор и задайте ключевое поле «Код сотрудника». Сохраните изменения и вновь перейдите в режим таблицы. Попробуйте произвести изменения в таблице. Сохраните произведенные изменения и закройте таблицу.

Теперь все изменения в экселевском файле «Сотрудники» будут автоматически отражаться в связанной таблице «СотрудникиExc» базы данных Access.

Обмен данными между Excel и VBA

Методические указания

Процессор ЭВМ манипулирует с данными, имеющимися в памяти машины. В зависимости от конкретной задачи эти данные могут принимать самые разнообразные значения, но они должны быть занесены в стандартные физические ячейки памяти, размер которых определяется конструкцией конкретного устройства. Поскольку для хранения различных данных может потребоваться различный объем памяти, используется метод последовательного размещения их в памяти. В этом случае одна единица данных может занимать одну или несколько последовательных физических ячеек памяти машины. Адресация к данным производится по адресу первой ячейки, но при этом общее число используемых ячеек должно быть точно известно. Так как только программист в состоянии предусмотреть возможные значения данных, используемых в программах, вопрос о распределении памяти для хранения информации ложится на его плечи. Конкретная организация памяти в задаче осуществляется за счет выбора программистом конкретного типа данных для хранения его информации.

Тип данных‑ способ внутреннего представления данных в памяти машины, учитывающий метод их кодирования в одной или нескольких ячейках памяти и предусматривающий возможности их расшифровки или преобразования.

Первые языки программирования содержали всего два типа данных – целые (Integer) и дробные (Real или Float или Single). С развитием языков программирования и расширением круга решаемых задач число используемых типов данных непрерывно росло. Так, для обеспечения требуемой точности и диапазона вычислений были введены соответственно для целых и дробных чисел типы Long и Double. Потребность в хранении текстовой информации привела к появлению типа данных Byte (в терминологии VBA), позволяющего наиболее экономно расходовать память ЭВМ (современные таблицы кодировки символов используют диапазон кодов от 0 до 255). Задачи, связанные с анализом и составлением текстовых сообщений, стали поддерживаться типом данных String. Для обеспечения возможностей ссылки на различные участки памяти был предложен специальный тип данных, называемый указателем (Object). В случае использования указателя в памяти хранится адрес ячейки памяти, содержащей интересующие нас данные или коды программы. Кроме этого, программисту предоставили возможность самому создавать интересующий его тип данных. Необходимость выполнения вычислений с датами и временем породила свой специальный тип данных Date. Особые условия выполнения вычислений с деньгами заставили добавить в перечень специальный тип Currency. Наконец, для упрощения начального ввода данных в клетки электронных таблиц Excel был разработан специальный тип данных Variant, позволяющий автоматически распознавать и обрабатывать числа и строки.

Илон Маск рекомендует:  Что такое код ncurses_insdelln

Готовясь к написанию программы, программист обязан задуматься над вопросом: какие значения могут принимать данные его программы? Ответив на этот вопрос, программист подбирает удобный ему тип данных из числа стандартных или создает свой. При этом приходится принимать во внимание следующее обстоятельство: использование стандартных типов данных существенно упрощает процесс создания программы, поскольку в языке программирования заложены возможности действий с этими данными и их преобразование из типа в тип. Мы предполагаем, что вы знакомы с типами данных Excel, задаваемыми ячейкам командой Формат, Ячейки вкладка Число окно Числовые форматы. В языке VBA существуют типы данных, приведенные в табл. 2

Пример 2. В программе, предназначенной для расчета начисления заработной платы рис. 1. для хранения номера в списке (если он будет добавлен в таблицу) можно выбрать тип данных Integer, для хранения фамилий сотрудников тип данных String. Ставка заработной платы и величина начисленного налога может быть описана типом данных Currency, а ставка налога типом данных Single. Кроме этого, например можно создать свой тип данных (Type), в который входят фамилия, начисленная сумма, сумма уплачиваемого налога и сумма к выдаче как самостоятельная единица хранимых в памяти данных.

Поскольку физически данные программы оказываются содержимым конкретных ячеек памяти машины, для их отыскания достаточно знать адрес первой ячейки, связанной с данными, и по типу данных определить общее число используемых для хранения элементарных ячеек. Такой подход имел место на самой ранней стадии программирования и оказался крайне неудобным из-за отсутствия наглядности в записи программы. Действительно, если память современной машины содержит несколько десятков, а то и сотен миллионов ячеек памяти, то обращение к ним по номерам было бы крайне неразумным. Уже первые трансляторы использовали прием, основанный на использовании так называемых идентификаторов.

Идентификаторомназывается символическое имя ячейки памяти. Каждый язык программирования содержит свои правила составления таких имен, общим является то, что программист вправе сам придумать имя, что позволяет ему сохранить в нем смысловое значение. В языке VBA имеются следующие ограничения на имена:

· Длина имени не должна превышать 255 символов.

· Имя должно начинаться с буквы.

· Имя не может содержать точек и символов %, &, !, #, @, $.

· Буквы рассматриваются инвариантно по отношению к регистру, то есть имя Aa и aA есть одно и то же имя.

· Совпадения имен идентификаторов с так называемыми ключевыми словами не допускается.


Ключевые слова- набор специальных слов, написанных символами латыни и имеющих определенный смысл с тоски зрения конструкций языка программирования. Ключевыми словами обозначаются, в частности, операторы языка и встроенные функции языка.

Пример 3. Возможные варианты идентификаторов языка VBA: I, j, Name, Переменная, Результат_вычислений. Еще варианты записи идентификаторов: A%, B&, C!, D#, E@, F$. В этом случае символы %, &, !, #, @, $ не входят в состав идентификатора и используются в качестве специального признака типа данных (смотри табл. 2).

Программист может вводить переменные в текст программы на VBA по мере их необходимости, применяя явное или неявное (по умолчанию) их объявление. В последнем случае переменная просто начинает использоваться в тексте. При первом ее появлении компилятор (интерпретатор) заносит новое имя в таблицу и закрепляет за ним определенный адрес и тип данных (в VBA – Variant).

Хотя возможность объявления переменных по умолчанию предусмотрена разработчиками языка, она представляется крайне нежелательной. Текст программы сам по себе представляет документ, в котором содержится исчерпывающая информация о ее работе, в том числе и о типах используемых данных. Введение переменных по умолчанию приводит к затруднениям при изучении программы и, как следствие, к ошибкам. Поэтому рекомендуется всегда явно определять переменные с помощью оператора Dimс указанием типа и задавать специальный режим принудительного объявления переменных программы помещенной в начале текста модуля инструкцией Option Explicit.

Пример 4. Явное объявление переменной:

Dim I As Integer, Name, j As Integer, Переменная As Integer, GGG As Integer

Обратите внимание на то, что если вы не указываете явно тип переменной, то по умолчанию она имеет тип Variant. Так, в рассмотренном выше примере такой тип имеет переменная Name.

Примечание. Интегрированная среда разработки VBA в окне редактора кодов предлагает в качестве сервиса возможность выбора одного из существующих типов данных из автоматически раскрывающегося списка. Так, после набора ключевого слова Dim, указания идентификатора переменной и набора ключевого слова As автоматически открывается список возможных значений (в данном случае типов данных). Перемещение по списку может осуществляться с помощью маркеров или путем ввода символов с клавиатуры. После того, как требуемое значение в списке установлено, оно может быть перенесено в текст программы клавишей Tab или в результате двойного клика клавишей мышки. Этой возможностью удобно пользоваться для избежания грамматических ошибок при наборе текста программы.

Рассмотренные выше примеры объявления переменных предусматривали создание одиночных констант или переменных, обращение к которым осуществляется только по имени. Практика программирования широко использует переменные, обращение к которым ведется как по имени, так и по номеру. В этом случае можно говорить о создании переменных табличного типа, когда обращение к данным ведется по имени и номеру (индексу) внутри этого имени. Такие переменные обычно называются массивами. Массив‑ последовательно упорядоченные в памяти данные одного типа.

Таблица 2.Типы данных языка VBA

Тип данных Размер (байт) Служебный символ Диапазон значений
Byte (байт) От 0 до 255
Boolean (логический) True или False
Integer (целые) % От -32768 до 32767
Long (длинное целое) & От -2147483648 до 2147483647
Single (плавающее обычной точности) ! От -3,402823E38 до -1,401298Е-45 и от 1,401298Е-45 до 3,402823E38
Double (плавающее двойной точности) # От -1,79769313486231Е308 до -4,94065645841247Е-324 и от 4,94065645841247Е-324 до 1,79769313486231Е308
Currency (денежный) @ От -922337203685477,5808 до 922337203685477,5808
Decimal (масштабируемое целое) +/-7922816251426433759353950335 и 28 знаков после запятой. Минимальное отличное от нуля значение имеет вид +/-0,0000000000000000000000000001
Date (время и дата) От 1 января 100 г. до 31 декабря 9999 г.
Object (объект) Любой указатель объекта
String (строка переменной длины) 10+ длина строки От 0 до приблизительно двух миллиардов
String (строка постоянной длины) Длина строки $ От 1 до 65400
Variant (числовые подтипы) От -1,79769313486232Е308 до -4,94065645841247Е-324 и от 4,94065645841247Е-324 до 1,79769313486232Е308
Variant (строковые подтипы) 22+ длина строки От 0 до приблизительно двух миллиардов
Type (определяемый пользователем) Определяется элементами типа Диапазон каждого элемента определяется его типом данных

Если мы хотим воспользоваться массивом, то мы должны его явно объявить. В дополнение к предыдущему объявление предусматривает еще и задание диапазона изменения номера элемента массива. По умолчанию считается, что минимальное значение номера (базовый индекс) равно нулю, а максимальное задается при объявлении. При необходимости (например, из соображений совместимости со старыми версиями языка) значение базового индекса 0 может быть изменено на 1 инструкцией Option Base 1. Другим способом изменения базового индекса конкретного массива является использование в объявлении ключевого слова To. Минимальное значение индекса указывается до слова To, а максимальное после. В некоторых случаях, например для хранения таблиц, оказывается удобным использовать двумерные массивы. В этом случае при объявлении через запятую описываются оба индекса. Возможно и применение массивов и большей размерности.

Пример 5. Объявления массивов:

Объявляется одномерный массив из 26 элементов. Начальный (базовый) индекс принят по умолчанию равным 0.

Dim ZZ(3,10) As Single

Объявляется двумерный массив ZZ типа Single, первый индекс которого меняется в диапазоне от 0 до 3, а второй в диапазоне от 0 до 10.

Dim SS(-3 To 3,1 To 10) As Integer

Переопределение базовых индексов с помощью явного указания нижних и верхних границ номеров элементов массива с использованием ключевого слова To.

Для обращения к ячейке памяти или элементу массива достаточно в тексте программы использовать соответствующий идентификатор (в случае массива с номером элемента, указанным в скобках). Важной особенностью систем программирования является то обстоятельство, что в качестве номера элемента массива может выступать не только константа, но и другая переменная, заданная своим идентификатором. Заметим, что недостатком рассмотренного приема является относительно высокая вероятность возникновения ошибки программирования связанной с выходом индекса (номера элемента) за границы массива. Программная среда VBA автоматически локализует такую ситуацию, выдавая соответствующее диагностическое сообщение.

Пример 6. Обращение к элементу массива в тексте программы с явным указанием номеров элементов: SS(-2,5).

Если переменная Name содержит число –2, а ячейка Переменная число 5, то обращение SS(Name, Переменная) полностью эквивалентно предыдущему.

Если в процессе предыдущих вычислений переменная Name примет значение –4, а мы попытаемся выполнить SS(Name, Переменная), то произойдет обращение к несуществующему элементу массива и возникнет ошибка выхода индекса за границы массива.

Массивы удобно использовать при программировании однотипных действий с ячейками памяти. В качестве примера рассмотрим задачу расчета начисления заработной платы (рис. 1). Поскольку исходные данные и результаты промежуточных вычислений должны храниться в памяти ЭВМ, в процессе программирования решения задачи на VBA приходится использовать идентификаторы. Заметим, что обычный идентификатор в этом случае не очень удобен. Действительно, хотя возможно введение в текст программы обычной переменной вида Налог_Трофимова_Л_А, создаваемая программа может быть в этом случае использована только для расчетов налога, уплачиваемого именно Л.А. Трофимовой. Если мы хотим запрограммировать вычисления для другого лица, то нам придется вводить другой идентификатор. Подобные действия ведут к изменению текста исходной программы и крайне нежелательны на практике. Конечно, мы можем ввести идентификаторы обычных переменных вида Налог_запись_2, однако и в этом случае мы должны будем индивидуально описать последовательность манипуляций с ячейками памяти для каждого сотрудника, включенного в список. Для нашего примера это вполне возможно, но реальный список может состоять, например, из 100 фамилий.

Кроме всего прочего, каждый раз при изменении количества сотрудников мы должны корректировать объявления переменных и, возможно, делать добавления в текст программы. Программирование существенно упростится, если ввести в рассмотрение массивы данных, имеющие смысл Начислено(1 To 4), Налог(1 To 4), К_выдаче(1 To 4) и рассматривать их элементы с одинаковыми номерами как записи, относящиеся к сотруднику, имеющему соответствующий идентификационный номер. На первый взгляд этот способ ничем существенным не отличается от использования идентификаторов одиночных переменных с номерами, однако если вспомнить, что существует возможность обращения к элементу массива с использованием идентификатора другой переменной, то можно рассматриваемую задачу попытаться описать и в общем виде.

Пример 7. В общем виде выражение для вычисления величины суммы к выдаче для каждого сотрудника может быть записано как:

К_выдаче(i) = Начислено(i) – Налог(i)

Здесь символом = обозначена операция присваивания результата вычислений в правой части оператора ячейке, указанной в левой части. Во время выполнения этой операции старое содержимое ячейки К_выдаче(i) теряется и она получает новое значение. В то же время символ – есть символ операции вычитания.

Если организовать повторения вычислений по этой формуле столько раз, сколько сотрудников имеется в списке для последовательно изменяющихся значений индекса i, то рассматриваемая задача может быть решена заметно проще, чем в случае объявления одиночных переменных.

Иногда приходится создавать массивы, размер которых невозможно определить на этапе компиляции программы. В нашем примере нам может быть неизвестно общее число сотрудников, для которых должна быть начислена зарплата. Конечно, можно объявить массивы с запасом, так, чтобы номер максимального элемента массива был заведомо большим максимально возможного числа сотрудников, допустим 100 человек. Однако такой прием приводит к нерациональному распределению памяти. Альтернативой является метод динамического объявления размера массива. В этом случае конкретный размер массива вычисляется в процессе выполнения программы и память для хранения данных отводится тоже во время выполнения. Чтобы воспользоваться этим методом, необходимо первоначально объявить массив без указания его размеров, а затем воспользоваться инструкцией ReDim. Менять границы изменения индекса массива можно сколь угодно много раз. Если массив больше не требуется в программе, память, занимаемая им, может быть освобождена с помощью инструкции Erase Начислено.

Пример 8.

Dim Начислено() As Currency, i As Integer

ReDim Начислено(1 To i)

МассивНачислено() первоначально был объявлен как массив неопределенной длины. Инструкция ReDim изменила массив, причем память под него была отведена в момент выполнения программы.

Очень часто при программировании возникает необходимость создания новых типов данных, вид которых определяется конкретной задачей. Так, например, программируя задачу, представленную на рис. 1, обратим внимание на то обстоятельство, что информация, размещенная в этой таблице, имеет одинаковую структуру по строкам. Более того, даже программируя соответствующую колонку таблицы в виде массива, программист обязан следить за тем, чтобы номера элементов разных массивов, относящихся к одному сотруднику, не отличались бы один от другого. Из соображений надежности программирования оказывается удобным рассматривать все, относящееся к одному сотруднику, в виде целой неделимой записи, содержащей соответственно фамилию, начисленную сумму, рассчитанный налог и сумму к выдаче. На самом деле речь идет о создании нового типа данных, определенного пользователем и включающего в себя относящиеся к записи поля. Структура данных‑ объединение под одним именем различных компонентов с индивидуальными именами и типами, называемых членами структуры.

Признаком структуры данных, как правило, является символ точки в ее идентификаторе, причем имя структуры записывается до точки, а имя ее компонента (члена) после точки. В языке VBA структуры данных можно создавать на основе типов данных, определяемым пользователем. Задание типа данных только описывает структуру, информация о которой размещается в общей области программы VBA. Для ее непосредственного объявления и резервирования ячеек памяти под хранение данных требуется явно объявить переменную в конкретном модуле.

Пример 9. Создание пользовательского типа данных, представляющего собой одну строку записи рис. 1.

Фамилия_И_О As String

Начислено_Ведомость As Currency

Налог_Ведомость As Currency

К_выдаче_Ведомость As Currency

Dim Запись1 As Запись_Ведомости

Запись значений в элементы структуры с использованием оператора присваивания:

Запись1.Фамилия_И_О = «Иванов В.Н.»

Запись1.Налог_Ведомость = Запись1.Начислено_Ведомость * 0.12

Здесь символом * обозначена операция умножения.

Объявление массива структур:

Dim Ведомость(1 To 4) As Запись_Ведомости

Соответствующие обращения к элементам массива и членам структуры будут иметь вид:

Ведомость(1).Фамилия_И_О = «Иванов В.Н.»

Ведомость(2).Фамилия_И_О = «Трофимова Л.А.

Примечание. Интегрированная среда разработки VBA в окне редактора кодов предлагает в качестве сервиса возможность конкретного выбора типа данных, определенных пользователем, из автоматически раскрывающегося списка. Если структура данных ранее была объявлена и выполнена компиляция проекта, после набора символа точки автоматически открывается список возможных имен полей структуры. Этой возможностью удобно пользоваться для избежания синтаксических ошибок при наборе текста программы.


Отдельную проблему представляет прямая и обратная передача данных из таблицы Excel в ячейки памяти, объявленные в программе, написанной на VBA. Автоматически созданный макроснепосредственно манипулирует с ячейками таблицы, используя стили ссылки на ячейки в Excel: A1 и R1C1. Конечно, такой прием может быть использован и в рабочей программе, однако в этом случае ее модификация и использование существенно затруднены. Гораздо предпочтительнее использовать свойство Cells() стандартного объекта Excel Range. Сам объект представляет собой ячейку, столбец, строку или выделенный диапазон листа Excel. Свойство Cells() позволяет непосредственно обратиться к объекту Excel по номеру строки и колонки. Поскольку это свойство установлено по умолчанию для рабочего листа Excel, то его можно использовать без дополнительных указаний.

Свойство Cells()позволяет обратиться к ячейке рабочего листа задав номер строки и колонки. Если запись свойства стоит слева от символа равенства (оператор присваивания), то производится запись данных в ячейку таблицы, если справа, то считывание значения из ячейки таблицы. Кроме собственно записи данных свойство Cells() в сочетании со свойствами других объектов (Font, Color и т.п.) позволяет задавать параметры шрифта, его цвет, фон и так далее. Для изучения этих возможностей целесообразно ознакомиться с описанием соответствующих свойств и объектов в литературе, воспользоваться Help-системой или, что вероятно проще всего, запустить режим записи макроса в Ехсеl, выполнить, например, установку цвета и изучить текст полученного макроса.

Илон Маск рекомендует:  FileSearch - Функция Delphi

Пример 10. Использование свойства Cells() для считывания данных в переменную VBA и возврата значения в Excel и установки нового цвета шрифта. Используется тот факт, что положение и количество ячеек в таблице рис. 1. известно. Дополнительно в программе используется символ комментария ‘ и комбинация символов «пробел»_ ( _) для обозначения продолжения длинной строки

Dim Начислено(1 To 4) As Currency, Налог(1 To 4) As Currency, _

К_Выдаче(1 To 4) As Currency, i As Integer

Начислено(i) = Cells(i + 1, 2) ‘В первую ячейке массива Начислено записывается содержимое

‘второй строки и второй колонки исходной таблицы Excel

Cells(i + 1, 2).Font.ColorIndex = 7 ‘В ячейке устанавливается новый цвет шрифта

Налог(i) = Начислено(i) * 0.12 ‘Рассчитывается значение налога и запоминается

‘в соответствующей ячейке

Cells(i + 1, 3) = Налог(i) ‘Значение налога возвращается в таблицу Excel

К_Выдаче(i) = Начислено(i) — Налог(i) ‘Рассчитывается значение к выдаче

‘и запоминается в соответствующей ячейке

Cells(i + 1, 4) = К_Выдаче(i) ‘Значение к выдаче возвращается в таблицу Excel

i = i + 1 ‘Переход к следующей записи

Начислено(i) = Cells(i + 1, 2)

Cells(i + 1, 2).Font.ColorIndex = 7

Налог(i) = Начислено(i) * 0.12

Cells(i + 1, 3) = Налог(i)

К_Выдаче(i) = Начислено(i) — Налог(i)

Cells(i + 1, 4) = К_Выдаче(i)

i = i + 1 ‘Переход к следующей записи

Начислено(i) = Cells(i + 1, 2)

Cells(i + 1, 2).Font.ColorIndex = 7

Налог(i) = Начислено(i) * 0.12

Cells(i + 1, 3) = Налог(i)

К_Выдаче(i) = Начислено(i) — Налог(i)

Cells(i + 1, 4) = К_Выдаче(i)

i = i + 1 ‘Переход к следующей записи

Начислено(i) = Cells(i + 1, 2)

Cells(i + 1, 2).Font.ColorIndex = 7

Налог(i) = Начислено(i) * 0.12

Cells(i + 1, 3) = Налог(i)

К_Выдаче(i) = Начислено(i) — Налог(i)

Cells(i + 1, 4) = К_Выдаче(i)

Задание

Используйте согласованный с преподавателем вариант задания (табл. 1), выполненную на его основе таблицу Excel и созданный вами макрос. Модифицируйте созданный вами макрос и напишите новую программу так, чтобы ее основные вычисления производились с переменными VBA. При этом исходные данные первоначально должны быть считаны из таблицы, а результаты вычислений возвращены в нее.

Обмен данными между Excel и MySQL.

Данные таблиц Microsoft Excel достаточно часто используются для формирования небольших по объему, но достаточно информативных баз данных. И как бы не старались разработчики, программисты, менеджеры внедрить новый продвинутый программный продукт (даже бесплатный) в рабочий процесс, довольно часто всё остается на своих местах. Не знаю как Вам, уважаемые читатели, а мне за почти пять лет работы на предприятии приходилось очень часто иметь дело именно с такими микро-БД будь то табличный отчёт по работе отдела или данные мониторинга прилегающих к предприятию территорий, включающий в себя не только таблицы на тысячи строк, но и графики, диаграммы, расчёты и т.д. Я не могу сказать, что такой метод предоставления данных (в Excel) не верный, напротив, данные предоставленые в Microsoft Excel или альтернативном OpenOffice информативны и понятны, а если добавить в предоставление данных диаграммы, то такому отчёту вообще цены нет. Но, акк бы красиво не выглядели небольшие БД в Excel, а если предприятию нужна централизованная обработка и хранение данных, то одним Excel тут врядли обойдешься, особенно, если предприятие крупное. Буквально неделю назад наблюдал картину, как сотрудник отдела промышленной безопасности со смиренным видом ручками передавал данные с листа Excel в базу MySQL. Скопировал чиселку, открыл программку-клиент, вставил чиселку, нажал «Отправить» и так до посинения. Бред? По-моему, полнейший. Так и с ума сойти не долго. Но я слава Богу в том отделе не работаю и навязывать свою точку зрения на счёт такой работы не стал. Пусть себе копипастят на здоровье. Однако вопрос этот засел в голове на долго: каким образом можно автоматизировать передачу данных из Excel в базу данных MySQL? И ответ на этот вопрос нашелся.

Вообще вариантов взаимодейсвтия Excel и MySQL нашлось два: использовать драйвер ODBC или воспользоваться технологией DataSnap. Сегодня рассмотрим вариант с ODBC.
Для демонстрации работы программы нам потребуется сервер MySQL. Обычно для таких случаев я использую джентельменский набор для веб-разработчиков Denwer. Поэтому, рекомендую и Вам его скачать, перейдя по этой ссылке. Я скачал и установил Denwer с PHP 5.2. В принципе это не важно — главное, чтоб был MySQL.
Как устанавливать Denwer и настраивать MySQL я не буду, т.к. все подробные инструкции есть на официальном сайте, а перейду сразу к созданию базы данных для тестирования. Итак, запускаем phpMyAdmin и создаем новую базу данных с названием demobase. Для примера создадим БД по статистике ключевых слов (не важно каких и для кого), база данных будет содержать всего одну таблицу, созданную с помощью следующего SQL-запроса:

Здесь мы создали таблицу keywords и сделали две записи в таблицу по двум ключевым словам. Теперь откроем MS Excel и создадим таблицу с точно такими же полями, но содержащую данные по другим ключевым словам:

Файл Excel сохраним с названием mykeywords.xls.
Теперь надо установить драйвер ODBC. Для этого можно воспользоваться утилитой mysql-connector-odbc-5.1.x-win32. После того как драйвер MySQL установлен, создаем DSN, в котором указываем информацию для доступа к таблице keywords в базе данных demobase. Для создания DSN воспользуемся стандартными средствами Windows XP:

Запускаем ODBC Data Source Administrator и в появившемся окне жмем кнопку Add или «Добавить» (для русских версий Windows):

Теперь, в появившемся окне записываем все данные по источнику данных, как показано на рисунке:

По умолчанию при установке Denwer’а пароль для root пустой, поэтому поле password оставляем пустым. После внесения всех данных нажмите на кнопку «Test» и, если все настроено верно, то должно появиться окно сообщения «Connection successful». Теперь жмем «Ok» и в исходном окне Администратора видим новую запись:

Теперь приступим непосредственно к программированию в Delphi. Создадим следующее приложение:

В левой части окна размещаются компоненты для доступа к файлу Excel, в правой — к MySQL. Представленные на рисунке компоненты:

  • DBGrid (страница Data Controls) — 2 шт.
  • ExcelDS и MySQLDS — компонены DataSource со страницы Data Access
  • ExcelQuery и MySQLQuery — 2 компонента ADOQuery со страницы dbGO.

Настройка компонентов для доступа к Microsoft Excel выглядит следующим образом. У первой таблицы DBGrid в свойстве DataSource указываем ExcelDS, тот же ExcelDS указываем и у DBNavigator’а. У ExcelDS в свойстве DataSet указываем ExcelQuery. Аналогичным образом настраиваем компоненты в правой части (относящиеся к MySQL).

Теперь необходимо настроить свойство ConnectionString у компонента ExcelQuery. Выделяем строку со свойством в Object Inspector’е и конфигурируем строку подключения:

Провайдер связи JET 4.0. может использоваться для доступа к различным данным, в том числе к Microsoft Excel или Access. После того как поставщик данных выбран, жмем «Далее» и делаем дополнительные настройки:


Если теперь проверить подключение, то получим сообщение с красным крестом и следующим содержанием: «Не выполнена проверка подключения из-за ошибки при инициализации…» и т.д. и т.п. Не паникуем, а учим JET 4.0. понимать нас с полуслова. Добавляем в настройки дополниельный параметр. Для этого переходим на вкладку «Все» и изменяем поле Extended Properties:

Теперь это свойство указывает на то, что мы будем работать с документом Excel в формате Excel97-2003. Также на вкладке «Дополнительно» укажем тип доступа ReadWrite. Теперь ещё раз проверяем подключение, радуемся:

Как видите, пока ничего сверхъестественного не происходит — все просто и давным-давно известно, но только чуть-чуть подзабыто ;) Двигаемся дальше. Теперь настраиваем доступ к MySQL — свойство ConnectionString у MySQLQuery. В окне «Свойства связи с данными» указываем Microsoft OLE DB Provider for ODBC Drivers.

На вкладке «Подключение» указываем ранее созданные MySQL_Connection, в поле «Пользователь» вписываем root и ставим галку напротив «Пустой пароль«:

Теперь, после успешного тестирования соединения, переходим к свойству SQL и записываем в него запрос следующего вида:

SELECT * FROM keywords

Теперь можете выставить к MySQLQuery свойство Active в True и увидите, что в DBGrid вывелись записи ранее внесенные в базу данных MySQL.

Остается самая малость — вытащить из листа Excel данные и скопировать их в базу MySQL. А как это сделать? Очень просто. С помощью ExcelQuery манипулировать данными в Excel также просто, как и с любой другой дазой данных. Вот запрос на получение данных из файла mykeywords.xls.

То есть в качестве таблицы БД выступает отдельный лист. Запишите этот запрос в свойство SQL компонента ExcelQuery и выставьте свойство Active в значение True. У меня получилась следующая картинка:

Теперь напришем следующий обработчик у кнопки (см. рисунок — кнопка «Скопировать текущий элемент»):

Вот так запросто можно копировать данные из Excel в MySQL. Здесь я показал Вам самый простейший пример, наподобие ручного копипаста данных. Но, если проявить совсем чуть-чуть смекалки и умений работы с базами данных, то можно запросто перекидывать из Excel листы целиком или хотябы по нескольку записей за раз.
Аналогичным образом, кстати, можно обрабатывать и листы Excel 2007 в Delphi.

1С и Excel. таблица функций Excel доступных из 1С

Многие знают, что Excel гораздо старше 1С. На мой взгляд, это очень успешный продукт, и нареканий о нем я не слышал. Excel прост и универсален. Он способен выполнять не только простые арифметические операции, но и сложные вычисления, построение графиков и т.п. Знаю примеры, когда организации до перехода на 1С вели часть бухгалтерии в Excel. Многие и сейчас параллельно используют две программы. В этой статье рассмотрим способы обмена данными между 1С и Excel.

Таблицы 1С и Excel.

Многие пользователи для удобства работы сохраняют таблицы 1С (печатные формы) в формате Excel (*.xls). После чего в полученном файле делают различные группировки, сортировки, вычисления и т.п. Связано это с тем, что в таблицах 1С нет такого огромного функционала как в таблицах Excel. Но в версии 1С 8.0 есть нововведения, делающих работу с таблицами более комфортной.

Замечание: В Excel разделителем дробной части считается символ «,». Поэтому перед сохранением таблицы 1С в формате Excel замените в ней другой разделитель (например «.») на «,». Иначе в Excel с этими числами не удастся произвести вычисления, или они вообще не будут отображаться как числа. Например, в таблице 1С «15.2» отобразится в Excel как «15.фев».

Получение данных из Excel.

Доступ из 1С к Excel производится посредством OLE. Например, код

Попытка
Эксель = СоздатьОбъект( «Excel.Application» );
Исключение

Сообщить(ОписаниеОшибки() + » Программа Exсel не установлена на данном компьютере!» );
Возврат;

позволит нам получить доступ через переменную «Эксель» к запущенному приложению Excel. А далее уже можно получить доступ к книге (файлу), листу и ячейке с данными. Далее примеры кода.

Открытие книги (файла):

Книга = Эксель . WorkBooks . Open ( ПутьКФайлу );

ПутьКФайлу — полный путь к файлу книги Excel.

Выбор листа книги для работы с ним:

Лист = Книга . WorkSheets ( НомерЛиста );

Лист = Книга . WorkSheets ( ИмяЛиста );

НомерЛиста — номер листа в книге, ИмяЛиста — имя листа в книге.

Получение значения ячейки листа:

Значение = Лист . Cells ( НомерСтроки , НомерКолонки ). Value ;

НомерСтроки, НомерКолонки — номер строки и номер колонки, на пересечении которых находится ячейка.

Важно: не забывайте поле выполнения нужных действий добавлять код Эксель . Quit (); , иначе запущенный процесс останется незавершенным и будет занимать память и процессор компьютера.

Вывод данных в Excel.

Для вывода (выгрузки) данных в Excel необходимо либо открыть существующую книгу, либо

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

Попытка
Эксель = СоздатьОбъект( «Excel.Application» );
Исключение

Сообщить(ОписаниеОшибки() + » Программа Exсel не установлена на данном компьютере!» );
Возврат;

КонецПопытки;
Книга = Эксель . WorkBooks . Add ();

Так как при создании книги в Excel автоматически создаются листы (Сервис->Параметры->Общие->Листов в новой книге), то нужно лишь произвести выбор листа, с которым будет вестись работа:

Лист = Книга . WorkSheets ( НомерЛиста );

либо добавить в книгу новый лист, если необходимо:
Лист = Книга . Sheets . Add ();

Следующим шагом будет установка значения ячейки:
Лист . Cells ( НомерСтроки , НомерКолонки ). Value = Значение ;

НомерСтроки, НомерКолонки — номер строки и номер колонки, на пересечении которых находится ячейка.

И в конце нужно произвести запись созданной книги:

Попытка
Книга . SaveAs ( ПутьКФайлу );
Исключение

Сообщить(ОписаниеОшибки() + » Файл не сохранен!» );
Возврат;

ПутьКФайлу — полный путь к файлу книги Excel (включая имя).

Важно: не забывайте, что в имени файлов не должно содержаться символов \ / : * ? » > Часто используемые методы для чтения/установки значений в Excel.

Эксель . Visible = Видимость ;


Книга = Эксель . WorkBooks . Add ();

Книга . SaveAs ( ИмяФайла );

Лист = Книга . WorkSheets . Add ();

Книга = Эксель . WorkBooks . Open ( ИмяФайла );

Лист = Книга . WorkSheets ( НомерЛиста );

Лист . Name = ИмяЛиста ;

Лист . PageSetup . Zoom = Масштаб ;

Лист . PageSetup . Orientation = Ориентация ;

Лист . PageSetup . LeftMargin = Эксель . CentimetersToPoints ( Сантиметры );

Лист . PageSetup . TopMargin =

Эксель . CentimetersToPoints ( Сантиметры );

Лист . PageSetup . RightMargin =

Эксель . CentimetersToPoints ( Сантиметры );

Лист . PageSetup . BottomMargin =

Эксель . CentimetersToPoints ( Сантиметры );

Лист . Columns ( НомерКолонки ) . ColumnWidth = Ширина ;

Лист . Cells ( НомерСтроки , НомерКолонки ) . Value = Значение ;

Лист . Cells ( НомерСтроки , НомерКолонки ) . Font . Name = ИмяШрифта ;

Лист . Cells ( НомерСтроки , НомерКолонки ) . Font . Size = РазмерШрифта ;

Лист . Cells ( НомерСтроки , НомерКолонки ) . Font . Bold = Жирный ;

Лист . Cells ( НомерСтроки , НомерКолонки ) . Font . Italic = Курсив ;

Лист . Cells ( НомерСтроки , НомерКолонки ) . Font . Underline =

Лист . Cells ( НомерСтроки , НомерКолонки ) . NumberFormat =

Лист . Cells ( НомерСтроки , НомерКолонки ) . Borders . Linestyle =

Обмен данными с excel

Обмен данными между Excel и MySQL

Вряд ли мы откроем секрет, если скажем, что MySQL является на сегодняшний день одной из наиболее доступных реляционных СУБД. Она бесплатна, стабильна в работе и ко всему прочему отличается неплохим быстродействием. Не случайно эта система управления базами данных пользуется огромной популярностью у разработчиков веб-приложений, построенных на основе клиент-серверной технологии.
Конечно, время никогда не проходит даром, в том числе и для MySQL. Эволюционировав до 5й версии, она значительно возмужала и обзавелась отсутствующими ранее функциями (поддержкой триггеров, вложенных запросов и др.), но… «У «Жигулей» тоже четыре колеса, фары, руль и прочие атрибуты, но это не Mercedes. Точно так же и MySQL — это не Oracle», — процитировал мне знакомый программист, пытаясь объяснить разницу между существующими платформами для хранения данных. Глубокомысленное высказывание, ничего не скажешь. Ладно, долой философию, займемся делом, а именно — обменом данных в связке «Microsoft Excel — MySQL».

Матерым программистам баз данных наверняка по зубам эта операция, но мы облегчим свою участь при помощи специальной надстройки к табличному редактору Excel с длиннющим названием Excel to MySQL Import, Export & Convert 1.1 от компании Sobolsoft. Размер утилиты — 5,7 Мб, стоимость — 20 долларов США.

Установка приложения проходит без шума и пыли, единственное, необходимо понизить уровень безопасности Excel (Сервис -> Макрос -> Безопасность), разрешив ему выполнять макросы.

Перед началом работы нужно подключить базу данных при помощи специального мастера Setup MySQL Database Connection и установить соединение. А далее все как по маслу: при желании можно экспортировать выбранные ячейки в таблицу MySQL, либо, напротив, импортировать нужную информацию из базы данных. Естественно, без хотя бы минимальных знаний основ языка структурированных запросов SQL здесь не обойтись, но это не страшно — ведь документации навалом.

Что до области применения этой утилитки от Sobolsoft, то она воистину многогранна, начиная от использования в качестве удобного инструмента для операторов баз данных и заканчивая эксплуатацией в собственных программных разработках. На этой оптимистической ноте и поставим точку в нашей маленькой заметке.

Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL
Эксель = СоздатьОбъект( «Excel.Application» ); Получение доступа к приложению Excel.
0 — Excel не виден, 1 — виден.
Создание новой книги (файла) Excel.
Сохранение книги Excel.
Добавление нового листа в книгу.
Открытие существующей книги (файла) Excel.
Установка листа в качестве рабочего с номером НомерЛиста.
Задание рабочему листу имени ИмяЛиста
Задание параметра страницы «Масштаб» (от 10 до 400).
Ориентация: 1 — книжная, 2 — альбомная.
Задание левой границы (в сантиметрах).
Задание верхней границы (в сантиметрах).
Задание правой границы (в сантиметрах).
Задание нижней границы (в сантиметрах).
Задание ширины колонке.
Ввод данных в ячейку.
Установка шрифта в ячейке.
Установка размера шрифта в ячейке.
1 — жирный шрифт, 0 — нормальный.
1 — наклонный шрифт, 0 — нормальный.
2 — подчеркнутый, 1 — нет.
Установка формата данных ячейки.