use std::collections::HashMap;
use std::io::BufReader;
use quick_xml::Reader;
use quick_xml::events::{BytesStart, Event};
use crate::error::Result;
use crate::read::images::{file_name, normalize_path, parse_rels};
use crate::read::merges::{MergeRange, parse_range, parse_ref};
use crate::read::source::ZipSource;
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct Hyperlink {
pub row: u32,
pub col: u16,
pub target: Option<String>,
pub location: Option<String>,
pub display: Option<String>,
}
impl Hyperlink {
pub fn contains(&self, row: u32, col: u16) -> bool {
self.row == row && self.col == col
}
pub fn href(&self) -> Option<&str> {
self.target.as_deref().or(self.location.as_deref())
}
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct Comment {
pub row: u32,
pub col: u16,
pub text: String,
pub author: Option<String>,
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct DataValidation {
pub kind: String,
pub ranges: Vec<MergeRange>,
pub formula: Option<String>,
pub items: Vec<String>,
pub prompt: Option<String>,
pub prompt_title: Option<String>,
pub allow_blank: bool,
}
impl DataValidation {
pub fn is_list(&self) -> bool {
self.kind == "list"
}
pub fn contains(&self, row: u32, col: u16) -> bool {
self.ranges.iter().any(|r| r.contains(row, col))
}
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct CfRule {
pub kind: String,
pub operator: Option<String>,
pub formulas: Vec<String>,
pub dxf_id: Option<u32>,
pub priority: Option<u32>,
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct ConditionalFormat {
pub ranges: Vec<MergeRange>,
pub rules: Vec<CfRule>,
}
impl ConditionalFormat {
pub fn is_empty(&self) -> bool {
self.rules.is_empty()
}
pub fn contains(&self, row: u32, col: u16) -> bool {
self.ranges.iter().any(|r| r.contains(row, col))
}
}
#[derive(Debug, Clone, Copy, Default)]
pub(crate) struct MetaWants {
pub merges: bool,
pub hyperlinks: bool,
pub validations: bool,
pub conditional_formats: bool,
}
#[derive(Debug, Default)]
pub(crate) struct SheetMeta {
pub merges: Vec<MergeRange>,
pub hyperlinks: Vec<Hyperlink>,
pub validations: Vec<DataValidation>,
pub conditional_formats: Vec<ConditionalFormat>,
}
pub(crate) fn load_sheet_meta(
source: &ZipSource,
sheet_path: &str,
wants: MetaWants,
) -> Result<SheetMeta> {
let mut meta = SheetMeta::default();
if !wants.merges
&& !wants.hyperlinks
&& !wants.validations
&& !wants.conditional_formats
{
return Ok(meta);
}
let entry = source.entry_reader(sheet_path)?;
let mut xml = Reader::from_reader(BufReader::with_capacity(64 * 1024, entry));
xml.config_mut().trim_text(true);
let mut buf = Vec::with_capacity(8 * 1024);
let mut raw_links: Vec<RawLink> = Vec::new();
let mut cur_dv: Option<DvBuilder> = None;
let mut capturing_formula = false;
let mut cur_cf: Option<CfBuilder> = None;
let mut cur_rule: Option<CfRule> = None;
let mut capturing_cf_formula = false;
loop {
match xml.read_event_into(&mut buf) {
Ok(Event::Eof) => break,
Err(e) => return Err(e.into()),
Ok(Event::Start(e)) => match e.local_name().as_ref() {
b"mergeCell" if wants.merges => {
if let Some(r) = attr(&e, b"ref").as_deref().and_then(parse_range) {
meta.merges.push(r);
}
}
b"hyperlink" if wants.hyperlinks => raw_links.push(raw_link(&e)),
b"dataValidation" if wants.validations => cur_dv = Some(DvBuilder::from(&e)),
b"formula1" if cur_dv.is_some() => capturing_formula = true,
b"conditionalFormatting" if wants.conditional_formats => {
cur_cf = Some(CfBuilder::from(&e))
}
b"cfRule" if cur_cf.is_some() => cur_rule = Some(CfRule::from(&e)),
b"formula" if cur_rule.is_some() => {
if let Some(rule) = cur_rule.as_mut() {
rule.formulas.push(String::new());
}
capturing_cf_formula = true;
}
_ => {}
},
Ok(Event::Empty(e)) => match e.local_name().as_ref() {
b"mergeCell" if wants.merges => {
if let Some(r) = attr(&e, b"ref").as_deref().and_then(parse_range) {
meta.merges.push(r);
}
}
b"hyperlink" if wants.hyperlinks => raw_links.push(raw_link(&e)),
b"dataValidation" if wants.validations => {
meta.validations.push(DvBuilder::from(&e).finish())
}
b"cfRule" if cur_cf.is_some() => {
if let Some(cf) = cur_cf.as_mut() {
cf.rules.push(CfRule::from(&e));
}
}
_ => {}
},
Ok(Event::Text(t)) if capturing_formula => {
if let Some(dv) = cur_dv.as_mut() {
dv.formula
.get_or_insert_with(String::new)
.push_str(&String::from_utf8_lossy(t.as_ref()));
}
}
Ok(Event::Text(t)) if capturing_cf_formula => {
if let Some(rule) = cur_rule.as_mut() {
if let Some(last) = rule.formulas.last_mut() {
last.push_str(&String::from_utf8_lossy(t.as_ref()));
}
}
}
Ok(Event::End(e)) => match e.local_name().as_ref() {
b"formula1" => capturing_formula = false,
b"formula" => capturing_cf_formula = false,
b"cfRule" => {
if let (Some(cf), Some(rule)) = (cur_cf.as_mut(), cur_rule.take()) {
cf.rules.push(rule);
}
}
b"conditionalFormatting" => {
if let Some(cf) = cur_cf.take() {
if !cf.rules.is_empty() {
meta.conditional_formats.push(cf.finish());
}
}
}
b"dataValidation" => {
if let Some(dv) = cur_dv.take() {
meta.validations.push(dv.finish());
}
}
_ => {}
},
_ => {}
}
buf.clear();
}
meta.merges.sort_by_key(|r| (r.first_row, r.first_col));
let rels = if raw_links.iter().any(|l| l.rid.is_some()) {
load_sheet_rels(source, sheet_path)
} else {
HashMap::new()
};
meta.hyperlinks = raw_links
.into_iter()
.filter_map(|l| {
let (row, col) = l.cell?;
Some(Hyperlink {
row,
col,
target: l.rid.and_then(|id| rels.get(&id).cloned()),
location: l.location,
display: l.display,
})
})
.collect();
Ok(meta)
}
struct RawLink {
cell: Option<(u32, u16)>,
rid: Option<String>,
location: Option<String>,
display: Option<String>,
}
fn raw_link(e: &BytesStart<'_>) -> RawLink {
RawLink {
cell: attr(e, b"ref")
.as_deref()
.and_then(parse_ref)
.map(|(col, row)| (row, col)),
rid: attr(e, b"r:id").or_else(|| attr(e, b"id")),
location: attr(e, b"location"),
display: attr(e, b"display"),
}
}
struct DvBuilder {
kind: String,
sqref: String,
formula: Option<String>,
prompt: Option<String>,
prompt_title: Option<String>,
allow_blank: bool,
}
impl From<&BytesStart<'_>> for DvBuilder {
fn from(e: &BytesStart<'_>) -> Self {
Self {
kind: attr(e, b"type").unwrap_or_else(|| "none".to_string()),
sqref: attr(e, b"sqref").unwrap_or_default(),
formula: None,
prompt: attr(e, b"prompt"),
prompt_title: attr(e, b"promptTitle"),
allow_blank: attr(e, b"allowBlank").as_deref() == Some("1"),
}
}
}
impl DvBuilder {
fn finish(self) -> DataValidation {
let ranges: Vec<MergeRange> = self
.sqref
.split_whitespace()
.filter_map(parse_range)
.collect();
let items = if self.kind == "list" {
self.formula.as_deref().map(parse_list_items).unwrap_or_default()
} else {
Vec::new()
};
DataValidation {
kind: self.kind,
ranges,
formula: self.formula,
items,
prompt: self.prompt,
prompt_title: self.prompt_title,
allow_blank: self.allow_blank,
}
}
}
struct CfBuilder {
sqref: String,
rules: Vec<CfRule>,
}
impl From<&BytesStart<'_>> for CfBuilder {
fn from(e: &BytesStart<'_>) -> Self {
Self {
sqref: attr(e, b"sqref").unwrap_or_default(),
rules: Vec::new(),
}
}
}
impl CfBuilder {
fn finish(self) -> ConditionalFormat {
ConditionalFormat {
ranges: self.sqref.split_whitespace().filter_map(parse_range).collect(),
rules: self.rules,
}
}
}
impl From<&BytesStart<'_>> for CfRule {
fn from(e: &BytesStart<'_>) -> Self {
Self {
kind: attr(e, b"type").unwrap_or_else(|| "expression".to_string()),
operator: attr(e, b"operator"),
formulas: Vec::new(),
dxf_id: attr(e, b"dxfId").and_then(|v| v.parse().ok()),
priority: attr(e, b"priority").and_then(|v| v.parse().ok()),
}
}
}
fn parse_list_items(formula: &str) -> Vec<String> {
let t = formula.trim();
let t = t
.strip_prefix('"')
.and_then(|x| x.strip_suffix('"'))
.unwrap_or(t);
t.split(',')
.map(|s| s.trim().to_string())
.filter(|s| !s.is_empty())
.collect()
}
pub(crate) fn load_comments(source: &ZipSource, sheet_path: &str) -> Result<Vec<Comment>> {
let rels = load_sheet_rels(source, sheet_path);
let mut out = Vec::new();
let persons = load_persons(source);
for target in rels.values() {
if target.contains("threadedComment") {
let path = normalize_path(&format!("xl/worksheets/{target}"));
if let Ok(xml) = source.read_entry(&path) {
out.extend(parse_threaded_comments(&xml, &persons));
}
} else if target.contains("comments") {
let path = normalize_path(&format!("xl/worksheets/{target}"));
if let Ok(xml) = source.read_entry(&path) {
out.extend(parse_comments(&xml)?);
}
}
}
out.sort_by_key(|c| (c.row, c.col));
Ok(out)
}
fn load_persons(source: &ZipSource) -> HashMap<String, String> {
let Ok(rels_xml) = source.read_entry("xl/_rels/workbook.xml.rels") else {
return HashMap::new();
};
let rels = parse_rels(&rels_xml);
let Some(target) = rels.values().find(|t| t.contains("person")) else {
return HashMap::new();
};
let path = normalize_path(&format!("xl/{target}"));
let Ok(xml) = source.read_entry(&path) else {
return HashMap::new();
};
let mut reader = Reader::from_reader(xml.as_slice());
reader.config_mut().trim_text(true);
let mut buf = Vec::with_capacity(4 * 1024);
let mut out = HashMap::new();
loop {
match reader.read_event_into(&mut buf) {
Ok(Event::Eof) => break,
Ok(Event::Start(e)) | Ok(Event::Empty(e))
if e.local_name().as_ref() == b"person" =>
{
if let (Some(id), Some(name)) = (attr(&e, b"id"), attr(&e, b"displayName")) {
out.insert(id, name);
}
}
_ => {}
}
buf.clear();
}
out
}
fn parse_threaded_comments(xml: &[u8], persons: &HashMap<String, String>) -> Vec<Comment> {
let mut reader = Reader::from_reader(xml);
reader.config_mut().trim_text(true);
let mut buf = Vec::with_capacity(8 * 1024);
let mut in_comment = false;
let mut in_text = false;
let mut cell: Option<(u32, u16)> = None;
let mut person: Option<String> = None;
let mut id: Option<String> = None;
let mut parent_id: Option<String> = None;
let mut text = String::new();
let mut out: Vec<Comment> = Vec::new();
let mut by_id: HashMap<String, usize> = HashMap::new();
loop {
match reader.read_event_into(&mut buf) {
Ok(Event::Eof) => break,
Ok(Event::Start(e)) => match e.local_name().as_ref() {
b"threadedComment" => {
in_comment = true;
cell = attr(&e, b"ref")
.as_deref()
.and_then(parse_ref)
.map(|(c, r)| (r, c));
person = attr(&e, b"personId");
id = attr(&e, b"id");
parent_id = attr(&e, b"parentId");
text.clear();
}
b"text" if in_comment => in_text = true,
_ => {}
},
Ok(Event::Text(t)) if in_text => {
text.push_str(&String::from_utf8_lossy(t.as_ref()));
}
Ok(Event::End(e)) => match e.local_name().as_ref() {
b"text" => in_text = false,
b"threadedComment" => {
in_comment = false;
let author = person.as_ref().and_then(|p| persons.get(p).cloned());
let body = text.trim().to_string();
match parent_id.as_ref().and_then(|p| by_id.get(p).copied()) {
Some(idx) => {
let who = author.as_deref().unwrap_or("回复");
out[idx].text.push_str(&format!("\n— {who}:{body}"));
}
None => {
if let Some((row, col)) = cell {
let idx = out.len();
if let Some(root_id) = id.clone() {
by_id.insert(root_id, idx);
}
out.push(Comment {
row,
col,
text: body,
author,
});
}
}
}
}
_ => {}
},
_ => {}
}
buf.clear();
}
out
}
fn parse_comments(xml: &[u8]) -> Result<Vec<Comment>> {
let mut reader = Reader::from_reader(xml);
reader.config_mut().trim_text(false);
let mut buf = Vec::with_capacity(8 * 1024);
let mut authors: Vec<String> = Vec::new();
let mut in_authors = false;
let mut in_author = false;
let mut in_comment = false;
let mut in_text = false;
let mut cur_cell: Option<(u32, u16)> = None;
let mut cur_author: Option<String> = None;
let mut cur_text = String::new();
let mut out = Vec::new();
loop {
match reader.read_event_into(&mut buf) {
Ok(Event::Eof) => break,
Err(e) => return Err(e.into()),
Ok(Event::Start(e)) => match e.local_name().as_ref() {
b"authors" => in_authors = true,
b"author" if in_authors => in_author = true,
b"comment" => {
in_comment = true;
cur_cell = attr(&e, b"ref").as_deref().and_then(parse_ref).map(|(c, r)| (r, c));
let id = attr(&e, b"authorId").and_then(|v| v.parse::<usize>().ok());
cur_author = id.and_then(|i| authors.get(i).cloned());
cur_text.clear();
}
b"text" if in_comment => in_text = true,
_ => {}
},
Ok(Event::Text(t)) => {
if in_author {
authors.push(String::from_utf8_lossy(t.as_ref()).into_owned());
} else if in_comment && in_text {
cur_text.push_str(&String::from_utf8_lossy(t.as_ref()));
}
}
Ok(Event::End(e)) => match e.local_name().as_ref() {
b"authors" => in_authors = false,
b"author" => in_author = false,
b"text" => in_text = false,
b"comment" => {
in_comment = false;
if let Some((row, col)) = cur_cell {
out.push(Comment {
row,
col,
text: cur_text.trim().to_string(),
author: cur_author.clone(),
});
}
}
_ => {}
},
_ => {}
}
buf.clear();
}
Ok(out)
}
fn load_sheet_rels(source: &ZipSource, sheet_path: &str) -> HashMap<String, String> {
let rels_path = format!("xl/worksheets/_rels/{}.rels", file_name(sheet_path));
source
.read_entry(&rels_path)
.map(|x| parse_rels(&x))
.unwrap_or_default()
}
fn attr(e: &BytesStart<'_>, name: &[u8]) -> Option<String> {
e.attributes()
.flatten()
.find(|a| a.key.as_ref() == name)
.map(|a| String::from_utf8_lossy(&a.value).into_owned())
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn test_parse_comments() {
let xml = r#"<?xml version="1.0"?>
<comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<authors><author>张三</author></authors>
<commentList>
<comment ref="B2" authorId="0"><text><r><t>请核对数量</t></r></text></comment>
<comment ref="C3"><text><t>无作者批注</t></text></comment>
</commentList>
</comments>"#;
let list = parse_comments(xml.as_bytes()).unwrap();
assert_eq!(list.len(), 2);
assert_eq!(list[0].row, 2);
assert_eq!(list[0].col, 1);
assert_eq!(list[0].text, "请核对数量");
assert_eq!(list[0].author.as_deref(), Some("张三"));
assert_eq!(list[1].text, "无作者批注");
assert_eq!(list[1].author, None);
}
#[test]
fn test_parse_threaded_comments_with_reply() {
let xml = r#"<?xml version="1.0"?>
<ThreadedComments xmlns="http://schemas.microsoft.com/office/spreadsheetml/2018/threadedcomments">
<threadedComment ref="B2" personId="{P1}" id="{C1}" date="2025-01-01T00:00:00Z"><text>请核对数量</text></threadedComment>
<threadedComment ref="C3" personId="{P2}" id="{C2}"><text>已确认</text></threadedComment>
<threadedComment parentId="{C1}" personId="{P2}" id="{C1R}"><text>已核对</text></threadedComment>
</ThreadedComments>"#;
let mut persons = HashMap::new();
persons.insert("{P1}".to_string(), "张三".to_string());
persons.insert("{P2}".to_string(), "李四".to_string());
let list = parse_threaded_comments(xml.as_bytes(), &persons);
assert_eq!(list.len(), 2, "回复不单独成条");
assert_eq!((list[0].row, list[0].col), (2, 1));
assert_eq!(list[0].author.as_deref(), Some("张三"));
assert!(list[0].text.contains("请核对数量"));
assert!(list[0].text.contains("已核对"), "回复应并入父批注:{}", list[0].text);
assert!(list[0].text.contains("李四"), "回复应带作者:{}", list[0].text);
assert_eq!(list[1].text, "已确认");
}
}