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 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️⃣ 日期和时间

• 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个例子............................

除了基本功能外，一些不常用的功能也是提供的，比如分组（类似树结构的折叠和展开）：

优点说完了，下面说下缺点，占用内存特别大，它把所有单元格数据都保存在内存中，最后在同样写入到文件中。将数据都保存在内存中的好处是，方便插入操作，如果只是顺序写数据，是可以通过修改源码，极大降低内存占用的。

原文链接：Qt优秀开源项目之二十四：EXCEL读写利器QXlsx-优快云博客