doe 1.1.85

/// xlsx model
/// ```ignore
/// use doe::xlsx;
/// let mut book = xlsx::new_file();
/// book.set_sheet_name(0, "book");
/// xlsx::write(&book, "book.xlsx".to_path_buf()).unwrap();
///
///
///use doe::xlsx::*;
/// use doe::xlsx::read_xlsx;
/// let xx = "E:/code/rust_code/doe_test/demo.xlsx";
/// let mut xlsx_book = read_xlsx(xx).unwrap();
/// let sheet_names: Vec<String> = xlsx_book.get_sheet_names();
/// println!("{:?}", sheet_names);
/// let sheet = xlsx_book.get_sheet(&0).unwrap();
/// let cells = sheet.read_cells().unwrap();
/// for (_, cell) in cells.iter().enumerate() {
///     for (c, c_idx, _) in cell {
///         let col_name = num_to_col(*c_idx as usize);
///         if col_name == "T" {
///             if let Some(c) = c {
///                 println!(
///                     "{:?},{}",
///                     c.get_cell_value().get_raw_value().get_data_type(),
///                     c.get_cell_value().get_value()
///                 );
///             }
///         }
///     }
/// }
/// ```
///
#[allow(warnings)]
#[cfg(feature = "xlsx")]
pub mod xlsx {
    use std::{io::Cursor, path::PathBuf};
    pub use umya_spreadsheet::writer::xlsx::*;
    pub use umya_spreadsheet::*;
    ///
    /// ```ignore
    /// use doe::xlsx;
    /// let s = vec![
    ///     vec!["a".to_string(), "b".to_string(), "c".to_string()],
    ///     vec!["d".to_string(), "e".to_string(), "f".to_string()],
    ///     vec!["g".to_string(), "h".to_string(), "i".to_string()],
    /// ];
    /// xlsx::write_csv_as_xlsx("demo.xlsx", s).unwrap();
    /// ```
    ///
    ///
    pub fn write_csv_as_xlsx(
        xlsx_path: impl AsRef<Path>,
        csv_data: Vec<Vec<String>>,
    ) -> anyhow::Result<()> {
        use crate::xlsx;
        let mut xlsx_book = xlsx::new_file();
        let mut st = Worksheet::default();
        st.set_name("csv_data".to_string());
        xlsx_book.add_sheet(st).map_err(|s| anyhow!(s))?;
        // xlsx_book.set_sheet_name(0, "Sheet1");
        if let Some(mut sheet) = xlsx_book.get_sheet_by_name_mut("csv_data") {
            for (r_index, row) in csv_data.iter().enumerate() {
                for (c_index, val) in row.iter().enumerate() {
                    let (col_name, _) = position_to_coordinate_tuple(c_index + 1, r_index + 1)
                        .context("Unexpected error at position_to_coordinate")?;
                    sheet
                        .get_column_dimension_mut(&col_name)
                        .set_auto_width(true);
                    let coordinate = position_to_coordinate(c_index + 1, r_index + 1)
                        .context("Unexpected error at position_to_coordinate")?;
                    let mut cell = sheet.get_cell_value_mut(coordinate.to_string());
                    cell.set_value_string(val.to_string());
                }
            }
            writer::xlsx::write(&xlsx_book, xlsx_path)?;
        } else {
            anyhow::anyhow!("Unexpected error at get sheet");
        }
        Ok(())
    }

    #[derive(Debug, Clone)]
    pub struct SheetData {
        pub sheet_name: String,
        pub csv_data: Vec<Vec<String>>,
    }

    pub fn write_xlsx_with_sheet_data_list(
        xlsx_path: impl AsRef<Path>,
        sheet_data: Vec<SheetData>,
    ) -> anyhow::Result<()> {
        use crate::xlsx;
        let mut xlsx_book = xlsx::new_file();
        let _ = xlsx_book.remove_sheet_by_name("Sheet1");
        for sd in sheet_data {
            let sheet_name = sd.sheet_name;
            let csv_data = sd.csv_data;
            let mut st = Worksheet::default();
            st.set_name(sheet_name.to_string());
            xlsx_book.add_sheet(st).map_err(|s| anyhow!(s))?;
            // xlsx_book.set_sheet_name(0, "Sheet1");
            if let Some(mut sheet) = xlsx_book.get_sheet_by_name_mut(&sheet_name.to_string()) {
                for (r_index, row) in csv_data.iter().enumerate() {
                    for (c_index, val) in row.iter().enumerate() {
                        let (col_name, _) = position_to_coordinate_tuple(c_index + 1, r_index + 1)
                            .context("Unexpected error at position_to_coordinate")?;
                        sheet
                            .get_column_dimension_mut(&col_name)
                            .set_auto_width(true);

                        let coordinate = position_to_coordinate(c_index + 1, r_index + 1)
                            .context("Unexpected error at position_to_coordinate")?;
                        let mut cell = sheet.get_cell_value_mut(coordinate.to_string());
                        cell.set_value_string(val.to_string());
                    }
                }
            } else {
                anyhow::anyhow!("Unexpected error at get sheet");
            }
        }
        writer::xlsx::write(&xlsx_book, xlsx_path)?;
        Ok(())
    }

    // if width == -1.0 => set_auto_width else set with width val
    pub fn write_xlsx_with_sheet_data_list_with_width(
        xlsx_path: impl AsRef<Path>,
        sheet_data: Vec<SheetData>,
        width: Option<f64>,
    ) -> anyhow::Result<()> {
        use crate::xlsx;
        let mut xlsx_book = xlsx::new_file();
        let _ = xlsx_book.remove_sheet_by_name("Sheet1");
        for sd in sheet_data {
            let sheet_name = sd.sheet_name;
            let csv_data = sd.csv_data;
            let mut st = Worksheet::default();
            st.set_name(sheet_name.to_string());
            xlsx_book.add_sheet(st).map_err(|s| anyhow!(s))?;
            // xlsx_book.set_sheet_name(0, "Sheet1");
            if let Some(mut sheet) = xlsx_book.get_sheet_by_name_mut(&sheet_name.to_string()) {
                for (r_index, row) in csv_data.iter().enumerate() {
                    for (c_index, val) in row.iter().enumerate() {
                        let (col_name, _) = position_to_coordinate_tuple(c_index + 1, r_index + 1)
                            .context("Unexpected error at position_to_coordinate")?;
                        if width.is_none() {
                            sheet
                                .get_column_dimension_mut(&col_name)
                                .set_auto_width(true);
                        } else {
                            sheet
                                .get_column_dimension_mut(&col_name)
                                .set_width(width.unwrap_or_default());
                        }
                        let coordinate = position_to_coordinate(c_index + 1, r_index + 1)
                            .context("Unexpected error at position_to_coordinate")?;
                        let mut cell = sheet.get_cell_value_mut(coordinate.to_string());
                        cell.set_value_string(val.to_string());
                    }
                }
            } else {
                anyhow::anyhow!("Unexpected error at get sheet");
            }
        }
        writer::xlsx::write(&xlsx_book, xlsx_path)?;
        Ok(())
    }

    pub fn write_csv_as_xlsx_with_sheet_name(
        xlsx_path: impl AsRef<Path>,
        sheet_name: impl ToString,
        csv_data: Vec<Vec<String>>,
    ) -> anyhow::Result<()> {
        use crate::xlsx;
        let mut xlsx_book = xlsx::new_file();
        let _ = xlsx_book.remove_sheet_by_name("Sheet1");
        let mut st = Worksheet::default();
        st.set_name(sheet_name.to_string());
        xlsx_book.add_sheet(st).map_err(|s| anyhow!(s))?;
        // xlsx_book.set_sheet_name(0, "Sheet1");
        if let Some(mut sheet) = xlsx_book.get_sheet_by_name_mut(&sheet_name.to_string()) {
            for (r_index, row) in csv_data.iter().enumerate() {
                for (c_index, val) in row.iter().enumerate() {
                    let (col_name, _) = position_to_coordinate_tuple(c_index + 1, r_index + 1)
                        .context("Unexpected error at position_to_coordinate")?;
                    sheet
                        .get_column_dimension_mut(&col_name)
                        .set_auto_width(true);
                    let coordinate = position_to_coordinate(c_index + 1, r_index + 1)
                        .context("Unexpected error at position_to_coordinate")?;
                    let mut cell = sheet.get_cell_value_mut(coordinate.to_string());
                    cell.set_value_string(val.to_string());
                }
            }
            writer::xlsx::write(&xlsx_book, xlsx_path)?;
        } else {
            anyhow::anyhow!("Unexpected error at get sheet");
        }
        Ok(())
    }

    pub fn write_csv_to_xlsx(
        xlsx_path: impl AsRef<Path>,
        sheet_name: impl ToString,
        csv_data: Vec<Vec<String>>,
    ) -> anyhow::Result<()> {
        let mut xlsx_book = umya_spreadsheet::reader::xlsx::lazy_read(xlsx_path.as_ref())?;
        if let Some(mut sheet) = xlsx_book.get_sheet_by_name_mut(&sheet_name.to_string()) {
            for (r_index, row) in csv_data.iter().enumerate() {
                for (c_index, val) in row.iter().enumerate() {
                    let (col_name, _) = position_to_coordinate_tuple(c_index + 1, r_index + 1)
                        .context("Unexpected error at position_to_coordinate")?;
                    sheet
                        .get_column_dimension_mut(&col_name)
                        .set_auto_width(true);
                    let coordinate = position_to_coordinate(c_index, r_index)
                        .context("Unexpected error at position_to_coordinate")?;
                    let mut cell = sheet.get_cell_value_mut(coordinate.to_string());
                    cell.set_value_string(val.to_string());
                }
            }
            writer::xlsx::write(&xlsx_book, xlsx_path)?;
        } else {
            let mut st = Worksheet::default();
            st.set_name(sheet_name.to_string());
            xlsx_book.add_sheet(st);
            writer::xlsx::write(&xlsx_book, xlsx_path.as_ref())?;
            let mut xlsx_book = umya_spreadsheet::reader::xlsx::lazy_read(xlsx_path.as_ref())?;
            if let Some(mut sheet) = xlsx_book.get_sheet_by_name_mut(&sheet_name.to_string()) {
                for (r_index, row) in csv_data.iter().enumerate() {
                    for (c_index, val) in row.iter().enumerate() {
                        let (col_name, _) = position_to_coordinate_tuple(c_index + 1, r_index + 1)
                            .context("Unexpected error at position_to_coordinate")?;
                        sheet
                            .get_column_dimension_mut(&col_name)
                            .set_auto_width(true);
                        let coordinate = position_to_coordinate(c_index + 1, r_index + 1)
                            .context("Unexpected error at position_to_coordinate")?;
                        let mut cell = sheet.get_cell_value_mut(coordinate.to_string());
                        cell.set_value_string(val.to_string());
                    }
                }
                writer::xlsx::write(&xlsx_book, xlsx_path)?;
            } else {
                anyhow::anyhow!("Unexpected error at get sheet");
            }
        }
        Ok(())
    }
    use crate::{DebugPrint, Print};
    ///
    /// value_type can be 'formula' 'string' 'number' 'bool' 'hyperlink'
    ///
    ///```rust
    ///use doe::*;
    ///xlsx::xlsx_set_values("./demo.xlsx", "Sheet1", &[cellvalue!("M4", "3", "number")]);
    /// ```
    ///
    #[macro_export]
    macro_rules! cellvalue {
        ($coordinate:expr,$value:expr,$value_type:expr) => {
            $crate::xlsx::CellValue::new($coordinate, $value, $value_type)
        };
    }
    ///num_to_col
    /// ```ignore
    ///col_to_num("D").dprintln();//4
    ///num_to_col(4).dprintln();//D
    ///coordinate_to_position("D27").dprintln();//4,27
    ///position_to_coordinate(4, 27).unwrap().dprintln();//D27
    /// ```
    pub fn num_to_col(index: usize) -> String {
        let index = index + 1;
        let mut result = String::new();
        let mut index = index;

        while index > 0 {
            let remainder = (index - 1) % 26;
            result.push((b'A' + remainder as u8) as char);
            index = (index - 1) / 26;
        }

        result.chars().rev().collect()
    }
    ///col_to_num
    /// ```ignore
    ///col_to_num("D").dprintln();//4
    ///num_to_col(4).dprintln();//D
    ///coordinate_to_position("D27").dprintln();//4,27
    ///position_to_coordinate(4, 27).unwrap().dprintln();//D27
    /// ```
    pub fn col_to_num(col: impl ToString) -> usize {
        let col_str = col.to_string().to_uppercase();
        let mut col_num = 0;
        for (i, c) in col_str.chars().enumerate() {
            if (c as u8 as i8 - 'A' as u8 as i8) >= 0 {
                let offset = (c as u8 - b'A') as usize + 1;
                col_num = col_num * 26 + offset;
            }
        }
        col_num - 1
    }
    ///coordinate_to_position
    /// ```ignore
    ///col_to_num("D").dprintln();//4
    ///num_to_col(4).dprintln();//D
    ///coordinate_to_position("D27").dprintln();//4,27
    ///position_to_coordinate(4, 27).unwrap().dprintln();//D27
    /// ```
    pub fn coordinate_to_position(coordinate: impl ToString) -> (usize, usize) {
        let coord_str = coordinate.to_string();
        let mut col_str = String::new();
        let mut row_str = String::new();
        for c in coord_str.chars() {
            if c.is_digit(10) {
                row_str.push(c);
            } else {
                col_str.push(c);
            }
        }
        let col_num = col_to_num(col_str);
        let row_num = row_str.parse::<usize>().unwrap();
        (col_num, row_num)
    }
    ///position_to_coordinate
    /// ```ignore
    ///col_to_num("D").dprintln();//4
    ///num_to_col(4).dprintln();//D
    ///coordinate_to_position("D27").dprintln();//4,27
    ///position_to_coordinate(4, 27).unwrap().dprintln();//D27
    /// ```
    pub fn position_to_coordinate(x: usize, y: usize) -> Option<String> {
        if x >= 1 && y >= 1 {
            let mut num = x;
            let mut col_name = String::new();
            while num > 0 {
                let rem = (num - 1) % 26;
                col_name.insert(0, ((rem as u8) + b'A') as char);
                num = (num - 1) / 26;
            }
            Some(format!("{}{}", col_name, y))
        } else {
            None
        }
    }

    pub fn position_to_coordinate_tuple(x: usize, y: usize) -> Option<(String, String)> {
        if x >= 1 && y >= 1 {
            let mut num = x;
            let mut col_name = String::new();
            while num > 0 {
                let rem = (num - 1) % 26;
                col_name.insert(0, ((rem as u8) + b'A') as char);
                num = (num - 1) / 26;
            }
            Some((format!("{}", col_name), format!("{}", y)))
        } else {
            None
        }
    }
    pub trait SpreadsheetHelper {
        fn get_sheet_names(&mut self) -> Vec<String>;
    }
    pub trait WorksheetHelper {
        fn get_cell_types(&self) -> Vec<String>;
        fn read_cells(&self) -> anyhow::Result<Vec<Vec<(Option<Cell>, u32, u32)>>>;
    }
    impl WorksheetHelper for Worksheet {
        fn get_cell_types(&self) -> Vec<String> {
            let cell_types: Vec<String> = self
                .get_cell_collection()
                .iter()
                .map(|c| c.get_cell_value().get_data_type().to_string())
                .collect();
            cell_types
        }
        fn read_cells(&self) -> anyhow::Result<Vec<Vec<(Option<Cell>, u32, u32)>>> {
            let col = self.get_highest_column();
            let row = self.get_highest_row();
            let mut csv_data = vec![];
            for r in 1..col + 1 {
                let mut row_vec: Vec<(Option<Cell>, u32, u32)> = Vec::new();
                for c in 1..row + 1 {
                    let cell: Option<Cell> =
                        self.get_cell((c as u32, r as u32)).map(|s| s.to_owned());
                    row_vec.push((cell, c as u32, r as u32));
                }
                csv_data.push(row_vec);
            }
            anyhow::Ok(csv_data)
        }
    }

    impl WorksheetHelper for &mut Worksheet {
        fn get_cell_types(&self) -> Vec<String> {
            let cell_types: Vec<String> = self
                .get_cell_collection()
                .iter()
                .map(|c| c.get_cell_value().get_data_type().to_string())
                .collect();
            cell_types
        }
        fn read_cells(&self) -> anyhow::Result<Vec<Vec<(Option<Cell>, u32, u32)>>> {
            let col = self.get_highest_column();
            let row = self.get_highest_row();
            let mut csv_data = vec![];
            for r in 1..col + 1 {
                let mut row_vec = Vec::new();
                for c in 1..row + 1 {
                    let cell: Option<Cell> =
                        self.get_cell((c as u32, r as u32)).map(|s| s.to_owned());
                    row_vec.push((cell, c as u32, r as u32));
                }
                csv_data.push(row_vec);
            }
            anyhow::Ok(csv_data)
        }
    }
    impl SpreadsheetHelper for Spreadsheet {
        fn get_sheet_names(&mut self) -> Vec<String> {
            let sheet_count = self.get_sheet_count();
            for i in 0..sheet_count {
                self.read_sheet(i);
            }
            let sheet_names: Vec<String> = self
                .get_sheet_collection_no_check()
                .iter()
                .map(|s| s.get_name().to_string())
                .collect();
            sheet_names
        }
    }
    impl SpreadsheetHelper for &mut Spreadsheet {
        fn get_sheet_names(&mut self) -> Vec<String> {
            let sheet_count = self.get_sheet_count();
            for i in 0..sheet_count {
                self.read_sheet(i);
            }
            let sheet_names: Vec<String> = self
                .get_sheet_collection_no_check()
                .iter()
                .map(|s| s.get_name().to_string())
                .collect();
            sheet_names
        }
    }
    ///xlsx_get_sheet_names
    ///```ignore
    ///let sheet_names = xlsx_get_sheet_names("./book.xlsx").unwrap();
    ///println!("{:?}", sheet_names);
    ///```
    pub fn xlsx_get_sheet_names(xlsx_path: impl ToString) -> Option<Vec<String>> {
        let xlsx_path = xlsx_path.to_string();
        let path = std::path::Path::new(&xlsx_path);
        if let std::result::Result::Ok(mut xlsx_book) =
            umya_spreadsheet::reader::xlsx::lazy_read(path)
        {
            let sheet_count = xlsx_book.get_sheet_count();
            let sheet_names: Vec<String> = xlsx_book
                .get_sheet_collection_no_check()
                .iter()
                .map(|s| s.get_name().to_string())
                .collect();
            return Some(sheet_names);
        } else {
            anyhow::anyhow!("Unexpected error at read xlsx file");
            return None;
        }
        return None;
    }
    ///xlsx_get_cell_value
    ///```ignore
    /// let cell_value = xlsx_get_cell_value("./lang.xlsx","Sheet1","D27");
    /// ```
    pub fn xlsx_get_cell_value(
        xlsx_path: impl ToString,
        sheet_name: impl ToString,
        coordinate: impl ToString,
    ) -> Option<String> {
        let xlsx_path = xlsx_path.to_string();
        let sheet_name = sheet_name.to_string();
        let coordinate = coordinate.to_string();
        let path = std::path::Path::new(&xlsx_path);
        if let std::result::Result::Ok(mut xlsx_book) =
            umya_spreadsheet::reader::xlsx::lazy_read(path)
        {
            if let Some(sheet) = xlsx_book.get_sheet_by_name_mut(&sheet_name) {
                let value = sheet.get_cell_value(coordinate).get_value().to_string();
                return Some(value);
            } else {
                anyhow::anyhow!("Unexpected error at get sheet");
            }
            return None;
        } else {
            anyhow::anyhow!("Unexpected error at read xlsx file");
        }
        return None;
    }

    pub fn xlsx_delete_row(
        xlsx_path: impl ToString,
        sheet_name: impl ToString,
        row_index: u32,
        num_rows: u32,
    ) -> Result<()> {
        use umya_spreadsheet::{reader::xlsx::lazy_read, writer::xlsx::write};

        let xlsx_path = xlsx_path.to_string();
        let sheet_name = sheet_name.to_string();
        let path = std::path::Path::new(&xlsx_path);
    
        // 1. 读取文件（添加错误上下文）
        let mut xlsx_book = lazy_read(path)
            .with_context(|| format!("读取 Excel 文件失败：{}", xlsx_path))?;
    
        // 2. 获取工作表
        let sheet = xlsx_book
            .get_sheet_by_name_mut(&sheet_name)
            .with_context(|| format!("未找到工作表：{}", sheet_name))?;
    
        // 3. 删除行（umya_spreadsheet API：起始索引，删除行数）
        sheet.remove_row(&row_index, &num_rows);
    
        // 4. 保存文件
        write(&xlsx_book, &xlsx_path)
            .with_context(|| format!("保存 Excel 文件失败：{}", xlsx_path))?;
    
        Ok(())
    }

    pub fn xlsx_get_cell(
        xlsx_path: impl ToString,
        sheet_name: impl ToString,
        coordinate: impl ToString,
    ) -> Option<umya_spreadsheet::CellValue> {
        let xlsx_path = xlsx_path.to_string();
        let sheet_name = sheet_name.to_string();
        let coordinate = coordinate.to_string();
        let path = std::path::Path::new(&xlsx_path);
        if let std::result::Result::Ok(mut xlsx_book) =
            umya_spreadsheet::reader::xlsx::lazy_read(path)
        {
            if let Some(sheet) = xlsx_book.get_sheet_by_name_mut(&sheet_name) {
                let value = sheet.get_cell_value(coordinate);
                return Some(value.to_owned());
            } else {
                anyhow::anyhow!("Unexpected error at get sheet");
            }
            return None;
        } else {
            anyhow::anyhow!("Unexpected error at read xlsx file");
        }
        return None;
    }
    /// CellValue coordinate is linke "A4" ..
    ///
    /// value is impl Tosting
    ///
    /// value_type can be 'formula' 'string' 'number' 'bool' 'hyperlink'
    pub struct CellValue<T: ToString, U: ToString> {
        pub coordinate: T,
        pub value: U,
        pub value_type: &'static str,
    }
    ///
    ///```rust
    ///use doe::*;
    ///xlsx::xlsx_set_values("./demo.xlsx", "Sheet1", &[CellValue::new("M4", "", "formula_attributes")]);
    /// ```
    ///
    impl<T: ToString, U: ToString> CellValue<T, U> {
        ///
        ///```rust
        ///use doe::*;
        ///xlsx::xlsx_set_values("./demo.xlsx", "Sheet1", &[CellValue::new("M4", "", "formula_attributes")]);
        /// ```
        /// value_type can be 'formula' 'string' 'number' 'bool' 'hyperlink'
        ///
        pub fn new(coordinate: T, value: U, value_type: &'static str) -> Self {
            Self {
                coordinate,
                value,
                value_type,
            }
        }
    }
    /// ### xlsx_bytes_set_values_and_save example
    ///```ignore
    /// let xlsx_bytes = std::fs::read("./demo.xlsx").unwrap();
    /// doe::xlsx::xlsx_bytes_set_values_and_save(xlsx_bytes, "Sheet1", &[CellValue::new("B4", "./XJTJWSHRD2023000026.pdf,pdf", "hyperlink")],"new.xlsx").unwrap();
    /// doe::xlsx::xlsx_bytes_set_values_and_save(xlsx_bytes, "Sheet1", &[CellValue::new("B4", "andrew", "string")],"new.xlsx").unwrap();
    /// doe::xlsx::xlsx_bytes_set_values_and_save(xlsx_bytes, "Sheet1", &[CellValue::new("B4", "12", "number")],"new.xlsx").unwrap();
    ///```

    pub fn xlsx_bytes_set_values_and_save(
        xlsx_bytes: Vec<u8>,
        sheet_name: impl ToString,
        values: &[CellValue<impl ToString, impl ToString>],
        path: PathBuf,
    ) -> Result<(), Box<dyn std::error::Error>> {
        let values = values;
        use umya_spreadsheet::writer;
        // let xlsx_path = xlsx_path.to_string();
        // let path = std::path::Path::new(&xlsx_path);
        let reader = Cursor::new(xlsx_bytes);
        let mut xlsx_book =
            umya_spreadsheet::reader::xlsx::read_reader(reader, true).expect("read xlsx Error");
        let mut sheet = xlsx_book
            .get_sheet_by_name_mut(&sheet_name.to_string())
            .expect("read sheet Error");
        for cellvalue in values.iter() {
            let mut cell = sheet.get_cell_mut(cellvalue.coordinate.to_string());
            let mut cell_value = cell.get_cell_value_mut();
            if cellvalue.value_type.to_string() == "string" {
                cell_value.set_value_string(cellvalue.value.to_string());
            } else if cellvalue.value_type.to_string() == "number" {
                cell_value.set_value_number(
                    cellvalue
                        .value
                        .to_string()
                        .parse::<f64>()
                        .expect("number parse f64 Error"),
                );
            } else if cellvalue.value_type.to_string() == "formula" {
                cell_value.set_formula(cellvalue.value.to_string());
            }
            // fn main() {
            //     use doe::*;
            //     xlsx::xlsx_set_values("./demo.xlsx", "Sheet1", &[CellValue::new("B4", "./XJTJWSHRD2023000026.pdf,pdf", "hyperlink")]).unwrap();
            // }
            else if cellvalue.value_type.to_string() == "hyperlink" {
                let mut hyperlink = Hyperlink::default();
                let v = cellvalue.value.to_string();
                let hyperlink_vec: Vec<_> = v.split(",").filter(|s| !s.is_empty()).collect();
                hyperlink
                    .set_url(hyperlink_vec.iter().nth(0).unwrap().to_string())
                    .set_tooltip(hyperlink_vec.iter().nth(1).unwrap().to_string())
                    .set_location(false);

                cell.set_hyperlink(hyperlink);
            } else if cellvalue.value_type.to_string() == "bool" {
                let value = move || {
                    if cellvalue.value.to_string() == "true" || cellvalue.value.to_string() == "1" {
                        true
                    } else if cellvalue.value.to_string() == "false"
                        || cellvalue.value.to_string() == "0"
                    {
                        false
                    } else {
                        false
                    }
                };
                cell_value.set_value_bool(value());
            }
        }

        let _ = writer::xlsx::write(&xlsx_book, path);
        std::result::Result::Ok(())
    }

    use umya_spreadsheet::Hyperlink;
    ///```rust
    /// use doe::*;
    /// xlsx::xlsx_set_values("./rust.xlsx", "Sheet1", &[cellvalue!("A5","rust","string")]);
    /// xlsx::xlsx_set_values("./demo.xlsx", "Sheet1", &[CellValue::new("B4", "./XJTJWSHRD2023000026.pdf,pdf", "hyperlink")]).unwrap();
    ///  let mut cellvalues = vec![];
    ///  for (index, c) in c_vec.iter().enumerate() {
    ///      for (a, q) in a_vec.iter().zip(q_vec.iter()) {
    ///          if c == q{
    ///              cellvalues.push(cellvalue!("A".push_back(index+2), a, "string"));
    ///          }
    ///          
    ///     }
    /// }
    /// xlsx_set_values("148.xlsx", "1704344947003", &cellvalues).unwrap();
    /// ```
    pub fn xlsx_set_values(
        xlsx_path: impl ToString,
        sheet_name: impl ToString,
        values: &[CellValue<impl ToString, impl ToString>],
    ) -> Result<(), Box<dyn std::error::Error>> {
        let values = values;
        use umya_spreadsheet::writer;
        let xlsx_path = xlsx_path.to_string();
        let path = std::path::Path::new(&xlsx_path);
        // umya_spreadsheet::reader::xlsx::read_reader
        let mut xlsx_book =
            umya_spreadsheet::reader::xlsx::lazy_read(path).expect("read xlsx Error");
        let mut sheet = xlsx_book
            .get_sheet_by_name_mut(&sheet_name.to_string())
            .expect("read sheet Error");
        for cellvalue in values.iter() {
            let mut cell = sheet.get_cell_mut(cellvalue.coordinate.to_string());
            let mut cell_value = cell.get_cell_value_mut();
            if cellvalue.value_type.to_string() == "string" {
                cell_value.set_value_string(cellvalue.value.to_string());
            } else if cellvalue.value_type.to_string() == "number" {
                cell_value.set_value_number(
                    cellvalue
                        .value
                        .to_string()
                        .parse::<f64>()
                        .expect("number parse f64 Error"),
                );
            } else if cellvalue.value_type.to_string() == "formula" {
                cell_value.set_formula(cellvalue.value.to_string());
            }
            // fn main() {
            //     use doe::*;
            //     xlsx::xlsx_set_values("./demo.xlsx", "Sheet1", &[CellValue::new("B4", "./XJTJWSHRD2023000026.pdf,pdf", "hyperlink")]).unwrap();
            // }
            else if cellvalue.value_type.to_string() == "hyperlink" {
                let mut hyperlink = Hyperlink::default();
                let v = cellvalue.value.to_string();
                let hyperlink_vec: Vec<_> = v.split(",").filter(|s| !s.is_empty()).collect();
                hyperlink
                    .set_url(hyperlink_vec.iter().nth(0).unwrap().to_string())
                    .set_tooltip(hyperlink_vec.iter().nth(1).unwrap().to_string())
                    .set_location(false);

                cell.set_hyperlink(hyperlink);
            } else if cellvalue.value_type.to_string() == "bool" {
                let value = move || {
                    if cellvalue.value.to_string() == "true" || cellvalue.value.to_string() == "1" {
                        true
                    } else if cellvalue.value.to_string() == "false"
                        || cellvalue.value.to_string() == "0"
                    {
                        false
                    } else {
                        false
                    }
                };
                cell_value.set_value_bool(value());
            }
        }
        let _ = writer::xlsx::write(&xlsx_book, path);
        std::result::Result::Ok(())
    }
    ///xlsx_set_cell_value
    /// ```ignore
    /// use doe::xlsx::position_to_coordinate as ptc;
    /// use doe::*;
    /// xlsx::xlsx_get_sheet_names("./book.xlsx").dprintln();
    /// for x in 1..10 {
    ///     for y in 1..10 {
    ///         xlsx::xlsx_set_cell_value_string("./book.xlsx", "Info", ptc(x, y).unwrap(), format!("{},{}", x, y));
    ///     }
    /// }
    /// xlsx::xlsx_set_cell_value_string("./book.xlsx", "Sheet1", "D27", "some value").unwrap();
    /// ```
    ///
    pub fn xlsx_set_cell_value_string(
        xlsx_path: impl ToString,
        sheet_name: impl ToString,
        coordinate: impl ToString,
        new_value: impl ToString,
    ) -> Result<(), Box<dyn std::error::Error>> {
        use umya_spreadsheet::writer;
        let xlsx_path = xlsx_path.to_string();
        let path = std::path::Path::new(&xlsx_path);
        if let std::result::Result::Ok(mut xlsx_book) =
            umya_spreadsheet::reader::xlsx::lazy_read(path)
        {
            if let Some(mut sheet) = xlsx_book.get_sheet_by_name_mut(&sheet_name.to_string()) {
                let mut cell = sheet.get_cell_value_mut(coordinate.to_string());
                cell.set_value_string(new_value.to_string());
                let _ = writer::xlsx::write(&xlsx_book, path);
            } else {
                anyhow::anyhow!("Unexpected error at get sheet");
            }
        } else {
            anyhow::anyhow!("Unexpected error at read xlsx file");
        }
        std::result::Result::Ok(())
    }
    ///
    /// ```ignore
    /// let data:Vec<String> = xlsx_get_col_values("2024-01-08.xlsx", "检验报告查询导出2024-01-08","J").unwrap();
    /// ```
    ///
    ///
    pub fn xlsx_get_col_values(
        xlsx_path: impl ToString,
        sheet_name: impl ToString,
        col: impl ToString,
    ) -> Result<Vec<String>, Box<dyn std::error::Error>> {
        let xlsx_path = xlsx_path.to_string();
        let sheet_name = sheet_name.to_string();
        let col = col.to_string();
        let col_num = col_to_num(col);
        let path = std::path::Path::new(&xlsx_path);
        let mut xlsx_book = umya_spreadsheet::reader::xlsx::lazy_read(path)?;
        let mut sheet = xlsx_book
            .get_sheet_by_name_mut(&sheet_name.to_string())
            .expect("Couldn't find sheet");
        let cells = sheet.get_cell_collection();
        let mut max = 0;
        for cell in cells.clone() {
            let coordinate = cell.get_coordinate().get_coordinate();
            let (x, y) = coordinate_to_position(coordinate);
            if x > max {
                max = x;
            }
            if y > max {
                max = y;
            }
        }
        let mut csv_data = vec![];
        for cell in cells.clone() {
            let coordinate = cell.get_coordinate().get_coordinate();
            let (x, y) = coordinate_to_position(coordinate);
            // csv_data[y - 1][x - 1] = cell.get_value().to_string();
            if x == col_num {
                csv_data.push(cell.get_value().to_string());
            }
        }
        return std::result::Result::Ok(csv_data);
    }

    pub fn convert_excel_date(excel_date_str: &str) -> String {
        use chrono::{Datelike, NaiveDate, NaiveDateTime, Timelike};

        // 去除可能的空白字符
        let excel_date_str = excel_date_str.trim();

        // 如果已经是标准日期时间格式，直接返回
        if excel_date_str.contains("-") && excel_date_str.len() >= 10 {
            return excel_date_str.to_string();
        }

        // 尝试解析为f64（Excel数字格式）
        if let std::result::Result::Ok(excel_date) = excel_date_str.parse::<f64>() {
            // Excel日期系统说明：
            // Excel 1 = 1900-01-01
            // Excel 2 = 1900-01-02
            // ...
            // Excel 60 = 1900-02-29 (这个日期实际上不存在，Excel错误地认为1900年是闰年)
            // Excel 61 = 1900-03-01
            // 所以我们需要处理这个1900闰年bug

            let whole_days = excel_date.floor() as i64;
            let fraction = excel_date.fract();

            // 基准日期：1899-12-30，这样Excel 1 = 1900-01-01
            let base_date = NaiveDate::from_ymd_opt(1899, 12, 30).unwrap();

            // 计算天数
            let adjusted_days = whole_days;

            // 注意：Excel的1900年闰年bug处理
            // Excel错误地认为1900年是闰年，所以Excel 60 = 1900-02-29（不存在的日期）
            // Excel 61 = 1900-03-01
            // 但是我们使用1899-12-30作为基准日期，这个基准已经考虑了这个bug
            // 所以不需要额外调整天数

            // 计算最终日期
            let date = match base_date.checked_add_signed(chrono::Duration::days(adjusted_days)) {
                Some(d) => d,
                None => return excel_date_str.to_string(),
            };

            // 处理时间部分（Excel的小数部分是时间的比例）
            let total_seconds = (fraction * 86400.0).round() as u32;
            let hours = total_seconds / 3600;
            let minutes = (total_seconds % 3600) / 60;
            let seconds = total_seconds % 60;

            // 创建日期时间
            let datetime = date.and_hms_opt(hours, minutes, seconds);

            match datetime {
                Some(dt) => {
                    // 格式化输出
                    format!(
                        "{:04}-{:02}-{:02} {:02}:{:02}:{:02}",
                        dt.year(),
                        dt.month(),
                        dt.day(),
                        dt.hour(),
                        dt.minute(),
                        dt.second()
                    )
                }
                None => excel_date_str.to_string(),
            }
        } else {
            // 尝试处理"2025/1/12"这样的格式
            excel_date_str.to_string()
        }
    }

    // 辅助函数：将"YYYY/MM/DD"格式转换为"YYYY-MM-DD HH:MM:SS"
    pub fn convert_date_format(date_str: &str) -> String {
        let parts: Vec<&str> = date_str.split('/').collect();
        if parts.len() == 3 {
            let year = parts[0];
            let month = parts[1];
            let day = parts[2];

            // 如果包含时间，处理时间部分
            // In function `convert_date_format`, around line 105
            let (day_part, time_part) = if day.contains(' ') {
                let day_parts: Vec<&str> = day.split(' ').collect();
                (day_parts[0], day_parts.get(1).copied().unwrap_or(""))
            } else {
                (day, "")
            };

            let mut result = format!(
                "{}-{:02}-{:02}",
                year,
                month.parse::<u32>().unwrap_or(0),
                day_part.parse::<u32>().unwrap_or(0)
            );

            if !time_part.is_empty() {
                result.push_str(&format!(" {}", time_part));
            } else {
                result.push_str(" 00:00:00");
            }

            result
        } else {
            date_str.to_string()
        }
    }

    pub fn read_xlsx(xlsx_path: impl ToString) -> Result<Spreadsheet, Box<dyn std::error::Error>> {
        let xlsx_path = xlsx_path.to_string();
        let path = std::path::Path::new(&xlsx_path);
        let mut xlsx_book: Spreadsheet = umya_spreadsheet::reader::xlsx::lazy_read(path)?;
        return std::result::Result::Ok(xlsx_book);
    }

    pub fn read_xlsx_from_buf(xlsx_buf: &[u8]) -> Result<Spreadsheet, Box<dyn std::error::Error>> {
        use std::io::Cursor;
        let cursor: Cursor<&[u8]> = Cursor::new(xlsx_buf);
        let xlsx_book: Spreadsheet = umya_spreadsheet::reader::xlsx::read_reader(cursor, true)?;
        return std::result::Result::Ok(xlsx_book);
    }

    pub fn xlsx_style_as_text_and_read_as_csv(
        xlsx_path: impl ToString,
        sheet_name: impl ToString,
    ) -> Result<Vec<Vec<String>>, Box<dyn std::error::Error>> {
        let xlsx_path = xlsx_path.to_string();
        let sheet_name = sheet_name.to_string();
        let path = std::path::Path::new(&xlsx_path);
        let mut xlsx_book = umya_spreadsheet::reader::xlsx::lazy_read(path)?;
        let mut sheet = xlsx_book
            .get_sheet_by_name_mut(&sheet_name.to_string())
            .expect("get_sheet_by_name error");
        let sheet_clone = sheet.clone();
        let col = sheet_clone.get_highest_column();
        let row = sheet_clone.get_highest_row();
        let mut csv_data = vec![];
        for r in 1..row + 1 {
            let mut row_vec = Vec::new();
            for c in 1..col + 1 {
                let cell = sheet.get_cell_mut((c as u32, r as u32));
                // let mut cell_val = sheet.get_cell_value_mut((c as u32, r as u32));
                cell.get_style_mut()
                    .get_number_format_mut()
                    .set_format_code("@");
                if cell.get_data_type() == "n" {
                    // row_vec.push(convert_excel_date(&cell.get_value_lazy().to_string()));
                    row_vec.push(cell.get_value_lazy().to_string());
                } else {
                    row_vec.push(cell.get_value_lazy().to_string());
                }
            }
            csv_data.push(row_vec);
        }
        return std::result::Result::Ok(csv_data);
    }

    pub fn xlsx_to_csv(
        xlsx_path: impl ToString,
        sheet_name: impl ToString,
    ) -> Result<Vec<Vec<String>>, Box<dyn std::error::Error>> {
        let xlsx_path = xlsx_path.to_string();
        let sheet_name = sheet_name.to_string();
        let path = std::path::Path::new(&xlsx_path);
        let mut xlsx_book = umya_spreadsheet::reader::xlsx::lazy_read(path)?;
        let mut sheet = xlsx_book
            .get_sheet_by_name_mut(&sheet_name.to_string())
            .expect("get_sheet_by_name error");
        let sheet_clone = sheet.clone();
        let col = sheet_clone.get_highest_column();
        let row = sheet_clone.get_highest_row();
        let mut csv_data = vec![];
        for r in 1..row + 1 {
            let mut row_vec = Vec::new();
            for c in 1..col + 1 {
                let cell = sheet.get_cell_mut((c as u32, r as u32));
                // let mut cell_val = sheet.get_cell_value_mut((c as u32, r as u32));
                // cell.get_style_mut()
                // .get_number_format_mut()
                // .set_format_code("@");
                if cell.get_data_type() == "n" {
                    // row_vec.push(convert_excel_date(&cell.get_value_lazy().to_string()));
                    row_vec.push(cell.get_value_lazy().to_string());
                } else {
                    row_vec.push(cell.get_value_lazy().to_string());
                }
            }
            csv_data.push(row_vec);
        }
        return std::result::Result::Ok(csv_data);
    }
    ///xlsx_to_btree_map
    /// ```ignore
    /// use doe::*;
    /// let bmap = doe::xlsx::xlsx_to_btree_map("get_xlsx.xlsx")?;
    /// bmap.iter().for_each(|(k, v)| {
    ///     std::fs::write(k.push_back(".csv"), v.iter().map(|s|s.join(",")).collect::<Vec<_>>().join("\n")).unwrap();
    /// });
    /// ```
    pub fn xlsx_to_btree_map(
        path: &str,
    ) -> crate::DynError<std::collections::BTreeMap<String, Vec<Vec<String>>>> {
        // 每个sheet就是一个二维数组
        use crate::*;
        // key 是sheet的名字
        // value 是二维数组
        let mut btree_map: std::collections::BTreeMap<String, Vec<Vec<String>>> = btreemap!();
        // 读xlsx
        if let Some(sheet_names) = crate::xlsx::xlsx_get_sheet_names(path) {
            for sheet_name in sheet_names {
                if let std::result::Result::Ok(sheet_data) =
                    crate::xlsx::xlsx_to_csv(path, sheet_name.clone())
                {
                    btree_map.insert(sheet_name, sheet_data);
                }
            }
        }
        std::result::Result::Ok(btree_map)
    }
    pub fn xlsx_to_csv_and_write(
        xlsx_path: impl ToString,
        sheet_name: impl ToString,
        csv_path: impl ToString,
    ) -> Result<(), Box<dyn std::error::Error>> {
        let csv_path = csv_path.to_string();
        let csv: Vec<Vec<String>> = xlsx_to_csv(xlsx_path, sheet_name).unwrap();
        let mut csv_string: String = csv
            .iter()
            .map(|s| s.join(","))
            .collect::<Vec<_>>()
            .join("\n");
        std::fs::write(csv_path, csv_string).unwrap();
        std::result::Result::Ok(())
    }
    /// xlsx replace values and save new xlsx file
    /// ```ignore
    ///use doe::xlsx::*;
    /// //xlsx replace [A] to andrew in the xlsx file
    ///let _ = xlsx_replace_values_save("demo.xlsx".into(), vec![("[A]".into(),"andrew".into())], "new_demo.xlsx".into()).unwrap();
    ///````
    ///
    pub fn xlsx_replace_values_save<T>(
        xlsx_path: PathBuf,
        values: Vec<(T, T)>,
        new_xlsx_path: PathBuf,
    ) -> Result<(), Box<dyn std::error::Error>>
    where
        T: ToString,
    {
        use std::fs::File;
        use std::io::prelude::*;
        use zip::read::ZipArchive;
        use zip::write::FileOptions;
        use zip::CompressionMethod;

        // Open the .docx file as a zip
        let file = File::open(xlsx_path.clone())?;
        let mut archive = ZipArchive::new(file)?;
        let new_archive_path = "new_archive.zip";

        let options = FileOptions::default()
            .compression_method(CompressionMethod::Stored)
            .unix_permissions(0o755);

        let file = File::create(&new_archive_path).unwrap();
        let mut new_zip = zip::ZipWriter::new(file);

        // Loop over all of the files in the .docx archive
        for i in 0..archive.len() {
            let mut file_in_archive = archive.by_index(i).unwrap();
            let file_in_archive_name = &file_in_archive.name().to_string();
            if file_in_archive_name.to_string().ends_with(".xml") {
                let mut contents = String::new();
                file_in_archive.read_to_string(&mut contents).unwrap();
                let mut new_contents = contents.to_string();
                for value in &values {
                    let (target, new_target) = value;
                    // Perform text replacement
                    new_contents =
                        new_contents.replace(&target.to_string(), &new_target.to_string());
                }
                new_zip.start_file(file_in_archive_name, options)?;
                new_zip.write_all(new_contents.as_bytes())?;
            } else {
                new_zip.start_file(file_in_archive_name.to_string(), options)?;
                let mut buffer = Vec::new();
                file_in_archive.read_to_end(&mut buffer)?;
                new_zip.write_all(&buffer)?;
            }
        }
        std::fs::rename(new_archive_path, new_xlsx_path).unwrap();
        std::result::Result::Ok(())
    }

    use anyhow::*; // 导入anyhow库，用于错误处理
                   // use doe::{
                   //     // 导入doe库中的相关模块
                   //     Str,                                                              // 导入Str类型
                   //     xlsx::{xlsx_get_sheet_names, xlsx_to_csv, xlsx_to_csv_and_write}, // 导入xlsx模块中的函数
                   // };
    use std::{collections::BTreeMap, path::Path}; // 导入Path类型，用于处理文件路径

    // 以下代码片段导入了多个库和模块，主要用于处理Excel文件（xlsx格式）的读取和转换操作。
    // 具体功能包括获取Excel文件中的工作表名称、将Excel文件转换为CSV格式，以及将转换后的CSV数据写入文件。
    // 这些操作依赖于`doe`库中的`xlsx`模块，并且使用了`anyhow`库进行错误处理。

    /// 将CSV格式的数据转换为Markdown表格格式的字符串。
    ///
    /// # 参数
    /// - `csv`: 一个二维字符串向量，表示CSV数据。每一行是一个字符串向量，表示CSV的一行数据。
    ///
    /// # 返回值
    /// 返回一个字符串，表示转换后的Markdown表格。如果输入的CSV数据为空，则返回空字符串。
    pub fn csv_to_markdown(csv: &Vec<Vec<String>>) -> String {
        // 如果CSV数据为空，直接返回空字符串
        if csv.is_empty() {
            return String::new();
        }

        // 获取CSV的表头行
        let header_row = &csv[0];
        // 生成Markdown表格的分隔行，格式为 "|---|...|---|"
        let separator = format!("|{}|", vec!["---"; header_row.len()].join("|"));
        let mut md_table = Vec::new();

        // 添加表头行到Markdown表格中
        md_table.push(format!("| {} |", header_row.join(" | ")));

        // 添加分隔行到Markdown表格中
        md_table.push(separator);

        // 遍历CSV数据行（跳过表头行），并将每一行添加到Markdown表格中
        for data_row in csv.iter().skip(1) {
            md_table.push(format!("| {} |", data_row.join(" | ")));
        }

        // 将Markdown表格的每一行用换行符连接，形成最终的Markdown表格字符串
        md_table.join("\n")
    }

    /// 将 XLSX 文件转换为 Markdown 格式的字符串列表。
    ///
    /// 该函数读取指定的 XLSX 文件，并将其中的每个工作表转换为 Markdown 格式的字符串。
    /// 每个工作表的内容将被转换为一个 Markdown 字符串，并返回包含所有工作表 Markdown 字符串的 `BTreeMap`。
    ///
    /// # 参数
    /// - `xlsx_path`: XLSX 文件的路径，可以是任何实现了 `AsRef<Path>` 的类型。
    ///
    /// # 返回值
    /// - 返回 `Result<BTreeMap<String, String>>`，如果转换成功则返回 `Ok`，其中包含一个 `BTreeMap`：
    ///   - 键为工作表的名称。
    ///   - 值为对应工作表的 Markdown 格式字符串。
    /// - 如果转换过程中出现错误，则返回包含错误信息的 `Err`。
    pub fn xlsx_to_markdown(xlsx_path: impl AsRef<Path>) -> Result<BTreeMap<String, String>> {
        // 获取 XLSX 文件中的所有工作表名称
        let sheet_names = xlsx_get_sheet_names(xlsx_path.as_ref().to_string_lossy())
            .context("Failed to get sheet names from XLSX file")?;
        let mut res = BTreeMap::new();

        // 遍历每个工作表，将其转换为 Markdown 格式
        for sheet_name in sheet_names.iter() {
            // 将当前工作表转换为 CSV 格式的二维向量
            let csv: Vec<Vec<String>> =
                xlsx_to_csv(xlsx_path.as_ref().to_string_lossy(), sheet_name.to_string())
                    .map_err(|s| anyhow!(s.to_string()))?
                    .into_iter()
                    .map(|s| {
                        s.iter()
                            .map(|s| {
                                if s.is_empty() {
                                    // 如果单元格为空，则用 "-" 代替
                                    "-".to_string()
                                } else {
                                    // 去除单元格内容的前后空白字符
                                    s.trim().to_string()
                                }
                            })
                            .collect()
                    })
                    .filter(|s: &Vec<String>| {
                        let ss = s.clone().join("").replace("-", "");
                        !ss.is_empty()
                    })
                    .collect();

            // 将 CSV 格式的数据转换为 Markdown 格式的字符串
            let markdown_string = csv_to_markdown(&csv);

            // 将工作表名称和对应的 Markdown 字符串插入到结果映射中
            res.insert(sheet_name.to_string(), markdown_string);
        }

        // 转换成功，返回包含 Markdown 字符串和工作表名称的映射
        Ok(res)
    }

    /// 将 XLSX 文件转换为 Markdown 文件。
    ///
    /// 该函数读取指定的 XLSX 文件，并将其中的每个工作表转换为 Markdown 格式的文件。
    /// 每个工作表将生成一个对应的 `.md` 文件，文件名为工作表的名称。
    ///
    /// # 参数
    /// - `xlsx_path`: XLSX 文件的路径，可以是任何实现了 `AsRef<Path>` 的类型。
    /// - `output_path`: 输出 Markdown 文件的路径，可以是任何实现了 `AsRef<Path>` 的类型。
    ///
    /// # 返回值
    /// - 返回 `Result<()>`，如果转换成功则返回 `Ok(())`，否则返回包含错误信息的 `Err`。
    pub fn xlsx_to_markdown_write(
        xlsx_path: impl AsRef<Path>,
        output_path: impl AsRef<Path>,
    ) -> Result<()> {
        // 获取 XLSX 文件中的所有工作表名称
        let sheet_names = xlsx_get_sheet_names(xlsx_path.as_ref().to_string_lossy())
            .context("Failed to get sheet names from XLSX file")?;

        // 遍历每个工作表，将其转换为 Markdown 格式并保存为 `.md` 文件
        for sheet_name in sheet_names {
            // 将当前工作表转换为 CSV 格式的二维向量
            let csv: Vec<Vec<String>> =
                xlsx_to_csv(xlsx_path.as_ref().to_string_lossy(), sheet_name.to_string())
                    .map_err(|s| anyhow!(s.to_string()))?
                    .into_iter()
                    .map(|s| {
                        s.iter()
                            .map(|s| {
                                if s.is_empty() {
                                    // 如果单元格为空，则用 "-" 代替
                                    "-".to_string()
                                } else {
                                    // 去除单元格内容的前后空白字符
                                    s.trim().to_string()
                                }
                            })
                            .collect()
                    })
                    .filter(|s: &Vec<String>| {
                        let ss = s.clone().join("").replace("-", "");
                        !ss.is_empty()
                    })
                    .collect();

            // 将 CSV 格式的数据转换为 Markdown 格式的字符串
            let csv_string = csv_to_markdown(&csv);

            // 将 Markdown 字符串写入以工作表名称命名的 `.md` 文件
            if !output_path.as_ref().to_path_buf().exists() {
                std::fs::create_dir(output_path.as_ref().to_path_buf())?;
            }
            use crate::traits::traits::Str;
            let path = output_path
                .as_ref()
                .to_path_buf()
                .join(sheet_name.to_string().push_back(".md"));
            std::fs::write(path, csv_string)?;
        }

        // 转换成功，返回 `Ok(())`
        Ok(())
    }

    pub mod zip_xlsx {
        #![allow(warnings)]
        use fancy_regex::Regex;
        use std::fs::File;
        use std::io::{Read, Write};
        use zip::{write::FileOptions, ZipArchive, ZipWriter};

        // 从 ZIP 数据中读取指定文件的内容
        fn read_file_from_zip(zip_data: &[u8], path: &str) -> anyhow::Result<String> {
            let cursor = std::io::Cursor::new(zip_data);
            let mut archive = ZipArchive::new(cursor)?;
            let mut file = archive.by_name(path)?;
            let mut content = String::new();
            file.read_to_string(&mut content)?;
            Ok(content)
        }
        ///
        /// 添加数据到指定工作表的指定单元格。
        ///
        /// # 参数
        /// - `data`: 要添加到单元格的数据。
        /// - `col_name`: 单元格的列名，例如 "A"、"B" 等。
        /// - `row_idx`: 单元格的行索引，从 1 开始。
        /// - `in_xlsx_data`: 原始 XLSX 数据的字节数组。
        /// - `sheet_index`: 要修改的工作表索引，从 1 开始。
        /// - `out_xlsx_path`: 输出 XLSX 文件的路径，可以是任何实现了 `AsRef<Path>` 的类型。
        pub fn add_data(
            data: &str,
            col_name: &str,
            row_idx: usize,
            in_xlsx_data: &[u8], // 新增参数：传入原始 XLSX 数据
            sheet_index: usize,
            out_xlsx_path: &str,
        ) -> anyhow::Result<()> {
            // Step 1: Read and modify sharedStrings.xml
            let shared_strings_path = "xl/sharedStrings.xml";
            let shared_strings_content = read_file_from_zip(in_xlsx_data, shared_strings_path)?;

            let (modified_shared_strings, si_index) =
                add_string_to_shared_strings(&shared_strings_content, data)?;

            // Step 2: Read and modify sheet1.xml
            let sheet1_path = format!("xl/worksheets/sheet{}.xml", sheet_index);
            let sheet1_content = read_file_from_zip(in_xlsx_data, &sheet1_path)?;

            let _cell_ref = format!("{}{}", col_name, row_idx);
            let modified_sheet1 =
                update_or_create_cell(&sheet1_content, col_name, row_idx, si_index, None)?;

            // Step 3: Create new xlsx file
            create_xlsx(
                out_xlsx_path,
                in_xlsx_data, // 传入原始数据
                &modified_shared_strings,
                shared_strings_path,
                &modified_sheet1,
                &sheet1_path,
            )?;

            Ok(())
        }

        ///
        /// 单元格数据结构体，用于存储要添加到单元格的数据、列名和行索引。
        ///
        /// # 字段
        /// - `data`: 要添加到单元格的数据。
        /// - `col_name`: 单元格的列名，例如 "A"、"B" 等。
        /// - `row_idx`: 单元格的行索引，从 1 开始。
        ///
        /// # 示例
        /// ```rust
        /// - `data`: 要添加到单元格的数据。
        /// - `col_name`: 单元格的列名，例如 "A"、"B" 等。
        /// - `row_idx`: 单元格的行索引，从 1 开始。
        /// ```
        pub struct CellData {
            pub data: String,
            pub col_name: String,
            pub row_idx: usize,
            pub style_ref: Option<String>,  // 单元格引用，如 "A5", "C20" 等
        }

        pub struct CellDataList {
            pub data: Vec<CellData>,
        }

        impl CellDataList {
            pub fn new() -> Self {
                Self { data: Vec::new() }
            }

            pub fn add(&mut self, data: String, col_name: String, row_idx: usize) {
                self.data.push(CellData {
                    data,
                    col_name,
                    row_idx,
                    style_ref: None,
                });
            }

            /// 添加单元格数据，并从指定单元格复制样式
            ///
            /// # 参数
            /// - `data`: 要添加到单元格的数据。
            /// - `col_name`: 单元格的列名，例如 "A"、"B" 等。
            /// - `row_idx`: 单元格的行索引，从 1 开始。
            /// - `style_pos`: 要复制样式的单元格位置，例如 "A5", "C20" 等。
            pub fn add_with_style(&mut self, data: String, col_name: String, row_idx: usize, style_pos: String) {
                self.data.push(CellData {
                    data,
                    col_name,
                    row_idx,
                    style_ref: Some(style_pos.to_string()),
                });
            }
        }
        ///
        /// 添加多个单元格数据到指定工作表。
        ///
        /// # 参数
        /// - `data`: 要添加到单元格的数据列表。
        /// - `in_xlsx_data`: 原始 XLSX 数据的字节数组。
        /// - `sheet_index`: 要修改的工作表索引，从 1 开始。
        /// - `out_xlsx_path`: 输出 XLSX 文件的路径，可以是任何实现了 `AsRef<Path>` 的类型。
        ///
        pub fn add_data_list(
            data: CellDataList,
            in_xlsx_data: &[u8], // 新增参数：传入原始 XLSX 数据
            sheet_index: usize,
            out_xlsx_path: &str,
        ) -> anyhow::Result<()> {
            if data.data.is_empty() {
                return Ok(());
            }

            // Step 1: Read sharedStrings.xml and sheet1.xml
            let shared_strings_path = "xl/sharedStrings.xml";
            let shared_strings_content = read_file_from_zip(in_xlsx_data, shared_strings_path)?;

            let sheet1_path = format!("xl/worksheets/sheet{}.xml", sheet_index);
            let sheet1_content_str = read_file_from_zip(in_xlsx_data, &sheet1_path)?;
            let mut sheet1_content = sheet1_content_str.to_string();

            // Step 2: Add all strings to sharedStrings.xml and get their indices
            let (modified_shared_strings, string_indices) =
                add_strings_to_shared_strings(&shared_strings_content, &data.data)?;

            // Step 3: Update all cells in sheet1.xml
            // Sort by row_idx first, then by column name to ensure correct order
            let mut sorted_data: Vec<_> = data.data.iter().zip(string_indices.iter()).collect();
            sorted_data.sort_by(|a, b| {
                let cell_a = a.0;
                let cell_b = b.0;
                // First sort by row
                if cell_a.row_idx != cell_b.row_idx {
                    cell_a.row_idx.cmp(&cell_b.row_idx)
                } else {
                    // Then sort by column (convert column name to number for comparison)
                    let col_a = super::col_to_num(cell_a.col_name.to_string());
                    let col_b = super::col_to_num(cell_b.col_name.to_string());
                    col_a.cmp(&col_b)
                }
            });

            for (cell, &si_index) in sorted_data {
                // Resolve style reference if present
                let style_index = if let Some(ref style_pos) = cell.style_ref {
                    get_cell_style_index(&sheet1_content, style_pos)?
                } else {
                    None
                };
                sheet1_content =
                    update_or_create_cell(&sheet1_content, &cell.col_name.to_string(), cell.row_idx, si_index, style_index)?;
            }

            // Step 4: Create new xlsx file
            create_xlsx(
                out_xlsx_path,
                in_xlsx_data, // 传入原始数据
                &modified_shared_strings,
                shared_strings_path,
                &sheet1_content,
                &sheet1_path,
            )?;

            Ok(())
        }

        /// 从 sheet XML 中读取指定单元格的样式索引
        fn get_cell_style_index(sheet_content: &str, cell_ref: &str) -> anyhow::Result<Option<usize>> {
            let cell_pattern = format!(r#"<c r="{}""#, cell_ref);
            if let Some(cell_start) = sheet_content.find(&cell_pattern) {
                // Find the style attribute (s="xx")
                let cell_section = &sheet_content[cell_start..];
                // Find the end of the cell tag
                if let Some(tag_end) = cell_section.find('>') {
                    let cell_tag = &cell_section[..tag_end];
                    // Look for s="xx" pattern
                    if let Some(s_start) = cell_tag.find(r#"s=""#) {
                        let after_s = &cell_tag[s_start + 3..];
                        if let Some(s_end) = after_s.find('"') {
                            let style_str = &after_s[..s_end];
                            if let Ok(style_idx) = style_str.parse::<usize>() {
                                return Ok(Some(style_idx));
                            }
                        }
                    }
                }
            }
            Ok(None)
        }

        fn add_strings_to_shared_strings(
            xml_content: &str,
            cells: &[CellData],
        ) -> anyhow::Result<(String, Vec<usize>)> {
            let mut result = xml_content.to_string();
            let mut indices = Vec::new();

            // Count existing <si> elements
            let si_count = result.matches("<si>").count() + result.matches("<si ").count();

            // Add each string and collect indices
            for (i, cell) in cells.iter().enumerate() {
                let index = si_count + i;
                indices.push(index);

                // Find the position of </sst> closing tag
                let sst_close_pos = result
                    .rfind("</sst>")
                    .ok_or_else(|| anyhow::anyhow!("</sst> closing tag not found"))?;

                // Insert new <si> before </sst>
                let new_si = format!(
                    "<si><t>{}</t></si>",
                    escape_xml(&cell.data)
                );
                let mut new_result = result[..sst_close_pos].to_string();
                new_result.push_str(&new_si);
                new_result.push_str(&result[sst_close_pos..]);

                result = new_result;
            }

            // Update count and uniqueCount attributes
            let new_count = si_count + cells.len();
            let count_re = Regex::new(r#"\bcount="\d+""#)?;
            let unique_count_re = Regex::new(r#"uniqueCount="\d+""#)?;

            let result = count_re.replace(&result, &format!(r#"count="{}""#, new_count));
            let result =
                unique_count_re.replace(&result, &format!(r#"uniqueCount="{}""#, new_count));

            Ok((result.to_string(), indices))
        }

        fn add_string_to_shared_strings(
            xml_content: &str,
            data: &str,
        ) -> anyhow::Result<(String, usize)> {
            // Find the position of </sst> closing tag
            let sst_close_pos = xml_content
                .rfind("</sst>")
                .ok_or_else(|| anyhow::anyhow!("</sst> closing tag not found"))?;

            // Count existing <si> elements (use pattern that won't match </si>)
            let si_count =
                xml_content.matches("<si>").count() + xml_content.matches("<si ").count();

            // Update count and uniqueCount attributes in sst tag using regex
            let new_count = si_count + 1;

            // Use regex with word boundaries to match count but not uniqueCount
            let count_re = Regex::new(r#"\bcount="\d+""#)?;
            let unique_count_re = Regex::new(r#"uniqueCount="\d+""#)?;

            let result = count_re.replace(xml_content, &format!(r#"count="{}""#, new_count));
            let result =
                unique_count_re.replace(&result, &format!(r#"uniqueCount="{}""#, new_count));

            // Insert new <si> before </sst>
            let new_si = format!("<si><t>{}</t></si>", escape_xml(data));

            // Find </sst> again after replacement
            let new_sst_close_pos = result
                .rfind("</sst>")
                .ok_or_else(|| anyhow::anyhow!("</sst> closing tag not found after update"))?;

            let mut final_result = result[..new_sst_close_pos].to_string();
            final_result.push_str(&new_si);
            final_result.push_str(&result[new_sst_close_pos..]);

            Ok((final_result.to_string(), si_count))
        }

        fn update_or_create_cell(
            xml_content: &str,
            col_name: &str,
            row_idx: usize,
            value: usize,
            style_index: Option<usize>,
        ) -> anyhow::Result<String> {
            let cell_ref = format!("{}{}", col_name, row_idx);
            let cell_pattern = format!(r#"<c r="{}""#, cell_ref);

            // Check if the cell already exists
            if let Some(cell_start) = xml_content.find(&cell_pattern) {
                // Cell exists, just update its value
                return update_existing_cell(xml_content, cell_ref, value, cell_start);
            }

            // Cell doesn't exist, need to create it
            // Check if the row exists
            let row_pattern = format!(r#"<row r="{}""#, row_idx);

            if let Some(row_start) = xml_content.find(&row_pattern) {
                // Row exists, add cell to it
                add_cell_to_existing_row(xml_content, row_idx, col_name, value, row_start, style_index)
            } else {
                // Row doesn't exist, create new row
                create_new_row_with_cell(xml_content, row_idx, col_name, value, style_index)
            }
        }

        fn update_existing_cell(
            xml_content: &str,
            cell_ref: String,
            value: usize,
            cell_start: usize,
        ) -> anyhow::Result<String> {
            // Find the <v> tag after this cell
            let v_start = xml_content[cell_start..]
                .find("<v>")
                .ok_or_else(|| anyhow::anyhow!("<v> tag not found in cell {}", cell_ref))?
                + cell_start;

            let v_end = xml_content[cell_start..]
                .find("</v>")
                .ok_or_else(|| anyhow::anyhow!("</v> tag not found in cell {}", cell_ref))?
                + cell_start;

            // Replace the content between <v> and </v>
            let mut result = xml_content[..v_start + 3].to_string();
            result.push_str(&value.to_string());
            result.push_str(&xml_content[v_end..]);

            Ok(result)
        }

        fn add_cell_to_existing_row(
            xml_content: &str,
            row_idx: usize,
            col_name: &str,
            value: usize,
            row_start: usize,
            style_index: Option<usize>,
        ) -> anyhow::Result<String> {
            // Find the end of this row (</row>)
            let row_end = xml_content[row_start..]
                .find("</row>")
                .ok_or_else(|| anyhow::anyhow!("</row> not found for row {}", row_idx))?
                + row_start;

            // Create the new cell element (compressed format like original)
            let cell_ref = format!("{}{}", col_name, row_idx);
            let style_attr = if let Some(s) = style_index {
                format!(r#" s="{}""#, s)
            } else {
                String::new()
            };
            let new_cell = format!(
                r#"<c r="{}"{} t="s"><v>{}</v></c>"#,
                cell_ref, style_attr, value
            );

            // Insert the cell before </row>
            let mut result = xml_content[..row_end].to_string();
            result.push_str(&new_cell);
            result.push_str(&xml_content[row_end..]);

            Ok(result)
        }

        fn create_new_row_with_cell(
            xml_content: &str,
            row_idx: usize,
            col_name: &str,
            value: usize,
            style_index: Option<usize>,
        ) -> anyhow::Result<String> {
            // Find the position to insert the new row
            // Look for </sheetData> tag and insert before it
            let sheet_data_end = xml_content
                .find("</sheetData>")
                .ok_or_else(|| anyhow::anyhow!("</sheetData> not found"))?;

            // Create the new row with cell (compressed format like original)
            let cell_ref = format!("{}{}", col_name, row_idx);
            let style_attr = if let Some(s) = style_index {
                format!(r#" s="{}""#, s)
            } else {
                String::new()
            };
            let new_row = format!(
                r#"<row r="{}" spans="1:3" x14ac:dyDescent="0.3"><c r="{}"{} t="s"><v>{}</v></c></row>"#,
                row_idx, cell_ref, style_attr, value
            );

            let mut result = xml_content[..sheet_data_end].to_string();
            result.push_str(&new_row);
            result.push_str(&xml_content[sheet_data_end..]);

            Ok(result)
        }

        fn create_xlsx(
            output_path: &str,
            xlsx_data: &[u8], // 新增参数：原始 XLSX 数据
            modified_shared_strings: &str,
            shared_strings_path: &str,
            modified_sheet1: &str,
            sheet1_path: &str,
        ) -> anyhow::Result<()> {
            let file = File::create(output_path)?;
            let mut zip = ZipWriter::new(file);
            let options =
                FileOptions::default().compression_method(zip::CompressionMethod::Deflated);

            // 读取原始 ZIP 并复制所有文件，替换修改过的文件
            let cursor = std::io::Cursor::new(xlsx_data);
            let mut archive = ZipArchive::new(cursor)?;

            for i in 0..archive.len() {
                let mut file = archive.by_index(i)?;
                let path = file.name().to_string();

                // 读取文件内容
                let mut buffer = Vec::new();
                file.read_to_end(&mut buffer)?;

                // 决定使用原始内容还是修改后的内容
                let content = if path == shared_strings_path {
                    modified_shared_strings.as_bytes()
                } else if path == sheet1_path {
                    modified_sheet1.as_bytes()
                } else {
                    &buffer
                };

                // 写入新 ZIP
                zip.start_file(path, options)?;
                zip.write_all(content)?;
            }

            zip.finish()?;
            Ok(())
        }

        fn escape_xml(s: &str) -> String {
            s.replace('&', "&amp;")
                .replace('<', "&lt;")
                .replace('>', "&gt;")
                .replace('"', "&quot;")
                .replace('\'', "&apos;")
        }
    }
}
#[cfg(feature = "xlsx")]
pub use xlsx::*;