(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

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

Источник: webdelphi

Данные таблиц 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-запроса:

1
2
3
create table keywords (number int, keyword varchar(50), count int);
insert into keywords VALUES (1, "Delphi", 2000);
insert into keywords VALUES (1, "Excel", 4000);

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

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

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

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

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

запись после успешной настройки драйвера mysql для odbc

Теперь приступим непосредственно к программированию в 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"е и конфигурируем строку подключения:

Настройка свойства ConnectionString компонента ADOQuery для доступа к Excelвыбор поставщика данных для Excel

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

настройка подключения к файлу Excel как к базе данных

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

дополнительные настройки Jet для доступа к Excel

Теперь это свойство указывает на то, что мы будем работать с документом 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.

SELECT * FROM [Лист1$]

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

доступ к листу Excel как к таблице базы данных

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
procedure TForm9.Button1Click(Sender: TObject);
var Number: integer;
    key: string;
    count:integer;
begin
MySQLQuery.Last; //переходим к последнему элементу
Number:=MySQLQuery.FieldByName('Number').AsInteger;
inc(Number);//наращиваем номер на 1
key:=ExcelQuery.FieldByName('keyword').AsString;
count:=ExcelQuery.FieldByName('count').AsInteger;
with MySQLQuery do
  begin
    SQL.Clear;
    SQL.Add('INSERT INTO keywords');
    SQL.Add('VALUES ('+IntToStr(Number)+',"'+key+'",'+IntToStr(count)+')');
    ExecSQL;
    Close;
    SQL.Clear;
    SQL.Add('SELECT * FROM keywords');
    Open;
  end;
end;

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

Ссылки по теме


 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Embarcadero »
Написать редактору 
 Рекомендовать » Дата публикации: 17.03.2010 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Delphi Professional Named User
Enterprise Connectors (1 Year term)
Oracle Database Personal Edition Named User Plus License
Delphi Professional Named User
IBM Domino Enterprise Server Processor Value Unit (PVU) License + SW Subscription & Support 12 Months
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
СУБД Oracle "с нуля"
Все о PHP и даже больше
Утиль - лучший бесплатный софт для Windows
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Играть в Книгу мертвых (1)
Тайные гробницы древнего Египта, богатства и забытые мумии фараонов - это все вы можете увидеть...
 
Пишу программы на заказ профессионально (3309)
Пишу программы на заказ на языках Pascal (численные методы, списки, деревья, прерывания) под...
 
Отличается ли ДрифтКазино от беттинга? (59)
Друзья, давно заметил, что на Дрифте уже несколько месяцев во всю рекламируется и предлагается...
 
Можно ли зарабатывать в интернете играя в казино ? (9)
Можно ли зарабатывать в интернете играя в казино ?
 
Ищу программиста для написания программы (77)
Ищу программиста ,владеющего Вижуал Бэйсик и программированием в Экселе, для написания...
 
 
 



    
rambler's top100 Rambler's Top100