officefileapi-devexpress-dot-spreadsheet-d6c47043.md
Namespace : DevExpress.Spreadsheet
Assembly : DevExpress.Spreadsheet.v25.2.Core.dll
NuGet Package : DevExpress.Spreadsheet.Core
public interface Worksheet :
ExternalWorksheet,
Sheet
Public Interface Worksheet
Inherits ExternalWorksheet,
Sheet
The following members return Worksheet objects:
Show 15 links
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.WorksheetsReturns the worksheet collection for a non-visual Workbook.IWorkbook.WorksheetsReturns the worksheet collection for the Spreadsheet control’s document.
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 |
Use the WorksheetCollection.Item property to access a worksheet in a workbook.
using DevExpress.Spreadsheet;
// ...
// Access the worksheet collection.
WorksheetCollection worksheets = workbook.Worksheets;
// Access the first worksheet.
Worksheet worksheet1 = workbook.Worksheets[0];
Imports DevExpress.Spreadsheet
' ...
' Access the worksheet collection.
Dim worksheets As WorksheetCollection = workbook.Worksheets
' Access the first worksheet.
Dim worksheet1 As Worksheet = workbook.Worksheets(0)
A worksheet index is zero-based. It specifies the worksheet position within a collection.
using DevExpress.Spreadsheet;
// ...
// Access the worksheet collection.
WorksheetCollection worksheets = workbook.Worksheets;
// Access the worksheet with the specified name.
Worksheet worksheet2 = workbook.Worksheets["MainSheet"];
Imports DevExpress.Spreadsheet
' ...
' Access the worksheet collection.
Dim worksheets As WorksheetCollection = workbook.Worksheets
' Access the worksheet with the specified name.
Dim worksheet2 As Worksheet = workbook.Worksheets("MainSheet")
A worksheet name is unique within the collection and is shown on a worksheet tab.
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");
Imports 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);
Imports 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)
Use the Worksheet.Name property to change the worksheet name.
// Change the name of the second worksheet.
workbook.Worksheets[1].Name = "New Name";
' Change the name of the second worksheet.
workbook.Worksheets(1).Name = "New Name"
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"];
Imports DevExpress.Spreadsheet
' ...
' Set "Sheet2" as the active worksheet.
workbook.Worksheets.ActiveWorksheet = workbook.Worksheets("Sheet2")
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;
Dim sheets As SheetCollection = workbook.Sheets
For i As Integer = sheets.Count - 1 To 0 Step -1
If i Mod 2 = 0 Then
sheets(i).ActiveView.IsSelected = True
End If
Next i
Use the Worksheet.ActiveView property to access display settings for a worksheet.
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;
' 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
Use the WorksheetView.Zoom property to change the worksheet zoom percentage.
// Set the worksheet zoom level to 150%.
workbook.Worksheets[0].ActiveView.Zoom = 150;
' Set the worksheet zoom level to 150%.
workbook.Worksheets(0).ActiveView.Zoom = 150
Use the WorksheetView.GridlineColor property to change the color of worksheet gridlines.
workbook.Worksheets[0].ActiveView.GridlineColor = Color.Blue;
workbook.Worksheets(0).ActiveView.GridlineColor = Color.Blue
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;
' Change the tab color for the first worksheet.
workbook.Worksheets(0).ActiveView.TabColor = Color.LightSkyBlue
Use the Worksheet.CopyFrom method to copy data from the specified worksheet to the current Worksheet instance.
// 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"]);
' 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"))
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]);
' Create a source workbook.
Dim sourceWorkbook As New Workbook()
' Create a destination workbook.
Dim destWorkbook As 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))
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();
' Move the first worksheet to the last position in the document.
workbook.Worksheets(0).MoveToEnd()
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;
' 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.
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"]);
Imports 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"))
Use the Worksheet.Print method to print a worksheet.
// Send the active worksheet to the default printer.
workbook.Worksheets.ActiveWorksheet.Print();
' Send the active worksheet to the default printer.
workbook.Worksheets.ActiveWorksheet.Print()
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);
Imports DevExpress.Spreadsheet
Imports System.Drawing.Printing
' ...
' Create an object that contains printer settings.
Dim printerSettings As 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)
The Workbook.Print method allows you to print multiple worksheets simultaneously.
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");
Imports DevExpress.Spreadsheet
Imports System.Drawing.Printing
' ...
' Create a new Workbook object.
Dim workbook As New Workbook()
' Load a document from a file.
workbook.LoadDocument("Document.xlsx")
' Create an object that contains printer settings.
Dim printerSettings As 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")
Use the Worksheet.ActiveView property to access and specify general page options.
Sets the page orientation.
// Set the page orientation to landscape.
workbook.Worksheets[0].ActiveView.Orientation = PageOrientation.Landscape;
' Set the page orientation to landscape.
workbook.Worksheets(0).ActiveView.Orientation = PageOrientation.Landscape
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;
' Set the measurement unit to inches.
workbook.Unit = DevExpress.Office.DocumentUnit.Inch
' Access page margins.
Dim pageMargins As Margins = 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
Specifies paper size.
// Select paper size.
workbook.Worksheets[0].ActiveView.PaperKind = DevExpress.Drawing.Printing.DXPaperKind.A4;
' 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.
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;
' Access an object that contains print options.
Dim printOptions As WorksheetPrintOptions = 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
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);
Dim worksheet As Worksheet = workbook.Worksheets(0)
' Protect the worksheet.
' End users are only allowed to select worksheet cells.
If Not worksheet.IsProtected Then
worksheet.Protect("password", WorksheetProtectionPermissions.Default)
End If
Note
If a worksheet is already protected, the Protect method throws an exception. Check the Worksheet.IsProtected value before the method call.
Use the Worksheet.Unprotect method to remove worksheet protection.
Worksheet worksheet = workbook.Worksheets[0];
// Unprotect the worksheet.
if (worksheet.IsProtected) {
worksheet.Unprotect("password");
}
Dim worksheet As Worksheet = workbook.Worksheets(0)
' Unprotect the worksheet.
If worksheet.IsProtected Then
worksheet.Unprotect("password")
End If
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"));
' 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.
// Scroll the worksheet to the "E" column.
workbook.Worksheets[0].ScrollToColumn("E");
' Scroll the worksheet to the "E" column.
workbook.Worksheets(0).ScrollToColumn("E")
Call the Worksheet.ScrollToRow method to scroll a worksheet to a specific row.
// Scroll the worksheet to the second row.
workbook.Worksheets[0].ScrollToRow("2");
' Scroll the worksheet to the second row.
workbook.Worksheets(0).ScrollToRow("2")
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.
Show 47 items
Import(Object[,], Int32, Int32)
Import(Object[,], Int32, Int32, IDataValueConverter)
Import(Object[,], Int32, Int32, DataImportOptions)
Import(Int32[,], Int32, Int32)
Import(Int16[,], Int32, Int32)
Import(Int64[,], Int32, Int32)
Import(Boolean[,], Int32, Int32)
Import(Single[,], Int32, Int32)
Import(Double[,], Int32, Int32)
Import(Decimal[,], Int32, Int32)
Import(DateTime[,], Int32, Int32)
Import(String[,], Int32, Int32)
Import(String[,], Int32, Int32, DataImportOptions)
Import(Object[], Int32, Int32, Boolean)
Import(Object[], Int32, Int32, Boolean, IDataValueConverter)
Import(Object[], Int32, Int32, Boolean, DataImportOptions)
Import(Int32[], Int32, Int32, Boolean)
Import(Int16[], Int32, Int32, Boolean)
Import(Byte[], Int32, Int32, Boolean)
Import(Int64[], Int32, Int32, Boolean)
Import(Boolean[], Int32, Int32, Boolean)
Import(Single[], Int32, Int32, Boolean)
Import(Double[], Int32, Int32, Boolean)
Import(Decimal[], Int32, Int32, Boolean)
Import(DateTime[], Int32, Int32, Boolean)
Import(String[], Int32, Int32, Boolean)
Import(IEnumerable, Int32, Int32, Boolean)
Import(IEnumerable, Int32, Int32, Boolean, IDataValueConverter)
Import(IEnumerable, Int32, Int32, Boolean, DataImportOptions)
Import(Object, Int32, Int32, DataSourceImportOptions)
Import(DataTable, Boolean, Int32, Int32)
Import(DataTable, Boolean, Int32, Int32, IDataValueConverter)
Import(DataTable, Boolean, Int32, Int32, DataImportOptions)
Import(IDataReader, Boolean, Int32, Int32)
Import(IDataReader, Boolean, Int32, Int32, IDataValueConverter)
Import(IDataReader, Boolean, Int32, Int32, DataImportOptions)
CreateDataTableExporter(CellRange, DataTable, Boolean)
CreateDataTable(CellRange, Boolean)
CreateDataTable(CellRange, Boolean, Boolean)
CreateThumbnail(Int32, Int32, WorksheetThumbnailOptions)
CreateThumbnail(String, ImageFileFormat, Int32, Int32)
CreateThumbnail(String, ImageFileFormat, Int32, Int32, WorksheetThumbnailOptions)
CreateThumbnail(Stream, ImageFileFormat, Int32, Int32)
CreateThumbnail(Stream, ImageFileFormat, Int32, Int32, WorksheetThumbnailOptions)
See Also