Skip to main content

Worksheets

A workbook is a collection of worksheets. XLWorkbook.Worksheets is the collection API (Add, Delete, Contains, indexing), while each IXLWorksheet exposes the operations that act on a single sheet (Name, Position, CopyTo, Delete, Hide).

Two things are worth knowing up front:

  • Positions are 1-based. workbook.Worksheet(1) is the leftmost tab.
  • Sheet names are case-insensitive. workbook.Worksheet("data") finds a sheet named Data, and adding a second sheet called DATA throws.

Adding​

The simplest form takes a name:

using var workbook = new XLWorkbook();

var sales = workbook.Worksheets.Add("Sales");
var costs = workbook.Worksheets.Add("Costs");

XLWorkbook also has AddWorksheet shortcuts that forward to the same collection, so both of these are equivalent:

var a = workbook.Worksheets.Add("Summary");
var b = workbook.AddWorksheet("Summary");   // same thing, fewer characters

Inserting at a position​

Pass a position to insert the sheet rather than appending it. Existing sheets at or after that position shift right:

workbook.Worksheets.Add("First");            // position 1
workbook.Worksheets.Add("Third");            // position 2
workbook.Worksheets.Add("Second", 2);        // inserted between them

// Order is now: First, Second, Third

Auto-generated names​

Omit the name and XLibur picks the next free Sheet1, Sheet2, … name:

var sheet = workbook.Worksheets.Add();       // "Sheet1"
var next = workbook.Worksheets.Add(2);       // "Sheet2", inserted at position 2

From a DataTable or DataSet​

A DataTable can become a sheet in one call. The overloads let you control the sheet name and the name of the Excel table that is created from the data:

DataTable orders = LoadOrders();

workbook.Worksheets.Add(orders);                            // sheet named after the DataTable
workbook.Worksheets.Add(orders, "Orders");                  // explicit sheet name
workbook.Worksheets.Add(orders, "Orders", "OrdersTable");   // + explicit table name

// Every table in a DataSet, one sheet each
workbook.Worksheets.Add(dataSet);
note

Excel limits sheet names to 31 characters and forbids : \ / ? * [ ]. XLibur does not validate this for you — a name Excel rejects produces a file Excel refuses to open, so truncate and sanitise names that come from user input or database columns.

Finding sheets​

var byName = workbook.Worksheet("Sales");      // throws if missing
var byPosition = workbook.Worksheet(1);        // throws if missing

if (workbook.Worksheets.Contains("Sales"))
{
    // ...
}

// Non-throwing lookup
if (workbook.Worksheets.TryGetWorksheet("Sales", out var sheet))
{
    sheet.Cell("A1").Value = "Found";
}

// The collection is IEnumerable<IXLWorksheet>
foreach (var ws in workbook.Worksheets)
{
    Console.WriteLine($"{ws.Position}: {ws.Name}");
}

Removing​

Delete by name, by position, or from the sheet itself. Remaining sheets close the gap in position order:

workbook.Worksheets.Delete("Scratch");
workbook.Worksheets.Delete(3);

workbook.Worksheet("Old Data").Delete();

Deleting while enumerating the collection will throw, so materialise the list first:

var temporary = workbook.Worksheets
    .Where(ws => ws.Name.StartsWith("tmp_", StringComparison.OrdinalIgnoreCase))
    .ToList();

foreach (var ws in temporary)
{
    ws.Delete();
}
warning

A workbook must contain at least one visible worksheet. Deleting the last one produces a file Excel will refuse to open.

What happens to references to a deleted sheet​

All three ways of deleting a sheet do the same work. XLibur updates everything that referred to the sheet, as Excel does:

What refers to the sheetAfter the delete
A cell formulaThe reference becomes #REF!, so =Sheet1!A1*2 reads =#REF!*2
A 3D reference with the sheet at one endIt gets smaller: Sheet1:Sheet3!A1 reads Sheet2:Sheet3!A1
A defined name, at any scopeThe reference becomes #REF!
A data validation or conditional format ruleThe reference becomes #REF!
A chart seriesThe reference becomes #REF!; the chart keeps its cached values
A pivot cacheIt is kept, with its data, as Excel keeps it

A name that belongs to the deleted sheet is deleted with it. But if a formula on another sheet uses that name, XLibur keeps the name and moves it to the workbook.

Deleting a sheet that is already deleted does nothing.

Moving​

Position is settable. Assigning to it shifts every other sheet accordingly, so you never have to renumber the rest yourself:

var summary = workbook.Worksheet("Summary");

summary.Position = 1;                          // move to the front

var last = workbook.Worksheets.Count;
workbook.Worksheet("Appendix").Position = last; // move to the end

To move a sheet one place left or right:

var ws = workbook.Worksheet("Detail");

if (ws.Position > 1)
{
    ws.Position--;                             // move left
}

Sorting all sheets alphabetically:

var ordered = workbook.Worksheets.OrderBy(ws => ws.Name, StringComparer.OrdinalIgnoreCase).ToList();

for (var i = 0; i < ordered.Count; i++)
{
    ordered[i].Position = i + 1;
}

Renaming​

Setting Name also rewrites everything that refers to the sheet, so references do not break:

var ws = workbook.Worksheet("Sheet1");
ws.Name = "Q1 Sales";

// A formula elsewhere reading "=Sheet1!A1" now reads "='Q1 Sales'!A1"

This includes:

  • cell formulas, including 3D references such as SUM(Sheet1:Sheet3!A1)
  • defined names, at every scope
  • data validation rules
  • conditional format rules, including the rules of pivot tables
  • chart series
  • print areas
  • the source of a pivot cache

XLibur adds quotes to the name where a formula needs them. A formula the parser cannot read keeps its text, because XLibur does not know which references it holds.

A worksheet cannot have the same name as a chartsheet in the workbook. Add and setting Name throw ArgumentException if you try.

Copying​

CopyTo duplicates a sheet — values, formulas, formatting, tables, and merged ranges — either within the same workbook or into a different one:

var template = workbook.Worksheet("Template");

// Within the same workbook
var january = template.CopyTo("January");
var february = template.CopyTo("February", 2);   // and place it at position 2

// Into another workbook
using var target = new XLWorkbook();
template.CopyTo(target, "Imported Template");

A common pattern — one sheet per group, built from a single template:

using var workbook = new XLWorkbook();
var template = workbook.Worksheets.Add("Template");
template.Cell("A1").Value = "Region";
template.Cell("A1").Style.Font.Bold = true;

foreach (var region in new[] { "North", "South", "East", "West" })
{
    var sheet = template.CopyTo(region);
    sheet.Cell("B1").Value = region;
}

template.Delete();   // drop the template once the copies exist
workbook.SaveAs("Regions.xlsx");

If a formula, a data validation rule or a conditional format rule refers to its own sheet by name, the copy refers to the copy. For example, a rule on Data that reads Data!$A$1>0 reads 'Data (2)'!$A$1>0 on a copy named Data (2). References to other sheets do not change. This is what Excel does.

A copy also keeps the pivot tables on the sheet, with their conditional formats.

A copy also keeps the source sheet's appearance — gridlines, header visibility, zoom, view mode and tab colour — including when it is copied into a different workbook, where it used to adopt the destination's display defaults instead. The one thing a copy does not inherit is which tab is selected: two sheets both claiming the selection is how Excel encodes a group, so the copy starts unselected and the source keeps its own selection.

Hiding​

Sheets have three visibility states. Hidden sheets can be unhidden by the user through the Excel UI; VeryHidden sheets can only be restored through VBA or code:

var ws = workbook.Worksheet("Lookups");

ws.Hide();                                             // == Visibility = Hidden
ws.Unhide();                                           // == Visibility = Visible

ws.Visibility = XLWorksheetVisibility.VeryHidden;      // not listed in Excel's unhide dialog

if (ws.Visibility != XLWorksheetVisibility.Visible)
{
    ws.Unhide();
}

Tab appearance and selection​

var ws = workbook.Worksheet("Sales");

ws.SetTabColor(XLColor.Red);
ws.TabColor = XLColor.FromHtml("#FF4F81BD");

ws.TabActive = true;      // the sheet shown when the file is opened
ws.TabSelected = true;    // part of the selected group of tabs

Sheet view options​

Each sheet carries its own view settings — gridlines, headers, zero display, and frozen panes:

var ws = workbook.Worksheet("Report");

ws.ShowGridLines = false;
ws.ShowRowColHeaders = false;
ws.ShowZeros = false;
ws.SetShowFormulas(false);

// Freeze the header row and the first two columns
ws.SheetView.Freeze(1, 2);

// Or one axis at a time
ws.SheetView.FreezeRows(1);
ws.SheetView.FreezeColumns(2);

Freezing versus splitting​

Excel has two kinds of pane, and they take their position in different units. IXLSheetView.FreezePanes is what tells them apart; the three Freeze methods set it, and assigning SplitRow or SplitColumn on their own does not.

FreezePanesSplitRow / SplitColumn mean
Freeze — panes locked, set with Freeze…truea count of frozen rows or columns
Split — a draggable bar, Excel's View → Splitfalsethe bar's position in twentieths of a point
// A freeze: three rows locked at the top
ws.SheetView.FreezeRows(3);

// A split bar: 900 twentieths of a point ≈ three default 15pt rows
ws.SheetView.SplitRow = 900;
ws.SheetView.SplitColumn = 2880;   // ≈ three default 48pt columns
caution

Assigning SplitRow/SplitColumn used to produce a freeze, so SheetView.SplitRow = 3 froze three rows. It now produces a split bar 3/20 of a point from the top, which is visually nothing. The code still compiles, so there is no signal at build time — call FreezeRows(3) instead if a freeze was what you meant. XLibur carries whichever number you give it verbatim rather than converting between the two units, so a file round-trips exactly.

View mode and zoom​

Each sheet remembers which of Excel's three views it was last in, and that survives a save and reload:

ws.SheetView.View = XLSheetViewOptions.PageLayout;   // Normal, PageBreakPreview, PageLayout
ws.SheetView.SetView(XLSheetViewOptions.Normal);     // fluent equivalent

ws.SheetView.ZoomScale = 140;                        // the current view's zoom
ws.SheetView.ZoomScaleNormal = 100;
ws.SheetView.ZoomScalePageLayoutView = 140;
ws.SheetView.ZoomScaleSheetLayoutView = 60;          // Page Break Preview

Setting ZoomScale also records it against the named scale for the sheet's current view. A file naming a view this build does not recognise loads as Normal rather than failing.

Protecting a sheet​

var ws = workbook.Worksheet("Locked");

ws.Protect("s3cret")
  .AllowElement(XLSheetProtectionElements.SelectEverything)
  .AllowElement(XLSheetProtectionElements.FormatCells);

ws.Unprotect("s3cret");
note

Sheet protection is a UI convenience, not a security feature — the data is not encrypted and any tool (including XLibur) can read it. To lock the structure of the workbook (adding, deleting, or reordering sheets), see Workbook Settings.

Default sizing and styling​

Defaults apply to the whole sheet and are cheaper than styling individual cells:

var ws = workbook.Worksheet("Data");

ws.ColumnWidth = 14;
ws.RowHeight = 18;
ws.Style.Font.FontName = "Calibri";
ws.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;

Where to next​