方法一:
//创建QODBC数据库(excel文件)
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC","excelexport");
QString dsn = QString("DRIVER={Microsoft Excel Driver (*.xls)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"test.xls\";DBQ=test.xls");
db.setDatabaseName(dsn);
db.open();
QSqlQuery query(db);
QString sSql;
bool state;
QString sheetName = "Sheet1";
//删除表
sSql = QString("drop table %1").arg(sheetName);
if (!query.exec(sSql))
{
qDebug() << "drop table failed!";
}
//创建表(sheet in excel)
sSql = QString("create table %1").arg(sheetName);
sSql += "(Q1 real, Q2 real, Q3 real, Q4 real, Q5 real, Q6 real, Q7 real, Q8 real, Q9 real, Q10 real, Q11 real, Q12 real)";
if (!query.exec(sSql))
{
qDebug() << "create table failed!";
}
//插入数据,一行一行的插入
sSql = QString("insert into %1 ").arg(sheetName);
sSql += "(Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
state = query.prepare(sSql);
for (int i = 0; i < QINDEX[0].size(); i++)
{
for (int j = 0; j < 12; j++)
{
query.addBindValue(QINDEX[j][i]);
}
if (!query.exec())
{
qDebug() << "insert failed!";
}
}
db.close();//关闭excel
方法二:
QAxObject excel("Excel.Application");
excel.setProperty("Visible", false);
QAxObject *work_books = excel.querySubObject("WorkBooks");
work_books->dynamicCall("Add(void)");
QAxObject *workbook = excel.querySubObject("ActiveWorkBook");//获取当前工作簿
QAxObject *worksheets = workbook->querySubObject("Sheets");//获取工作表集合
QAxObject *worksheet = worksheets->querySubObject("Item(int)", 1);//获取工作表集合的工作表1,即sheet1
for (int i = 0; i < 12; i++)
{
int count = QINDEX[i].size();
for (int j = 0; j < QINDEX[i].size(); j++)
{
QAxObject *cellnum;
QString num = (QChar)('A' + i) + QString::number(j + 1);
cellnum = worksheet->querySubObject("Range(QVariant, QVariant)", num);//获取单元格
cellnum->dynamicCall("SetValue(const QVariant&)", QVariant(QINDEX[i][j]));
}
}
workbook->dynamicCall("SaveAs(const QString&)", QString("./test.xlsx"));
workbook->dynamicCall("Close()");//关闭工作簿
excel.dynamicCall("Quit()");//关闭excel