Лекция № 16

Тема: «Создание отчетов в Microsoft Excel»

 

План

1. Операции с COM-сервером Microsoft Excel

2. Работа с книгами в Microsoft Excel

3. Работа с листами книги в Microsoft Excel

4. Запись данных и формуд в ячейки листа Microsoft Excel

5. Форматирование ячеек

 

1. Операции с COM-сервером Microsoft Excel

Для работы с COM сервером Microsoft Excel в среде Visual Studio на языке программирования C# необходимо выполнить ряд действий.

Вначале подключите пространства имен:

 

using Excel = Microsoft.Office.Interop.Excel;

using System.Reflection;

using System.Runtime.InteropServices;

 

Затем нужно подключить динамическую. библиотеку программы

Кроме этого нужно подключить COM библиотеку программы Microsoft Excel. 

Для этого в контекстном меню проекта выберите команду "Добавить ссылку". В новом окне перейдите на закладку COM и выберите элемент с именем "Microsoft Excel XX Object Library", где ХХ - номер версии Excel (на разных компьютерах номер XX может быть разным, в зависимости от установленной версии пакета Microsoft Office).

 

 

На рисунке в списке отображается две библиотеки версии 11.0 и 14.0. Это говорит о том, что на компьютере установлено несколько версий Microsoft Excel. Чаще всего такая библиотека одна. Выбирайте версию с большим номером (более новая).

В результате в панели "Обозреватель решений" в папке "Ссылки" появится элемент "Microsoft.Office.Interop.Excel".

 

 

Далее нужно описать глобальную переменную для получения ссылки на запущенный экземпляр Microsoft Office и работы с ним.

 

//описываем переменную ex типа Excel.Application

Excel.Application ex;

 

После описания переменной можно создать новый объект COM-сервера с помощью команды:

 

ex = new Excel.Application();

 

После выполнения этого оператора приложение Word запустится, но его окно не появится на экране монитора. В диспетчере задач в списке процессов можно увидеть, что процесс Excel.exe запущен и находится в памяти компьютера.

Чтобы окно программы стало видимой, необходимо установить следующее свойством:

 

ex.Visible=true;

 

При формировании документов, рекомендуется свойство Visible включать после окончания формирования документа. Так можно повысить производительность работы приложения, так как не будут тратиться ресурсы компьютера на отображение на монитора самого процесса построения документа. При этом, пока документ строится, процесс можно сопровождать отображением заполняющего индикатора до 100%.

 

Запуск или подключения к Excel

Мы рассмотрели пример создания нового объекта для запуска копии Microsoft Excel. Однако такой подход имеет недостаток: если Excel уже будет запущен пользователем, то создание нового экземпляра с помощью метода  new Excel.Application() запустит новую копию программы, что ведет к нерациональному расходу оперативной памяти компьютера. Для решения данной проблемы нужно иметь в виду, что есть функция, которая не создает объект, а подключается к уже запущенной копии:

 

ex = Marshal.GetActiveObject ("Excel.Application") as Excel.Application;

 

Перед использованием COM-сервера Excel целесообразно выполнять подключение или создание объекта в зависимости от состояния программы Excel. Правильный код при обращении к WExcel следующий:

 

//пытаемся подключиться к запущенному Excel

try

{

    ex = Marshal.GetActiveObject ("Excel.Application") as Excel.Application;

}

//если Excel на запущен, запускаем его

catch (COMException err)

{

    ex = new Excel.Application();

}

 

2. Работа с книгами в Microsoft Excel

Итак, объект Excel загружен в память компьютера, и у нас есть доступ к нему через переменную ex. Объект имеет методы, с помощью которых можно создавать, открывать и сохранять книги.

 

Создание книги

Для создания книги используют метод:

 

ex.Workbooks.Add();

 

После выполнения этого метода будет создана книга на основании стандартного шаблона.

Вызвать метода Add() можно с аргументом (именем файла шаблона). В этом случае создается документ по шаблону. Для своих приложений можно создать дополнительные шаблоны книг, например шаблон формы платежного поручения или налоговой декларации. В дальнейшем, используя метод Add() с указанием на шаблон, легко создать нужный отчет, программно заполняемый информацией.

Пример. Пусть в папке с программой имеется папка шаблонов с именем Templates, а в ней файл шаблона с именем Anketa.xlt. Создадим новую книгу на основе шаблона.

 

ex.Workbooks.Add(Application.StartupPath + "\\Templates\\Anketa.xlt");

 

Сохранение книги

Можно программно сохранить созданную книгу на диск. Для этого используется метод вида:

 

ex.ActiveWorkbook.SaveAs("имя файла" [,Type.Missing, "пароль"]);

 

Например: сохраним книгу в папку с программой в подпапку Books под именем Report.xls:

 

ex.ActiveWorkbook.SaveAs(Application.StartupPath + "\\Books\\Report.xls");

 

Если нужно сохранить книгу и защитить ее паролем, то код будет иметь вид:

 

ex.ActiveWorkbook.SaveAs(Application.StartupPath + "\\Books\\Report2.xls", Type.Missing, "111");

 

Открытие книги

Для открытия файла книги с диска имеется метод:

 

ex.Wordbooks.Open("имя файла" [,Type.Missing, Type.Missing, Type.Missing, "пароль"]);

 

Например: откроем сохраненный ранее файл без пароля:

 

ex.Workbooks.Open(Application.StartupPath + "\\Books\\Report.xls");

 

Если файл имеет пароль, то его можно открыть так:

 

ex.Workbooks.Open(Application.StartupPath + "\\Books\\Report2.xls", Type.Missing, Type.Missing, Type.Missing, "111");

 

Закрытие книги и приложения Excel

Если книга сохранена, то ее можно закрыть с помощью метода:

 

ex.ActiveWorkbook.Close();

 

После закрытия книги можно закрыть и само приложение Excel. Для этого используют два оператора:

 

ex.Quit();

ex=null;

 

Используя методы работы с книгами можно организовывать фоновое создание файлов без отображения их на экране.

 

Пример. На основании шаблона Anketa.xlt из папки Templates создать новую книгу. Сохранить книгу с именем Report.xls в папку Books. После сохранения выдать сообщение об успешном создании файла и предложить пользователю открыть файл для просмотра. Если ответ положительный, то файл открывается, если отрицательный, то Excel закрывается.

 

//создаем новый объект, запуская Excel

ex = new Excel.Application();

 

//создаем новую книгу на основе шаблона

ex.Workbooks.Add(Application.StartupPath + "\\Templates\\Anketa.xlt");

//сохраняем книгу в указанный файл

ex.ActiveWorkbook.SaveAs(Application.StartupPath + "\\Books\\Report.xls");

 

//выдаем запрос на открытие сохраненного файла

if (MessageBox.Show("Файл сохранен. Показать? ",

                    "Запрос",

                    MessageBoxButtons.YesNo,

                    MessageBoxIcon.Question)==DialogResult.Yes)

  //если ответ Да, делаем Excel видимым

  ex.Visible=true;

else

//если ответ Нет, закрываем книгу и программу

{

  ex.ActiveWorkbook.Close();

  ex.Quit();

  ex=null;

};

 

3. Работа с листами книги в Microsoft Excel

Каждая книга имеет несколько листов (по умолчанию 3). Для обращения к листу используют запись вида:

 

(ex.ActiveWorkbook.Sheets[i] as Excel.Worksheet)

 

где i - номер листа в книге (нумерация начинается с 1).

 

Каждый лист имеет команды для управления ими:

 

//добавляет лист слева от i-го листа

(ex.ActiveWorkbook.Sheets[i] as Excel.Worksheet).Add();

// удаляет i-й лист

(ex.ActiveWorkbook.Sheets[i] as Excel.Worksheet).Delete();

//копирует i-й лист, вставляя его после j-го листа

(ex.ActiveWorkbook.Sheets[i] as Excel.Worksheet).Copy(Type.Missing, (ex.ActiveWorkbook.Sheets[j] as Excel.Worksheet));

// задает имя i-го листа

(ex.ActiveWorkbook.Sheets[i] as Excel.Worksheet).Name="новое имя";

//делает лист активным в книге

(ex.ActiveWorkbook.Sheets[i] as Excel.Worksheet).Select();

 

Чтобы узнать, сколько всего листов книге, можно использовать свойство:

 

ex.ActiveWorkbook.Sheets.Count

 

Замечание. При удалении листа Excel может выдавать сообщения на подтверждении операции. Для выключения выдачи окон с сообщениями необходимо в начало кода добавлять команду вида:

 

ex.DisplayAlerts=false;

 

Для включения функции выдачи сообщений используют команду:

 

ex.DisplayAlerts=true;

 

Пример. Пусть есть некоторый шаблон Primer.xlt, состоящий из одного лист. Необходимо написать код, который добавляет еще девять копий этого листа и каждый лист нумерует числами от 1 до 10.

 

//пытаемся подключиться к запущенному Excel

try

{

    ex = Marshal.GetActiveObject("Excel.Application") as Excel.Application;

}

//если Excel на запущен, запускаем его

catch (COMException err)

{

    ex = new Excel.Application();

}

//создаем новую книгу на основании шаблона

ex.Workbooks.Add(Application.StartupPath + "\\Templates\\Primer.xlt");

 

//запускаем цикл, добавляем 10 листов

for (int i=1; i<=10; i++)

{

    //копируем первый лист и вставляем его после i-го листа

    (ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Copy(Type.Missing, ex.ActiveWorkbook.Sheets[i] as Excel.Worksheet);

    //i-му листу присваиваем имя, которое является значением переменной i

    (ex.ActiveWorkbook.Sheets[i] as Excel.Worksheet).Name=i.ToString();

}

//перед удаление листа выключает отображение предупреждений

ex.DisplayAlerts=false;

//удаляем последний лист

(ex.ActiveWorkbook.Sheets[ex.ActiveWorkbook.Sheets.Count] as Excel.Worksheet).Delete();

//переходи на первый лист

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Select();

 

//делаем окно Excel видимым

ex.Visible = true;

 

4. Запись данных и формул в ячейки листа Microsoft Excel

Шаблоны является самым удобным способом формирования книг Excel. Разработчик может создать коллекцию шаблонов, в которых задать расположение текстовых блоков и их форматирования. Так как лист Excel – это таблица, то можно, записывая данные в определенные ячейки такой таблицы, формировать отчеты в программе. Для обращения к ячейке таблицы используют запись:

 

(ex.ActiveWorkbook.Sheets[n] as Excel.Worksheet).Cells[i,j]="значение";

 

где n – номер листа в книге (нумерация с 1);

i – номер строки на листе (нумерация с 1);

j – номер колонки (нумерация с 1).

 

Например, пусть в шаблоне Anketa.xlt из папки Tempates на первом листе в ячейках второй колонки друг под другом необходимо подставить значение Иванов, Иван, Иванович.

 

//пытаемся подключиться к запущенному Excel

try

{

    ex = Marshal.GetActiveObject("Excel.Application") as Excel.Application;

}

//если Excel на запущен, запускаем его

catch (COMException err)

{

    ex = new Excel.Application();

}

//создаем новую книгу на основании шаблона

ex.Workbooks.Add(Application.StartupPath + "\\Templates\\Anketa.xlt");

 

//записываем в 1 строку 2 столбца текст «Иванов»

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[1,2]="Иванов";

//записываем во 2 строку 2 столбца текст «Иван»

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[2,2]="Иван";

//записываем в 3 строку 2 столбца текст «Иванович»

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[3,2]="Иванович";

 

// делаем книгу видимой

ex.Visible=true;

 

Вместо переменных в шаблоне можно подставлять не только конкретные значения, но и содержимое полей таблицы БД. Такой подход позволяет создавать отчеты по БД с экспортом их в Excel.

Пример. Пусть в шаблоне Anketa.xlt из папки Tempates на первом листе в ячейках второй колонки друг под другом необходимо подставить значение полей fam, imya, otch из таблицы БД.

Для этого напишем код:

 

//пытаемся подключиться к запущенному Excel

try

{

    ex = Marshal.GetActiveObject("Excel.Application") as Excel.Application;

}

//если Excel на запущен, запускаем его

catch (COMException err)

{

    ex = new Excel.Application();

}

//создаем новую книгу на основании шаблона

ex.Workbooks.Add(Application.StartupPath + "\\Templates\\Anketa.xlt");

 

//записываем в 1 строку 2 столбца поле fam

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[1,2]=(sotrBindingSource.Current as DataRowView)["fam"];

//записываем во 2 строку 2 столбца поле imya

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[2,2]=(sotrBindingSource.Current as DataRowView)["imya"];

//записываем в 3 строку 2 столбца поле otch

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[3,2]=(sotrBindingSource.Current as DataRowView)["otch"];

 

// делаем книгу видимой

ex.Visible=true;

 

Пример. Для каждой записи из набора данных сформировать свой отдельный лист на основании листа шаблона Anketa.xlt.

Процесс построения отчета будем сопровождать заполнением строки  индикатора до 100%. Нанесите на форму компонент ProgressBar и задайте свойство Visible=false (сделайте невидимым).

 

//сбрасываем значение индикатора в 0

progressBar1.Value = 0;

//задаем максимальное значение индикатора

//общее число записей + 1

progressBar1.Maximum = sotrBindingSource.Count + 1;

//делаем индикатор видимым

progressBar1.Visible = true;

 

//пытаемся подключиться к запущенному Excel

try

{

    ex = Marshal.GetActiveObject("Excel.Application") as Excel.Application;

}

//если Excel на запущен, запускаем его

catch (COMException err)

{

    ex = new Excel.Application();

}

//создаем новую книгу на основании шаблона

ex.Workbooks.Add(Application.StartupPath + "\\Templates\\Anketa.xlt");

 

//наращиваем значение индикатора на 1

progressBar1.Value++;

 

//номер текущего листа книги (начинаем строить отчет с 1 листа)

int n=1;

 

//в цикле проходим по записям набора данных

foreach (DataRowView row in sotrBindingSource)

{

    //копируем текущий лист (n) после "себя" (n)

    (ex.ActiveWorkbook.Sheets[n] as Excel.Worksheet).Copy(Type.Missing, ex.ActiveWorkbook.Sheets[n] as Excel.Worksheet);

    //на листе с номером n выводим данные о текущем сотруднике

    //записываем в 1 строку 2 столбца поле fam

    (ex.ActiveWorkbook.Sheets[n] as Excel.Worksheet).Cells[1,2]=row["fam"];

    // записываем во 2 строку 2 столбца поле imya

    (ex.ActiveWorkbook.Sheets[n] as Excel.Worksheet).Cells[2,2]=row["imya"];

    //записываем в 3 строку 2 столбца поле otch

    (ex.ActiveWorkbook.Sheets[n] as Excel.Worksheet).Cells[3,2]=row["otch"];

    //переименовываем лист по фамилии сотрудника

    (ex.ActiveWorkbook.Sheets[n] as Excel.Worksheet).Name = row["fam"].ToString();

    //наращиваем счетчик листов для перехода на следующий лист

    n++;

    //наращиваем значение индикатора на 1

    progressBar1.Value++;

}

 

//перед удаление листа выключает отображение предупреждений

ex.DisplayAlerts=false;

//удаляем последний лист книги

(ex.ActiveWorkbook.Sheets[ex.ActiveWorkbook.Sheets.Count] as Excel.Worksheet).Delete();

//переходим на первый лист в книге

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Select();

 

//скрываем индикатор

progressBar1.Visible = false;

// делаем книгу видимой

ex.Visible=true;

 

Формирование таблицы

Работа с шаблонами, рассмотренная выше, позволяет создавать отчет только для отдельной записи таблицы. Гораздо чаще отчет создается для нескольких записей одновременно. При этом данные представляются на одном листе в виде таблицы-списка. Для формирования списка на одном листе необходимо запускать цикл по всем записям набора данных и менять номер строки ячейки при выводе данных.

Пример. Пусть имеется шаблон List.xlt, который находится в папке Templates. Шаблон имеет шапку таблицы с полями № п/п, Фамилия, Имя, Отчество, Дата рождения. Шапка находится в первой строке листа.

На основании данных таблицы БД сформировать список из записей.

Процесс построения отчета будем сопровождать заполнением строки  индикатора до 100%. Нанесите на форму компонент ProgressBar и задайте свойство Visible=false (сделайте невидимым).

 

//сбрасываем значение индикатора в 0

progressBar1.Value = 0;

//задаем максимальное значение индикатора

//общее число записей + 1

progressBar1.Maximum = sotrBindingSource.Count + 1;

//делаем индикатор видимым

progressBar1.Visible = true;

 

//пытаемся подключиться к запущенному Excel

try

{

    ex = Marshal.GetActiveObject("Excel.Application") as Excel.Application;

}

//если Excel на запущен, запускаем его

catch (COMException err)

{

    ex = new Excel.Application();

}

//создаем новую книгу на основании шаблона

ex.Workbooks.Add(Application.StartupPath + "\\Templates\\List.xlt");

 

//наращиваем значение индикатора на 1

progressBar1.Value++;

 

//номер строки таблицы на листе книги

//отчет уже имеют шапку, то есть одна строка уже есть

int n = 1;

 

//в цикле проходим по записям набора данных

foreach (DataRowView row in sotrBindingSource)

{

    //наращиваем число строк в таблице

    n++;

    /записываем в i строку 1 столбца № п/п

    (ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[n, 1] = (n-1);

    //записываем в i строку 2 столбца поле fam

    (ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[n, 2] = row["fam"];
        //записываем во i строку 3 столбца поле imya

    (ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[n, 3] = row["imya"];

    //записываем в i строку 4 столбца поле otch

    (ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[n, 4] = row["otch"];

    //записываем в i строку 5 столбца поле datar

    (ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[n, 5] = row["datar"];

    //наращиваем значение индикатора на 1

    progressBar1.Value++;

}
 

//скрываем индикатор

progressBar1.Visible = false;

// делаем книгу видимой

ex.Visible = true;

 

Работа с формулами

Одним из основных преимуществ Excel является использование формул. Если в ячейке написать текст формулы, то она автоматически будет рассчитана самой программой Microsoft Excel.

Для ввода формулы используют свойство вида:

 

(ex.ActiveWorkbook.Sheets[n] as Excel.Worksheet).Cells[i, j].Formula="=текст формулы";

 

При вводе формул нужно учитывать , что каждая функция Excel записывается в ее англоязычном варианте.

К примеру. В ячейке A3 посчитать сумму ячеек  A1 и A2.

 

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[3, 1].Formula:='=A1+A2';

 

Пусть в ячейках A1:A6 находятся некоторые числовые значения. Необходимо посчитать сумму значений в этих ячейках и отразить результат в ячейке А7.

 

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[7, 1].Formula:='=sum(A1:A6)';

 

Пример. В расcмотренном выше примере вывода списка записей в виде таблицы, добавим код, который в строке под списком в четвертой колонке выводит количество сотрудников и в пятой колонке - минимальную дату рождения.

Для расчета количества будем использовать функцию counta(дапазон). В качестве диапазона можно взять любую колонку без пустых значений. Возьмем колонку А.

Для расчета минимальной даты рождения будем использовать функцию min(диапазон). В качестве диапазона нужно брать колонку E, так как именно в ней выведены даты рождения сотрудников.

 

//после завершения цикла добавьте команды

//увеличиваем число строк в таблице на 1

n++;

//считаем число записей в колоyке А,

//начиная со 2 строки (1 - это шапка таблицы) и до предпоследней

//ответ выводим в четвертой колонке строки

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[n, 4].Formula="=counta(a2:a"+(n-1).ToString()+")";

//находим имнимальную дата рождения в колонке E,

//начиная со 2 строки (1 - это шапка таблицы) и до предпоследней

//ответ выводим в пятой колонке строки

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[n, 5].Formula="=min(e2:e"+(n-1).ToString()+")";

 

5. Форматирование ячеек

Для форматирования ячеек используют следующие свойства:

 

// задает цвет фона в ячейках диапазона

(ex.ActiveWorkbook.Sheets[n] as Excel.Worksheet).get_Range("диапазон").Interior.Color=Color.цвет;

 

// задает толщину границы

(ex.ActiveWorkbook.Sheets[n] as Excel.Worksheet).get_Range("диапазон").Borders.Weight = Excel.XlBorderWeight.значение;

 

// задает тип линии границы

(ex.ActiveWorkbook.Sheets[n] as Excel.Worksheet).get_Range("диапазон").Borders.LineStyle = Excel.XlLineStyle.значение;

 

// задает цвет линии границы

(ex.ActiveWorkbook.Sheets[n] as Excel.Worksheet).get_Range("диапазон").Borders.Color=Color.цвет;

 

Замечание. Несмотря на то, что для задания цвета фона ячейки и цвета линии границы используется класс Color, тем не менее в Excel поддерживаются не все цвета, имеющиеся в данном классе. Если задать не поддерживаемый цвет, то применяется цвет по умолчанию: цвет линии - черный, цвет фона ячейки - белый.

 

Пример. В полученном выше отчете отведем всю таблицу одинарной линией произвольного цвета.

 

//задаем обычную толщину линии

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).get_Range("A1:E"+n.ToString()).Borders.Weight = Excel.XlBorderWeight.xlThin;

//задает сплошной тип линии границы

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).get_Range("A1:E"+n.ToString()).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

//задаем цвет линии

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).get_Range("A1:E"+n.ToString()).Borders.Color=Color.DarkBlue;

 

Ячейки шапки таблицы (1 строка) зальем одним цветом. Ячейки последней строки (строка n) - другим.

 

//заливаем ячейки первой строки таблицы

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).get_Range("A1:E1").Interior.Color = Color.Aqua;

//заливаем ячейки последней строки таблицы

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).get_Range("A"+n.ToString()+":E"+n.ToString()).Interior.Color = Color.LightGrey;

 

 

Вопросы для самоконтроля

1. Опишите принципы запуска Excel из проектов C#.

2. Какие методы для выполнения операций над книгами Excel существуют в C#?

3. Опишите основные методы для работы с листами Excel в C#.

4. Как записывать данные и формулы в ячейки книги Excel  в программах на C#?

5. Какие свойства используются для форматирования ячеек на листе Excel при создании программ в C#?