IMpos project. Part 011. Viewing the names of fuel. Export results to xlsx file.

QTableWidget, Qt, xlsx, export

View fuel names.

After processing requests for names, we form a table to display information to the user.

Indication of the process of executing queries to the petrol station databases will be used not only for viewing, but also when exporting to xlsx and when executing scripts that change names. To do this, add one more parameter to the constructor of the ViewFuelNameDialog dialog class that determines further actions. Let's define the list of actions as an enumeration.
tasklist.h

#ifndef TASKLIST_H
#define TASKLIST_H
 
enum taskList {                 //Список задач
    VIEW_NAME,                  //Просмотр наименований
    XLSX_EXPORT,                //Экспорт в xlsx
    UPDATE_FUEL_NAME            //Изменение наименований
};
 
#endif // TASKLIST_H

Let's change the signal handler accepted() buttonBoxView:

void FuelNameDialog::on_buttonBoxView_accepted()
{
    //Определяем дальнейший алгоритм работы в зависимости от выбранного checkBox
    int currentTask = (ui->radioButtonReport->isChecked()) ? VIEW_NAME : XLSX_EXPORT;
    //Диалог для отображения результатов и прогресса получения данных с АЗС
    ViewFuelNameDialog *viewFnDlg = new ViewFuelNameDialog(&listTerminals,currentTask, this);
    viewFnDlg->exec();
}

In the slotGetStatusThread slot, we add a check for the completion of operations for obtaining names and, depending on the type of task, we call the appropriate function.

    ui->progressBarGetFuel->setFormat("Обработано %v из %m. Ошибок "+QString::number(colError));
    //Проверяем что произошло получение информации от всех АЗС указанных в списке
    //И в зависимости от типа задачи запускаем соответствующую функцию
    //Сортируем список азс определив статическую функцию compare
    std::sort(m_listFuelName.begin(), m_listFuelName.end(),compare);
    if(ui->progressBarGetFuel->value() == m_connectionsList.size()){
        switch (m_currentTask) {
        case VIEW_NAME:
            showFuelName();
            break;
        case XLSX_EXPORT:
            exportXlsx();
            break;
        default:
            break;
        }
    }

Name mapping implementation function

//Отображение наименований
void ViewFuelNameDialog::showFuelName()
{
    QSqlQuery q;
    ui->groupBoxProgress->hide();
    ui->tableWidgetView->setColumnCount(4);
    ui->tableWidgetView->setHorizontalHeaderLabels(QStringList() <<"Резервуар"<<"Код"<<"Краткое"<<"Полное");
    ui->tableWidgetView->verticalHeader()->hide();
    int colAzs = m_listFuelName.size();
    for(int i = 0; i<colAzs; ++i ){
        //Добавляем строку с номером и адресом АЗС
        int row = ui->tableWidgetView->rowCount();
        ui->tableWidgetView->insertRow(row);
        QTableWidgetItem *itemAZS = new QTableWidgetItem(QString::number(m_listFuelName.at(i).terminalID())+" "+m_listFuelName.at(i).azsName());
        itemAZS->setTextAlignment(Qt::AlignHCenter);
        itemAZS->setBackground(QColor("#aaff7f"));
        //Объединяем ячейки
        ui->tableWidgetView->setSpan(row,0,1,4);
        ui->tableWidgetView->setItem(row,0,itemAZS);
        for(int j = 0; j<m_listFuelName.at(i).listFuels().size();++j){
            //Заполняем строки наименованиями
            int rowName = ui->tableWidgetView->rowCount();
            ui->tableWidgetView->insertRow(rowName);
            ui->tableWidgetView->setItem(rowName,0, new QTableWidgetItem(QString::number(m_listFuelName.at(i).listFuels().at(j).tankID())));
            ui->tableWidgetView->item(rowName,0)->setTextAlignment(Qt::AlignCenter);
            ui->tableWidgetView->setItem(rowName,1, new QTableWidgetItem(QString::number(m_listFuelName.at(i).listFuels().at(j).fuelID())));
            ui->tableWidgetView->item(rowName,1)->setTextAlignment(Qt::AlignCenter);
            ui->tableWidgetView->setItem(rowName,2, new QTableWidgetItem(m_listFuelName.at(i).listFuels().at(j).shortName()));
            ui->tableWidgetView->setItem(rowName,3, new QTableWidgetItem(m_listFuelName.at(i).listFuels().at(j).name()));
            ui->tableWidgetView->resizeColumnToContents(3);
        }
    }
    ui->tableWidgetView->verticalHeader()->setDefaultSectionSize(ui->tableWidgetView->verticalHeader()->minimumSectionSize());
    ui->groupBoxView->show();
}

Export results to XSLX file.

To export the results to an xlsx file, we will use the QXlsx library https://qtexcel.github.io/QXlsx/ . I settled on it after looking at examples of use and available instructions for connecting to the project. I compiled the examples and quickly figured out the simplest use of the library.
Connection instructions are available here https://qtexcel.github.io/QXlsx/HowToSetProject.html .

Briefly, it looks like this:
Clone sources from github

git clone https://github.com/j2doll/QXlsx.git

Copy the contents of the QXlsx folder (header, source folder and QXlsx.pri, QXlsx.pro files) to our project folder.

Add to our project file (*.pro)

# QXlsx code for Application Qt project
QXLSX_PARENTPATH=./         # current QXlsx path is . (. means curret directory)
QXLSX_HEADERPATH=./header/  # current QXlsx header path is ./header/
QXLSX_SOURCEPATH=./source/  # current QXlsx source path is ./source/
include(./QXlsx.pri)
 

In the file in which we will use the library, add header files.

#include "xlsxdocument.h"
#include "xlsxchartsheet.h"
#include "xlsxcellrange.h"
#include "xlsxchart.h"
#include "xlsxrichstring.h"
#include "xlsxworkbook.h"
using namespace QXlsx;

The library is ready to use.

void ViewFuelNameDialog::exportXlsx()
{
    Document xlsx;          // Будущий документ
    Format format;          // Формат обычных ячеек
    Format formatMerge;     // Формат объединенной ячейки
    Format formatTitle;     // Формат заголовка
 
    //Задаем параметры форматирования
    format.setHorizontalAlignment(Format::AlignHCenter);
    format.setVerticalAlignment(Format::AlignVCenter);
    format.setBorderStyle(Format::BorderThin);
 
    formatMerge.setPatternBackgroundColor(QColor("#aaff7f"));
    formatMerge.setHorizontalAlignment(Format::AlignHCenter);
    formatMerge.setVerticalAlignment(Format::AlignVCenter);
    formatMerge.setBorderStyle(Format::BorderThin);
 
    formatTitle.setHorizontalAlignment(Format::AlignHCenter);
    formatTitle.setVerticalAlignment(Format::AlignVCenter);
    formatTitle.setFontBold(true);
    formatTitle.setBorderStyle(Format::BorderThin);
    formatTitle.setPatternBackgroundColor(QColor("#A9BCF5"));
 
    //Необходимо отметить что нумерация строк и столбцов в xlsx документе начинается с 1
    //Устанавливаем ширину столбцов
    xlsx.setColumnWidth(1,10);
    xlsx.setColumnWidth(4,30);
 
    int columnCount = headers.size();
    int colAzs = m_listFuelName.size();
    //Заполняем заголовки
    for(int i =0; i<columnCount; ++i){
        xlsx.write(1,i+1, headers.at(i),formatTitle);
    }
 
    //Табличная часть
    int rowX=2;
    for(int i=0; i<colAzs; ++i){
        xlsx.write(rowX,1, QString::number(m_listFuelName.at(i).terminalID())+" "+m_listFuelName.at(i).azsName());
        CellRange cellRange = CellRange(rowX,1,rowX,columnCount);
        xlsx.mergeCells(cellRange, formatMerge);
        for(int j = 0; j<m_listFuelName.at(i).listFuels().size(); ++j) {
            rowX++;
            xlsx.write(rowX,1,m_listFuelName.at(i).listFuels().at(j).tankID(),format);
            xlsx.write(rowX,2,m_listFuelName.at(i).listFuels().at(j).fuelID(),format);
            xlsx.write(rowX,3,m_listFuelName.at(i).listFuels().at(j).shortName(),format);
            xlsx.write(rowX,4,m_listFuelName.at(i).listFuels().at(j).name(),format);
        }
    }
    QFile xlsxFile;
    //Создаем абсолютный путь к файлу
    xlsxFile.setFileName(QApplication::applicationDirPath()+"/"+"FuelName.xlsx");
    xlsx.saveAs(xlsxFile.fileName()); // Сохраняем документ
#ifdef Q_OS_WIN
    QDesktopServices::openUrl(QUrl("file:///"+xlsxFile.fileName(), QUrl::TolerantMode));
#else
    QDesktopServices::openUrl(QUrl("file://"+xlsxFile.fileName(), QUrl::TolerantMode));
#endif
    this->reject();
}

We get the FuelName.xlsx file, which is opened by the program by default.

Project archive

iMposCh011.zip

.