use crate::data::Sheet;
use std::io::Cursor;
use std::path::Path;
use umya_spreadsheet::{Style, Workbook};
mod formula;
pub use formula::shift_rows;
type Result<T> = std::result::Result<T, Box<dyn std::error::Error>>;
pub enum TemplateSource<'a> {
Path(&'a Path),
Bytes(&'a [u8]),
}
impl<'a> From<&'a Path> for TemplateSource<'a> {
fn from(p: &'a Path) -> Self {
TemplateSource::Path(p)
}
}
impl<'a> From<&'a [u8]> for TemplateSource<'a> {
fn from(b: &'a [u8]) -> Self {
TemplateSource::Bytes(b)
}
}
impl<'a> From<&'a Vec<u8>> for TemplateSource<'a> {
fn from(b: &'a Vec<u8>) -> Self {
TemplateSource::Bytes(b.as_slice())
}
}
pub fn render<'a>(source: impl Into<TemplateSource<'a>>, sheet: &Sheet) -> Result<Workbook> {
Ok(render_with_warnings(source, sheet)?.0)
}
pub fn render_with_warnings<'a>(
source: impl Into<TemplateSource<'a>>,
sheet: &Sheet,
) -> Result<(Workbook, Vec<String>)> {
let mut book = read_template(source.into())?;
let model = extract_template(&book, &sheet.template)?;
let warnings = collect_warnings(&model, sheet);
fill_in_place(&mut book, sheet, &model)?;
Ok((book, warnings))
}
pub fn render_to_file<'a>(
source: impl Into<TemplateSource<'a>>,
sheet: &Sheet,
output_path: impl AsRef<Path>,
) -> Result<Vec<String>> {
let (book, warnings) = render_with_warnings(source, sheet)?;
let path = output_path.as_ref();
umya_spreadsheet::writer::xlsx::write(&book, path)
.map_err(|e| format!("writing {}: {e:?}", path.display()))?;
Ok(warnings)
}
pub fn render_to_bytes<'a>(
source: impl Into<TemplateSource<'a>>,
sheet: &Sheet,
) -> Result<Vec<u8>> {
let book = render(source, sheet)?;
let mut buf = Vec::new();
umya_spreadsheet::writer::xlsx::write_writer(&book, &mut buf)
.map_err(|e| format!("serializing workbook: {e:?}"))?;
Ok(buf)
}
fn read_template(source: TemplateSource) -> Result<Workbook> {
match source {
TemplateSource::Path(path) => umya_spreadsheet::reader::xlsx::read(path)
.map_err(|e| format!("reading template {}: {e:?}", path.display()).into()),
TemplateSource::Bytes(bytes) => {
umya_spreadsheet::reader::xlsx::read_reader(Cursor::new(bytes), true)
.map_err(|e| format!("reading template from {} bytes: {e:?}", bytes.len()).into())
}
}
}
enum Param {
Named(String),
Indexed(usize),
}
struct TplCell {
col: u32,
content: String,
is_formula: bool,
param: Option<Param>,
style: Style,
}
struct TplRow {
row: u32,
label: String,
cells: Vec<TplCell>,
merges: Vec<(u32, u32)>,
height: Option<f64>,
}
struct TplModel {
rows: Vec<TplRow>,
schemas: std::collections::HashMap<String, Vec<String>>,
}
const TAG_COL: u32 = 1;
fn parse_tag(raw: &str) -> (String, Option<Vec<String>>) {
let raw = raw.trim();
if let Some(open) = raw.find('(')
&& raw.ends_with(')')
{
let label = raw[..open].trim().to_string();
let names = raw[open + 1..raw.len() - 1]
.split(',')
.map(|s| s.trim().to_string())
.filter(|s| !s.is_empty())
.collect();
return (label, Some(names));
}
(raw.to_string(), None)
}
fn parse_param(comment: &str) -> Option<Param> {
let hash = comment.find('#')?;
let token: String = comment[hash + 1..]
.chars()
.take_while(|c| c.is_alphanumeric() || *c == '_')
.collect();
if token.is_empty() {
return None;
}
match token.parse::<usize>() {
Ok(n) if n >= 1 => Some(Param::Indexed(n)),
Ok(_) => None,
Err(_) => Some(Param::Named(token)),
}
}
fn comment_text(comment: &umya_spreadsheet::Comment) -> String {
let ct = comment.text();
if let Some(t) = ct.text() {
let s = t.value();
if !s.is_empty() {
return s.to_string();
}
}
if let Some(rt) = ct.rich_text() {
return rt.text().into_owned();
}
String::new()
}
fn extract_template(book: &Workbook, sheet_name: &str) -> Result<TplModel> {
let ws = book
.sheet_by_name(sheet_name)
.map_err(|e| format!("template sheet `{sheet_name}` not found: {e:?}"))?;
let (max_col, max_row) = ws.highest_column_and_row();
let mut merges_by_row: std::collections::HashMap<u32, Vec<(u32, u32)>> = Default::default();
for range in ws.merge_cells() {
let (c1, r1, c2, r2) = parse_range(&range.range());
if r1 == r2 {
merges_by_row.entry(r1).or_default().push((c1, c2));
}
}
let mut params_by_cell: std::collections::HashMap<(u32, u32), Param> = Default::default();
for comment in ws.comments() {
if let Some(param) = parse_param(&comment_text(comment)) {
let co = comment.coordinate();
params_by_cell.insert((co.col_num(), co.row_num()), param);
}
}
let mut rows = Vec::new();
let mut schemas: std::collections::HashMap<String, Vec<String>> = Default::default();
for r in 1..=max_row {
let (label, schema) = parse_tag(&ws.value(coord(TAG_COL, r)));
if let Some(names) = schema {
schemas.entry(label.clone()).or_insert(names);
}
let mut cells = Vec::new();
for c in (TAG_COL + 1)..=max_col {
if let Some(cell) = ws.cell(coord(c, r)) {
let is_formula = cell.is_formula();
let content = if is_formula {
cell.formula().to_string()
} else {
cell.value().to_string()
};
cells.push(TplCell {
col: c,
content,
is_formula,
param: params_by_cell.remove(&(c, r)),
style: cell.style().clone(),
});
}
}
rows.push(TplRow {
row: r,
label,
cells,
merges: merges_by_row.remove(&r).unwrap_or_default(),
height: ws.row_dimension(r).map(|rd| rd.height()),
});
}
Ok(TplModel { rows, schemas })
}
fn is_singleton_label(label: &str) -> bool {
matches!(label, "header" | "footer")
}
fn is_detail_label(label: &str) -> bool {
!label.is_empty() && !is_singleton_label(label)
}
fn collect_warnings(model: &TplModel, sheet: &Sheet) -> Vec<String> {
use std::collections::HashSet;
let template_labels: HashSet<&str> = model
.rows
.iter()
.map(|r| r.label.as_str())
.filter(|l| !l.is_empty())
.collect();
let mut warnings = Vec::new();
let mut reported: HashSet<&str> = HashSet::new();
for rec in &sheet.records {
if !template_labels.contains(rec.label.as_str()) && reported.insert(rec.label.as_str()) {
warnings.push(format!(
"data label `{}` has no matching template row — those records are ignored",
rec.label
));
}
}
warnings
}
fn record_data(
sheet: &Sheet,
label: &str,
) -> (Vec<String>, std::collections::BTreeMap<String, String>) {
sheet
.records
.iter()
.find(|rec| rec.label == label)
.map(|rec| (rec.fields.clone(), rec.named.clone()))
.unwrap_or_default()
}
fn detail_band(model: &TplModel) -> Option<(u32, u32, usize)> {
let rows: Vec<&TplRow> = model
.rows
.iter()
.filter(|r| is_detail_label(&r.label))
.collect();
rows.first()
.map(|f| (f.row, rows[rows.len() - 1].row, rows.len()))
}
fn fill_in_place(book: &mut Workbook, sheet: &Sheet, model: &TplModel) -> Result<()> {
let band = detail_band(model);
let detail_records: Vec<&crate::data::Record> = sheet
.records
.iter()
.filter(|r| is_detail_label(&r.label))
.collect();
let m = detail_records.len();
let shift = band.map_or(0, |(_, _, h)| m as i64 - h as i64);
let band_end = band.map(|(_, e, _)| e);
let detail_rows: Vec<&TplRow> = model
.rows
.iter()
.filter(|r| is_detail_label(&r.label))
.collect();
let ws = book
.sheet_by_name_mut(&sheet.template)
.map_err(|e| format!("template sheet `{}` not found: {e:?}", sheet.template))?;
if let Some((start, end, h)) = band {
let k = m as i64 - h as i64;
if k > 0 {
ws.insert_new_row(end, k as u32);
} else if k < 0 {
ws.remove_row(start, (-k) as u32);
}
}
ws.column_dimension_by_number_mut(TAG_COL).set_hidden(true);
ws.comments_mut().clear();
for trow in &model.rows {
if !is_singleton_label(&trow.label) {
continue;
}
let cur = match band_end {
Some(end) if trow.row > end => (trow.row as i64 + shift) as u32,
_ => trow.row,
};
let (fields, named) = record_data(sheet, &trow.label);
let schema = model.schemas.get(&trow.label);
for cell in &trow.cells {
if let Some(param) = &cell.param {
ws.cell_mut(coord(cell.col, cur).as_str())
.set_value(resolve_param(param, schema, &fields, &named));
}
}
}
if let Some((start, _, h)) = band
&& m > 0
{
let (lo, hi) = (start, start + m as u32 - 1);
ws.merge_cells_mut().retain(|rng| {
let (_, r1, _, r2) = parse_range(&rng.range());
!(r1 >= lo && r2 <= hi)
});
for (i, rec) in detail_records.iter().enumerate() {
let target = start + i as u32;
let src = detail_rows
.iter()
.find(|r| r.label == rec.label)
.copied()
.unwrap_or(detail_rows[i % h]);
let delta = target as i64 - src.row as i64;
let schema = model.schemas.get(&src.label);
if let Some(height) = src.height
&& height > 0.0
{
ws.row_dimension_mut(target).set_height(height);
}
for cell in &src.cells {
let c = ws.cell_mut(coord(cell.col, target).as_str());
c.set_style(cell.style.clone());
if cell.is_formula {
c.set_formula(formula::shift_rows(&cell.content, delta));
} else if let Some(param) = &cell.param {
c.set_value(resolve_param(param, schema, &rec.fields, &rec.named));
} else {
c.set_value(cell.content.clone());
}
}
for &(c1, c2) in &src.merges {
ws.add_merge_cells(format!("{}:{}", coord(c1, target), coord(c2, target)));
}
}
}
for cell in ws.cells_mut() {
if cell.is_formula() {
cell.set_formula_result_default("");
}
}
let title = if sheet.title.trim().is_empty() {
sheet.template.as_str()
} else {
sheet.title.trim()
};
if title != sheet.template {
retarget_chart_series(ws, &sheet.template, title);
ws.set_name(title);
}
Ok(())
}
fn retarget_chart_series(ws: &mut umya_spreadsheet::Worksheet, from: &str, to: &str) {
for chart in ws.chart_collection_mut() {
for series in chart.area_chart_series_list_mut().area_chart_series_mut() {
for formula in series.formula_mut() {
let address = formula.address_mut();
if address.sheet_name() == from {
address.set_sheet_name(to);
}
}
}
}
}
fn resolve_param(
param: &Param,
schema: Option<&Vec<String>>,
fields: &[String],
named: &std::collections::BTreeMap<String, String>,
) -> String {
match param {
Param::Indexed(n) => fields.get(n - 1).cloned().unwrap_or_default(),
Param::Named(name) => {
if let Some(v) = named.get(name) {
return v.clone();
}
schema
.and_then(|names| names.iter().position(|x| x == name))
.and_then(|i| fields.get(i))
.cloned()
.unwrap_or_default()
}
}
}
fn coord(col: u32, row: u32) -> String {
format!("{}{}", column_letter(col), row)
}
fn column_letter(mut col: u32) -> String {
let mut s = Vec::new();
while col > 0 {
let rem = ((col - 1) % 26) as u8;
s.push(b'A' + rem);
col = (col - 1) / 26;
}
s.reverse();
String::from_utf8(s).unwrap()
}
fn column_index(letters: &str) -> u32 {
let mut idx = 0u32;
for ch in letters.bytes() {
idx = idx * 26 + (ch.to_ascii_uppercase() - b'A' + 1) as u32;
}
idx
}
fn parse_range(range: &str) -> (u32, u32, u32, u32) {
let (a, b) = match range.split_once(':') {
Some((a, b)) => (a, b),
None => (range, range),
};
let (c1, r1) = parse_cell_ref(a);
let (c2, r2) = parse_cell_ref(b);
(c1, r1, c2, r2)
}
fn parse_cell_ref(s: &str) -> (u32, u32) {
let s = s.replace('$', "");
let split = s.find(|c: char| c.is_ascii_digit()).unwrap_or(s.len());
let (letters, digits) = s.split_at(split);
(column_index(letters), digits.parse().unwrap_or(1))
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn column_letters_round_trip() {
for (n, s) in [
(1, "A"),
(2, "B"),
(26, "Z"),
(27, "AA"),
(28, "AB"),
(53, "BA"),
] {
assert_eq!(column_letter(n), s);
assert_eq!(column_index(s), n);
}
}
#[test]
fn parses_ranges() {
assert_eq!(parse_range("B1:E1"), (2, 1, 5, 1));
assert_eq!(parse_range("E10"), (5, 10, 5, 10));
assert_eq!(parse_range("$A$3:$C$3"), (1, 3, 3, 3));
}
#[test]
fn parses_column_a_tag_and_schema() {
let (label, schema) = parse_tag("header(date, receipt, customer)");
assert_eq!(label, "header");
assert_eq!(
schema,
Some(vec!["date".into(), "receipt".into(), "customer".into()])
);
assert_eq!(parse_tag("row1"), ("row1".to_string(), None));
assert_eq!(parse_tag(" footer "), ("footer".to_string(), None));
}
#[test]
fn parses_param_markers() {
assert!(matches!(parse_param("#price"), Some(Param::Named(n)) if n == "price"));
assert!(matches!(
parse_param("see #2 here"),
Some(Param::Indexed(2))
));
assert!(parse_param("no marker").is_none());
assert!(parse_param("#").is_none());
}
#[test]
fn resolves_named_and_indexed_params() {
use std::collections::BTreeMap;
let schema = vec!["date".to_string(), "receipt".to_string()];
let fields = vec!["1/5/2009".to_string(), "22215".to_string()];
let empty = BTreeMap::new();
assert_eq!(
resolve_param(
&Param::Named("receipt".into()),
Some(&schema),
&fields,
&empty
),
"22215"
);
assert_eq!(
resolve_param(&Param::Indexed(1), Some(&schema), &fields, &empty),
"1/5/2009"
);
let named: BTreeMap<String, String> = [("receipt".to_string(), "99999".to_string())]
.into_iter()
.collect();
assert_eq!(
resolve_param(&Param::Named("receipt".into()), None, &[], &named),
"99999"
);
assert_eq!(
resolve_param(&Param::Named("nope".into()), Some(&schema), &fields, &empty),
""
);
assert_eq!(resolve_param(&Param::Indexed(9), None, &fields, &empty), "");
}
}