Struct xlsxwriter::Worksheet

pub struct Worksheet<'a> { /* fields omitted */ }

The Worksheet object represents an Excel worksheet. It handles operations such as writing data to cells or formatting worksheet layout.

A Worksheet object isn't created directly. Instead a worksheet is created by calling the workbook.add_worksheet() function from a Workbook object:

use xlsxwriter::*;
let workbook = Workbook::new("test-worksheet.xlsx");
let mut worksheet = workbook.add_worksheet(None)?;
worksheet.write_string(0, 0, "Hello, excel", None)?;
workbook.close()

Please read original libxlsxwriter document for description missing functions. Most of this document is based on libxlsxwriter document.

Methods

impl<'a> Worksheet<'a>

pub fn write_number(
    &mut self,
    row: WorksheetRow,
    col: WorksheetCol,
    number: f64,
    format: Option<&Format>
) -> Result<(), XlsxError>

This function writes numeric types to the cell specified by row and column:

worksheet.write_number(0, 0, 123456.0, None)?;
worksheet.write_number(1, 0, 2.3451, None)?;

The native data type for all numbers in Excel is a IEEE-754 64-bit double-precision floating point, which is also the default type used by worksheet_write_number.

The format parameter is used to apply formatting to the cell. This parameter can be None to indicate no formatting or it can be a Format object.

let format = workbook.add_format()
    .set_num_format("$#,##0.00");
worksheet.write_number(0, 0, 1234.567, Some(&format))?;

Note

Excel doesn't support NaN, Inf or -Inf as a number value. If you are writing data that contains these values then your application should convert them to a string or handle them in some other way.

pub fn write_string(
    &mut self,
    row: WorksheetRow,
    col: WorksheetCol,
    text: &str,
    format: Option<&Format>
) -> Result<(), XlsxError>

This function writes a string to the cell specified by row and column:

worksheet.write_string(0, 0, "This phrase is English!", None)?;

The format parameter is used to apply formatting to the cell. This parameter can be None to indicate no formatting or it can be a Format object:

let format = workbook.add_format()
    .set_bold();
worksheet.write_string(0, 0, "This phrase is Bold!", Some(&format))?;

Unicode strings are supported in UTF-8 encoding.

worksheet.write_string(0, 0, "こんにちは、世界！", None)?;

pub fn write_formula(
    &mut self,
    row: WorksheetRow,
    col: WorksheetCol,
    formula: &str,
    format: Option<&Format>
) -> Result<(), XlsxError>

This function writes a formula or function to the cell specified by row and column:

worksheet.write_formula(0, 0, "=B3 + 6", None)?;
worksheet.write_formula(1, 0, "=SIN(PI()/4)", None)?;
worksheet.write_formula(2, 0, "=SUM(A1:A2)", None)?;
worksheet.write_formula(3, 0, "=IF(A3>1,\"Yes\", \"No\")", None)?;
worksheet.write_formula(4, 0, "=AVERAGE(1, 2, 3, 4)", None)?;
worksheet.write_formula(5, 0, "=DATEVALUE(\"1-Jan-2013\")", None)?;

The format parameter is used to apply formatting to the cell. This parameter can be None to indicate no formatting or it can be a Format object.

Libxlsxwriter doesn't calculate the value of a formula and instead stores a default value of 0. The correct formula result is displayed in Excel, as shown in the example above, since it recalculates the formulas when it loads the file. For cases where this is an issue see the write_formula_num() function and the discussion in that section.

Formulas must be written with the US style separator/range operator which is a comma (not semi-colon). Therefore a formula with multiple values should be written as follows:

// OK
worksheet.write_formula(0, 0, "=SUM(1, 2, 3)", None)?;
// NO. Error on load.
worksheet.write_formula(1, 0, "=SUM(1; 2; 3)", None)?;

See also Working with Formulas.

pub fn write_array_formula(
    &mut self,
    first_row: WorksheetRow,
    first_col: WorksheetCol,
    last_row: WorksheetRow,
    last_col: WorksheetCol,
    formula: &str,
    format: Option<&Format>
) -> Result<(), XlsxError>

This function writes an array formula to a cell range. In Excel an array formula is a formula that performs a calculation on a set of values. In Excel an array formula is indicated by a pair of braces around the formula: {=SUM(A1:B1*A2:B2)}.

Array formulas can return a single value or a range or values. For array formulas that return a range of values you must specify the range that the return values will be written to. This is why this function has first_ and last_ row/column parameters:

worksheet.write_array_formula(4, 0, 6, 0, "{=TREND(C5:C7,B5:B7)}", None)?;

If the array formula returns a single value then the first_ and last_ parameters should be the same:

worksheet.write_array_formula(1, 0, 1, 0, "{=SUM(B1:C1*B2:C2)}", None)?;

pub fn write_datetime(
    &mut self,
    row: WorksheetRow,
    col: WorksheetCol,
    datetime: &DateTime,
    format: Option<&Format>
) -> Result<(), XlsxError>

This function can be used to write a date or time to the cell specified by row and column:

use xlsxwriter::*;
let datetime = DateTime::new(2013, 2, 28, 12, 0, 0.0);
let datetime_format = workbook.add_format()
    .set_num_format("mmm d yyyy hh:mm AM/PM");
worksheet.write_datetime(1, 0, &datetime, Some(&datetime_format))?;

The format parameter should be used to apply formatting to the cell using a Format object as shown above. Without a date format the datetime will appear as a number only.

See Working with Dates and Times for more information about handling dates and times in libxlsxwriter.

pub fn write_url(
    &mut self,
    row: WorksheetRow,
    col: WorksheetCol,
    url: &str,
    format: Option<&Format>
) -> Result<(), XlsxError>

This function is used to write a URL/hyperlink to a worksheet cell specified by row and column. The format parameter is used to apply formatting to the cell. This parameter can be None to indicate no formatting or it can be a Format object. The typical worksheet format for a hyperlink is a blue underline:

let url_format = workbook.add_format()
    .set_underline(FormatUnderline::Single).set_font_color(FormatColor::Blue);
worksheet.write_url(0, 0, "http://libxlsxwriter.github.io", Some(&url_format))?;

The usual web style URI's are supported: http://, https://, ftp:// and mailto: :

worksheet.write_url(0, 0, "ftp://www.python.org/", Some(&url_format))?;
worksheet.write_url(1, 0, "http://www.python.org/", Some(&url_format))?;
worksheet.write_url(2, 0, "https://www.python.org/", Some(&url_format))?;
worksheet.write_url(3, 0, "mailto:foo@example.com", Some(&url_format))?;

An Excel hyperlink is comprised of two elements: the displayed string and the non-displayed link. By default the displayed string is the same as the link. However, it is possible to overwrite it with any other libxlsxwriter type using the appropriate Worksheet.write_*() function. The most common case is to overwrite the displayed link text with another string:

worksheet.write_url(0, 0, "http://libxlsxwriter.github.io", Some(&url_format))?;
worksheet.write_string(0, 0, "Read the documentation.", Some(&url_format))?;

Two local URIs are supported: internal: and external:. These are used for hyperlinks to internal worksheet references or external workbook and worksheet references:

worksheet.write_url(0, 0, "internal:Sheet2!A1", Some(&url_format))?;
worksheet.write_url(1, 0, "internal:Sheet2!B2", Some(&url_format))?;
worksheet.write_url(2, 0, "internal:Sheet2!A1:B2", Some(&url_format))?;
worksheet.write_url(3, 0, "internal:'Sales Data'!A1", Some(&url_format))?;
worksheet.write_url(4, 0, "external:c:\\temp\\foo.xlsx", Some(&url_format))?;
worksheet.write_url(5, 0, "external:c:\\foo.xlsx#Sheet2!A1", Some(&url_format))?;
worksheet.write_url(6, 0, "external:..\\foo.xlsx", Some(&url_format))?;
worksheet.write_url(7, 0, "external:..\\foo.xlsx#Sheet2!A1", Some(&url_format))?;
worksheet.write_url(8, 0, "external:\\\\NET\\share\\foo.xlsx", Some(&url_format))?;

pub fn write_boolean(
    &mut self,
    row: WorksheetRow,
    col: WorksheetCol,
    value: bool,
    format: Option<&Format>
) -> Result<(), XlsxError>

Write an Excel boolean to the cell specified by row and column:

worksheet.write_boolean(0, 0, true, None)?;
worksheet.write_boolean(1, 0, false, None)?;

pub fn write_blank(
    &mut self,
    row: WorksheetRow,
    col: WorksheetCol,
    format: Option<&Format>
) -> Result<(), XlsxError>

Write a blank cell specified by row and column:

worksheet.write_blank(1, 1, Some(&url_format));

This function is used to add formatting to a cell which doesn't contain a string or number value.

Excel differentiates between an "Empty" cell and a "Blank" cell. An Empty cell is a cell which doesn't contain data or formatting whilst a Blank cell doesn't contain data but does contain formatting. Excel stores Blank cells but ignores Empty cells.

As such, if you write an empty cell without formatting it is ignored.

pub fn write_formula_num(
    &mut self,
    row: WorksheetRow,
    col: WorksheetCol,
    formula: &str,
    format: Option<&Format>,
    number: f64
) -> Result<(), XlsxError>

Write a blank cell specified by row and column:

worksheet.write_blank(1, 1, Some(&url_format));

This function is used to add formatting to a cell which doesn't contain a string or number value.

Excel differentiates between an "Empty" cell and a "Blank" cell. An Empty cell is a cell which doesn't contain data or formatting whilst a Blank cell doesn't contain data but does contain formatting. Excel stores Blank cells but ignores Empty cells.

As such, if you write an empty cell without formatting it is ignored.

pub fn write_rich_string(
    &mut self,
    row: WorksheetRow,
    col: WorksheetCol,
    text: &[(&str, Option<&Format>)],
    format: Option<&Format>
) -> Result<(), XlsxError>

This function is used to write strings with multiple formats. For example to write the string 'This is bold and this is italic' you would use the following:

let mut bold = workbook.add_format()
    .set_bold();
let mut italic = workbook.add_format()
    .set_italic();
worksheet.write_rich_string(
    0, 0,
    &[
        ("This is ", None),
        ("bold", Some(&bold)),
        (" and this is ", None),
        ("italic", Some(&italic))
    ],
    None
)?;

The basic rule is to break the string into fragments and put a lxw_format object before the fragment that you want to format. So if we look at the above example again:

This is bold and this is italic

The would be broken down into 4 fragments:

default: |This is |
bold:    |bold|
default: | and this is |
italic:  |italic|

This in then converted to the tuple fragments shown in the example above. For the default format we use None.

Note

Excel doesn't allow the use of two consecutive formats in a rich string or an empty string fragment. For either of these conditions a warning is raised and the input to worksheet.write_rich_string() is ignored.

pub fn set_row(
    &mut self,
    row: WorksheetRow,
    height: f64,
    format: Option<&Format>
) -> Result<(), XlsxError>

pub fn set_row_opt(
    &mut self,
    row: WorksheetRow,
    height: f64,
    format: Option<&Format>,
    options: &mut RowColOptions
) -> Result<(), XlsxError>

pub fn set_column(
    &mut self,
    first_col: WorksheetCol,
    last_col: WorksheetCol,
    height: f64,
    format: Option<&Format>
) -> Result<(), XlsxError>

pub fn set_column_opt(
    &mut self,
    first_col: WorksheetCol,
    last_col: WorksheetCol,
    height: f64,
    format: Option<&Format>,
    options: &mut RowColOptions
) -> Result<(), XlsxError>

pub fn insert_image(
    &mut self,
    row: WorksheetRow,
    col: WorksheetCol,
    filename: &str
) -> Result<(), XlsxError>

This function can be used to insert a image into a worksheet. The image can be in PNG, JPEG or BMP format:

worksheet.insert_image(2, 1, "images/simple1.png")?;

The Worksheet.insert_image_opt() function takes additional optional parameters to position and scale the image, see below.

Note

The scaling of a image may be affected if is crosses a row that has its default height changed due to a font that is larger than the default font size or that has text wrapping turned on. To avoid this you should explicitly set the height of the row using Worksheet.set_row() if it crosses an inserted image.

BMP images are only supported for backward compatibility. In general it is best to avoid BMP images since they aren't compressed. If used, BMP images must be 24 bit, true color, bitmaps.

pub fn insert_image_opt(
    &mut self,
    row: WorksheetRow,
    col: WorksheetCol,
    filename: &str,
    opt: &ImageOptions
) -> Result<(), XlsxError>

This function is like Worksheet.insert_image() function except that it takes an optional ImageOptions struct to scale and position the image:

worksheet.insert_image_opt(
    2, 1,
   "images/simple1.png",
    &ImageOptions{
        x_offset: 30,
        y_offset: 30,
        x_scale: 0.5,
        y_scale: 0.5,
    }
)?;

Note

See the notes about row scaling and BMP images in Worksheet.insert_image() above.

pub fn insert_image_buffer(
    &mut self,
    row: WorksheetRow,
    col: WorksheetCol,
    buffer: &[u8]
) -> Result<(), XlsxError>

This function can be used to insert a image into a worksheet from a memory buffer:

let data = include_bytes!("../images/simple1.png");
worksheet.insert_image_buffer(0, 0, &data[..])?;

See Worksheet.insert_image() for details about the supported image formats, and other image features.

pub fn insert_image_buffer_opt(
    &mut self,
    row: WorksheetRow,
    col: WorksheetCol,
    buffer: &[u8],
    opt: &ImageOptions
) -> Result<(), XlsxError>

pub fn merge_range(
    &mut self,
    first_row: WorksheetRow,
    first_col: WorksheetCol,
    last_row: WorksheetRow,
    last_col: WorksheetCol,
    string: &str,
    format: Option<&Format>
) -> Result<(), XlsxError>

pub fn autofilter(
    &mut self,
    first_row: WorksheetRow,
    first_col: WorksheetCol,
    last_row: WorksheetRow,
    last_col: WorksheetCol
) -> Result<(), XlsxError>

pub fn data_validation_cell(
    &mut self,
    row: WorksheetRow,
    col: WorksheetCol,
    validation: &DataValidation
) -> Result<(), XlsxError>

pub fn data_validation_range(
    &mut self,
    first_row: WorksheetRow,
    first_col: WorksheetCol,
    last_row: WorksheetRow,
    last_col: WorksheetCol,
    validation: &DataValidation
) -> Result<(), XlsxError>

pub fn activate(&mut self)

pub fn select(&mut self)

pub fn hide(&mut self)

pub fn set_first_sheet(&mut self)

pub fn freeze_panes(&mut self, row: WorksheetRow, col: WorksheetCol)

pub fn split_panes(&mut self, vertical: f64, horizontal: f64)

pub fn set_selection(
    &mut self,
    first_row: WorksheetRow,
    first_col: WorksheetCol,
    last_row: WorksheetRow,
    last_col: WorksheetCol
)

pub fn set_landscape(&mut self)

pub fn set_portrait(&mut self)

pub fn set_page_view(&mut self)

pub fn set_paper(&mut self, paper: PaperType)

pub fn set_header(&mut self, header: &str) -> Result<(), XlsxError>

pub fn set_footer(&mut self, footer: &str) -> Result<(), XlsxError>

pub fn set_header_opt(
    &mut self,
    header: &str,
    options: &HeaderFooterOptions
) -> Result<(), XlsxError>

pub fn set_footer_opt(
    &mut self,
    footer: &str,
    options: &HeaderFooterOptions
) -> Result<(), XlsxError>

pub fn set_h_pagebreaks(
    &mut self,
    breaks: &[WorksheetRow]
) -> Result<(), XlsxError>

pub fn set_v_pagebreaks(
    &mut self,
    breaks: &[WorksheetCol]
) -> Result<(), XlsxError>

pub fn print_across(&mut self)

pub fn set_zoom(&mut self, scale: u16)

pub fn gridlines(&mut self, option: GridLines)

pub fn center_horizontally(&mut self)

pub fn center_vertically(&mut self)

pub fn print_row_col_headers(&mut self)

pub fn repeat_rows(
    &mut self,
    first_row: WorksheetRow,
    last_row: WorksheetRow
) -> Result<(), XlsxError>

pub fn repeat_columns(
    &mut self,
    first_col: WorksheetCol,
    last_col: WorksheetCol
) -> Result<(), XlsxError>

pub fn print_area(
    &mut self,
    first_row: WorksheetRow,
    first_col: WorksheetCol,
    last_row: WorksheetRow,
    last_col: WorksheetCol
) -> Result<(), XlsxError>

pub fn fit_to_pages(&mut self, width: u16, height: u16)

pub fn set_start_page(&mut self, start_page: u16)

pub fn set_print_scale(&mut self, scale: u16)

pub fn set_right_to_left(&mut self)

pub fn set_hide_zero(&mut self)

pub fn set_tab_color(&mut self, color: FormatColor)

pub fn protect(&mut self, password: &str, protection: &Protection)

pub fn outline_settings(
    &mut self,
    visible: bool,
    symbols_below: bool,
    symbols_right: bool,
    auto_style: bool
)

pub fn set_default_row(&mut self, height: f64, hide_unused_rows: bool)

pub fn set_vba_name(&mut self, name: &str) -> Result<(), XlsxError>