Обмін даними між 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. Представлені на малюнку компоненти:



Налаштування компонентів для доступу до 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. У мене вийшла наступна картинка:



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






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>

*

*