-
Notifications
You must be signed in to change notification settings - Fork 0
/
Form1.vb
336 lines (301 loc) · 17.1 KB
/
Form1.vb
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
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
Imports DevExpress.Export.Xl
Imports DevExpress.Spreadsheet
Imports System
Imports System.Collections.Generic
Imports System.Diagnostics
Imports System.Drawing.Printing
Imports System.Globalization
Imports System.IO
Imports System.Windows.Forms
Namespace XLExportExample
Public Partial Class Form1
Inherits DevExpress.XtraEditors.XtraForm
Private employees As List(Of EmployeeData) = CreateEmployees()
Private departments As List(Of String) = CreateDepartments()
Private headerRowFormatting As XlCellFormatting
Private evenRowFormatting As XlCellFormatting
Private oddRowFormatting As XlCellFormatting
Public Sub New()
InitializeComponent()
InitializeFormatting()
End Sub
Private Sub InitializeFormatting()
' Specify formatting settings for the even rows.
evenRowFormatting = New XlCellFormatting()
evenRowFormatting.Font = New XlFont()
evenRowFormatting.Font.Name = "Century Gothic"
evenRowFormatting.Font.SchemeStyle = XlFontSchemeStyles.None
evenRowFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Center)
' Specify formatting settings for the odd rows.
oddRowFormatting = New XlCellFormatting()
oddRowFormatting.CopyFrom(evenRowFormatting)
oddRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light1, -0.15))
' Specify formatting settings for the header row.
headerRowFormatting = New XlCellFormatting()
headerRowFormatting.CopyFrom(evenRowFormatting)
headerRowFormatting.Font.Bold = True
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0)
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0))
headerRowFormatting.Border = New XlBorder()
headerRowFormatting.Border.TopColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0)
headerRowFormatting.Border.TopLineStyle = XlBorderLineStyle.Medium
headerRowFormatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0)
headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Medium
End Sub
' Export the document to XLSX format.
Private Sub btnExportToXLSX_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnExportToXLSX.Click
Dim fileName As String = GetSaveFileName("Excel Workbook files(*.xlsx)|*.xlsx", "Document.xlsx")
If String.IsNullOrEmpty(fileName) Then Return
If ExportToFile(fileName, XlDocumentFormat.Xlsx) Then ShowFile(fileName)
End Sub
' Export the document to XLS format.
Private Sub btnExportToXLS_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnExportToXLS.Click
Dim fileName As String = GetSaveFileName("Excel 97-2003 Workbook files(*.xls)|*.xls", "Document.xls")
If String.IsNullOrEmpty(fileName) Then Return
If ExportToFile(fileName, XlDocumentFormat.Xls) Then ShowFile(fileName)
End Sub
' Export the document to CSV format.
Private Sub btnExportToCSV_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnExportToCSV.Click
Dim fileName As String = GetSaveFileName("CSV (Comma delimited files)(*.csv)|*.csv", "Document.csv")
If String.IsNullOrEmpty(fileName) Then Return
If ExportToFile(fileName, XlDocumentFormat.Csv) Then ShowFile(fileName)
End Sub
Private Function GetSaveFileName(ByVal filter As String, ByVal defaultName As String) As String
saveFileDialog1.Filter = filter
saveFileDialog1.FileName = defaultName
If saveFileDialog1.ShowDialog() <> DialogResult.OK Then Return Nothing
Return saveFileDialog1.FileName
End Function
Private Sub ShowFile(ByVal fileName As String)
If Not File.Exists(fileName) Then Return
Dim dResult As DialogResult = MessageBox.Show(String.Format("Do you want to open the resulting file?", fileName), Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question)
If dResult = DialogResult.Yes Then Call Process.Start(fileName)
End Sub
Private Function ExportToFile(ByVal fileName As String, ByVal documentFormat As XlDocumentFormat) As Boolean
Try
Using stream As FileStream = New FileStream(fileName, FileMode.Create)
' Create an exporter with the specified formula parser.
Dim exporter As IXlExporter = XlExport.CreateExporter(documentFormat, New XlFormulaParser())
' Create a new document and begin to write it to the specified stream.
Using document As IXlDocument = exporter.CreateDocument(stream)
' Generate the document content.
GenerateDocument(document)
End Using
End Using
Return True
Catch ex As Exception
MessageBox.Show(ex.Message, Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
Return False
End Try
End Function
Private Sub GenerateDocument(ByVal document As IXlDocument)
' Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture
' Add a new worksheet to the document.
Using sheet As IXlSheet = document.CreateSheet()
' Specify the worksheet name.
sheet.Name = "Employees"
' Specify print settings for the worksheet.
SetupPageParameters(sheet)
' Generate worksheet columns.
GenerateColumns(sheet)
' Add the title to the documents exported to the XLSX and XLS formats.
If document.Options.DocumentFormat <> XlDocumentFormat.Csv Then GenerateTitle(sheet)
' Create the header row.
GenerateHeaderRow(sheet)
Dim firstDataRowIndex As Integer = sheet.CurrentRowIndex
' Create the data rows.
For i As Integer = 0 To employees.Count - 1
GenerateDataRow(sheet, employees(i), i + 1 = employees.Count)
Next
' Specify the data range to be printed.
sheet.PrintArea = sheet.DataRange
' Create data validation criteria for the documents exported to the XLSX and XLS formats.
If document.Options.DocumentFormat <> XlDocumentFormat.Csv Then GenerateDataValidations(sheet, firstDataRowIndex)
End Using
' Create the hidden worksheet containing source data for the data validation drop-down list.
If document.Options.DocumentFormat <> XlDocumentFormat.Csv Then
Using sheet As IXlSheet = document.CreateSheet()
sheet.Name = "Departments"
sheet.VisibleState = XlSheetVisibleState.Hidden
For Each department As String In departments
Using row As IXlRow = sheet.CreateRow()
Using cell As IXlCell = row.CreateCell()
cell.Value = department
End Using
End Using
Next
End Using
End If
End Sub
Private Sub GenerateColumns(ByVal sheet As IXlSheet)
' Create the "Employee ID" column and set its width.
Using column As IXlColumn = sheet.CreateColumn()
column.WidthInPixels = 110
End Using
' Create the "Employee Name" column and set its width.
Using column As IXlColumn = sheet.CreateColumn()
column.WidthInPixels = 200
End Using
Dim numberFormat As XlNumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"
' Create the "Salary" and "Bonus" columns and set the specific number format for their cells.
For i As Integer = 0 To 2 - 1
Using column As IXlColumn = sheet.CreateColumn()
column.WidthInPixels = 100
column.ApplyFormatting(numberFormat)
End Using
Next
' Create the "Department" column and set its width.
Using column As IXlColumn = sheet.CreateColumn()
column.WidthInPixels = 140
End Using
End Sub
Private Sub GenerateTitle(ByVal sheet As IXlSheet)
' Specify formatting settings for the document title.
Dim formatting As XlCellFormatting = New XlCellFormatting()
formatting.Font = New XlFont()
formatting.Font.Name = "Calibri Light"
formatting.Font.SchemeStyle = XlFontSchemeStyles.None
formatting.Font.Size = 24
formatting.Font.Color = XlColor.FromTheme(XlThemeColor.Dark1, 0.35)
formatting.Border = New XlBorder()
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.35)
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium
' Add the document title.
Using row As IXlRow = sheet.CreateRow()
Using cell As IXlCell = row.CreateCell()
cell.Value = "LIST OF EMPLOYEES"
cell.Formatting = formatting
End Using
' Create four empty cells with the title formatting.
row.BlankCells(4, formatting)
End Using
' Skip one row before starting to generate data rows.
sheet.SkipRows(1)
End Sub
Private Sub GenerateHeaderRow(ByVal sheet As IXlSheet)
Dim columnNames As String() = New String() {"Employee ID", "Employee Name", "Salary", "Bonus", "Department"}
' Create the header row and set its height.
Using row As IXlRow = sheet.CreateRow()
row.HeightInPixels = 28
' Create required cells in the header row and apply specific formatting settings to them.
For Each columnName As String In columnNames
Using cell As IXlCell = row.CreateCell()
cell.Value = columnName
cell.ApplyFormatting(headerRowFormatting)
End Using
Next
End Using
End Sub
Private Sub GenerateDataRow(ByVal sheet As IXlSheet, ByVal employee As EmployeeData, ByVal isLastRow As Boolean)
' Create the data row to display the employee's information.
Using row As IXlRow = sheet.CreateRow()
row.HeightInPixels = 28
' Specify formatting settings to be applied to the data rows to shade alternate rows.
Dim formatting As XlCellFormatting = New XlCellFormatting()
formatting.CopyFrom(If(row.RowIndex Mod 2 = 0, evenRowFormatting, oddRowFormatting))
' Set the bottom border for the last data row.
If isLastRow Then
formatting.Border = New XlBorder()
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0)
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium
End If
' Create the cell containing the employee's ID.
Using cell As IXlCell = row.CreateCell()
cell.Value = employee.Id
cell.ApplyFormatting(formatting)
End Using
' Create the cell containing the employee's name.
Using cell As IXlCell = row.CreateCell()
cell.Value = employee.Name
cell.ApplyFormatting(formatting)
End Using
' Create the cell containing the employee's salary.
Using cell As IXlCell = row.CreateCell()
cell.Value = employee.Salary
cell.ApplyFormatting(formatting)
End Using
' Create the cell containing information about bonuses.
Using cell As IXlCell = row.CreateCell()
cell.Value = employee.Bonus
cell.ApplyFormatting(formatting)
End Using
' Create the cell containing the department name.
Using cell As IXlCell = row.CreateCell()
cell.Value = employee.Department
cell.ApplyFormatting(formatting)
End Using
End Using
End Sub
Private Sub SetupPageParameters(ByVal sheet As IXlSheet)
' Specify the header and footer for the odd-numbered pages.
sheet.HeaderFooter.OddHeader = XlHeaderFooter.FromLCR("NorthWind Inc.", Nothing, XlHeaderFooter.Date)
sheet.HeaderFooter.OddFooter = XlHeaderFooter.FromLCR("List of employees", Nothing, XlHeaderFooter.PageNumber & " of " & XlHeaderFooter.PageTotal)
' Specify page margins.
sheet.PageMargins = New XlPageMargins()
sheet.PageMargins.PageUnits = XlPageUnits.Centimeters
sheet.PageMargins.Left = 2.0
sheet.PageMargins.Right = 1.0
sheet.PageMargins.Top = 1.4
sheet.PageMargins.Bottom = 1.4
sheet.PageMargins.Header = 0.7
sheet.PageMargins.Footer = 0.7
' Specify page settings.
sheet.PageSetup = New XlPageSetup()
' Select the paper size.
sheet.PageSetup.PaperKind = PaperKind.A4
' Scale the print area to fit to one page wide.
sheet.PageSetup.FitToPage = True
sheet.PageSetup.FitToWidth = 1
sheet.PageSetup.FitToHeight = 0
End Sub
Private Sub GenerateDataValidations(ByVal sheet As IXlSheet, ByVal firstDataRowIndex As Integer)
' Restrict data entry in the "Employee ID" column using criteria calculated by a worksheet formula (Employee ID must be a 5-digit number).
Dim validation As XlDataValidation = New XlDataValidation()
validation.Ranges.Add(XlCellRange.FromLTRB(0, firstDataRowIndex, 0, sheet.CurrentRowIndex - 1))
validation.Type = XlDataValidationType.Custom
validation.Criteria1 = String.Format("=AND(ISNUMBER(A{0}),LEN(A{0})=5)", firstDataRowIndex + 1)
validation.InputPrompt = "Please enter a 5-digit number."
validation.PromptTitle = "Employee ID"
sheet.DataValidations.Add(validation)
' Restrict data entry in the "Salary" column to a whole number from 600 to 2000.
validation = New XlDataValidation()
validation.Ranges.Add(XlCellRange.FromLTRB(2, firstDataRowIndex, 2, sheet.CurrentRowIndex - 1))
validation.Type = XlDataValidationType.Whole
validation.Operator = XlDataValidationOperator.Between
validation.Criteria1 = 600
validation.Criteria2 = 2000
' Specify the error message.
validation.ErrorMessage = "Salary must be greater than $600 and less than $2000."
validation.ErrorTitle = "Warning"
validation.ErrorStyle = XlDataValidationErrorStyle.Warning
' Specify the input message.
validation.InputPrompt = "Please enter a whole number in the range 600...2000."
validation.PromptTitle = "Salary"
validation.ShowErrorMessage = True
validation.ShowInputMessage = True
sheet.DataValidations.Add(validation)
' Restrict data entry in the "Bonus" column to a decimal number within the specified limits (bonus cannot be greater than 10% of the salary.)
validation = New XlDataValidation()
validation.Ranges.Add(XlCellRange.FromLTRB(3, firstDataRowIndex, 3, sheet.CurrentRowIndex - 1))
validation.Type = XlDataValidationType.Whole
validation.Operator = XlDataValidationOperator.Between
validation.Criteria1 = 0
validation.Criteria2 = String.Format("=C{0}*0.1", firstDataRowIndex + 1)
' Specify the error message.
validation.ErrorMessage = "Bonus cannot be greater than 10% of the salary."
validation.ErrorTitle = "Information"
validation.ErrorStyle = XlDataValidationErrorStyle.Information
validation.ShowErrorMessage = True
sheet.DataValidations.Add(validation)
' Restrict data entry in the "Department" column to values in a drop-down list obtained from the cell range in the hidden "Departments" worksheet.
validation = New XlDataValidation()
validation.Ranges.Add(XlCellRange.FromLTRB(4, firstDataRowIndex, 4, sheet.CurrentRowIndex - 1))
validation.Type = XlDataValidationType.List
Dim sourceRange As XlCellRange = XlCellRange.FromLTRB(0, 0, 0, departments.Count - 1).AsAbsolute()
sourceRange.SheetName = "Departments"
validation.Criteria1 = sourceRange
sheet.DataValidations.Add(validation)
End Sub
End Class
End Namespace