xls_table.cpp

#include "xls_table.h"

QString osVersion()
{
#ifdef Q_OS_LINUX
    return "Linux";
#else
#ifdef Q_OS_DARWIN
    return "Mac OS X";
#else
#ifdef Q_OS_WIN
    return "Windows";
#else
    return "Other";
#endif
#endif
#endif
}

#ifdef WIN32

QList<QVariantList> XLS_Table::openXLS(QString name)
{
    //Ñîçäàåì îáúåêò Excel.Application
    QAxObject * excel = new QAxObject( "Excel.Application", nullptr );
    //Âûáðàñûâàåì èñêëþ÷åíèå, åñëè îáúåêò application_ íåâàëèäíûé
    //if( !excel ) throw std::exception( "Excel.Application" );
    //Ïîñëå ýòîãî Excel ïåðåñòàåò âûáðàñûâàòü ïðåäóïðåæäåíèÿ
    excel->dynamicCall( "SetDisplayAlerts", QVariant( false ) );
    //Ñîçäàåì îáúåêò Workbooks (êîëëåêöèÿ êíèã)
    QAxObject * workbooks = excel->querySubObject( "Workbooks" );
    //Âûáðàñûâàåì èñêëþ÷åíèå, åñëè îáúåêò workbooks íåâàëèäíûé
    //if( !workbooks ) throw std::exception( "Application.Workbooks" );

    QAxObject* workbook = workbooks->querySubObject( "Open(const QString&)", QString(name.replace('/', '\\')) );
    //Ïîëó÷àåì äîñòóï ê àêòèâíîìó ëèñòó
    QAxObject * worksheets = workbook->querySubObject( "Sheets" );
    const int xlWorksheet =-4167;
    const int count = worksheets->property("Count").toInt();
    QAxObject * worksheet = nullptr;
    for (int index = 1; index <= count; index++)
    {
        worksheet = worksheets->querySubObject( "Item(QVariant)", QVariant(index) );
        //Âûáðàñûâàåì èñêëþ÷åíèå, åñëè îáúåêò worksheet íåâàëèäíûé
        //if( !worksheet ) throw std::exception( "Workbook.ActiveSheet" );
        if (worksheet->property("Type").toInt() == xlWorksheet)
        {
            break;
        }
    }

    QList<QVariantList> result = getRowValues(worksheet);

    workbook->dynamicCall("Close()");
    excel->dynamicCall("Quit()");
    delete excel;
    return result;
}

void XLS_Table::saveXLS(QString name, vector<point> &data)
{
    //Ñîçäàåì îáúåêò Excel.Application
    QAxObject * excel = new QAxObject( "Excel.Application", nullptr );
    //Âûáðàñûâàåì èñêëþ÷åíèå, åñëè îáúåêò application_ íåâàëèäíûé
    //if( !excel ) throw std::exception( "Excel.Application" );
    //Óñòàíàâëèâàåì êîëè÷åñòâî ëèñòîâ íà íîâîé êíèãå ðàâíûì 1
    excel->dynamicCall( "SetSheetsInNewWorkbook(int)", QVariant( 1 ) );
    //Ïîñëå ýòîãî Excel ïåðåñòàåò âûáðàñûâàòü ïðåäóïðåæäåíèÿ
    excel->dynamicCall( "SetDisplayAlerts", QVariant( false ) );
    //Ñîçäàåì îáúåêò Workbooks (êîëëåêöèÿ êíèã)
    QAxObject * workbooks = excel->querySubObject( "Workbooks" );
    //Âûáðàñûâàåì èñêëþ÷åíèå, åñëè îáúåêò workbooks íåâàëèäíûé
    //if( !workbooks ) throw std::exception( "Application.Workbooks" );
    //Äîáàâëÿåì íîâóþ êíèãó
    QAxObject * workbook = workbooks->querySubObject( "Add()" );
    //Âûáðàñûâàåì èñêëþ÷åíèå, åñëè îáúåêò workbook íåâàëèäíûé
    //if( !workbook ) throw std::exception( "Workbooks.Add()" );
    //Ïîëó÷àåì äîñòóï ê àêòèâíîìó ëèñòó
    QAxObject * worksheet = workbook->querySubObject( "ActiveSheet" );
    //Âûáðàñûâàåì èñêëþ÷åíèå, åñëè îáúåêò worksheet íåâàëèäíûé
    //if( !worksheet ) throw std::exception( "Workbook.ActiveSheet" );

    setRowValues(worksheet, data);

    createChart(excel, workbook, worksheet, data);

    workbook->dynamicCall( "SaveAs(QVariant)", QVariant(name.replace('/', '\\')) );
    workbook->dynamicCall("Close()");
    excel->dynamicCall("Quit()");
    delete excel;
}

void XLS_Table::createChart(QAxObject *excel, QAxObject *workbook, QAxObject *worksheet, vector<point> &data)
{
    //Ñîçäàåì îáúåêò Charts (êîëëåêöèÿ îáëàñòåé äèàãðàìì)
    QAxObject * charts = workbook->querySubObject( "Charts" );
    //Âûáðàñûâàåì èñêëþ÷åíèå, åñëè îáúåêò charts íåâàëèäíûé
    //if( !charts ) throw std::exception( "Workbook.Charts" );
    //Äîáàâëÿåì íîâóþ îáëàñòü äèàãðàìì
    QAxObject * chart = charts->querySubObject( "Add()" );
    //Âûáðàñûâàåì èñêëþ÷åíèå, åñëè îáúåêò chart íåâàëèäíûé
    //if( !chart ) throw std::exception( "Charts.Add()" );
    //Óñòàíàâëèâàåì òèï îáëàñòè äèàãðàìì
    chart->dynamicCall( "ChartType", QVariant( "xlXYScatter" ) );

    //Ïîëó÷àåì äîñòóï ê òåêóùåìó ðÿäó îáëàñòè äèàãðàìì
    QAxObject * series = chart->querySubObject( "SeriesCollection(const QVariant&)", QVariant( 1 ) );
    //Âûáðàñûâàåì èñêëþ÷åíèå, åñëè îáúåêò series íåâàëèäíûé
    //if( !series ) throw std::exception( "SeriesCollection(const QVariant&)" );

    const double version = strtod(excel->property("Version").toString().split('.')[0].toStdString().c_str(), nullptr);
    if (version >= 12)
    {
        chart->querySubObject( "SeriesCollection(const QVariant&)", QVariant( 2 ) )->dynamicCall("Delete()");
    }

    const int count = data.size();
    QAxObject * x1 = worksheet->querySubObject( "Cells( int, int )", 1, 1 );
    QAxObject * x2 = worksheet->querySubObject( "Cells( int, int )", count, 1 );
    QAxObject * y1 = worksheet->querySubObject( "Cells( int, int )", 1, 2 );
    QAxObject * y2 = worksheet->querySubObject( "Cells( int, int )", count, 2 );
    QVariantList params;
    params.append(x1->asVariant());
    params.append(x2->asVariant());

    //Ïîëó÷àåì îáúåêò Range äëÿ îñè X â âèäå QVariant
    QVariant source = worksheet->dynamicCall( "Range(const QVariant&, const QVariant&)", params );
    //Óñòàíàâëèâàåì äèàïàçîí çíà÷àíèé X äëÿ ðÿäà
    series->dynamicCall( "XValues", source );
    //Ïîëó÷àåì îáúåêò Range äëÿ îñè Y â âèäå QVariant
    params.clear();
    params.append(y1->asVariant());
    params.append(y2->asVariant());
    source = worksheet->dynamicCall( "Range(const QVariant&, const QVariant&)", params );
    //Óñòàíàâëèâàåì äèàïàçîí çíà÷àíèé Y äëÿ ðÿäà
    series->dynamicCall( "Values", source );
}

QList<QVariantList> XLS_Table::getRowValues( QAxObject * worksheet )
{
    QList<QVariantList> data;	//Data list from excel, each QVariantList is worksheet row
    bool isEmpty;	//when all the cells of row are empty, it means that file is at end (of course, it maybe not right for different excel files. it's just criteria to calculate somehow row count for my file)
    int row = 0;

    do
    {
        QVariantList dataRow;
        isEmpty = true;
        row++;
        for (int column = 1; column <= 2; column++)
        {
            QAxObject* cell = worksheet->querySubObject("Cells( int, int )", row, column);
            QString value = cell->property("Value").toString();
            if (!value.isEmpty() && isEmpty)
            {
                isEmpty = false;
            }
            dataRow.append(value);
        }
        data.append(dataRow);
    }
    while (!isEmpty);
    return data;
}

void XLS_Table::setRowValues( QAxObject * worksheet, vector<point> data )
{
    const int count = data.size();
    for (int index = 0; index < count; index++)
    {
        worksheet->querySubObject( "Cells( int, int )", index + 1, 1 )->dynamicCall("SetValue", QVariant(data[index].x));
        worksheet->querySubObject( "Cells( int, int )", index + 1, 2 )->dynamicCall("SetValue", QVariant(data[index].y));
    }
}

XLS_Table::XLS_Table() = default;

#endif