use std::collections::HashMap;
use std::path::Path;
use rust_xlsxwriter::{
Color, ExcelDateTime, Format, FormatAlign, FormatBorder, Image, Workbook, Worksheet,
};
use crate::column::{ColumnDef, ColumnKind};
use crate::error::{ExcelError, Result};
use crate::model::ExcelRow;
use crate::value::{CellImage, CellValue};
use crate::write::{SheetOptions, WriteOptions, default_column_width};
pub struct ExcelWriter {
workbook: Workbook,
sheet_count: u32,
}
impl Default for ExcelWriter {
fn default() -> Self {
Self::new()
}
}
impl ExcelWriter {
pub fn new() -> Self {
Self {
workbook: Workbook::new(),
sheet_count: 0,
}
}
pub fn sheet_count(&self) -> u32 {
self.sheet_count
}
pub fn add_sheet(&mut self, options: &SheetOptions) -> Result<SheetWriter<'_>> {
let name = if options.name.is_empty() {
format!("Sheet{}", self.sheet_count + 1)
} else {
options.name.clone()
};
let ws = if options.write.constant_memory {
self.workbook.add_worksheet_with_constant_memory()
} else {
self.workbook.add_worksheet()
};
self.sheet_count += 1;
ws.set_name(&name)?;
let mut writer = SheetWriter::new(ws, options.clone());
writer.prepare()?;
Ok(writer)
}
pub fn save<P: AsRef<Path>>(&mut self, path: P) -> Result<u64> {
let path = path.as_ref();
self.workbook.save(path)?;
Ok(std::fs::metadata(path).map(|m| m.len()).unwrap_or(0))
}
pub fn save_to_buffer(&mut self) -> Result<Vec<u8>> {
Ok(self.workbook.save_to_buffer()?)
}
pub fn workbook(&mut self) -> &mut Workbook {
&mut self.workbook
}
}
pub struct SheetWriter<'a> {
ws: &'a mut Worksheet,
options: SheetOptions,
row_cursor: u32,
last_col: u16,
header_row: Option<u32>,
col_count: u16,
formats: HashMap<String, Format>,
}
impl<'a> SheetWriter<'a> {
fn new(ws: &'a mut Worksheet, options: SheetOptions) -> Self {
let col_count = options.effective_columns().len() as u16;
Self {
ws,
options,
row_cursor: 0,
last_col: col_count.saturating_sub(1),
header_row: None,
col_count,
formats: HashMap::new(),
}
}
fn prepare(&mut self) -> Result<()> {
let columns = self.options.effective_columns();
if !columns.is_empty() {
for (i, col) in columns.iter().enumerate() {
let width = col.width.unwrap_or_else(|| default_column_width(col));
self.ws.set_column_width(i as u16, width)?;
}
if self.options.write.freeze_header {
self.ws.set_freeze_panes(1, 0)?;
}
}
Ok(())
}
pub fn options(&self) -> &SheetOptions {
&self.options
}
pub fn current_row(&self) -> u32 {
self.row_cursor
}
pub fn write_headers(&mut self) -> Result<()> {
self.write_headers_with(&self.options.effective_columns())
}
pub fn write_headers_with(&mut self, columns: &[ColumnDef]) -> Result<()> {
let row = self.row_cursor;
let header_format = self.header_format();
for (i, col) in columns.iter().enumerate() {
if self.options.write.header_style {
let fmt = header_format.clone();
self.ws
.write_string_with_format(row, i as u16, &col.header, &fmt)?;
} else {
self.ws.write_string(row, i as u16, &col.header)?;
}
}
if columns.len() as u16 > self.col_count {
self.col_count = columns.len() as u16;
}
self.last_col = self.last_col.max(columns.len().saturating_sub(1) as u16);
self.header_row = Some(row);
self.row_cursor += 1;
Ok(())
}
pub fn write_headers_marked(&mut self, columns: &[ColumnDef]) -> Result<()> {
let row = self.row_cursor;
let header_format = self.header_format();
let required_format = self.required_header_format();
for (i, col) in columns.iter().enumerate() {
let fmt = if col.required {
&required_format
} else {
&header_format
};
self.ws
.write_string_with_format(row, i as u16, &col.header, fmt)?;
}
self.last_col = self.last_col.max(columns.len().saturating_sub(1) as u16);
self.col_count = self.col_count.max(columns.len() as u16);
self.header_row = Some(row);
self.row_cursor += 1;
Ok(())
}
pub fn write_merged_header_row(&mut self, groups: &[(String, u16)]) -> Result<()> {
let row = self.row_cursor;
let fmt = self.header_format();
let mut col: u16 = 0;
let mut max_col = self.col_count.saturating_sub(1);
for (title, span) in groups {
let span = (*span).max(1);
let last = col.saturating_add(span - 1);
if last > col {
self.ws.merge_range(row, col, row, last, title, &fmt)?;
} else {
self.ws.write_string_with_format(row, col, title, &fmt)?;
}
max_col = max_col.max(last);
col = last.saturating_add(1);
}
self.last_col = self.last_col.max(max_col);
self.col_count = self.col_count.max(col);
self.row_cursor += 1;
Ok(())
}
fn required_header_format(&mut self) -> Format {
let key = "header:required".to_string();
if let Some(f) = self.formats.get(&key) {
return f.clone();
}
let fmt = Format::new()
.set_bold()
.set_align(FormatAlign::Center)
.set_border(FormatBorder::Thin)
.set_background_color(Color::RGB(0xFD_E3_E3))
.set_font_color(Color::Red);
self.formats.insert(key, fmt.clone());
fmt
}
pub fn add_dropdown(
&mut self,
col: u16,
options: &[String],
first_row: u32,
last_row: u32,
prompt: Option<&str>,
) -> Result<()> {
if options.is_empty() {
return Ok(());
}
let list: Vec<&str> = options.iter().map(|s| s.as_str()).collect();
let mut dv = rust_xlsxwriter::DataValidation::new().allow_list_strings(&list)?;
if let Some(p) = prompt {
dv = dv.set_input_message(p)?;
}
self.ws
.add_data_validation(first_row, col, last_row, col, &dv)?;
Ok(())
}
pub fn write_row(&mut self, values: &[CellValue]) -> Result<()> {
let values = self.options.eval_row(values.to_vec());
let row = self.row_cursor;
let has_image = values
.iter()
.any(|v| matches!(v, CellValue::Image(img) if !img.bytes.is_empty()));
if has_image {
self.ws
.set_row_height(row, self.options.write.image_row_height)?;
} else if let Some(h) = self.options.write.default_row_height {
self.ws.set_row_height(row, h)?;
}
let columns = self.options.effective_columns();
for (i, value) in values.iter().enumerate() {
let def = columns.get(i);
self.write_value(row, i as u16, value, def)?;
}
self.row_cursor += 1;
self.last_col = self.last_col.max(values.len().saturating_sub(1) as u16);
Ok(())
}
pub fn write_model<T: ExcelRow>(&mut self, model: &T) -> Result<()> {
self.write_row(&model.to_row())
}
pub fn write_models<T: ExcelRow>(&mut self, models: &[T]) -> Result<()> {
for m in models {
self.write_model(m)?;
}
Ok(())
}
pub fn write_text_row<I, S>(&mut self, texts: I) -> Result<()>
where
I: IntoIterator<Item = S>,
S: AsRef<str>,
{
let row = self.row_cursor;
for (i, t) in texts.into_iter().enumerate() {
self.ws.write_string(row, i as u16, t.as_ref())?;
}
self.row_cursor += 1;
Ok(())
}
pub fn skip_rows(&mut self, n: u32) {
self.row_cursor += n;
}
pub fn write_row_with_merges(
&mut self,
values: &[CellValue],
merges: &[(u16, u16)],
) -> Result<()> {
let values = self.options.eval_row(values.to_vec());
let row = self.row_cursor;
let has_image = values
.iter()
.any(|v| matches!(v, CellValue::Image(img) if !img.bytes.is_empty()));
if has_image {
self.ws
.set_row_height(row, self.options.write.image_row_height)?;
} else if let Some(h) = self.options.write.default_row_height {
self.ws.set_row_height(row, h)?;
}
let mut spans: Vec<(u16, u16)> = merges.iter().map(|(a, b)| (*a.min(b), *a.max(b))).collect();
spans.sort_by_key(|(a, _)| *a);
for (c1, c2) in &spans {
if c1 == c2 {
return Err(ExcelError::Other(
"横向合并区间至少需要 2 列".to_string(),
));
}
}
let len = values.len() as u16;
let max_col = spans
.iter()
.map(|(_, c2)| c2.saturating_add(1))
.chain(std::iter::once(len))
.max()
.unwrap_or(len);
let columns = self.options.effective_columns();
let fmt = Format::new().set_align(FormatAlign::Center);
let mut col = 0u16;
while col < max_col {
if let Some(&(_, c2)) = spans.iter().find(|(c1, _)| *c1 == col) {
let text = values
.get(col as usize)
.map(|v| v.to_text())
.unwrap_or_default();
self.ws.merge_range(row, col, row, c2, &text, &fmt)?;
self.last_col = self.last_col.max(c2);
col = c2.saturating_add(1);
} else {
if col < len {
let def = columns.get(col as usize);
self.write_value(row, col, &values[col as usize], def)?;
}
col += 1;
}
}
self.row_cursor += 1;
self.last_col = self.last_col.max(max_col.saturating_sub(1));
self.col_count = self.col_count.max(max_col);
Ok(())
}
pub fn write_row_with_links(
&mut self,
values: &[CellValue],
links: &[(u16, String, Option<String>)],
) -> Result<()> {
let values = self.options.eval_row(values.to_vec());
let row = self.row_cursor;
let has_image = values
.iter()
.any(|v| matches!(v, CellValue::Image(img) if !img.bytes.is_empty()));
if has_image {
self.ws
.set_row_height(row, self.options.write.image_row_height)?;
} else if let Some(h) = self.options.write.default_row_height {
self.ws.set_row_height(row, h)?;
}
let len = values.len() as u16;
let max_col = links
.iter()
.map(|(c, _, _)| c.saturating_add(1))
.chain(std::iter::once(len))
.max()
.unwrap_or(len);
let columns = self.options.effective_columns();
for col in 0..max_col {
match links.iter().find(|(c, _, _)| *c == col) {
Some((_, url, text)) => match text {
Some(t) => {
self.ws.write_url_with_text(row, col, url.as_str(), t.as_str())?;
}
None => {
self.ws.write_url(row, col, url.as_str())?;
}
},
None => {
if col < len {
let def = columns.get(col as usize);
self.write_value(row, col, &values[col as usize], def)?;
}
}
}
}
self.row_cursor += 1;
self.last_col = self.last_col.max(max_col.saturating_sub(1));
self.col_count = self.col_count.max(max_col);
Ok(())
}
pub fn merge_range(
&mut self,
first_row: u32,
first_col: u16,
last_row: u32,
last_col: u16,
text: impl AsRef<str>,
) -> Result<()> {
let fmt = Format::new().set_align(FormatAlign::Center);
self.merge_range_with_format(
first_row,
first_col,
last_row,
last_col,
text.as_ref(),
&fmt,
)
}
pub fn merge_header(
&mut self,
first_row: u32,
first_col: u16,
last_row: u32,
last_col: u16,
text: impl AsRef<str>,
) -> Result<()> {
let fmt = self.header_format();
self.merge_range_with_format(
first_row,
first_col,
last_row,
last_col,
text.as_ref(),
&fmt,
)
}
fn merge_range_with_format(
&mut self,
first_row: u32,
first_col: u16,
last_row: u32,
last_col: u16,
text: &str,
format: &Format,
) -> Result<()> {
if first_row > last_row || first_col > last_col {
return Err(ExcelError::Other(format!(
"合并区域行列顺序错误:({first_row},{first_col})-({last_row},{last_col})"
)));
}
if first_row == last_row && first_col == last_col {
return Err(ExcelError::Other(
"合并区域至少需要 2 个单元格".to_string(),
));
}
if first_row < self.row_cursor {
return Err(ExcelError::Other(format!(
"常量内存模式只能顺序写:合并起始行 {first_row} 早于当前行游标 {}",
self.row_cursor
)));
}
self.ws
.merge_range(first_row, first_col, last_row, last_col, text, format)?;
self.row_cursor = self.row_cursor.max(last_row.saturating_add(1));
self.last_col = self.last_col.max(last_col);
self.col_count = self.col_count.max(last_col.saturating_add(1));
Ok(())
}
pub fn finish(&mut self) -> Result<()> {
let start = self.header_row.unwrap_or(0);
if self.options.write.auto_filter
&& self.options.write.header_style
&& self.row_cursor > start + 1
&& self.col_count > 0
{
self.ws
.autofilter(start, 0, self.row_cursor - 1, self.last_col)?;
}
Ok(())
}
pub fn worksheet(&mut self) -> &mut Worksheet {
self.ws
}
fn write_value(
&mut self,
row: u32,
col: u16,
value: &CellValue,
def: Option<&ColumnDef>,
) -> Result<()> {
match value {
CellValue::Empty => {}
CellValue::Text(s) => {
if s.is_empty() {
return Ok(());
}
match self.cell_format_key(def, true) {
Some(key) => {
let fmt = self.format_of(&key, def, true);
self.ws.write_string_with_format(row, col, s, &fmt)?;
}
None => {
self.ws.write_string(row, col, s)?;
}
}
}
CellValue::Number(n) => match self.cell_format_key(def, false) {
Some(key) => {
let fmt = self.format_of(&key, def, false);
self.ws.write_number_with_format(row, col, *n, &fmt)?;
}
None => {
self.ws.write_number(row, col, *n)?;
}
},
CellValue::Bool(b) => {
self.ws.write_boolean(row, col, *b)?;
}
CellValue::Date(d) => {
let fmt = self.date_format(def, false);
let dt = ExcelDateTime::from_ymd(
chrono::Datelike::year(d) as u16,
chrono::Datelike::month(d) as u8,
chrono::Datelike::day(d) as u8,
)?;
self.ws.write_datetime_with_format(row, col, &dt, &fmt)?;
}
CellValue::DateTime(raw) => {
let fmt = self.date_format(def, true);
let dt = ExcelDateTime::from_ymd(
chrono::Datelike::year(raw) as u16,
chrono::Datelike::month(raw) as u8,
chrono::Datelike::day(raw) as u8,
)?
.and_hms(
chrono::Timelike::hour(raw) as u16,
chrono::Timelike::minute(raw) as u8,
chrono::Timelike::second(raw) as f64,
)?;
self.ws.write_datetime_with_format(row, col, &dt, &fmt)?;
}
CellValue::Formula(f) => {
self.ws.write_formula(row, col, f.as_str())?;
}
CellValue::Error(e) => {
self.ws.write_string(row, col, e)?;
}
CellValue::Image(img) => {
self.write_image(row, col, img)?;
}
}
Ok(())
}
fn write_image(&mut self, row: u32, col: u16, img: &CellImage) -> Result<()> {
if img.bytes.is_empty() {
return Ok(());
}
let mut image = Image::new_from_buffer(&img.bytes)?;
if let Some(name) = &img.name {
image = image.set_alt_text(name.clone());
}
if self.options.write.cell_image {
self.ws.embed_image(row, col, &image)?;
} else {
self.ws
.insert_image_fit_to_cell_centered(row, col, &image)?;
}
Ok(())
}
fn header_format(&mut self) -> Format {
let key = "header".to_string();
if let Some(f) = self.formats.get(&key) {
return f.clone();
}
let fmt = Format::new()
.set_bold()
.set_align(FormatAlign::Center)
.set_border(FormatBorder::Thin)
.set_background_color(Color::RGB(0xD9_E1_F2))
.set_font_color(Color::RGB(0x1F_23_29));
self.formats.insert(key, fmt.clone());
fmt
}
fn cell_format_key(&self, def: Option<&ColumnDef>, text: bool) -> Option<String> {
let def = def?;
if text && def.text_format {
return Some("text:@".to_string());
}
if !text {
if let Some(f) = &def.format {
return Some(format!("num:{f}"));
}
if def.text_format {
return Some("text:@".to_string());
}
}
None
}
fn format_of(&mut self, key: &str, def: Option<&ColumnDef>, text: bool) -> Format {
if let Some(f) = self.formats.get(key) {
return f.clone();
}
let code = match def.and_then(|d| d.format.clone()) {
Some(code) => code,
None => {
if matches!(def.map(|d| d.kind), Some(ColumnKind::Integer)) {
"0".to_string()
} else if matches!(
def.map(|d| d.kind),
Some(ColumnKind::Decimal) | Some(ColumnKind::Number)
) {
"0.00".to_string()
} else if text {
"@".to_string()
} else {
"General".to_string()
}
}
};
let fmt = Format::new().set_num_format(code);
self.formats.insert(key.to_string(), fmt.clone());
fmt
}
fn date_format(&mut self, def: Option<&ColumnDef>, with_time: bool) -> Format {
let default = if with_time {
self.options.write.datetime_format.clone()
} else {
self.options.write.date_format.clone()
};
let code = def.and_then(|d| d.format.clone()).unwrap_or(default);
let key = format!("date:{code}");
if let Some(f) = self.formats.get(&key) {
return f.clone();
}
let fmt = Format::new().set_num_format(code);
self.formats.insert(key, fmt.clone());
fmt
}
}
pub fn export_single_sheet<T: ExcelRow>(
path: impl AsRef<Path>,
sheet: &SheetOptions,
rows: &[T],
) -> Result<u64> {
let mut writer = ExcelWriter::new();
{
let mut ws = writer.add_sheet(sheet)?;
ws.write_headers()?;
ws.write_models(rows)?;
ws.finish()?;
}
writer.save(path)
}
pub fn default_write_options() -> WriteOptions {
WriteOptions::default()
}