Worksheet Manipulation(VB.Net)

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)

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

Copy & Move Worksheets

Copy and move worksheets within(between) workbooks

'copy the selected sheet to the specified location

Dim anotherWorkbook As New WorkBook

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

Freeze Panes

freeze panes in the worksheet

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

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.
workBook.ShowTabs = workBook.TabsTop

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

'show/hide grid lines
workBook.ShowGridLines = False

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

Page Break Preview

switch worksheet in Normal View or Page Break Preview

'sheet view type
workBook.SheetViewType = WorkBook.SheetPageBreakPreView

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)

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)