Qt中实现excel文件的写操作

最新推荐文章于 2024-05-17 05:56:23 发布

甩你十万八千里

最新推荐文章于 2024-05-17 05:56:23 发布

阅读量1.2k

点赞数 1

分类专栏： Qt 文章标签： excel QT

本文链接：https://blog.csdn.net/xiao123456bin/article/details/127963973

版权

Qt 专栏收录该内容

54 篇文章 3 订阅

订阅专栏

包含头文件

#include <ActiveQt/QAxObject>
在Qt Project Settings的Qt Modules中勾选“ActiveQt container”模块

备注：以下代码实现功能为将float类型数据以列的形式添加写入excel文件中

bool exportToExcel(vector<float2> ch1)
{
QString filepath = QString("D:\\Test.xlsx");
if (!filepath.isEmpty()) 
{
	QAxObject* excel = new QAxObject(this);
	excel->setControl("Excel.Application");
	//连接Excel控件
	//excel->dynamicCall("SetVisible (bool Visible)", "true");
	//不显示窗体
	excel->dynamicCall("SetVisible (bool Visible)", "false");
	//不显示窗体		
	excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
	
	QAxObject* workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合
	//workbooks->dynamicCall("Add");//新建一个工作簿
	workbooks->dynamicCall("Add");//新建一个工作簿

	//4) 打开已存在的工作簿：
	//workbooks->dynamicCall("Open(const QString&)", QString("c:/test.xls"));
	workbooks->dynamicCall("Open(const QString&)", filepath);

	QAxObject* workbook = excel->querySubObject("ActiveWorkBook");//获取当前工作簿
	QAxObject* worksheets = workbook->querySubObject("Sheets");//获取工作表集合
	QAxObject* worksheet = worksheets->querySubObject("Item(int)", 1);//获取工作表集合的工作表1，即sheet1


	QAxObject* used_range = worksheet->querySubObject("UsedRange");     //获得利用的范围
	QAxObject* rows_1 = used_range->querySubObject("Rows");
	QAxObject* columns = used_range->querySubObject("Columns"); 

	int row_start = used_range->property("Row").toInt();          //获得开始行
	int row_end = rows_1->property("Count").toInt();

	int column_start = used_range->property("Column").toInt();     //获得开始列
	int column_end = columns->property("Count").toInt();
	int columnCount = 0;
	columnCount = column_start + column_end;
	/*if (1 == column_end)
	{
		columnCount = 1;
	}
	else
	{
		columnCount+=2;
	}*/

	printf("\n\n\n\n********row_start = %d, row_end = %d, column_start = %d, column_end = %d********\n\n\n\n",row_start,row_end, column_start, column_end);

	QAxObject* cellA, * cellB, * cellC, * cellD;

	//设置标题
	int cellrow = 1;
	//QString A = "A" + QString::number(cellrow);//设置要操作的单元格，如A1
	//QString B = "B" + QString::number(cellrow);
	/*QString B = "B" + QString::number(row_end+1);
	QString C = "C" + QString::number(cellrow);
	QString D = "D" + QString::number(cellrow);*/
	//cellA = worksheet->querySubObject("Range(QVariant, QVariant)", A);//获取单元格
	//cellB = worksheet->querySubObject("Range(QVariant, QVariant)", B);
	cellB = worksheet->querySubObject("Cells(int,int)", 1, columnCount /*column_end+2*/);

	//QAxObject* range = worksheet->querySubObject("Cells(int,int)", 1, 1);

	/*cellC = worksheet->querySubObject("Range(QVariant, QVariant)", C);
	cellD = worksheet->querySubObject("Range(QVariant, QVariant)", D);*/
	//cellA->dynamicCall("SetValue(const QVariant&)", QVariant(cellTitle));//设置单元格的值
	cellB->dynamicCall("SetValue(const QVariant&)", QVariant(cellTitle));
	/*cellB->dynamicCall("SetValue(const QVariant&)", QVariant(QStringLiteral("用户名")));
	cellC->dynamicCall("SetValue(const QVariant&)", QVariant(QStringLiteral("金额")));
	cellD->dynamicCall("SetValue(const QVariant&)", QVariant(QStringLiteral("日期")));*/
	cellrow++;

	//int rows = this->model->rowCount();
	int rows = ch1.size();
	for (int i = 0; i < rows; i++) {
		//QString A = "A" + QString::number(cellrow);//设置要操作的单元格，如A1
		//QString B = "B" + QString::number(row_end + i);
		//QString B = "B" + QString::number(i);
		//QString B = "B" + QString::number(cellrow);
		//QString C = "C" + QString::number(cellrow);
		//QString D = "D" + QString::number(cellrow);
		//cellA = worksheet->querySubObject("Range(QVariant, QVariant)", A);//获取单元格
		//cellB = worksheet->querySubObject("Range(QVariant, QVariant)", B);
		//cellB = worksheet->querySubObject("Cells(int,int)", 3,i);


		//cellB = worksheet->querySubObject("Cells(int,int)", 3, 1);

		//cellC = worksheet->querySubObject("Range(QVariant, QVariant)", C);
		//cellD = worksheet->querySubObject("Range(QVariant, QVariant)", D);
		//cellA->dynamicCall("SetValue(const QVariant&)", QVariant(this->model->item(i, 0)->data(Qt::DisplayRole).toString()));//设置单元格的值
		//cellB->dynamicCall("SetValue(const QVariant&)", QVariant(this->model->item(i, 1)->data(Qt::DisplayRole).toString()));
		//cellC->dynamicCall("SetValue(const QVariant&)", QVariant(this->model->item(i, 2)->data(Qt::DisplayRole).toString()));
		//cellD->dynamicCall("SetValue(const QVariant&)", QVariant(this->model->item(i, 3)->data(Qt::DisplayRole).toString()));

		//cellA->dynamicCall("SetValue(const QVariant&)", QString("%1").arg(ch1[i].y));//设置单元格的值
		//cellB->dynamicCall("SetValue(const QVariant&)", QString("%1").arg(ch1[i].y));

		//获取单元格
		cellB = worksheet->querySubObject("Cells(int, int", i+2, columnCount /*column_end+2*/);
		//储存一个字符串数据至表格
		cellB->dynamicCall("setValue(const QVariant&", QVariant(ch1[i].y));

		//cellC->dynamicCall("SetValue(const QVariant&)", "1.3");
		//cellD->dynamicCall("SetValue(const QVariant&)", "1.4");

		//cellA = worksheet->querySubObject("Cells(int, int)", i, i + 1);//获取单元格
		//cellA->dynamicCall("SetValue(const QString&)", QString::number(i));//设置单元格的值

		cellrow++;

		printf("cellrow = %d\n", cellrow);
	}

	workbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(filepath));//保存至filepath，注意一定要用QDir::toNativeSeparators将路径中的"/"转换为"\"，不然一定保存不了。
	workbook->dynamicCall("Close()");//关闭工作簿

	/*excel->setProperty("DisplayAlerts", 1);
	workbook->dynamicCall("Close (Boolean)", false);*/

	excel->dynamicCall("Quit()");//关闭excel
	delete excel;
	excel = NULL;
}
return true;

}