use super::Book;
use super::convert::{count, list, object, opt_count, opt_str, ranges_to_js};
#[cfg(feature = "write")]
use super::js;
use super::style::run_font_to_js;
#[cfg(feature = "write")]
use crate::coordinate::{CellRef, Col, Range};
use crate::model::chart::{
ChartColor, ColorTransform, Fill, GradientPath, LineFormat, ShapeFormat,
};
use wasm_bindgen::prelude::*;
#[wasm_bindgen]
impl Book {
#[wasm_bindgen(unchecked_return_type = "SheetComment[]")]
pub fn comments(&self, sheet: usize) -> Result<JsValue, JsError> {
Ok(list(&self.sheet_of(sheet)?.comments, |(at, comment)| {
object(&[
("address", JsValue::from_str(&at.to_string())),
("author", JsValue::from_str(&comment.author)),
("text", JsValue::from_str(&comment.plain_text())),
])
}))
}
#[wasm_bindgen(unchecked_return_type = "SheetHyperlink[]")]
pub fn hyperlinks(&self, sheet: usize) -> Result<JsValue, JsError> {
use crate::model::LinkTarget;
Ok(list(&self.sheet_of(sheet)?.hyperlinks, |link| {
let (target, external) = match &link.target {
LinkTarget::Inside(to) => (to, false),
LinkTarget::Outside(to) => (to, true),
};
object(&[
("range", JsValue::from_str(&link.range.to_string())),
("target", JsValue::from_str(target)),
("external", JsValue::from_bool(external)),
("display", opt_str(link.display.as_deref())),
("tooltip", opt_str(link.tooltip.as_deref())),
])
}))
}
#[wasm_bindgen(unchecked_return_type = "SheetTable[]")]
pub fn tables(&self, sheet: usize) -> Result<JsValue, JsError> {
Ok(list(&self.sheet_of(sheet)?.tables, |table| {
object(&[
("name", JsValue::from_str(&table.name)),
("displayName", JsValue::from_str(&table.display_name)),
("range", JsValue::from_str(&table.range.to_string())),
("headerRowCount", opt_count(table.header_row_count)),
("totalsRowCount", opt_count(table.totals_row_count)),
(
"columns",
list(&table.columns, |column| JsValue::from_str(&column.name)),
),
])
}))
}
#[wasm_bindgen(unchecked_return_type = "SheetChart[]")]
pub fn charts(&self, sheet: usize) -> Result<JsValue, JsError> {
Ok(list(&self.sheet_of(sheet)?.charts, |chart| {
let title = chart
.title
.as_ref()
.and_then(|t| t.text.as_ref())
.and_then(crate::model::chart::ChartText::shown);
object(&[
("name", JsValue::from_str(&chart.name)),
("title", opt_str(title)),
(
"kinds",
list(&chart.plots, |plot| JsValue::from_str(plot.kind.element())),
),
(
"seriesCount",
count(chart.plots.iter().map(|p| p.series.len()).sum()),
),
("anchor", anchor_to_js(&chart.anchor)),
("format", self.format_to_js(chart.format.as_ref())),
("plotFormat", self.format_to_js(chart.plot_format.as_ref())),
])
}))
}
#[wasm_bindgen(unchecked_return_type = "SheetImage[]")]
pub fn images(&self, sheet: usize) -> Result<JsValue, JsError> {
Ok(list(&self.sheet_of(sheet)?.images, |image| {
object(&[
("name", JsValue::from_str(&image.name)),
("description", JsValue::from_str(&image.description)),
("format", JsValue::from_str(image.format.extension())),
("byteLength", count(image.data.len())),
("anchor", anchor_to_js(&image.anchor)),
])
}))
}
#[wasm_bindgen(js_name = imageData)]
pub fn image_data(&self, sheet: usize, index: usize) -> Result<Vec<u8>, JsError> {
self.sheet_of(sheet)?
.images
.get(index)
.map(|image| image.data.clone())
.ok_or_else(|| JsError::new("no such image"))
}
#[wasm_bindgen(unchecked_return_type = "SheetShape[]")]
pub fn shapes(&self, sheet: usize) -> Result<JsValue, JsError> {
Ok(list(&self.sheet_of(sheet)?.shapes, |shape| {
object(&[
("name", JsValue::from_str(&shape.name)),
("description", JsValue::from_str(&shape.description)),
("geometry", opt_str(shape.geometry.as_deref())),
("text", JsValue::from_str(&shape.text)),
("anchor", anchor_to_js(&shape.anchor)),
(
"rotation",
JsValue::from_f64(f64::from(shape.rotation) / 60_000.0),
),
("flipH", JsValue::from_bool(shape.flip_h)),
("flipV", JsValue::from_bool(shape.flip_v)),
("fill", self.fill_to_js(&shape.effective_fill())),
("line", self.line_to_js(&shape.effective_line())),
("font", run_font_to_js(&shape.effective_font())),
])
}))
}
#[wasm_bindgen(js_name = definedNames, unchecked_return_type = "WorkbookName[]")]
#[must_use]
pub fn defined_names(&self) -> JsValue {
list(&self.workbook.defined_names, |name| {
object(&[
("name", JsValue::from_str(&name.name)),
("formula", JsValue::from_str(&name.formula)),
("sheet", name.sheet.map_or(JsValue::NULL, count)),
("hidden", JsValue::from_bool(name.hidden)),
])
})
}
#[wasm_bindgen(js_name = dataValidations, unchecked_return_type = "SheetValidation[]")]
pub fn data_validations(&self, sheet: usize) -> Result<JsValue, JsError> {
Ok(list(&self.sheet_of(sheet)?.data_validations, |rule| {
object(&[
("sqref", ranges_to_js(&rule.sqref)),
("type", JsValue::from_str(rule.kind.as_str())),
("operator", JsValue::from_str(rule.operator.as_str())),
("formula1", JsValue::from_str(&rule.formula1)),
("formula2", JsValue::from_str(&rule.formula2)),
("allowBlank", JsValue::from_bool(rule.allow_blank)),
("showDropDown", JsValue::from_bool(!rule.hide_drop_down)),
])
}))
}
#[wasm_bindgen(
js_name = conditionalFormats,
unchecked_return_type = "SheetConditionalFormat[]"
)]
pub fn conditional_formats(&self, sheet: usize) -> Result<JsValue, JsError> {
Ok(list(&self.sheet_of(sheet)?.conditional_formats, |block| {
let rules = list(&block.rules, |rule| {
object(&[
("type", JsValue::from_str(rule.kind.as_str())),
("priority", JsValue::from_f64(f64::from(rule.priority))),
(
"operator",
opt_str(rule.operator.map(crate::model::CfOperator::as_str)),
),
("formulas", list(&rule.formulas, JsValue::from)),
("text", opt_str(rule.text.as_deref())),
])
});
object(&[("sqref", ranges_to_js(&block.sqref)), ("rules", rules)])
}))
}
#[wasm_bindgen(js_name = autoFilter, unchecked_return_type = "SheetAutoFilter | undefined")]
pub fn auto_filter(&self, sheet: usize) -> Result<JsValue, JsError> {
use crate::model::autofilter::ColumnFilter;
let Some(filter) = &self.sheet_of(sheet)?.auto_filter else {
return Ok(JsValue::UNDEFINED);
};
let columns = list(&filter.columns, |column| {
let kind = match &column.filter {
Some(ColumnFilter::Values { .. }) => "values",
Some(ColumnFilter::Custom { .. }) => "custom",
Some(ColumnFilter::Dynamic { .. }) => "dynamic",
Some(ColumnFilter::Top10 { .. }) => "top10",
Some(ColumnFilter::Color { .. }) => "color",
Some(ColumnFilter::Icon { .. }) => "icon",
None => "none",
};
object(&[
("colId", JsValue::from_f64(f64::from(column.col_id))),
("kind", JsValue::from_str(kind)),
])
});
Ok(object(&[
("range", JsValue::from_str(&filter.range.to_string())),
("columns", columns),
]))
}
#[wasm_bindgen(js_name = pivotTables, unchecked_return_type = "SheetPivotTable[]")]
pub fn pivot_tables(&self, sheet: usize) -> Result<JsValue, JsError> {
Ok(list(&self.sheet_of(sheet)?.pivot_tables, |pivot| {
object(&[
("name", JsValue::from_str(&pivot.name)),
(
"location",
opt_str(pivot.location.map(|r| r.to_string()).as_deref()),
),
("cacheId", JsValue::from_f64(f64::from(pivot.cache_id))),
("rowFields", count(pivot.row_fields.len())),
("columnFields", count(pivot.column_fields.len())),
("valueFields", count(pivot.data_fields.len())),
])
}))
}
#[wasm_bindgen(js_name = arrayFormulas)]
pub fn array_formulas(&self, sheet: usize) -> Result<Vec<String>, JsError> {
Ok(self
.sheet_of(sheet)?
.array_formulas
.iter()
.map(ToString::to_string)
.collect())
}
#[wasm_bindgen(js_name = externalBooks)]
#[must_use]
pub fn external_books(&self) -> Vec<JsValue> {
self.workbook
.external
.iter()
.map(|book| opt_str(book.path.as_deref()))
.collect()
}
}
#[cfg(feature = "write")]
#[wasm_bindgen]
impl Book {
#[wasm_bindgen(js_name = setComment)]
pub fn set_comment(
&mut self,
sheet: usize,
address: &str,
author: &str,
text: &str,
) -> Result<(), JsError> {
let at = CellRef::parse(address).map_err(js)?;
let comment = crate::model::Comment {
author: author.to_owned(),
text: vec![crate::model::TextRun {
text: text.to_owned(),
font: None,
}],
..crate::model::Comment::default()
};
self.sheet_mut(sheet)?.comments.insert(at, comment);
Ok(())
}
#[wasm_bindgen(js_name = removeComment)]
pub fn remove_comment(&mut self, sheet: usize, address: &str) -> Result<bool, JsError> {
let at = CellRef::parse(address).map_err(js)?;
Ok(self.sheet_mut(sheet)?.comments.remove(&at).is_some())
}
#[wasm_bindgen(js_name = setHyperlink)]
pub fn set_hyperlink(
&mut self,
sheet: usize,
range: &str,
target: &str,
inside: Option<bool>,
display: Option<String>,
tooltip: Option<String>,
) -> Result<(), JsError> {
use crate::model::{Hyperlink, LinkTarget};
let area = Range::parse(range).map_err(js)?;
let target = if inside.unwrap_or(false) {
LinkTarget::Inside(target.to_owned())
} else {
LinkTarget::Outside(target.to_owned())
};
let links = &mut self.sheet_mut(sheet)?.hyperlinks;
links.retain(|link| link.range != area);
links.push(Hyperlink {
range: area,
target,
display,
tooltip,
});
Ok(())
}
#[wasm_bindgen(js_name = removeHyperlink)]
pub fn remove_hyperlink(&mut self, sheet: usize, range: &str) -> Result<bool, JsError> {
let area = Range::parse(range).map_err(js)?;
let links = &mut self.sheet_mut(sheet)?.hyperlinks;
let before = links.len();
links.retain(|link| link.range != area);
Ok(links.len() != before)
}
#[wasm_bindgen(js_name = addTable)]
pub fn add_table(
&mut self,
sheet: usize,
name: &str,
range: &str,
header_row: Option<bool>,
) -> Result<(), JsError> {
use crate::model::table::{Table, TableColumn, TableStyle};
let area = Range::parse(range).map_err(js)?;
let taken = self
.workbook
.sheets()
.iter()
.flat_map(|ws| &ws.tables)
.any(|t| t.display_name.eq_ignore_ascii_case(name));
if taken {
return Err(JsError::new(
"a table of that name is already in the workbook",
));
}
let header = header_row.unwrap_or(true);
let ws = self.sheet_mut(sheet)?;
let columns = (area.start.col.index()..=area.end.col.index())
.zip(1..)
.map(|(col, id)| {
let named = Col::new(col)
.filter(|_| header)
.and_then(|col| ws.get(CellRef::new(col, area.start.row)))
.and_then(|cell| cell.value.plain_text())
.filter(|text| !text.is_empty());
TableColumn {
id,
name: named.unwrap_or_else(|| format!("Column{id}")),
totals_row_function: None,
totals_row_label: None,
calculated_formula: None,
}
})
.collect();
let id = u32::try_from(ws.tables.len() + 1).unwrap_or(u32::MAX);
ws.tables.push(Table {
id,
name: name.to_owned(),
display_name: name.to_owned(),
range: area,
header_row_count: Some(u32::from(header)),
totals_row_count: None,
auto_filter: header.then(|| crate::model::AutoFilter::new(area)),
sort_state: None,
columns,
style: Some(TableStyle::default()),
extensions: None,
});
Ok(())
}
#[wasm_bindgen(js_name = setTableFilter)]
pub fn set_table_filter(
&mut self,
name: &str,
#[wasm_bindgen(unchecked_param_type = "string | number")] column: &JsValue,
#[wasm_bindgen(unchecked_param_type = "TableFilter | null")] criteria: &JsValue,
) -> Result<(), JsError> {
let columns = self
.workbook
.sheets()
.iter()
.flat_map(|ws| &ws.tables)
.find(|t| {
t.display_name.eq_ignore_ascii_case(name) || t.name.eq_ignore_ascii_case(name)
})
.map(|t| t.columns.iter().map(|c| c.name.clone()).collect::<Vec<_>>())
.ok_or_else(|| JsError::new(&format!("no table called {name}")))?;
let offset = if let Some(header) = column.as_string() {
columns
.iter()
.position(|c| c.to_lowercase() == header.to_lowercase())
.ok_or_else(|| JsError::new(&format!("table {name} has no column {header:?}")))?
} else {
column
.as_f64()
.filter(|n| n.fract() == 0.0 && *n >= 1.0 && *n <= 16_384.0)
.map(|n| {
#[expect(
clippy::cast_possible_truncation,
clippy::cast_sign_loss,
reason = "a whole column number checked just above"
)]
let n = n as usize;
n - 1
})
.ok_or_else(|| JsError::new("a column is a header or a number from 1"))?
};
let filter = if criteria.is_null() || criteria.is_undefined() {
None
} else {
Some(table_filter_of(criteria)?)
};
let offset = u32::try_from(offset).map_err(|_| JsError::new("no such column"))?;
crate::edit::filter_table(&mut self.workbook, name, offset, filter).map_err(js)
}
#[wasm_bindgen(js_name = removeTable)]
pub fn remove_table(&mut self, sheet: usize, name: &str) -> Result<bool, JsError> {
let tables = &mut self.sheet_mut(sheet)?.tables;
let before = tables.len();
tables.retain(|table| !table.display_name.eq_ignore_ascii_case(name));
Ok(tables.len() != before)
}
#[wasm_bindgen(js_name = setDefinedName)]
pub fn set_defined_name(
&mut self,
name: &str,
formula: &str,
sheet: Option<usize>,
) -> Result<(), JsError> {
if name.is_empty() {
return Err(JsError::new("a defined name is not empty"));
}
if let Some(index) = sheet {
self.sheet_of(index)?;
}
self.remove_defined_name(name, sheet);
self.workbook.defined_names.push(crate::model::DefinedName {
name: name.to_owned(),
sheet,
formula: formula.to_owned(),
hidden: false,
});
self.forget_dependencies();
Ok(())
}
#[wasm_bindgen(js_name = removeDefinedName)]
pub fn remove_defined_name(&mut self, name: &str, sheet: Option<usize>) -> bool {
let names = &mut self.workbook.defined_names;
let before = names.len();
names.retain(|existing| !(existing.name == name && existing.sheet == sheet));
let removed = names.len() != before;
if removed {
self.forget_dependencies();
}
removed
}
}
fn anchor_to_js(anchor: &crate::model::chart::Anchor) -> JsValue {
use crate::model::chart::Anchor;
let at = match anchor {
Anchor::TwoCell { from, .. } | Anchor::OneCell { from, .. } => Some(from),
Anchor::Absolute { .. } => None,
};
let kind = match anchor {
Anchor::TwoCell { .. } => "twoCell",
Anchor::OneCell { .. } => "oneCell",
Anchor::Absolute { .. } => "absolute",
};
object(&[
("kind", JsValue::from_str(kind)),
("row", opt_count(at.map(|m| m.row.one_based()))),
("column", opt_count(at.map(|m| m.col.one_based()))),
])
}
impl Book {
fn chart_color_to_js(&self, color: &ChartColor) -> JsValue {
color
.resolve(self.workbook.theme.as_deref())
.map_or(JsValue::NULL, |rgb| {
let alpha = color.transforms.iter().rev().find_map(|t| match t {
ColorTransform::Alpha(a) => {
Some((a.clamp(&0, &100_000) * 255 + 50_000) / 100_000)
}
_ => None,
});
JsValue::from_str(&format!("#{:02X}{rgb:06X}", alpha.unwrap_or(0xFF)))
})
}
fn opt_color(&self, color: Option<&ChartColor>) -> JsValue {
color.map_or(JsValue::NULL, |c| self.chart_color_to_js(c))
}
fn fill_to_js(&self, fill: &Fill) -> JsValue {
let kind = |name: &str| ("type", JsValue::from_str(name));
match fill {
Fill::None => object(&[kind("none")]),
Fill::Solid(color) => {
object(&[kind("solid"), ("color", self.chart_color_to_js(color))])
}
Fill::Gradient { stops, angle, path } => object(&[
kind("gradient"),
(
"stops",
list(stops, |stop| {
object(&[
(
"position",
JsValue::from_f64(f64::from(stop.position) / 1000.0),
),
("color", self.chart_color_to_js(&stop.color)),
])
}),
),
(
"angle",
angle.map_or(JsValue::NULL, |a| {
JsValue::from_f64(f64::from(a) / 60_000.0)
}),
),
("path", opt_str(path.map(GradientPath::as_str))),
]),
Fill::Pattern {
preset,
foreground,
background,
} => object(&[
kind("pattern"),
("preset", opt_str(preset.as_deref())),
("foreground", self.opt_color(foreground.as_ref())),
("background", self.opt_color(background.as_ref())),
]),
Fill::Other => object(&[kind("other")]),
}
}
fn line_to_js(&self, line: &LineFormat) -> JsValue {
object(&[
(
"fill",
line.fill
.as_ref()
.map_or(JsValue::NULL, |f| self.fill_to_js(f)),
),
(
"width",
line.width.map_or(JsValue::NULL, |w| {
JsValue::from_f64(f64::from(w) / 12_700.0)
}),
),
])
}
fn format_to_js(&self, format: Option<&ShapeFormat>) -> JsValue {
format.map_or(JsValue::NULL, |format| {
object(&[
(
"fill",
format
.fill
.as_ref()
.map_or(JsValue::NULL, |f| self.fill_to_js(f)),
),
(
"line",
format
.line
.as_ref()
.map_or(JsValue::NULL, |l| self.line_to_js(l)),
),
])
})
}
}
#[cfg(feature = "write")]
fn table_filter_of(criteria: &JsValue) -> Result<crate::model::autofilter::ColumnFilter, JsError> {
use super::convert::{array, field};
use crate::model::autofilter::{ColumnFilter, CustomFilter, FilterOperator};
let spelled = |v: &JsValue| {
v.as_string()
.or_else(|| v.as_f64().map(|n| n.to_string()))
.ok_or_else(|| JsError::new("a filter value is a string or a number"))
};
let flag = |key: &str| field(criteria, key).is_some_and(|v| v.is_truthy());
if let Some(values) = field(criteria, "values") {
return Ok(ColumnFilter::Values {
blank: flag("blank"),
values: array(&values, "values")?
.iter()
.map(|v| spelled(&v))
.collect::<Result<_, _>>()?,
date_groups: Vec::new(),
});
}
if let Some(rules) = field(criteria, "custom") {
let rules = array(&rules, "custom")?
.iter()
.map(|rule| {
let op = field(&rule, "op")
.and_then(|o| o.as_string())
.unwrap_or_else(|| "=".into());
let operator = match op.as_str() {
"=" => FilterOperator::Equal,
"<>" | "!=" => FilterOperator::NotEqual,
">" => FilterOperator::GreaterThan,
">=" => FilterOperator::GreaterThanOrEqual,
"<" => FilterOperator::LessThan,
"<=" => FilterOperator::LessThanOrEqual,
other => {
return Err(JsError::new(&format!(
"a comparison is =, <>, >, >=, < or <=, not {other:?}"
)));
}
};
let value = field(&rule, "value")
.ok_or_else(|| JsError::new("a comparison has a value"))?;
Ok(CustomFilter {
operator,
value: spelled(&value)?,
})
})
.collect::<Result<Vec<_>, JsError>>()?;
if rules.is_empty() || rules.len() > 2 {
return Err(JsError::new("custom takes one or two comparisons"));
}
return Ok(ColumnFilter::Custom {
and: flag("and"),
rules,
});
}
for (key, top) in [("top", true), ("bottom", false)] {
if let Some(count) = field(criteria, key) {
let n = count
.as_f64()
.filter(|n| *n > 0.0 && n.is_finite())
.ok_or_else(|| JsError::new(&format!("{key} is a positive number")))?;
return Ok(ColumnFilter::Top10 {
value: Some(n.to_string()),
percent: flag("percent"),
top,
filter_value: None,
});
}
}
Err(JsError::new(
"a filter is { values }, { custom }, { top } or { bottom }",
))
}