Skip to main content

sheetkit_core/
vml.rs

1//! VML (Vector Markup Language) support for Excel legacy comment rendering.
2//!
3//! Excel uses VML drawing parts (`xl/drawings/vmlDrawingN.vml`) to render
4//! comment/note pop-up boxes in the UI. This module generates minimal VML
5//! markup for new comments and tracks preserved VML bytes for round-tripping.
6
7use quick_xml::events::{BytesStart, Event};
8use quick_xml::name::ResolveResult;
9use quick_xml::NsReader;
10
11use crate::utils::cell_ref::cell_name_to_coordinates;
12
13/// Default comment box width in columns (roughly 2 columns).
14const DEFAULT_COMMENT_WIDTH_COLS: u32 = 2;
15/// Default comment box height in rows (roughly 4 rows).
16const DEFAULT_COMMENT_HEIGHT_ROWS: u32 = 4;
17
18/// Build a complete VML drawing document containing shapes for each comment cell.
19///
20/// `cells` is a list of cell references (e.g. `["A1", "B3"]`).
21/// Returns the VML XML string.
22pub fn build_vml_drawing(cells: &[&str]) -> String {
23    let shapes = build_comment_shapes(cells, 1025);
24
25    let mut doc = String::with_capacity(1024 + shapes.len());
26    doc.push_str("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\"");
27    doc.push_str(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
28    doc.push_str(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\n");
29    doc.push_str(" <o:shapelayout v:ext=\"edit\">\n");
30    doc.push_str("  <o:idmap v:ext=\"edit\" data=\"1\"/>\n");
31    doc.push_str(" </o:shapelayout>\n");
32    doc.push_str(" <v:shapetype id=\"_x0000_t202\" coordsize=\"21600,21600\"");
33    doc.push_str(" o:spt=\"202\" path=\"m,l,21600r21600,l21600,xe\">\n");
34    doc.push_str("  <v:stroke joinstyle=\"miter\"/>\n");
35    doc.push_str("  <v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>\n");
36    doc.push_str(" </v:shapetype>\n");
37    doc.push_str(&shapes);
38    doc.push_str("</xml>\n");
39    doc
40}
41
42/// Merge regenerated comment shapes into preserved VML content.
43pub fn merge_vml_comments(existing_vml: &[u8], cells: &[&str], start_shape_id: usize) -> Vec<u8> {
44    let existing = String::from_utf8_lossy(existing_vml);
45    let shapes = build_comment_shapes(cells, start_shape_id);
46    if let Some(close_pos) = existing.rfind("</xml>") {
47        let mut result = String::with_capacity(existing.len() + shapes.len());
48        result.push_str(&existing[..close_pos]);
49        if !existing.contains("_x0000_t202") {
50            result.push_str(
51                " <v:shapetype id=\"_x0000_t202\" coordsize=\"21600,21600\" \
52                 o:spt=\"202\" path=\"m,l,21600r21600,l21600,xe\">\n\
53                  <v:stroke joinstyle=\"miter\"/>\n\
54                  <v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>\n\
55                 </v:shapetype>\n",
56            );
57        }
58        result.push_str(&shapes);
59        result.push_str("</xml>\n");
60        result.into_bytes()
61    } else {
62        build_vml_drawing(cells).into_bytes()
63    }
64}
65
66/// VML namespace URI for `v:` elements.
67const VML_NS: &[u8] = b"urn:schemas-microsoft-com:vml";
68/// Excel namespace URI for `x:` elements such as `x:ClientData`.
69const EXCEL_NS: &[u8] = b"urn:schemas-microsoft-com:office:excel";
70
71/// Remove comment shapes while retaining non-comment VML content.
72///
73/// A comment shape is a VML `shape` element containing an Excel `ClientData`
74/// element whose `ObjectType` is `Note`. Elements are matched by namespace and
75/// local name, so any prefix, quote style, or attribute whitespace is accepted.
76/// All other content is preserved byte-for-byte. Returns `None` when no
77/// non-comment shape remains or the VML cannot be parsed.
78pub fn strip_vml_comment_shapes_from_vml(vml_bytes: &[u8]) -> Option<Vec<u8>> {
79    filter_vml_shapes(vml_bytes, |is_note| !is_note)
80}
81
82fn build_comment_shapes(cells: &[&str], start_shape_id: usize) -> String {
83    let mut shapes = String::new();
84    for (i, cell) in cells.iter().enumerate() {
85        if let Ok((col, row)) = cell_name_to_coordinates(cell) {
86            let anchor = comment_anchor(col, row);
87            write_vml_shape(
88                &mut shapes,
89                start_shape_id + i,
90                i + 1,
91                &anchor,
92                row - 1,
93                col - 1,
94            );
95        }
96    }
97    shapes
98}
99
100/// Whether `ns` is the namespace `uri`, also accepting an undeclared
101/// conventional prefix for leniency with hand-written VML.
102fn in_namespace(ns: &ResolveResult<'_>, uri: &[u8], conventional_prefix: &[u8]) -> bool {
103    match ns {
104        ResolveResult::Bound(bound) => bound.as_ref() == uri,
105        ResolveResult::Unknown(prefix) => prefix.as_slice() == conventional_prefix,
106        ResolveResult::Unbound => false,
107    }
108}
109
110fn is_vml_shape(ns: &ResolveResult<'_>, local_name: &[u8]) -> bool {
111    local_name == b"shape" && in_namespace(ns, VML_NS, b"v")
112}
113
114fn is_note_client_data(ns: &ResolveResult<'_>, element: &BytesStart<'_>) -> bool {
115    element.local_name().as_ref() == b"ClientData"
116        && in_namespace(ns, EXCEL_NS, b"x")
117        && element
118            .attributes()
119            .flatten()
120            .find(|attr| attr.key.as_ref() == b"ObjectType")
121            .and_then(|attr| attr.unescape_value().ok())
122            .is_some_and(|value| value.trim() == "Note")
123}
124
125/// Remove VML `shape` elements for which `keep(is_note)` returns false.
126///
127/// Byte ranges of removed shapes (plus one trailing line break) are cut from
128/// the original text; everything else is preserved. Returns `None` if no shape
129/// is kept or the markup cannot be parsed.
130fn filter_vml_shapes<F>(vml_bytes: &[u8], mut keep: F) -> Option<Vec<u8>>
131where
132    F: FnMut(bool) -> bool,
133{
134    let vml = String::from_utf8_lossy(vml_bytes);
135    let mut reader = NsReader::from_str(&vml);
136    // Legacy VML textboxes may contain HTML-style unclosed tags such as `<br>`.
137    reader.config_mut().check_end_names = false;
138
139    // Start offset and note flag of the shape currently open.
140    let mut open_shape: Option<(usize, bool)> = None;
141    let mut removals: Vec<(usize, usize)> = Vec::new();
142    let mut retained = false;
143
144    loop {
145        let event_start = usize::try_from(reader.buffer_position()).ok()?;
146        let (ns, event) = reader.read_resolved_event().ok()?;
147        match event {
148            Event::Start(ref e) | Event::Empty(ref e) => {
149                let is_empty = matches!(event, Event::Empty(_));
150                if let Some((_, is_note)) = open_shape.as_mut() {
151                    if is_note_client_data(&ns, e) {
152                        *is_note = true;
153                    }
154                } else if is_vml_shape(&ns, e.local_name().as_ref()) {
155                    if is_empty {
156                        if keep(false) {
157                            retained = true;
158                        } else {
159                            let end = usize::try_from(reader.buffer_position()).ok()?;
160                            removals.push((event_start, end));
161                        }
162                    } else {
163                        open_shape = Some((event_start, false));
164                    }
165                }
166            }
167            Event::End(ref e) => {
168                if open_shape.is_some() && is_vml_shape(&ns, e.local_name().as_ref()) {
169                    if let Some((start, is_note)) = open_shape.take() {
170                        if keep(is_note) {
171                            retained = true;
172                        } else {
173                            let end = usize::try_from(reader.buffer_position()).ok()?;
174                            removals.push((start, end));
175                        }
176                    }
177                }
178            }
179            Event::Eof => break,
180            _ => {}
181        }
182    }
183
184    if open_shape.is_some() || !retained {
185        return None;
186    }
187
188    let mut result = String::with_capacity(vml.len());
189    let mut cursor = 0;
190    for (start, end) in removals {
191        result.push_str(vml.get(cursor..start)?);
192        let rest = vml.get(end..)?;
193        cursor = end
194            + if rest.starts_with("\r\n") {
195                2
196            } else if rest.starts_with('\n') {
197                1
198            } else {
199                0
200            };
201    }
202    result.push_str(vml.get(cursor..)?);
203    Some(result.into_bytes())
204}
205
206/// Write a single VML shape element for a comment to the output string.
207fn write_vml_shape(
208    out: &mut String,
209    shape_id: usize,
210    z_index: usize,
211    anchor: &str,
212    row_0: u32,
213    col_0: u32,
214) {
215    use std::fmt::Write;
216    let _ = write!(out, " <v:shape id=\"_x0000_s{}\"", shape_id);
217    out.push_str(" type=\"#_x0000_t202\"");
218    let _ = write!(
219        out,
220        " style=\"position:absolute;margin-left:59.25pt;margin-top:1.5pt;\
221         width:108pt;height:59.25pt;z-index:{};visibility:hidden\"",
222        z_index
223    );
224    out.push_str(" fillcolor=\"#ffffe1\" o:insetmode=\"auto\">\n");
225    out.push_str("  <v:fill color2=\"#ffffe1\"/>\n");
226    out.push_str("  <v:shadow on=\"t\" color=\"black\" obscured=\"t\"/>\n");
227    out.push_str("  <v:path o:connecttype=\"none\"/>\n");
228    out.push_str("  <v:textbox/>\n");
229    out.push_str("  <x:ClientData ObjectType=\"Note\">\n");
230    out.push_str("   <x:MoveWithCells/>\n");
231    out.push_str("   <x:SizeWithCells/>\n");
232    let _ = writeln!(out, "   <x:Anchor>{}</x:Anchor>", anchor);
233    let _ = writeln!(out, "   <x:Row>{}</x:Row>", row_0);
234    let _ = writeln!(out, "   <x:Column>{}</x:Column>", col_0);
235    out.push_str("  </x:ClientData>\n");
236    out.push_str(" </v:shape>\n");
237}
238
239/// Compute the 8-value anchor string for a comment box near a cell.
240///
241/// Format: "LeftCol, LeftOff, TopRow, TopOff, RightCol, RightOff, BottomRow, BottomOff"
242/// Offsets are in Excel internal units (EMU/15). We use zero offsets for simplicity.
243fn comment_anchor(col: u32, row: u32) -> String {
244    let left_col = col;
245    let top_row = if row > 1 { row - 2 } else { 0 };
246    let right_col = col + DEFAULT_COMMENT_WIDTH_COLS;
247    let bottom_row = top_row + DEFAULT_COMMENT_HEIGHT_ROWS;
248    format!("{left_col}, 15, {top_row}, 10, {right_col}, 15, {bottom_row}, 4")
249}
250
251/// Extract comment cell references from an existing VML drawing XML string.
252///
253/// Scans for `<x:Row>` and `<x:Column>` elements in the VML and returns
254/// (row_0based, col_0based) pairs.
255pub fn extract_vml_comment_cells(vml_xml: &str) -> Vec<(u32, u32)> {
256    let mut cells = Vec::new();
257    let mut current_row: Option<u32> = None;
258    let mut current_col: Option<u32> = None;
259
260    for line in vml_xml.lines() {
261        let trimmed = line.trim();
262        if let Some(val) = extract_element_value(trimmed, "x:Row") {
263            current_row = val.parse().ok();
264        }
265        if let Some(val) = extract_element_value(trimmed, "x:Column") {
266            current_col = val.parse().ok();
267        }
268        if let (Some(row), Some(col)) = (current_row, current_col) {
269            cells.push((row, col));
270            current_row = None;
271            current_col = None;
272        }
273    }
274    cells
275}
276
277/// Simple extraction of text content from an XML element like `<tag>value</tag>`.
278///
279/// The closing tag is searched only after the first opening tag, so malformed
280/// input yields `None` instead of an invalid slice.
281fn extract_element_value<'a>(line: &'a str, tag: &str) -> Option<&'a str> {
282    let open = format!("<{}>", tag);
283    let close = format!("</{}>", tag);
284    let val_start = line.find(&open)? + open.len();
285    let rest = line.get(val_start..)?;
286    let len = rest.find(&close)?;
287    rest.get(..len)
288}
289
290#[cfg(test)]
291mod tests {
292    use super::*;
293
294    #[test]
295    fn test_build_vml_drawing_single_cell() {
296        let vml = build_vml_drawing(&["A1"]);
297        assert!(vml.contains("xmlns:v=\"urn:schemas-microsoft-com:vml\""));
298        assert!(vml.contains("<x:Row>0</x:Row>"));
299        assert!(vml.contains("<x:Column>0</x:Column>"));
300        assert!(vml.contains("ObjectType=\"Note\""));
301        assert!(vml.contains("_x0000_t202"));
302        assert!(vml.contains("fillcolor=\"#ffffe1\""));
303    }
304
305    #[test]
306    fn test_build_vml_drawing_multiple_cells() {
307        let vml = build_vml_drawing(&["A1", "C5"]);
308        assert!(vml.contains("<x:Row>0</x:Row>"));
309        assert!(vml.contains("<x:Column>0</x:Column>"));
310        assert!(vml.contains("<x:Row>4</x:Row>"));
311        assert!(vml.contains("<x:Column>2</x:Column>"));
312        assert!(vml.contains("_x0000_s1025"));
313        assert!(vml.contains("_x0000_s1026"));
314    }
315
316    #[test]
317    fn test_build_vml_drawing_empty() {
318        let vml = build_vml_drawing(&[]);
319        assert!(vml.contains("<o:shapelayout"));
320        assert!(vml.contains("<v:shapetype"));
321        assert!(!vml.contains("<v:shape id="));
322    }
323
324    #[test]
325    fn test_extract_vml_comment_cells() {
326        let vml = build_vml_drawing(&["B3", "D10"]);
327        let cells = extract_vml_comment_cells(&vml);
328        assert_eq!(cells.len(), 2);
329        assert_eq!(cells[0], (2, 1));
330        assert_eq!(cells[1], (9, 3));
331    }
332
333    #[test]
334    fn test_comment_anchor_format() {
335        let anchor = comment_anchor(1, 1);
336        assert!(anchor.contains(", "));
337        let parts: Vec<&str> = anchor.split(", ").collect();
338        assert_eq!(parts.len(), 8);
339    }
340
341    #[test]
342    fn test_extract_element_value() {
343        assert_eq!(
344            extract_element_value("<x:Row>5</x:Row>", "x:Row"),
345            Some("5")
346        );
347        assert_eq!(
348            extract_element_value("<x:Column>3</x:Column>", "x:Column"),
349            Some("3")
350        );
351        assert_eq!(extract_element_value("no match here", "x:Row"), None);
352    }
353
354    #[test]
355    fn test_extract_vml_comment_cells_never_panics_on_malformed_tags() {
356        let cases: &[(&str, Vec<(u32, u32)>)] = &[
357            ("</x:Row><x:Row>0</x:Row>", vec![]),
358            (
359                "</x:Row><x:Row>0</x:Row><x:Column>1</x:Column>",
360                vec![(0, 1)],
361            ),
362            ("</x:Column><x:Column>", vec![]),
363            ("<x:Row>1", vec![]),
364            ("<x:Row></x:Row>", vec![]),
365            (
366                "<x:Row>1</x:Row><x:Row>2</x:Row><x:Column>3</x:Column>",
367                vec![(1, 3)],
368            ),
369            (
370                "<x:Row><x:Row>4</x:Row></x:Row><x:Column>5</x:Column>",
371                vec![],
372            ),
373            ("</x:Row>", vec![]),
374            ("", vec![]),
375        ];
376        for (input, expected) in cases {
377            assert_eq!(&extract_vml_comment_cells(input), expected, "{input}");
378        }
379
380        let pieces = [
381            "<x:Row>",
382            "</x:Row>",
383            "<x:Column>",
384            "</x:Column>",
385            "7",
386            "\n",
387            "\u{e9}",
388        ];
389        let mut input = String::new();
390        for a in pieces {
391            for b in pieces {
392                for c in pieces {
393                    for d in pieces {
394                        input.clear();
395                        input.push_str(a);
396                        input.push_str(b);
397                        input.push_str(c);
398                        input.push_str(d);
399                        let _ = extract_vml_comment_cells(&input);
400                    }
401                }
402            }
403        }
404    }
405
406    const VML_NS_HEADER: &str = "<xml xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\">";
407
408    #[test]
409    fn test_strip_comment_shapes_single_quoted_object_type() {
410        let vml = format!(
411            "{VML_NS_HEADER}\n<v:shape id='c1'><x:ClientData ObjectType='Note'><x:Row>0</x:Row></x:ClientData></v:shape>\n<v:shape id='b1'><x:ClientData ObjectType='Button'/></v:shape>\n</xml>\n"
412        );
413        let out =
414            String::from_utf8(strip_vml_comment_shapes_from_vml(vml.as_bytes()).unwrap()).unwrap();
415        assert!(!out.contains("c1"), "{out}");
416        assert!(out.contains("<v:shape id='b1'><x:ClientData ObjectType='Button'/></v:shape>"));
417        assert!(out.trim_end().ends_with("</xml>"));
418    }
419
420    #[test]
421    fn test_strip_comment_shapes_whitespace_and_newlines_in_attributes() {
422        let vml = format!(
423            "{VML_NS_HEADER}\n<v:shape\n id=\"c1\"><x:ClientData\n   ObjectType = \"Note\"\n></x:ClientData></v:shape\n>\n<v:shape\tid=\"b1\"><x:ClientData ObjectType=\"Drop\"/></v:shape>\n</xml>\n"
424        );
425        let out =
426            String::from_utf8(strip_vml_comment_shapes_from_vml(vml.as_bytes()).unwrap()).unwrap();
427        assert!(!out.contains("c1"), "{out}");
428        assert!(out.contains("<v:shape\tid=\"b1\">"));
429    }
430
431    #[test]
432    fn test_strip_comment_shapes_alternate_vml_prefix() {
433        let vml = "<xml xmlns:s=\"urn:schemas-microsoft-com:vml\" xmlns:e=\"urn:schemas-microsoft-com:office:excel\">\n<s:shape id=\"c1\"><e:ClientData ObjectType=\"Note\"/></s:shape>\n<s:shape id=\"b1\"><e:ClientData ObjectType=\"Button\"/></s:shape>\n</xml>\n";
434        let out =
435            String::from_utf8(strip_vml_comment_shapes_from_vml(vml.as_bytes()).unwrap()).unwrap();
436        assert!(!out.contains("c1"), "{out}");
437        assert!(out.contains("<s:shape id=\"b1\"><e:ClientData ObjectType=\"Button\"/></s:shape>"));
438        assert!(out.starts_with("<xml xmlns:s="));
439    }
440
441    #[test]
442    fn test_strip_comment_shapes_preserves_surrounding_content() {
443        let vml = format!(
444            "{VML_NS_HEADER}\n <o:shapelayout v:ext=\"edit\"><o:idmap v:ext=\"edit\" data=\"1\"/></o:shapelayout>\n<v:shapetype id=\"_x0000_t201\"/>\n<v:shape id=\"b1\"><x:ClientData ObjectType=\"Button\"/></v:shape>\n<v:shapetype id=\"_x0000_t202\"/>\n<v:shape id=\"c1\"><x:ClientData ObjectType=\"Note\"/></v:shape>\n<v:rect id=\"r1\"/>\n</xml>\n"
445        );
446        let out =
447            String::from_utf8(strip_vml_comment_shapes_from_vml(vml.as_bytes()).unwrap()).unwrap();
448        assert!(!out.contains("c1"), "{out}");
449        for kept in [
450            "<o:shapelayout",
451            "_x0000_t201",
452            "_x0000_t202",
453            "id=\"b1\"",
454            "<v:rect id=\"r1\"/>",
455        ] {
456            assert!(out.contains(kept), "missing {kept}: {out}");
457        }
458        assert_eq!(out.matches("</xml>").count(), 1);
459    }
460
461    #[test]
462    fn test_strip_comment_shapes_only_comments_returns_none() {
463        let vml = build_vml_drawing(&["A1", "B2"]);
464        assert!(strip_vml_comment_shapes_from_vml(vml.as_bytes()).is_none());
465    }
466
467    #[test]
468    fn test_strip_comment_shapes_ignores_foreign_namespace_lookalikes() {
469        // A `shape` element outside the VML namespace is not a VML shape, and a
470        // `ClientData` element outside the Excel namespace does not mark a note.
471        let vml = format!(
472            "{VML_NS_HEADER}<other:shape xmlns:other=\"urn:other\" id=\"f1\"/><v:shape id=\"k1\"><y:ClientData xmlns:y=\"urn:other\" ObjectType=\"Note\"/></v:shape></xml>"
473        );
474        let out =
475            String::from_utf8(strip_vml_comment_shapes_from_vml(vml.as_bytes()).unwrap()).unwrap();
476        assert!(out.contains("id=\"f1\""));
477        assert!(out.contains("id=\"k1\""));
478    }
479
480    #[test]
481    fn test_strip_comment_shapes_then_merge_has_no_stale_notes() {
482        let mut vml = build_vml_drawing(&["A1"]);
483        let button = " <v:shape id='_x0000_s2000' type='#_x0000_t201'><x:ClientData ObjectType='Button'/></v:shape>\n";
484        vml.insert_str(vml.rfind("</xml>").unwrap(), button);
485        let vml = vml.replace("ObjectType=\"Note\"", "ObjectType='Note'");
486
487        let stripped = strip_vml_comment_shapes_from_vml(vml.as_bytes()).unwrap();
488        let merged = String::from_utf8(merge_vml_comments(&stripped, &["C3"], 3000)).unwrap();
489        assert_eq!(extract_vml_comment_cells(&merged), vec![(2, 2)]);
490        assert!(merged.contains("_x0000_s2000"));
491    }
492
493    #[test]
494    fn test_strip_comment_shapes_never_panics() {
495        for input in [
496            "",
497            "<xml>",
498            "<v:shape ",
499            "<v:shape id='a'>",
500            "</v:shape><v:shape id='a'></v:shape>",
501            "<xml xmlns:v=\"urn:schemas-microsoft-com:vml\"><v:shape><v:shape></v:shape>",
502            "\u{0}\u{ff}<",
503        ] {
504            let _ = strip_vml_comment_shapes_from_vml(input.as_bytes());
505        }
506    }
507}