Working with worksheet tabs

Worksheet tabs in Excel allow the user to differentiate between different worksheets.

Worksheets in a workbook can be highlighted via the tab name, color, position or the fact that it is active when the user opens the workbook.

Image of a worksheet with four tabs

The rust_xlsxwriter library provides a number of methods, explained below, to emulate this functionality.

Worksheet names

The worksheet name can be set with Worksheet::set_name():

// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org

//! The following example demonstrates setting user defined worksheet names
//! and the default values when a name isn't set.

use rust_xlsxwriter::{Workbook, XlsxError};

fn main() -> Result<(), XlsxError> {
    let mut workbook = Workbook::new();

    let _worksheet1 = workbook.add_worksheet(); // Defaults to Sheet1
    let _worksheet2 = workbook.add_worksheet().set_name("Foglio2");
    let _worksheet3 = workbook.add_worksheet().set_name("Data");
    let _worksheet4 = workbook.add_worksheet(); // Defaults to Sheet4

    workbook.save("worksheets.xlsx")?;

    Ok(())
}

Which gives the following output:

Image of output from doc_worksheet_set_name.rs

Excel applies various rules to worksheet names such as:

The name must be less than 32 characters.
The name cannot be blank.
The name cannot contain any of the characters: [ ] : * ? / \.
The name cannot start or end with an apostrophe.
The name shouldn't be "History" (case-insensitive) since that is reserved by Excel.
The name must not be a duplicate (case-insensitive) of another worksheet name used in the workbook.

The rules for worksheet names in Excel are explained in the Microsoft Office documentation.

Setting the active worksheet

In Excel the visible worksheet in a group of worksheets is known as the active worksheet. Since only one worksheet is in the foreground at any one time there can only be one active worksheet.

With rust_xlsxwriter the Worksheet::set_active() method is used to specify which worksheet is active. If no worksheet is set as the active worksheet then the default is to have the first one active, like in Excel.

Here is an example of making the second worksheet active:

// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org

//! The following example demonstrates setting a worksheet as the visible
//! worksheet when a file is opened.

use rust_xlsxwriter::{Workbook, Worksheet, XlsxError};

fn main() -> Result<(), XlsxError> {
    let mut workbook = Workbook::new();

    let worksheet1 = Worksheet::new();
    let worksheet3 = Worksheet::new();
    let mut worksheet2 = Worksheet::new();

    worksheet2.set_active(true);

    workbook.push_worksheet(worksheet1);
    workbook.push_worksheet(worksheet2);
    workbook.push_worksheet(worksheet3);

    workbook.save("worksheet.xlsx")?;

    Ok(())
}

Which gives the following output:

Image of output from doc_worksheet_set_active.rs

If you have a lot of worksheets then they may not all fit on the screen at the same time. In cases like that the active worksheet will still be visible but its tab may not be. In those, rare, cases you can use the Worksheet::set_first_tab() method to set the first visible tab (not worksheet) in a group of worksheets.

Setting worksheet tab colors

Another way of highlighting one or more worksheets in Excel is to set the tab color. With rust_xlsxwriter this is achieved with Worksheet::set_tab_color() and a Color color:

// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org

//! The following example demonstrates set the tab color of worksheets.

use rust_xlsxwriter::{Color, Workbook, Worksheet, XlsxError};

fn main() -> Result<(), XlsxError> {
    let mut workbook = Workbook::new();

    let mut worksheet1 = Worksheet::new();
    let mut worksheet2 = Worksheet::new();
    let mut worksheet3 = Worksheet::new();
    let mut worksheet4 = Worksheet::new();

    worksheet1.set_tab_color(Color::Red);
    worksheet2.set_tab_color(Color::Green);
    worksheet3.set_tab_color(Color::RGB(0xFF9900));

    // worksheet4 will have the default color.
    worksheet4.set_active(true);

    workbook.push_worksheet(worksheet1);
    workbook.push_worksheet(worksheet2);
    workbook.push_worksheet(worksheet3);
    workbook.push_worksheet(worksheet4);

    workbook.save("worksheet.xlsx")?;

    Ok(())
}

Which gives the following output:

Image of output from doc_worksheet_set_tab_color.rs

Hiding worksheets

Sometimes it is desirable to hide worksheets if they contain a lot of intermediate data or calculations that end user doesn't need to see. With rust_xlsxwriter this is achieved with the Worksheet::set_hidden() method:

// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org

//! The following example demonstrates hiding a worksheet.

use rust_xlsxwriter::{Workbook, Worksheet, XlsxError};

fn main() -> Result<(), XlsxError> {
    let mut workbook = Workbook::new();

    let worksheet1 = Worksheet::new();
    let worksheet3 = Worksheet::new();
    let mut worksheet2 = Worksheet::new();

    worksheet2.set_hidden(true);

    workbook.push_worksheet(worksheet1);
    workbook.push_worksheet(worksheet2);
    workbook.push_worksheet(worksheet3);

    workbook.save("worksheet.xlsx")?;

    Ok(())
}

Which gives the following output:

Image of output from doc_worksheet_set_hidden.rs

In Excel a hidden worksheet can not be activated or selected so Worksheet::set_hidden() is mutually exclusive with the Worksheet::set_active() and Worksheet::set_selected() methods. In addition, since the first worksheet will default to being the active worksheet, you cannot hide the first worksheet without activating another sheet.

Selecting worksheets

A selected worksheet has its tab highlighted. Selecting worksheets is a way of grouping them together so that, for example, several worksheets could be printed in one go.

The Worksheet::set_selected() method is used to indicate that a worksheet is selected in a multi-sheet workbook.

A worksheet that has been activated via the Worksheet::set_active() method will also appear as selected.

// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org

//! The following example demonstrates selecting worksheet in a workbook. The
//! active worksheet is selected by default so in this example the first two
//! worksheets are selected.

use rust_xlsxwriter::{Workbook, Worksheet, XlsxError};

fn main() -> Result<(), XlsxError> {
    let mut workbook = Workbook::new();

    let worksheet1 = Worksheet::new();
    let worksheet3 = Worksheet::new();
    let mut worksheet2 = Worksheet::new();

    worksheet2.set_selected(true);

    workbook.push_worksheet(worksheet1);
    workbook.push_worksheet(worksheet2);
    workbook.push_worksheet(worksheet3);

    workbook.save("worksheet.xlsx")?;

    Ok(())
}

Which gives the following output. Note that Sheet 1 and Sheet2 are selected but Sheet3 isn't:

Image of output from doc_worksheet_set_selected.rs

Working with the rust_xlsxwriter library

Working with worksheet tabs

Worksheet names

Setting the active worksheet

Setting worksheet tab colors

Hiding worksheets

Selecting worksheets