Worksheet Interface
Namespace: DevExpress.Spreadsheet
Assembly: DevExpress.Spreadsheet.v24.2.Core.dll
NuGet Package: DevExpress.Spreadsheet.Core
#Declaration
#Related API Members
The following members return Worksheet objects:
#Remarks
A worksheet is a single page within a document. It is divided into rows and columns and is used to store and edit spreadsheet data. Use one of the following properties to access a collection of worksheets in a workbook:
- Workbook.Worksheets
- Returns the worksheet collection for a non-visual Workbook.
- IWorkbook.Worksheets
- Returns the worksheet collection for the Spreadsheet control’s document.
#Worksheet Content
The following table lists properties used to access and manage different worksheet elements:
Property | Description | Example |
---|---|---|
Worksheet. |
Returns the collection of worksheet rows. | Access a Row or Column |
Worksheet. |
Returns the collection of worksheet columns. | Access a Row or Column |
Worksheet. |
Returns the cell collection. | Access a Cell in a Worksheet |
Worksheet. |
Provides access to a cell range. | Access a Cell Range in a Worksheet |
Worksheet. |
Provides access to the collection of all charts in a worksheet. | Create a Chart |
Worksheet. |
Returns the picture collection. | Insert a Picture |
Worksheet. |
Returns all drawing objects embedded in a worksheet: shapes, pictures, and charts. | Create a Shape |
Worksheet. |
Returns the collection of comments attached to worksheet cells. | Add a Comment To a Cell |
Worksheet. |
Returns the hyperlink collection. | Add a Hyperlink to a Cell |
Worksheet. |
Provides access to the collection of worksheet tables. | Create a Table |
Worksheet. |
Provides access to the collection of pivot tables. | Create a Pivot Table |
Worksheet. |
Provides access to worksheet-level defined names. | Create a Defined Name |
#Access a Worksheet
Use the WorksheetCollection.Item property to access a worksheet in a workbook.
#Obtain the Worksheet at the Specified Index
using DevExpress.Spreadsheet;
// ...
// Access the worksheet collection.
WorksheetCollection worksheets = workbook.Worksheets;
// Access the first worksheet.
Worksheet worksheet1 = workbook.Worksheets[0];
A worksheet index is zero-based. It specifies the worksheet position within a collection.
#Obtain the Worksheet with a Given Name
using DevExpress.Spreadsheet;
// ...
// Access the worksheet collection.
WorksheetCollection worksheets = workbook.Worksheets;
// Access the worksheet with the specified name.
Worksheet worksheet2 = workbook.Worksheets["MainSheet"];
A worksheet name is unique within the collection and is shown on a worksheet tab.
#Add a New Worksheet
Use the WorksheetCollection.Add method to add a worksheet to the end of the worksheet collection.
using DevExpress.Spreadsheet;
// ...
// Add a worksheet with the default name.
// Default names are "Sheet1", "Sheet2", ..., "SheetN".
workbook.Worksheets.Add();
// Add new worksheets with the specified names.
workbook.Worksheets.Add().Name = "TestSheet1";
workbook.Worksheets.Add("TestSheet2");
The WorksheetCollection.Insert method allows you to insert a worksheet at the specified position in the collection.
using DevExpress.Spreadsheet;
// ...
// Insert a worksheet at the second position in the workbook.
workbook.Worksheets.Insert(1, "TestSheet3");
// Insert a worksheet with the default name at the fourth position in the workbook.
workbook.Worksheets.Insert(3);
#Rename a Worksheet
Use the Worksheet.Name property to change the worksheet name.
#Set an Active Worksheet
Use the WorksheetCollection.ActiveWorksheet property to specify the active worksheet in a workbook.
using DevExpress.Spreadsheet;
// ...
// Set "Sheet2" as the active worksheet.
workbook.Worksheets.ActiveWorksheet = workbook.Worksheets["Sheet2"];
#Select a Worksheet
Use the Worksheet.ActiveView.IsSelected property to select a worksheet in a workbook.
The following example selects all odd sheets in the document:
SheetCollection sheets = workbook.Sheets;
for (int i = sheets.Count - 1; i >= 0; i--)
if (i % 2 == 0)
sheets[i].ActiveView.IsSelected = true;
#Change a Worksheet’s Appearance
Use the Worksheet.ActiveView property to access display settings for a worksheet.
#Hide Worksheet Elements
WorksheetView.ShowGridlines - shows or hides gridlines.
WorksheetView.ShowHeadings - shows or hides row and column headings.
// Hide gridlines on the first worksheet.
workbook.Worksheets[0].ActiveView.ShowGridlines = false;
// Hide row and column headings on the first worksheet.
workbook.Worksheets[0].ActiveView.ShowHeadings = false;
#Change the Zoom Level
Use the WorksheetView.Zoom property to change the worksheet zoom percentage.
#Specify the Gridline Color
Use the WorksheetView.GridlineColor property to change the color of worksheet gridlines.
#Specify the Tab Color
Use the WorksheetView.TabColor property to change the color of a worksheet tab.
// Change the tab color for the first worksheet.
workbook.Worksheets[0].ActiveView.TabColor = Color.LightSkyBlue;
#Copy a Worksheet
Use the Worksheet.CopyFrom method to copy data from the specified worksheet to the current Worksheet
instance.
#Copy a Worksheet Within a Workbook
// Add a new worksheet to the workbook.
workbook.Worksheets.Add("Sheet1_Copy");
// Copy all information from "Sheet1"
// to the newly created worksheet.
workbook.Worksheets["Sheet1_Copy"].CopyFrom(workbook.Worksheets["Sheet1"]);
#Copy a Worksheet Between Workbooks
Important
You need a license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use this example in production code.
// Create a source workbook.
Workbook sourceWorkbook = new Workbook();
// Create a destination workbook.
Workbook destWorkbook = new Workbook();
// Add data to the first worksheet of the source workbook.
sourceWorkbook.Worksheets[0].Cells["A1"].Value = "A worksheet to copy";
sourceWorkbook.Worksheets[0].Cells["A1"].Font.Color = Color.ForestGreen;
// Copy the first worksheet of the source workbook
// to the destination workbook.
destWorkbook.Worksheets[0].CopyFrom(sourceWorkbook.Worksheets[0]);
#Move a Worksheet
Use the following methods to move a worksheet to another location in a workbook:
Worksheet.Move - moves a worksheet to a specific position in the document.
Worksheet.MoveAfter - positions a worksheet after the specified worksheet.
Worksheet.MoveBefore - positions a worksheet before the specified worksheet.
Worksheet.MoveToBeginning - moves a worksheet to the first position in the workbook.
Worksheet.MoveToEnd - moves a worksheet to the last position in the workbook.
// Move the first worksheet to the last position in the document.
workbook.Worksheets[0].MoveToEnd();
#Hide a Worksheet
Use the Worksheet.Visible or Worksheet.VisibilityType property to control the visibility of a worksheet.
// Hide the "Sheet2" worksheet.
// End users can unhide this worksheet from the user interface.
workbook.Worksheets["Sheet2"].Visible = false;
// Mark the "Sheet3" worksheet as "very hidden".
// End users cannot unhide this worksheet from the user interface.
workbook.Worksheets["Sheet3"].VisibilityType = WorksheetVisibilityType.VeryHidden;
Note
A workbook must contain at least one visible worksheet.
#Delete a Worksheet
Use the following methods to delete a worksheet:
WorksheetCollection.Remove - removes a specific worksheet from the collection.
WorksheetCollection.RemoveAt - removes the worksheet with the specified index from the collection.
Note
A workbook must contain at least one visible worksheet.
using DevExpress.Spreadsheet;
// ...
// Delete the first worksheet from the workbook.
workbook.Worksheets.RemoveAt(0);
// Delete the "Sheet2" worksheet from the workbook.
workbook.Worksheets.Remove(workbook.Worksheets["Sheet2"]);
#Print a Worksheet
Use the Worksheet.Print method to print a worksheet.
// Send the active worksheet to the default printer.
workbook.Worksheets.ActiveWorksheet.Print();
#Specify Printer Settings
To select a printer and specify printer settings, create a PrinterSettings class instance and pass it to the Worksheet.Print method.
using DevExpress.Spreadsheet;
using System.Drawing.Printing;
// ...
// Create an object that contains printer settings.
PrinterSettings printerSettings = new PrinterSettings();
// Specify that the first two pages should be printed.
printerSettings.PrintRange = PrintRange.SomePages;
printerSettings.FromPage = 1;
printerSettings.ToPage = 2;
// Set the number of copies to print.
printerSettings.Copies = 2;
// Print the active worksheet.
workbook.Worksheets.ActiveWorksheet.Print(printerSettings);
#Print Multiple Worksheets
The Workbook.Print method allows you to print multiple worksheets at once.
Important
You need a license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use this method in production code.
using DevExpress.Spreadsheet;
using System.Drawing.Printing;
// ...
// Create a new Workbook object.
Workbook workbook = new Workbook();
// Load a document from a file.
workbook.LoadDocument("Document.xlsx");
// Create an object that contains printer settings.
PrinterSettings printerSettings = new PrinterSettings();
// Define the printer to use.
printerSettings.PrinterName = "Microsoft Print to PDF";
printerSettings.PrintToFile = true;
printerSettings.PrintFileName = "PrintedDocument.pdf";
// Print specific worksheets in the document.
workbook.Print(printerSettings, "Sheet1", "Sheet2");
#Define Page Options
Use the Worksheet.ActiveView property to access and specify general page options.
- WorksheetView.Orientation
Sets the page orientation.
- WorksheetView.Margins
Defines page margins.
// Set the measurement unit to inches. workbook.Unit = DevExpress.Office.DocumentUnit.Inch; // Access page margins. Margins pageMargins = workbook.Worksheets[0].ActiveView.Margins; // Specify page margins. pageMargins.Left = 1; pageMargins.Top = 1.5F; pageMargins.Right = 1; pageMargins.Bottom = 0.8F; // Specify header and footer margins. pageMargins.Header = 1; pageMargins.Footer = 0.4F;
- WorksheetView.PaperKind
Specifies paper size.
// Select paper size. workbook.Worksheets[0].ActiveView.PaperKind = DevExpress.Drawing.Printing.DXPaperKind.A4;
Use the WorksheetView.SetCustomPaperSize method to specify custom paper size for a worksheet.
#Specify Print Options
The Worksheet.PrintOptions property allows you to access and configure print options, as shown in the following example:
// Access an object that contains print options.
WorksheetPrintOptions printOptions = worksheet.PrintOptions;
// Do not print gridlines.
printOptions.PrintGridlines = false;
// Scale the worksheet to fit within the width of one page.
printOptions.FitToPage = true;
printOptions.FitToWidth = 1;
// Print in black and white.
printOptions.BlackAndWhite = true;
// Print a dash instead of the cell error message.
printOptions.ErrorsPrintMode = ErrorsPrintMode.Dash;
#Protect a Worksheet
Use the Worksheet.Protect method to protect a worksheet. The WorksheetProtectionPermissions enumeration members allow you to specify actions that users can execute on the protected worksheet.
When protection is applied, worksheet cells become read-only. To allow users to edit a specific cell, set its Protection.Locked attribute to false.
Worksheet worksheet = workbook.Worksheets[0];
// Protect the worksheet.
// End users are only allowed to select worksheet cells.
if (!worksheet.IsProtected)
worksheet.Protect("password", WorksheetProtectionPermissions.Default);
Note
If a worksheet is already protected, the Protect method throws an exception. Check the Worksheet.
#Unprotect a Worksheet
Use the Worksheet.Unprotect method to remove worksheet protection.
Worksheet worksheet = workbook.Worksheets[0];
// Unprotect the worksheet.
if (worksheet.IsProtected) {
worksheet.Unprotect("password");
}
#Scroll a Worksheet
Use the Worksheet.ScrollTo method to scroll a worksheet to the specified column and row.
// Scroll the worksheet to the "C3" cell.
workbook.Worksheets[0].ScrollTo(worksheet("C3"));
Call the Worksheet.ScrollToColumn method to scroll a worksheet to a specific column.
Call the Worksheet.ScrollToRow method to scroll a worksheet to a specific row.
Note
If you scroll to a hidden column, the worksheet is scrolled to the first visible column to the right of the specified column.
If you scroll to a hidden row, the worksheet is scrolled to the first visible row below the specified row.