Обмен данными между 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-запроса:

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 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.

SELECT * FROM [Лист1$]

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

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

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.