use umya_spreadsheet::structs::drawing::charts::{CategoryAxisData, StringReference};
use umya_spreadsheet::structs::drawing::spreadsheet::MarkerType;
use umya_spreadsheet::{
BorderStyleValues, Chart, ChartType, Color, Comment, ConditionalFormatValueObject,
ConditionalFormatValueObjectValues, ConditionalFormatValues, ConditionalFormatting,
ConditionalFormattingOperatorValues, ConditionalFormattingRule, DataBar,
HorizontalAlignmentValues, SequenceOfReferences, Style, Worksheet,
};
const SHADE: &str = "FFF2F2F2"; const HEAD_FILL: &str = "FFD9E1F2"; const CURRENCY: &str = "$#,##0.00";
pub fn sample_sales_receipt_template_bytes() -> Vec<u8> {
let mut book = umya_spreadsheet::new_file();
book.set_sheet_name(0, "SalesReceipt")
.expect("rename sheet");
let ws = book.sheet_by_name_mut("SalesReceipt").expect("sheet");
ws.column_dimension_mut("A").set_width(16.0);
ws.column_dimension_mut("B").set_width(8.0);
ws.column_dimension_mut("C").set_width(40.0);
ws.column_dimension_mut("D").set_width(13.0);
ws.column_dimension_mut("E").set_width(13.0);
set(
ws,
"B1",
"SALES RECEIPT",
sb().bold().size(16.0).center().done(),
);
ws.add_merge_cells("B1:E1");
set(ws, "A2", "header(date,receipt,customer,address)", plain());
set(ws, "B2", "Receipt #:", sb().bold().done());
param(ws, "C2", "NNNNN", "receipt", plain());
set(ws, "D2", "Date:", sb().bold().right().done());
param(ws, "E2", "MM/DD/YYYY", "date", sb().right().done());
set(ws, "A3", "header", plain());
set(ws, "B3", "Sold To:", sb().bold().done());
param(ws, "C3", "Customer Name", "customer", plain());
ws.add_merge_cells("C3:E3");
set(ws, "A4", "header", plain());
param(ws, "C4", "Street, City, State ZIP", "address", plain());
ws.add_merge_cells("C4:E4");
set(ws, "B6", "Qty", sb().bold().right().fill(HEAD_FILL).done());
set(ws, "C6", "Description", sb().bold().fill(HEAD_FILL).done());
set(
ws,
"D6",
"Unit Price",
sb().bold().right().fill(HEAD_FILL).done(),
);
set(
ws,
"E6",
"Amount",
sb().bold().right().fill(HEAD_FILL).done(),
);
set(ws, "A7", "row1(seq,qty,desc,price)", plain());
param(ws, "B7", "2", "qty", sb().right().done());
param(ws, "C7", "Sample item", "desc", plain());
param(ws, "D7", "9.99", "price", sb().right().fmt(CURRENCY).done());
setf(ws, "E7", "B7*D7", sb().right().fmt(CURRENCY).done());
set(ws, "A8", "row2(seq,qty,desc,price)", plain());
param(ws, "B8", "3", "qty", sb().right().fill(SHADE).done());
param(
ws,
"C8",
"Another sample item",
"desc",
sb().fill(SHADE).done(),
);
param(
ws,
"D8",
"4.99",
"price",
sb().right().fill(SHADE).fmt(CURRENCY).done(),
);
setf(
ws,
"E8",
"B8*D8",
sb().right().fill(SHADE).fmt(CURRENCY).done(),
);
set(ws, "A10", "footer(taxrate)", plain());
set(ws, "D10", "Subtotal:", sb().bold().right().done());
setf(ws, "E10", "SUM(E$7:E8)", sb().right().fmt(CURRENCY).done());
set(ws, "A11", "footer", plain());
set(ws, "C11", "Sales Tax", sb().right().done());
param(
ws,
"D11",
"0.1",
"taxrate",
sb().right().fmt("0.00%").done(),
);
setf(ws, "E11", "E10*D11", sb().right().fmt(CURRENCY).done());
set(ws, "A12", "footer", plain());
set(ws, "D12", "TOTAL:", sb().bold().right().done());
setf(
ws,
"E12",
"E10+E11",
sb().bold().right().fmt(CURRENCY).done(),
);
let mut buf = Vec::new();
umya_spreadsheet::writer::xlsx::write_writer(&book, &mut buf).expect("serialize template");
buf
}
const LOGO_PNG: &[u8] = include_bytes!("../templates/northwind_logo.png");
const NAVY: &str = "FF1F3A5F"; const TEAL: &str = "FF26A69A"; const GREY: &str = "FF5A6473"; const BAND: &str = "FFF4F7FB"; const WHITE: &str = "FFFFFFFF";
const GAIN: &str = "FF1E8449"; const GAIN_FILL: &str = "FFE9F7EF";
const LOSS: &str = "FFC0392B"; const LOSS_FILL: &str = "FFFCECEA";
const PCT: &str = "0.0%";
const INT: &str = "#,##0";
pub fn sample_portfolio_statement_template_bytes() -> Vec<u8> {
let mut book = umya_spreadsheet::new_file();
book.set_sheet_name(0, "Portfolio").expect("rename sheet");
let ws = book.sheet_by_name_mut("Portfolio").expect("sheet");
ws.column_dimension_mut("A").set_width(26.0);
ws.column_dimension_mut("B").set_width(20.0);
ws.column_dimension_mut("C").set_width(8.0);
ws.column_dimension_mut("D").set_width(15.0);
ws.column_dimension_mut("E").set_width(15.0);
ws.column_dimension_mut("F").set_width(15.0);
ws.column_dimension_mut("G").set_width(11.0);
ws.row_dimension_mut(1).set_height(30.0);
ws.row_dimension_mut(2).set_height(18.0);
set(
ws,
"E1",
"PORTFOLIO STATEMENT",
sb().bold().size(18.0).right().color(NAVY).done(),
);
ws.add_merge_cells("E1:G1");
set(
ws,
"E2",
"Monthly Holdings Summary",
sb().size(10.0).right().color(GREY).done(),
);
ws.add_merge_cells("E2:G2");
add_logo(ws, "B1");
set(ws, "A4", "header(account,holder,period,asof)", plain());
set(ws, "B4", "Account No.", sb().bold().color(GREY).done());
param(ws, "C4", "0000-000000", "account", plain());
set(
ws,
"E4",
"Statement Period",
sb().bold().right().color(GREY).done(),
);
param(ws, "F4", "Month YYYY", "period", sb().right().done());
ws.add_merge_cells("F4:G4");
set(ws, "A5", "header", plain());
set(ws, "B5", "Account Holder", sb().bold().color(GREY).done());
param(ws, "C5", "Holder Name", "holder", plain());
ws.add_merge_cells("C5:D5");
set(ws, "E5", "Prepared", sb().bold().right().color(GREY).done());
param(ws, "F5", "MM/DD/YYYY", "asof", sb().right().done());
ws.add_merge_cells("F5:G5");
let head = || sb().bold().color(WHITE).fill(NAVY);
set(ws, "B7", "Security", head().done());
set(ws, "C7", "Qty", head().right().done());
set(ws, "D7", "Cost Basis", head().right().done());
set(ws, "E7", "Market Value", head().right().done());
set(ws, "F7", "Gain / Loss", head().right().done());
set(ws, "G7", "Return %", head().right().done());
detail_row(ws, 8, None);
detail_row(ws, 9, Some(BAND));
set(ws, "A11", "footer", plain());
set(
ws,
"B11",
"TOTAL",
sb().bold().color(NAVY).top_border(NAVY).done(),
);
for col in ["C", "D", "E", "F", "G"] {
set(ws, &format!("{col}11"), "", sb().top_border(NAVY).done());
}
let total_money = || {
sb().bold()
.right()
.color(NAVY)
.fmt(CURRENCY)
.top_border(NAVY)
};
setf(ws, "D11", "SUM(D$8:D9)", total_money().done());
setf(ws, "E11", "SUM(E$8:E9)", total_money().done());
setf(ws, "F11", "SUM(F$8:F9)", total_money().done());
setf(
ws,
"G11",
"F11/D11",
sb().bold()
.right()
.color(NAVY)
.fmt(PCT)
.top_border(NAVY)
.done(),
);
add_gain_loss_rules(ws, "F8:F9");
add_market_data_bar(ws, "E8:E9");
add_column_chart(
ws,
"Market Value by Holding",
"B13",
"G29",
"Portfolio!$E$8:$E$9",
"Portfolio!$B$8:$B$9",
);
add_column_chart(
ws,
"Gain / Loss by Holding",
"B31",
"G47",
"Portfolio!$F$8:$F$9",
"Portfolio!$B$8:$B$9",
);
let mut buf = Vec::new();
umya_spreadsheet::writer::xlsx::write_writer(&book, &mut buf).expect("serialize template");
buf
}
fn detail_row(ws: &mut Worksheet, row: u32, fill: Option<&str>) {
let tint = |s: StyleBuilder| match fill {
Some(argb) => s.fill(argb),
None => s,
};
let label = if fill.is_some() { "row2" } else { "row1" };
set(
ws,
&format!("A{row}"),
&format!("{label}(symbol,qty,cost,market)"),
plain(),
);
param(
ws,
&format!("B{row}"),
"TICKER",
"symbol",
tint(sb()).done(),
);
param(
ws,
&format!("C{row}"),
"0",
"qty",
tint(sb().right().fmt(INT)).done(),
);
param(
ws,
&format!("D{row}"),
"0",
"cost",
tint(sb().right().fmt(CURRENCY)).done(),
);
param(
ws,
&format!("E{row}"),
"0",
"market",
tint(sb().right().fmt(CURRENCY)).done(),
);
setf(
ws,
&format!("F{row}"),
&format!("E{row}-D{row}"),
tint(sb().right().fmt(CURRENCY)).done(),
);
setf(
ws,
&format!("G{row}"),
&format!("F{row}/D{row}"),
tint(sb().right().fmt(PCT)).done(),
);
}
fn add_logo(ws: &mut Worksheet, coord: &str) {
let mut marker = MarkerType::default();
marker.set_coordinate(coord);
let mut image = umya_spreadsheet::Image::default();
image.new_image_with_dimensions(34, 200, "northwind_logo.png", LOGO_PNG.to_vec(), marker);
ws.add_image(image);
}
fn add_gain_loss_rules(ws: &mut Worksheet, range: &str) {
let rule = |op: ConditionalFormattingOperatorValues, font: &str, fill: &str, priority: i32| {
let mut style = Style::default();
style.font_mut().set_bold(true);
style.font_mut().color_mut().set_argb_str(font);
style.set_background_color(fill);
let mut formula = umya_spreadsheet::Formula::default();
formula.set_string_value("0");
let mut r = ConditionalFormattingRule::default();
r.set_type(ConditionalFormatValues::CellIs);
r.set_operator(op);
r.set_formula(formula);
r.set_priority(priority);
r.set_style(style);
r
};
let mut seq = SequenceOfReferences::default();
seq.set_sqref(range);
let mut cf = ConditionalFormatting::default();
cf.set_sequence_of_references(seq);
cf.add_conditional_collection(rule(
ConditionalFormattingOperatorValues::LessThan,
LOSS,
LOSS_FILL,
1,
));
cf.add_conditional_collection(rule(
ConditionalFormattingOperatorValues::GreaterThan,
GAIN,
GAIN_FILL,
2,
));
ws.add_conditional_formatting_collection(cf);
}
fn add_market_data_bar(ws: &mut Worksheet, range: &str) {
let cfvo = |kind: ConditionalFormatValueObjectValues| {
let mut o = ConditionalFormatValueObject::default();
o.set_type(kind);
o
};
let mut bar = DataBar::default();
bar.add_cfvo_collection(cfvo(ConditionalFormatValueObjectValues::Min));
bar.add_cfvo_collection(cfvo(ConditionalFormatValueObjectValues::Max));
let mut color = Color::default();
color.set_argb_str(TEAL);
bar.add_color_collection(color);
let mut rule = ConditionalFormattingRule::default();
rule.set_type(ConditionalFormatValues::DataBar);
rule.set_priority(3);
rule.set_data_bar(bar);
let mut seq = SequenceOfReferences::default();
seq.set_sqref(range);
let mut cf = ConditionalFormatting::default();
cf.set_sequence_of_references(seq);
cf.add_conditional_collection(rule);
ws.add_conditional_formatting_collection(cf);
}
fn add_column_chart(
ws: &mut Worksheet,
title: &str,
from: &str,
to: &str,
values: &str,
categories: &str,
) {
let mut from_marker = MarkerType::default();
from_marker.set_coordinate(from);
let mut to_marker = MarkerType::default();
to_marker.set_coordinate(to);
let mut chart = Chart::default();
chart.new_chart(&ChartType::BarChart, from_marker, to_marker, vec![values]);
chart.set_series_title(vec![title]);
for series in chart.area_chart_series_list_mut().area_chart_series_mut() {
let mut formula = umya_spreadsheet::structs::drawing::charts::Formula::default();
formula.set_address_str(categories);
let mut sref = StringReference::default();
sref.set_formula(formula);
let mut cad = CategoryAxisData::default();
cad.set_string_reference(sref);
series.set_category_axis_data(cad);
}
ws.add_chart(chart);
}
fn set(ws: &mut umya_spreadsheet::Worksheet, at: &str, value: &str, style: Style) {
let cell = ws.cell_mut(at);
cell.set_value(value);
cell.set_style(style);
}
fn setf(ws: &mut umya_spreadsheet::Worksheet, at: &str, formula: &str, style: Style) {
let cell = ws.cell_mut(at);
cell.set_formula(formula);
cell.set_style(style);
}
fn param(ws: &mut umya_spreadsheet::Worksheet, at: &str, value: &str, name: &str, style: Style) {
{
let cell = ws.cell_mut(at);
cell.set_value(value);
cell.set_style(style);
}
let mut comment = Comment::default();
comment.new_comment(at);
comment.set_text_string(format!("#{name}"));
ws.add_comments(comment);
}
fn plain() -> Style {
Style::default()
}
fn sb() -> StyleBuilder {
StyleBuilder {
st: Style::default(),
}
}
struct StyleBuilder {
st: Style,
}
impl StyleBuilder {
fn bold(mut self) -> Self {
self.st.font_mut().set_bold(true);
self
}
fn size(mut self, pt: f64) -> Self {
self.st.font_mut().set_size(pt);
self
}
fn right(mut self) -> Self {
self.st
.alignment_mut()
.set_horizontal(HorizontalAlignmentValues::Right);
self
}
fn center(mut self) -> Self {
self.st
.alignment_mut()
.set_horizontal(HorizontalAlignmentValues::Center);
self
}
fn fill(mut self, argb: &str) -> Self {
self.st.set_background_color(argb);
self
}
fn fmt(mut self, code: &str) -> Self {
self.st.number_format_mut().set_format_code(code);
self
}
fn color(mut self, argb: &str) -> Self {
self.st.font_mut().color_mut().set_argb_str(argb);
self
}
fn top_border(mut self, argb: &str) -> Self {
let mut color = Color::default();
color.set_argb_str(argb);
let top = self.st.borders_mut().top_mut();
top.set_style(BorderStyleValues::Thin);
top.set_color(color);
self
}
fn done(self) -> Style {
self.st
}
}