Обмін даними між 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-запиту:





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 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 "е і конфігуруємо рядок підключення:



Тепер напрішем наступний обробник у кнопки (див. малюнок – кнопка "Копіювати поточний елемент"):





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.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*