Worksheet Manipulation(C#)

 
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);

WorkBook anotherWorkbook = 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
int option =
WorkBook.sheetProtectionAllowFormatCells | WorkBook.sheetProtectionAllowUseAutoFilter;
//set sheet protection options
workBook.setSheetProtected(0, option, "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);