jmcnamara
commented
1 year ago /// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2023, John McNamara, jmcnamara@cpan.org
//! Example of how to add tables to a worksheet using the rust_xlsxwriter
//! library.
//!
//! Tables in Excel are used to group rows and columns of data into a single
//! structure that can be referenced in a formula or formatted collectively.
use rust_xlsxwriter::{Table, TableColumn, TableFunction, TableStyle, Workbook, XlsxError};
fn main() -> Result<(), XlsxError> {
// Create a new Excel file object.
let mut workbook = Workbook::new();
// Some sample data for the table.
let items = ["Apples", "Pears", "Bananas", "Oranges"];
let data = [
[10000, 5000, 8000, 6000],
[2000, 3000, 4000, 5000],
[6000, 6000, 6500, 6000],
[500, 300, 200, 700],
];
// -----------------------------------------------------------------------
// Example 1. Default table with no data.
// -----------------------------------------------------------------------
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
let caption = "Default table with no data.";
// Set the columns widths for clarity.
for col_num in 1..=6u16 {
worksheet.set_column_width(col_num, 12)?;
}
// Write the caption.
worksheet.write(0, 1, caption)?;
// Create a new table.
let table = Table::new();
// Add the table to the worksheet.
worksheet.add_table(2, 1, 6, 5, &table)?;
// -----------------------------------------------------------------------
// Example 2. Default table with data.
// -----------------------------------------------------------------------
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
let caption = "Default table with data.";
// Set the columns widths for clarity.
for col_num in 1..=6u16 {
worksheet.set_column_width(col_num, 12)?;
}
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the table data.
worksheet.write_column(3, 1, items)?;
worksheet.write_row_matrix(3, 2, data)?;
// Create a new table.
let table = Table::new();
// Add the table to the worksheet.
worksheet.add_table(2, 1, 6, 5, &table)?;
// -----------------------------------------------------------------------
// Example 3. Table without default autofilter.
// -----------------------------------------------------------------------
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
let caption = "Table without default autofilter.";
// Set the columns widths for clarity.
for col_num in 1..=6u16 {
worksheet.set_column_width(col_num, 12)?;
}
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the table data.
worksheet.write_column(3, 1, items)?;
worksheet.write_row_matrix(3, 2, data)?;
// Create and configure a new table.
let mut table = Table::new();
table.set_autofilter(false);
// Add the table to the worksheet.
worksheet.add_table(2, 1, 6, 5, &table)?;
// -----------------------------------------------------------------------
// Example 4. Table without default header row.
// -----------------------------------------------------------------------
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
let caption = "Table without default header row.";
// Set the columns widths for clarity.
for col_num in 1..=6u16 {
worksheet.set_column_width(col_num, 12)?;
}
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the table data.
worksheet.write_column(3, 1, items)?;
worksheet.write_row_matrix(3, 2, data)?;
// Create and configure a new table.
let mut table = Table::new();
table.set_header_row(false);
// Add the table to the worksheet.
worksheet.add_table(3, 1, 6, 5, &table)?;
// -----------------------------------------------------------------------
// Example 5. Default table with "First Column" and "Last Column" options.
// -----------------------------------------------------------------------
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
let caption = "Default table with 'First Column' and 'Last Column' options.";
// Set the columns widths for clarity.
for col_num in 1..=6u16 {
worksheet.set_column_width(col_num, 12)?;
}
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the table data.
worksheet.write_column(3, 1, items)?;
worksheet.write_row_matrix(3, 2, data)?;
// Create and configure a new table.
let mut table = Table::new();
table.set_first_column(true);
table.set_last_column(true);
// Add the table to the worksheet.
worksheet.add_table(2, 1, 6, 5, &table)?;
// -----------------------------------------------------------------------
// Example 6. Table with banded columns but without default banded rows.
// -----------------------------------------------------------------------
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
let caption = "Table with banded columns but without default banded rows.";
// Set the columns widths for clarity.
for col_num in 1..=6u16 {
worksheet.set_column_width(col_num, 12)?;
}
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the table data.
worksheet.write_column(3, 1, items)?;
worksheet.write_row_matrix(3, 2, data)?;
// Create and configure a new table.
let mut table = Table::new();
table.set_banded_rows(false);
table.set_banded_columns(true);
// Add the table to the worksheet.
worksheet.add_table(2, 1, 6, 5, &table)?;
// -----------------------------------------------------------------------
// Example 7. Table with user defined column headers.
// -----------------------------------------------------------------------
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
let caption = "Table with user defined column headers.";
// Set the columns widths for clarity.
for col_num in 1..=6u16 {
worksheet.set_column_width(col_num, 12)?;
}
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the table data.
worksheet.write_column(3, 1, items)?;
worksheet.write_row_matrix(3, 2, data)?;
// Create and configure a new table.
let mut table = Table::new();
let columns = vec![
TableColumn::new().set_header("Product"),
TableColumn::new().set_header("Quarter 1"),
TableColumn::new().set_header("Quarter 2"),
TableColumn::new().set_header("Quarter 3"),
TableColumn::new().set_header("Quarter 4"),
];
table.set_columns(&columns);
// Add the table to the worksheet.
worksheet.add_table(2, 1, 6, 5, &table)?;
// -----------------------------------------------------------------------
// Example 8. Table with user defined column headers, and formulas.
// -----------------------------------------------------------------------
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
let caption = "Table with user defined column headers, and formulas.";
// Set the columns widths for clarity.
for col_num in 1..=6u16 {
worksheet.set_column_width(col_num, 12)?;
}
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the table data.
worksheet.write_column(3, 1, items)?;
worksheet.write_row_matrix(3, 2, data)?;
// Create and configure a new table.
let mut table = Table::new();
let columns = vec![
TableColumn::new().set_header("Product"),
TableColumn::new().set_header("Quarter 1"),
TableColumn::new().set_header("Quarter 2"),
TableColumn::new().set_header("Quarter 3"),
TableColumn::new().set_header("Quarter 4"),
TableColumn::new()
.set_header("Year")
.set_formula("SUM(Table8[@[Quarter 1]:[Quarter 4]])"),
];
table.set_columns(&columns);
// Add the table to the worksheet.
worksheet.add_table(2, 1, 6, 6, &table)?;
// -----------------------------------------------------------------------
// Example 9. Table with totals row (but no caption or totals).
// -----------------------------------------------------------------------
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
let caption = "Table with totals row (but no caption or totals).";
// Set the columns widths for clarity.
for col_num in 1..=6u16 {
worksheet.set_column_width(col_num, 12)?;
}
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the table data.
worksheet.write_column(3, 1, items)?;
worksheet.write_row_matrix(3, 2, data)?;
// Create and configure a new table.
let mut table = Table::new();
let columns = vec![
TableColumn::new().set_header("Product"),
TableColumn::new().set_header("Quarter 1"),
TableColumn::new().set_header("Quarter 2"),
TableColumn::new().set_header("Quarter 3"),
TableColumn::new().set_header("Quarter 4"),
TableColumn::new()
.set_header("Year")
.set_formula("SUM(Table9[@[Quarter 1]:[Quarter 4]])"),
];
table.set_columns(&columns);
table.set_total_row(true);
// Add the table to the worksheet.
worksheet.add_table(2, 1, 7, 6, &table)?;
// -----------------------------------------------------------------------
// Example 10. Table with totals row with user captions and functions.
// -----------------------------------------------------------------------
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
let caption = "Table with totals row with user captions and functions.";
// Set the columns widths for clarity.
for col_num in 1..=6u16 {
worksheet.set_column_width(col_num, 12)?;
}
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the table data.
worksheet.write_column(3, 1, items)?;
worksheet.write_row_matrix(3, 2, data)?;
// Create and configure a new table.
let mut table = Table::new();
let columns = vec![
TableColumn::new()
.set_header("Product")
.set_total_label("Totals"),
TableColumn::new()
.set_header("Quarter 1")
.set_total_function(TableFunction::Sum),
TableColumn::new()
.set_header("Quarter 2")
.set_total_function(TableFunction::Sum),
TableColumn::new()
.set_header("Quarter 3")
.set_total_function(TableFunction::Sum),
TableColumn::new()
.set_header("Quarter 4")
.set_total_function(TableFunction::Sum),
TableColumn::new()
.set_header("Year")
.set_total_function(TableFunction::Sum)
.set_formula("SUM(Table10[@[Quarter 1]:[Quarter 4]])"),
];
table.set_columns(&columns);
table.set_total_row(true);
// Add the table to the worksheet.
worksheet.add_table(2, 1, 7, 6, &table)?;
// -----------------------------------------------------------------------
// Example 11. Table with alternative Excel style.
// -----------------------------------------------------------------------
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
let caption = "Table with alternative Excel style.";
// Set the columns widths for clarity.
for col_num in 1..=6u16 {
worksheet.set_column_width(col_num, 12)?;
}
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the table data.
worksheet.write_column(3, 1, items)?;
worksheet.write_row_matrix(3, 2, data)?;
// Create and configure a new table.
let mut table = Table::new();
let columns = vec![
TableColumn::new()
.set_header("Product")
.set_total_label("Totals"),
TableColumn::new()
.set_header("Quarter 1")
.set_total_function(TableFunction::Sum),
TableColumn::new()
.set_header("Quarter 2")
.set_total_function(TableFunction::Sum),
TableColumn::new()
.set_header("Quarter 3")
.set_total_function(TableFunction::Sum),
TableColumn::new()
.set_header("Quarter 4")
.set_total_function(TableFunction::Sum),
TableColumn::new()
.set_header("Year")
.set_total_function(TableFunction::Sum)
.set_formula("SUM(Table11[@[Quarter 1]:[Quarter 4]])"),
];
table.set_columns(&columns);
table.set_total_row(true);
table.set_style(TableStyle::Light11);
// Add the table to the worksheet.
worksheet.add_table(2, 1, 7, 6, &table)?;
// -----------------------------------------------------------------------
// Example 12. Table with Excel style removed.
// -----------------------------------------------------------------------
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
let caption = "Table with Excel style removed.";
// Set the columns widths for clarity.
for col_num in 1..=6u16 {
worksheet.set_column_width(col_num, 12)?;
}
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the table data.
worksheet.write_column(3, 1, items)?;
worksheet.write_row_matrix(3, 2, data)?;
// Create and configure a new table.
let mut table = Table::new();
let columns = vec![
TableColumn::new()
.set_header("Product")
.set_total_label("Totals"),
TableColumn::new()
.set_header("Quarter 1")
.set_total_function(TableFunction::Sum),
TableColumn::new()
.set_header("Quarter 2")
.set_total_function(TableFunction::Sum),
TableColumn::new()
.set_header("Quarter 3")
.set_total_function(TableFunction::Sum),
TableColumn::new()
.set_header("Quarter 4")
.set_total_function(TableFunction::Sum),
TableColumn::new()
.set_header("Year")
.set_total_function(TableFunction::Sum)
.set_formula("SUM(Table12[@[Quarter 1]:[Quarter 4]])"),
];
table.set_columns(&columns);
table.set_total_row(true);
table.set_style(TableStyle::None);
// Add the table to the worksheet.
worksheet.add_table(2, 1, 7, 6, &table)?;
// Save the file to disk.
workbook.save("tables.xlsx")?;
Ok(())
}
I've started work on supporting worksheet tables.
Tables in Excel are a way of grouping a range of cells into a single entity that has common formatting or that can be referenced from formulas. Tables can have column headers, autofilters, total rows, column formulas and default formatting.
See the Python docs for details: https://xlsxwriter.readthedocs.io/working_with_tables.html
Here is an example of one of the tables generated by the example program below.
There is still work to do on parameter validation, documentation and some minor features. It also doesn't currently support cell formatting. That will have to wait for a subsequent release.