Need help on rendering chrono date with custom format to excel

[BharathIO]

Hi, can anyone help me write a date to Excel where the cell has the custom format as shown below?

I have Chrono NaiveDate value as input / year,month,day individual values.

[MathNya]

@BharathIO Thank you for contacting us. Try this one.

fn issue_172() {
    let value = convert_date(2024, 2, 3, 10 ,59, 00);
    let mut numbering_format = NumberingFormat::default();
    numbering_format.set_format_code("dd-mmm-yy");

    let mut book = umya_spreadsheet::new_file();
    let mut sheet = book.get_sheet_mut(&0).unwrap();
    sheet.get_cell_mut("A1").set_value_number(value);
    sheet.get_style_mut("A1").set_numbering_format(numbering_format);

    let result = sheet.get_formatted_value("A1");
    dbg!(&result);
}

fn convert_date(year: i32, month: i32, day: i32, hours: i32, minutes: i32, seconds: i32)-> f64
{
    let mut year = year;
    let mut month = month;
    let myexcel_base_date = 2415020;
    let excel1900is_leap_year = 1;

    // Julian base date Adjustment
    if month > 2 {
        month -= 3;
    } else {
        month += 9;
        year -= 1;
    }

    // Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
    let century = (year.to_string()[0..2]).parse::<i32>().unwrap();
    let decade = (year.to_string()[2..4]).parse::<i32>().unwrap();

    let excel_date = ((146097 * century) / 4) as i32 + ((1461 * decade) / 4) as i32 + ((153 * month + 2) / 5) as i32 + day + 1721119 - myexcel_base_date + excel1900is_leap_year;
    let excel_time = (((hours * 3600) + (minutes * 60) + seconds)) as f64 / 86400 as f64;

    return (excel_date as f64 + excel_time) as f64;
}

convert_date() will be added to the next version.

[BharathIO]

Thanks, @MathNya, one more question. How to handle timezones during date conversion? I need to format to PST/IST/UTC formats before writing to excel.

[MathNya]

@BharathIO Excel does not seem to retain the time zone. It is processed in UTC. For PST, I think it can be handled by subtracting 8 hours.

[BharathIO]

Thanks, convert_date function is working as expected.

[Expurple]

For a nice chrono->Excel integration, you can also check out rust_xlsxwriter::IntoExcelDateTime. It's a really nice API. I ended up importing rust_xlsxwriter to get support for NaiveTime without figuring the math myself.