简单介绍：

文章内使用的是Windows系统的ActiveX对象(QAxObject)操作Excel。

相关参考：

微软vba开发指南：https://docs.microsoft.com/zh-cn/office/vba/api/overview/
其他人的文章：https://blog.csdn.net/fcqwin/article/details/17885557

主要操作接口

主要流程先是querySubObject() 获取某一个子对象，然后可以对其设置属性setProperty()或读取属性property()，进一步可以调用dynamicCall()来实现对其进行操作。

querySubObject：获取对象的某个属性，示例代码如下

QAxObject* range = sheet->querySubObject("Cells(int,int)", row, column);

dynamicCall：调用对象的某个方法，示例代码如下

range->dynamicCall("SetValue(const QString&)", value);

setProperty：设置对象的属性值，示例代码如下

range->setProperty("VerticalAlignment", -4108);//xlCenter

相关代码

class QTCREATOR_UTILS_EXPORT QExcel : public QObject
{
	Q_OBJECT
public:
	QExcel(QString name = "");
	~QExcel();

	void writeTitle(const QString& module, const QStringList& titles);
	void writeLineTest(int row, QVariantList pValues, UNI_UINT32 valCnt);
	QVariant readLine(int row, int column);

	QAxObject* getWorkBooks();
	QAxObject* getWorkBook();
	QAxObject* getWorkSheets();
	QAxObject* getWorkSheet();

	/**************************************************************************/
	/* 工作表                                                                 */
	/**************************************************************************/
	void selectSheet(const QString& sheetName);
	//sheetIndex 起始于 1  
	void selectSheet(int sheetIndex);
	void deleteSheet(const QString& sheetName);
	void deleteSheet(int sheetIndex);
	void insertSheet(QString sheetName);
	int getSheetsCount();
	//在 selectSheet() 之后才可调用  
	QString getSheetName();
	QString getSheetName(int sheetIndex);
	//datas是二维数组,datas的值类型是QList<QVariant>
	bool readSheet(QString sheetName, QVariantList& datas);
	bool writeSheet(QString sheetName, int startRow, int startCol, QVariantList& datas);
	bool writeSheet(QString sheetName, const int& startRow, const int& startCol, const int& endRow, const int& endCol, const QVariant& datas);

	/**************************************************************************/
	/* 单元格                                                                 */
	/**************************************************************************/
	void setCellString(int row, int column, const QString& value);
	//cell 例如 "A7"  
	void setCellString(const QString& cell, const QString& value);
	//range 例如 "A5:C7"  
	void mergeCells(const QString& range);
	void mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn);
	QVariant getCellValue(int row, int column);
	void clearCell(int row, int column);
	void clearCell(const QString& cell);
	void setCellDropItems(int row, int col, const QString& items);

	/**************************************************************************/
	/* 布局格式                                                               */
	/**************************************************************************/
	void getUsedRange(int* topLeftRow, int* topLeftColumn, int* bottomRightRow, int* bottomRightColumn);
	void setColumnWidth(int column, int width);
	void setRowHeight(int row, int height);
	void setCellTextCenter(int row, int column);
	void setCellTextCenter(const QString& cell);
	void setCellTextWrap(int row, int column, bool isWrap);
	void setCellTextWrap(const QString& cell, bool isWrap);
	void setAutoFitRow(int row);
	void mergeSerialSameCellsInAColumn(int column, int topRow);
	int getUsedRowsCount();
	void setCellFontBold(int row, int column, bool isBold);
	void setCellFontBold(const QString& cell, bool isBold);
	void setCellFontSize(int row, int column, int size);
	void setCellFontSize(const QString& cell, int size);
	//复制指令区域的内容到目标区域，带格式
	void copyRangeToRange(QString resource, QString target);

	/**************************************************************************/
	/* 文件                                                                   */
	/**************************************************************************/
	void save();
	void saveAs(const QString& filePath);
	void close();


	/**************************************************************************/
	/* 自定义辅助方法								                                     */
	/**************************************************************************/
	QString getRangeString(int startRow, int startCol, int endRow, int endCol);

	static bool isFileUsed(const QString& fpath);
private:
	void freeSheet();
	QString columnIntToString(int col);
private:
	QAxObject* excel;
	QAxObject* workBooks;
	QAxObject* workBook;
	QAxObject* sheets;
	QAxObject* sheet;
};
``

```cpp

QExcel::QExcel(QString name) :
	excel(nullptr),
	workBooks(nullptr),
	workBook(nullptr),
	sheets(nullptr),
	sheet(nullptr)
{
	CoInitializeEx(NULL, COINIT_MULTITHREADED);
	excel = new QAxObject(this);
	excel->setControl("Excel.Application");
	excel->dynamicCall("SetVisible (bool Visible)", "false");
	excel->setProperty("DisplayAlerts", false);
	workBooks = excel->querySubObject("Workbooks");	
	if(!name.isEmpty())
		workBooks->dynamicCall("Open(const QString&)", name);
	else
		workBooks->dynamicCall("Add");
	workBook = excel->querySubObject("ActiveWorkBook");
	sheets = workBook->querySubObject("WorkSheets");
}

QExcel::~QExcel()
{
	close();
}

void QExcel::writeTitle(const QString& module, const QStringList& titles)
{
	freeSheet();

	sheets->querySubObject("Add()");
	sheet = sheets->querySubObject("Item(int)", 1);
	sheet->setProperty("Name", module);

	for (int column =0; column < titles.size(); ++column)
	{
		QAxObject* cell = sheet->querySubObject("Cells(int,int)", 1, column + 1);
		cell->dynamicCall("SetValue(const QString&)", titles.at(column));
		delete cell;
		cell = nullptr;
	}
}

void QExcel::writeLineTest(int row, QVariantList pValues, UNI_UINT32 valCnt)
{
	for (UNI_UINT32 column = 0; column < valCnt; ++column)
	{
		QAxObject* cell = sheet->querySubObject("Cells(int,int)", row, column + 1);
		cell->dynamicCall("SetValue(const QString&)", pValues[column]);
		delete cell;
		cell = nullptr;
	}
}

QVariant QExcel::readLine(int row, int column)
{
	QVariant data;
	QAxObject* range = sheet->querySubObject("Cells(int,int)", row, column);
	//        return range->property("value");
	if (range)
	{
		data = range->dynamicCall("Value2()");
	}
	return data;
}

void QExcel::close()
{
	//关闭excel
	excel->dynamicCall("Quit()");

	delete sheet;
	delete sheets;
	delete workBook;
	delete workBooks;
	delete excel;

	excel = nullptr;
	workBooks = nullptr;
	workBook = nullptr;
	sheets = nullptr;
	sheet = nullptr;

	CoUninitialize();
}

QAxObject* QExcel::getWorkBooks()
{
	return workBooks;
}

QAxObject* QExcel::getWorkBook()
{
	return workBook;
}

QAxObject* QExcel::getWorkSheets()
{
	return sheets;
}

QAxObject* QExcel::getWorkSheet()
{
	return sheet;
}

void QExcel::selectSheet(const QString& sheetName)
{
	sheet = sheets->querySubObject("Item(const QString&)", sheetName);
}

void QExcel::deleteSheet(const QString& sheetName)
{
	QAxObject* a = sheets->querySubObject("Item(const QString&)", sheetName);
	a->dynamicCall("delete");
}

void QExcel::deleteSheet(int sheetIndex)
{
	QAxObject* a = sheets->querySubObject("Item(int)", sheetIndex);
	a->dynamicCall("delete");
}

void QExcel::selectSheet(int sheetIndex)
{
	sheet = sheets->querySubObject("Item(int)", sheetIndex);
}

void QExcel::setCellString(int row, int column, const QString& value)
{
	QAxObject* range = sheet->querySubObject("Cells(int,int)", row, column);
	range->dynamicCall("SetValue(const QString&)", value);
}

void QExcel::setCellFontBold(int row, int column, bool isBold)
{
	QString cell;
	cell.append(QChar(column - 1 + 'A'));
	cell.append(QString::number(row));

	QAxObject* range = sheet->querySubObject("Range(const QString&)", cell);
	range = range->querySubObject("Font");
	range->setProperty("Bold", isBold);
}

void QExcel::setCellFontSize(int row, int column, int size)
{
	QString cell;
	cell.append(QChar(column - 1 + 'A'));
	cell.append(QString::number(row));

	QAxObject* range = sheet->querySubObject("Range(const QString&)", cell);
	range = range->querySubObject("Font");
	range->setProperty("Size", size);
}

void QExcel::copyRangeToRange(QString resource, QString target)
{
	if (sheet)
	{
		QAxObject* resRange = sheet->querySubObject("Range(const QString&)", resource);
		resRange->dynamicCall("Copy");//复制指令区域内容到剪贴板
		
		QAxObject* tarRange = sheet->querySubObject("Range(const QString&)", target);//选中目标区域
		tarRange->dynamicCall("PasteSpecial");
	}
}

void QExcel::mergeCells(const QString& cell)
{
	QAxObject* range = sheet->querySubObject("Range(const QString&)", cell);
	range->setProperty("VerticalAlignment", -4108);//xlCenter  
	range->setProperty("WrapText", true);
	range->setProperty("MergeCells", true);
}

void QExcel::mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn)
{
	QString cell;
	cell.append(QChar(topLeftColumn - 1 + 'A'));
	cell.append(QString::number(topLeftRow));
	cell.append(":");
	cell.append(QChar(bottomRightColumn - 1 + 'A'));
	cell.append(QString::number(bottomRightRow));

	QAxObject* range = sheet->querySubObject("Range(const QString&)", cell);
	range->setProperty("VerticalAlignment", -4108);//xlCenter  
	range->setProperty("WrapText", true);
	range->setProperty("MergeCells", true);
}

QVariant QExcel::getCellValue(int row, int column)
{
	QAxObject* range = sheet->querySubObject("Cells(int,int)", row, column);
	return range->property("Value");
}

void QExcel::save()
{
	workBook->dynamicCall("Save()");
}

void QExcel::saveAs(const QString& filePath)
{
	//QDateTime current_date_time = QDateTime::currentDateTime();
	//QString current_date = current_date_time.toString("yyyy_MM_dd_hh_mm_ss");
	//if (isFileUsed(filePath))
	//{
	//	filePath == "C:\\Users\\" + current_date + ".xlsx";
	//}
	workBook->dynamicCall("SaveAs(const QString &)", QDir::toNativeSeparators(filePath));
	workBook->dynamicCall("Close(Boolean)", true);
}

int QExcel::getSheetsCount()
{
	return sheets->property("Count").toInt();
}

QString QExcel::getSheetName()
{
	return sheet->property("Name").toString();
}

QString QExcel::getSheetName(int sheetIndex)
{
	QAxObject* a = sheets->querySubObject("Item(int)", sheetIndex);
	return a->property("Name").toString();
}

bool QExcel::readSheet(QString sheetName, QVariantList& datas)
{
	selectSheet(sheetName);
	if (sheet == nullptr)
		return false;
	if (QAxObject* usedRange = sheet->querySubObject("UsedRange"))
	{
		QVariantList varRows = usedRange->dynamicCall("Value").toList();
		datas = varRows;
	}
	return true;
}

bool QExcel::writeSheet(QString sheetName, int startRow, int startCol, QVariantList& datas)
{
	selectSheet(sheetName);
	if (sheet == nullptr)
		return false;
	
	for (auto iter : datas)
	{
		QList<QVariant> rowDatas = iter.toList();
		int curCol = startCol;
		for (auto value : rowDatas)
		{
			setCellString(startRow, curCol, value.toString());
			++curCol;
		}
		++startRow;
	}
	//QString rangeCells = getRangeString(startRow, startCol, startRow + datas.size(), startCol + colCnt);
	//QAxObject* range = sheet->querySubObject("Range(const QString&)", rangeCells);
	//range->dynamicCall("SetValue(const QString&)", datas);

	return true;
}

bool QExcel::writeSheet(QString sheetName, const int& startRow, const int& startCol, const int& endRow, const int& endCol,
	const QVariant& datas)
{
	selectSheet(sheetName);
	if (sheet == nullptr)
		return false;
	QString rangeStr = getRangeString(startRow, startCol, endRow, endCol);
	QAxObject* range = sheet->querySubObject("Range(const QString&)", rangeStr);
	if (range)
	{
		bool res = range->setProperty("Value", datas);
		return res;
	}
	return true;
}

void QExcel::getUsedRange(int* topLeftRow, int* topLeftColumn, int* bottomRightRow, int* bottomRightColumn)
{
	QAxObject* usedRange = sheet->querySubObject("UsedRange");
	*topLeftRow = usedRange->property("Row").toInt();
	*topLeftColumn = usedRange->property("Column").toInt();

	QAxObject* rows = usedRange->querySubObject("Rows");
	*bottomRightRow = *topLeftRow + rows->property("Count").toInt() - 1;

	QAxObject* columns = usedRange->querySubObject("Columns");
	*bottomRightColumn = *topLeftColumn + columns->property("Count").toInt() - 1;
}

void QExcel::setColumnWidth(int column, int width)
{
	QString columnName;
	columnName.append(QChar(column - 1 + 'A'));
	columnName.append(":");
	columnName.append(QChar(column - 1 + 'A'));

	QAxObject* col = sheet->querySubObject("Columns(const QString&)", columnName);
	col->setProperty("ColumnWidth", width);
}

void QExcel::setCellTextCenter(int row, int column)
{
	QString cell;
	cell.append(QChar(column - 1 + 'A'));
	cell.append(QString::number(row));

	QAxObject* range = sheet->querySubObject("Range(const QString&)", cell);
	range->setProperty("HorizontalAlignment", -4108);//xlCenter  
}

void QExcel::setCellTextWrap(int row, int column, bool isWrap)
{
	QString cell;
	cell.append(QChar(column - 1 + 'A'));
	cell.append(QString::number(row));

	QAxObject* range = sheet->querySubObject("Range(const QString&)", cell);
	range->setProperty("WrapText", isWrap);
}

void QExcel::setAutoFitRow(int row)
{
	QString rowsName;
	rowsName.append(QString::number(row));
	rowsName.append(":");
	rowsName.append(QString::number(row));

	QAxObject* rows = sheet->querySubObject("Rows(const QString &)", rowsName);
	rows->dynamicCall("AutoFit()");
}

void QExcel::insertSheet(QString sheetName)
{
	sheets->querySubObject("Add()");
	QAxObject* a = sheets->querySubObject("Item(int)", 1);
	a->setProperty("Name", sheetName);
}

void QExcel::mergeSerialSameCellsInAColumn(int column, int topRow)
{
	int a, b, c, rowsCount;
	getUsedRange(&a, &b, &rowsCount, &c);

	int aMergeStart = topRow, aMergeEnd = topRow + 1;

	QString value;
	while (aMergeEnd <= rowsCount)
	{
		value = getCellValue(aMergeStart, column).toString();
		while (value == getCellValue(aMergeEnd, column).toString())
		{
			clearCell(aMergeEnd, column);
			aMergeEnd++;
		}
		aMergeEnd--;
		mergeCells(aMergeStart, column, aMergeEnd, column);

		aMergeStart = aMergeEnd + 1;
		aMergeEnd = aMergeStart + 1;
	}
}

void QExcel::clearCell(int row, int column)
{
	QString cell;
	cell.append(QChar(column - 1 + 'A'));
	cell.append(QString::number(row));

	clearCell(cell);
}

void QExcel::clearCell(const QString& cell)
{
	QAxObject* range = sheet->querySubObject("Range(const QString&)", cell);
	range->dynamicCall("ClearContents()");
}

void QExcel::setCellDropItems(int row, int col, const QString& items)
{
	QAxObject* range = sheet->querySubObject("Range(const QString&)", getRangeString(row, col, row, col));
	QAxObject* validTion = range->querySubObject("Validation");
	//validTion->dynamicCall("Add(Type,AlertStyle,Operator,Formula1)", "xlValidateList", "xlValidAlertStop", "xlBetween", items);
	validTion->dynamicCall("Modify(int,int,int,QVariant)", 3, 1, 1, items);
	validTion->setProperty("IgnoreBlank", true);
	validTion->setProperty("InCellDropdown", true);
	validTion->setProperty("InputTitle", "test");
	//validTion->setProperty("Formula1", items);
}

int QExcel::getUsedRowsCount()
{
	QAxObject* usedRange = sheet->querySubObject("UsedRange");
	int topRow = usedRange->property("Row").toInt();
	QAxObject* rows = usedRange->querySubObject("Rows");
	int bottomRow = topRow + rows->property("Count").toInt() - 1;
	return bottomRow;
}

void QExcel::setCellString(const QString& cell, const QString& value)
{
	QAxObject* range = sheet->querySubObject("Range(const QString&)", cell);
	range->dynamicCall("SetValue(const QString&)", value);
}

void QExcel::setCellFontSize(const QString& cell, int size)
{
	QAxObject* range = sheet->querySubObject("Range(const QString&)", cell);
	range = range->querySubObject("Font");
	range->setProperty("Size", size);
}

void QExcel::setCellTextCenter(const QString& cell)
{
	QAxObject* range = sheet->querySubObject("Range(const QString&)", cell);
	range->setProperty("HorizontalAlignment", -4108);//xlCenter  
}

void QExcel::setCellFontBold(const QString& cell, bool isBold)
{
	QAxObject* range = sheet->querySubObject("Range(const QString&)", cell);
	range = range->querySubObject("Font");
	range->setProperty("Bold", isBold);
}

void QExcel::setCellTextWrap(const QString& cell, bool isWrap)
{
	QAxObject* range = sheet->querySubObject("Range(const QString&)", cell);
	range->setProperty("WrapText", isWrap);
}

void QExcel::setRowHeight(int row, int height)
{
	QString rowsName;
	rowsName.append(QString::number(row));
	rowsName.append(":");
	rowsName.append(QString::number(row));

	QAxObject* r = sheet->querySubObject("Rows(const QString &)", rowsName);
	r->setProperty("RowHeight", height);
}

QString QExcel::getRangeString(int startRow, int startCol, int endRow, int endCol)
{
	QString res = QString("%1%2:%3%4").arg(columnIntToString(startCol)).arg(startRow).arg(columnIntToString(endCol)).arg(endRow);
	if (startRow = endRow && startCol == endCol)
	{
		res = QString("%1%2").arg(columnIntToString(startCol)).arg(startRow);
	}
	return	res;
}

bool QExcel::isFileUsed(const QString& fpath)
{
	bool isUsed = false;

	QString fpathx = fpath + "x";

	QFile file(fpath);
	if (file.exists())
	{
		bool isCanRename = file.rename(fpath, fpathx);
		if (isCanRename == false)
		{
			isUsed = true;
		}
		else
		{
			file.rename(fpathx, fpath);
		}
	}
	file.close();

	return isUsed;
}

void QExcel::freeSheet()
{
	if (sheet)
	{
		delete sheet;
		sheet = nullptr;
	}	
}

QString QExcel::columnIntToString(int col)
{
	QString res;
	if (col / 26 > 0)
	{
		res += columnIntToString(col - 26);
	}
	else
	{
		res = QString("%1").arg(QString(col - 1 + 'A'));
	}
	return res;
}