Skip to main content

sheetkit_core/
pivot.rs

1//! Pivot table configuration and builder.
2
3use crate::error::{Error, Result};
4
5/// Aggregate function for pivot table data fields.
6#[derive(Debug, Clone, PartialEq)]
7pub enum AggregateFunction {
8    Sum,
9    Count,
10    Average,
11    Max,
12    Min,
13    Product,
14    CountNums,
15    StdDev,
16    StdDevP,
17    Var,
18    VarP,
19}
20
21impl AggregateFunction {
22    /// Returns the XML string representation of this aggregate function.
23    pub fn to_xml_str(&self) -> &str {
24        match self {
25            Self::Sum => "sum",
26            Self::Count => "count",
27            Self::Average => "average",
28            Self::Max => "max",
29            Self::Min => "min",
30            Self::Product => "product",
31            Self::CountNums => "countNums",
32            Self::StdDev => "stdDev",
33            Self::StdDevP => "stdDevp",
34            Self::Var => "var",
35            Self::VarP => "varp",
36        }
37    }
38
39    /// Parses an aggregate function from its XML string representation.
40    pub fn from_xml_str(s: &str) -> Option<Self> {
41        match s {
42            "sum" => Some(Self::Sum),
43            "count" => Some(Self::Count),
44            "average" => Some(Self::Average),
45            "max" => Some(Self::Max),
46            "min" => Some(Self::Min),
47            "product" => Some(Self::Product),
48            "countNums" => Some(Self::CountNums),
49            "stdDev" => Some(Self::StdDev),
50            "stdDevp" => Some(Self::StdDevP),
51            "var" => Some(Self::Var),
52            "varp" => Some(Self::VarP),
53            _ => None,
54        }
55    }
56}
57
58/// Configuration for adding a pivot table.
59#[derive(Debug, Clone)]
60pub struct PivotTableConfig {
61    /// Name of the pivot table.
62    pub name: String,
63    /// Source data sheet name.
64    pub source_sheet: String,
65    /// Source data range (e.g., "A1:D10").
66    pub source_range: String,
67    /// Target sheet name where the pivot table will be placed.
68    pub target_sheet: String,
69    /// Target cell (top-left corner of pivot table, e.g., "A1").
70    pub target_cell: String,
71    /// Row fields (column names from source data).
72    pub rows: Vec<PivotField>,
73    /// Column fields.
74    pub columns: Vec<PivotField>,
75    /// Data/value fields.
76    pub data: Vec<PivotDataField>,
77}
78
79/// A field used as row or column in the pivot table.
80#[derive(Debug, Clone)]
81pub struct PivotField {
82    /// Column name from the source data header row.
83    pub name: String,
84}
85
86/// A data/value field in the pivot table.
87#[derive(Debug, Clone)]
88pub struct PivotDataField {
89    /// Column name from the source data header row.
90    pub name: String,
91    /// Aggregate function to apply.
92    pub function: AggregateFunction,
93    /// Optional custom display name.
94    pub display_name: Option<String>,
95}
96
97/// Information about an existing pivot table.
98#[derive(Debug, Clone)]
99pub struct PivotTableInfo {
100    pub name: String,
101    pub source_sheet: String,
102    pub source_range: String,
103    pub target_sheet: String,
104    pub location: String,
105}
106
107/// Builds the pivot table definition XML from config and field names.
108pub fn build_pivot_table_xml(
109    config: &PivotTableConfig,
110    cache_id: u32,
111    field_names: &[String],
112) -> Result<sheetkit_xml::pivot_table::PivotTableDefinition> {
113    build_pivot_table_xml_with_items(config, cache_id, field_names, &vec![0; field_names.len()])
114}
115
116pub(crate) fn build_pivot_table_xml_with_items(
117    config: &PivotTableConfig,
118    cache_id: u32,
119    field_names: &[String],
120    item_counts: &[u32],
121) -> Result<sheetkit_xml::pivot_table::PivotTableDefinition> {
122    use sheetkit_xml::pivot_table::*;
123
124    let ns = sheetkit_xml::namespaces::SPREADSHEET_ML;
125
126    let find_field_index = |name: &str| -> Result<usize> {
127        field_names.iter().position(|n| n == name).ok_or_else(|| {
128            Error::Internal(format!("pivot field '{}' not found in source data", name))
129        })
130    };
131
132    let mut pivot_field_defs = Vec::new();
133    for (field_index, field_name) in field_names.iter().enumerate() {
134        let is_row = config.rows.iter().any(|r| r.name == *field_name);
135        let is_col = config.columns.iter().any(|c| c.name == *field_name);
136        let is_data = config.data.iter().any(|d| d.name == *field_name);
137
138        let axis = if is_row {
139            Some("axisRow".to_string())
140        } else if is_col {
141            Some("axisCol".to_string())
142        } else {
143            None
144        };
145
146        pivot_field_defs.push(PivotFieldDef {
147            axis,
148            data_field: if is_data { Some(true) } else { None },
149            show_all: Some(false),
150            items: if is_row || is_col {
151                let item_count = item_counts.get(field_index).copied().unwrap_or(0);
152                let mut items = (0..item_count)
153                    .map(|index| FieldItem {
154                        item_type: None,
155                        index: Some(index),
156                    })
157                    .collect::<Vec<_>>();
158                items.push(FieldItem {
159                    item_type: Some("default".to_string()),
160                    index: None,
161                });
162                Some(FieldItems {
163                    count: Some(items.len() as u32),
164                    items,
165                })
166            } else {
167                None
168            },
169        });
170    }
171
172    let row_fields = if config.rows.is_empty() {
173        None
174    } else {
175        let fields: Result<Vec<FieldRef>> = config
176            .rows
177            .iter()
178            .map(|r| find_field_index(&r.name).map(|i| FieldRef { index: i as i32 }))
179            .collect();
180        Some(FieldList {
181            count: Some(config.rows.len() as u32),
182            fields: fields?,
183        })
184    };
185
186    let col_fields = if config.columns.is_empty() {
187        None
188    } else {
189        let fields: Result<Vec<FieldRef>> = config
190            .columns
191            .iter()
192            .map(|c| find_field_index(&c.name).map(|i| FieldRef { index: i as i32 }))
193            .collect();
194        Some(FieldList {
195            count: Some(config.columns.len() as u32),
196            fields: fields?,
197        })
198    };
199
200    let data_fields = if config.data.is_empty() {
201        None
202    } else {
203        let fields: Result<Vec<DataFieldDef>> = config
204            .data
205            .iter()
206            .map(|d| {
207                let idx = find_field_index(&d.name)?;
208                Ok(DataFieldDef {
209                    name: d.display_name.clone().or_else(|| {
210                        Some(format!(
211                            "{} of {}",
212                            capitalize_first(d.function.to_xml_str()),
213                            d.name
214                        ))
215                    }),
216                    field_index: idx as u32,
217                    subtotal: Some(d.function.to_xml_str().to_string()),
218                    base_field: Some(0),
219                    base_item: Some(0),
220                })
221            })
222            .collect();
223        Some(DataFields {
224            count: Some(config.data.len() as u32),
225            fields: fields?,
226        })
227    };
228
229    let (target_col, target_row) =
230        crate::utils::cell_ref::cell_name_to_coordinates(&config.target_cell)
231            .map_err(|_| Error::InvalidCellReference(config.target_cell.clone()))?;
232    let source_rows = config
233        .source_range
234        .split_once(':')
235        .and_then(|(start, end)| {
236            let (_, start_row) = crate::utils::cell_ref::cell_name_to_coordinates(start).ok()?;
237            let (_, end_row) = crate::utils::cell_ref::cell_name_to_coordinates(end).ok()?;
238            end_row.checked_sub(start_row).map(|rows| rows + 1)
239        })
240        .unwrap_or(1);
241    let output_width = (config.rows.len() + config.columns.len() + config.data.len()).max(2) as u32;
242    let output_height = source_rows.saturating_add(1).max(2);
243    let end_cell = crate::utils::cell_ref::coordinates_to_cell_name(
244        target_col.saturating_add(output_width - 1),
245        target_row.saturating_add(output_height - 1),
246    )?;
247
248    Ok(PivotTableDefinition {
249        xmlns: ns.to_string(),
250        name: config.name.clone(),
251        cache_id,
252        data_on_rows: Some(false),
253        apply_number_formats: Some(false),
254        apply_border_formats: Some(false),
255        apply_font_formats: Some(false),
256        apply_pattern_formats: Some(false),
257        apply_alignment_formats: Some(false),
258        apply_width_height_formats: Some(true),
259        location: PivotLocation {
260            reference: format!("{}:{}", config.target_cell.to_ascii_uppercase(), end_cell),
261            first_header_row: 1,
262            first_data_row: 1,
263            first_data_col: 1,
264        },
265        pivot_fields: PivotFields {
266            count: Some(field_names.len() as u32),
267            fields: pivot_field_defs,
268        },
269        row_fields,
270        col_fields,
271        data_fields,
272    })
273}
274
275/// Builds the pivot cache definition XML.
276pub fn build_pivot_cache_definition(
277    source_sheet: &str,
278    source_range: &str,
279    field_names: &[String],
280) -> sheetkit_xml::pivot_cache::PivotCacheDefinition {
281    use sheetkit_xml::pivot_cache::*;
282
283    let cache_fields = CacheFields {
284        count: Some(field_names.len() as u32),
285        fields: field_names
286            .iter()
287            .map(|name| CacheField {
288                name: name.clone(),
289                num_fmt_id: Some(0),
290                shared_items: Some(SharedItems {
291                    contains_semi_mixed_types: None,
292                    contains_string: None,
293                    contains_number: None,
294                    contains_blank: None,
295                    count: Some(0),
296                    string_items: vec![],
297                    number_items: vec![],
298                }),
299            })
300            .collect(),
301    };
302
303    PivotCacheDefinition {
304        xmlns: sheetkit_xml::namespaces::SPREADSHEET_ML.to_string(),
305        xmlns_r: sheetkit_xml::namespaces::RELATIONSHIPS.to_string(),
306        r_id: None,
307        record_count: Some(0),
308        refresh_on_load: Some(true),
309        cache_source: CacheSource {
310            source_type: "worksheet".to_string(),
311            worksheet_source: Some(WorksheetSource {
312                reference: source_range.to_string(),
313                sheet: source_sheet.to_string(),
314            }),
315        },
316        cache_fields,
317    }
318}
319
320#[derive(Clone)]
321enum CacheValue {
322    String(String, bool),
323    Number(f64),
324}
325
326fn cache_value(value: &crate::cell::CellValue) -> CacheValue {
327    use crate::cell::CellValue;
328
329    match value {
330        CellValue::Empty => CacheValue::String(String::new(), true),
331        CellValue::Bool(value) => {
332            CacheValue::String(if *value { "TRUE" } else { "FALSE" }.to_string(), false)
333        }
334        CellValue::Number(value) | CellValue::Date(value) if value.is_finite() => {
335            CacheValue::Number(*value)
336        }
337        CellValue::String(value) => CacheValue::String(value.clone(), false),
338        CellValue::RichString(runs) => {
339            CacheValue::String(crate::rich_text::rich_text_to_plain(runs), false)
340        }
341        CellValue::Formula {
342            result: Some(result),
343            ..
344        } => cache_value(result),
345        other => CacheValue::String(other.to_string(), false),
346    }
347}
348
349pub(crate) fn build_pivot_cache_parts(
350    source_sheet: &str,
351    source_range: &str,
352    field_names: &[String],
353    rows: &[Vec<crate::cell::CellValue>],
354    records_r_id: String,
355) -> (
356    sheetkit_xml::pivot_cache::PivotCacheDefinition,
357    sheetkit_xml::pivot_cache::PivotCacheRecords,
358    Vec<u32>,
359) {
360    use sheetkit_xml::pivot_cache::*;
361
362    let values = rows
363        .iter()
364        .map(|row| row.iter().map(cache_value).collect::<Vec<_>>())
365        .collect::<Vec<_>>();
366    let mut fields = Vec::with_capacity(field_names.len());
367    let mut item_counts = Vec::with_capacity(field_names.len());
368    let mut field_indexes = Vec::with_capacity(field_names.len());
369    let mut numeric_columns = Vec::with_capacity(field_names.len());
370
371    for (column, name) in field_names.iter().enumerate() {
372        let mut strings = Vec::<String>::new();
373        let mut numbers = Vec::<f64>::new();
374        let mut string_indexes = std::collections::HashMap::<&str, u32>::new();
375        let mut number_indexes = std::collections::HashMap::<u64, u32>::new();
376        let mut contains_blank = false;
377        for row in &values {
378            match &row[column] {
379                CacheValue::String(value, blank) => {
380                    contains_blank |= *blank;
381                    if let std::collections::hash_map::Entry::Vacant(entry) =
382                        string_indexes.entry(value.as_str())
383                    {
384                        let index = strings.len() as u32;
385                        entry.insert(index);
386                        strings.push(value.clone());
387                    }
388                }
389                CacheValue::Number(value) => {
390                    let key = value.to_bits();
391                    if let std::collections::hash_map::Entry::Vacant(entry) =
392                        number_indexes.entry(key)
393                    {
394                        let index = numbers.len() as u32;
395                        entry.insert(index);
396                        numbers.push(*value);
397                    }
398                }
399            }
400        }
401
402        let indexes = values
403            .iter()
404            .map(|row| match &row[column] {
405                CacheValue::String(value, _) => {
406                    string_indexes.get(value.as_str()).copied().unwrap_or(0)
407                }
408                CacheValue::Number(value) => {
409                    strings.len() as u32
410                        + number_indexes.get(&value.to_bits()).copied().unwrap_or(0)
411                }
412            })
413            .collect::<Vec<_>>();
414        let count = strings.len() + numbers.len();
415        numeric_columns.push(strings.is_empty() && !numbers.is_empty());
416        item_counts.push(count as u32);
417        field_indexes.push(indexes);
418        fields.push(CacheField {
419            name: name.clone(),
420            num_fmt_id: Some(0),
421            shared_items: Some(SharedItems {
422                contains_semi_mixed_types: Some(!strings.is_empty() && !numbers.is_empty()),
423                contains_string: Some(!strings.is_empty()),
424                contains_number: Some(!numbers.is_empty()),
425                contains_blank: Some(contains_blank),
426                count: Some(count as u32),
427                string_items: strings
428                    .into_iter()
429                    .map(|value| StringItem { value })
430                    .collect(),
431                number_items: numbers
432                    .into_iter()
433                    .map(|value| NumberItem { value })
434                    .collect(),
435            }),
436        });
437    }
438
439    let records = (0..values.len())
440        .map(|row| {
441            let mut index_fields = Vec::new();
442            let mut number_fields = Vec::new();
443            for (column, value) in values[row].iter().enumerate() {
444                if numeric_columns[column] {
445                    let CacheValue::Number(value) = value else {
446                        unreachable!("numeric pivot cache columns contain only numbers")
447                    };
448                    number_fields.push(NumberField { v: *value });
449                } else {
450                    index_fields.push(IndexField {
451                        v: field_indexes[column][row],
452                    });
453                }
454            }
455            CacheRecord {
456                index_fields,
457                number_fields,
458                string_fields: vec![],
459                bool_fields: vec![],
460            }
461        })
462        .collect::<Vec<_>>();
463    let record_count = records.len() as u32;
464    let definition = PivotCacheDefinition {
465        xmlns: sheetkit_xml::namespaces::SPREADSHEET_ML.to_string(),
466        xmlns_r: sheetkit_xml::namespaces::RELATIONSHIPS.to_string(),
467        r_id: Some(records_r_id),
468        record_count: Some(record_count),
469        refresh_on_load: Some(true),
470        cache_source: CacheSource {
471            source_type: "worksheet".to_string(),
472            worksheet_source: Some(WorksheetSource {
473                reference: source_range.to_string(),
474                sheet: source_sheet.to_string(),
475            }),
476        },
477        cache_fields: CacheFields {
478            count: Some(fields.len() as u32),
479            fields,
480        },
481    };
482    let cache_records = PivotCacheRecords {
483        xmlns: sheetkit_xml::namespaces::SPREADSHEET_ML.to_string(),
484        xmlns_r: sheetkit_xml::namespaces::RELATIONSHIPS.to_string(),
485        count: Some(record_count),
486        records,
487    };
488
489    (definition, cache_records, item_counts)
490}
491
492fn capitalize_first(s: &str) -> String {
493    let mut c = s.chars();
494    match c.next() {
495        None => String::new(),
496        Some(f) => f.to_uppercase().collect::<String>() + c.as_str(),
497    }
498}
499
500#[cfg(test)]
501mod tests {
502    use super::*;
503
504    #[test]
505    fn test_aggregate_function_to_xml_str() {
506        assert_eq!(AggregateFunction::Sum.to_xml_str(), "sum");
507        assert_eq!(AggregateFunction::Count.to_xml_str(), "count");
508        assert_eq!(AggregateFunction::Average.to_xml_str(), "average");
509        assert_eq!(AggregateFunction::Max.to_xml_str(), "max");
510        assert_eq!(AggregateFunction::Min.to_xml_str(), "min");
511        assert_eq!(AggregateFunction::Product.to_xml_str(), "product");
512        assert_eq!(AggregateFunction::CountNums.to_xml_str(), "countNums");
513        assert_eq!(AggregateFunction::StdDev.to_xml_str(), "stdDev");
514        assert_eq!(AggregateFunction::StdDevP.to_xml_str(), "stdDevp");
515        assert_eq!(AggregateFunction::Var.to_xml_str(), "var");
516        assert_eq!(AggregateFunction::VarP.to_xml_str(), "varp");
517    }
518
519    #[test]
520    fn test_aggregate_function_from_xml_str() {
521        assert_eq!(
522            AggregateFunction::from_xml_str("sum"),
523            Some(AggregateFunction::Sum)
524        );
525        assert_eq!(
526            AggregateFunction::from_xml_str("count"),
527            Some(AggregateFunction::Count)
528        );
529        assert_eq!(
530            AggregateFunction::from_xml_str("average"),
531            Some(AggregateFunction::Average)
532        );
533        assert_eq!(
534            AggregateFunction::from_xml_str("max"),
535            Some(AggregateFunction::Max)
536        );
537        assert_eq!(
538            AggregateFunction::from_xml_str("min"),
539            Some(AggregateFunction::Min)
540        );
541        assert_eq!(
542            AggregateFunction::from_xml_str("product"),
543            Some(AggregateFunction::Product)
544        );
545        assert_eq!(
546            AggregateFunction::from_xml_str("countNums"),
547            Some(AggregateFunction::CountNums)
548        );
549        assert_eq!(
550            AggregateFunction::from_xml_str("stdDev"),
551            Some(AggregateFunction::StdDev)
552        );
553        assert_eq!(
554            AggregateFunction::from_xml_str("stdDevp"),
555            Some(AggregateFunction::StdDevP)
556        );
557        assert_eq!(
558            AggregateFunction::from_xml_str("var"),
559            Some(AggregateFunction::Var)
560        );
561        assert_eq!(
562            AggregateFunction::from_xml_str("varp"),
563            Some(AggregateFunction::VarP)
564        );
565    }
566
567    #[test]
568    fn test_aggregate_function_from_xml_str_unknown() {
569        assert_eq!(AggregateFunction::from_xml_str("unknown"), None);
570        assert_eq!(AggregateFunction::from_xml_str(""), None);
571        assert_eq!(AggregateFunction::from_xml_str("SUM"), None);
572    }
573
574    #[test]
575    fn test_aggregate_function_roundtrip() {
576        let functions = vec![
577            AggregateFunction::Sum,
578            AggregateFunction::Count,
579            AggregateFunction::Average,
580            AggregateFunction::Max,
581            AggregateFunction::Min,
582            AggregateFunction::Product,
583            AggregateFunction::CountNums,
584            AggregateFunction::StdDev,
585            AggregateFunction::StdDevP,
586            AggregateFunction::Var,
587            AggregateFunction::VarP,
588        ];
589        for func in functions {
590            let xml_str = func.to_xml_str();
591            let parsed = AggregateFunction::from_xml_str(xml_str).unwrap();
592            assert_eq!(func, parsed);
593        }
594    }
595
596    #[test]
597    fn test_capitalize_first() {
598        assert_eq!(capitalize_first("sum"), "Sum");
599        assert_eq!(capitalize_first("count"), "Count");
600        assert_eq!(capitalize_first("average"), "Average");
601        assert_eq!(capitalize_first(""), "");
602        assert_eq!(capitalize_first("a"), "A");
603    }
604
605    #[test]
606    fn test_build_pivot_table_xml_basic() {
607        let config = PivotTableConfig {
608            name: "PivotTable1".to_string(),
609            source_sheet: "Data".to_string(),
610            source_range: "A1:C5".to_string(),
611            target_sheet: "Pivot".to_string(),
612            target_cell: "A1".to_string(),
613            rows: vec![PivotField {
614                name: "Region".to_string(),
615            }],
616            columns: vec![],
617            data: vec![PivotDataField {
618                name: "Sales".to_string(),
619                function: AggregateFunction::Sum,
620                display_name: None,
621            }],
622        };
623        let field_names = vec![
624            "Region".to_string(),
625            "Product".to_string(),
626            "Sales".to_string(),
627        ];
628
629        let def = build_pivot_table_xml(&config, 0, &field_names).unwrap();
630        assert_eq!(def.name, "PivotTable1");
631        assert_eq!(def.cache_id, 0);
632        assert_eq!(def.pivot_fields.count, Some(3));
633        assert_eq!(def.pivot_fields.fields.len(), 3);
634
635        // Region is axisRow
636        assert_eq!(def.pivot_fields.fields[0].axis, Some("axisRow".to_string()));
637        assert_eq!(def.pivot_fields.fields[0].data_field, None);
638
639        // Product has no axis
640        assert_eq!(def.pivot_fields.fields[1].axis, None);
641
642        // Sales is data field
643        assert_eq!(def.pivot_fields.fields[2].axis, None);
644        assert_eq!(def.pivot_fields.fields[2].data_field, Some(true));
645
646        // Row fields
647        let row_fields = def.row_fields.unwrap();
648        assert_eq!(row_fields.count, Some(1));
649        assert_eq!(row_fields.fields[0].index, 0);
650
651        // No col fields
652        assert!(def.col_fields.is_none());
653
654        // Data fields
655        let data_fields = def.data_fields.unwrap();
656        assert_eq!(data_fields.count, Some(1));
657        assert_eq!(data_fields.fields[0].field_index, 2);
658        assert_eq!(data_fields.fields[0].subtotal, Some("sum".to_string()));
659        assert_eq!(data_fields.fields[0].name, Some("Sum of Sales".to_string()));
660    }
661
662    #[test]
663    fn test_build_pivot_table_xml_with_columns() {
664        let config = PivotTableConfig {
665            name: "SalesReport".to_string(),
666            source_sheet: "Data".to_string(),
667            source_range: "A1:D10".to_string(),
668            target_sheet: "Report".to_string(),
669            target_cell: "A1".to_string(),
670            rows: vec![PivotField {
671                name: "Region".to_string(),
672            }],
673            columns: vec![PivotField {
674                name: "Quarter".to_string(),
675            }],
676            data: vec![PivotDataField {
677                name: "Revenue".to_string(),
678                function: AggregateFunction::Average,
679                display_name: Some("Avg Revenue".to_string()),
680            }],
681        };
682        let field_names = vec![
683            "Region".to_string(),
684            "Quarter".to_string(),
685            "Revenue".to_string(),
686        ];
687
688        let def = build_pivot_table_xml(&config, 1, &field_names).unwrap();
689        assert_eq!(def.cache_id, 1);
690
691        // Region = axisRow, Quarter = axisCol
692        assert_eq!(def.pivot_fields.fields[0].axis, Some("axisRow".to_string()));
693        assert_eq!(def.pivot_fields.fields[1].axis, Some("axisCol".to_string()));
694
695        let col_fields = def.col_fields.unwrap();
696        assert_eq!(col_fields.count, Some(1));
697        assert_eq!(col_fields.fields[0].index, 1);
698
699        let data_fields = def.data_fields.unwrap();
700        assert_eq!(data_fields.fields[0].name, Some("Avg Revenue".to_string()));
701        assert_eq!(data_fields.fields[0].subtotal, Some("average".to_string()));
702    }
703
704    #[test]
705    fn test_build_pivot_table_xml_unknown_field() {
706        let config = PivotTableConfig {
707            name: "Bad".to_string(),
708            source_sheet: "Data".to_string(),
709            source_range: "A1:B2".to_string(),
710            target_sheet: "Pivot".to_string(),
711            target_cell: "A1".to_string(),
712            rows: vec![PivotField {
713                name: "NonExistent".to_string(),
714            }],
715            columns: vec![],
716            data: vec![],
717        };
718        let field_names = vec!["Actual".to_string()];
719
720        let result = build_pivot_table_xml(&config, 0, &field_names);
721        assert!(result.is_err());
722        let err = result.unwrap_err().to_string();
723        assert!(err.contains("NonExistent"));
724    }
725
726    #[test]
727    fn test_build_pivot_table_xml_no_rows_or_cols() {
728        let config = PivotTableConfig {
729            name: "DataOnly".to_string(),
730            source_sheet: "Sheet1".to_string(),
731            source_range: "A1:B5".to_string(),
732            target_sheet: "Pivot".to_string(),
733            target_cell: "A1".to_string(),
734            rows: vec![],
735            columns: vec![],
736            data: vec![PivotDataField {
737                name: "Amount".to_string(),
738                function: AggregateFunction::Count,
739                display_name: None,
740            }],
741        };
742        let field_names = vec!["Amount".to_string()];
743
744        let def = build_pivot_table_xml(&config, 0, &field_names).unwrap();
745        assert!(def.row_fields.is_none());
746        assert!(def.col_fields.is_none());
747        assert!(def.data_fields.is_some());
748    }
749
750    #[test]
751    fn test_build_pivot_cache_definition() {
752        let field_names = vec![
753            "Name".to_string(),
754            "Region".to_string(),
755            "Sales".to_string(),
756        ];
757        let def = build_pivot_cache_definition("Sheet1", "A1:C10", &field_names);
758
759        assert_eq!(def.xmlns, sheetkit_xml::namespaces::SPREADSHEET_ML);
760        assert_eq!(def.cache_source.source_type, "worksheet");
761        let ws = def.cache_source.worksheet_source.unwrap();
762        assert_eq!(ws.sheet, "Sheet1");
763        assert_eq!(ws.reference, "A1:C10");
764
765        assert_eq!(def.cache_fields.count, Some(3));
766        assert_eq!(def.cache_fields.fields.len(), 3);
767        assert_eq!(def.cache_fields.fields[0].name, "Name");
768        assert_eq!(def.cache_fields.fields[1].name, "Region");
769        assert_eq!(def.cache_fields.fields[2].name, "Sales");
770
771        // Each field should have empty shared items
772        for field in &def.cache_fields.fields {
773            assert!(field.shared_items.is_some());
774            let items = field.shared_items.as_ref().unwrap();
775            assert_eq!(items.count, Some(0));
776        }
777
778        assert_eq!(def.record_count, Some(0));
779        assert!(def.r_id.is_none());
780    }
781
782    #[test]
783    fn test_build_pivot_cache_definition_empty_fields() {
784        let field_names: Vec<String> = vec![];
785        let def = build_pivot_cache_definition("Sheet1", "A1:A1", &field_names);
786        assert_eq!(def.cache_fields.count, Some(0));
787        assert!(def.cache_fields.fields.is_empty());
788    }
789
790    #[test]
791    fn test_build_pivot_cache_parts_indexes_mixed_type_columns() {
792        let field_names = vec!["Category".to_string(), "Mixed".to_string()];
793        let rows = vec![
794            vec![
795                crate::cell::CellValue::String("A".to_string()),
796                crate::cell::CellValue::Number(1.0),
797            ],
798            vec![
799                crate::cell::CellValue::String("B".to_string()),
800                crate::cell::CellValue::String("one".to_string()),
801            ],
802        ];
803
804        let (definition, records, _) =
805            build_pivot_cache_parts("Sheet1", "A1:B3", &field_names, &rows, "rId1".to_string());
806
807        let mixed_items = definition.cache_fields.fields[1]
808            .shared_items
809            .as_ref()
810            .unwrap();
811        assert_eq!(mixed_items.contains_semi_mixed_types, Some(true));
812        assert!(records
813            .records
814            .iter()
815            .all(|record| { record.index_fields.len() == 2 && record.number_fields.is_empty() }));
816    }
817
818    #[test]
819    fn pivot_cache_indexes_preserve_first_seen_order_across_value_types() {
820        use crate::cell::CellValue;
821
822        let field_names = vec!["Mixed".to_string()];
823        let rows = vec![
824            vec![CellValue::String("B".to_string())],
825            vec![CellValue::Number(2.0)],
826            vec![CellValue::String("A".to_string())],
827            vec![CellValue::Number(1.0)],
828            vec![CellValue::String("B".to_string())],
829            vec![CellValue::Number(2.0)],
830        ];
831
832        let (definition, records, item_counts) =
833            build_pivot_cache_parts("Sheet1", "A1:A7", &field_names, &rows, "rId1".to_string());
834        let items = definition.cache_fields.fields[0]
835            .shared_items
836            .as_ref()
837            .unwrap();
838        assert_eq!(
839            items
840                .string_items
841                .iter()
842                .map(|item| item.value.as_str())
843                .collect::<Vec<_>>(),
844            vec!["B", "A"]
845        );
846        assert_eq!(
847            items
848                .number_items
849                .iter()
850                .map(|item| item.value)
851                .collect::<Vec<_>>(),
852            vec![2.0, 1.0]
853        );
854        assert_eq!(item_counts, vec![4]);
855        assert_eq!(
856            records
857                .records
858                .iter()
859                .map(|record| record.index_fields[0].v)
860                .collect::<Vec<_>>(),
861            vec![0, 2, 1, 3, 0, 2]
862        );
863    }
864
865    #[test]
866    fn pivot_cache_indexes_fifty_thousand_unique_values() {
867        let field_names = vec!["Text".to_string(), "Number".to_string()];
868        let rows = (0..50_000)
869            .map(|index| {
870                vec![
871                    crate::cell::CellValue::String(format!("value-{index:05}")),
872                    crate::cell::CellValue::Number(f64::from(index)),
873                ]
874            })
875            .collect::<Vec<_>>();
876
877        let (definition, records, item_counts) = build_pivot_cache_parts(
878            "Sheet1",
879            "A1:B50001",
880            &field_names,
881            &rows,
882            "rId1".to_string(),
883        );
884        let items = definition.cache_fields.fields[0]
885            .shared_items
886            .as_ref()
887            .unwrap();
888        let number_items = definition.cache_fields.fields[1]
889            .shared_items
890            .as_ref()
891            .unwrap();
892        assert_eq!(item_counts, vec![50_000, 50_000]);
893        assert_eq!(items.string_items.len(), 50_000);
894        assert_eq!(items.string_items[0].value, "value-00000");
895        assert_eq!(items.string_items[49_999].value, "value-49999");
896        assert_eq!(number_items.number_items.len(), 50_000);
897        assert_eq!(number_items.number_items[0].value, 0.0);
898        assert_eq!(number_items.number_items[49_999].value, 49_999.0);
899        assert_eq!(records.records[0].index_fields[0].v, 0);
900        assert_eq!(records.records[49_999].index_fields[0].v, 49_999);
901    }
902
903    #[test]
904    fn test_pivot_table_info_struct() {
905        let info = PivotTableInfo {
906            name: "PT1".to_string(),
907            source_sheet: "Data".to_string(),
908            source_range: "A1:D10".to_string(),
909            target_sheet: "Report".to_string(),
910            location: "A3:E20".to_string(),
911        };
912        assert_eq!(info.name, "PT1");
913        assert_eq!(info.source_sheet, "Data");
914        assert_eq!(info.source_range, "A1:D10");
915        assert_eq!(info.target_sheet, "Report");
916        assert_eq!(info.location, "A3:E20");
917    }
918
919    #[test]
920    fn test_build_pivot_table_xml_generates_default_display_name() {
921        let config = PivotTableConfig {
922            name: "PT".to_string(),
923            source_sheet: "S".to_string(),
924            source_range: "A1:B2".to_string(),
925            target_sheet: "T".to_string(),
926            target_cell: "A1".to_string(),
927            rows: vec![],
928            columns: vec![],
929            data: vec![
930                PivotDataField {
931                    name: "Amount".to_string(),
932                    function: AggregateFunction::Sum,
933                    display_name: None,
934                },
935                PivotDataField {
936                    name: "Count".to_string(),
937                    function: AggregateFunction::Count,
938                    display_name: Some("Total Count".to_string()),
939                },
940            ],
941        };
942        let field_names = vec!["Amount".to_string(), "Count".to_string()];
943
944        let def = build_pivot_table_xml(&config, 0, &field_names).unwrap();
945        let data_fields = def.data_fields.unwrap();
946
947        // No display_name -> auto-generated
948        assert_eq!(
949            data_fields.fields[0].name,
950            Some("Sum of Amount".to_string())
951        );
952        // Custom display_name preserved
953        assert_eq!(data_fields.fields[1].name, Some("Total Count".to_string()));
954    }
955
956    #[test]
957    fn test_error_pivot_table_not_found() {
958        let err = Error::PivotTableNotFound {
959            name: "Missing".to_string(),
960        };
961        assert_eq!(err.to_string(), "pivot table 'Missing' not found");
962    }
963
964    #[test]
965    fn test_error_pivot_table_already_exists() {
966        let err = Error::PivotTableAlreadyExists {
967            name: "PT1".to_string(),
968        };
969        assert_eq!(err.to_string(), "pivot table 'PT1' already exists");
970    }
971
972    #[test]
973    fn test_error_invalid_source_range() {
974        let err = Error::InvalidSourceRange("bad range".to_string());
975        assert_eq!(err.to_string(), "invalid source range: bad range");
976    }
977}