Iterate Through Worksheets
In many scenarios you need to iterate through all worksheets in a given workbook. The API of the Workbook class exposes a Worksheets collection that allows you to retrieve worksheets both by name and index. The collection also allows you to iterate all worksheets. The Iterating Used Cells article shows how to iterate the cells inside a worksheet.
Retrieving and Iterating Worksheets
Example 1 illustrates how to retrieve worksheets that have already been added to the workbook.
Example 1: Retrieve worksheets by index and by name from the Worksheets collection
Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.Worksheets;
worksheets.Add();
worksheets.Add();
Worksheet firstWorksheet = worksheets[0];
Worksheet secondWorksheet = worksheets["Sheet2"];Example 2 creates a new workbook with three worksheets. The code further iterates through all worksheets and sets the value of cell A1 to the name of the corresponding worksheet. The example also sets the ForeColor and BackgroundFill of the cell.
Example 2: Iterate through all worksheets and label cell A1 with each worksheet name
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
workbook.Worksheets.Add();
workbook.Worksheets.Add();
ThemableColor foregroundColor = new ThemableColor(Colors.Red);
var backgroundColor = Colors.Green;
IFill backgroundFill = new PatternFill(PatternType.Solid, backgroundColor, backgroundColor);
foreach (Worksheet worksheet in workbook.Worksheets)
{
CellSelection cell = worksheet.Cells[0, 0];
cell.SetValue("The name of this worksheet is: " + worksheet.Name);
cell.SetForeColor(foregroundColor);
cell.SetFill(backgroundFill);
}