void MainWindow::readExcelFile(QString file_name)
{
QXlsx::Document * xlsx = new QXlsx::Document(file_name);
QSqlQuery query;
for (int table_id = number_of_tables - 1; table_id >= 0; --table_id)
{
if (!xlsx->selectSheet(table_names.at(table_id)))
{
// qDebug() << table_names.at(table_id) << " not found";
continue;
}
QXlsx::CellRange range = xlsx->dimension();
/*
if (range.columnCount() != visible_columns.at(table_id))
{
// qDebug() << table_names.at(table_id) << " size not correct";
continue;
}
*/
int rowCount = range.rowCount();
for (int i = 1; i <= rowCount; ++i)
{
QString q = "INSERT INTO " + table_names.at(table_id) + " (";
for (int j = 1; j < column_names.at(table_id).size(); ++j)
{
q += column_names.at(table_id).at(j).first;
if (j < column_names.at(table_id).size() - 1)
{
q += ", ";
}
else
{
q += ") VALUES(";
}
}
for (int j = 1; j < column_names.at(table_id).size(); ++j)
{
q += ":" + column_names.at(table_id).at(j).first;
if (j < column_names.at(table_id).size() - 1)
{
q += ", ";
}
else
{
q += ");";
}
}
// qDebug() << q;
query.prepare(q);
QString name = "";
for (int j = 1; j <= visible_columns.at(table_id); ++j)
{
QVariant value = xlsx->read(i, j);
// qDebug() << ":" + column_names.at(table_id).at(j).first << " : " << value.toString();
query.bindValue(":" + column_names.at(table_id).at(j).first, value);
if (table_id == 0 && column_names.at(table_id).at(j).first == "Name")
{
name = value.toString();
}
}
if (table_id == 0)
{
QString name1 = refineString1(name);
QString name2 = refineString2(name1);
query.bindValue(":Name1", QVariant(name1));
// qDebug() << ":Name1" << " : " << name1;
query.bindValue(":Name2", QVariant(name2));
// qDebug() << ":Name2" << " : " << name2;
}
query.exec();
}
}
delete xlsx;
}
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));
}
DATA::Agent LiliHelper::parseAgent(const QString aPath, const QStringList aAliases, const QString aSheet)
{
QXlsx::Document aDocument (aPath);
DATA::Agent aAgent;
QStringList aStringList;
if (aSheet.length() != 0)
{
aStringList.append(aSheet);
}
else
{
aStringList = aDocument.sheetNames();
}
for (auto aSheet : aStringList)
{
aDocument.selectSheet(aSheet);
QXlsx::CellRange aSheetRange (aDocument.dimension());
QHash<QString, QDate> aRefDateMap;
QDate aCurrentDate;
QString aNote;
for (int nRow = aSheetRange.firstRow(); nRow <= aSheetRange.lastRow(); ++nRow)
{
QVariant aCell = aDocument.read(nRow, 2);
const bool bFirst = aCell.type() == QVariant::String && s_aWeekDays.contains(aCell.toString());
if (bFirst)
{
if (aDocument.read(nRow, 19).type() == QVariant::String)
{
aNote = aDocument.read(nRow, 19).toString();
}
QString aCellRef = QXlsx::CellReference (nRow, 9).toString();
QVariant aDateVariant = aDocument.read(aCellRef);
// Looking for date without reference
if (!aCurrentDate.isValid() && aDateVariant.type() == QVariant::Date)
{
aCurrentDate = aDateVariant.toDate();
aRefDateMap.insert(aCellRef, aCurrentDate);
}
// Looking for date with reference
else if (aCurrentDate.isValid() && aDateVariant.type() == QVariant::String)
{
QRegularExpression aRx;
QRegularExpressionMatchIterator aRxIterator;
aRx.setPattern("=(\\w+\\d+)\\+(\\d+)");
aRxIterator = aRx.globalMatch(aDateVariant.toString());
if (aRxIterator.hasNext())
{
QRegularExpressionMatch aMatch = aRxIterator.next();
QString aReferencedCell = aMatch.captured(1);
if (aRefDateMap.contains(aReferencedCell))
{
aCurrentDate = aRefDateMap[aReferencedCell].addDays(aMatch.captured(2).toInt());
aRefDateMap.insert(aCellRef, aCurrentDate);
}
}
}
}
else if (aCurrentDate.isValid())
{
QVariant aNameVariant = aDocument.read(nRow, 1);
if (aNameVariant.type() == QVariant::String && aAliases.contains(aNameVariant.toString()))
{
int nHourHead = 2;
while (nHourHead <= 54)
{
QVariant aVariant = aDocument.read(nRow, nHourHead);
int nTempHead = nHourHead + 1;
if (aVariant.type() == QVariant::Double && aVariant.toInt() == 1)
{
QTime aStartTime (7, 0);
if (nHourHead > 2)
{
aStartTime = aStartTime.addSecs(1800 + (nHourHead - 3) * 900);
}
QTime aEndTime = aStartTime.addSecs(15 * 60);
aVariant = aDocument.read(nRow, nTempHead);
while (nTempHead <= 54 && aVariant.type() == QVariant::Double && aVariant.toInt() == 1)
{
aEndTime = aEndTime.addSecs(15 * 60);
++nTempHead;
aVariant = aDocument.read(nRow, nTempHead);
}
aAgent.getEvents().append(DATA::CalEvent (QDateTime (aCurrentDate, aStartTime),
QDateTime (aCurrentDate, aEndTime),
aNote));
}
nHourHead = nTempHead;
}
}
}
}
}
return aAgent;
}