1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
| Imports Microsoft.Office.Interop.Excel
Public Class Form1
Dim ExApp As New Application 'Приложение Excel
Dim ExWB As Workbook 'Книга Excel
Dim ExWS As Worksheet 'Лист книги Excel
#Region "Форма"
Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
ExApp.DisplayAlerts = False
If ExWS IsNot Nothing Then ExWS = Nothing
If ExWB IsNot Nothing Then ExWB = Nothing
ExApp.Quit()
End Sub
#End Region
#Region "Кнопки"
'Открываем файл
Private Sub btn_OpenFile_Click(sender As Object, e As EventArgs) Handles btn_OpenFile.Click
Try
ExWB = ExApp.Workbooks.Open("d:\test_vb.xls")
ExWS = ExWB.ActiveSheet
'Листы книги
gb_WS.Enabled = True
FillWSList()
'Колонки/Строки Запись/Чтение
gb_WRInFile.Enabled = True
Catch ex As Exception
ErrMsgBox(ex)
End Try
End Sub
'Закрываем открытый файл
Private Sub btn_CloseFile_Click(sender As Object, e As EventArgs) Handles btn_CloseFile.Click
Try
If ExWB IsNot Nothing Then
ExWB.Save()
ExWB.Close()
ExWB = Nothing
gb_WS.Enabled = False
gb_WRInFile.Enabled = False
End If
Catch ex As Exception
ErrMsgBox(ex)
End Try
End Sub
'Скрываем или отображаем окно приложения Excel
Private Sub btn_ShowHideExcel_Click(sender As Object, e As EventArgs) Handles btn_ShowHideExcel.Click
ExApp.Visible = Not ExApp.Visible
Select Case ExApp.Visible
Case True
btn_ShowHideExcel.Text = "Скрыть Excel"
Case False
btn_ShowHideExcel.Text = "Показать Excel"
End Select
End Sub
'Добавить лист в книгу
Private Sub btn_AddWS_Click(sender As Object, e As EventArgs) Handles btn_AddWS.Click
Try
ExWB.Worksheets.Add()
ExWS = ExWB.ActiveSheet
FillWSList()
Catch ex As Exception
ErrMsgBox(ex)
End Try
End Sub
'Удалить лист из книги
Private Sub btn_DeleteWS_Click(sender As Object, e As EventArgs) Handles btn_DeleteWS.Click
Dim iAllWS As Integer = ExWB.Worksheets.Count
If iAllWS > 1 Then
Try
ExWB.Sheets(cb_WSList.SelectedItem).delete()
FillWSList()
Catch ex As Exception
ErrMsgBox(ex)
End Try
Else
MsgBox("Нельзя удалить последний лист в книге", MsgBoxStyle.OkOnly + MsgBoxStyle.Information)
End If
End Sub
'Запишим в Excel свои данные
Private Sub btn_Write_Click(sender As Object, e As EventArgs) Handles btn_Write.Click
If CheckRowCol() Then
Try
ExWS.Cells(CInt(tb_Row.Text), CInt(tb_Col.Text)) = tb_Data.Text
Catch ex As Exception
ErrMsgBox(ex)
End Try
End If
End Sub
'Считаем данные с листа Excel
Private Sub btn_Read_Click(sender As Object, e As EventArgs) Handles btn_Read.Click
If CheckRowCol() Then
Try
tb_Data.Text = ExWS.Cells(CInt(tb_Row.Text), CInt(tb_Col.Text))
Catch ex As Exception
ErrMsgBox(ex)
End Try
End If
End Sub
#End Region
'Позволяет изменить лист в книге и активирует его
Private Sub cb_WSList_SelectionChangeCommitted(sender As Object, e As EventArgs) Handles cb_WSList.SelectionChangeCommitted
ExWS = ExWB.Sheets(cb_WSList.SelectedItem)
ExWS.Activate()
End Sub
#Region "Вспомогательные функции и процедуры"
Private Sub ErrMsgBox(ByRef ex As Exception)
If ex Is Nothing Then Exit Sub
MsgBox(ex.Source & vbCrLf & ex.Message, MsgBoxStyle.OkOnly + MsgBoxStyle.Critical)
End Sub
'Обработка только цифровых клавиш
Private Sub NumberKeyPress(sender As Object, e As KeyPressEventArgs) Handles tb_Row.KeyPress, tb_Col.KeyPress
If Not (Char.IsNumber(e.KeyChar) Or e.KeyChar = vbBack) Then
e.Handled = True
End If
End Sub
'Заполнение списка листов книги
Private Sub FillWSList()
Dim iFor As Integer
cb_WSList.Items.Clear()
cb_WSList.BeginUpdate()
Dim iAllWS As Integer = ExWB.Worksheets.Count
For iFor = 1 To iAllWS
cb_WSList.Items.Add(ExWB.Sheets(iFor).name)
Next
cb_WSList.EndUpdate()
cb_WSList.SelectedIndex = 0
End Sub
'Проверим, что бы были указаны Строка и Колонка
Private Function CheckRowCol() As Boolean
If tb_Row.Text = "" Then
MsgBox("Не заполнено поле Строка.", MsgBoxStyle.OkOnly + MsgBoxStyle.Information)
Return False
Exit Function
End If
If tb_Row.Text > ExWS.Rows.Count Then
MsgBox("Укажите номер строки в пределах от 1 до " & ExWS.Rows.Count, MsgBoxStyle.OkOnly + MsgBoxStyle.Information)
Return False
Exit Function
End If
If tb_Col.Text = "" Then
MsgBox("Не заполнено поле Колонка.", MsgBoxStyle.OkOnly + MsgBoxStyle.Information)
Return False
Exit Function
End If
If tb_Col.Text > ExWS.Columns.Count Then
MsgBox("Укажите номер колонки в пределах от 1 до " & ExWS.Columns.Count, MsgBoxStyle.OkOnly + MsgBoxStyle.Information)
Return False
Exit Function
End If
Return True
End Function
#End Region
End Class |
