Skip to main content

fast_excel/read/
sheet_meta.rs

1//! 工作表级附加信息:合并区域 / 超链接 / 批注
2//!
3//! `<mergeCells>` 与 `<hyperlinks>` 都在 `<sheetData>` **之后**,流式读取时行早已产出,
4//! 因此这里用一次独立预扫描把它们收集好;批注存在独立的 `comments*.xml` 部件里,
5//! 通过工作表 rels 定位。
6
7use std::collections::HashMap;
8use std::io::BufReader;
9
10use quick_xml::Reader;
11use quick_xml::events::{BytesStart, Event};
12
13use crate::error::Result;
14use crate::read::images::{file_name, normalize_path, parse_rels};
15use crate::read::merges::{MergeRange, parse_range, parse_ref};
16use crate::read::source::ZipSource;
17
18/// 单元格超链接
19#[derive(Debug, Clone, PartialEq, Eq)]
20pub struct Hyperlink {
21    /// 所在行(1 基)
22    pub row: u32,
23    /// 所在列(0 基)
24    pub col: u16,
25    /// 外部目标(URL / 文件路径),来自工作表 rels
26    pub target: Option<String>,
27    /// 内部位置(如 `Sheet2!A1`)
28    pub location: Option<String>,
29    /// 显示文本
30    pub display: Option<String>,
31}
32
33impl Hyperlink {
34    pub fn contains(&self, row: u32, col: u16) -> bool {
35        self.row == row && self.col == col
36    }
37
38    /// 链接地址(优先外部目标,其次内部位置)
39    pub fn href(&self) -> Option<&str> {
40        self.target.as_deref().or(self.location.as_deref())
41    }
42}
43
44/// 单元格批注
45#[derive(Debug, Clone, PartialEq, Eq)]
46pub struct Comment {
47    /// 所在行(1 基)
48    pub row: u32,
49    /// 所在列(0 基)
50    pub col: u16,
51    pub text: String,
52    pub author: Option<String>,
53}
54
55/// 数据验证(下拉 / 数值区间等)
56#[derive(Debug, Clone, PartialEq, Eq)]
57pub struct DataValidation {
58    /// 类型:`list` / `whole` / `decimal` / `date` / `textLength` / `custom` …
59    pub kind: String,
60    /// 生效区域(`sqref`,可能多段)
61    pub ranges: Vec<MergeRange>,
62    /// 原始来源(`formula1`,下拉列表可能形如 `"A,B,C"` 或区域引用)
63    pub formula: Option<String>,
64    /// 下拉选项(`kind == "list"` 且为内联列表时解析)
65    pub items: Vec<String>,
66    pub prompt: Option<String>,
67    pub prompt_title: Option<String>,
68    pub allow_blank: bool,
69}
70
71impl DataValidation {
72    pub fn is_list(&self) -> bool {
73        self.kind == "list"
74    }
75
76    /// 某单元格是否落在该验证区域内
77    pub fn contains(&self, row: u32, col: u16) -> bool {
78        self.ranges.iter().any(|r| r.contains(row, col))
79    }
80}
81
82/// 一条条件格式规则
83#[derive(Debug, Clone, PartialEq, Eq)]
84pub struct CfRule {
85    /// `cellIs` / `expression` / `colorScale` / `dataBar` / `iconSet` / `top10` / `containsText` …
86    pub kind: String,
87    /// 比较运算符(`cellIs` 时有效,如 `greaterThan`)
88    pub operator: Option<String>,
89    /// 规则公式 / 阈值(可能多条)
90    pub formulas: Vec<String>,
91    /// 差异格式索引(`dxfId`,具体颜色需查 `dxfs`)
92    pub dxf_id: Option<u32>,
93    pub priority: Option<u32>,
94}
95
96/// 条件格式(一个 `sqref` 区域 + 若干规则)
97#[derive(Debug, Clone, PartialEq, Eq)]
98pub struct ConditionalFormat {
99    /// 生效区域(`sqref`)
100    pub ranges: Vec<MergeRange>,
101    pub rules: Vec<CfRule>,
102}
103
104impl ConditionalFormat {
105    pub fn is_empty(&self) -> bool {
106        self.rules.is_empty()
107    }
108
109    /// 某单元格是否落在该条件格式区域内
110    pub fn contains(&self, row: u32, col: u16) -> bool {
111        self.ranges.iter().any(|r| r.contains(row, col))
112    }
113}
114
115/// 一次预扫描需要哪些信息
116#[derive(Debug, Clone, Copy, Default)]
117pub(crate) struct MetaWants {
118    pub merges: bool,
119    pub hyperlinks: bool,
120    pub validations: bool,
121    pub conditional_formats: bool,
122}
123
124/// 一次预扫描得到的附加信息
125#[derive(Debug, Default)]
126pub(crate) struct SheetMeta {
127    pub merges: Vec<MergeRange>,
128    pub hyperlinks: Vec<Hyperlink>,
129    pub validations: Vec<DataValidation>,
130    pub conditional_formats: Vec<ConditionalFormat>,
131}
132
133/// 扫描 `<mergeCells>` / `<hyperlinks>` / `<dataValidations>`(一趟遍历)
134pub(crate) fn load_sheet_meta(
135    source: &ZipSource,
136    sheet_path: &str,
137    wants: MetaWants,
138) -> Result<SheetMeta> {
139    let mut meta = SheetMeta::default();
140    if !wants.merges
141        && !wants.hyperlinks
142        && !wants.validations
143        && !wants.conditional_formats
144    {
145        return Ok(meta);
146    }
147
148    let entry = source.entry_reader(sheet_path)?;
149    let mut xml = Reader::from_reader(BufReader::with_capacity(64 * 1024, entry));
150    xml.config_mut().trim_text(true);
151    let mut buf = Vec::with_capacity(8 * 1024);
152    let mut raw_links: Vec<RawLink> = Vec::new();
153    let mut cur_dv: Option<DvBuilder> = None;
154    let mut capturing_formula = false;
155    let mut cur_cf: Option<CfBuilder> = None;
156    let mut cur_rule: Option<CfRule> = None;
157    let mut capturing_cf_formula = false;
158
159    loop {
160        match xml.read_event_into(&mut buf) {
161            Ok(Event::Eof) => break,
162            Err(e) => return Err(e.into()),
163            Ok(Event::Start(e)) => match e.local_name().as_ref() {
164                b"mergeCell" if wants.merges => {
165                    if let Some(r) = attr(&e, b"ref").as_deref().and_then(parse_range) {
166                        meta.merges.push(r);
167                    }
168                }
169                b"hyperlink" if wants.hyperlinks => raw_links.push(raw_link(&e)),
170                b"dataValidation" if wants.validations => cur_dv = Some(DvBuilder::from(&e)),
171                b"formula1" if cur_dv.is_some() => capturing_formula = true,
172                b"conditionalFormatting" if wants.conditional_formats => {
173                    cur_cf = Some(CfBuilder::from(&e))
174                }
175                b"cfRule" if cur_cf.is_some() => cur_rule = Some(CfRule::from(&e)),
176                b"formula" if cur_rule.is_some() => {
177                    if let Some(rule) = cur_rule.as_mut() {
178                        rule.formulas.push(String::new());
179                    }
180                    capturing_cf_formula = true;
181                }
182                _ => {}
183            },
184            Ok(Event::Empty(e)) => match e.local_name().as_ref() {
185                b"mergeCell" if wants.merges => {
186                    if let Some(r) = attr(&e, b"ref").as_deref().and_then(parse_range) {
187                        meta.merges.push(r);
188                    }
189                }
190                b"hyperlink" if wants.hyperlinks => raw_links.push(raw_link(&e)),
191                b"dataValidation" if wants.validations => {
192                    meta.validations.push(DvBuilder::from(&e).finish())
193                }
194                b"cfRule" if cur_cf.is_some() => {
195                    if let Some(cf) = cur_cf.as_mut() {
196                        cf.rules.push(CfRule::from(&e));
197                    }
198                }
199                _ => {}
200            },
201            Ok(Event::Text(t)) if capturing_formula => {
202                if let Some(dv) = cur_dv.as_mut() {
203                    dv.formula
204                        .get_or_insert_with(String::new)
205                        .push_str(&String::from_utf8_lossy(t.as_ref()));
206                }
207            }
208            Ok(Event::Text(t)) if capturing_cf_formula => {
209                if let Some(rule) = cur_rule.as_mut() {
210                    if let Some(last) = rule.formulas.last_mut() {
211                        last.push_str(&String::from_utf8_lossy(t.as_ref()));
212                    }
213                }
214            }
215            Ok(Event::End(e)) => match e.local_name().as_ref() {
216                b"formula1" => capturing_formula = false,
217                b"formula" => capturing_cf_formula = false,
218                b"cfRule" => {
219                    if let (Some(cf), Some(rule)) = (cur_cf.as_mut(), cur_rule.take()) {
220                        cf.rules.push(rule);
221                    }
222                }
223                b"conditionalFormatting" => {
224                    if let Some(cf) = cur_cf.take() {
225                        if !cf.rules.is_empty() {
226                            meta.conditional_formats.push(cf.finish());
227                        }
228                    }
229                }
230                b"dataValidation" => {
231                    if let Some(dv) = cur_dv.take() {
232                        meta.validations.push(dv.finish());
233                    }
234                }
235                _ => {}
236            },
237            _ => {}
238        }
239        buf.clear();
240    }
241
242    meta.merges.sort_by_key(|r| (r.first_row, r.first_col));
243
244    let rels = if raw_links.iter().any(|l| l.rid.is_some()) {
245        load_sheet_rels(source, sheet_path)
246    } else {
247        HashMap::new()
248    };
249    meta.hyperlinks = raw_links
250        .into_iter()
251        .filter_map(|l| {
252            let (row, col) = l.cell?;
253            Some(Hyperlink {
254                row,
255                col,
256                target: l.rid.and_then(|id| rels.get(&id).cloned()),
257                location: l.location,
258                display: l.display,
259            })
260        })
261        .collect();
262
263    Ok(meta)
264}
265
266/// 扫描到的原始超链接(`r:id` 待解析成 target)
267struct RawLink {
268    cell: Option<(u32, u16)>,
269    rid: Option<String>,
270    location: Option<String>,
271    display: Option<String>,
272}
273
274fn raw_link(e: &BytesStart<'_>) -> RawLink {
275    RawLink {
276        cell: attr(e, b"ref")
277            .as_deref()
278            .and_then(parse_ref)
279            .map(|(col, row)| (row, col)),
280        rid: attr(e, b"r:id").or_else(|| attr(e, b"id")),
281        location: attr(e, b"location"),
282        display: attr(e, b"display"),
283    }
284}
285
286/// `dataValidation` 的暂存(`formula1` 文本在子元素里)
287struct DvBuilder {
288    kind: String,
289    sqref: String,
290    formula: Option<String>,
291    prompt: Option<String>,
292    prompt_title: Option<String>,
293    allow_blank: bool,
294}
295
296impl From<&BytesStart<'_>> for DvBuilder {
297    fn from(e: &BytesStart<'_>) -> Self {
298        Self {
299            kind: attr(e, b"type").unwrap_or_else(|| "none".to_string()),
300            sqref: attr(e, b"sqref").unwrap_or_default(),
301            formula: None,
302            prompt: attr(e, b"prompt"),
303            prompt_title: attr(e, b"promptTitle"),
304            allow_blank: attr(e, b"allowBlank").as_deref() == Some("1"),
305        }
306    }
307}
308
309impl DvBuilder {
310    fn finish(self) -> DataValidation {
311        let ranges: Vec<MergeRange> = self
312            .sqref
313            .split_whitespace()
314            .filter_map(parse_range)
315            .collect();
316        let items = if self.kind == "list" {
317            self.formula.as_deref().map(parse_list_items).unwrap_or_default()
318        } else {
319            Vec::new()
320        };
321        DataValidation {
322            kind: self.kind,
323            ranges,
324            formula: self.formula,
325            items,
326            prompt: self.prompt,
327            prompt_title: self.prompt_title,
328            allow_blank: self.allow_blank,
329        }
330    }
331}
332
333/// `conditionalFormatting` 的暂存(`sqref` + 规则)
334struct CfBuilder {
335    sqref: String,
336    rules: Vec<CfRule>,
337}
338
339impl From<&BytesStart<'_>> for CfBuilder {
340    fn from(e: &BytesStart<'_>) -> Self {
341        Self {
342            sqref: attr(e, b"sqref").unwrap_or_default(),
343            rules: Vec::new(),
344        }
345    }
346}
347
348impl CfBuilder {
349    fn finish(self) -> ConditionalFormat {
350        ConditionalFormat {
351            ranges: self.sqref.split_whitespace().filter_map(parse_range).collect(),
352            rules: self.rules,
353        }
354    }
355}
356
357impl From<&BytesStart<'_>> for CfRule {
358    fn from(e: &BytesStart<'_>) -> Self {
359        Self {
360            kind: attr(e, b"type").unwrap_or_else(|| "expression".to_string()),
361            operator: attr(e, b"operator"),
362            formulas: Vec::new(),
363            dxf_id: attr(e, b"dxfId").and_then(|v| v.parse().ok()),
364            priority: attr(e, b"priority").and_then(|v| v.parse().ok()),
365        }
366    }
367}
368
369/// 解析内联下拉列表 `"A,B,C"` → `["A","B","C"]`
370fn parse_list_items(formula: &str) -> Vec<String> {
371    let t = formula.trim();
372    let t = t
373        .strip_prefix('"')
374        .and_then(|x| x.strip_suffix('"'))
375        .unwrap_or(t);
376    t.split(',')
377        .map(|s| s.trim().to_string())
378        .filter(|s| !s.is_empty())
379        .collect()
380}
381
382/// 读取某工作表的所有批注:经典批注(`comments*.xml`)+ 线程批注(`threadedComments`)
383///
384/// 线程批注只收「根批注」(带 `ref` 的条目);回复(只有 `parentId`)暂不展开。
385pub(crate) fn load_comments(source: &ZipSource, sheet_path: &str) -> Result<Vec<Comment>> {
386    let rels = load_sheet_rels(source, sheet_path);
387    let mut out = Vec::new();
388    let persons = load_persons(source);
389    for target in rels.values() {
390        if target.contains("threadedComment") {
391            let path = normalize_path(&format!("xl/worksheets/{target}"));
392            if let Ok(xml) = source.read_entry(&path) {
393                out.extend(parse_threaded_comments(&xml, &persons));
394            }
395        } else if target.contains("comments") {
396            let path = normalize_path(&format!("xl/worksheets/{target}"));
397            if let Ok(xml) = source.read_entry(&path) {
398                out.extend(parse_comments(&xml)?);
399            }
400        }
401    }
402    out.sort_by_key(|c| (c.row, c.col));
403    Ok(out)
404}
405
406/// 读取 `persons`(线程批注作者表):`personId → displayName`
407fn load_persons(source: &ZipSource) -> HashMap<String, String> {
408    let Ok(rels_xml) = source.read_entry("xl/_rels/workbook.xml.rels") else {
409        return HashMap::new();
410    };
411    let rels = parse_rels(&rels_xml);
412    let Some(target) = rels.values().find(|t| t.contains("person")) else {
413        return HashMap::new();
414    };
415    let path = normalize_path(&format!("xl/{target}"));
416    let Ok(xml) = source.read_entry(&path) else {
417        return HashMap::new();
418    };
419
420    let mut reader = Reader::from_reader(xml.as_slice());
421    reader.config_mut().trim_text(true);
422    let mut buf = Vec::with_capacity(4 * 1024);
423    let mut out = HashMap::new();
424    loop {
425        match reader.read_event_into(&mut buf) {
426            Ok(Event::Eof) => break,
427            Ok(Event::Start(e)) | Ok(Event::Empty(e))
428                if e.local_name().as_ref() == b"person" =>
429            {
430                if let (Some(id), Some(name)) = (attr(&e, b"id"), attr(&e, b"displayName")) {
431                    out.insert(id, name);
432                }
433            }
434            _ => {}
435        }
436        buf.clear();
437    }
438    out
439}
440
441/// 解析 `threadedComments/threadedCommentN.xml`
442fn parse_threaded_comments(xml: &[u8], persons: &HashMap<String, String>) -> Vec<Comment> {
443    let mut reader = Reader::from_reader(xml);
444    reader.config_mut().trim_text(true);
445    let mut buf = Vec::with_capacity(8 * 1024);
446
447    let mut in_comment = false;
448    let mut in_text = false;
449    let mut cell: Option<(u32, u16)> = None;
450    let mut person: Option<String> = None;
451    let mut id: Option<String> = None;
452    let mut parent_id: Option<String> = None;
453    let mut text = String::new();
454    let mut out: Vec<Comment> = Vec::new();
455    // 根批注 id → out 下标(回复按 parentId 并入父批注)
456    let mut by_id: HashMap<String, usize> = HashMap::new();
457
458    loop {
459        match reader.read_event_into(&mut buf) {
460            Ok(Event::Eof) => break,
461            Ok(Event::Start(e)) => match e.local_name().as_ref() {
462                b"threadedComment" => {
463                    in_comment = true;
464                    cell = attr(&e, b"ref")
465                        .as_deref()
466                        .and_then(parse_ref)
467                        .map(|(c, r)| (r, c));
468                    person = attr(&e, b"personId");
469                    id = attr(&e, b"id");
470                    parent_id = attr(&e, b"parentId");
471                    text.clear();
472                }
473                b"text" if in_comment => in_text = true,
474                _ => {}
475            },
476            Ok(Event::Text(t)) if in_text => {
477                text.push_str(&String::from_utf8_lossy(t.as_ref()));
478            }
479            Ok(Event::End(e)) => match e.local_name().as_ref() {
480                b"text" => in_text = false,
481                b"threadedComment" => {
482                    in_comment = false;
483                    let author = person.as_ref().and_then(|p| persons.get(p).cloned());
484                    let body = text.trim().to_string();
485                    match parent_id.as_ref().and_then(|p| by_id.get(p).copied()) {
486                        // 回复:并入父批注文本
487                        Some(idx) => {
488                            let who = author.as_deref().unwrap_or("回复");
489                            out[idx].text.push_str(&format!("\n— {who}:{body}"));
490                        }
491                        // 根批注
492                        None => {
493                            if let Some((row, col)) = cell {
494                                let idx = out.len();
495                                if let Some(root_id) = id.clone() {
496                                    by_id.insert(root_id, idx);
497                                }
498                                out.push(Comment {
499                                    row,
500                                    col,
501                                    text: body,
502                                    author,
503                                });
504                            }
505                        }
506                    }
507                }
508                _ => {}
509            },
510            _ => {}
511        }
512        buf.clear();
513    }
514    out
515}
516
517fn parse_comments(xml: &[u8]) -> Result<Vec<Comment>> {
518    let mut reader = Reader::from_reader(xml);
519    reader.config_mut().trim_text(false);
520    let mut buf = Vec::with_capacity(8 * 1024);
521
522    let mut authors: Vec<String> = Vec::new();
523    let mut in_authors = false;
524    let mut in_author = false;
525    let mut in_comment = false;
526    let mut in_text = false;
527    let mut cur_cell: Option<(u32, u16)> = None;
528    let mut cur_author: Option<String> = None;
529    let mut cur_text = String::new();
530    let mut out = Vec::new();
531
532    loop {
533        match reader.read_event_into(&mut buf) {
534            Ok(Event::Eof) => break,
535            Err(e) => return Err(e.into()),
536            Ok(Event::Start(e)) => match e.local_name().as_ref() {
537                b"authors" => in_authors = true,
538                b"author" if in_authors => in_author = true,
539                b"comment" => {
540                    in_comment = true;
541                    cur_cell = attr(&e, b"ref").as_deref().and_then(parse_ref).map(|(c, r)| (r, c));
542                    let id = attr(&e, b"authorId").and_then(|v| v.parse::<usize>().ok());
543                    cur_author = id.and_then(|i| authors.get(i).cloned());
544                    cur_text.clear();
545                }
546                b"text" if in_comment => in_text = true,
547                _ => {}
548            },
549            Ok(Event::Text(t)) => {
550                if in_author {
551                    authors.push(String::from_utf8_lossy(t.as_ref()).into_owned());
552                } else if in_comment && in_text {
553                    cur_text.push_str(&String::from_utf8_lossy(t.as_ref()));
554                }
555            }
556            Ok(Event::End(e)) => match e.local_name().as_ref() {
557                b"authors" => in_authors = false,
558                b"author" => in_author = false,
559                b"text" => in_text = false,
560                b"comment" => {
561                    in_comment = false;
562                    if let Some((row, col)) = cur_cell {
563                        out.push(Comment {
564                            row,
565                            col,
566                            text: cur_text.trim().to_string(),
567                            author: cur_author.clone(),
568                        });
569                    }
570                }
571                _ => {}
572            },
573            _ => {}
574        }
575        buf.clear();
576    }
577    Ok(out)
578}
579
580fn load_sheet_rels(source: &ZipSource, sheet_path: &str) -> HashMap<String, String> {
581    let rels_path = format!("xl/worksheets/_rels/{}.rels", file_name(sheet_path));
582    source
583        .read_entry(&rels_path)
584        .map(|x| parse_rels(&x))
585        .unwrap_or_default()
586}
587
588fn attr(e: &BytesStart<'_>, name: &[u8]) -> Option<String> {
589    e.attributes()
590        .flatten()
591        .find(|a| a.key.as_ref() == name)
592        .map(|a| String::from_utf8_lossy(&a.value).into_owned())
593}
594
595#[cfg(test)]
596mod tests {
597    use super::*;
598
599    #[test]
600    fn test_parse_comments() {
601        let xml = r#"<?xml version="1.0"?>
602<comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
603  <authors><author>张三</author></authors>
604  <commentList>
605    <comment ref="B2" authorId="0"><text><r><t>请核对数量</t></r></text></comment>
606    <comment ref="C3"><text><t>无作者批注</t></text></comment>
607  </commentList>
608</comments>"#;
609        let list = parse_comments(xml.as_bytes()).unwrap();
610        assert_eq!(list.len(), 2);
611        assert_eq!(list[0].row, 2);
612        assert_eq!(list[0].col, 1);
613        assert_eq!(list[0].text, "请核对数量");
614        assert_eq!(list[0].author.as_deref(), Some("张三"));
615        assert_eq!(list[1].text, "无作者批注");
616        assert_eq!(list[1].author, None);
617    }
618
619    #[test]
620    fn test_parse_threaded_comments_with_reply() {
621        let xml = r#"<?xml version="1.0"?>
622<ThreadedComments xmlns="http://schemas.microsoft.com/office/spreadsheetml/2018/threadedcomments">
623  <threadedComment ref="B2" personId="{P1}" id="{C1}" date="2025-01-01T00:00:00Z"><text>请核对数量</text></threadedComment>
624  <threadedComment ref="C3" personId="{P2}" id="{C2}"><text>已确认</text></threadedComment>
625  <threadedComment parentId="{C1}" personId="{P2}" id="{C1R}"><text>已核对</text></threadedComment>
626</ThreadedComments>"#;
627        let mut persons = HashMap::new();
628        persons.insert("{P1}".to_string(), "张三".to_string());
629        persons.insert("{P2}".to_string(), "李四".to_string());
630        let list = parse_threaded_comments(xml.as_bytes(), &persons);
631        assert_eq!(list.len(), 2, "回复不单独成条");
632        assert_eq!((list[0].row, list[0].col), (2, 1));
633        assert_eq!(list[0].author.as_deref(), Some("张三"));
634        assert!(list[0].text.contains("请核对数量"));
635        assert!(list[0].text.contains("已核对"), "回复应并入父批注:{}", list[0].text);
636        assert!(list[0].text.contains("李四"), "回复应带作者:{}", list[0].text);
637        assert_eq!(list[1].text, "已确认");
638    }
639}