Лекція 2. Семестр 2. Приклади стандартних дій в Ексель

         В даній лекції будуть винесені в окремі функції основні операції, які потрібні при переносі даних в ексель. Для зручності можна їх винести в окремий клас, можна їх використовувати як окремі функції в просторі імен вашого додатку. В будь-якому разі метою даної лекції буде висвітлення лише методів роботи з екселем, а не якогось конкретного механізму внутрішньої взаємодії компонентів всередині додатку.

Отже для початку потрібно завантажити ексель. Припустимо, що всі потрібні модулі роботи екселю підключені і ми можемо вже викликати потрібні нам функції. Перше що нам потрібно це відкрити та закрити ексель.

Для збереження зв’язків з самим екселем та робочою сторінкою, будемо використовувати дві такі змінні.

public excel.Application excelapp; //змінна для збереження зв"язку із екселем

public excel.Worksheet sheet; //змінна для посилання на робочий лист куди будуть виводитися дані

Процедура завантаження та ініціалізації з’єднання з екселем може виглядати так:

public void RunExcel(string n = "Немає імені",bool visible=true) //Завантажує ексель

    //де n - задає назву листа

    //visible - показувати чи ні ексель на екрані

 {

  excelapp = new excel.Application();//створюємо зв"язок з екселем і поміщаємо його в excelapp

  excelapp.Visible = visible; //показуємо ексель на екрані, якщо потрібно

  excelapp.SheetsInNewWorkbook = 1; // Кількість сторінок які будуть у книзі

  excelapp.Workbooks.Add(Type.Missing); //Створюємо нову книгу

  sheet = excelapp.Workbooks[1].Worksheets[1]; //Зв"язуємо лист робочої книги і змінну sheet

  sheet.Name = n;// Присвоюємо назву

 }

Показувати ексель на екрані потрібно лише в самому кінці, так як, кожен крок (перенос даних в клітинку, форматування і тд) буде відображатися на екрані, а на це буде затрачатися процесорний час, за рахунок чого Ваша інформаційна система буде формувати звіт значно довше. Але в навчальних цілях ми будемо відображати його з самого початку, щоб ви «наочно» могли спостерігати сам процес.

Процедура закриття нам потрібна лише для «екстрених» випадків, наприклад, коли в блоці була перехоплена помилка і існує вірогідність, що дані не були передані повністю, і, щоб не відображати користувачу.

public void QuitExcel() //Екстренний вихід з екселю, наприклад, кщо трапилась помилка

 {

  if (excelapp != null)//якщо ексель був запущений

   {

    excelapp.DisplayAlerts = false;//Забороняємо показувати повідомлення користувачу

    excelapp.Quit();//Виходимо з екселю

   }

  sheet = null;//Присвоюємо порожнє значення

  excelapp = null;//листу і екселю (знищуємо)

 }

Для того, щоб мати змогу перенести в ексель хоча б що-небудь потрібно перейти від числового індексу клітинки (який використовується в c#) до буквенно-числового (який використовує ексель). Функція може мати вигляд:

public string ConvertRange(int x, int y)// перетворює числову адресу клітинки в формат виду "А1"

 {

  string n = "";//тут буде міститися перетворена адреса

  string sym = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";//Літери англійського алфавіту, використовуються для першої координати

  int symc = 26;//Кількість літер

  if (x / symc == 0)//якщо 0, тоді терша координата буде з однієї літери

   { n = sym[x].ToString(); }//записуємо відповідний індекс літри, як першу координату

  else //якщо кордината записується двома літерами

   { n = sym[x / symc].ToString() + sym[x % symc]; }//додаємо дві літери - перша відділення націло, друга остача від ділення

  n = n + y.ToString();//додаємо другу координату

  return n;//повертаємо перетворені координати

 }

Тепер ми можемо перейти до вставки тексту в клітинку, процедура може мати такий вигляд:

public void InCell(int x, int y, string n)// Додаємо в клітинку з координатами x,y текст n

 {

  sheet.Range[ConvertRange(x - 1, y)].Value = "'"+n;//sheet.Range - об"єкт який здійснює всі операції з клітинками

 }

Для зручності оголосимо ще одну функцію, яка буде повертати текст із клітинки екселю:

public string InCell(int x, int y)//Повертає текст, якій міститься у заданій клітинці

 {

  return sheet.Range[ConvertRange(x - 1, y)].Value; //працює аналогічно попередній функції

 }

Для покращення зовнішнього вигляду, читабельності документа, оголосимо процедуру, яка буде форматувати шрифт клітинки.

public void FormatFont(int x, int y, bool b = false, bool i = false, //змінює параметри шрифту в

bool u = false, int color = 0, int size = 0, string face = "") //заданій клітинці

 // х,у - координати клітинки

 // b, i, u - жирний, курсив, підкреслений шрифт

 // color - колір заданий у форматі 0хBBGGRR

 // size - розмір, face - назва шрифту

  {

   if (b) sheet.Range[ConvertRange(x-1, y)].Font.Bold = b; //якщо потрібно задаємо жирність

   if (i) sheet.Range[ConvertRange(x-1, y)].Font.Italic = i;//якщо потрібно задаємо курсив

   if (u) sheet.Range[ConvertRange(x-1, y)].Font.Underline = u;//якщо потрібно задаємо підкреслення

   if (color != 0) sheet.Range[ConvertRange(x-1, y)].Font.Color = color;//якщо потрібно задаємо колір

   if (size != 0) sheet.Range[ConvertRange(x-1, y)].Font.Size = size;//якщо потрібно задаємо розмір

   if (face != "") sheet.Range[ConvertRange(x-1, y)].Font.Name = face;//якщо потрібно задаємо новий шрифт

  }

Опишемо, також, можливість вставки формулу в клітинку екселю:

public void CellFormula(int x, int y, string f)// Додаємо формулу в потрібну клітинку

     // x, y - координати, f - формула

 {

  sheet.Range[ConvertRange(x-1, y)].Formula = f; //записуємо формулу

 }

Опишемо функцію зміни розміру клітинки. Зверніть увагу, в екселі одиниці виміру розмірів по горизонталі та одиниці виміру по горизонталі – різні, тобто розмір клітинки 20х20 не зробить її квадратною!

public void ResizeCell(int x, int y, int width, int height)//Масштабує потрібну клітинку книги

  // x, y, - координати, width, height - ширина та висота

 {

  if (height != 0) sheet.Range[ConvertRange(x-1, y)].RowHeight = height; //Якщо задана висота - змінюємо

  if (width != 0) sheet.Range[ConvertRange(x-1, y)].ColumnWidth = width; //Якщо задана ширина - змінюємо

 }

Створимо, для зручності, властивість зміни видимості екселю на екрані:

public bool visible //Властивість можна використовувати для регулювання видимості екселя на екрані

 {

  get { return excelapp.Visible; } // повертає статус видимості (видимий/невидимий)

  set { excelapp.Visible = visible; } // встановлює видимість екселю

 }

Для того, щоб розмістити логотип компанії на звіті, або ж, помістити кліше, нам може знадобитися процедура розміщення малюнку на листі:

public void InsertPicture(int left,int top,int width,int height,string name) //додає малюнок в книгу

   // left - позиція малюнку зліва,

   // top - позиція зверху,

   // width,height - ширинв та висота малюнку відповідно,

   // name - ім"я малюнку з абсолютним шляхом до нього

 {

sheet.Shapes.AddPicture(name,MsoTriState.msoFalse,MsoTriState.msoTrue,left,top,width,height).Select();

   // вставляє малюнок відповідно заданих параметрів, та виділяє його,

   // якщо потрібно в подальшому змінити

 }

І, на кінець, ми вже дійшли до самої основної процедури, процедури, яка безпосередньо і буде переносити дані в ексель:

     //Вставляє на лист екселю данні із потрібної сітки з даними

public void InsertTable(DataGridView dg,int left=1,int top=1,

  int hFSize=0,string hFname="",bool hB=false,bool hI=false,bool hU=false,int hColor=0,

  int bFSize=0,string bFName="",bool bB=false,bool bI=false,bool bU=false,int bColor=0)

     //dg - посилання на сітку з даними,

     //left,top - відступи від верхнього кута листа (по замовчуванню 1,1),

     //hFSize - розмір шрифта для рядка заголовків,

     //hFname - назва шрифта для рядка заголовків,

     //hB - жирність шрифта для рядка заголовків,

     //hI - курсив шрифта для рядка заголовків,

     //hU - підкреслення шрифта для рядка заголовків,

     //hColor - колір шрифта для рядка заголовків,

     //bFSize - розмір шрифта для рядків таблиці,

     //bFName - назва шрифта для рядків таблиці,

     //bB - жирність шрифта для рядків таблиці,

     //bI - курсив шрифта для рядків таблиці,

     //bU - підкреслення шрифта для рядків таблиці,

     //bColor - колір шрифта для рядків таблиц

   {

    int r = dg.RowCount; int c = dg.ColumnCount; //поміщаємо в змінні

     // r та с кількість рядків та стовпців відповідно

    for (int hc = 0; hc < c; hc++) //перебераємо всі стовпці по черзі через змінну hc

     {

      InCell(hc+left,top,dg.Columns[hc].Name+"");//дізнаємося заголовок стовпця з індексом hc,

     // та поміщаємо в відповідну клітинку ексель

      FormatFont(hc + left, top, hB, hI, hU, hColor, hFSize, hFname); //задаємо потрібне форматування клітинки

      }

       for (int cr = 0; cr < r; cr++) //перебираємо рядки сітки через змінну cr

          {

              for (int cc = 0; cc < c; cc++) //перебераємо стовпці сітки через змінну сс

                {

                 InCell(cc + left, cr + 1 + top, dg.Rows[cr].Cells[cc].Value + "");

                 //дізнаємося значення клітинки з індексом сс,cr та вставляємо в ексель

                 FormatFont(cc + left,cr+ 1+ top, bB, bI, bU, bColor, bFSize, bFName);//задаємо потрібне форматування клітинки

                }

            }

        }