Worksheet

A worksheet is returned from add_sheet_mut / AddSheet (new sheet) or get_worksheet_mut (existing sheet). All cell, row, column, merge, hyperlink and picture operations are performed through the worksheet object.

Note

In Rust and Go, call flush / Flush on the worksheet before saving the workbook to make sure the sheet data is written. In C# the worksheet implements IDisposable, so a using block flushes it automatically.

Methods

The table below lists worksheet methods currently available and exercised by the language test suites. A dash (—) means the method is not yet surfaced for that language.

Rust	C#	Go	Function
set_cell_index_value_mut(row, col, cells)	—	—	Set cell values starting at a row/column index
set_cell_ref_value_mut(ref, cells)	SetCellRefValues(ref, cells)	SetCellRefValues(ref, cells)	Set cell values starting at a cell reference (e.g. A1)
set_row_index_properties_mut(row, props)	—	—	Set row properties by index
set_column_index_properties_mut(col, props)	SetColumnRefProperties(ref, props)	—	Set column properties
list_merge_cell_()	—	—	List merged cell ranges
set_merge_cell_mut(range)	—	—	Merge a cell range
remove_merge_cell_mut(range)	—	—	Remove a merged cell range
list_hyperlinks()	—	—	List hyperlinks in the sheet
set_hyperlink_mut(text, url, range)	—	—	Add a hyperlink
remove_hyperlink_mut(range)	—	—	Remove a hyperlink
get_range_cell_properties(range)	—	—	Read cell properties for a range
add_picture(path, setting)	—	—	Add a picture (see Picture)
delete_sheet_mut()	—	—	Delete this worksheet
flush()	Dispose() (via using)	Flush()	Write pending sheet data

Sheet Code Samples

To add, get, rename and delete a sheet from the workbook.

Rust

use draviavemal_openxml_office::spreadsheet_2007::{Excel, ExcelPropertiesModel};

let mut file = Excel::new(None, ExcelPropertiesModel::default())
.expect("Create New File Failed");
// Add Sheet with custom name
file.add_sheet_mut(Some("Test".to_string()))
.expect("Failed to add Sheet");
// Add Sheet with default name
file.add_sheet_mut(None)
.expect("Failed to add Sheet");
// Rename an existing sheet
file.rename_sheet_name_mut("Test".to_string(), "Renamed".to_string())
.expect("Failed to rename the sheet");
// Get an existing sheet and delete it
let sheet = file.get_worksheet_mut("Renamed".to_string())
.expect("Failed to get worksheet");
sheet.delete_sheet_mut().expect("Failed to delete sheet");
file.save_as("./output.xlsx").expect("File Save Failed");

C#

// Add a new sheet (optional name)
using Worksheet worksheet = excel.AddSheet("Data Sheet 2");
// Rename an existing sheet
excel.RenameSheet("Data Sheet 2", "Sheet 1");

Go

worksheet, err := excel.AddSheet("Test")
if err != nil {
panic(err)
}
// ... set data ...
if err := worksheet.Flush(); err != nil {
panic(err)
}

Cell Values Code Sample

Rust

use draviavemal_openxml_office::spreadsheet_2007::models::CellProperty;

let mut sheet = file.add_sheet_mut(Some("Data".to_string()))
.expect("Failed to add Sheet");
// Set by row/column index (row, column)
sheet.set_cell_index_value_mut(
1, 1,
vec![
CellProperty::default().set_value(Some("Value 1".to_string())),
CellProperty::default().set_value(Some("Value 2".to_string())),
CellProperty::default().set_formula(Some("=SUM(A1:B1)".to_string())),
],
).expect("Failed to set value");
// Set by cell reference
sheet.set_cell_ref_value_mut(
"A3",
vec![CellProperty::default().set_value(Some("Ref value".to_string()))],
).expect("Failed to set value");
sheet.flush().expect("Failed to write Data");

C#

using Worksheet worksheet = excel.AddSheet("Data");
StyleId styleId = excel.GetStyleId(new CellStyleSetting()
{
CustomNumberFormat = "00.000",
});
worksheet.SetCellRefValues("A1", new CellProperty[3]{
new(){ Value = "test1", DataType = CellDataType.String },
new(){ Value = "test2", DataType = CellDataType.String },
new(){ Value = "2.51", DataType = CellDataType.Number, StyleId = styleId },
});

Go

cells := []spreadsheet_2007.CellProperty{
{ Value: "10", DataType: spreadsheet_2007.CellDataTypeNumber },
{ Value: "20", DataType: spreadsheet_2007.CellDataTypeNumber },
}
if err := worksheet.SetCellRefValues("A1", cells); err != nil {
panic(err)
}

CellProperty Options

Rust	C# / Go	Type	Details
set_value(value)	Value	string?	Cell value, parsed based on the data type
—	DataType	CellDataType	Data type of the value (String, Number, …)
set_formula(value)	—	string?	Cell formula (e.g. =SUM(A1:B1))
set_style_id(id)	StyleId	StyleId?	Style id returned from the Style component

Column Properties Code Sample

Rust

use draviavemal_openxml_office::spreadsheet_2007::models::ColumnProperties;

sheet.set_column_index_properties_mut(
&1,
Some(ColumnProperties::default().set_width(Some(200.0))),
).expect("Failed to set column width");
sheet.set_column_index_properties_mut(
&3,
Some(ColumnProperties::default().set_hidden(Some(true))),
).expect("Failed to hide column");

C#

worksheet.SetColumnRefProperties("A1", new ColumnProperties()
{
Width = 30
});
worksheet.SetColumnRefProperties("C4", new ColumnProperties()
{
Width = 30,
BestFit = true
});
worksheet.SetColumnRefProperties("G7", new ColumnProperties()
{
Hidden = true
});

ColumnProperties Options

Rust	C#	Type	Details
set_width(value)	Width	float?	Set manual column width
set_best_fit(value)	BestFit	bool?	Auto-fit column width based on content
set_hidden(value)	Hidden	bool?	Hide the column

Row Properties Code Sample

Rust

use draviavemal_openxml_office::spreadsheet_2007::models::RowProperties;

sheet.set_row_index_properties_mut(
&1,
RowProperties::default().set_height(Some(100.0)),
).expect("Failed to set row height");
sheet.set_row_index_properties_mut(
&3,
RowProperties::default().set_hidden(Some(true)),
).expect("Failed to hide row");

RowProperties Options

Rust	Type	Details
set_height(value)	float?	Set row height
set_hidden(value)	bool?	Hide the row
set_thick_top(value)	bool?	Thick top border for the row
set_thick_bottom(value)	bool?	Thick bottom border for the row

Merge Cells Code Sample

Rust

use draviavemal_openxml_office::spreadsheet_2007::models::ReferenceRange;

sheet.set_merge_cell_mut(ReferenceRange {
column_start: 1,
column_end: 3,
row_start: 1,
row_end: 2,
}).expect("Failed to set merge range");
// List existing merges
let merges = sheet.list_merge_cell_();
// Remove a merge
sheet.remove_merge_cell_mut(ReferenceRange {
column_start: 1,
column_end: 3,
row_start: 1,
row_end: 2,
}).expect("Failed to remove merge range");

Hyperlink Code Sample

Rust

use draviavemal_openxml_office::spreadsheet_2007::models::ReferenceRange;

sheet.set_hyperlink_mut(
Some("OpenXML-Office".to_string()),
"https://openxml-office.draviavemal.com/".to_string(),
ReferenceRange { column_start: 1, column_end: 1, row_start: 1, row_end: 1 },
).expect("Failed to set hyperlink");
// List existing hyperlinks
let links = sheet.list_hyperlinks();
// Remove a hyperlink
sheet.remove_hyperlink_mut(
ReferenceRange { column_start: 1, column_end: 1, row_start: 1, row_end: 1 },
).expect("Failed to remove hyperlink");

ReferenceRange Options

Property	Type	Details
column_start	u32	Start column index (1-based)
column_end	u32	End column index (1-based)
row_start	u32	Start row index (1-based)
row_end	u32	End row index (1-based)