Qt5编程——excel操作

来源：互联网发布：商标设计软件生成器编辑：程序博客网时间：2024/06/03 18:54

方法一：

//创建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，即sheet1for (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

0 0