// 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\"/>")); }
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 }
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"); } }
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); }
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(); } }
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; }
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)); }
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); }