Beispiel #1
0
// For a solid fill, Excel reverses the role of foreground and background colours
void StylesTest::testSolidFillBackgroundColor()
{
    QXlsx::Styles styles;
    QXlsx::Format *format = styles.createFormat();
    format->setPatternBackgroundColor(QColor(Qt::red));
    styles.addFormat(format);

    QByteArray xmlData = styles.saveToXmlData();

    QVERIFY(xmlData.contains("<patternFill patternType=\"solid\"><fgColor rgb=\"FFff0000\"/>"));
}
Beispiel #2
0
void StylesTest::testAddFormat()
{
    QXlsx::Styles styles;

    for (int i=0; i<10; ++i) {
        QXlsx::Format *format = styles.createFormat();
        format->setFontBold(true);
        styles.addFormat(format);
    }

    QByteArray xmlData = styles.saveToXmlData();
    QVERIFY2(xmlData.contains("<cellXfs count=\"2\">"), ""); //Note we have a default one
}
Beispiel #3
0
void orderWidget::genPrice()
{
    if(orderNumber->currentIndex()>-1){
        QString path = QFileDialog::getExistingDirectory(this, "Выберите папку для сохранения", "C:\\");

        QXlsx::Document xlsx;

        xlsx.write("A1", "Заказ №");
        xlsx.write("A2", "Дата:");
        xlsx.write("A4", "Фамилия:");
        xlsx.write("A5", "Имя:");
        xlsx.write("A6", "Отчество:");

        QXlsx::Format form;
        form.setHorizontalAlignment(QXlsx::Format::AlignHCenter);

        xlsx.mergeCells("B8:D8", form);
        xlsx.write("B8", "Товарные позиции:", form);
        xlsx.write("B9", "Название", form);
        xlsx.write("C9", "Количество", form);
        xlsx.write("D9", "Сумма", form);

        xlsx.setColumnWidth(1, 12);
        xlsx.setColumnWidth(2, 25);
        xlsx.setColumnWidth(3, 12);
        xlsx.setColumnWidth(4, 15);

        ordr ord = ords[orderNumber->currentIndex()];

        xlsx.write("B1", ord.orderNum);
        xlsx.write("B2", ord.orderDate);
        xlsx.write("B4", ord.userSur);
        xlsx.write("B5", ord.userName);
        xlsx.write("B6", ord.userMid);

        int i = 0;

        for (i = 0; i < ord.itms.size(); i++){
            xlsx.write(QString("B%1").arg(i+10), ord.itms.at(i).name);
            xlsx.write(QString("C%1").arg(i+10), ord.itms.at(i).count);
            xlsx.write(QString("D%1").arg(i+10), ord.itms.at(i).sum);
        }

        xlsx.write(QString("A%1").arg(i+12), "Итого:");
        xlsx.write(QString("D%1").arg(i+12), ord.orderSum);


        xlsx.saveAs(path+"\\CompSave.xlsx");
    }
}
Beispiel #4
0
void StylesTest::testAddFormat2()
{
    QXlsx::Styles styles;

    QXlsx::Format *format = styles.createFormat();
    format->setNumberFormat("h:mm:ss AM/PM"); //builtin 19
    styles.addFormat(format);

    QCOMPARE(format->numberFormatIndex(), 19);

    QXlsx::Format *format2 = styles.createFormat();
    format2->setNumberFormat("aaaaa h:mm:ss AM/PM"); //custom
    styles.addFormat(format2);

    QCOMPARE(format2->numberFormatIndex(), 176);
}
Beispiel #5
0
void DBsearchsetting::on_seve_excel_btn_clicked()
{
    QFileDialog filepath(this);
    filepath.setAcceptMode(QFileDialog::AcceptSave);
    filepath.setNameFilter("*.xlsx");

    QStringList headeritem;
    for(int i=0;i<Setting_Query->columnCount();i++){
        headeritem.insert(i,Setting_Query->headerData(i,Qt::Horizontal).toString());
    }
    QString S_filepath;
    S_filepath = filepath.getSaveFileName(this,QString(""),QString(""),QString("*.xlsx"));
    if(S_filepath != ""){
        QXlsx::Document xlsx;
        for(int i=1;i<=Setting_Query->columnCount();i++){
            QXlsx::Format format;
            format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
            format.setPatternBackgroundColor(QColor("gray"));
            xlsx.setColumnWidth(i,20);
            xlsx.write(1,i,headeritem.at(i-1),format);
        }
        for(int i=0;i<Setting_Query->rowCount();i++){
            for(int j=0;j<Setting_Query->columnCount();j++){
            QXlsx::Format format;
            format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
            QString data =Setting_Query->index(i,j).data(0).toString();
            xlsx.write(i+2,j+1,data,format);
            }
        }
        bool saveresult;
        saveresult = xlsx.saveAs(S_filepath);
        QMessageBox messagebox;
        if(saveresult){
            messagebox.setText(tr("save ok"));
        }else {
            messagebox.setText(tr("save error"));
        }
        messagebox.exec();
    }
}
Beispiel #6
0
int main(int argc, char** argv)
{
    QGuiApplication(argc, argv);

    QXlsx::Document xlsx;
    xlsx.setColumn(1, 4, 20.0);

    QXlsx::Format header;
    header.setFontBold(true);
    header.setFontSize(20);

    //Custom number formats
    QStringList numFormats;
    numFormats<<"Qt #"
             <<"yyyy-mmm-dd"
            <<"$ #,##0.00"
           <<"[red]0.00";
    xlsx.write(1, 1, "Raw data", header);
    xlsx.write(1, 2, "Format", header);
    xlsx.write(1, 3, "Shown value", header);
    for (int i=0; i<numFormats.size(); ++i) {
        int row = i+2;
        xlsx.write(row, 1, 100.0);
        xlsx.write(row, 2, numFormats[i]);
        QXlsx::Format format;
        format.setNumberFormat(numFormats[i]);
        xlsx.write(row, 3, 100.0, format);
    }

    //Builtin number formats
    xlsx.addSheet();
    xlsx.setColumn(1, 4, 20.0);
    xlsx.write(1, 1, "Raw data", header);
    xlsx.write(1, 2, "Builtin Format", header);
    xlsx.write(1, 3, "Shown value", header);
    for (int i=0; i<50; ++i) {
        int row = i+2;
        int numFmt = i;
        xlsx.write(row, 1, 100.0);
        xlsx.write(row, 2, numFmt);
        QXlsx::Format format;
        format.setNumberFormatIndex(numFmt);
        xlsx.write(row, 3, 100.0, format);
    }

    xlsx.save();
    return 0;
}
Beispiel #7
0
void MainWindow::checkExcelFile(QString file_name)
{
    bool debug = false;
    QXlsx::Document * xlsx = new QXlsx::Document(file_name);
    QSqlQuery query;
    if (!xlsx->selectSheet("Check"))
    {
        return;
    }
    QXlsx::CellRange range = xlsx->dimension();
    int rowCount = range.rowCount();
//    int columnCount = range.columnCount();
    int columnCount = visible_columns.at(0);
/*
    if (check_box_check->isChecked() &&
        columnCount != visible_columns.at(0))
    {
        QMessageBox::information(this,tr(""), tr("Check columns do not fit database"));
    }
*/
    if (debug)
    {
        QMessageBox::information(this,tr("Rows * Columns"),
           QString::number(rowCount) + tr(" * ") + QString::number(columnCount));
    }
    int newCount = 0;
    int insertCount = 0;

    for (int i = 1; i <= rowCount; ++i)
    {
        QString name = xlsx->read(i, 1).toString();
        if (debug)
        {
            QMessageBox::information(this,tr("name"), name);
        }
        SearchResult result = searchCompany(name);
        if (debug)
        {
            QMessageBox::information(this,tr("Search result: "), result.information);
        }
        if (result.color != Qt::white)
        {
            for (int j = 1; j <= columnCount; ++j)
            {
                QVariant value = xlsx->read(i, j);
                if (!value.isValid())
                {
                    continue;
                }
                QXlsx::Format format = xlsx->cellAt(i, j)->format();
                format.setPatternBackgroundColor(result.color);
                xlsx->write(i, j, value, format);
            }
            // Department;
            xlsx->write(i, columnCount + 1, result.department);
            QXlsx::Format format = xlsx->cellAt(i, columnCount + 1)->format();
            format.setPatternBackgroundColor(result.color);
            xlsx->write(i, columnCount + 1, result.department, format);

            // Row(s)
            QVariant value(result.information);
            xlsx->write(i, columnCount + 2, value);
            format = xlsx->cellAt(i, columnCount + 2)->format();
            format.setPatternBackgroundColor(result.color);
            xlsx->write(i, columnCount + 2, value, format);
        }
        if ((result.color == Qt::white) // add to database
            && check_box_check->isChecked())
        {
            newCount++;
            QString q = "INSERT INTO " + table_names.at(0) + " (";
            for (int k = 1; k < column_names.at(0).size(); ++k)
            {
                q += column_names.at(0).at(k).first;
                if (k < column_names.at(0).size() - 1)
                {
                    q += ", ";
                }
                else
                {
                    q += ") VALUES(";
                }
            }
            for (int k = 1; k < column_names.at(0).size(); ++k)
            {
                q += ":" + column_names.at(0).at(k).first;
                if (k < column_names.at(0).size() - 1)
                {
                    q += ", ";
                }
                else
                {
                    q += ");";
                }
            }
            query.prepare(q);
//            qDebug() << q;
            QString name = "";
            for (int k = 1; k <= visible_columns.at(0); ++k)
            {
                QVariant value = xlsx->read(i, k);
                query.bindValue(":" + column_names.at(0).at(k).first, value);
//                qDebug() << ":" + column_names.at(0).at(k).first << " = " << value.toString();
                if (column_names.at(0).at(k).first == "Name")
                {
                    name = value.toString();
                }
            }
            QString name1 = refineString1(name);
            QString name2 = refineString2(name1);
            query.bindValue(":Name1", QVariant(name1));
            query.bindValue(":Name2", QVariant(name2));
            if (query.exec())
            {
                insertCount++;
            }
        }
    }
    xlsx->save();
    delete xlsx;
    QMessageBox::information(this,tr(""),
                             tr("Check ok: new = ") + QString::number(newCount) +
                             tr("; insert = ")      + QString::number(insertCount));
}
Beispiel #8
0
void IoslotValueExporter::toExcel()
{
    QXlsx::Document xlsx;

    // Title
    QXlsx::Format format;
    format.setFontSize(18);
    format.setFontBold(true);
    format.setHorizontalAlignment(QXlsx::Format::AlignLeft);
    xlsx.write(XLSX_PROGRAM_NAME_ROW, XLSX_KEY_COL, tr("Hydroponic system configurator"), format);

    format.setFontSize(10);
    format.setFontBold(false);
    format.setHorizontalAlignment(QXlsx::Format::AlignLeft);
    xlsx.write(XLSX_VERSION_ROW, XLSX_KEY_COL, tr("Version"), format);
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    xlsx.write(XLSX_VERSION_ROW, XLSX_VALUE_COL, tr("1.0"), format);

    format.setFontSize(12);
    format.setFontBold(true);
    format.setHorizontalAlignment(QXlsx::Format::AlignLeft);
    xlsx.write(XLSX_DEVICE_ADDRESS_ROW, XLSX_KEY_COL, tr("Address"), format);
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    xlsx.write(XLSX_DEVICE_ADDRESS_ROW, XLSX_VALUE_COL, d_address, format);

    QVector<bool> valueEmpty(d_ioslotManager->ioslotCount(), false);

    // Table header
    format.setFontSize(12);
    format.setFontBold(true);
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    format.setTopBorderStyle(QXlsx::Format::BorderThick);
    format.setBottomBorderStyle(QXlsx::Format::BorderThick);
    format.setLeftBorderStyle(QXlsx::Format::BorderThin);
    format.setRightBorderStyle(QXlsx::Format::BorderThin);

    int col = 1;
    xlsx.write(XLSX_HEADER_ROW, col, tr("Timestamp"), format);
    ++col;

    for (int num = 0; num < d_ioslotManager->ioslotCount(); ++num) {
        if (d_ioslotManager->ioslot(num)->type() == UnknownIoslotType) {
            valueEmpty[num] = true;
            continue;
        }

        const QString &name = d_ioslotManager->ioslot(num)->name();
        xlsx.write(XLSX_HEADER_ROW, col, name, format);
        ++col;
    }

    // Table content
    format.setFontSize(12);
    format.setFontBold(false);
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    format.setTopBorderStyle(QXlsx::Format::BorderThin);
    format.setBottomBorderStyle(QXlsx::Format::BorderThin);
    format.setLeftBorderStyle(QXlsx::Format::BorderThin);
    format.setRightBorderStyle(QXlsx::Format::BorderThin);

    int count = d_table->recordCount(d_from, d_to);
    const int limit = 100;

    int row = 0;
    for (int offset = 0; offset < count; offset += limit) {
        QList<IoslotValueRecord> records = d_table->records(d_from, d_to, limit, offset);
        QList<IoslotValueRecord>::iterator it = records.begin();

        for (; it != records.end(); ++it, ++row) {
            IoslotValueRecord &record = *it;

            int col = 1;
            xlsx.write(XLSX_DATA_START_ROW + row, col,
                       record.timestamp().toString("yyyy-MM-dd hh:mm:ss.zzz"),
                       format);
            ++col;

            const QList<IoslotValueRecord::RecordValue> &values = record.values();
            QList<IoslotValueRecord::RecordValue>::const_iterator it2 = values.begin();

            for (int num = 0; it2 != values.end(); ++it2, ++num) {
                if (valueEmpty[num]) continue;

                const IoslotValueRecord::RecordValue &value = *it2;
                xlsx.write(XLSX_DATA_START_ROW + row, col, value.second, format);
                ++col;
            }
        }

        Q_EMIT progress(offset, count);
    }

    (void)xlsx.saveAs(d_filename);
}