Worksheet Manipulation(VB.Net)

 
top
Add/Remove sheet

add worksheet or remove worksheet from the workbook.

'set or get the number of worksheets in the current workbook
'Worksheets are added to or deleted from the end of the current list of worksheets.
workBook.NumSheets = 2

'Inserts one or more worksheets at the specified location
'Sheets are indexed from left to right beginning with 0
workBook.insertSheets(0, 1)

'Deletes one or more worksheets from the specified location
'Sheets are indexed from left to right beginning with 0
workBook.deleteSheets(0, 1)

 
top
Hide/Unhide sheet

Hide or unhide the sheet(Must have one sheet shown)

'the hidden state for the current selected sheet.
'hidden state:SheetStateShown,SheetStateHidden,SheetStateVeryHidden
workBook.SheetHidden = WorkBook.SheetStateVeryHidden

'show the hidden sheet
workBook.SheetHidden = WorkBook.SheetStateShown

 
top
Copy & Move Worksheets

Copy and move worksheets within(between) workbooks

'copy the selected sheet to the specified location
workBook.copySheet(1)

Dim anotherWorkbook As New WorkBook

'copy sheet from anotherWorkbook to current workbook
workBook.CopySheetFromBook(anotherWorkbook, 0, 1)

 
top
Freeze Panes

freeze panes in the worksheet

'Sets frozen panes at the specified position
workBook.freezePanes(0, 0, 8, 6, false)

 
top
Display/Hide sheet options(tabs,scroll bars,grid line,row/column headers)

Display/Hide the worksheet options like tabs,scroll bars,grid line,row/column headers

'the visibility or position of the sheet name tabs on a workbook.
'TabsOff,TabsBottom,TabsTop
workBook.ShowTabs = workBook.TabsTop

'the mode for the horizontal scroll bar.
'ShowAutomatic,ShowOff,ShowOn
workBook.ShowHScrollBar = workBook.ShowAutomatic
'the mode for the vertical scroll bar.
'ShowAutomatic,ShowOff,ShowOn
workBook.ShowVScrollBar = workBook.ShowOff

'show/hide grid lines
workBook.ShowGridLines = False

'show or hide row and column headers
workBook.ShowRowColHeaders = False

 
top
Page Break Preview

switch worksheet in Normal View or Page Break Preview

'sheet view type
'SheetNormalView,SheetPageLayoutView,SheetPageBreakPreView
workBook.SheetViewType = WorkBook.SheetPageBreakPreView

 
top
Worksheet protection

enable/disable protection for worksheet

'combine the protection options
Dim protectOption As Integer = workBook.sheetProtectionAllowFormatCells + workBook.sheetProtectionAllowUseAutoFilter
'set sheet protection options
workBook.setSheetProtected(0, protectOption, "pass")

'enable protection for worksheet
'in default protection option
workBook.setSheetProtection(0, "pass", True)

 
top
Row Height & Column Width

adjust row height and column width

'set row height
workBook.setRowHeight(1, 25 * 1440 / 256)
'auto resize the row 6
workBook.setRowHeightAuto(5, True)

'set column width
workBook.setColWidth(1, 25 * 256)
'auto resize the column A
workBook.setColWidthAutoSize(0, True)