-
Notifications
You must be signed in to change notification settings - Fork 5
/
RowAndColumnActions.vb
217 lines (169 loc) · 7.32 KB
/
RowAndColumnActions.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
Imports System
Imports DevExpress.Spreadsheet
Imports System.Drawing
Namespace SpreadsheetControl_API
Public NotInheritable Class RowAndColumnActions
Private Sub New()
End Sub
#Region "Actions"
Public Shared InsertRowsColumnsAction As Action(Of IWorkbook) = AddressOf InsertRowsColumns
Public Shared DeleteRowsColumnsAction As Action(Of IWorkbook) = AddressOf DeleteRowsColumns
Public Shared CopyRowsColumnsAction As Action(Of IWorkbook) = AddressOf CopyRowsColumns
Public Shared ShowHideRowsColumnsAction As Action(Of IWorkbook) = AddressOf ShowHideRowsColumns
Public Shared SpecifyRowsHeightColumnsWidthAction As Action(Of IWorkbook) = AddressOf SpecifyRowsHeightColumnsWidth
#End Region
Private Shared Sub InsertRowsColumns(ByVal workbook As IWorkbook)
workbook.BeginUpdate()
Try
Dim worksheet As Worksheet = workbook.Worksheets(0)
' Populate cells with data.
For i As Integer = 0 To 9
worksheet.Cells(i, 0).Value = i + 1
worksheet.Cells(0, i).Value = i + 1
Next i
' #Region "#InsertRows"
' Insert a new row 3.
worksheet.Rows("3").Insert()
' Insert a new row into the worksheet at the fifth position.
worksheet.Rows.Insert(4)
' Insert five rows into the worksheet at the ninth position.
worksheet.Rows.Insert(8, 5)
' Insert two rows above the "L15:M16" cell range.
worksheet.InsertCells(worksheet.Range("L15:M16"), InsertCellsMode.EntireRow)
' #End Region ' #InsertRows
' #Region "#InsertColumns"
' Insert a new column C.
worksheet.Columns("C").Insert()
' Insert a new column into the worksheet at the fifth position.
worksheet.Columns.Insert(4)
' Insert three columns into the worksheet at the seventh position.
worksheet.Columns.Insert(6, 3)
' Insert two columns to the left of the "L15:M16" cell range.
worksheet.InsertCells(worksheet.Range("L15:M16"), InsertCellsMode.EntireColumn)
' #End Region ' #InsertColumns
Finally
workbook.EndUpdate()
End Try
End Sub
Private Shared Sub DeleteRowsColumns(ByVal workbook As IWorkbook)
workbook.BeginUpdate()
Try
Dim worksheet As Worksheet = workbook.Worksheets("Sheet1")
' Fill cells with data.
For i As Integer = 0 To 14
worksheet.Cells(i, 0).Value = i + 1
worksheet.Cells(0, i).Value = i + 1
Next i
' #Region "#DeleteRows"
' Delete the second row from the worksheet.
worksheet.Rows(1).Delete()
' Delete the third row from the worksheet.
worksheet.Rows.Remove(2)
' Delete three rows from the worksheet starting from the tenth row.
worksheet.Rows.Remove(9, 3)
' Delete a row that contains the "B2" cell.
worksheet.DeleteCells(worksheet.Cells("B2"), DeleteMode.EntireRow)
' #End Region ' #DeleteRows
' #Region "#DeleteColumns"
' Delete the second column from the worksheet.
worksheet.Columns(1).Delete()
' Delete the third column from the worksheet.
worksheet.Columns.Remove(2)
' Delete three columns from the worksheet starting from the tenth column.
worksheet.Columns.Remove(9, 3)
' Delete a column that contains the "B2" cell.
worksheet.DeleteCells(worksheet.Cells("B2"), DeleteMode.EntireColumn)
' #End Region ' #DeleteColumns
Finally
workbook.EndUpdate()
End Try
End Sub
Private Shared Sub CopyRowsColumns(ByVal workbook As IWorkbook)
workbook.BeginUpdate()
Try
Dim worksheet As Worksheet = workbook.Worksheets(0)
' Modify the 2nd row.
worksheet.Cells("A2").Value = "Row 2"
worksheet.Rows("2").Height = 50
worksheet.Rows("2").Alignment.Vertical = SpreadsheetVerticalAlignment.Center
worksheet.Rows("2").FillColor = Color.LightCyan
' Modify the "B" column.
worksheet.Cells("B1").Value = "ColumnB"
worksheet.Columns("B").Borders.SetOutsideBorders(Color.CadetBlue, BorderLineStyle.Thick)
' #Region "#CopyRowsColumns"
' Copy all data from the 2nd row to the 5th row.
worksheet.Rows("5").CopyFrom(worksheet.Rows("2"))
' Copy only borders from the "B" column to the "E" column.
worksheet.Columns("E").CopyFrom(worksheet.Columns("B"), PasteSpecial.Borders)
' #End Region ' #CopyRowsColumns
Finally
workbook.EndUpdate()
End Try
End Sub
Private Shared Sub ShowHideRowsColumns(ByVal workbook As IWorkbook)
' #Region "#ShowHideRowsColumns"
Dim worksheet As Worksheet = workbook.Worksheets(0)
' Hide the 8th row of the worksheet.
worksheet.Rows(7).Visible = False
' Hide the 4th column of the worksheet.
worksheet.Columns(3).Visible = False
' #End Region ' #ShowHideRowsColumns
End Sub
Private Shared Sub SpecifyRowsHeightColumnsWidth(ByVal workbook As IWorkbook)
workbook.BeginUpdate()
Try
Dim worksheet As Worksheet = workbook.Worksheets(0)
worksheet.Range("B1:J1").Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center
worksheet.Cells("B1").Value = "30 characters"
worksheet.Cells("C1").Value = "15 mm"
worksheet.Cells("E1").Value = "100 pt"
worksheet.Cells("F1").Value = "70 pt"
worksheet.Cells("G1").Value = "70 pt"
worksheet.Cells("H1").Value = "70 pt"
worksheet.Cells("J1").Value = "30 characters"
worksheet.Cells("K1").Value = "15 mm"
worksheet.Cells("A3").Value = "50 pt"
worksheet.Cells("A5").Value = "2"""
worksheet.Cells("A7").Value = "50 pt"
Dim rowHeightValues As Formatting = worksheet.Range("A3:A7").BeginUpdateFormatting()
rowHeightValues.Alignment.RotationAngle = 90
rowHeightValues.Alignment.Vertical = SpreadsheetVerticalAlignment.Center
rowHeightValues.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center
worksheet.Range("A3:A7").EndUpdateFormatting(rowHeightValues)
' #Region "#RowHeight"
' Set the height of the 3rd row to 50 points
workbook.Unit = DevExpress.Office.DocumentUnit.Point
worksheet.Rows(2).Height = 50
' Set the height of the row that contains the "C5" cell to 2 inches.
workbook.Unit = DevExpress.Office.DocumentUnit.Inch
worksheet.Cells("C5").RowHeight = 2
' Set the height of the 7th row to the height of the 3rd row.
worksheet.Rows("7").Height = worksheet.Rows("3").Height
' Set the default row height to 30 points.
workbook.Unit = DevExpress.Office.DocumentUnit.Point
worksheet.DefaultRowHeight = 30
' #End Region ' #RowHeight
' #Region "#ColumnWidth"
' Set the "B" column width to 30 characters of the default font that is specified by the Normal style.
worksheet.Columns("B").WidthInCharacters = 30
' Set the "C" column width to 15 millimeters.
workbook.Unit = DevExpress.Office.DocumentUnit.Millimeter
worksheet.Columns("C").Width = 15
' Set the width of the column that contains the "E15" cell to 100 points.
workbook.Unit = DevExpress.Office.DocumentUnit.Point
worksheet.Cells("E15").ColumnWidth = 100
' Set the width of all columns that contain the "F4:H7" cell range (the "F", "G" and "H" columns) to 70 points.
worksheet.Range("F4:H7").ColumnWidth = 70
' Set the "J" column width to the "B" column width value.
worksheet.Columns("J").Width = worksheet.Columns("B").Width
' Copy the "C" column width value and assign it to the "K" column width.
worksheet.Columns("K").CopyFrom(worksheet.Columns("C"), PasteSpecial.ColumnWidths)
' Set the default column width to 40 pixels.
worksheet.DefaultColumnWidthInPixels = 40
' #End Region ' #ColumnWidth
Finally
workbook.EndUpdate()
End Try
End Sub
End Class
End Namespace