Worksheet Interface
Namespace: DevExpress.Spreadsheet
Assembly: DevExpress.Spreadsheet.v20.1.Core.dll
NuGet Package: DevExpress.Spreadsheet.Core
NuGet Packages: DevExpress.Spreadsheet.Core, DevExpress.WindowsDesktop.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:
-
Returns the worksheet collection for a non-visual Workbook.
-
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.Rows | Returns the collection of worksheet rows. | Access a Row or Column |
| Worksheet.Columns | Returns the collection of worksheet columns. | Access a Row or Column |
| Worksheet.Cells | Returns the cell collection. | Access a Cell in a Worksheet |
| Worksheet.Range | Provides access to a cell range. | Access a Cell Range in a Worksheet |
| Worksheet.Charts | Provides access to the collection of all charts in a worksheet. | Create a Chart |
| Worksheet.Pictures | Returns the picture collection. | Insert a Picture |
| Worksheet.Shapes | Returns all drawing objects embedded in a worksheet: shapes, pictures, and charts. | Create a Shape |
| Worksheet.Comments | Returns the collection of comments attached to worksheet cells. | Add a Comment To a Cell |
| Worksheet.Hyperlinks | Returns the hyperlink collection. | Add a Hyperlink to a Cell |
| Worksheet.Tables | Provides access to the collection of worksheet tables. | Create a Table |
| Worksheet.PivotTables | Provides access to the collection of pivot tables. | Create a Pivot Table |
| Worksheet.DefinedNames | 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 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 an active 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 an active 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.
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.IsProtected value before the method call.
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.