Module: Asposecellsjava::RowsAndColumns

Defined in:
lib/asposecellsjava/rowsandcolumns.rb

Instance Method Summary collapse

Instance Method Details

#autofit_column ⇒ Object 



309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 309

def autofit_column()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Book1.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)

    # Auto-fitting the 4th column of the worksheet
    worksheet.autoFitColumn(3)

    # Auto-fitting the 4th column of the worksheet based on the contents in a range of
    # cells (from 1st to 9th row) within the column
    #worksheet.autoFitColumn(3,0,8) #Uncomment this line if you to do AutoFit Column in a Range of Cells. Also, comment line 310.

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Autofit Column.xls")

    puts "Autofit Column Successfully."
end

#autofit_row ⇒ Object 



287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 287

def autofit_row()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Book1.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)

    # Auto-fitting the 3rd row of the worksheet
    worksheet.autoFitRow(2)

    # Auto-fitting the 3rd row of the worksheet based on the contents in a range of
    # cells (from 1st to 9th column) within the row
    #worksheet.autoFitRow(2,0,8) # Uncomment this line if you to do AutoFit Row in a Range of Cells. Also, comment line 288.

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Autofit Row.xls")

    puts "Autofit Row Successfully."
end

#copy_columns ⇒ Object 



350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 350

def copy_columns()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)

    # Put some data into header rows (A1:A4)
    i = 0
    while i < 5
        worksheet.getCells().get(i, 0).setValue("Header Row #{i}")
        i +=1
    end

    # Put some detail data (A5:A999)
    i = 5
    while i < 1000
        worksheet.getCells().get(i, 0).setValue("Detail Row #{i}")
        i +=1
    end

    # Create another Workbook.
    workbook1 = Rjb::import('com.aspose.cells.Workbook').new

    # Get the first worksheet in the book.
    worksheet1 = workbook1.getWorksheets().get(0)

    # Copy the first column from the first worksheet of the first workbook into
    # the first worksheet of the second workbook.
    worksheet1.getCells().copyColumn(worksheet.getCells(),0,2)

    # Autofit the column.
    worksheet1.autoFitColumn(2)

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Copy Columns.xls")

    puts "Copy Columns Successfully."
end

#copy_rows ⇒ Object 



331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 331

def copy_rows()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Book1.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)

    # Copy the second row with data, formattings, images and drawing objects
    # to the 12th row in the worksheet.
    worksheet.getCells().copyRow(worksheet.getCells(),1,11);

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Copy Rows.xls")

    puts "Copy Rows Successfully."
end

#delete_column ⇒ Object 



143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 143

def delete_column()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Book1.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)

    # Deleting a column from the worksheet at 2nd position
    worksheet.getCells().deleteColumns(1,1,true)

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Delete Column.xls")

    puts "Delete Column Successfully."
end

#delete_multiple_rows ⇒ Object 



107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 107

def delete_multiple_rows()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Book1.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)

    # Deleting 10 rows from the worksheet starting from 3rd row
    worksheet.getCells().deleteRows(2,10,true)

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Delete Multiple Rows.xls")

    puts "Delete Multiple Rows Successfully."
end

#delete_row ⇒ Object 



89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 89

def delete_row()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Book1.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)

    # Deleting 3rd row from the worksheet
    worksheet.getCells().deleteRows(2,1,true)

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Delete Row.xls")

    puts "Delete Row Successfully."
end

#group_rows_columns ⇒ Object 



205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 205

def group_rows_columns()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Book1.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)
    cells = worksheet.getCells()

    # Grouping first six rows (from 0 to 5) and making them hidden by passing true
    cells.groupRows(0,5,true)

    # Grouping first three columns (from 0 to 2) and making them hidden by passing true
    cells.groupColumns(0,2,true)

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Group Rows And Columns.xls")

    puts "Group Rows And Columns Successfully."
end

#hide_rows_columns ⇒ Object 



161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 161

def hide_rows_columns()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Book1.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)
    cells = worksheet.getCells()

    # Hiding the 3rd row of the worksheet
    cells.hideRow(2)

    # Hiding the 2nd column of the worksheet
    cells.hideColumn(1)

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Hide Rows And Columns.xls")

    puts "Hide Rows And Columns Successfully."
end

#initialize ⇒ Object 



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
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 3

def initialize()
    # Inserting a Row
    insert_row()

    # Inserting Multiple Rows
    insert_multiple_rows()

    # Deleting a Row
    delete_row()

    # Deleting Multiple Rows
    delete_multiple_rows()

    # Inseting one or Multiple Columns
    insert_column()

    # Deleting a Column
    delete_column()

    # Hiding Rows and Columns
    hide_rows_columns()

    # Showing Rows and Columns
    unhide_rows_columns()

    # Grouping Rows & Columns
    group_rows_columns()

    # Ungrouping Rows & Columns
    ungroup_rows_columns()

    # Setting the Row Height
    set_row_height()

    # Setting the Width of a Column
    set_column_width()

    # Auto Fit Row
    autofit_row()

    # Auto Fit Column
    autofit_column()

    # Copying Rows
    copy_rows()

    # Copying Columns
    copy_columns()
end

#insert_column ⇒ Object 



125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 125

def insert_column()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Book1.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)

    # Inserting a column into the worksheet at 2nd position
    worksheet.getCells().insertColumns(1,1)

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Insert Column.xls")

    puts "Insert Column Successfully."
end

#insert_multiple_rows ⇒ Object 



71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 71

def insert_multiple_rows()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Book1.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)

    # Inserting a row into the worksheet at 3rd position
    worksheet.getCells().insertRows(2,10)

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Insert Multiple Rows.xls")

    puts "Insert Multiple Rows Successfully."
end

#insert_row ⇒ Object 



53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 53

def insert_row()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Book1.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)

    # Inserting a row into the worksheet at 3rd position
    worksheet.getCells().insertRows(2,1)

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Insert Row.xls")

    puts "Insert Row Successfully."
end

#set_column_width ⇒ Object 



268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 268

def set_column_width()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Book1.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)
    cells = worksheet.getCells()

    # Setting the width of the second column to 17.5
    cells.setColumnWidth(1, 17.5)

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Set Column Width.xls")

    puts "Set Column Width Successfully."
end

#set_row_height ⇒ Object 



249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 249

def set_row_height()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Book1.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)
    cells = worksheet.getCells()

    # Setting the height of the second row to 13
    cells.setRowHeight(1, 13)

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Set Row Height.xls")

    puts "Set Row Height Successfully."
end

#ungroup_rows_columns ⇒ Object 



227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 227

def ungroup_rows_columns()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Group Rows And Columns.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)
    cells = worksheet.getCells()

    # Ungrouping first six rows (from 0 to 5)
    cells.ungroupRows(0,5)

    # Ungrouping first three columns (from 0 to 2)
    cells.ungroupColumns(0,2)

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Ungroup Rows And Columns.xls")

    puts "Ungroup Rows And Columns Successfully."
end

#unhide_rows_columns ⇒ Object 



183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
 
# File 'lib/asposecellsjava/rowsandcolumns.rb', line 183

def unhide_rows_columns()
    data_dir = File.dirname(File.dirname(File.dirname(__FILE__))) + '/data/'
    
    # Instantiating a Workbook object by excel file path
    workbook = Rjb::import('com.aspose.cells.Workbook').new(data_dir + 'Book1.xls')

    # Accessing the first worksheet in the Excel file
    worksheet = workbook.getWorksheets().get(0)
    cells = worksheet.getCells()

    # Unhiding the 3rd row and setting its height to 13.5
    cells.unhideRow(2,13.5)

    # Unhiding the 2nd column and setting its width to 8.5
    cells.unhideColumn(1,8.5)

    # Saving the modified Excel file in default (that is Excel 2003) format
    workbook.save(data_dir + "Unhide Rows And Columns.xls")

    puts "Unhide Rows And Columns Successfully."
end