QTabelView和QTabelWidget导出Excel三种方法

感谢以下博主的博客

1，https://blog.youkuaiyun.com/hejiajian241/article/details/45191921

2，https://blog.youkuaiyun.com/taihezcf/article/details/52327841

话不多说，直接上代码：

方法一：

QString filepath = QFileDialog::getSaveFileName(this, tr("Save as..."),
		QString(), tr("EXCEL files (*.xls *.xlsx)"));

	int row = m_model->rowCount();
	int col = m_model->columnCount();
	QList<QString> list;
	//添加列标题
	QString HeaderRow;
	for (int i = 0; i<col; i++)
	{
		HeaderRow.append(m_model->horizontalHeaderItem(i)->text() + "\t");
	}
	list.push_back(HeaderRow);
	for (int i = 0; i<row; i++)
	{
		QString rowStr = "";
		for (int j = 0; j<col; j++) {
			rowStr += m_model->item(i, j)->text() + "\t";
		}
		list.push_back(rowStr);
	}
	QTextEdit textEdit;
	for (int i = 0; i<list.size(); i++)
	{
		textEdit.append(list.at(i));
	}

	QFile file(filepath);
	if (file.open(QFile::WriteOnly | QIODevice::Text))
	{
		QTextStream ts(&file);
		ts.setCodec("UTF-8");
		ts << textEdit.document()->toPlainText();
		file.close();
	}

方法二：（只加数据）

QString filePath = QFileDialog::getSaveFileName(this, "Save Data", "untitle",
		"Microsoft Excel 2013(*.xlsx)");
	if (!filePath.isEmpty()) {
		QAxObject *excel = new QAxObject(this);
		excel->setControl("Excel.Application");
		excel->dynamicCall("SetVisible(bool Visible)", false);
		excel->setProperty("DisplayAlerts", false);
		QAxObject *workbooks = excel->querySubObject("WorkBooks");
		workbooks->dynamicCall("Add");
		QAxObject *workbook = excel->querySubObject("ActiveWorkBook");
		QAxObject *worksheets = workbook->querySubObject("Sheets");
		QAxObject *worksheet = worksheets->querySubObject("Item(int)", 1);
		int rowCount = m_model->rowCount();
		int columnCount = m_model->columnCount();




		for (int i = 1; i < rowCount+1; ++i) {
			for (int j = 1; j < columnCount+1; ++j) {
				QAxObject *Range = worksheet->querySubObject("Cells(int,int)", i, j);
				Range->dynamicCall("SetValue(const QString &)", m_model->item(i-1, j-1)->text());
			}
		}
		workbook->dynamicCall("SaveAs(const QString &)", QDir::toNativeSeparators(filePath));
		if (excel != NULL) {
			excel->dynamicCall("Quit()");
			delete excel;
			excel = NULL;
		}
		QMessageBox::information(this, QStringLiteral("提示"), "Exporting data successful");
	}

方法三：（完整版）

QString fileName = QFileDialog::getSaveFileName(this, QStringLiteral("保存"), QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation), QStringLiteral("Excel 文件(*.xls *.xlsx)"));
	if (fileName != "")
	{
		QAxObject *excel = new QAxObject;
		if (excel->setControl("Excel.Application")) //连接Excel控件
		{
			excel->dynamicCall("SetVisible (bool Visible)", "false");//不显示窗体
			excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
			QAxObject *workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合
			workbooks->dynamicCall("Add");//新建一个工作簿
			QAxObject *workbook = excel->querySubObject("ActiveWorkBook");//获取当前工作簿
			QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", 1);

			int i, j;


			
			int colount = m_model->columnCount();
			
			int rowcount = m_model->rowCount();

			QAxObject *cell, *col;
			//标题行
			cell = worksheet->querySubObject("Cells(int,int)", 1, 1);
			cell->dynamicCall("SetValue(const QString&)", QStringLiteral("系统日志"));
			cell->querySubObject("Font")->setProperty("Size", 18);
			//调整行高
			worksheet->querySubObject("Range(const QString&)", "1:1")->setProperty("RowHeight", 30);
			//合并标题行
			QString cellTitle;
			cellTitle.append("A1:");
			cellTitle.append(QChar(colount - 1 + 'A'));
			cellTitle.append(QString::number(1));
			QAxObject *range = worksheet->querySubObject("Range(const QString&)", cellTitle);
			range->setProperty("WrapText", true);
			range->setProperty("MergeCells", true);
			range->setProperty("HorizontalAlignment", -4108);//xlCenter
			range->setProperty("VerticalAlignment", -4108);//xlCenter
														   //列标题
			for (i = 0; i<colount; i++)
			{
				QString columnName;
				columnName.append(QChar(i + 'A'));
				columnName.append(":");
				columnName.append(QChar(i + 'A'));
				col = worksheet->querySubObject("Columns(const QString&)", columnName);
				col->setProperty("ColumnWidth", m_tableview->columnWidth(i) / 6);
				cell = worksheet->querySubObject("Cells(int,int)", 2, i + 1);
				
				//QTableView 获取表格头部文字信息
				columnName= m_model->headerData(i,Qt::Horizontal,Qt::DisplayRole).toString();
				cell->dynamicCall("SetValue(const QString&)", columnName);
				cell->querySubObject("Font")->setProperty("Bold", true);
				cell->querySubObject("Interior")->setProperty("Color", QColor(191, 191, 191));
				cell->setProperty("HorizontalAlignment", -4108);//xlCenter
				cell->setProperty("VerticalAlignment", -4108);//xlCenter
			}

			//数据区

			


			//QTableView 获取表格数据部分
			  for(i=0;i<rowcount;i++) //行数
			     {
			         for (j=0;j<colount;j++)   //列数
			         {
			             QModelIndex index = m_model->index(i, j);
			             QString strdata= m_model->data(index).toString();
			             worksheet->querySubObject("Cells(int,int)", i+3, j+1)->dynamicCall("SetValue(const QString&)", strdata);
			         }
			     }


			//画框线
			QString lrange;
			lrange.append("A2:");
			lrange.append(colount - 1 + 'A');
			lrange.append(QString::number(m_model->rowCount() + 2));
			range = worksheet->querySubObject("Range(const QString&)", lrange);
			range->querySubObject("Borders")->setProperty("LineStyle", QString::number(1));
			range->querySubObject("Borders")->setProperty("Color", QColor(0, 0, 0));
			//调整数据区行高
			QString rowsName;
			rowsName.append("2:");
			rowsName.append(QString::number(m_model->rowCount() + 2));
			range = worksheet->querySubObject("Range(const QString&)", rowsName);
			range->setProperty("RowHeight", 20);
			workbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(fileName));//保存至fileName
			workbook->dynamicCall("Close()");//关闭工作簿
			excel->dynamicCall("Quit()");//关闭excel
			delete excel;
			excel = NULL;
			if (QMessageBox::question(this, QStringLiteral("完成"), QStringLiteral("文件已经导出，是否现在打开？"), QMessageBox::Yes | QMessageBox::No) == QMessageBox::Yes)
			{
				QDesktopServices::openUrl(QUrl("file:///" + QDir::toNativeSeparators(fileName)));
			}
		}
		else
		{
			QMessageBox::warning(NULL, QStringLiteral("错误"), QStringLiteral("未能创建 Excel 对象，请安装 Microsoft Excel。"), QMessageBox::Apply);
		}
	}

 

效果图：