Qt优秀开源项目之二十四：EXCEL读写利器QXlsx

QXlsx是基于Qt5/Qt6的Excel文件（*.xlsx）的读写库。
github地址：https://github.com/QtExcel/QXlsx
QXlsx既可以编译成库，也可以直接引用源码QXlsx-master\QXlsx\QXlsx.pri
QXls提供了非常丰富的Examples，比如：

1️⃣ 读单元格数据

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️⃣ 写单元格数据

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️⃣ 添加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️⃣ 选择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️⃣ 读所有sheet的数据

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 sheetint maxRow = -1;int maxCol = -1;currentSheet->workbook()->setActiveSheet( sheetIndexNumber );Worksheet* wsheet = (Worksheet*) currentSheet->workbook()->activeSheet();if ( NULL == wsheet )continue;QString strSheetName = wsheet->sheetName(); // sheet nameqDebug() << 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 locationint row = cl.row - 1;int col = cl.col - 1;QSharedPointer<Cell> ptrCell = cl.cell; // cell pointer// value of cellQVariant 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️⃣ 日期和时间

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️⃣ 单元格的颜色

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;}
}

此处省略N个例子............................