Home

QXlsx wiki

• If you would like to participate in the wiki, please submit an issue or proceed with a Pull Request. 😄

FAQ

• See FAQ for more information.

Examples

1️⃣ Reading cell data

using namespace QXlsx;

Document doc("ReadExcel.xlsx");
if (!doc.load())
	return false;

int row = 1; int col = 1;
QVariant var = doc.read( row, col );
// check type of var for more information
qDebug() << var.toString(); 

2️⃣ Writing cell data

QVariant writeValue = QString("hello"); // you can use QString, double(or int), QDateTime, QDate, QTime
int row = 1; int col = 1;
doc.write(row, col, writeValue);

doc.saveAs("datetime.xlsx");

3️⃣ Add sheet

doc.addSheet("added sheet"); // add a sheet. current sheet is 'added sheet'.
int row = 1; int col = 1;
QVariant var = doc.read( row, col );

4️⃣ Select sheet

doc.selectSheet("added sheet"); // select a sheet. current sheet is 'added sheet'.
int row = 1; int col = 1;
QVariant var = doc.read( row, col );

5️⃣ Read all sheets data

using namespace QXlsx;
Document xlsxDoc;

// ...

int sheetIndexNumber = 0;
foreach( QString currentSheetName, xlsxDoc.sheetNames() )
{
	// get current sheet 
	AbstractSheet* currentSheet = xlsxDoc.sheet( currentSheetName );
	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

• 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)

7️⃣ Colors of a cell

void printColor(Cell* cell)
{
    if ( NULL == cell )
        return;

     QColor clrForeGround = cell->format().patternForegroundColor();
     QColor clrBackGround = cell->format().patternBackgroundColor();

     if ( clrForeGround.isValid() &&
          clrBackGround.isValid() )
     {
           qDebug() << "[debug] color : " << clrForeGround << clrBackGround;
     }
}

8️⃣ sax reader method that uses less RAM memory.

void dump_all_sheets_sax(QXlsx::Document& doc)
{
    QXlsx::sax_options opt;
    opt.resolve_shared_strings = true;     // If there are many strings, set to false to save more RAM (but sharedString index may be output instead)
    opt.read_formulas_as_text  = true;     // Output formula as string instead of result value (set false if not needed)
    opt.stop_on_empty_sheetdata = false;   // Continue even if sheetData is empty

    const QStringList sheets = doc.sheetNames();
    qInfo() << "sheet count:" << sheets.size();

    for (const QString& sheet_name : sheets) {
        qInfo().noquote() << "\n=== SHEET:" << sheet_name << "===";

        const bool ok = doc.read_sheet_sax(
            sheet_name,
            opt,
            [&](const QXlsx::sax_cell& cell) -> bool {
                qDebug().noquote()
                << QString("%1!R%2C%3 = %4")
                        .arg(cell.sheet_name)
                        .arg(cell.row)
                        .arg(cell.col)
                        .arg(cell.value.toString());
                return true; // continue
            });

        qInfo() << "sheet done:" << sheet_name << "ok=" << ok;
    }
}