void dlgReports::report_sign()
{
Document xl(App::theApp()->tmplPath() + "tmpl_sign.xlsx");
Worksheet *sh;
// Лист "Финал" view --> viewReport_sign_total;
xl.selectSheet("Финал");
sh = xl.currentWorksheet();
sh->write("A3", QString(xl.read("A3").toString()).arg(_dateA.toString("dd.MM.yyyy")).arg(_dateZ.toString("dd.MM.yyyy")));
sh->write("O4", QString(xl.read("O4").toString()).arg(_dateX.toString("dd.MM.yyyy")));
int r1_t(10); int r2_t(0);
QString q_total = QString("call p_Report_sign_total('%1','%2');")
.arg(_dateA.toString("yyyy-MM-dd"))
.arg(_dateZ.toString("yyyy-MM-dd"));
queryToSheet(sh, q_total, r1_t, r2_t);
r1_t++;
sh->write(r2_t, 9, QString("=COUNT(I%1:I%2)").arg(r1_t).arg(r2_t - 1));
sh->write(r2_t,10, QString("=COUNT(J%1:J%2)").arg(r1_t).arg(r2_t - 1));
sh->write(r2_t,11, QString("=COUNT(K%1:K%2)").arg(r1_t).arg(r2_t - 1));
sh->write(r2_t,12, QString("=COUNT(L%1:L%2)").arg(r1_t).arg(r2_t - 1));
sh->write(r2_t,13, QString("=COUNT(M%1:M%2)").arg(r1_t).arg(r2_t - 1));
// Лист "Свод" view --> viewReport_sign_stat;
xl.selectSheet("Свод");
sh = xl.currentWorksheet();
sh->write("A3", QString(xl.read("A3").toString()).arg(_dateA.toString("dd.MM.yyyy")).arg(_dateZ.toString("dd.MM.yyyy")));
int r1_s(5); int r2_s(0);
QString q_stat = QString("call p_Report_sign_stat('%1','%2');")
.arg(_dateA.toString("yyyy-MM-dd"))
.arg(_dateZ.toString("yyyy-MM-dd"));
queryToSheet(sh, q_stat, r1_s, r2_s);
r1_s++;
sh->write(r2_s, 8, QString("=SUM(H%1:H%2)").arg(r1_s).arg(r2_s - 1));
xl.selectSheet("Финал");
save_report(xl, "sign");
qDebug() << "report_sign ok!";
}
QTXLSX_USE_NAMESPACE
int main()
{
//![0]
Document xlsx;
//![0]
//![1]
xlsx.setColumnWidth(1, 2, 40);
Format rAlign;
rAlign.setHorizontalAlignment(Format::AlignRight);
Format lAlign;
lAlign.setHorizontalAlignment(Format::AlignLeft);
xlsx.write("B3", 40, lAlign);
xlsx.write("B4", 30, lAlign);
xlsx.write("B5", 50, lAlign);
xlsx.write("A7", "SUM(B3:B5)=", rAlign);
xlsx.write("B7", "=SUM(B3:B5)", lAlign);
xlsx.write("A8", "AVERAGE(B3:B5)=", rAlign);
xlsx.write("B8", "=AVERAGE(B3:B5)", lAlign);
xlsx.write("A9", "MAX(B3:B5)=", rAlign);
xlsx.write("B9", "=MAX(B3:B5)", lAlign);
xlsx.write("A10", "MIN(B3:B5)=", rAlign);
xlsx.write("B10", "=MIN(B3:B5)", lAlign);
xlsx.write("A11", "COUNT(B3:B5)=", rAlign);
xlsx.write("B11", "=COUNT(B3:B5)", lAlign);
xlsx.write("A13", "IF(B7>100,\"large\",\"small\")=", rAlign);
xlsx.write("B13", "=IF(B7>100,\"large\",\"small\")", lAlign);
xlsx.write("A15", "SQRT(25)=", rAlign);
xlsx.write("B15", "=SQRT(25)", lAlign);
xlsx.write("A16", "RAND()=", rAlign);
xlsx.write("B16", "=RAND()", lAlign);
xlsx.write("A17", "2*PI()=", rAlign);
xlsx.write("B17", "=2*PI()", lAlign);
xlsx.write("A19", "UPPER(\"qtxlsx\")=", rAlign);
xlsx.write("B19", "=UPPER(\"qtxlsx\")", lAlign);
xlsx.write("A20", "LEFT(\"ubuntu\",3)=", rAlign);
xlsx.write("B20", "=LEFT(\"ubuntu\",3)", lAlign);
xlsx.write("A21", "LEN(\"Hello Qt!\")=", rAlign);
xlsx.write("B21", "=LEN(\"Hello Qt!\")", lAlign);
//![1]
//![2]
xlsx.addSheet("ArrayFormula");
Worksheet *sheet = xlsx.currentWorksheet();
for (int row=2; row<20; ++row) {
sheet->write(row, 2, QString(row%5+1, 'X')); //B2:B19
sheet->write(row, 3, QString(row%5+1, 'X')); //C2:C19
sheet->write(row, 5, 100.0 - row); //E2:E19
}
sheet->writeArrayFormula("C20", "{=SUM(IF((C2:C19=\"X\")*(B2:B19=\"X\"),1,0))}");
sheet->writeArrayFormula("F2:F19", "{=E2:E19*10}");
//![2]
//![3]
xlsx.save();
//![3]
return 0;
}