Занятие № 15

Цель работы: получить практические навыки по формированию отчетов в программе Microsoft Excel

Пусть в СУБД Access спроектирована база данных (скачать). База защищена паролем "rpo" и имеет две таблицы.

В программе на C# выполните подключение базы данных

Указания: на панели "Источники данных" щелкните на кнопке "Добавить новый источник данных" (если панель не отображается, то выбери команду меню "Данные - Показать источники данных"). Запуститься мастер создания подключения к БД. В окне выберите значение "База данных", затем значение "Набор данных". На следующем шаге щелкните на кнопке "Создать подключение". В новом окне Вам будет предложено подключить файлы БД SQL Server. Щелкните на кнопке "Изменить" и выберите формат БД "Microsoft Access". После этого укажите нужный файл БД (путь к файлу не удаляйте, среда C# самостоятельно скопирует указанный файл в папку проекта и скорректирует путь к нему). Так как база защищена паролем, то щелкните на кнопке "Дополнительно" и в строке "Jet OLEDB: DataBase Password" укажите пароль "rpo". Для проверки соединения щелкните на кнопке "Проверить подключение". После задания всех параметров мастер предложит выбрать способ сохранения пароля к БД: не включать в строку подключения или включать в строку подключения. Выберите первый вариант для безопасности.

После задания параметров БД при переходе на следующий шаг мастера появится запрос на копирование файла БД в папку с проектом, подтвердите эту операцию.

На следующем шаге мастер предложит сохранить строку подключения в файл конфигурации приложения. Так как пароль доступа не включен в строку подключения, то можете согласиться на сохранение в файл.

На следующем шаге выберите обе таблицы students и groups, с которыми планируете работать в проекте, и щелкните "Готово".

Сразу нужно настроить поведение полей-счетчиков в таблицах. В окне "Источники данных" щелкните на кнопке "Изменить набор данных в конструкторе". Отобразится структура таблиц. Выделите поле id_stud в таблице students и измените свойства: AutoIncrementSeed = 1, AutoIncrementStep = 1. Выделите поле id_grup в таблице groups и измените свойства: AutoIncrementSeed = 1, AutoIncrementStep = 1.

Отображение данных из связанных таблиц

При выводе отчета о студентах одной из колонок будет колонка "Название группы", которой соответствует поле grupa из таблицы groups. Следовательно, нам нужно получать данные из двух связанных таблиц в одном наборе данных. Для этого создадим новый TableAdapter.

Щелкните правой кнопкой на пустом месте окна с таблицами и выберите команду "Добавить - Table Adapter". Введите текст запроса для связанного набора данных:

//отбираем все поля из таблицы stud и поле grupa из таблицы groups

SELECT students.*, groups.grupa FROM groups INNER JOIN students ON groups.id_grup = students.id_grup

Переименуйте созданную новую таблицу: Name = StudReport. В результате автоматически переименуется и связанный с таблицей TableAdapter в studReportTableAdapter.

Все поля в сетке назвать по-русски. Сетка заблокирована для ввода.

Указания: для формы задайте свойства: Caption=Поиск данных в БД, Position=DesktopCenter, Borderstyle=bsSingle (нерастягиваемая форма), MiximizeBox = false (нет кнопки разворачивания окна).

Для подключения связанного набора данных SotrReport нанесите на форму компонент DataRowView. Появится окно, в котором нужно указать подключаемую таблицу SotrReport. На форме появятся дополнительные компоненты для работы с таблицей: bazaDataSet - для доступа к подключению, studReportBindingSource - для работы со связанным набором данных, studReportTableAdapter - для работы с копией таблицы БД. Фамилии студентов будем отображать в алфавитном порядке. Для этого для компонента sotrReportBindingSource задайте свойство Sort = fam (имя поля для сортировки).

Для сетки задайте свойства: AllowUserToAddRows = false (запрет добавления новых строк в сетке), AllowUserToDeleteRows = false (запрет на удаление данных в сетке без подтверждения), AlternatingRowsDefaultCellStyle - BackColor = серый (цвет фона четных строк сетки), AutoSizeColumnsMode=Fill (ширина колонок подстраивается под размер сетки). Для настройки параметров колонок сетки откройте ее свойство Columns. Вы увидите список всех ее колонок. Колонки id_stud, id_grup, foto сделайте невидимыми (Visible=false), для остальных колонок в свойстве HeaderText укажите названия как на рисунке. Колонку "Группа" переместите так, как показано на рисунке.

Если запустить программу на выполнение, то сетка будет пустой, потому что в строке соединения мы не сохранили пароль к БД и подключение не установлено.

Для формы откройте событие Load. В этом событии будут находится вызов метода Fill(), загружающий копию связанного набора данных в компонент TableAdapter. Над этой командой добавьте команду подстановки пароля в строку соединения:

Properties.Settings.Default["BazaConnectionString"] += ";Jet OLEDB:Database Password=rpo";

Фото будем заполнять с помощью диалога выбора графического файла при щелчке на кнопке "+". Кнопка "-" очищает поле Фото.

Кнопка Анкета формирует отчет по текущей записи на основании созданного шаблона.

Указания: в папке с программой создайте папку "Templates" для хранения шаблонов. Запустите Mіcrosoft Excel, в первую колонку листа введите в разные ячейки подписи к данным: Фамилия, Имя, Отчество, Группа, Финансирование, Дата рождения, Средний балл. Сохраните файл в папку "Templates". При этом в поле "Тип файла" укажите "Шаблон Excel 97-2003", а имя файла "Anketa".

Для формирования анкеты нанесите на форму кнопку Button. В свойстве Text укажите "Анкета".

Для работы с программой Microsoft Excel вверху кода формы подключите пространства имен:

using System.IO;

using Excel = Microsoft.Office.Interop.Excel;

using System.Reflection;

using System.Runtime.InteropServices;

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

Также вверху класса формы опишите глобальную переменную:

Excel.Application ex;

Для кнопки напишите код вида:

void Button3Click(object sender, EventArgs e)

{

//пытаемся подключиться к запущенному 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");

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

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

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[1,2]=

(studReportBindingSource.Current as DataRowView)["fam"];
//во вторую колонку второй строки листа отображаем третье поле поле таблицы и т.д.

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[2,2]=

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

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

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

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

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

(studReportBindingSource.Current as DataRowView)["srbal"].ToString();

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

ex.Visible=true;

}

Кнопка "Список" формирует в программе Microsoft Excel документ со списком отобранных записей. При этом условия отбора задаются в полях внизу формы. Если условие не задано, то отчет формируется по всей таблице.

В конце таблицы добавляется новая строка. В первой ячейке отображается текст "Всего", а во второй ячейке - количество отобранных в отчете записей, в третьей - текст "Средний балл", в четвертой - значение среднего балла. Строка с рассчитанными значениями заливается произвольным цветом.

Документ со списком формируется на основе разработанного ранее шаблона и сохраненного во вложенной папке "Templates".

Указания: отчет будет формироваться по шаблону. Запустите Mіcrosoft Excel, на листе введите произвольный заголовок списка, а ниже создайте таблицу, которая состоит из 1 строки и 9 колонок. Данная строка будет играть роль шапки таблицы.

В ячейках шапки введите текст: № п/п, Фамилия, Имя, Отчество, Группа, Финансирование, Дата рожд., Средний балл. Если колонок много разверните лист в альбомную ориентацию. Укажите нужную ширину столбцов, задайте размер шрифта, выравнивание и другие параметры форматирования на ваше усмотрение. Сохраните файл в папку "Templates". При этом в поле "Тип файла укажите "Шаблон Excel 97-2003", а имя файла "List".

Проектирование панели для задания условия фильтрации

Для задания условий поиска нанесите на форму 3 текстовых поля TextBox.

Для отображения на форме процесса постройки отчета нанесите на форму компонент ProgressBar и задайте свойство Visible=false, Style = Continuous (плавное заполнение индикатора).

Нанесите на форму кнопку и задайте для нее свойство Text = Список. Для кнопки введите код:

void Button4Click(object sender, EventArgs e)

{

//вначале условие не задано

string s = "";

//если первое поле не пустое, добавляем к условию

if (textBox1.Text != "")

s += "fam like '" + textBox1.Text + "%'";

//если второе поле не пустое, добавляем к условию

if (textBox2.Text != "")

if (s != "")

s += " and imya like '" + textBox2.Text + "%'";

else

s += "imya like '" + textBox2.Text + "%'";

//если третье поле не пустое, добавляем к условию

if (textBox3.Text != "")

if (s != "")

s += " and otch like '" + textBox3.Text + "%'";

else

s += "otch like '" + textBox3.Text + "%'";

//если условие задано

if (s != "")

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

studReportBindingSource.Filter = s;

//если условие не задано

else

//у набора данных из сетки отключаем фильтр

studReportBindingSource.Filter = null;

//начинаем строить отчет

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

//начальное значение 0

progressBar1.Value = 0;

//максимальное значение равно количество записей в наборе + 2

//(запуск Excel + подсчет итоговых показателей по таблице)

progressBar1.Maximum = studReportBindingSource.Count+2;

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

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++;

//счетчик строк в таблице начинается с 2

//(у нас в шаблоне две строки листа заняты: заголовок + шапка)

int i = 2;

//в цикле проходим по набору данных StudReport

foreach (DataRowView row in studReportBindingSource)

{

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

i++;

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

//в первую колонку выводи значение № п/п

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

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

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

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

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

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

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[i, 6] = row["finance"];

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

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[i, 8] = row["srbal"];

//увеличиваем индикатор на 1

progressBar1.Value++;

}

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

//наращиваем число строк для вывода расчетных данных

i++;

//в первой ячейке i-й строки отображаем текст "Всего"

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[i, 1] = "Всего";

//во второй ячейке i-й строки вводим формулу

//функция counta() считает число непустых значений в диапазоне

//диапазон зададим по колонке A3:A(i-1)

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[i, 2].Formula = "=counta(A3:A" + (i - 1).ToString() + ")";

//в третьей ячейке i-й строки отображаем текст "Средний балл"

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[i, 3] = "Средний балл";

//в четвертой ячейке i-й строки вводим формулу

//функция average() считает среднее по ячейкам диапазона

//диапазон зададим по колонке со средним баллом H3:H(i-1)

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells[i, 4].Formula = "=average(H3:H" + (i - 1).ToString() + ")";

//увеличиваем индикатор на 1

progressBar1.Value++;

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

//таблица находится в диапазоне A2:Hi

//задаем тип линии

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells.get_Range("A2:H" + i.ToString()).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

//задаем толщину линии

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells.get_Range("A2:H" + i.ToString()).Borders.Weight = Excel.XlBorderWeight.xlThin;

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

//строка задается диапазоном Ai:Hi

(ex.ActiveWorkbook.Sheets[1] as Excel.Worksheet).Cells.get_Range("A" + i.ToString() + ":H" + i.ToString()).Interior.Color =
Color.LightGray;

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

progressBar1.Visible = false;

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

ex.Visible = true;

}

Имя поля	Тип	Размер
id_stud	Счетчик	Ключевое
fam	Текстовый	15
imya	Текстовый	15
otch	Текстовый	15
finance	Текстовый	10 (Госзаказ, Контракт)
datar	Дата/время
srbal	Числовой	Одинарной точности с плавающей точкой. Значение по умолчанию - 0
foto	Поле OLE
id_grup	Числовой	Внешний ключ для связи с таблицей groups

Имя поля	Тип	Размер
id_grup	Счетчик	Ключевое
grupa	Текстовый	30