一、在界面文件中创建TableView
#include "form.h"
#include "ui_form.h"
#include
#include
Form::Form(QWidget *parent) :
QWidget(parent),
ui(new Ui::Form)
{
ui->setupUi(this);
QSqlTableModel *model_; //数据库模型
model_=new QSqlTableModel; //负责提取数据
model_->setTable("score");//选择要输出的表名称
if(model_->select())
{
qDebug()<<"model设置成功";
}
else
{
qDebug()<<"model设置失败";
}
ui->tableView->setModel(model_);
二、导入Excel
#include
#include
#include
#include
#include
#include
QAxObject 的对象
Execl
workbooks //工作簿集合
workbook //单个工作簿
worksheets //表合集
worksheet //单个表
cell //单元格
使用querySubject访问各种属性,使用dynamicCall访问各种方法(可查询VBA脚本查看各种对象下的属性和方法)
VBA:Office Visual Basic for Applications (VBA) 参考 | Microsoft Docs
QString path = "F:/QTCode/learning/SaveExcelFile/widget/widget/widget/";
QString fileName = path.append("Excel.xlsx");
QAxObject* excel = new QAxObject("Excel.Application");
if (!excel)
{
qDebug() << "create excel file failed";
}
else
{
qDebug() << "create excel file successed";
}
excel->dynamicCall("SetVisible (bool Visible)", true);
excel->dynamicCall("SetUserControl(bool UserControl)", true);
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);
workSheet->setProperty("Name", "数据");
int colCount = ui->tableView->model()->columnCount();
int rowCount =ui->tableView->model()->rowCount();
//写入数据到excel表格
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
// 获取tableView中的索引号
QModelIndex index = ui->tableView->model()->index(i, j);
// 获取索引号对应的数据
QString strData = ui->tableView->model()->data(index).toString();
// 获取excel表格中的单元格索引,以(1, 1)为起点
QAxObject* cell = workSheet->querySubObject("Cells(int, int)", i+2, j+1);
// 写入数据到excel中的单元格
cell->dynamicCall("SetValue(const QString&)", strData);
}
}
// 列标题
for (int i = 0; i < colCount; i++)
{
// 获取单元格 A1, B1, C1
QString columnName;
columnName.append(QChar(i + 'A'));
columnName.append(QString::number(1));
QAxObject* cell = workSheet->querySubObject("Range(const QString)", columnName);
// 单元格属性设置
cell->setProperty("ColumnWidth", 500);
cell->setProperty("RowHeight", 30);
cell->querySubObject("Font")->setProperty("Bold", true);
cell->querySubObject("Interior")->setProperty("Color", QColor(150, 150, 150));
cell->setProperty("HorizontalAlignment", -4108);
// 获取列标题的单元格索引
QAxObject* col = workSheet->querySubObject("Cells(int, int)", 1, i + 1);
// 获取tableView中的列标题数据
columnName =ui->tableView->model()->headerData(i, Qt::Horizontal, Qt::DisplayRole).toString();
// 写入到excel的单元格中
col->dynamicCall("SetValue(const QString&)", columnName);
}
// 对单元格画边框
QString drawCellLine;
drawCellLine.append("A1:");
drawCellLine.append(colCount-1 + 'A');
drawCellLine.append(QString::number(ui->tableView->model()->rowCount()));
QAxObject* range = workSheet->querySubObject("Range(const QString&)", drawCellLine);
range->querySubObject("Borders")->setProperty("LineStyle", QString::number(1));
range->querySubObject("Borders")->setProperty("Color", QColor(0, 0, 0));
// 调整数据行高
QString rowsName;
rowsName.append("1:");
rowsName.append(QString::number(ui->tableView->model()->rowCount()));
range = workSheet->querySubObject("Range(const QString&)", rowsName);
range->setProperty("RowHeight", 20);
workBook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(fileName));
workBook->dynamicCall("Close()");
excel->dynamicCall("Quit()");
delete excel;
excel = NULL;
发表评论