Working with Spreadsheet Management

Working with Spreadsheet Management

The Workbook class is the root object for all spreadsheet operations in Aspose.Cells FOSS for .NET. It provides access to the WorksheetCollection, defined names, and save/load lifecycle. This guide covers fault-tolerant loading, worksheet organisation, print-layout configuration, and row/column/cell layout controls.

Loading Workbooks with Repair Options

Pass a file path to the Workbook constructor to load an existing XLSX file. Supply a LoadOptions object with TryRepairPackage and TryRepairXml set to true to attempt recovery of files with minor structural corruption. A WorkbookLoadException is thrown when the file is unrecoverable.

using Aspose.Cells_FOSS;

var options = new LoadOptions
{
    TryRepairPackage = true,
    TryRepairXml = true,
};

try
{
    var wb = new Workbook("data.xlsx", options);
    Console.WriteLine("Sheets: " + wb.Worksheets.Count);
    Console.WriteLine("Cell A1: " + wb.Worksheets[0].Cells["A1"].StringValue);
}
catch (WorkbookLoadException ex)
{
    Console.WriteLine("Unrecoverable: " + ex.Message);
}

Adding and Organising Worksheets

Workbook.Worksheets returns a WorksheetCollection. Call Add(name) to append a new worksheet by name; the method returns the new sheet’s index. Access worksheets by zero-based index or by name. Set WorksheetCollection.ActiveSheetName to control which tab is active when the file opens.

using Aspose.Cells_FOSS;

var wb = new Workbook();

// Rename the default sheet
wb.Worksheets[0].Name = "Summary";

// Add sheets
var dataIdx = wb.Worksheets.Add("Data");
var archiveIdx = wb.Worksheets.Add("Archive");

// Set active sheet
wb.Worksheets.ActiveSheetName = "Data";

// Write to sheets by name
wb.Worksheets["Summary"].Cells["A1"].PutValue("Overview total");
wb.Worksheets["Data"].Cells["A1"].PutValue("Raw rows start here");

wb.Save("organised.xlsx");

// Verify
var loaded = new Workbook("organised.xlsx");
Console.WriteLine("Active: " + loaded.Worksheets.ActiveSheetName);
Console.WriteLine("Sheet count: " + loaded.Worksheets.Count);

Hiding and Showing Worksheets

Set Worksheet.VisibilityType to VisibilityType.Hidden to hide a sheet without deleting it. Hidden sheets are preserved in the XLSX file and can be made visible by setting VisibilityType.Visible. Tab colour is set via Worksheet.TabColor.

using Aspose.Cells_FOSS;

var wb = new Workbook();
wb.Worksheets[0].Name = "Visible";
var hiddenIdx = wb.Worksheets.Add("Internal");
var hiddenSheet = wb.Worksheets[hiddenIdx];

hiddenSheet.VisibilityType = VisibilityType.Hidden;
hiddenSheet.Cells["A1"].PutValue("Internal data");

wb.Worksheets["Visible"].TabColor = System.Drawing.Color.FromArgb(255, 70, 130, 180);

wb.Save("hidden.xlsx");

Configuring Page Setup

Worksheet.PageSetup exposes all print-related properties. Use PageOrientationType to switch between portrait and landscape. Use PaperSizeType to select a paper size. Define the print area with PrintArea, set repeat rows/columns with PrintTitleRows/PrintTitleColumns, and add manual page breaks with AddHorizontalPageBreak and AddVerticalPageBreak.

using Aspose.Cells_FOSS;

var wb = new Workbook();
var ws = wb.Worksheets[0];

// Sample data
for (var r = 0; r < 60; r++)
    ws.Cells[r, 0].PutValue("Row " + (r + 1));

var ps = ws.PageSetup;
ps.Orientation = PageOrientationType.Landscape;
ps.PaperSize = PaperSizeType.PaperA4;
ps.LeftMarginInch = 0.5d;
ps.RightMarginInch = 0.5d;
ps.TopMarginInch = 0.75d;
ps.BottomMarginInch = 0.75d;
ps.PrintArea = "$A$1:$H$60";
ps.PrintTitleRows = "$1:$1";
ps.CenterHeader = "Sales Report";
ps.LeftFooter = "Confidential";
ps.RightFooter = "Page &P of &N";
ps.PrintGridlines = true;
ps.CenterHorizontally = true;
ps.AddHorizontalPageBreak(30);

wb.Save("paged.xlsx");

var loaded = new Workbook("paged.xlsx");
Console.WriteLine("Orientation: " + loaded.Worksheets[0].PageSetup.Orientation);
Console.WriteLine("Print area: " + loaded.Worksheets[0].PageSetup.PrintArea);

Row and Column Sizing

Access Worksheet.Cells.Rows by index to get a Row object. Set Row.Height (in points) to control row height. Set Row.IsHidden to true to hide the row. Access Worksheet.Cells.Columns by index to get a Column object. Set Column.Width (in character units) and Column.IsHidden.

using Aspose.Cells_FOSS;

var wb = new Workbook();
var ws = wb.Worksheets[0];

// Populate data
for (var r = 0; r < 5; r++)
    for (var c = 0; c < 4; c++)
        ws.Cells[r, c].PutValue($"R{r + 1}C{c + 1}");

// Row height and visibility
ws.Cells.Rows[0].Height = 28d;   // Header taller
ws.Cells.Rows[3].IsHidden = true; // Hide row 4

// Column width and visibility
ws.Cells.Columns[0].Width = 22d;
ws.Cells.Columns[3].IsHidden = true;

wb.Save("layout.xlsx");

var loaded = new Workbook("layout.xlsx");
Console.WriteLine("Row 0 height: " + loaded.Worksheets[0].Cells.Rows[0].Height);
Console.WriteLine("Col 0 width: " + loaded.Worksheets[0].Cells.Columns[0].Width);

Merging Cells

Call Worksheet.Cells.Merge(firstRow, firstColumn, totalRows, totalColumns) to merge a contiguous rectangular block into a single cell. The merged cell inherits the value of the top-left cell. Use Worksheet.Cells.MergedCells to read back all merged regions in a loaded workbook.

using Aspose.Cells_FOSS;

var wb = new Workbook();
var ws = wb.Worksheets[0];

// Title spanning A1:D1
ws.Cells["A1"].PutValue("Quarterly Sales Report");
ws.Cells.Merge(0, 0, 1, 4);

// Section header spanning A3:D3
ws.Cells["A3"].PutValue("Region: North");
ws.Cells.Merge(2, 0, 1, 4);

// Column headers
ws.Cells["A4"].PutValue("Product"); ws.Cells["B4"].PutValue("Q1");
ws.Cells["C4"].PutValue("Q2");      ws.Cells["D4"].PutValue("Q3");

wb.Save("report.xlsx");

var loaded = new Workbook("report.xlsx");
Console.WriteLine("Merged regions: " + loaded.Worksheets[0].Cells.MergedCells.Count);

Tips and Best Practices

• Use WorksheetCollection.ActiveSheetName to ensure the correct tab is selected when the file opens in Excel.
• Set page margins in inches (LeftMarginInch, TopMarginInch) — these are floating-point values, not integers.
• Hiding rows/columns (IsHidden = true) preserves their data; deleting a row removes data permanently.
• Always call Workbook.Save() once at the end of a batch operation rather than after each change.
• Merge cells sparingly in data sheets — merged regions prevent sorting and auto-filter from working correctly across the merged area.

Common Issues

FAQ

How many worksheets can I add?

The XLSX format supports up to 1024 sheets per workbook. Practical limits depend on available memory and file size.

Can I copy a worksheet between workbooks?

The current API surface does not expose a CopyTo method for cross-workbook sheet copies. Load both files and transfer data cell by cell.

How do I reorder worksheets?

Use WorksheetCollection index access and rename sheets to change their logical order. Direct positional move is not in the current API surface.

What units does PageSetup use for margins?

All margin properties (LeftMarginInch, TopMarginInch, etc.) are in inches as double values.

Does Cells.Merge replace existing cell data?

The top-left cell value is preserved; values in the other merged cells are cleared.

API Reference Summary