Создание приложения VBA в Access "Начисления заработной платы по должностям"

Размещено на http://www.allbest.ru

Размещено на http://www.allbest.ru

СОДЕРЖАНИЕ

1. СОЗДАНИЕ БАЗ ДАННЫХ С ИСПОЛЬЗОВАНИЕМ ТЕКСТОВЫХ ФАЙЛОВ

1.1 Последовательный доступ к данным

1.2 Прямой доступ к данным

2. ОБЪЕКТНАЯ МОДЕЛЬ VBA MS EXCEL

2.1 Язык программирования VBA MS Excel

2.2 Использование ADO с данными MS Excel

3. СОЗДАНИЕ И ОБРАБОТКА ЭЛЕКТРОННЫХ ТАБЛИЦ MS EXCEL

3.1 Сложные объекты VBA MS Excel

3.2 Создание отчетов и официальных документов

4. ОБЪЕКТНАЯ МОДЕЛЬ VBA MS ACCESS.

4.1 Язык программирования VBA MS Access

4.2 Программные модули в VBA MS Access

5. СОЗДАНИЕ БАЗЫ ДАННЫХ MS ACCESS: МОДЕЛЬ ADO.NET

5.1 Применение ADO с данными MS Access

5.2 Управление БД MS Access

6. РАБОТА С ТАБЛИЦАМИ MS ACCESS: ОБЪЕКТ DATASET

6.1 Доступ к данным таблицы

6.2 Управление данными таблицы через интерфейс

7. ОБРАБОТКА ДАННЫХ MS ACCESS: ОБЪЕКТ DATAADAPTER

7.1 Управление данными таблицы

7.2 Выборка и представление данных

СПИСОК ИСПОЛЬЗОВАННЫХ ЛИТЕРАТУРЫ



1. СОЗДАНИЕ БАЗ ДАННЫХ С ИСПОЛЬЗОВАНИЕМ ТЕКСТОВЫХ ФАЙЛОВ

1.1 Последовательный доступ к данным

Листинг 1.1 Объявление переменных

Public Class Form1

Dim fak As String Dim nfak As String Dim nb As String

Dim dolj As String Dim ndolj As String Dim tb As String

Таким же путем объявляем другие нужные нам переменные.

Теперь последовательно читаем с текстовых файлов введенные данные.

Листинг 1.2 Чтение из текстовых файлов

fb = "test.txt tb = "test1.txt" nb = "test2.txt"

FileOpen(1, fb, OpenMode.Input id = LineInput(1)

fam = LineInput(1) imya = LineInput(1)

otc = LineInput(1) datroj = LineInput(1)

k = Int(LineInput(1))

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

FileClose(1)

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

id = LineInput(1)

fam = LineInput(1)

imya = LineInput(1)

otc = LineInput(1)

datroj = LineInput(1)

k = Int(LineInput(1))

В результате выполнения данного кода данные из файлов читаются последовательно. Для этого сначала нужно создать 3 текстовых файла и вводить данные в эти файлы. Результат приведен на рисунке 1.1.

Рис.1.1.Последовательный доступ к данным.

1.2 Прямой доступ к данным

Сначала нужно создать структуру. Код приведен на листинге 1.3.

Листинг 1.3 Код структуры для создания прямого доступа

Structure sotrudniki

<VBFixedString(8)> Dim id_sotr As String

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

Листинг 1.4 Код прямого доступа к данным

FileOpen(1, fn, OpenMode.Random, OpenAccess.ReadWrite, 77)



Результат данного кода приведен на рисунке 1.2.

Рис.1.2 Форма для ввода данных

Заключение. С помощью этих кодов можно читать данные сотрудников и их должностей на форме. Проект приводится в папке Yusupova Z_den1



2. ОБЪЕКТНАЯ МОДЕЛЬ VBA MS EXCEL

2.1 Язык программирования VBA MS Excel

Объектная модель точно соответствует пользовательскому интерфейсу. Объект Application представляет приложение в целом, и каждый из объектов Workbook содержит коллекцию объектов Worksheet. Отсюда следует, что основная абстракция, представляющая ячейки, является объектом Range, позволяющим работать с отдельными ячейками или группой ячеек.

. Существуют 4 основных объекта в Excel: Приложение, книга, лист, диапазон.

С помощью MS Excel можно создать макросы и модели. Для создания макросов нужно сначала записать макрос и совершить какие-то операции на листе. После записи можно посмотреть код. Ниже приведены листинги макросов.

Листинг 2.1. Код макроса ввода данных

ActiveCell.FormulaR1C1 = "Орипова"

Range("B3").Select

ActiveCell.FormulaR1C1 = "Манижа"

Range("C3").Select

ActiveCell.FormulaR1C1 = "Джамоловна"

Range("D3").Select

ActiveCell.FormulaR1C1 = "8"

Range("E3").Select

ActiveCell.FormulaR1C1 = "11/1/1974"

Range("F2").Select

ActiveCell.FormulaR1C1 = "4/27/2011"



В результате данного макроса в таблицу в Excel-е, в указанные в листинге ячейки вводились данные сотрудников. Результат данного макроса приведен на рисунке 2.1.

Рис.2.1 Результат выполнения макроса

Таким же путем были созданы еще 6 макросов. Их функция тоже «ввод данных» в таблицах. Макросы облегчают работу с данными.

Кроме макросов в Excel-е также можно создавать модули, где можно написать код для изменения данных на листе. Каждый модуль выполняет отдельную функцию. Ниже приведена модуль на листинге 2.2.

Листинг 2.2. Код изменения типа шрифта

Sub день_2()

ActiveWorkbook.ActiveSheet.Cells.Font.Name = "Times New Roman"

End Sub

Данный код изменяет шрифт на листе.

Листинг 2.3 Код изменения цвет фона в диапазоне

Sub день_2()

ActiveWorkbook.Worksheets("Лист1").Range("B2:D3,H7").Interior.Color = vbRed

End Sub

В результате выполнения данного кода меняется цвет в указанном диапазоне. Таким же путем были созданы еще 5 модулей в Excel-е.



2.2 Использование ADO с данными MS Excel

С помощью VB Net можно управлять данными в Excel-е. С помощью формы и элементов управления можно вводить данные в Excel. Пример приведен на листинге 2.4

Листинг 2.4 Код ввода данных с помощью VB Net

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Dim xl As New Excel.Application

Dim xlwb As Excel.Workbook

xlwb = xl.Workbooks.Open("C:\Users\zulol\Desktop\База данных-2\день 2БД\день_2_1")

Теперь нужно выбрать диапазон для ввода данных.

Листинг 2.5 Выбор ячеек на рабочем листе

xlwb.Worksheets("лист1").range("o2").value = "итого"

xlwb.Worksheets("лист2").range("A8").value = "Олимов"

xlwb.Worksheets("лист2").range("b8").value = "Умед"

xlwb.Save()

xlwb.Close()

xl.Quit()

MsgBox("ok")

С помощью данного кода можно вводить данные сотрудника в таблице на листах в Excel-е. Результат приведен на рисунке 2.2.



Рис. 2.2 Результат листинга 2.4

Можно также создать гистограмму с помощью VB Net. Код создания приведен на листинге 2.5.

Листинг 2.5. Код создания гистограммы

Imports Microsoft.Office.Interop

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim ex As New Excel.Application

ex.Workbooks.Add()

.ex.Range("A1").Value = "Зарплата"

.ex.Range("A2").Value = "Декан"

.ex.Range("B1").Value = "Должности"

.ex.Range("B2").Value = TextBox1.Text

.ex.Charts.Add()

Выше в каждой ячейках ввели значения обеих осей гистограммы. Значения мы выдаем в Textbox-ах, после чего гистограмма создается в Excel-е.

Листинг 2.6 Выбор листа и типа гистограммы

With ex.ActiveChart

.ChartType = 51

.SetSourceData(ex.Sheets("Лист1").Range("A1:B5"), lotBy:=2)

.HasLegend = False

.HasTitle = True

.ChartTitle.Characters.Text = "Зарплата сотрудников"

.Axes(1, 1).HasTitle = True

.Export("E:\zarplata.jpg")

End With

With ex.ActiveWorkbook

.Close(False)

End With

ex.Quit()

PictureBox1.Image = Image.FromFile("E:\zarplata.jpg")

End Sub

С помощью данного кода можно создать гистограмму зарплаты по разным должностям. Данная гистограмма сохранится как картинка и экспортируется в Picturebox-е. Код этих операций приведен на листинге 2.6. Результат приведен на рисунке 2.3.

Рис.2.3 Результат листинга 2.5

Данные ввели и гистограмма появилась на форме.

Заключение. В результате можно сказать, что макросы облегчают совершения операций в Excel-е. Также с помощью кода VB.Net не создавая лист Excel можно создать гистограмму на листе или ввести данные на нем. На других известных нам платформах, например, как Access невозможно создать гистограмму для БД. На гистограмме показывается зарплата работников по должностям. Проект приводится в папке Yusupova Z_den2.



3. СОЗДАНИЕ И ОБРАБОТКА ЭЛЕКТРОННЫХ ТАБЛИЦ MS EXCEL

3.1 Сложные объекты VBA MS Excel

В Excel-е была создана рабочая книга. На листе вводили данные соответственно предметной области (Отдел кадров). Создана табель зарплаты сотрудников. Также в модуле была создана форма, с помощью которой можно вводить данные в этот табель. Код данной операции приведен на листинге 3.1.

Листинг 3.1. Код ввода данных с помощью формы

Private Sub CommandButton1_Click()

Dim r As Integer

r = Application.CountA(ActiveSheet.Columns(1)) + 1

Вышеприведенный код позволяет вводить данные в отдельных столбцах.

Листинг 3.12 Присвоение значений к текстовым полям

ActiveSheet.Cells(r, 1).Value = TextBox1.Text

ActiveSheet.Cells(r, 2).Value = TextBox2.Text

ActiveSheet.Cells(r, 3).Value = TextBox3.Text

End Sub

Можно вводить данные в текстовых полях

Данная форма приведена на рисунке 3.1.



Рис.3.1 Форма для ввода данных

Данные этого сотрудника ввелись в таблицу.

Также можно защитить лист для сохранности информации из вкладки «Рецензирование», «Защита листа». Также разрешить изменения на листе. Все это можно сделать с паролем или без пароля. Также был задан пароль для скрытия кода в модуле.

3.2 Создание отчетов и официальных документов

В среде VBA MS Excel можно создавать пользовательские функции для обработки данных, в нашем случае для вычисления заработной платы по должностям. Код приведен на листинге 3.2.

Листинг 3.2 Код вычисления зарплаты

Function zp(chasi As Double, ch_plata As Double) As Double

zp = chasi * ch_plata

End Function

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

Рис.3.2 Результат листинга 3.1

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

Заключение. Цель работы в том, что можно применять эти функции при создании табели о зарплате. Чтобы каждый раз не вводить одни и те же данные можно использовать шаблон этой ведомости и поставить защиту в нужных полях. А также составить рейтинг работников с помощью гистограмм.



4. ОБЪЕКТНАЯ МОДЕЛЬ VBA MS ACCESS

4.1 Язык программирования VBA MS Access

Программирование на VBA в СУБД Access используют в основном разработчики (программисты) в процессе создания приложений (различных баз данных, БД), с которыми работают пользователи.

Для программирования в Access используется не приложение Visual Basic, а встроенная в Microsoft Office система программирования Visual Basic for Applications (VBA - Visual Basic для приложений). Система программирования VBA является неотъемлемой частью приложений Microsoft Office и предназначена для визуального программирования в таких приложениях как Word, Excel, PowerPoint, Access и т.д. В VBA языком программирования является VB, а инструментальная среда программирования реализована в виде редактора VB, который может активизироваться из любого приложения MS Office. 

Программные модули в VBA MS Access

Сначала из вкладки «Проекты» , «Добавить ссылку» подключим среду Microsoft ActiveX Data Objects. После этого можно будет работать с Ms Access. Теперь можно с помощью кода создать таблицы в Ms Access, а также указать связи между ними. Была создана 5 таблиц и связи между ними. Код приведен на листинге 4.1.

Листинг 4.1 Код создания таблицы и связи

Dim cn = New OleDbConnection("Provider=microsoft.ACE.oledb.12.0; Data Source=E:\sotrudniki.accdb")

cn.Open()

Dim sql As New OleDbCommand("CREATE TABLE sotrudniki(id_sotr char(6), fio char(55), staj char(6), data_roj char(10), id_sotr_dolzhn char(6), id_kafed char(6), id_otpuska char(6), primary key(id_sotr), foreign key (id_sotr_dolzhn) references dolzhnosti(id_dolzhn), foreign key (id_kafed) references kafedri(id_kaf), foreign key (id_otpuska) references otpusk(id_otp) ON DELETE CASCADE ON UPDATE CASCADE)", cn)

Для этого использовали проводник и указали источник - существующую базу. С помощью оператора Try Catch проверено ошибка при выполнении кода.

Листинг 4.2 Код проверки на ошибку

Try

sql.ExecuteNonQuery()

MsgBox("Таблица создалась!!!")

Catch ex As Exception

MsgBox(ex.Message)

MsgBox("К сожалению таблица не создалась")

End Try

cn.Close()

Используя вышеприведенные команды были созданы еще 4 вспомогательных таблиц. Схема данных в Ms Access приведен на рисунке 4.1.

Рис.4.1 Схема данных БД «Отдел кадров».



Заключение. Из вышеприведенных операций можно сделать такой вывод, что с помощью кода можно создать таблицы, добавлять столбцы в них, а также провести связей в них. Это в основном нужно разработчикам для создания различных БД, с которыми работают пользователи. Проект приводится в папке Yusupova Z_den_4



5. СОЗДАНИЕ БАЗЫ ДАННЫХ MS ACCESS: МОДЕЛЬ ADO.NET

5.1 Применение ADO с данными MS Access

Объект - представитель класса Connection представляет соединение с источником (базой) данных и обеспечивает подключение к базе данных. Visual Studio .NET поддерживает класс OleDbConnection (обеспечивает подключение к прочим вариантам БД). Внутри этого класса нужно указать подходящий провайдер и источник нужной БД.

Объект Recordset представляет любой набор записей. Этот набор записей можно представлять себе как обычную временную таблицу, имеющую строки и столбцы. При помощи этого объекта вы сможете манипулировать данными, полученными от установленного провайдера. Каждый объект Recordset характеризуется определенным типом курсора, то есть типом объекта, обслуживающего данный набор.

5.2 Управление БД MS Access

Для выборки данных из таблицы с использованием объекта OleDbConnection на листинге 5.1 приведен код.

Листинг 5.1 Код выборки из таблицы

Dim cn = New OleDbConnection("Provider=microsoft.ace.oledb.12.0; Data Source=E:\sotrudniki.accdb")

cn.Open()

Dim sql As New OleDbCommand("SELECT * FROM dolzhnosti", cn)

Dim rs As OleDbDataReader = sql.ExecuteReader

Dim tabl As New DataTable



Как уже видно из кода, внутри объекта OleDbConnection указан провайдер и источник базы. Дальше читается поток информации.

Далее нужно выбрать нужные поля и строки для чтения таблицы на форме. Код приведен на листинге 5.2.

Листинг 5.1 Выбор полей и строк для чтения

tabl.Columns.Add(rs.GetName(0))

tabl.Columns.Add(rs.GetName(1))

While rs.Read = True

tabl.Rows.Add(New String() {rs.GetValue(0), rs.GetValue(1), rs.GetValue(2), rs.GetValue(3)})

End While

rs.Close()

cn.Close()

DataGridView1.DataSource = tabl

После ввода данного кода на форме читается таблица «должности», приведенная на рисунке 5.2.

Рис.5.2. Чтение таблицы «Должности»

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

Заключение. Часто для создания коммерческих БД программисты создают таблицы и используют команды Select в платформах VB Net. И с помощью кода можно выполнять операции, которые выполняют в Ms Access-е. Проект приводится в папке Yusupova Z_den_4



6. РАБОТА С ТАБЛИЦАМИ MS ACCESS: ОБЪЕКТ DATASET

6.1 Доступ к данным таблицы

Объект DataSet представляет собой буфер для хранения данных из базы. Этот буфер предназначен для хранения структурированной информации, представленной в виде таблиц, поэтому первым, самым очевидным вложенным объектом DataSet является DataTable. Внутри одного объекта DataSet может храниться несколько загруженных таблиц из базы данных, помещенных в соответствующие объекты DataTable. Используя объекты DataTable была совершена операция чтения таблицы( глава 5).

6.2 Управление данными таблицы через интерфейс

Через интерфейс формы можно управлять данными в таблицах. Можно добавить, удалить или обновить информацию. Код добавления в главную таблицу приведена на листинге 6.1.

Листинг 6.1 Код добавления информацию через формы

Dim darhost, id_sotr, fio, staj, data_roj As String

Dim cn = New OleDbConnection("Provider= MicroSoft.ACE.OLEDB.12.0; Data Source=E:\sotrudniki.accdb")

cn.Open()

Dim sql2 As New OleDbCommand("SELECT max(id_sotr)+1 as max_id_sotr FROM sotrudniki", cn)

Dim rs As OleDbDataReader = sql2.ExecuteReader

Для ввода Id выше написан код, где указан, что он вводится автоматически последовательно после существующей в таблице. Дальше нужно присваивать значения к текстовым полям и добавим значения внутри команды Insert. Код приведен на листинге 6.2.

Листинг 6.2. Добавление данных в таблицу «Сотрудники»

darhost = "INSERT INTO sotrudniki (id_sotr, fio, staj, data_roj) VALUES ('" & id_sotr & "', '" & fio & "', '" & staj & "', '" & data_roj & "')"

Dim sql As New OleDbCommand(darhost)

Теперь можно запустить форму и вводить данные в одну таблицу. Форма приведена на рисунке 6.1.

Рис.6.1 Форма для ввода информации

Данные, которые вводим автоматически появляются в таблице в Access-е.

Также можно обновить (изменить) данные а таблицах. Пример приведен на листинге 6.3.

Листинг 6.3. Код изменения данных в таблице

sqlupdate = "update otpusk set nazv_otp= @nazv_otp, data_uh=@data_uh, data_vih= @data_vih where id_otp='" & Val(Trim(Txtidotp.Text)) & "'"

В результате выполнения данного листинга можно вводить новые данные вместо существующих данных.

Теперь нужно удалить данные в таблице. Код приведен на листинге 6.4.

Листинг 6.4. Код удаления данных из таблицы



"delete * from otpusk where id_otp='" & Val(Trim(Txtidotp.Text)) & "'"

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

Заключение. Итак, используя те же команды insert, update, delete можно управлять данными в таблицах. Не открывая Access можно с помощью кода изменить данные в таблицах. Это дополнительные возможности в среде VB Net. Проект приводится в папке Yusupova Z_den_6



7. ОБРАБОТКА ДАННЫХ MS ACCESS: ОБЪЕКТ DATAADAPTER

7.1 Управление данными таблицы

Класс DbDataAdapter имеет четыре свойства, которые служат для получения данных из источника данных и обновления данных в нем: свойство SelectCommand возвращает данные из источника данных, а свойства InsertCommand UpdateComman и DeleteCommand используются для управления изменениями в источнике данных. Свойство SelectCommand должно быть установлено до вызова метода Fill объекта DataAdapter. Свойства InsertCommand, UpdateCommand или DeleteCommand должны быть установлены до вызова метода Update объекта DataAdapter в зависимости от того, какие изменения были сделаны в данных в DataTable. Если метод Update обрабатывает вставленную, обновленную или удаленную строку, DataAdapter использует соответствующее свойство Command для обработки действия. Текущие данные об измененной строке передаются в объект Command через коллекцию Parameters.

7.2 Выборка и представление данных

приложение данный отчет таблица

Для выполнения удаления, добавления или изменения данных в таблицах, как уже приведено во главе 6 были использованы Insert, Update и Delete. Эти команды можно использовать для выборки из нескольких таблиц и представить с помощью элемента управления Combobox. Пример приведен на листинге 7.1.

Листинг 7.1 Код выборки из нескольких таблиц

cn.Open()

Dim sql4 As New OleDbCommand("SELECT * FROM fakulteti nazv_fak", cn)

Dim rs4 As OleDbDataReader = sql4.ExecuteReader

While rs4.Read = True

ComboBox4.Items.Add(Trim(rs4.GetValue(1)))

'Label7.Text = (Trim(rs1.GetValue(0)))

End While

rs1.Close(): cn.Close(): cn.Open()

Как уже видно из кода, в Combobox.-е появляется список названий факультетов - нужно выбрать один из них, чтобы добавить в нужную таблицу. Дальше на листинге 7.2 приведен код добавления из другой таблицы.

Листинг 7.2 Выбор данных из таблицы

Dim sql6 As New OleDbCommand("SELECT * FROM kafedri order by nazv_kaf, kol_rab ", cn)

Dim rs6 As OleDbDataReader = sql6.ExecuteReader

While rs6.Read = True

ComboBox7.Items.Add(Trim(rs6.GetValue(0)) & "--->" & Trim(rs6.GetValue(1)) & " " & Trim(rs6.GetValue(2)) & " " & Trim(rs6.GetValue(3)))

End While

rs6.Close()

cn.Close()

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



Рис.7.1. Выборка из нескольких таблиц

Как уже видно эти данные можно будет добавить в таблицу. Таким же образом можно получить данные и из других таблиц тоже.

Заключение. Данные операции позволяют разработчикам с помощью одного потока кодов добавлять нужные данные из нескольких таблиц в главную таблицу «Сотрудники». Это намного облегчит им работу. Для этого можно использовать и другие элементы управления. Проект приводится в папке Yusupova Z_den_6



СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ

Зиборов В.В. Visual Basic 2010 на примерах. -СПб.: БХВ-Питер. 2010. -336с.

Гарнаев А.Ю. Самоучитель Visual Studio 2003. -СПб.: БХВ-Питер. 2003. -688с.

Карпов Р.Г. и др. Самоучитель Visual Basic.Net. -СПб.: БХВ-Питер. 2005. -592с.

Солиев О.М., Худойбердиев Х.А. Ма?м?и кор?ои лаборатор? базаи маълумот (MS Access 2010) -Ху?анд: ДПДТТ. 2013. -292с

Майкл Р. Microsoft Access 2000. Библия пользователя. -М.: Вильямс. 2001. -1040с.

Л. Д. Слепцова. Программирование на VBA в Microsoft Office 2010. -М.: Вильямс. 2010. -432с.

Назаров А.А. Технология?ои сохтани шабака?ои компютер?. -Ху?анд: ДПДТТ, 2013. -395с

Файзуллоева Ш. Талаботи меъёри сан?иш барои ороиши кор?ои хатт?. -Ху?анд: ДПДТТ, 2012. -42с.

Размещено на Allbest.ru