СОЗДАНИЕ ОТЧЕТОВ СРЕДСТВАМИ VBA EXCEL И WORD

CREATING REPORTS VBA EXCEL AND WORD

Roza Gaynanova

lecturer of the Department of General Educational Disciplines Kazan national research Technological University,

Russia, Kazan

АННОТАЦИЯ

В статье рассматривается способы создания отчетов средствами VBA Excel и Word. На SQL Server создана база данных «Учет успеваемости» факультета, содержащая сведения о специальностях, группах, студентах данного факультета и о результатах сдачи студентами сессии. Работа с отчетами рассматривается на примерах создания экзаменационной ведомости и создания сводной ведомости о сдаче сессии данной группой.

ABSTRACT

The article discusses ways to create reports using VBA Excel and Word. On SQL Server, a database "Recording progress" of the faculty has been created, which contains information about specialties, groups, students of a given faculty and about the results of students passing the session. Working with reports is considered on the examples of creating an examination sheet and creating a summary sheet of the passing of the session by this group.

Ключевые слова: VBA Excel, MS Word, SQL Server, база данных, хранимая процедура, отчет.

Keywords: VBA Excel, SQL Server, database, stored procedure, report.

На SQL Server создана база данных «Учет успеваемости» факультета. В этой базе данных содержатся сведения о направлениях подготовки, осуществляемых данным факультетом, о преподаваемых предметах, о группах, списки студентов групп, результаты сдачи студентами зачетов и экзаменов [1,с.102]. С этой базой данных работают сотрудники деканата: вносят изменения при появлении новых направлений подготовки, удаляют списки групп, закончивших обучение, вносят списки студентов зачисленных на певый курс, удаляют отчисленные студенты, перед началом сессии выдают экзаменационные и зачетные ведомости старостам групп, вносят результаты сдачи сесии в эту базу данных. Также анализируют результаты сдачи сесии, создают документацию по успеваемости в конце семестра, подают декану сводку успеваемости групп.

Сотрудник деканата должен обеспечивать: перед сессией печать зачетных и экзаменационных ведомостей; ввод результатов сдачи зачетов и экзаменов на основании ведомостей и направлений. Декан, заместители декана, сотрудники деканата должны получить информацию: сведения о сдаче экзаменов и зачетов указанной группой; полные сведения об учебе конкретного студента; список задолжников по факультету с указанием групп и несданных предметов. Для выполнения этих функций разработаны программы на языке VBA [1,102].

Рассмотрим создание отчета «Экзаменационная ведомость» средствами VBA Excel и Word. В базе данных «Учет успеваемости» создана хранимая процедура «Экзаменационная ведомость». В эту процедуру передаются четыре параметра: номер группы, номер семестра, название предмета, ФИО преподавателя. Для создания этой процедуры понадобятся данные из четырех связанных таблиц: Преподаватель, Студент, Предмет, Рейтинг. Таблицы Преподаватель и Предмет связаны по полю ID преподавателя, таблицы Студент и Рейтинг – по полю ID студента, таблицы Предмет и Рейтинг – по полю ID предмета. Из таблицы Студент берется ФИО студента, номер зачетной книжки, из таблицы Рейтинг – поле Рейтинг, которое переименуется как «Текущая рейтинговая оценка». Если Рейтинг меньше чем 32, студент будет не допущен к экзамену.

На рабочем листе установлены три командные кнопки, и результаты выполнения хранимой процедуры отображаются на рабочем листе (рис.1).

Рисунок 1. Результаты выполнения хранимой процедуры «Экзаменационная ведомость»

На основе данных полученных при отображении результатов выполнения хранимой процедуры в Excel нужно создать отчетный документ Word. На рабочем листе устанавливается кнопка «Вывод на печать». При нажатии на эту кнопку создается документ Word (рис. 2). В рабочем каталоге предварительно создается документ Word «Экзаменационная ведомость.docx». Для корректного функционирования программы устанавливается ссылка на библиотеку объектов Microsoft Word 14.0 Object Library.

Рисунок 2. Вид экзаменационной ведомости, созданной средствами VBA Excel и Word

Для вывода заголовка и заключительной части отчета созданы две процедуры Заголовок и Итоги. В процедуру Заголовок нужно будет передавать пять параметров: номер группы, номер семестра, название предмета, ФИО преподавателя, объект ObjWord.

ObjWord – переменная типа Object, которая используется для доступа к объектам других приложений. Название семестра «Осенний семестр» и «Весенний семестр» определяются по номеру семестра. Если при делении номера семестра на 2 остаток равен нулю Val(Nsem) Mod 2 = 0, то семестр весенний, иначе осенний. Номер курса тоже определяется по номеру семестра. Если при делении номера семестра на 2 остаток равен нулю, то номер курса равен частному от целочисленного деления на 2 kurs = str(Val(Nsem) \ 2), иначе kurs= str(Val(Nsem) \ 2 + 1). Для определения названия учебного года используется функция Date, которая возвращает текущую дату в следующем виде: 21.04.2021, из этой даты выделяются 4 символа справа с помощью функции Right и присваивается переменной nazGod: nazGod = Right (Date, 4), uchGod = Val(nazGod). Для весеннего семестра nazGod = str(uchGod - 1) & "-" & str(uchGod), для осеннего - nazGod=str(uchGod) & "-" & str(uchGod + 1).

Sub Заголовок(Ngr, Nsem, Predmet, prepod As String, objWord As Object)

 Dim uchGod, semestr As Integerи

 Dim nazSem, nazGod, kurs As String

nazGod = Right (Date, 4)        

 uchGod = Val(nazGod)

 If Val(Nsem) Mod 2 = 0 Then

 nazSem = "Весенний семестр"

 nazGod = str(uchGod - 1) & "-" & str(uchGod)

 kurs = str(Val(Nsem) \ 2)

 Else

 nazSem = "Осенний семестр"

 nazGod = str(uchGod) & "-" & str(uchGod + 1)

 kurs = str(Val(Nsem) \ 2 + 1)

 End If

' Вывод названия отчета

With objWord.Selection

.Font.Bold = wdToggle

. . . . .

.TypeText Text:="Федеральное государственное бюджетное образовательное учреждение высшего образования"

. . . . .

.TypeText Text:=nazSem + "  " + nazGod + " уч. года  "

. . . . .

.ParagraphFormat.Alignment = wdAlignParagraphRight

.TypeText Text:="Дата экзамена  _________________"

End With

 End Sub

Создается процедура Итоги. В эту процедуру будет передаваться один параметр objWord типа Object.

Процедура обработки события нажатия кнопки «Вывод на печать»:

Private Sub CommandButton2_Click()

Dim r As Range

Dim n, m, i, j As Integer

Dim msg As String

Dim objWord As Object

Dim objDoc As Word.Document

Dim File As String

File = "D:\Деканат\Экзаменационная ведомость.docx"

. . . . .

Set r = ActiveSheet.UsedRange

' Определяем число строк и столбцов диапазона

n = r.Rows.Count

m = r.Columns.Count

If n = 0 Then Exit Sub

With objWord

.WindowState = wdWindowStateMaximize

.Visible = True

Set objDoc = .ActiveDocument

End With

Call Заголовок(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text)

' Создание таблицы из n-1 строк и из m столбцов

With objDoc

.Tables.Add Range:=objWord.Selection.Range, _

NumRows:=n - 1, NumColumns:=m

.Tables.Item(1).AutoFormat

End With

' Вывод таблицы из Excel в документ  Word

For i = 1 To n

For j = 1 To m

With objWord.Selection

 msg = Cells(i, j).Value

.TypeText Text:=msg

.MoveRight Unit:=wdCell

End With

Next j

Next i

With objWord.Selection

.ParagraphFormat.Alignment = wdAlignParagraphLeft

.MoveDown Unit:=wdLine, Count:=1

End With

' освобождение переменной представляющей объект

Set objWord = Nothing

End Sub

Для получения полных сведений о сдаче экзаменов и зачетов указанной группой в данном семестре в базе данных «Учет успеваемости» создана хранимая процедура «УспеваемостьГруппаСеместр». В эту процедуру передаются два параметра: номер группы и номер семестра. Эта процедура извлекает из базы данных сведения о сдаче экзаменов и зачетов в данном семестре данной группой. Данные берутся из трех связанных таблиц: Студент, Предмет, Успеваемость. Таблицы Студент и Успеваемость связаны по полям Номер группы и ID студента, таблицы Предмет и Успеваемость – по полю ID предмета. Из таблицы Студент берется ФИО студента, из таблицы Предмет – Название предмета, из таблицы Успеваемость – Оценка.

Результаты выполнения хранимой процедуры УспеваемостьГруппаСеместр отображаются на рабочем листе (рис. 3). Данные отображаются в таком виде: ФИО первого студента, все сданные им экзамены и зачеты, результаты сдачи. Дальше ФИО следующего студента и его результаты сдачи сессии и т.д.

На основании этих данных нужно создать сводную ведомость о результатах сдачи сессии данной группой в данном семестре. Для создания сводной ведомости разработан программный код. В программном коде объявляются три массива: a, b, c типа Variant. Массивы a и c двумерные, массив b – одномерный. В массив а с рабочего листа начиная со второй строки переписываются результаты отображения хранимой процедуры УспеваемостьГруппаСеместр. В массив b записываются заголовки столбцов создаваемой сводной ведомости. Первый элемент этого массива – ФИО. В следующие элементы записываются названия сданных предметов. В массив c записываются ФИО студента и результаты сдачи студентом названных предметов. В этом массиве будет столько строк, сколько студентов в группе. Количество столбцов равно количеству сданных предметов и плюс один.

Рисунок 3. Результаты выполнения хранимой процедуры УспеваемостьГруппаСеместр

При создании сводной ведомости используется объект Range. В иерархии Excel объект Range идет сразу после объекта Worksheet. Range является одним из ключевых объектов VBA. Основные свойства объекта Range: Value – присваивает значения массиву из диапазона и наоборот из диапазона массиву; Count – возвращает число объектов а наборе [2, с.90].

В VBA имеется тесная связь между диапазонами и массивами. Допустимо как заполнение массива одним оператором присваивания значениями из ячеек диапазона, так и наоборот, заполнение диапазона ячеек одним оператором присваивания элементами массива. Массив должен быть объявлен как переменная типа Variant [2, с.97].

Private Sub CommandButton2_Click()

Dim I, j, n, m, k, n1, m1 As Intger

Dim r As Range

Dim a, b, c As Variant

' устанавливаем ссылку на диапазон, содержащий данные

Set r = ActiveSheet.UsedRange

' Определяем число строк и столбцов диапазона

n = r.Rows.Count

m = r.Columns.Count

' Переобъявляем массивы a, b, c

ReDim a(1 To n, 1 To m)

ReDim b(1 To 10)

ReDim c(1 To 25, 1 To 10)

' массиву а присваиваются значения из диапазона r

a = r.Value

b(1) = "ФИО"

c(1, 1) = a(2, 1): k = 2

For I = 2 To n

If a(I, 1) = c(1, 1) Then

b(k) = a(I, 2)

k = k + 1

End If

Next I

ReDim Preserve b(k - 1)

n1 = 1: m1 = 2

For I = 2 To n

If a(I, 1) = s1 Then

m1 = m1 + 1

' Если название предмета из массива a совпадает

' с названием заголовка, оценка переписывается

' в массив c в соответствующий столбец

if b(m1)=a(i,2) then c(n1, m1) = a(I, 3)

Else

s1 = a(I, 1)

n1 = n1 + 1

c(n1, 1) = s1

c(n1, 2) = a(I, 3)

m1 = 3

End If

Next I

' данные из массива b переписываются на рабочий

' лист, начиная с ячейки (19, 9)

Set r = Range(Cells(19, 9), Cells(19, k + 8))

r.Value = b

' данные из массива с переписываются на рабочий

' лист, начиная с ячейки (20, 9)

Set r = Range(Cells(20, 9), Cells(20 + n1, 20 + m1))

r.Value = c

End Sub

На основании данных, содержащихся в сводной ведомости, создается текстовый документ Word (рис.4). Для создания текстового документа диапазон, содержащий данные сводной ведомости, нужно выделить и нажать кнопку «Вывод на печать».

Рисунок 4. Вид документа Word, содержащего результаты сдачи сессии группой

Практическая значимость результатов данной работы заключается в создании отчетов средствами VBA Excel и Word. А также в создании сводной ведомости с использованием связи между диапазонами и массивами и возможностью заполнения диапазона ячеек одним оператором присваивания элементами массива и наоборот.

Список литературы:

1. Р.Ш. Гайнанова, А.А. Караванов. Разработка интерфейса пользователя средствами VBA Excel. //Вестник Казанского технологического университета. 2020. Т. 23. № 3. С. 101-105.
2. Программирование на Visual Basic for Applications в Excel: учебное пособие /  Р. Ш. Гайнанова, О. А. Широкова ­– Казань: КФУ, 2012. – 153с./ http://kpfu.ru/docs/F386355132/VBA_Excel.pdf.