1use crate::error::{Error, Result};
7use crate::utils::cell_ref::{cell_name_to_coordinates, coordinates_to_cell_name};
8use crate::utils::constants::{MAX_COLUMNS, MAX_ROWS};
9use sheetkit_xml::table::{
10 TableAutoFilter, TableColumnXml, TableColumnsXml, TableStyleInfoXml, TableXml,
11};
12use sheetkit_xml::worksheet::{AutoFilter, WorksheetXml};
13
14#[derive(Debug, Clone)]
22pub struct TableConfig {
23 pub name: String,
25 pub display_name: String,
27 pub range: String,
30 pub columns: Vec<TableColumn>,
32 pub show_header_row: bool,
34 pub style_name: Option<String>,
36 pub auto_filter: bool,
38 pub show_first_column: bool,
40 pub show_last_column: bool,
42 pub show_row_stripes: bool,
44 pub show_column_stripes: bool,
46}
47
48impl Default for TableConfig {
49 fn default() -> Self {
50 Self {
51 name: String::new(),
52 display_name: String::new(),
53 range: String::new(),
54 columns: Vec::new(),
55 show_header_row: true,
56 style_name: None,
57 auto_filter: true,
58 show_first_column: false,
59 show_last_column: false,
60 show_row_stripes: true,
61 show_column_stripes: false,
62 }
63 }
64}
65
66#[derive(Debug, Clone)]
68pub struct TableColumn {
69 pub name: String,
71 pub totals_row_function: Option<String>,
74 pub totals_row_label: Option<String>,
77}
78
79#[derive(Debug, Clone, PartialEq)]
81pub struct TableInfo {
82 pub name: String,
84 pub display_name: String,
86 pub range: String,
88 pub show_header_row: bool,
90 pub auto_filter: bool,
92 pub columns: Vec<String>,
94 pub style_name: Option<String>,
96}
97
98pub const MAX_TABLE_NAME_LEN: usize = 255;
100
101pub(crate) fn effective_display_name(config: &TableConfig) -> &str {
104 if config.display_name.is_empty() {
105 &config.name
106 } else {
107 &config.display_name
108 }
109}
110
111fn is_a1_cell_reference(name: &str) -> bool {
114 let letters = name.chars().take_while(char::is_ascii_alphabetic).count();
115 if !(1..=3).contains(&letters) || letters == name.len() {
116 return false;
117 }
118 let (col_part, row_part) = name.split_at(letters);
119 if !row_part.chars().all(|c| c.is_ascii_digit()) {
120 return false;
121 }
122 let col = col_part.chars().fold(0u32, |acc, c| {
123 acc * 26 + (c.to_ascii_uppercase() as u32 - 'A' as u32 + 1)
124 });
125 let row = row_part.parse::<u64>().unwrap_or(u64::MAX);
126 col <= MAX_COLUMNS && (1..=u64::from(MAX_ROWS)).contains(&row)
127}
128
129fn is_r1c1_reference(name: &str) -> bool {
132 let upper = name.to_ascii_uppercase();
133 let digits = |s: &str| s.chars().all(|c| c.is_ascii_digit());
134 if let Some(rest) = upper.strip_prefix('R') {
135 match rest.split_once('C') {
136 Some((row, col)) => digits(row) && digits(col),
137 None => digits(rest),
138 }
139 } else if let Some(rest) = upper.strip_prefix('C') {
140 digits(rest)
141 } else {
142 false
143 }
144}
145
146pub(crate) fn validate_table_name(kind: &str, name: &str) -> Result<()> {
152 let len = name.chars().count();
153 if len == 0 {
154 return Err(Error::InvalidArgument(format!("{kind} cannot be empty")));
155 }
156 if len > MAX_TABLE_NAME_LEN {
157 return Err(Error::InvalidArgument(format!(
158 "{kind} exceeds {MAX_TABLE_NAME_LEN} characters"
159 )));
160 }
161 let mut chars = name.chars();
162 let first = chars.next().unwrap_or_default();
163 if !(first.is_alphabetic() || first == '_' || first == '\\') {
164 return Err(Error::InvalidArgument(format!(
165 "{kind} must start with a letter, underscore, or backslash: {name}"
166 )));
167 }
168 if let Some(bad) = chars.find(|c| !(c.is_alphanumeric() || *c == '.' || *c == '_')) {
169 return Err(Error::InvalidArgument(format!(
170 "{kind} contains invalid character {bad:?}: {name}"
171 )));
172 }
173 if is_a1_cell_reference(name) || is_r1c1_reference(name) {
174 return Err(Error::InvalidArgument(format!(
175 "{kind} must not be a cell reference: {name}"
176 )));
177 }
178 Ok(())
179}
180
181pub(crate) fn build_table_xml(config: &TableConfig, table_id: u32) -> TableXml {
183 let columns: Vec<TableColumnXml> = config
184 .columns
185 .iter()
186 .enumerate()
187 .map(|(i, col)| TableColumnXml {
188 id: (i + 1) as u32,
189 name: col.name.clone(),
190 totals_row_function: col.totals_row_function.clone(),
191 totals_row_label: col.totals_row_label.clone(),
192 ..Default::default()
193 })
194 .collect();
195
196 let totals_row = has_totals_row(config);
197 let auto_filter = if config.auto_filter {
198 Some(TableAutoFilter {
199 reference: if totals_row {
200 range_without_last_row(&config.range).unwrap_or_else(|| config.range.clone())
201 } else {
202 config.range.clone()
203 },
204 ..Default::default()
205 })
206 } else {
207 None
208 };
209
210 let style_info = if config.style_name.is_some()
211 || config.show_first_column
212 || config.show_last_column
213 || config.show_row_stripes
214 || config.show_column_stripes
215 {
216 Some(TableStyleInfoXml {
217 name: config.style_name.clone(),
218 show_first_column: Some(config.show_first_column),
219 show_last_column: Some(config.show_last_column),
220 show_row_stripes: Some(config.show_row_stripes),
221 show_column_stripes: Some(config.show_column_stripes),
222 })
223 } else {
224 None
225 };
226
227 let header_row_count = if !config.show_header_row {
228 Some(0)
229 } else {
230 None
231 };
232
233 TableXml {
234 xmlns: sheetkit_xml::namespaces::SPREADSHEET_ML.to_string(),
235 id: table_id,
236 name: config.name.clone(),
237 display_name: effective_display_name(config).to_string(),
238 reference: config.range.clone(),
239 totals_row_count: totals_row.then_some(1),
240 totals_row_shown: None,
241 header_row_count,
242 auto_filter,
243 table_columns: TableColumnsXml {
244 count: columns.len() as u32,
245 columns,
246 },
247 table_style_info: style_info,
248 ..Default::default()
249 }
250}
251
252pub(crate) fn table_xml_to_info(table_xml: &TableXml) -> TableInfo {
254 let columns = table_xml
255 .table_columns
256 .columns
257 .iter()
258 .map(|c| c.name.clone())
259 .collect();
260 let show_header_row = table_xml.header_row_count != Some(0);
261 let auto_filter = table_xml.auto_filter.is_some();
262 let style_name = table_xml
263 .table_style_info
264 .as_ref()
265 .and_then(|s| s.name.clone());
266
267 TableInfo {
268 name: table_xml.name.clone(),
269 display_name: table_xml.display_name.clone(),
270 range: table_xml.reference.clone(),
271 show_header_row,
272 auto_filter,
273 columns,
274 style_name,
275 }
276}
277
278fn table_range_bounds(range: &str) -> Result<(u32, u32, u32, u32)> {
279 let mut cells = range.split(':');
280 let start = cells.next().unwrap_or_default();
281 let end = cells.next().unwrap_or(start);
282 if cells.next().is_some() {
283 return Err(Error::InvalidArgument(format!(
284 "table range must contain one cell or a single cell range: {range}"
285 )));
286 }
287
288 let (start_col, start_row) = cell_name_to_coordinates(start)?;
289 let (end_col, end_row) = cell_name_to_coordinates(end)?;
290 Ok((
291 start_col.min(end_col),
292 start_row.min(end_row),
293 start_col.max(end_col),
294 start_row.max(end_row),
295 ))
296}
297
298const TOTALS_ROW_FUNCTIONS: [(&str, Option<u32>); 9] = [
301 ("none", None),
302 ("sum", Some(109)),
303 ("min", Some(105)),
304 ("max", Some(104)),
305 ("average", Some(101)),
306 ("count", Some(103)),
307 ("countNums", Some(102)),
308 ("stdDev", Some(107)),
309 ("var", Some(110)),
310];
311
312pub(crate) fn has_totals_row(config: &TableConfig) -> bool {
314 config
315 .columns
316 .iter()
317 .any(|c| c.totals_row_function.is_some() || c.totals_row_label.is_some())
318}
319
320fn range_without_last_row(range: &str) -> Option<String> {
321 let (start_col, start_row, end_col, end_row) = table_range_bounds(range).ok()?;
322 let start = coordinates_to_cell_name(start_col, start_row).ok()?;
323 let end = coordinates_to_cell_name(end_col, end_row.checked_sub(1)?).ok()?;
324 Some(format!("{start}:{end}"))
325}
326
327fn structured_reference_column(name: &str) -> String {
329 let mut escaped = String::with_capacity(name.len());
330 for c in name.chars() {
331 if matches!(c, '\'' | '#' | '[' | ']' | '@') {
332 escaped.push('\'');
333 }
334 escaped.push(c);
335 }
336 escaped
337}
338
339#[derive(Debug, Clone, PartialEq)]
341pub(crate) enum TotalsCell {
342 Label(String),
343 Formula(String),
344}
345
346pub(crate) fn table_totals_cells(config: &TableConfig) -> Result<Vec<(String, TotalsCell)>> {
349 if !has_totals_row(config) {
350 return Ok(Vec::new());
351 }
352 let (start_col, _, _, end_row) = table_range_bounds(&config.range)?;
353 let mut cells = Vec::new();
354 for (offset, column) in config.columns.iter().enumerate() {
355 let cell = coordinates_to_cell_name(start_col + offset as u32, end_row)?;
356 if let Some(label) = &column.totals_row_label {
357 cells.push((cell, TotalsCell::Label(label.clone())));
358 } else if let Some(function) = &column.totals_row_function {
359 let code = TOTALS_ROW_FUNCTIONS
360 .iter()
361 .find(|(name, _)| name == function)
362 .and_then(|(_, code)| *code);
363 if let Some(code) = code {
364 cells.push((
365 cell,
366 TotalsCell::Formula(format!(
367 "SUBTOTAL({code},[{}])",
368 structured_reference_column(&column.name)
369 )),
370 ));
371 }
372 }
373 }
374 Ok(cells)
375}
376
377pub(crate) fn table_header_cells(config: &TableConfig) -> Result<Vec<(String, String)>> {
379 if !config.show_header_row {
380 return Ok(Vec::new());
381 }
382
383 let (start_col, start_row, _, _) = table_range_bounds(&config.range)?;
384 config
385 .columns
386 .iter()
387 .enumerate()
388 .map(|(offset, column)| {
389 Ok((
390 coordinates_to_cell_name(start_col + offset as u32, start_row)?,
391 column.name.clone(),
392 ))
393 })
394 .collect()
395}
396
397pub(crate) fn validate_table_config(config: &TableConfig) -> Result<()> {
399 validate_table_name("table name", &config.name)?;
400 validate_table_name("table display name", effective_display_name(config))?;
401 if config.range.is_empty() {
402 return Err(Error::InvalidArgument("table range cannot be empty".into()));
403 }
404 if config.columns.is_empty() {
405 return Err(Error::InvalidArgument(
406 "table must have at least one column".into(),
407 ));
408 }
409
410 let (start_col, start_row, end_col, end_row) = table_range_bounds(&config.range)?;
411 let range_width = end_col - start_col + 1;
412 if range_width != config.columns.len() as u32 {
413 return Err(Error::InvalidArgument(format!(
414 "table range width ({range_width}) must match column count ({})",
415 config.columns.len()
416 )));
417 }
418 for column in &config.columns {
419 if let Some(function) = &column.totals_row_function {
420 if !TOTALS_ROW_FUNCTIONS
421 .iter()
422 .any(|(name, _)| name == function)
423 {
424 return Err(Error::InvalidArgument(format!(
425 "unsupported totals row function {function:?} for column {}",
426 column.name
427 )));
428 }
429 }
430 }
431 if has_totals_row(config) {
432 let header_rows = u32::from(config.show_header_row);
433 let rows = end_row - start_row + 1;
434 if rows < header_rows + 2 {
435 return Err(Error::InvalidArgument(format!(
436 "table range {} must include at least one data row and the totals row",
437 config.range
438 )));
439 }
440 }
441 Ok(())
442}
443
444pub fn set_auto_filter(ws: &mut WorksheetXml, range: &str) -> Result<()> {
446 ws.auto_filter = Some(AutoFilter {
447 reference: range.to_string(),
448 });
449 Ok(())
450}
451
452pub fn remove_auto_filter(ws: &mut WorksheetXml) {
454 ws.auto_filter = None;
455}
456
457#[cfg(test)]
458mod tests {
459 use super::*;
460
461 #[test]
462 fn test_set_auto_filter() {
463 let mut ws = WorksheetXml::default();
464 set_auto_filter(&mut ws, "A1:D10").unwrap();
465
466 assert!(ws.auto_filter.is_some());
467 assert_eq!(ws.auto_filter.as_ref().unwrap().reference, "A1:D10");
468 }
469
470 #[test]
471 fn test_remove_auto_filter() {
472 let mut ws = WorksheetXml::default();
473 set_auto_filter(&mut ws, "A1:D10").unwrap();
474 remove_auto_filter(&mut ws);
475
476 assert!(ws.auto_filter.is_none());
477 }
478
479 #[test]
480 fn test_auto_filter_xml_roundtrip() {
481 let mut ws = WorksheetXml::default();
482 set_auto_filter(&mut ws, "A1:C100").unwrap();
483
484 let xml = quick_xml::se::to_string(&ws).unwrap();
485 assert!(xml.contains("autoFilter"));
486 assert!(xml.contains("A1:C100"));
487
488 let parsed: WorksheetXml = quick_xml::de::from_str(&xml).unwrap();
489 assert!(parsed.auto_filter.is_some());
490 assert_eq!(parsed.auto_filter.as_ref().unwrap().reference, "A1:C100");
491 }
492
493 #[test]
494 fn test_remove_auto_filter_when_none() {
495 let mut ws = WorksheetXml::default();
496 remove_auto_filter(&mut ws);
497 assert!(ws.auto_filter.is_none());
498 }
499
500 #[test]
501 fn test_overwrite_auto_filter() {
502 let mut ws = WorksheetXml::default();
503 set_auto_filter(&mut ws, "A1:B10").unwrap();
504 set_auto_filter(&mut ws, "A1:D20").unwrap();
505
506 assert_eq!(ws.auto_filter.as_ref().unwrap().reference, "A1:D20");
507 }
508
509 #[test]
510 fn test_table_config_creation() {
511 let config = TableConfig {
512 name: "Table1".to_string(),
513 display_name: "Table1".to_string(),
514 range: "A1:D10".to_string(),
515 columns: vec![
516 TableColumn {
517 name: "Name".to_string(),
518 totals_row_function: None,
519 totals_row_label: None,
520 },
521 TableColumn {
522 name: "Age".to_string(),
523 totals_row_function: None,
524 totals_row_label: None,
525 },
526 TableColumn {
527 name: "City".to_string(),
528 totals_row_function: None,
529 totals_row_label: None,
530 },
531 TableColumn {
532 name: "Score".to_string(),
533 totals_row_function: None,
534 totals_row_label: None,
535 },
536 ],
537 show_header_row: true,
538 style_name: Some("TableStyleMedium2".to_string()),
539 auto_filter: true,
540 ..TableConfig::default()
541 };
542
543 assert_eq!(config.name, "Table1");
544 assert_eq!(config.columns.len(), 4);
545 assert!(config.auto_filter);
546 }
547
548 #[test]
549 fn test_build_table_xml() {
550 let config = TableConfig {
551 name: "Sales".to_string(),
552 display_name: "Sales".to_string(),
553 range: "A1:C5".to_string(),
554 columns: vec![
555 TableColumn {
556 name: "Product".to_string(),
557 totals_row_function: None,
558 totals_row_label: None,
559 },
560 TableColumn {
561 name: "Quantity".to_string(),
562 totals_row_function: None,
563 totals_row_label: None,
564 },
565 TableColumn {
566 name: "Price".to_string(),
567 totals_row_function: None,
568 totals_row_label: None,
569 },
570 ],
571 show_header_row: true,
572 style_name: Some("TableStyleMedium2".to_string()),
573 auto_filter: true,
574 show_row_stripes: true,
575 ..TableConfig::default()
576 };
577
578 let table_xml = build_table_xml(&config, 1);
579 assert_eq!(table_xml.id, 1);
580 assert_eq!(table_xml.name, "Sales");
581 assert_eq!(table_xml.reference, "A1:C5");
582 assert_eq!(table_xml.table_columns.count, 3);
583 assert!(table_xml.auto_filter.is_some());
584 assert!(table_xml.table_style_info.is_some());
585 assert!(table_xml.header_row_count.is_none());
586 }
587
588 #[test]
589 fn test_build_table_xml_no_header() {
590 let config = TableConfig {
591 name: "TestTable1".to_string(),
592 display_name: "TestTable1".to_string(),
593 range: "A1:B5".to_string(),
594 columns: vec![TableColumn {
595 name: "Col1".to_string(),
596 totals_row_function: None,
597 totals_row_label: None,
598 }],
599 show_header_row: false,
600 ..TableConfig::default()
601 };
602
603 let table_xml = build_table_xml(&config, 2);
604 assert_eq!(table_xml.header_row_count, Some(0));
605 }
606
607 #[test]
608 fn test_table_xml_to_info() {
609 let table_xml = TableXml {
610 xmlns: sheetkit_xml::namespaces::SPREADSHEET_ML.to_string(),
611 id: 1,
612 name: "Inventory".to_string(),
613 display_name: "Inventory".to_string(),
614 reference: "A1:D20".to_string(),
615 totals_row_count: None,
616 totals_row_shown: None,
617 header_row_count: None,
618 auto_filter: Some(TableAutoFilter {
619 reference: "A1:D20".to_string(),
620 ..Default::default()
621 }),
622 table_columns: TableColumnsXml {
623 count: 4,
624 columns: vec![
625 TableColumnXml {
626 id: 1,
627 name: "Item".to_string(),
628 totals_row_function: None,
629 totals_row_label: None,
630 ..Default::default()
631 },
632 TableColumnXml {
633 id: 2,
634 name: "Stock".to_string(),
635 totals_row_function: None,
636 totals_row_label: None,
637 ..Default::default()
638 },
639 TableColumnXml {
640 id: 3,
641 name: "Price".to_string(),
642 totals_row_function: None,
643 totals_row_label: None,
644 ..Default::default()
645 },
646 TableColumnXml {
647 id: 4,
648 name: "Supplier".to_string(),
649 totals_row_function: None,
650 totals_row_label: None,
651 ..Default::default()
652 },
653 ],
654 },
655 table_style_info: Some(TableStyleInfoXml {
656 name: Some("TableStyleLight1".to_string()),
657 show_first_column: Some(false),
658 show_last_column: Some(false),
659 show_row_stripes: Some(true),
660 show_column_stripes: Some(false),
661 }),
662 ..Default::default()
663 };
664
665 let info = table_xml_to_info(&table_xml);
666 assert_eq!(info.name, "Inventory");
667 assert_eq!(info.display_name, "Inventory");
668 assert_eq!(info.range, "A1:D20");
669 assert!(info.show_header_row);
670 assert!(info.auto_filter);
671 assert_eq!(info.columns, vec!["Item", "Stock", "Price", "Supplier"]);
672 assert_eq!(info.style_name, Some("TableStyleLight1".to_string()));
673 }
674
675 #[test]
676 fn test_validate_table_config_empty_name() {
677 let config = TableConfig {
678 name: String::new(),
679 range: "A1:B5".to_string(),
680 columns: vec![TableColumn {
681 name: "Col".to_string(),
682 totals_row_function: None,
683 totals_row_label: None,
684 }],
685 ..TableConfig::default()
686 };
687 assert!(validate_table_config(&config).is_err());
688 }
689
690 #[test]
691 fn test_validate_table_config_empty_range() {
692 let config = TableConfig {
693 name: "TestTable1".to_string(),
694 range: String::new(),
695 columns: vec![TableColumn {
696 name: "Col".to_string(),
697 totals_row_function: None,
698 totals_row_label: None,
699 }],
700 ..TableConfig::default()
701 };
702 assert!(validate_table_config(&config).is_err());
703 }
704
705 #[test]
706 fn test_validate_table_config_no_columns() {
707 let config = TableConfig {
708 name: "TestTable1".to_string(),
709 range: "A1:B5".to_string(),
710 columns: vec![],
711 ..TableConfig::default()
712 };
713 assert!(validate_table_config(&config).is_err());
714 }
715
716 #[test]
717 fn test_validate_table_config_valid() {
718 let config = TableConfig {
719 name: "TestTable1".to_string(),
720 display_name: "TestTable1".to_string(),
721 range: "A1:A5".to_string(),
722 columns: vec![TableColumn {
723 name: "Col".to_string(),
724 totals_row_function: None,
725 totals_row_label: None,
726 }],
727 ..TableConfig::default()
728 };
729 assert!(validate_table_config(&config).is_ok());
730 }
731
732 #[test]
733 fn test_validate_table_config_rejects_range_width_mismatch() {
734 let config = TableConfig {
735 name: "TestTable1".to_string(),
736 display_name: "TestTable1".to_string(),
737 range: "A1:B5".to_string(),
738 columns: vec![TableColumn {
739 name: "Only column".to_string(),
740 totals_row_function: None,
741 totals_row_label: None,
742 }],
743 ..TableConfig::default()
744 };
745
746 assert!(matches!(
747 validate_table_config(&config),
748 Err(Error::InvalidArgument(_))
749 ));
750 }
751
752 #[test]
753 fn test_table_config_default() {
754 let config = TableConfig::default();
755 assert!(config.show_header_row);
756 assert!(config.auto_filter);
757 assert!(config.show_row_stripes);
758 assert!(!config.show_first_column);
759 assert!(!config.show_last_column);
760 assert!(!config.show_column_stripes);
761 }
762}