QTXLSX_USE_NAMESPACE
int main()
{
Document xlsx;
for (int i=1; i<=10; ++i) {
xlsx.write(i, 1, i);
xlsx.write(i, 2, i*i);
xlsx.write(i, 3, i*i*i);
}
Worksheet* sheet = xlsx.currentWorksheet();
sheet->writeFormula(CellReference(11, 1),
CellFormula(Formula::AVERAGE(CellRange(1, 1, 10, 1)),
CellReference::toString(11, 1),
CellFormula::SharedType));
sheet->writeFormula(CellReference(11, 2),
CellFormula(Formula::SUM(CellRange(1, 2, 10, 2)),
CellReference::toString(11, 2),
CellFormula::SharedType));
sheet->writeFormula(CellReference(11, 3),
CellFormula(Formula::COUNTIF(CellRange(1, 3, 10, 3), QString("\">50\"")),
CellReference::toString(11, 3),
CellFormula::SharedType));
xlsx.save();
return 0;
}
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;
}
//------------------------------------------------------------------------------
int main(int argc, char * const argv[])
{
Options opt = GetOptions(argc, argv);
// Log in
SpreadsheetService service(opt.user, opt.password,
"xeckollc-thirteen-" + VERSION);
// Get the spreadsheet we are interested in
Spreadsheet spreadsheet;
if(opt.spreadsheet_id.size()) {
spreadsheet = service.GetSpreadsheet(opt.spreadsheet_id);
} else {
vector<Spreadsheet> sheets = service.GetSpreadsheets();
int report_index = -1;
for (int i=0; i < sheets.size(); ++i) {
if(sheets[i].GetTitle() == opt.spreadsheet_name) {
report_index = i;
break;
}
}
if(report_index < 0) {
cerr << format("No spreadsheet named [%1%] found") %
opt.spreadsheet_name << endl;
return 1;
}
spreadsheet = sheets[report_index];
}
// Get the worksheet we are interested in. If we didn't specify one,
// use the first one in the spreadsheet
Worksheet worksheet;
if(opt.worksheet_id.size()) {
worksheet = spreadsheet.GetWorksheet(opt.worksheet_id);
} else {
vector<Worksheet> sheets = spreadsheet.GetWorksheets();
if(opt.worksheet_name.size()) {
int report_index = -1;
for (int i=0; i < sheets.size(); ++i) {
if(sheets[i].GetTitle() == opt.worksheet_name) {
report_index = i;
break;
}
}
if(report_index < 0) {
cerr << format("No worksheet named [%1%] found") %
opt.worksheet_name << endl;
return 1;
}
worksheet = sheets[report_index];
} else {
worksheet = sheets[0];
}
}
// Get 2 columns of Cells from selected worksheet
CellRange cells = worksheet.GetColumns(1, 2);
// Parse the cells for the data
map<string, int> on_air, off_air;
State state = NONE;
for (int row=cells.GetFirstRow(); row <= cells.GetLastRow(); ++row) {
Cell *label_cell = cells.GetCell(row, 1);
if(label_cell == NULL) {
state = NONE;
continue;
}
string label = label_cell->GetContent();
if(!label.size()) {
state = NONE;
continue;
}
if(label == ON_AIR_HEADER) {
state = ONAIR;
continue;
}
if(label == OFF_AIR_HEADER) {
state = OFFAIR;
continue;
}
Cell *value_cell = cells.GetCell(row, 2);
if(value_cell == NULL) {
continue;
}
string value = value_cell->GetContent();
int ivalue;
try {
ivalue = lexical_cast<int>(value.c_str());
} catch(bad_lexical_cast &) {
ivalue = 0;
}
switch (state) {
case ONAIR:
if(ivalue) {
on_air[label] = ivalue;
}
break;
case OFFAIR:
if(ivalue) {
off_air[label] = ivalue;
}
break;
case NONE:
break;
}
}
// Figure the start and stop dates from the worksheet name
string name = worksheet.GetTitle();
re::sregex rex = re::sregex::compile("(.+) +(.+)");
re::smatch matches;
if(!re::regex_match(name, matches, rex)) {
cerr << format("Worksheet name [%1%] does not look like a month and year") %
name << endl;
return 1;
}
string mon, yr;
int month, year;
mon = matches[1];
yr = matches[2];
year = lexical_cast<int>(yr.c_str());
if(year < 2000) {
year += 2000;
}
month = 0;
string months[] = {
"jan", "feb", "mar", "apr", "may", "jun",
"jul", "aug", "sep", "oct", "nov", "dec"
};
for (int i=0; i<12; ++i) {
if (iequals(mon, months[i])) {
month=i+1;
break;
}
}
if(month < 1) {
cerr << format("Worksheet name [%1%] does not contain a valid month") %
name << endl;
return 1;
}
date start_date(year, month, 1);
date stop_date = start_date.end_of_month();
stringstream start, stop;
date_facet *facet = new date_facet("%d %b");
start.imbue(locale(cout.getloc(), facet));
start << start_date;
opt.start_date = to_upper_copy(start.str());
facet = new date_facet("%d %b %Y");
stop.imbue(locale(cout.getloc(), facet));
stop << stop_date;
opt.stop_date = to_upper_copy(stop.str());
// Print the report
PrintReport(cout, opt, on_air, off_air);
return 0;
}
void CartesianPlotDock::setPlots(QList<CartesianPlot*> list) {
m_initializing = true;
m_plotList = list;
m_plot=list.first();
QList<TextLabel*> labels;
foreach(CartesianPlot* plot, list)
labels.append(plot->title());
labelWidget->setLabels(labels);
//if there is more then one plot in the list, disable the name and comment fields in the tab "general"
if (list.size()==1) {
ui.lName->setEnabled(true);
ui.leName->setEnabled(true);
ui.lComment->setEnabled(true);
ui.leComment->setEnabled(true);
ui.leName->setText(m_plot->name());
ui.leComment->setText(m_plot->comment());
} else {
ui.lName->setEnabled(false);
ui.leName->setEnabled(false);
ui.lComment->setEnabled(false);
ui.leComment->setEnabled(false);
ui.leName->setText("");
ui.leComment->setText("");
}
//show the properties of the first plot
this->load();
//update active widgets
backgroundTypeChanged(ui.cbBackgroundType->currentIndex());
//Deactivate the geometry related widgets, if the worksheet layout is active.
//Currently, a plot can only be a child of the worksheet itself, so we only need to ask the parent aspect (=worksheet).
//TODO redesign this, if the hierarchy will be changend in future (a plot is a child of a new object group/container or so)
Worksheet* w = dynamic_cast<Worksheet*>(m_plot->parentAspect());
if (w) {
bool b = (w->layout()==Worksheet::NoLayout);
ui.sbTop->setEnabled(b);
ui.sbLeft->setEnabled(b);
ui.sbWidth->setEnabled(b);
ui.sbHeight->setEnabled(b);
connect(w, SIGNAL(layoutChanged(Worksheet::Layout)), this, SLOT(layoutChanged(Worksheet::Layout)));
}
//SIGNALs/SLOTs
connect( m_plot, SIGNAL(aspectDescriptionChanged(const AbstractAspect*)), this, SLOT(plotDescriptionChanged(const AbstractAspect*)) );
connect( m_plot, SIGNAL(rectChanged(QRectF&)), this, SLOT(plotRectChanged(QRectF&)) );
connect( m_plot, SIGNAL(xAutoScaleChanged(bool)), this, SLOT(plotXAutoScaleChanged(bool)) );
connect( m_plot, SIGNAL(xMinChanged(float)), this, SLOT(plotXMinChanged(float)) );
connect( m_plot, SIGNAL(xMaxChanged(float)), this, SLOT(plotXMaxChanged(float)) );
connect( m_plot, SIGNAL(xScaleChanged(int)), this, SLOT(plotXScaleChanged(int)) );
connect( m_plot, SIGNAL(yAutoScaleChanged(bool)), this, SLOT(plotYAutoScaleChanged(bool)) );
connect( m_plot, SIGNAL(yMinChanged(float)), this, SLOT(plotYMinChanged(float)) );
connect( m_plot, SIGNAL(yMaxChanged(float)), this, SLOT(plotYMaxChanged(float)) );
connect( m_plot, SIGNAL(yScaleChanged(int)), this, SLOT(plotYScaleChanged(int)) );
connect( m_plot, SIGNAL(visibleChanged(bool)), this, SLOT(plotVisibleChanged(bool)) );
//range breaks
connect( m_plot, SIGNAL(xRangeBreakingEnabledChanged(bool)), this, SLOT(plotXRangeBreakingEnabledChanged(bool)) );
connect( m_plot, SIGNAL(xRangeBreaksChanged(CartesianPlot::RangeBreaks)), this, SLOT(plotXRangeBreaksChanged(CartesianPlot::RangeBreaks)) );
connect( m_plot, SIGNAL(yRangeBreakingEnabledChanged(bool)), this, SLOT(plotYRangeBreakingEnabledChanged(bool)) );
connect( m_plot, SIGNAL(yRangeBreaksChanged(CartesianPlot::RangeBreaks)), this, SLOT(plotYRangeBreaksChanged(CartesianPlot::RangeBreaks)) );
// Plot Area
connect( m_plot->plotArea(), SIGNAL(backgroundTypeChanged(PlotArea::BackgroundType)), this, SLOT(plotBackgroundTypeChanged(PlotArea::BackgroundType)) );
connect( m_plot->plotArea(), SIGNAL(backgroundColorStyleChanged(PlotArea::BackgroundColorStyle)), this, SLOT(plotBackgroundColorStyleChanged(PlotArea::BackgroundColorStyle)) );
connect( m_plot->plotArea(), SIGNAL(backgroundImageStyleChanged(PlotArea::BackgroundImageStyle)), this, SLOT(plotBackgroundImageStyleChanged(PlotArea::BackgroundImageStyle)) );
connect( m_plot->plotArea(), SIGNAL(backgroundBrushStyleChanged(Qt::BrushStyle)), this, SLOT(plotBackgroundBrushStyleChanged(Qt::BrushStyle)) );
connect( m_plot->plotArea(), SIGNAL(backgroundFirstColorChanged(QColor&)), this, SLOT(plotBackgroundFirstColorChanged(QColor&)) );
connect( m_plot->plotArea(), SIGNAL(backgroundSecondColorChanged(QColor&)), this, SLOT(plotBackgroundSecondColorChanged(QColor&)) );
connect( m_plot->plotArea(), SIGNAL(backgroundFileNameChanged(QString&)), this, SLOT(plotBackgroundFileNameChanged(QString&)) );
connect( m_plot->plotArea(), SIGNAL(backgroundOpacityChanged(float)), this, SLOT(plotBackgroundOpacityChanged(float)) );
connect( m_plot->plotArea(), SIGNAL(borderPenChanged(QPen&)), this, SLOT(plotBorderPenChanged(QPen&)) );
connect( m_plot->plotArea(), SIGNAL(borderOpacityChanged(float)), this, SLOT(plotBorderOpacityChanged(float)) );
connect( m_plot, SIGNAL(horizontalPaddingChanged(float)), this, SLOT(plotHorizontalPaddingChanged(float)) );
connect( m_plot, SIGNAL(verticalPaddingChanged(float)), this, SLOT(plotVerticalPaddingChanged(float)) );
m_initializing = false;
}