Delphi в Интернет, блог Delphi программистов

Данные таблиц 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.

Делись!	Загружай!	Плюсуй!
	Отправить PDF на

Комментарии (7)

• homolibere пишет:
  15 Мар 2010 в 10:44 дп

  очень, очень не красиво смотрится «єту статью нагло сперли»… в статье ничего особо гениального нет. еще в институте такое воротил… слишком претензион0, и пафосно (касательно копирайтов на скринах). против вотермарки http://www.webdelphi.ru я ничего против не имею, но такое писать могут позволить себе только «варезники ацтойные» и те, кто думают, что эта статья тянет на нобелевку.
  что касательно статьи — кому-то может быть и полезно.

• Vlad пишет:
  15 Мар 2010 в 11:48 дп

  Если начали появляться такие надписи — значит был повод, чтобы их ставить. И я очень надеюсь, что эту надпись увидят те кому она непосредственно адресована. Согласен — не красиво, но красиво общаться с теми кто считает, что в Сети он БОГ и имеет право брать всё, что ему хочеться без спросу — надоело. Просишь по-человечески, без наездов — молчат, ставишь вот такую надпись — понимают и уже сами начинают просить. С волками жить — по-вольчьи выть

• Андрей пишет:
  23 Мар 2010 в 2:56 дп

  Почему не DBExpress? Всё-таки получше ODBC-драйвера будет… С ADO старался не работать. Потому как не очень удобно бывает и не слишком быстро. Но вообще, для небольших приложений очень даже ничего. Для начинающих неплохая статья. Очень важно уметь грамотно писать о простых вещах. Как бы ни ругали, к примеру, Фленова, я всё же считаю его книги отличным началам после школьного Паскалевского курса.

• Vlad пишет:
  23 Мар 2010 в 10:59 дп

  В целом пост и писался как раз для начинающих, т.к. даже более-менее опытным разработчикам вся эта «кухня» работы с ADO и Excel давно знакома.
  Флеонов по-моему один из лучших авторов в том плане, что с его книгами можно легко научиться работать в Pascal и Delphi. Ругают видимо «гуру программирования» :)

• den пишет:
  25 Мар 2010 в 6:59 пп

  не понятно зачем тут Delphi
  эти задачи прекрасно решаются на VBA

• Kalash пишет:
  12 Июл 2010 в 4:22 пп

  den, смешной ты:) представь, что ты пишешь программу на delphi (почему именно на делфях — личный выбор), пишешь уже больше полугода. В программе уже куча модулей, процедур, форм, отчетов и т.д. И тут на работе тебе говорят, что в состав нашего холдинга (или фирмы) входи фирма «ООО ****» и что у нее вся документация в excel и нужно все перегнать в уже существующую БД. Что ты возьмешь VBA напишешь им то что нужно и скажешь пользуйтесь так. Тут пойдут вопросы «почему не в программе?», «и что нам всегда так делать?» и т.д. Не лучше ли потратить 10-20 минут на создание класса импорта из excel и закрыть этот вопрос навсегда?

  У меня точно так же реализован импорт, только у меня нужно еще делать выборку и искать по базе совпадения по классу и виду добавляемых записей (база данных техники, класс техники, вид техники). Наткнулся на эту статью случайно. Ищу самый простой и быстрый способ генерации отчетов в Word и Excel. Такие как ты в den, без обид, предлагают создание шаблонов в excel с макросами и программно все это запускать. И как мне потом рассказывать секретарям «что такое макрос», если они не знают, что такое копи-паст….

• alekc пишет:
  01 Дек 2010 в 11:23 пп

  А мне понравилось. симпотно.

Ваш ответ