1.一种简单的方法
这种方法是在进程中打开一个文件保存对话框,读取软件表格中的内容,将其文字直接保存到Excel中。其具体的实现代码如下:
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();
}
2.采用COM接口的方式
用这种方式要用到Qt框架中的QAxObject类。此类对COM对象进行了封装,它继承于类QAxBase,而此类中提供了一组API通过IUnKnown指针直接访问COM对象。 Excel也是一个COM对象,因此,我们可方便地使用QAxObject类来操作。
要正确地操作Excel,先要理清其对象的主要层次结构。其结构可归纳为如下形式:
从上图可以看出,每个Excel有一个Application对象,每个Application对象由多个Workbook对象组成的,这些对象由Workbooks对象统一管理; Workbook对象下可以包含若干Worksheet,这些对象由Worksheet对象统一管理;Range对象就是Worksheet里的表格单元。
在将软件中的数据信息导出到Excel中时,不仅可以导出数据,也可以设置Excel文件的样式和格式。下面先示例只导出数据的代码,再示例整个代码。
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");
}
下面的代码示例了,在导出数据的过程中也顺便设置了Excel文件的格式。
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);//不显示任何警告信息
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", 15);
//调整行高
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);
range->setProperty("VerticalAlignment", -4108);
//列标题
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);
}
本文到此结束!
如果对你有帮助,请随手 点赞 或 点喜欢!