Лекція 1. Семестр 2. Використання редактора таблиць Excell
В даній лекції буде описано як перекинути данні таблиці в ексель, це може бути актуально, наприклад, якщо потрібно влаштувати обмін даними між офісами компаній, або просто імпортувати в іншу програму так як файли електронних таблиць екселю є широко поширюваними, та підтримуються багатьма сторонніми розробниками.
Ми не будемо зберігати файл в форматі екселю, так як це досить тяжко і в принципі не є потрібним так як ексель має вбудований і досить гнучкий інтерфейс обміну даними із зовнішніми програмами.
І так розпочнемо, перше, що нам потрібно щоб на машині на якій безпосередньо буде відбуватися експорт був встановлений ексель. По друге, нам потрібно якимось чином його зв’язати з нашим додатком, а зв’язувати його ми будемо за допомогою COM інтерфейсу.
Вданій лекції буде описано взаємодію Microsoft Excel 2010, по великому рахунку, суттєвих змін в коді для нижчих (версія 2003-2007) або вищих (версія 2013) версій пакету – непотрібно, але, все-таки потрібно буде перевірити роботу програми.
Для того щоб встановити зв’язок з екселем нам потрібно додати в менеджері проекту (References) посилання (Add reference…) на СОМ об’єкт Microsoft Excel XX.X Object Library, де XX.X це версія екселю, який встановлено на комп’ютері.
В просторі імен (Using) потрібно додати два розширення using Microsoft.Office.Interop.Excel; та using Microsoft.Office.Core; перший – це безпосередньо інтерфейсний модуль додатку ексель, через який можливо викликати будь-яку його функцію. Другий – набір службових типів Microsoft Office, який є спільним для всіх додатків офісу і представляє лише оголошення структур.
Оголосимо дві змінні public Microsoft.Office.Interop.Excel.Application excelapp; public Microsoft.Office.Interop.Excel.Worksheet sheet; перша відповідає за саму програму ексель, а друга відповідатиме за робочу сторінку на яку ми будемо вставляти данні. В лабораторній роботі для скорочення буде використаний аліас, тут ми для наочності його використовувати не будемо.
excel = new Microsoft.Office.Interop.Excel.Application(); - встановить зв’язок з середовищем ексель, зв’язок буде не помітним для користувача, так як вікно не відобразиться на моніторі. Встановлюється один раз, при наступному виклику буде запущений ще один екземпляр екселю, тому будьте уважні.
excel.Visible = true; - покаже вікно екселю на моніторі, вікно перекриє наш додаток, тому показувати його слід тільки після того, як користувач ввів всі потрібні нам для роботи данні. Ще не потрібно показувати ексель одразу при відкритті тому, що 1) дані переносяться та форматуються по клітинкам, користувачу просто не потрібно спостерігати як послідовно заповнюється його документ і 2) на те, щоб відобразити послідовне форматування і наповнення документу затрачаються системні ресурси, тому процес займе довше часу.
excel.Workbooks.Add(); - додасть чисту робочу сторінку. Якщо як аргумент передати назву та абсолютний шлях до документа (наприклад @"с:\C#\book1.xlsx"), ексель його відкриє, що може бути корисним якщо ви підготовили шаблон документа і вам просто потрібно внести в нього данні. Абсолютний шлях вказується тому, що ваша програма запущена з одного місця на диску а ексель з іншого, тому ексель буде шукати відносне ім’я документу відносно свого робочого каталогу.
Далі, для зручності sheet = excelapp.Workbooks[1].Worksheets[1]; - запам’ятаємо посилання на робочу сторінку. З наведеного вгорі тексту видно, що одночасно можна працювати не лише з одною сторінкою.
sheet.Name = n; - задасть ім’я нової сторінки, змінна рядкового типу.
Для того, щоб завершити роботу ексель можна просто очистити контролюючі змінні таким чином:
sheet = null;
excel = null;
Якщо ж потрібно екстрено завершити ексель (без збереження данних). Потрібно використати таку послідовність керуючих процедур:
excel.DisplayAlerts = false;
excel.Quit();
Ексель мусить бути запущеним, якщо закрити ексель попередньо його не запустивши отримаємо помилку. Після цього потрібно все-одно очистити контролюючі змінні.
Так як ексель використовує своєрідну нумерацію сторінок (наприклад клітинка 2:2 в розумінні екселю має адресу В2). Тому потрібно це врахувати при адресуванні даних в експорті. Можна написати спеціальну процедуру переходу від однієї системи в іншу, цим ми будемо займатись у наступній лекції, де буде описано основні послідовності роботи і створення документів в екселі, зараз ми просто опишемо алгоритм переходу.
1. Потрібно оголосити як константу послідовність літер англійського алфавіту
2. Потрібно з’ясувати скільки літер буде в першій координаті одна чи дві. Для цього потрібно поділити число націло на 26 (кількість літер в англійському алфавіті) попередньо віднявши одиницю, якщо отримали 0 тоді просто підставляємо відповідну літеру з англійського алфавіту. Якщо ж більше 0, тоді індекс першої літери буде результат від ділення націло на 26, а індекс другої – остача від ділення на 26
3. До отриманих літер просто додаємо кількість рядків
4. Отриманий рядок і можна передавати як адресу клітинки
Функція передачі даних в клітинку має такий вигляд: sheet.Range[ адреса клітинки ].Value = текст ; - де адреса клітинки може містити як і адресу певної клітинки (наприклад А1), так і прямокутник виділений по вершинах (наприклад А1,А2,В1,В2 – в такому виді потрібно замість тексту передавати масив потрібних значень).
Текст може бути або просто рядком, або масивом із рядків.
Форматування тексту клітинки здійснюється таким чином:
sheet.Range[адреса клітинки].Font.Bold = bool;
sheet.Range[ адреса клітинки ].Font.Italic = bool;
sheet.Range[ адреса клітинки ].Font.Underline = bool;
sheet.Range[ адреса клітинки ].Font.Color =0xBBGGRR;
sheet.Range[ адреса клітинки ].Font.Size =int;
sheet.Range[ адреса клітинки ].Font.Name =string;
Де bool це логічне значення (true або false). Color – колір записаний в форматі цілочисельного значення еквівалентного кольору, будьте уважні так, як колір задається в шіснадцятковому форматі 0xBBGGRR – дві цифри синя компонента кольору, дві – зелена і останні дві – червона, (int) Size – розмір шрифту Name – ім’я шрифту (наприклад “Times New Roman”)
Масштабування клітинки здійснюється таким чином (звернути увагу потрібно на те, що довжина і ширина клітинки задається в мірах довжини екселя, може містити дробову частину, також одиниці ширини і висоти різні)
sheet.Range[“ адреса клітинки “].RowHeight =Height;
sheet.Range[ “адреса клітинки” ].ColumnWidth =Width;
Для того, щоб додати малюнок до екселю (буде доцільно, наприклад, якщо потрібно додати логотип компанії) потрібно:
sheet.Shapes.AddPicture(name,MsoTriState.msoFalse,MsoTriState.msoTrue,left,top,width,height).Select();
Select потрібне для того, щоб малюнок став активним, так як можливо потрібно буде з ним проводити ще які-небудь операції.
І так name – це абсолютна адреса малюнку, слідуючі два параметри - це чи потрібно зв’язувати малюнок з документом, та чи зберігати малюнок разом із документом. Left, top, width, height – відповідно положення малюнку зліва, положення зверху, ширина та висота. Зверніть увагу, пропорції малюнку не зберігаються.
І на кінець, опишемо як вставити формулу в клітинку. Синтаксис формули такий же як в екселі і також починатися має із =
sheet.Range[ номер клітинки ].Formula =Formula;
Де Formula є рядковою змінною.
Взагалі-то ексель має досить багато функцій і по обробці даних і по форматуванню листів робочої книги, так що тут описати все не можливо. Якщо потрібно із документом зробити якусь операцію, якої тут не описано, можна в екселі почати запис макросу, а потім переглянути код записаного макросу, він хоч і пишеться в синтаксисі Visual Basic for Excel, але функції і аргументи до них розгледіти можна. Майте на увазі, що по замовчуванню запис макросів в екселі може бути вимкнений, включити його можна в меню Файл – Параметри – Налаштування смуги. А там вибрати Основні вкладки – Розробник, та натиснути на ок.