#[allow(warnings)]
#[cfg(feature = "xlsx")]
pub mod xlsx {
    use std::path::PathBuf;

    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
        }
    }
    ///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 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 {
            panic!("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 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 {
                panic!("Unexpected error at get sheet");
            }
            return None;
        } else {
            panic!("Unexpected error at read xlsx file");
        }
        return None;
    }

    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 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 {
                panic!("Unexpected error at get sheet");
            }
            return None;
        } else {
            panic!("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,
            }
        }
    }
    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);
        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);
        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 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 {
                panic!("Unexpected error at get sheet");
            }
        } else {
            panic!("Unexpected error at read xlsx file");
        }
        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 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 mut cell = sheet.get_cell_value_mut((c as u32, r as u32));
                row_vec.push(cell.get_value_lazy().to_string());
            }
            csv_data.push(row_vec);
        }
        return 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 Ok(sheet_data)= crate::xlsx::xlsx_to_csv(path, sheet_name.clone()){
                    btree_map.insert(sheet_name, sheet_data);
                }
            }
        }
        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();
        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();
        Ok(())
    }
}
#[cfg(feature = "xlsx")]
pub use xlsx::*;
