#ifndef _Excel_h_
#define _Excel_h_

#include <ctime>
#include <fstream>
#include <iomanip>
#include <iostream>
#include <sstream>
#include <string>
#include <vector>
#include <Xlnt/Xlnt.h>
#include <plugin/Xlnt/include/xlnt/cell/cell_type.hpp>


//Vector<Vector<String>> data = {
//      {"One1", "One2", "10"}, {"Two1", "Two2", "20"}, {"Three1", "Three2", "30"}};
//
//  Vector<String> headers = {"Name", "Surname", "Money"};
//
//  saveToXLSX(data, headers);
void SaveToXLStoFile(const String fileNameXlsx, const Vector<Vector<String>>& data, const Vector<String>& headers) {
    xlnt::workbook wb;
    xlnt::worksheet ws = wb.active_sheet();

    auto EstimateCellWidth = [](const std::string& text) -> int {
        return static_cast<int>(text.size()) + 1;
    };

    Time now = GetSysTime();
    String sheetName = Format("%04d.%02d.%02d %02d-%02d-%02d",
        now.year, now.month, now.day,
        now.hour, now.minute, now.second);
    ws.title(sheetName.ToStd());

    std::vector<int> columnWidths;
    for (int row = 0; row < data.GetCount(); ++row){
        columnWidths.resize( max(static_cast<int>(columnWidths.size()), static_cast<int>(data[row].size())));
    }
    


    xlnt::style text_style = wb.create_style("text_style");
    text_style.number_format().general();
    
    xlnt::style number_style = wb.create_style("number_style");
    number_style.number_format().number();

    for (int col = 0; col < headers.GetCount(); ++col) {
        xlnt::cell_reference ref(col + 1, 1);
        auto valueStr = headers[col].ToStd();
        ws.cell(ref).value(valueStr);
        ws.cell(ref).font(xlnt::font().bold(true));
        columnWidths[col] = EstimateCellWidth(valueStr);
    }

    int shiftHeader = 0;
    if (headers.GetCount() > 0) ++shiftHeader;

    for (int row = 0; row < data.GetCount(); ++row) {
        const auto& rowData = data[row];
        for (int col = 0; col < rowData.GetCount(); ++col) {
            std::string valueStr = data[row][col].ToStd();
            columnWidths[col] = max(columnWidths[col],EstimateCellWidth(valueStr));

            char* end = nullptr;
            double num = std::strtod(valueStr.c_str(), &end);
            const auto color = xlnt::rgb_color(255, 255, 204);

            if (end != valueStr.c_str() && *end == '\0' && data[row][col].Find("E")==-1) {
                ws.cell(xlnt::cell_reference(col + 1, row + 1 + shiftHeader)).value(num);
                //ws.cell(xlnt::cell_reference(col + 1, row + 1 + shiftHeader)).fill(xlnt::fill().solid(color));
         
                if (std::floor(num) == num) {
                    ws.cell(xlnt::cell_reference(col + 1, row + 1 + shiftHeader)).number_format(xlnt::number_format::number());
                } else {
                    ws.cell(xlnt::cell_reference(col + 1, row + 1 + shiftHeader)).number_format(xlnt::number_format::number_00());
                }
                
            } else {
                ws.cell(xlnt::cell_reference(col + 1, row + 1 + shiftHeader)).value(valueStr);
            }
        }
    }


    for (std::size_t col = 0; col < columnWidths.size(); ++col) {
        int width = columnWidths[col];
        if (width > 0) {
            ws.column_properties(xlnt::column_t(col + 1)).width = width * 1.0 + 1.0;
        }
    }


    wb.save(fileNameXlsx.ToStd());
}


void SaveToXLS(const Vector<Vector<String>>& data, const Vector<String>& headers) {
    FileSel fs;
    fs.ReadOnlyOption(true);
    fs.MkDirOption(false);
    fs.ActiveDir(GetExeFolder());
    fs.EditFileName(true);
    fs.Types("Excel files\t*.xlsx");

    if (!fs.ExecuteSaveAs()) return;
    SaveToXLStoFile(fs, data, headers);
}


//  std::vector<std::vector<std::string>> data = {
//      {"One1", "One2", "10"}, {"Two1", "Two2", "20"}, {"Three1", "Three2", "30"}};
//
//  std::vector<int> columnWidths = {20, 20, 10};
//
//  saveToXLSX(data, columnWidths, "output.xlsx");

void saveToXmlXls(const std::vector<std::vector<std::string>>& data,
                const std::vector<int>& columnWidths,
                const std::string& filename) {
    std::ofstream file(filename);
    if (!file.is_open()) return;

    file << "<?xml version=\"1.0\"?>\n";
    file << "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\n";
    file << "          xmlns:o=\"urn:schemas-microsoft-com:office:office\"\n";
    file << "          xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\n";
    file << "          xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\n";


    file << "  <Styles>\n";
    file << "    <Style ss:ID=\"BoldStyle\">\n";
    file << "      <Font ss:Bold=\"1\"/>\n";
    file << "    </Style>\n";
    file << "  </Styles>\n";


    file << "  <Worksheet ss:Name=\"Sheet1\">\n";
    file << "    <Table>\n";


    for (size_t i = 0; i < columnWidths.size(); ++i) {
        file << "      <Column ss:Width=\"" << columnWidths[i] << "\"/>\n";
    }


    for (size_t rowIndex = 0; rowIndex < data.size(); ++rowIndex) {
        const auto& row = data[rowIndex];
        file << "      <Row>\n";
        for (const auto& cell : row) {
            if (rowIndex == 0) {
                file << "        <Cell ss:StyleID=\"BoldStyle\">"
                    << "<Data ss:Type=\"String\">" << cell << "</Data></Cell>\n";
            } else {
                file << "        <Cell><Data ss:Type=\"String\">"
                    << cell << "</Data></Cell>\n";
            }
        }
        file << "      </Row>\n";
    }

    file << "    </Table>\n";
    file << "  </Worksheet>\n";
    file << "</Workbook>\n";
    file.close();
}



#endif
