use crate::shared_strings::write_text_node;
use crate::writer::{Result, XmlWriter};
use std::io::Write;
use xlsbye_core::types::{
Cell, CellValue, ColumnInfo, MergeCells, PaneInfo, RangeRef, RichTextRun, RowInfo,
SelectionInfo, SheetFormatInfo, SheetViewInfo,
};
use xlsbye_core::xml_names::{RELATIONSHIPS_NS, SPREADSHEET_NS};
#[derive(Debug, Clone, PartialEq)]
pub struct SheetRow {
pub info: RowInfo,
pub cells: Vec<Cell>,
}
#[derive(Debug, Clone, PartialEq, Default)]
pub struct SheetData {
pub dimension: Option<RangeRef>,
pub sheet_views: Vec<SheetViewInfo>,
pub sheet_format: Option<SheetFormatInfo>,
pub columns: Vec<ColumnInfo>,
pub rows: Vec<SheetRow>,
pub merge_cells: MergeCells,
}
pub fn write_worksheet(writer: impl Write, sheet_data: &SheetData) -> Result<()> {
let mut writer = XmlWriter::new(writer);
writer.write_xml_declaration()?;
writer.write_start_element_with_ns(
"worksheet",
[
("", SPREADSHEET_NS),
("r", RELATIONSHIPS_NS),
("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"),
],
std::iter::empty::<(&str, &str)>(),
)?;
if let Some(dimension) = &sheet_data.dimension {
writer.write_empty_element("dimension", [("ref", range_ref_to_a1(dimension))])?;
}
if !sheet_data.sheet_views.is_empty() {
writer.write_start_element("sheetViews", std::iter::empty::<(&str, &str)>())?;
for sheet_view in &sheet_data.sheet_views {
write_sheet_view(&mut writer, sheet_view)?;
}
writer.write_end_element("sheetViews")?;
}
if let Some(sheet_format) = &sheet_data.sheet_format {
let mut attrs = vec![("defaultRowHeight".to_string(), trim_float(sheet_format.default_row_height))];
if sheet_format.outline_level_row != 0 {
attrs.push(("outlineLevelRow".to_string(), sheet_format.outline_level_row.to_string()));
}
if sheet_format.outline_level_col != 0 {
attrs.push(("outlineLevelCol".to_string(), sheet_format.outline_level_col.to_string()));
}
attrs.push(("x14ac:dyDescent".to_string(), "0.2".to_string()));
writer.write_empty_element("sheetFormatPr", attrs)?;
}
if !sheet_data.columns.is_empty() {
writer.write_start_element("cols", std::iter::empty::<(&str, &str)>())?;
for column in &sheet_data.columns {
let mut attrs = vec![
("min".to_string(), column.min.to_string()),
("max".to_string(), column.max.to_string()),
("width".to_string(), column.width.to_string()),
];
if column.style_index != 0 {
attrs.push(("style".to_string(), column.style_index.to_string()));
}
if column.hidden {
attrs.push(("hidden".to_string(), "1".to_string()));
}
if column.best_fit {
attrs.push(("bestFit".to_string(), "1".to_string()));
}
if column.custom_width {
attrs.push(("customWidth".to_string(), "1".to_string()));
}
if column.outline_level != 0 {
attrs.push(("outlineLevel".to_string(), column.outline_level.to_string()));
}
if column.collapsed {
attrs.push(("collapsed".to_string(), "1".to_string()));
}
writer.write_empty_element("col", attrs)?;
}
writer.write_end_element("cols")?;
}
writer.write_start_element("sheetData", std::iter::empty::<(&str, &str)>())?;
for row in &sheet_data.rows {
write_row(&mut writer, row, sheet_data.sheet_format.as_ref())?;
}
writer.write_end_element("sheetData")?;
if !sheet_data.merge_cells.is_empty() {
writer.write_start_element(
"mergeCells",
[("count", sheet_data.merge_cells.len().to_string())],
)?;
for merge in &sheet_data.merge_cells {
writer.write_empty_element("mergeCell", [("ref", range_ref_to_a1(&merge.range))])?;
}
writer.write_end_element("mergeCells")?;
}
writer.write_end_element("worksheet")?;
Ok(())
}
fn write_row<W: Write>(writer: &mut XmlWriter<W>, row: &SheetRow, sheet_format: Option<&SheetFormatInfo>) -> Result<()> {
let mut attrs = vec![("r".to_string(), row.info.row.to_string())];
if let Some((min_col, max_col)) = row.cells.first().zip(row.cells.last()).map(|(first, last)| (first.col, last.col)) {
attrs.push(("spans".to_string(), format!("{min_col}:{max_col}")));
}
let default_row_height = sheet_format.map(|format| format.default_row_height).unwrap_or(15.0);
if row.info.custom_height || (row.info.height - default_row_height).abs() > 0.0001 {
attrs.push(("ht".to_string(), trim_float(row.info.height)));
}
if row.info.custom_height {
attrs.push(("customHeight".to_string(), "1".to_string()));
}
if row.info.style_index != 0 {
attrs.push(("s".to_string(), row.info.style_index.to_string()));
attrs.push(("customFormat".to_string(), "1".to_string()));
}
if row.info.hidden {
attrs.push(("hidden".to_string(), "1".to_string()));
}
if row.info.outline_level != 0 {
attrs.push(("outlineLevel".to_string(), row.info.outline_level.to_string()));
}
if row.info.collapsed {
attrs.push(("collapsed".to_string(), "1".to_string()));
}
if row.info.thick_top {
attrs.push(("thickTop".to_string(), "1".to_string()));
}
if row.info.thick_bottom {
attrs.push(("thickBot".to_string(), "1".to_string()));
}
attrs.push(("x14ac:dyDescent".to_string(), "0.2".to_string()));
writer.write_start_element("row", attrs)?;
for cell in &row.cells {
write_cell(writer, row.info.row, cell)?;
}
writer.write_end_element("row")
}
fn trim_float(value: f64) -> String {
if value.fract() == 0.0 {
format!("{value:.0}")
} else {
let text = value.to_string();
text.trim_end_matches('0').trim_end_matches('.').to_string()
}
}
fn write_sheet_view<W: Write>(writer: &mut XmlWriter<W>, sheet_view: &SheetViewInfo) -> Result<()> {
let mut attrs = vec![("workbookViewId".to_string(), sheet_view.workbook_view_id.to_string())];
if !sheet_view.show_grid_lines {
attrs.push(("showGridLines".to_string(), "0".to_string()));
}
if sheet_view.tab_selected {
attrs.push(("tabSelected".to_string(), "1".to_string()));
}
if sheet_view.top_left_row != 0 || sheet_view.top_left_col != 0 {
attrs.push((
"topLeftCell".to_string(),
cell_ref_zero_based_to_a1(sheet_view.top_left_row, sheet_view.top_left_col),
));
}
if let Some(zoom_scale) = sheet_view.zoom_scale {
attrs.push(("zoomScale".to_string(), zoom_scale.to_string()));
}
if let Some(zoom_scale_normal) = sheet_view.zoom_scale_normal {
attrs.push(("zoomScaleNormal".to_string(), zoom_scale_normal.to_string()));
}
writer.write_start_element("sheetView", attrs)?;
if let Some(pane) = &sheet_view.pane {
write_pane(writer, pane)?;
}
for selection in &sheet_view.selections {
write_selection(writer, selection)?;
}
writer.write_end_element("sheetView")
}
fn write_pane<W: Write>(writer: &mut XmlWriter<W>, pane: &PaneInfo) -> Result<()> {
let mut attrs = vec![
("xSplit".to_string(), split_to_string(pane.x_split)),
("ySplit".to_string(), split_to_string(pane.y_split)),
(
"topLeftCell".to_string(),
cell_ref_zero_based_to_a1(pane.top_left_row, pane.top_left_col),
),
("activePane".to_string(), pane.active_pane.clone()),
("state".to_string(), pane.state.clone()),
];
attrs.retain(|(_, value)| !value.is_empty());
writer.write_empty_element("pane", attrs)
}
fn write_selection<W: Write>(writer: &mut XmlWriter<W>, selection: &SelectionInfo) -> Result<()> {
let mut attrs = Vec::new();
if let Some(pane) = &selection.pane {
attrs.push(("pane".to_string(), pane.clone()));
}
attrs.push(("activeCell".to_string(), selection.active_cell.clone()));
attrs.push(("sqref".to_string(), selection.sqref.clone()));
writer.write_empty_element("selection", attrs)
}
fn split_to_string(value: f64) -> String {
if value.fract() == 0.0 {
format!("{value:.0}")
} else {
value.to_string()
}
}
fn cell_ref_zero_based_to_a1(row: u32, col: u32) -> String {
format!("{}{}", col_to_name(col + 1), row + 1)
}
fn write_cell<W: Write>(writer: &mut XmlWriter<W>, row: u32, cell: &Cell) -> Result<()> {
let mut attrs = vec![
("r".to_string(), cell_ref_to_a1(row, cell.col)),
("s".to_string(), cell.style_index.to_string()),
];
let mut inline_string_runs: Option<&[RichTextRun]> = None;
let mut inline_string_text: Option<&str> = None;
let mut value_text: Option<String> = None;
match &cell.value {
CellValue::Blank => {}
CellValue::Bool(v) => {
attrs.push(("t".to_string(), "b".to_string()));
value_text = Some(if *v { "1".to_string() } else { "0".to_string() });
}
CellValue::Number(v) => {
value_text = Some(v.to_string());
}
CellValue::Error(err) => {
attrs.push(("t".to_string(), "e".to_string()));
value_text = Some(err.as_str().to_string());
}
CellValue::String(text) => {
if cell.formula.is_some() || cell.shared_formula_index.is_some() {
attrs.push(("t".to_string(), "str".to_string()));
value_text = Some(text.clone());
} else {
attrs.push(("t".to_string(), "inlineStr".to_string()));
inline_string_text = Some(text.as_str());
}
}
CellValue::SharedString(index) => {
attrs.push(("t".to_string(), "s".to_string()));
value_text = Some(index.to_string());
}
CellValue::RichString(runs) => {
attrs.push(("t".to_string(), "inlineStr".to_string()));
inline_string_runs = Some(runs);
}
}
if cell.formula.is_none()
&& cell.shared_formula_index.is_none()
&& inline_string_runs.is_none()
&& inline_string_text.is_none()
&& value_text.is_none()
{
writer.write_empty_element("c", attrs)?;
return Ok(());
}
writer.write_start_element("c", attrs)?;
if cell.formula.is_some() || cell.shared_formula_index.is_some() {
let mut formula_attrs = Vec::new();
if let Some(shared_index) = cell.shared_formula_index {
formula_attrs.push(("t".to_string(), "shared".to_string()));
formula_attrs.push(("si".to_string(), shared_index.to_string()));
if let Some(shared_ref) = &cell.shared_formula_ref {
formula_attrs.push(("ref".to_string(), range_ref_to_a1(shared_ref)));
}
}
if let Some(formula) = &cell.formula {
writer.write_text_element("f", formula_attrs, formula)?;
} else {
writer.write_empty_element("f", formula_attrs)?;
}
}
if let Some(text) = inline_string_text {
writer.write_start_element("is", std::iter::empty::<(&str, &str)>())?;
write_text_node(writer, "t", text)?;
writer.write_end_element("is")?;
} else if let Some(runs) = inline_string_runs {
writer.write_start_element("is", std::iter::empty::<(&str, &str)>())?;
for run in runs {
writer.write_start_element("r", std::iter::empty::<(&str, &str)>())?;
if let Some(font_index) = run.font_index {
writer.write_start_element("rPr", std::iter::empty::<(&str, &str)>())?;
writer.write_empty_element("rFont", [("val", format!("font{}", font_index))])?;
writer.write_end_element("rPr")?;
}
write_text_node(writer, "t", &run.text)?;
writer.write_end_element("r")?;
}
writer.write_end_element("is")?;
} else if let Some(v) = value_text {
writer.write_text_element("v", std::iter::empty::<(&str, &str)>(), &v)?;
}
writer.write_end_element("c")
}
fn range_ref_to_a1(range: &RangeRef) -> String {
format!(
"{}:{}",
cell_ref_to_a1(range.first_row, range.first_col),
cell_ref_to_a1(range.last_row, range.last_col)
)
}
fn cell_ref_to_a1(row: u32, col: u32) -> String {
format!("{}{}", col_to_name(col), row)
}
fn col_to_name(mut col: u32) -> String {
let mut letters = Vec::new();
while col > 0 {
let rem = ((col - 1) % 26) as u8;
letters.push((b'A' + rem) as char);
col = (col - 1) / 26;
}
letters.iter().rev().collect()
}