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> {
let mut book = read_template(source.into())?;
let model = extract_template(&book, &sheet.template)?;
let plan = plan_rows(&model, sheet);
book.remove_sheet_by_name(&sheet.template)
.map_err(|e| format!("removing template sheet `{}`: {e:?}", sheet.template))?;
let title = if sheet.title.trim().is_empty() {
"Report"
} else {
sheet.title.trim()
};
write_output(&mut book, title, &model, &plan)?;
Ok(book)
}
pub fn render_to_file<'a>(
source: impl Into<TemplateSource<'a>>,
sheet: &Sheet,
output_path: impl AsRef<Path>,
) -> Result<()> {
let book = render(source, sheet)?;
let path = output_path.as_ref();
umya_spreadsheet::writer::xlsx::write(&book, path)
.map_err(|e| format!("writing {}: {e:?}", path.display()))?;
Ok(())
}
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())
}
}
}
struct TplCell {
col: u32,
content: String,
is_formula: bool,
style: Style,
}
struct TplRow {
row: u32,
tag: String,
cells: Vec<TplCell>,
merges: Vec<(u32, u32)>,
}
struct TplModel {
rows: Vec<TplRow>,
col_widths: Vec<(u32, f64)>,
}
const TAG_COL: u32 = 1;
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 rows = Vec::new();
for r in 1..=max_row {
let tag = ws.value(coord(TAG_COL, r)).trim().to_string();
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,
style: cell.style().clone(),
});
}
}
rows.push(TplRow {
row: r,
tag,
cells,
merges: merges_by_row.remove(&r).unwrap_or_default(),
});
}
let mut col_widths = Vec::new();
for c in 1..=max_col {
if let Some(col) = ws.column_dimension_by_number(c) {
col_widths.push((c, col.width()));
}
}
Ok(TplModel { rows, col_widths })
}
struct Emit<'a> {
tpl: &'a TplRow,
fields: Vec<String>,
out_row: u32,
}
struct Plan<'a> {
emits: Vec<Emit<'a>>,
detail_first: Option<u32>,
detail_last: Option<u32>,
}
fn is_singleton_tag(tag: &str) -> bool {
matches!(tag, "header" | "footer")
}
fn is_detail_tag(tag: &str) -> bool {
!tag.is_empty() && !is_singleton_tag(tag)
}
fn plan_rows<'a>(model: &'a TplModel, sheet: &Sheet) -> Plan<'a> {
let record_fields = |label: &str| -> Vec<String> {
sheet
.records
.iter()
.find(|rec| rec.label == label)
.map(|rec| rec.fields.clone())
.unwrap_or_default()
};
let detail_records: Vec<&crate::data::Record> = sheet
.records
.iter()
.filter(|rec| is_detail_tag(&rec.label))
.collect();
let mut emits = Vec::new();
let mut out_row: u32 = 1;
let mut detail_first = None;
let mut detail_last = None;
let mut i = 0;
while i < model.rows.len() {
let row = &model.rows[i];
if is_detail_tag(&row.tag) {
let band_start = i;
while i < model.rows.len() && is_detail_tag(&model.rows[i].tag) {
i += 1;
}
let band = &model.rows[band_start..i];
for rec in &detail_records {
let tpl = band
.iter()
.find(|tr| tr.tag == rec.label)
.unwrap_or(&band[0]);
if detail_first.is_none() {
detail_first = Some(out_row);
}
detail_last = Some(out_row);
emits.push(Emit {
tpl,
fields: rec.fields.clone(),
out_row,
});
out_row += 1;
}
} else {
let fields = if is_singleton_tag(&row.tag) {
record_fields(&row.tag)
} else {
Vec::new()
};
emits.push(Emit {
tpl: row,
fields,
out_row,
});
out_row += 1;
i += 1;
}
}
Plan {
emits,
detail_first,
detail_last,
}
}
fn write_output(book: &mut Workbook, title: &str, model: &TplModel, plan: &Plan) -> Result<()> {
let ws = book
.new_sheet(title)
.map_err(|e| format!("creating output sheet `{title}`: {e:?}"))?;
for &(c, w) in &model.col_widths {
ws.column_dimension_by_number_mut(c).set_width(w);
}
ws.column_dimension_by_number_mut(TAG_COL).set_hidden(true);
for emit in &plan.emits {
let delta = emit.out_row as i64 - emit.tpl.row as i64;
for tcell in &emit.tpl.cells {
let at = coord(tcell.col, emit.out_row);
let cell = ws.cell_mut(at.as_str());
cell.set_style(tcell.style.clone());
if tcell.is_formula {
let shifted = formula::shift_rows(&tcell.content, delta);
let resolved = substitute(&shifted, &emit.fields, emit.out_row, plan);
cell.set_formula(resolved);
} else {
let resolved = substitute(&tcell.content, &emit.fields, emit.out_row, plan);
cell.set_value(resolved);
}
}
for &(c1, c2) in &emit.tpl.merges {
let range = format!("{}:{}", coord(c1, emit.out_row), coord(c2, emit.out_row));
ws.add_merge_cells(range);
}
}
Ok(())
}
fn substitute(text: &str, fields: &[String], out_row: u32, plan: &Plan) -> String {
let mut out = String::with_capacity(text.len());
let bytes = text.as_bytes();
let mut i = 0;
while i < bytes.len() {
if bytes[i] == b'$'
&& i + 1 < bytes.len()
&& bytes[i + 1] == b'{'
&& let Some(end) = text[i + 2..].find('}')
{
let key = text[i + 2..i + 2 + end].trim();
out.push_str(&resolve_key(key, fields, out_row, plan));
i = i + 2 + end + 1;
continue;
}
let ch = text[i..].chars().next().unwrap();
out.push(ch);
i += ch.len_utf8();
}
out
}
fn resolve_key(key: &str, fields: &[String], out_row: u32, plan: &Plan) -> String {
match key {
"row" => out_row.to_string(),
"firstrow" => plan.detail_first.unwrap_or(out_row).to_string(),
"lastrow" => plan.detail_last.unwrap_or(out_row).to_string(),
_ => match key.parse::<usize>() {
Ok(n) if n >= 1 && n <= fields.len() => fields[n - 1].clone(),
_ => String::new(),
},
}
}
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 resolves_placeholders() {
let plan = Plan {
emits: vec![],
detail_first: Some(7),
detail_last: Some(9),
};
let fields = vec!["1/5/2009".to_string(), "22215".to_string()];
assert_eq!(
substitute("Date: ${1}", &fields, 2, &plan),
"Date: 1/5/2009"
);
assert_eq!(substitute("#${2}", &fields, 2, &plan), "#22215");
assert_eq!(
substitute("SUM(E${firstrow}:E${lastrow})", &fields, 12, &plan),
"SUM(E7:E9)"
);
assert_eq!(substitute("${row}", &fields, 12, &plan), "12");
assert_eq!(substitute("${9}", &fields, 2, &plan), "");
}
}