excel_add_sheet 0.1.0

//! Library for adding worksheets to existing Excel (XLSX) files.

use std::collections::HashMap;
use std::fs::File;
use std::io::{Read, Write};
use quick_xml::events::BytesText;

/// Represents an Excel document that can be modified.
pub struct ExcelDoc {
    files: HashMap<String, Vec<u8>>, // Map of file path in archive to file contents
}

impl ExcelDoc {
    /// Opens an existing XLSX file for modification.
    pub fn open<P: AsRef<std::path::Path>>(path: P) -> Result<Self, Box<dyn std::error::Error>> {
        let file = File::open(path)?;
        let mut archive = zip::ZipArchive::new(file)?;
        let mut files = HashMap::new();
        
        for i in 0..archive.len() {
            let mut file = archive.by_index(i)?;
            let mut contents = Vec::new();
            file.read_to_end(&mut contents)?;
            files.insert(file.name().to_string(), contents);
        }
        
        Ok(ExcelDoc { files })
    }

    /// Adds a worksheet with the given name and optional data.
    /// `data` is a 2D vector of strings representing rows and columns.
    pub fn add_worksheet<S: AsRef<str>>(
        &mut self,
        name: S,
        data: Option<Vec<Vec<String>>>,
    ) -> Result<(), Box<dyn std::error::Error>> {
        let (sheet_path, sheet_num) = self.next_worksheet_path();
        let sheet_name = name.as_ref();
        let sheet_xml = Self::generate_worksheet_xml(&data);
        let new_rid = self.next_relationship_id();

        // 1. Add the new worksheet file
        self.files.insert(sheet_path.clone(), sheet_xml);

        // 2. Update xl/workbook.xml
        self.update_workbook_xml_with_rid(sheet_name, sheet_num, &new_rid)?;

        // 3. Update xl/_rels/workbook.xml.rels
        self.update_workbook_rels_with_rid(sheet_num, &new_rid)?;

        // 4. Update [Content_Types].xml
        self.update_content_types(sheet_num)?;

        Ok(())
    }

    /// Adds a new blank worksheet to the loaded Excel file, updating all necessary XML files.
    pub fn add_blank_worksheet(&mut self, sheet_name: &str) -> Result<(), Box<dyn std::error::Error>> {
        // 1. Find the next available worksheet number
        let (sheet_path, sheet_num) = self.next_worksheet_path();
        let r_id = format!("rId{}", sheet_num);
        let sheet_id = sheet_num;

        // 2. Generate minimal worksheet XML
        let worksheet_xml = br#"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <sheetData/>
</worksheet>"#.to_vec();
        
        self.files.insert(sheet_path.clone(), worksheet_xml);

        // 3. Update xl/workbook.xml
        self.update_workbook_xml_blank(sheet_name, sheet_id, &r_id)?;

        // 4. Update xl/_rels/workbook.xml.rels
        self.update_workbook_rels_blank(sheet_num, &r_id)?;

        // 5. Update [Content_Types].xml
        self.update_content_types_blank(sheet_num)?;

        Ok(())
    }

    /// Saves the modified document to the given path.
    pub fn save<P: AsRef<std::path::Path>>(&self, path: P) -> Result<(), Box<dyn std::error::Error>> {
        let file = File::create(path)?;
        let mut zip = zip::ZipWriter::new(file);
        let options = zip::write::FileOptions::default().compression_method(zip::CompressionMethod::Stored);
        
        for (name, data) in &self.files {
            zip.start_file(name, options)?;
            zip.write_all(data)?;
        }
        zip.finish()?;
        Ok(())
    }

    /// Loads an XLSX file and prints the list of files in the archive.
    pub fn print_file_list<P: AsRef<std::path::Path>>(path: P) -> Result<(), Box<dyn std::error::Error>> {
        let file = File::open(path)?;
        let mut archive = zip::ZipArchive::new(file)?;
        println!("Files in XLSX archive:");
        for i in 0..archive.len() {
            let file = archive.by_index(i)?;
            println!("- {}", file.name());
        }
        Ok(())
    }

    /// Helper to find the next available worksheet number and path (e.g., xl/worksheets/sheet3.xml)
    fn next_worksheet_path(&self) -> (String, usize) {
        let mut max_num = 0;
        let prefix = "xl/worksheets/sheet";
        let suffix = ".xml";
        
        for key in self.files.keys() {
            if key.starts_with(prefix) && key.ends_with(suffix) {
                let num_part = &key[prefix.len()..key.len() - suffix.len()];
                if let Ok(num) = num_part.parse::<usize>() {
                    if num > max_num {
                        max_num = num;
                    }
                }
            }
        }
        
        let next_num = max_num + 1;
        (format!("{}{}{}", prefix, next_num, suffix), next_num)
    }

    /// Helper to find the next available unique rId in workbook.xml.rels
    fn next_relationship_id(&self) -> String {
        let rels_path = "xl/_rels/workbook.xml.rels";
        if let Some(rels_xml) = self.files.get(rels_path) {
            let mut reader = quick_xml::Reader::from_reader(&rels_xml[..]);
            reader.trim_text(true);
            let mut buf: Vec<u8> = Vec::new();
            let mut max_id = 0;
            loop {
                use quick_xml::events::Event;
                buf.clear();
                match reader.read_event_into(&mut buf) {
                    Ok(Event::Empty(ref e)) if e.name().as_ref() == b"Relationship" => {
                        for attr in e.attributes().flatten() {
                            if attr.key.as_ref() == b"Id" {
                                let val = String::from_utf8_lossy(&attr.value);
                                if let Some(num) = val.strip_prefix("rId").and_then(|s| s.parse::<u32>().ok()) {
                                    if num > max_id {
                                        max_id = num;
                                    }
                                }
                            }
                        }
                    }
                    Ok(Event::Eof) => break,
                    Ok(_) => {}
                    Err(_) => break,
                }
            }
            format!("rId{}", max_id + 1)
        } else {
            "rId1".to_string()
        }
    }

    /// Generate minimal worksheet XML with optional data
    fn generate_worksheet_xml(data: &Option<Vec<Vec<String>>>) -> Vec<u8> {
        let mut xml = Vec::new();
        xml.extend_from_slice(br#"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <sheetData>
"#);
        
        if let Some(rows) = data {
            for (r, row) in rows.iter().enumerate() {
                xml.extend_from_slice(format!("    <row r=\"{}\">\n", r + 1).as_bytes());
                for (c, cell) in row.iter().enumerate() {
                    let col = (b'A' + (c as u8)) as char;
                    xml.extend_from_slice(format!("      <c r=\"{}{}\" t=\"str\"><v>{}</v></c>\n", col, r + 1, cell).as_bytes());
                }
                xml.extend_from_slice(b"    </row>\n");
            }
        }
        
        xml.extend_from_slice(b"  </sheetData>\n</worksheet>");
        xml
    }

    /// Update workbook.xml with new worksheet and custom rId
    fn update_workbook_xml_with_rid(&mut self, sheet_name: &str, sheet_num: usize, r_id: &str) -> Result<(), Box<dyn std::error::Error>> {
        let wb_path = "xl/workbook.xml";
        let wb_xml = self.files.get(wb_path).ok_or("workbook.xml not found")?.clone();
        
        let mut reader = quick_xml::Reader::from_reader(&wb_xml[..]);
        reader.trim_text(true);
        let mut writer = quick_xml::Writer::new(Vec::new());
        let mut buf: Vec<u8> = Vec::new();
        let mut in_sheets = false;
        let mut pending_sheets = Vec::new();
        let mut sheet_inserted = false;
        
        loop {
            use quick_xml::events::{Event, BytesStart};
            buf.clear();
            match reader.read_event_into(&mut buf) {
                Ok(Event::Start(ref e)) if e.name().as_ref() == b"sheets" => {
                    in_sheets = true;
                    writer.write_event(Event::Start(e.clone()))?;
                }
                Ok(Event::Empty(ref e)) if in_sheets && e.name().as_ref() == b"sheet" => {
                    // Buffer <sheet> events to insert after the last one
                    pending_sheets.push(Event::Empty(e.to_owned()));
                }
                Ok(Event::End(ref e)) if in_sheets && e.name().as_ref() == b"sheets" => {
                    // Write all buffered <sheet> events
                    for ev in &pending_sheets {
                        writer.write_event(ev.clone())?;
                    }
                    // Insert new <sheet> after the last existing <sheet>
                    let mut sheet_elem = BytesStart::new("sheet");
                    sheet_elem.push_attribute(("name", sheet_name));
                    sheet_elem.push_attribute(("sheetId", sheet_num.to_string().as_str()));
                    sheet_elem.push_attribute(("r:id", r_id));
                    writer.write_event(Event::Empty(sheet_elem))?;
                    writer.write_event(Event::End(e.clone()))?;
                    in_sheets = false;
                    sheet_inserted = true;
                    pending_sheets.clear();
                }
                Ok(Event::Eof) => break,
                Ok(e) => {
                    if in_sheets && matches!(e, Event::Start(_) | Event::End(_)) && !matches!(e, Event::End(ref end) if end.name().as_ref() == b"sheets") {
                        // Write any non-<sheet> nodes inside <sheets> immediately
                        writer.write_event(e.clone())?;
                    } else if in_sheets && matches!(e, Event::Text(_)) {
                        // Write text nodes inside <sheets> (shouldn't be present, but just in case)
                        writer.write_event(e.clone())?;
                    } else if !in_sheets {
                        writer.write_event(e.clone())?;
                    }
                }
                Err(e) => return Err(Box::new(e)),
            }
        }
        
        // Fallback: if <sheets> was not found, use the original
        if !sheet_inserted {
            self.files.insert(wb_path.to_string(), wb_xml);
        } else {
            self.files.insert(wb_path.to_string(), writer.into_inner());
        }
        
        Ok(())
    }

    /// Update workbook.xml for blank worksheet
    fn update_workbook_xml_blank(&mut self, sheet_name: &str, sheet_id: usize, r_id: &str) -> Result<(), Box<dyn std::error::Error>> {
        let wb_path = "xl/workbook.xml";
        let wb_xml = self.files.get(wb_path).ok_or("workbook.xml not found")?.clone();
        
        let mut reader = quick_xml::Reader::from_reader(&wb_xml[..]);
        reader.trim_text(true);
        let mut writer = quick_xml::Writer::new(Vec::new());
        let mut buf: Vec<u8> = Vec::new();
        let mut in_sheets = false;
        let mut sheet_inserted = false;
        let mut pending_events = Vec::new();
        
        loop {
            use quick_xml::events::{Event, BytesStart};
            buf.clear();
            match reader.read_event_into(&mut buf) {
                Ok(Event::Start(ref e)) if e.name().as_ref() == b"sheets" => {
                    in_sheets = true;
                    writer.write_event(Event::Start(e.clone()))?;
                }
                Ok(Event::Empty(ref e)) if in_sheets && e.name().as_ref() == b"sheet" => {
                    // Buffer <sheet> events to insert after the last one
                    pending_events.push(Event::Empty(e.to_owned()));
                }
                Ok(Event::End(ref e)) if in_sheets && e.name().as_ref() == b"sheets" => {
                    // Write all buffered <sheet> events
                    for ev in &pending_events {
                        writer.write_event(ev.clone())?;
                    }
                    // Insert new <sheet> after the last existing <sheet>
                    let mut sheet_elem = BytesStart::new("sheet");
                    sheet_elem.push_attribute(("name", sheet_name));
                    sheet_elem.push_attribute(("sheetId", sheet_id.to_string().as_str()));
                    sheet_elem.push_attribute(("r:id", r_id));
                    writer.write_event(Event::Empty(sheet_elem))?;
                    writer.write_event(Event::End(e.clone()))?;
                    in_sheets = false;
                    sheet_inserted = true;
                    pending_events.clear();
                }
                Ok(Event::Eof) => break,
                Ok(e) => {
                    if in_sheets && matches!(e, Event::Start(_) | Event::End(_)) && !matches!(e, Event::End(ref end) if end.name().as_ref() == b"sheets") {
                        // Write any non-<sheet> nodes inside <sheets> immediately
                        writer.write_event(e.clone())?;
                    } else if in_sheets && matches!(e, Event::Text(_)) {
                        // Write text nodes inside <sheets> (shouldn't be present, but just in case)
                        writer.write_event(e.clone())?;
                    } else if !in_sheets {
                        writer.write_event(e.clone())?;
                    }
                }
                Err(e) => return Err(Box::new(e)),
            }
        }
        
        // Fallback: if <sheets> was not found, use the original
        if !sheet_inserted {
            self.files.insert(wb_path.to_string(), wb_xml);
        } else {
            self.files.insert(wb_path.to_string(), writer.into_inner());
        }
        
        Ok(())
    }

    /// Update workbook.xml.rels
    fn update_workbook_rels(&mut self, sheet_num: usize) -> Result<(), Box<dyn std::error::Error>> {
        let rels_path = "xl/_rels/workbook.xml.rels";
        let rels_xml = self.files.get(rels_path).ok_or("workbook.xml.rels not found")?.clone();
        
        let mut reader = quick_xml::Reader::from_reader(&rels_xml[..]);
        reader.trim_text(true);
        let mut writer = quick_xml::Writer::new(Vec::new());
        let mut buf: Vec<u8> = Vec::new();
        let mut inserted = false;
        
        loop {
            use quick_xml::events::{Event, BytesStart};
            buf.clear();
            match reader.read_event_into(&mut buf) {
                Ok(Event::End(ref e)) if e.name().as_ref() == b"Relationships" && !inserted => {
                    // Insert new <Relationship> before closing
                    let mut rel_elem = BytesStart::new("Relationship");
                    rel_elem.push_attribute(("Id", format!("rId{}", sheet_num).as_str()));
                    rel_elem.push_attribute(("Type", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"));
                    rel_elem.push_attribute(("Target", format!("worksheets/sheet{}.xml", sheet_num).as_str()));
                    writer.write_event(Event::Empty(rel_elem))?;
                    writer.write_event(Event::End(e.clone()))?;
                    inserted = true;
                }
                Ok(Event::Eof) => break,
                Ok(e) => {
                    writer.write_event(e)?;
                }
                Err(e) => return Err(Box::new(e)),
            }
        }
        
        self.files.insert(rels_path.to_string(), writer.into_inner());
        Ok(())
    }

    /// Update workbook.xml.rels with custom rId
    fn update_workbook_rels_with_rid(&mut self, sheet_num: usize, r_id: &str) -> Result<(), Box<dyn std::error::Error>> {
        let rels_path = "xl/_rels/workbook.xml.rels";
        let rels_xml = self.files.get(rels_path).ok_or("workbook.xml.rels not found")?.clone();
        
        let mut reader = quick_xml::Reader::from_reader(&rels_xml[..]);
        reader.trim_text(true);
        let mut writer = quick_xml::Writer::new(Vec::new());
        let mut buf: Vec<u8> = Vec::new();
        let mut inserted = false;
        
        loop {
            use quick_xml::events::{Event, BytesStart};
            buf.clear();
            match reader.read_event_into(&mut buf) {
                Ok(Event::End(ref e)) if e.name().as_ref() == b"Relationships" && !inserted => {
                    // Insert new <Relationship> before closing
                    let mut rel_elem = BytesStart::new("Relationship");
                    rel_elem.push_attribute(("Id", r_id));
                    rel_elem.push_attribute(("Type", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"));
                    rel_elem.push_attribute(("Target", format!("worksheets/sheet{}.xml", sheet_num).as_str()));
                    writer.write_event(Event::Empty(rel_elem))?;
                    writer.write_event(Event::End(e.clone()))?;
                    inserted = true;
                }
                Ok(Event::Eof) => break,
                Ok(e) => {
                    writer.write_event(e)?;
                }
                Err(e) => return Err(Box::new(e)),
            }
        }
        
        self.files.insert(rels_path.to_string(), writer.into_inner());
        Ok(())
    }

    /// Update workbook.xml.rels for blank worksheet
    fn update_workbook_rels_blank(&mut self, sheet_num: usize, r_id: &str) -> Result<(), Box<dyn std::error::Error>> {
        let rels_path = "xl/_rels/workbook.xml.rels";
        let rels_xml = self.files.get(rels_path).ok_or("workbook.xml.rels not found")?.clone();
        
        let mut reader = quick_xml::Reader::from_reader(&rels_xml[..]);
        reader.trim_text(true);
        let mut writer = quick_xml::Writer::new(Vec::new());
        let mut buf: Vec<u8> = Vec::new();
        let mut inserted = false;
        
        loop {
            use quick_xml::events::{Event, BytesStart};
            buf.clear();
            match reader.read_event_into(&mut buf) {
                Ok(Event::End(ref e)) if e.name().as_ref() == b"Relationships" && !inserted => {
                    // Insert new <Relationship> before closing
                    let mut rel_elem = BytesStart::new("Relationship");
                    rel_elem.push_attribute(("Id", r_id));
                    rel_elem.push_attribute(("Type", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"));
                    rel_elem.push_attribute(("Target", format!("worksheets/sheet{}.xml", sheet_num).as_str()));
                    writer.write_event(Event::Empty(rel_elem))?;
                    writer.write_event(Event::End(e.clone()))?;
                    inserted = true;
                }
                Ok(Event::Eof) => break,
                Ok(e) => {
                    writer.write_event(e)?;
                }
                Err(e) => return Err(Box::new(e)),
            }
        }
        
        self.files.insert(rels_path.to_string(), writer.into_inner());
        Ok(())
    }

    /// Update [Content_Types].xml
    fn update_content_types(&mut self, sheet_num: usize) -> Result<(), Box<dyn std::error::Error>> {
        let ct_path = "[Content_Types].xml";
        let ct_xml = self.files.get(ct_path).ok_or("[Content_Types].xml not found")?.clone();
        
        let mut reader = quick_xml::Reader::from_reader(&ct_xml[..]);
        reader.trim_text(true);
        let mut writer = quick_xml::Writer::new(Vec::new());
        let mut buf: Vec<u8> = Vec::new();
        let mut inserted = false;
        
        loop {
            use quick_xml::events::{Event, BytesStart};
            buf.clear();
            match reader.read_event_into(&mut buf) {
                Ok(Event::End(ref e)) if e.name().as_ref() == b"Types" && !inserted => {
                    // Insert new <Override>
                    let mut override_elem = BytesStart::new("Override");
                    override_elem.push_attribute(("PartName", format!("/xl/worksheets/sheet{}.xml", sheet_num).as_str()));
                    override_elem.push_attribute(("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"));
                    writer.write_event(Event::Empty(override_elem))?;
                    writer.write_event(Event::End(e.clone()))?;
                    inserted = true;
                }
                Ok(Event::Eof) => break,
                Ok(e) => {
                    writer.write_event(e)?;
                }
                Err(e) => return Err(Box::new(e)),
            }
        }
        
        self.files.insert(ct_path.to_string(), writer.into_inner());
        Ok(())
    }

    /// Update [Content_Types].xml for blank worksheet
    fn update_content_types_blank(&mut self, sheet_num: usize) -> Result<(), Box<dyn std::error::Error>> {
        let ct_path = "[Content_Types].xml";
        let ct_xml = self.files.get(ct_path).ok_or("[Content_Types].xml not found")?.clone();
        
        let mut reader = quick_xml::Reader::from_reader(&ct_xml[..]);
        reader.trim_text(true);
        let mut writer = quick_xml::Writer::new(Vec::new());
        let mut buf: Vec<u8> = Vec::new();
        let mut inserted = false;
        
        loop {
            use quick_xml::events::{Event, BytesStart};
            buf.clear();
            match reader.read_event_into(&mut buf) {
                Ok(Event::End(ref e)) if e.name().as_ref() == b"Types" && !inserted => {
                    // Insert new <Override>
                    let mut override_elem = BytesStart::new("Override");
                    override_elem.push_attribute(("PartName", format!("/xl/worksheets/sheet{}.xml", sheet_num).as_str()));
                    override_elem.push_attribute(("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"));
                    writer.write_event(Event::Empty(override_elem))?;
                    writer.write_event(Event::End(e.clone()))?;
                    inserted = true;
                }
                Ok(Event::Eof) => break,
                Ok(e) => {
                    writer.write_event(e)?;
                }
                Err(e) => return Err(Box::new(e)),
            }
        }
        
        self.files.insert(ct_path.to_string(), writer.into_inner());
        Ok(())
    }
}

// --- In-memory Excel object model ---

pub struct ExcelWorkbook {
    pub sheets: Vec<Worksheet>,
}

pub struct Worksheet {
    pub name: String,
    pub sheet_id: u32,
    pub cells: Vec<Cell>,
}

impl Worksheet {
    /// Set the value of a cell (row, col). Updates if exists, inserts if not.
    pub fn set_cell_value(&mut self, row: u32, col: u32, value: String) {
        if let Some(cell) = self.cells.iter_mut().find(|c| c.row == row && c.col == col) {
            cell.value = value;
        } else {
            self.cells.push(Cell { row, col, value });
        }
    }

    /// Serialize the worksheet to XML (minimal, string values only)
    pub fn to_xml(&self) -> Vec<u8> {
        use std::fmt::Write;
        let mut xml = String::new();
        xml.push_str(r#"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <sheetData>
"#);
        // Group cells by row
        let mut rows: std::collections::BTreeMap<u32, Vec<&Cell>> = std::collections::BTreeMap::new();
        for cell in &self.cells {
            rows.entry(cell.row).or_default().push(cell);
        }
        for (row_num, cells) in rows {
            let _ = write!(xml, "    <row r=\"{}\">\n", row_num);
            for cell in cells {
                let col_ref = col_to_excel(cell.col);
                let _ = write!(xml, "      <c r=\"{}{}\" t=\"str\"><v>{}</v></c>\n", col_ref, cell.row, cell.value);
            }
            xml.push_str("    </row>\n");
        }
        xml.push_str("  </sheetData>\n</worksheet>");
        xml.into_bytes()
    }
}

pub struct Cell {
    pub row: u32,
    pub col: u32,
    pub value: String,
}

impl ExcelWorkbook {
    /// Open an XLSX file and parse the workbook and sheet names
    pub fn open<P: AsRef<std::path::Path>>(path: P) -> Result<Self, Box<dyn std::error::Error>> {
        let file = File::open(path)?;
        let mut archive = zip::ZipArchive::new(file)?;
        
        // Read workbook.xml
        let mut wb_xml = Vec::new();
        archive.by_name("xl/workbook.xml")?.read_to_end(&mut wb_xml)?;
        
        let mut reader = quick_xml::Reader::from_reader(&wb_xml[..]);
        reader.trim_text(true);
        let mut buf: Vec<u8> = Vec::new();
        let mut sheets = Vec::new();
        let mut in_sheets = false;
        
        // Store mapping of r:id to sheet file path
        let mut sheet_id_to_rid = Vec::new();
        let mut rid_to_sheet_path = std::collections::HashMap::new();
        
        // First pass: parse sheet names, ids, and r:ids
        loop {
            buf.clear();
            use quick_xml::events::Event;
            match reader.read_event_into(&mut buf) {
                Ok(Event::Start(ref e)) if e.name().as_ref() == b"sheets" => {
                    in_sheets = true;
                }
                Ok(Event::End(ref e)) if e.name().as_ref() == b"sheets" => {
                    in_sheets = false;
                }
                Ok(Event::Empty(ref e)) if in_sheets && e.name().as_ref() == b"sheet" => {
                    let mut name = String::new();
                    let mut sheet_id = 0;
                    let mut r_id = String::new();
                    
                    for attr in e.attributes().flatten() {
                        match attr.key.as_ref() {
                            b"name" => name = String::from_utf8_lossy(&attr.value).to_string(),
                            b"sheetId" => sheet_id = String::from_utf8_lossy(&attr.value).parse().unwrap_or(0),
                            b"r:id" => r_id = String::from_utf8_lossy(&attr.value).to_string(),
                            _ => {}
                        }
                    }
                    
                    sheet_id_to_rid.push((sheet_id, r_id.clone()));
                    sheets.push(Worksheet { name, sheet_id, cells: Vec::new() });
                }
                Ok(Event::Eof) => break,
                Ok(_) => {},
                Err(e) => return Err(Box::new(e)),
            }
        }
        
        // Parse workbook.xml.rels to map r:id to worksheet file path
        let mut rels_xml = Vec::new();
        archive.by_name("xl/_rels/workbook.xml.rels")?.read_to_end(&mut rels_xml)?;
        
        let mut rels_reader = quick_xml::Reader::from_reader(&rels_xml[..]);
        rels_reader.trim_text(true);
        let mut buf: Vec<u8> = Vec::new();
        
        loop {
            buf.clear();
            use quick_xml::events::Event;
            match rels_reader.read_event_into(&mut buf) {
                Ok(Event::Empty(ref e)) if e.name().as_ref() == b"Relationship" => {
                    let mut id = String::new();
                    let mut target = String::new();
                    let mut typ = String::new();
                    
                    for attr in e.attributes().flatten() {
                        match attr.key.as_ref() {
                            b"Id" => id = String::from_utf8_lossy(&attr.value).to_string(),
                            b"Target" => target = String::from_utf8_lossy(&attr.value).to_string(),
                            b"Type" => typ = String::from_utf8_lossy(&attr.value).to_string(),
                            _ => {}
                        }
                    }
                    
                    if typ.ends_with("/worksheet") {
                        rid_to_sheet_path.insert(id, format!("xl/{}", target));
                    }
                }
                Ok(Event::Eof) => break,
                Ok(_) => {},
                Err(e) => return Err(Box::new(e)),
            }
        }
        
        // For each sheet, parse its XML and fill cells
        for (i, (_sheet_id, r_id)) in sheet_id_to_rid.iter().enumerate() {
            if let Some(path) = rid_to_sheet_path.get(r_id) {
                if let Ok(mut sheet_xml) = archive.by_name(path) {
                    let mut xml = Vec::new();
                    sheet_xml.read_to_end(&mut xml)?;
                    
                    let mut reader = quick_xml::Reader::from_reader(&xml[..]);
                    reader.trim_text(true);
                    let mut buf: Vec<u8> = Vec::new();
                    let mut in_sheet_data = false;
                    let mut current_row = 0;
                    
                    loop {
                        buf.clear();
                        use quick_xml::events::Event;
                        match reader.read_event_into(&mut buf) {
                            Ok(Event::Start(ref e)) if e.name().as_ref() == b"sheetData" => {
                                in_sheet_data = true;
                            }
                            Ok(Event::End(ref e)) if e.name().as_ref() == b"sheetData" => {
                                in_sheet_data = false;
                            }
                            Ok(Event::Start(ref e)) if in_sheet_data && e.name().as_ref() == b"row" => {
                                for attr in e.attributes().flatten() {
                                    if attr.key.as_ref() == b"r" {
                                        current_row = String::from_utf8_lossy(&attr.value).parse().unwrap_or(0);
                                    }
                                }
                            }
                            Ok(Event::Empty(ref e)) if in_sheet_data && e.name().as_ref() == b"c" => {
                                let mut col = 0;
                                let mut cell_ref = String::new();
                                for attr in e.attributes().flatten() {
                                    if attr.key.as_ref() == b"r" {
                                        cell_ref = String::from_utf8_lossy(&attr.value).to_string();
                                    }
                                }
                                // Parse cell reference (e.g., "A1") to col/row
                                if !cell_ref.is_empty() {
                                    let (c, _r) = parse_cell_ref(&cell_ref);
                                    col = c;
                                }
                                // For non-empty cells, read value
                                let mut value = String::new();
                                let mut cell_buf: Vec<u8> = Vec::new();
                                loop {
                                    cell_buf.clear();
                                    match reader.read_event_into(&mut cell_buf) {
                                        Ok(Event::Start(ref e)) if e.name().as_ref() == b"v" => {
                                            if let Ok(Event::Text(t)) = reader.read_event_into(&mut cell_buf) {
                                                value = t.unescape().unwrap_or_default().to_string();
                                            }
                                        }
                                        Ok(Event::End(ref e)) if e.name().as_ref() == b"c" => break,
                                        Ok(Event::Eof) => break,
                                        _ => {}
                                    }
                                }
                                if let Some(sheet) = sheets.get_mut(i) {
                                    sheet.cells.push(Cell { row: current_row, col, value });
                                }
                            }
                            Err(e) => return Err(Box::new(e)),
                            Ok(Event::Eof) => break,
                            _ => {}
                        }
                    }
                }
            }
        }
        Ok(ExcelWorkbook { sheets })
    }

    /// Print the names and indices of all sheets in the workbook
    pub fn list_sheets(&self) {
        println!("Sheets in workbook:");
        for (i, sheet) in self.sheets.iter().enumerate() {
            println!("  {}: {} (sheet_id: {})", i, sheet.name, sheet.sheet_id);
        }
    }
}

// --- Helper functions ---

/// Parses a cell reference (e.g., "A1") into column and row numbers.
/// Returns (column_number, row_number).
fn parse_cell_ref(cell_ref: &str) -> (u32, u32) {
    let mut col_str = String::new();
    let mut row_str = String::new();
    let mut is_col = true;

    for c in cell_ref.chars() {
        if c.is_digit(10) {
            is_col = false;
            row_str.push(c);
        } else {
            col_str.push(c);
        }
    }

    let col_num = col_str.chars().fold(0, |sum, c| sum * 26 + (c.to_ascii_uppercase() as u32 - b'A' as u32 + 1));
    let row_num = row_str.parse::<u32>().unwrap_or(0);

    (col_num, row_num)
}

/// Converts a column number to an Excel column letter (e.g., 1 -> "A", 26 -> "Z", 27 -> "AA").
fn col_to_excel(col: u32) -> String {
    let mut col_str = String::new();
    let mut n = col;

    while n > 0 {
        n -= 1;
        col_str.push((b'A' + (n % 26) as u8) as char);
        n /= 26;
    }

    col_str.chars().rev().collect()
}