-
-
Notifications
You must be signed in to change notification settings - Fork 347
Home
Jay Two edited this page Oct 11, 2019
·
28 revisions
- If you would like to participate in the wiki, please submit an issue or proceed with a Pull Request. 😄
1️⃣ Basic reading data.
QXlsx::Document xlsx("ReadExcel.xlsx");
if (xlsx.load())
{
int row = 1; int col = 1;
Cell* cell = xlsx.cellAt(row, col);
if (cell != NULL)
{
QVariant readValue = cell->readValue();
// Type of readValue is string, number, date/time, etc.
// See QVariant::type()
}
}
2️⃣ Basic writing data.
QXlsx::Document xlsx;
QVariant writeValue = QString("hello");
int row = 1; int col = 1;
xlsx.write(row, col, writeValue);
3️⃣ Add sheet
xlsx.addSheet("added sheet"); // current sheet is 'added sheet'.
xlsx.write(2, 2, "hello");
4️⃣ Read sheet data
xlsx.selectSheet("added sheet"); // current sheet is 'added sheet'.
Cell* cell = xlsx.cellAt(row, col);
if (cell != NULL)
{
QVariant readValue = cell->readValue();
}
5️⃣ Read all sheets data
int sheetIndexNumber = 0;
foreach( QString curretnSheetName, xlsxDoc.sheetNames() )
{
// get current sheet
QXlsx::AbstractSheet* currentSheet = xlsxDoc.sheet( curretnSheetName );
if ( NULL == currentSheet )
continue;
// get full cells of current sheet
int maxRow = -1;
int maxCol = -1;
currentSheet->workbook()->setActiveSheet( sheetIndexNumber );
Worksheet* wsheet = (Worksheet*) currentSheet->workbook()->activeSheet();
if ( NULL == wsheet )
continue;
QString strSheetName = wsheet->sheetName(); // sheet name
qDebug() << strSheetName;
QVector<CellLocation> clList = wsheet->getFullCells( &maxRow, &maxCol );
QVector< QVector<QString> > cellValues;
for (int rc = 0; rc < maxRow; rc++)
{
QVector<QString> tempValue;
for (int cc = 0; cc < maxCol; cc++)
{
tempValue.push_back(QString(""));
}
cellValues.push_back(tempValue);
}
for ( int ic = 0; ic < clList.size(); ++ic )
{
CellLocation cl = clList.at(ic); // cell location
int row = cl.row - 1;
int col = cl.col - 1;
QSharedPointer<Cell> ptrCell = cl.cell; // cell pointer
// value of cell
QVariant var = cl.cell.data()->value();
QString str = var.toString();
cellValues[row][col] = str;
}
for (int rc = 0; rc < maxRow; rc++)
{
for (int cc = 0; cc < maxCol; cc++)
{
QString strCell = cellValues[rc][cc];
qDebug() << "( row : " << rc << ", col : " << cc << ") "
<< strCell; // display cell value
}
}
sheetIndexNumber++;
}
6️⃣ Date and time (1)
using namespace QXlsx;
Document doc;
doc.addSheet("TestWorksheet");
QVariant expected = QDateTime::currentDateTimeUtc();
assert(doc.write("A1", expected));
QVariant actual;
auto cell = doc.cellAt("A1"); // get cell pointer.
if ( cell != nullptr)
actual = cell->readValue();
qDebug() << (actual == expected ? "Values are the same" : "Values are Not the same");
qDebug() << "Expected:" << expected.toString() << expected.type();
qDebug() << "Actual:" << actual.toString() << actual.type();
doc.saveAs("EA.xlsx");
- Output
Values are Not the same
Expected: "2019-10-11T12:24:57.422Z" QVariant::QDateTime
Actual: "43749.89233127315" QVariant::double
7️⃣ Date and time (2)
QString fileName = QUuid::createUuid().toString() + ".xlsx";
QFile::remove(fileName);
auto docW = new QXlsx::Document(fileName);
docW->addSheet("TestWorksheet");
QVariant expected = QDateTime::currentDateTimeUtc();
assert(docW->write("A1", expected));
docW->save();
delete docW;
auto docR = new QXlsx::Document(fileName);
docR->load();
QVariant actual;
auto cell = docR->cellAt("A1"); // get cell pointer.
if ( cell != nullptr)
actual = cell->readValue();
auto expectedTime = expected.toDateTime();
auto actualTime = actual.toDateTime();
qDebug() << (actualTime == expectedTime ? "Values are the same" : "Values are Not the same");
qDebug() << "Expected:" << expectedTime.toString(Qt::DateFormat::ISODate);
qDebug() << "Actual:" << actualTime.toString(Qt::DateFormat::ISODate);
delete docR;
QFile::remove(fileName);
- Output
expected.type() : QVariant::QDateTime
actual.type() : QVariant::QTime
Values are Not the same
Expected: "2019-10-11T12:38:23Z"
Actual: ""
8️⃣ date and time (3)
-
More example
-
Code
using namespace QXlsx;
Document doc;
doc.write( "A1", QVariant(QDateTime::currentDateTimeUtc()) );
doc.write( "A2", QVariant(double(10.5)) );
doc.write( "A3", QVariant(QDate(2019, 10, 9)) );
doc.write( "A4", QVariant(QTime(10, 9, 5)) );
doc.write( "A5", QVariant((int) 40000) );
qDebug() << "doc.read()";
qDebug() << doc.read( 1, 1 ).type() << doc.read( 1, 1 );
qDebug() << doc.read( 2, 1 ).type() << doc.read( 2, 1 );
qDebug() << doc.read( 3, 1 ).type() << doc.read( 3, 1 );
qDebug() << doc.read( 4, 1 ).type() << doc.read( 4, 1 );
qDebug() << doc.read( 5, 1 ).type() << doc.read( 5, 1 );
qDebug() << "\n";
qDebug() << "doc.cellAt()->value()";
qDebug() << doc.cellAt( 1, 1 )->value().type() << doc.cellAt( 1, 1 )->value();
qDebug() << doc.cellAt( 2, 1 )->value().type() << doc.cellAt( 2, 1 )->value();
qDebug() << doc.cellAt( 3, 1 )->value().type() << doc.cellAt( 3, 1 )->value();
qDebug() << doc.cellAt( 4, 1 )->value().type() << doc.cellAt( 4, 1 )->value();
qDebug() << doc.cellAt( 5, 1 )->value().type() << doc.cellAt( 5, 1 )->value();
doc.saveAs("datetime.xlsx");
- Output
doc.read()
QVariant::QDateTime QVariant(QDateTime, QDateTime(2019-10-12 01:25:59.047 대한민국 표준시 Qt::LocalTime))
QVariant::double QVariant(double, 10.5)
QVariant::QDate QVariant(QDate, QDate("2019-10-09"))
QVariant::QTime QVariant(QTime, QTime("10:09:05.000"))
QVariant::double QVariant(double, 40000)
doc.cellAt()->value()
QVariant::double QVariant(double, 43750.1)
QVariant::double QVariant(double, 10.5)
QVariant::double QVariant(double, 43747)
QVariant::double QVariant(double, 0.422975)
QVariant::double QVariant(double, 40000)