excel_alternative.rs

use csv::StringRecord;
use indicatif::{MultiProgress, ProgressBar};
use rayon::prelude::*;

use std::{
    collections::{BTreeMap, HashMap},
    io::Write,
    path::PathBuf,
    thread,
};

use chrono::{
    //Datelike,
    NaiveDate,
};

// https://github.com/jmcnamara/rust_xlsxwriter/issues/1
// https://rustxlsxwriter.github.io/index.html
use rust_xlsxwriter::{
    Color, DocProperties, ExcelDateTime, Format, FormatAlign, Workbook, Worksheet,
};

use crate::{
    display_aliquota, display_cst, get_tipo_de_operacao, indicador_de_origem_to_str, month_to_str,
    obter_descricao_da_natureza_da_bc_dos_creditos, obter_descricao_do_cfop,
    obter_descricao_do_cst, obter_descricao_do_tipo_de_credito, AnaliseDosCreditos,
    ConsolidacaoCST, DocsFiscais, MyResult, DECIMAL_VALOR, OUTPUT_DIRECTORY, OUTPUT_FILENAME,
};

use claudiofsr_lib::{get_style, num_digits, OptionExtension};

const FONT_SIZE: f64 = 12.0;
const HEADER_FONT_SIZE: f64 = 11.0;
const MAX_NUMBER_OF_ROWS: usize = 1_000_000;

/// Create excel xlsx file from data_efd, data_cst, data_nat.
///
/// data_efd can be divided into N Worksheets generated by N different threads.
pub fn create_xlsx(
    data_efd: &[DocsFiscais],
    data_cst: &[ConsolidacaoCST],
    data_nat: &[AnaliseDosCreditos],
    write: &mut dyn Write,
) -> MyResult<()> {
    let mut excel_file: PathBuf = [OUTPUT_DIRECTORY, OUTPUT_FILENAME].iter().collect();
    excel_file.set_extension("xlsx");
    writeln!(write, "Write Excel xlsx file: {:?}\n", excel_file.display())?;

    let mut workbook = Workbook::new();
    let formats: HashMap<String, Format> = create_formats()?;
    let properties = get_properties()?;
    workbook.set_properties(&properties);

    let multiprogressbar: MultiProgress = MultiProgress::new();

    let worksheets: Vec<Worksheet> =
        get_all_worksheets(data_efd, data_cst, data_nat, &formats, &multiprogressbar)?;

    for worksheet in worksheets {
        // Add the worksheets to the workbook.
        workbook.push_worksheet(worksheet);
    }

    workbook.save(excel_file)?;

    Ok(())
}

fn get_properties() -> MyResult<DocProperties> {
    // Create a datetime object.
    let date = ExcelDateTime::from_ymd(2023, 1, 1)?.and_hms(0, 0, 0)?;

    // Add it to the document metadata.
    let properties = DocProperties::new()
        .set_title("SPED EFD Contribuições em Excel")
        .set_subject("Informações extraídas de arquivos de SPED EFD Contribuições")
        .set_author("Claudio FSR (https://github.com/claudiofsr/rust-sped)")
        .set_keywords("SPED EFD Contribuições, Rust, Excel")
        .set_comment("Created with Rust and rust_xlsxwriter")
        .set_hyperlink_base("https://github.com/claudiofsr/rust-sped")
        .set_creation_datetime(&date);

    Ok(properties)
}

/// Generate worksheets with thread scope
fn get_all_worksheets(
    data_efd: &[DocsFiscais],
    data_cst: &[ConsolidacaoCST],
    data_nat: &[AnaliseDosCreditos],
    formats: &HashMap<String, Format>,
    multiprogressbar: &MultiProgress,
) -> MyResult<Vec<Worksheet>> {
    // Use std::thread in the following functions (these functions are independent of each other):
    let results = thread::scope(|s| {
        let thread_efd = s.spawn(|| -> MyResult<Vec<Worksheet>> {
            add_worksheet_efd(data_efd, formats, multiprogressbar, 0)
        });
        let thread_cst = s.spawn(|| -> MyResult<Vec<Worksheet>> {
            add_worksheet_cst(data_cst, formats, multiprogressbar, 1)
        });
        let thread_nat = s.spawn(|| -> MyResult<Vec<Worksheet>> {
            add_worksheet_nat(data_nat, formats, multiprogressbar, 2)
        });

        // Wait for background thread to complete.
        // Call join() on each handle to make sure all the threads finish.
        // join() returns immediately when the associated thread completes.

        let threads: Vec<_> = [thread_efd, thread_cst, thread_nat]
            .into_iter()
            .flat_map(|scoped_join_handle| scoped_join_handle.join())
            .collect();

        threads
    });

    let worksheets: Vec<Worksheet> = results
        .into_par_iter() // rayon: parallel iterator
        .flat_map(|result| match result {
            Ok(vec_ws) => vec_ws,
            Err(_) => panic!("Failed to generate worksheet!"),
        })
        .collect();

    Ok(worksheets)
}

// https://stackoverflow.com/questions/61230633/how-to-obtain-the-chunk-index-in-rayons-par-chunks-mut
fn add_worksheet_efd(
    data_efd: &[DocsFiscais],
    formats: &HashMap<String, Format>,
    multiprogressbar: &MultiProgress,
    index: usize,
) -> MyResult<Vec<Worksheet>> {
    let mut sheet_name = "Itens de Docs Fiscais".to_string();

    let progressbar: ProgressBar =
        multiprogressbar.insert(index, ProgressBar::new(data_efd.len().try_into()?));
    let style = get_style(0, 0, 35)?;
    progressbar.set_style(style);

    let msg: String = format!("Write Excel: {sheet_name}");
    progressbar.set_message(msg);

    let mut worksheets: Vec<Worksheet> = Vec::new();

    // Split a vector into smaller vectors of size N
    for (k, data) in data_efd.chunks(MAX_NUMBER_OF_ROWS).enumerate() {
        if k >= 1 {
            sheet_name = format!("Itens de Docs Fiscais {}", k + 1);
        }

        let mut worksheet1 = Worksheet::new();
        worksheet1.set_name(&sheet_name)?;
        let headers_efd: StringRecord = DocsFiscais::get_headers();
        let mut width_map: BTreeMap<u16, usize> = BTreeMap::new();
        create_headers(
            &headers_efd,
            &mut worksheet1,
            &mut width_map,
            formats,
            "data_efd",
        )?;

        for (j, colunas) in data.iter().enumerate() {
            // data.iter().enumerate().for_each(|(j, colunas)| {
            add_row_efd(j as u32, colunas, &mut worksheet1, formats, &mut width_map)?;
            progressbar.inc(1);
        }
        set_max_width(&mut worksheet1, &width_map, 1.26)?;
        worksheets.push(worksheet1)
    }

    progressbar.finish();
    Ok(worksheets)
}

fn add_worksheet_cst(
    data_cst: &[ConsolidacaoCST],
    formats: &HashMap<String, Format>,
    multiprogressbar: &MultiProgress,
    index: usize,
) -> MyResult<Vec<Worksheet>> {
    // CST: CONSOLIDAÇÃO DAS OPERAÇÕES POR CST
    let mut sheet_name = "Consolidação CST".to_string();

    let progressbar: ProgressBar =
        multiprogressbar.insert(index, ProgressBar::new(data_cst.len().try_into()?));
    let style = get_style(0, 0, 35)?;
    progressbar.set_style(style);

    let msg: String = format!("Write Excel: {sheet_name}");
    progressbar.set_message(msg);

    let mut worksheets: Vec<Worksheet> = Vec::new();

    // Split a vector into smaller vectors of size N
    for (k, data) in data_cst.chunks(MAX_NUMBER_OF_ROWS).enumerate() {
        if k >= 1 {
            sheet_name = format!("Consolidação CST {}", k + 1);
        }
        let mut worksheet2 = Worksheet::new();
        worksheet2.set_name(&sheet_name)?;
        let headers_cst: StringRecord = ConsolidacaoCST::get_headers();
        let mut width_map: BTreeMap<u16, usize> = BTreeMap::new();
        create_headers(
            &headers_cst,
            &mut worksheet2,
            &mut width_map,
            formats,
            "data_cst",
        )?;
        for (j, colunas) in data.iter().enumerate() {
            //data.iter().enumerate().for_each(|(j, colunas)| {
            add_row_cst(j as u32, colunas, &mut worksheet2, formats, &mut width_map)?;
            progressbar.inc(1);
        }
        set_max_width(&mut worksheet2, &width_map, 1.00)?;
        worksheets.push(worksheet2)
    }

    progressbar.finish();
    Ok(worksheets)
}

fn add_worksheet_nat(
    data_nat: &[AnaliseDosCreditos],
    formats: &HashMap<String, Format>,
    multiprogressbar: &MultiProgress,
    index: usize,
) -> MyResult<Vec<Worksheet>> {
    // NAT: Natureza da Base de Cálculo dos Créditos
    let mut sheet_name = "Análise dos Créditos".to_string();

    let progressbar: ProgressBar =
        multiprogressbar.insert(index + 1, ProgressBar::new(data_nat.len().try_into()?));
    let style = get_style(0, 0, 35)?;
    progressbar.set_style(style);

    let msg: String = format!("Write Excel: {sheet_name}");
    progressbar.set_message(msg);

    let mut worksheets: Vec<Worksheet> = Vec::new();

    // Split a vector into smaller vectors of size N
    for (k, data) in data_nat.chunks(MAX_NUMBER_OF_ROWS).enumerate() {
        if k >= 1 {
            sheet_name = format!("Análise dos Créditos {}", k + 1);
        }
        let mut worksheet3 = Worksheet::new();
        worksheet3.set_name(&sheet_name)?;
        let headers_nat: StringRecord = AnaliseDosCreditos::get_headers();
        let mut width_map: BTreeMap<u16, usize> = BTreeMap::new();
        create_headers(
            &headers_nat,
            &mut worksheet3,
            &mut width_map,
            formats,
            "data_nat",
        )?;
        for (j, colunas) in data.iter().enumerate() {
            //data.iter().enumerate().for_each(|(j, colunas)| {
            add_row_nat(j as u32, colunas, &mut worksheet3, formats, &mut width_map)?;
            progressbar.inc(1);
        }
        set_max_width(&mut worksheet3, &width_map, 1.00)?;
        worksheets.push(worksheet3)
    }

    progressbar.finish();
    Ok(worksheets)
}

fn create_default_formats() -> Vec<(&'static str, Format)> {
    let fmt_default: Format = Format::new()
        .set_align(FormatAlign::VerticalCenter)
        .set_font_size(FONT_SIZE);

    let fmt_center: Format = Format::new()
        .set_align(FormatAlign::Center)
        .set_align(FormatAlign::VerticalCenter)
        .set_font_size(FONT_SIZE);

    let fmt_date: Format = Format::new()
        .set_align(FormatAlign::Center)
        .set_align(FormatAlign::VerticalCenter)
        .set_num_format("dd/mm/yyyy")
        .set_font_size(FONT_SIZE);

    let fmt_number: Format = Format::new()
        .set_align(FormatAlign::VerticalCenter)
        .set_num_format("#,##0.00")
        .set_font_size(FONT_SIZE);

    let fmt_aliquota: Format = Format::new()
        .set_align(FormatAlign::VerticalCenter)
        .set_num_format("0.0000")
        .set_font_size(FONT_SIZE);

    let fmt_integer: Format = Format::new()
        .set_align(FormatAlign::Center)
        .set_align(FormatAlign::VerticalCenter)
        .set_num_format("#") // "0#": leading zeros
        .set_font_size(FONT_SIZE);

    let default_formats: Vec<(&str, Format)> = vec![
        ("default", fmt_default),
        ("center", fmt_center),
        ("date", fmt_date),
        ("number", fmt_number),
        ("aliquota", fmt_aliquota),
        ("integer", fmt_integer),
    ];

    default_formats
}

fn create_formats() -> MyResult<HashMap<String, Format>> {
    let color_header: u32 = u32::from_str_radix("c5d9f1", 16)?;
    let color_bcsoma: u32 = u32::from_str_radix("bfbfbf", 16)?;
    let color_descon: u32 = u32::from_str_radix("fff2cc", 16)?;
    let color_saldoc: u32 = u32::from_str_radix("f8cbad", 16)?; // rosa ('f8cbad') ou verde ('c4d79b')

    let fmt_header: Format = Format::new()
        .set_text_wrap()
        .set_align(FormatAlign::Center)
        .set_align(FormatAlign::VerticalCenter)
        .set_font_size(HEADER_FONT_SIZE)
        //.set_font_color(FormatColor::Black)
        .set_background_color(Color::RGB(color_header));

    let mut hash_map: HashMap<String, Format> = HashMap::from([("header".to_string(), fmt_header)]);

    let default_formats: Vec<(&str, Format)> = create_default_formats();

    for (name, default_format) in default_formats {
        let name_bcsoma: String = [name, "_bcsoma"].concat();
        let name_descon: String = [name, "_descon"].concat();
        let name_saldoc: String = [name, "_saldoc"].concat();

        // BG Color: Base de Cálculo dos Créditos - Alíquota Básica (Soma)
        let format_bcsoma: Format = default_format
            .clone()
            .set_background_color(Color::RGB(color_bcsoma))
            .clone();

        // BG Color: Crédito Disponível após Descontos
        let format_descon: Format = default_format
            .clone()
            .set_background_color(Color::RGB(color_descon))
            .clone();

        // BG Color: Saldo de Crédito Passível de Desconto ou Ressarcimento
        let format_saldoc: Format = default_format
            .clone()
            .set_background_color(Color::RGB(color_saldoc))
            .clone();

        hash_map.insert(name.to_string(), default_format); // BG Color: default color
        hash_map.insert(name_bcsoma, format_bcsoma);
        hash_map.insert(name_descon, format_descon);
        hash_map.insert(name_saldoc, format_saldoc);
    }

    Ok(hash_map)
}

fn create_headers(
    headers: &StringRecord,
    sheet: &mut Worksheet,
    width_map: &mut BTreeMap<u16, usize>,
    fmt: &HashMap<String, Format>,
    tipo: &str,
) -> MyResult<()> {
    let mut last_col: u16 = 0;
    let fmt_header = fmt.get("header").unwrap();

    for (idx, header) in headers.iter().enumerate() {
        //println!("header {idx:02}: {header}");
        sheet.write_string_with_format(0, idx as u16, header, fmt_header)?;
        let mut width = header.len();

        match tipo {
            "data_efd" => {
                // definir largura mínima de colunas específicas
                let valor_ou_aliquota: bool =
                    header.contains("Valor") || header.contains("Alíquota");
                if idx == 2 || header.contains("Período de Apuração") || valor_ou_aliquota {
                    width = 12;
                } else if header.contains("CNPJ") || header.contains("Data") {
                    width = 18;
                }
            }
            "data_cst" => {
                // definir largura mínima
                width = 12;
            }
            "data_nat" => {
                // definir largura mínima
                width = 12;

                // definir largura mínima de colunas específicas
                if idx == 5 {
                    width = 6;
                } else if idx >= 9 {
                    width = 18;
                }
            }
            _ => continue,
        }

        get_max_width(idx as u16, width, width_map)?;

        last_col = idx as u16;
    }

    sheet.autofilter(0, 0, 0, last_col)?;
    sheet.set_freeze_panes(1, 0)?;
    Ok(())
}

fn add_row_efd(
    row: u32,
    colunas: &DocsFiscais,
    sheet: &mut Worksheet,
    fmt: &HashMap<String, Format>,
    width_map: &mut BTreeMap<u16, usize>,
) -> MyResult<()> {
    let fmt_default = fmt.get("default").unwrap();
    let fmt_center = fmt.get("center").unwrap();
    let fmt_date = fmt.get("date").unwrap();
    let fmt_integer = fmt.get("integer").unwrap();
    let fmt_number = fmt.get("number").unwrap();
    let fmt_aliquota = fmt.get("aliquota").unwrap();

    let month_name = month_to_str(&colunas.mes);
    let indicador_de_origem = indicador_de_origem_to_str(&colunas.indicador_de_origem);
    let tipo_de_operacao = get_tipo_de_operacao(&colunas.tipo_de_operacao).to_string();
    let descricao_do_cst = obter_descricao_do_cst(&colunas.cst);
    let descricao_do_cfop = obter_descricao_do_cfop(&colunas.cfop);
    let descricao_do_tipo_de_credito =
        obter_descricao_do_tipo_de_credito(&colunas.tipo_de_credito, true);
    let descricao_da_nat_bc = obter_descricao_da_natureza_da_bc_dos_creditos(&colunas.natureza_bc);

    add_integer_column(row, 0, Some(row + 2), sheet, width_map, fmt_integer)?;
    add_strings_column(row, 1, &colunas.arquivo_efd, sheet, width_map, fmt_default)?;
    add_usizenb_column(row, 2, colunas.num_linha_efd, sheet, width_map, fmt_integer)?;
    add_strings_column(
        row,
        3,
        &colunas.estabelecimento_cnpj,
        sheet,
        width_map,
        fmt_center,
    )?;
    add_strings_column(
        row,
        4,
        &colunas.estabelecimento_nome,
        sheet,
        width_map,
        fmt_default,
    )?;
    add_naidate_column(
        row,
        5,
        colunas.periodo_de_apuracao,
        sheet,
        width_map,
        fmt_date,
    )?;
    add_integer_column(row, 6, colunas.ano, sheet, width_map, fmt_integer)?;
    add_integer_column(row, 7, colunas.trimestre, sheet, width_map, fmt_integer)?;
    add_strings_column(row, 8, month_name, sheet, width_map, fmt_center)?;
    add_strings_column(row, 9, &tipo_de_operacao, sheet, width_map, fmt_default)?;

    add_strings_column(row, 10, indicador_de_origem, sheet, width_map, fmt_default)?;
    add_integer_column(row, 11, colunas.cod_credito, sheet, width_map, fmt_integer)?;
    add_strings_column(
        row,
        12,
        &descricao_do_tipo_de_credito,
        sheet,
        width_map,
        fmt_default,
    )?;
    add_strings_column(row, 13, &colunas.registro, sheet, width_map, fmt_default)?;
    add_strings_column(row, 14, &descricao_do_cst, sheet, width_map, fmt_default)?;
    add_strings_column(row, 15, &descricao_do_cfop, sheet, width_map, fmt_default)?;
    add_strings_column(row, 16, &descricao_da_nat_bc, sheet, width_map, fmt_default)?;
    add_strings_column(
        row,
        17,
        &colunas.particante_cnpj,
        sheet,
        width_map,
        fmt_center,
    )?;
    add_strings_column(
        row,
        18,
        &colunas.particante_cpf,
        sheet,
        width_map,
        fmt_center,
    )?;
    add_strings_column(
        row,
        19,
        &colunas.particante_nome,
        sheet,
        width_map,
        fmt_default,
    )?;

    add_usizenb_column(row, 20, colunas.num_doc, sheet, width_map, fmt_default)?;
    add_strings_column(row, 21, &colunas.chave_doc, sheet, width_map, fmt_center)?;
    add_strings_column(
        row,
        22,
        &colunas.modelo_doc_fiscal,
        sheet,
        width_map,
        fmt_center,
    )?;
    add_integer_column(row, 23, colunas.num_item, sheet, width_map, fmt_integer)?;
    add_strings_column(row, 24, &colunas.tipo_item, sheet, width_map, fmt_default)?;
    add_strings_column(row, 25, &colunas.descr_item, sheet, width_map, fmt_default)?;
    add_strings_column(row, 26, &colunas.cod_ncm, sheet, width_map, fmt_center)?;
    add_strings_column(
        row,
        27,
        &colunas.nat_operacao,
        sheet,
        width_map,
        fmt_default,
    )?;
    add_strings_column(
        row,
        28,
        &colunas.complementar,
        sheet,
        width_map,
        fmt_default,
    )?;
    add_strings_column(
        row,
        29,
        &colunas.nome_da_conta,
        sheet,
        width_map,
        fmt_default,
    )?;

    add_naidate_column(row, 30, colunas.data_emissao, sheet, width_map, fmt_date)?;
    add_naidate_column(row, 31, colunas.data_lancamento, sheet, width_map, fmt_date)?;
    add_float64_column(row, 32, colunas.valor_item, sheet, width_map, fmt_number)?;
    add_float64_column(row, 33, colunas.valor_bc, sheet, width_map, fmt_number)?;
    add_float64_column(row, 34, colunas.aliq_pis, sheet, width_map, fmt_aliquota)?;
    add_float64_column(row, 35, colunas.aliq_cofins, sheet, width_map, fmt_aliquota)?;
    add_float64_column(row, 36, colunas.valor_pis, sheet, width_map, fmt_number)?;
    add_float64_column(row, 37, colunas.valor_cofins, sheet, width_map, fmt_number)?;
    add_float64_column(row, 38, colunas.valor_iss, sheet, width_map, fmt_number)?;
    add_float64_column(row, 39, colunas.valor_bc_icms, sheet, width_map, fmt_number)?;

    add_float64_column(row, 40, colunas.aliq_icms, sheet, width_map, fmt_aliquota)?;
    add_float64_column(row, 41, colunas.valor_icms, sheet, width_map, fmt_number)?;

    let mut height = FONT_SIZE + 3.0;

    if row == 0 {
        height = HEADER_FONT_SIZE + 40.0;
    };

    // Set the row height in Excel character units.
    sheet.set_row_height(row, height)?;
    Ok(())
}

fn add_row_cst(
    row: u32,
    colunas: &ConsolidacaoCST,
    sheet: &mut Worksheet,
    fmt: &HashMap<String, Format>,
    width_map: &mut BTreeMap<u16, usize>,
) -> MyResult<()> {
    let mut fmt_center = fmt.get("center").unwrap();
    let mut fmt_integer = fmt.get("integer").unwrap();
    let mut fmt_number = fmt.get("number").unwrap();

    if let Some(490 | 980) = colunas.cst {
        // BG Color: Base de Cálculo dos Créditos - Alíquota Básica (Soma)
        fmt_center = fmt.get("center_bcsoma").unwrap();
        fmt_integer = fmt.get("integer_bcsoma").unwrap();
        fmt_number = fmt.get("number_bcsoma").unwrap();
    }

    let month_name = month_to_str(&colunas.mes);
    let descricao_do_cst = display_cst(&colunas.cst);

    add_strings_column(row, 0, &colunas.cnpj_base, sheet, width_map, fmt_center)?;
    add_integer_column(row, 1, colunas.ano, sheet, width_map, fmt_integer)?;
    add_integer_column(row, 2, colunas.trimestre, sheet, width_map, fmt_integer)?;
    add_strings_column(row, 3, month_name, sheet, width_map, fmt_center)?;
    add_strings_column(row, 4, &descricao_do_cst, sheet, width_map, fmt_center)?;
    add_float64_column(row, 5, colunas.valor_item, sheet, width_map, fmt_number)?;
    add_float64_column(row, 6, colunas.valor_bc, sheet, width_map, fmt_number)?;
    add_float64_column(row, 7, colunas.valor_pis, sheet, width_map, fmt_number)?;
    add_float64_column(row, 8, colunas.valor_cofins, sheet, width_map, fmt_number)?;

    let mut height = FONT_SIZE + 3.0;

    if row == 0 {
        height = HEADER_FONT_SIZE + 40.0;
    };

    // Set the row height in Excel character units.
    sheet.set_row_height(row, height)?;
    Ok(())
}

fn add_row_nat(
    row: u32,
    colunas: &AnaliseDosCreditos,
    sheet: &mut Worksheet,
    fmt: &HashMap<String, Format>,
    width_map: &mut BTreeMap<u16, usize>,
) -> MyResult<()> {
    // BG Color: default color
    let mut fmt_default = fmt.get("default").unwrap();
    let mut fmt_center = fmt.get("center").unwrap();
    let mut fmt_integer = fmt.get("integer").unwrap();
    let mut fmt_number = fmt.get("number").unwrap();

    match colunas.natureza_bc {
        // BG Color: Base de Cálculo dos Créditos - Alíquota Básica (Soma)
        Some(101..=199 | 300) => {
            fmt_default = fmt.get("default_bcsoma").unwrap();
            fmt_center = fmt.get("center_bcsoma").unwrap();
            fmt_integer = fmt.get("integer_bcsoma").unwrap();
            fmt_number = fmt.get("number_bcsoma").unwrap();
        }

        // BG Color: Crédito Disponível após Descontos
        Some(221 | 225) => {
            fmt_default = fmt.get("default_descon").unwrap();
            fmt_center = fmt.get("center_descon").unwrap();
            fmt_integer = fmt.get("integer_descon").unwrap();
            fmt_number = fmt.get("number_descon").unwrap();
        }

        // BG Color: Saldo de Crédito Passível de Desconto ou Ressarcimento
        Some(301 | 305) => {
            fmt_default = fmt.get("default_saldoc").unwrap();
            fmt_center = fmt.get("center_saldoc").unwrap();
            fmt_integer = fmt.get("integer_saldoc").unwrap();
            fmt_number = fmt.get("number_saldoc").unwrap();
        }

        _ => {}
    }

    let month_name = month_to_str(&colunas.mes);
    let descricao_tipo_de_credito =
        obter_descricao_do_tipo_de_credito(&colunas.tipo_de_credito, false);
    let tipo_de_operacao = get_tipo_de_operacao(&colunas.tipo_de_operacao).to_string();
    let descricao_da_nat_bc = obter_descricao_da_natureza_da_bc_dos_creditos(&colunas.natureza_bc);

    let aliq_pis: String = display_aliquota(&colunas.aliq_pis);
    let aliq_cof: String = display_aliquota(&colunas.aliq_cofins);

    add_strings_column(row, 0, &colunas.cnpj_base, sheet, width_map, fmt_center)?;
    add_integer_column(row, 1, colunas.ano, sheet, width_map, fmt_integer)?;
    add_integer_column(row, 2, colunas.trimestre, sheet, width_map, fmt_integer)?;
    add_strings_column(row, 3, month_name, sheet, width_map, fmt_center)?;
    add_strings_column(row, 4, &tipo_de_operacao, sheet, width_map, fmt_default)?;
    add_strings_column(
        row,
        5,
        &descricao_tipo_de_credito,
        sheet,
        width_map,
        fmt_center,
    )?;
    add_integer_column(row, 6, colunas.cst, sheet, width_map, fmt_integer)?;
    add_strings_column(row, 7, &aliq_pis, sheet, width_map, fmt_center)?;
    add_strings_column(row, 8, &aliq_cof, sheet, width_map, fmt_center)?;
    add_strings_column(row, 9, &descricao_da_nat_bc, sheet, width_map, fmt_default)?;

    add_float64_column(row, 10, colunas.valor_bc, sheet, width_map, fmt_number)?;
    add_float64_column(
        row,
        11,
        colunas.valor_rbnc_trib,
        sheet,
        width_map,
        fmt_number,
    )?;
    add_float64_column(
        row,
        12,
        colunas.valor_rbnc_ntrib,
        sheet,
        width_map,
        fmt_number,
    )?;
    add_float64_column(
        row,
        13,
        colunas.valor_rbnc_exp,
        sheet,
        width_map,
        fmt_number,
    )?;
    add_float64_column(row, 14, colunas.valor_rb_cum, sheet, width_map, fmt_number)?;

    let mut height = FONT_SIZE + 3.0;

    if row == 0 {
        height = HEADER_FONT_SIZE + 50.0;
    };

    // Set the row height in Excel character units.
    sheet.set_row_height(row, height)?;
    Ok(())
}

fn add_strings_column(
    row: u32,
    column: u16,
    data: &str,
    sheet: &mut Worksheet,
    width_map: &mut BTreeMap<u16, usize>,
    fmt: &Format,
) -> MyResult<()> {
    sheet.write_string_with_format(row + 1, column, data, fmt)?;
    get_max_width(column, data.len(), width_map)?;
    Ok(())
}

fn add_naidate_column(
    row: u32,
    column: u16,
    date: Option<NaiveDate>,
    sheet: &mut Worksheet,
    width_map: &mut BTreeMap<u16, usize>,
    fmt_date: &Format,
) -> MyResult<()> {
    let date_len = 10; // '25/11/2022': max lenght = 10 char
    match date {
        Some(dt) => {
            //let (y, m, d) = (dt.year(), dt.month(), dt.day());
            //let date = ExcelDateTime::from_ymd(y as u16, m as u8, d as u8)?;
            //sheet.write_datetime_with_format(row + 1, column, &date, fmt_date)?
            sheet.write_datetime_with_format(row + 1, column, dt, fmt_date)?
        }
        None => sheet.write_blank(row + 1, column, fmt_date)?,
    };
    get_max_width(column, date_len, width_map)?;
    Ok(())
}

// https://stackoverflow.com/questions/40574512/non-scalar-cast-t-as-f64-when-doing-generic-multiplication
fn add_integer_column<T>(
    row: u32,
    column: u16,
    number: Option<T>,
    sheet: &mut Worksheet,
    width_map: &mut BTreeMap<u16, usize>,
    fmt_number: &Format,
) -> MyResult<()>
where
    T: ToString + Copy + std::ops::Mul<Output = T> + std::convert::Into<f64> + ilog::IntLog,
{
    let mut num_len = 1; // init value
    match number {
        Some(integer) => {
            num_len = num_digits(integer); // integer.to_string().len()
            sheet.write_number_with_format(row + 1, column, integer, fmt_number)?
        }
        None => sheet.write_blank(row + 1, column, fmt_number)?,
    };
    get_max_width(column, num_len, width_map)?;
    Ok(())
}

fn add_usizenb_column(
    row: u32,
    column: u16,
    number: Option<usize>,
    sheet: &mut Worksheet,
    width_map: &mut BTreeMap<u16, usize>,
    fmt_number: &Format,
) -> MyResult<()> {
    let mut num_len = 1; // init value
    match number {
        Some(usize_value) => {
            num_len = num_digits(usize_value); // usize_value.to_string().len()
            sheet.write_number_with_format(row + 1, column, usize_value as f64, fmt_number)?
        }
        None => sheet.write_blank(row + 1, column, fmt_number)?,
    };
    get_max_width(column, num_len, width_map)?;
    Ok(())
}

// https://docs.rs/xlsxwriter/0.3.5/xlsxwriter/struct.Worksheet.html#method.write_number
fn add_float64_column(
    row: u32,
    column: u16,
    number: Option<f64>,
    sheet: &mut Worksheet,
    width_map: &mut BTreeMap<u16, usize>,
    fmt_number: &Format,
) -> MyResult<()> {
    let mut num_len = 1; // init value
    match number {
        Some(f64_value) => {
            num_len = num_digits(f64_value as usize) + 4 + DECIMAL_VALOR; // f64_value.to_string().len()
            sheet.write_number_with_format(row + 1, column, f64_value, fmt_number)?
        }
        None => sheet.write_blank(row + 1, column, fmt_number)?,
    };
    get_max_width(column, num_len, width_map)?;
    Ok(())
}

fn get_max_width(column: u16, new: usize, width_map: &mut BTreeMap<u16, usize>) -> MyResult<()> {
    width_map
        .entry(column)
        .and_modify(|previous_value| {
            if new > *previous_value {
                *previous_value = new
            }
        })
        .or_insert(new);
    Ok(())
}

fn set_max_width(
    sheet: &mut Worksheet,
    width_map: &BTreeMap<u16, usize>,
    fator_de_correcao: f64,
) -> MyResult<()> {
    for (&k, &v) in width_map {
        sheet.set_column_width(k, (v as f64) * fator_de_correcao)?;
    }
    Ok(())
}