QT 创建、读取、修改excel表格

QT       += core gui axcontainer

创建excel表格

int MainWindow::createExcel(QString path)
{
//    QString path = "C:/Users/49300/Desktop/ccc.xlsx";
    QAxObject *excel = new QAxObject(this);
    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 *worksheets = workbook->querySubObject("Sheets");//获取工作表集合
    QAxObject *worksheet = worksheets->querySubObject("Item(int)", 1);//获取工作表集合的工作表1，即sheet1
    workbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(path));//保存至filepath，注意一定要用QDir::toNativeSeparators将路径中的"/"转换为"\"，不然一定保存不了。
    workbook->dynamicCall("Close()");//关闭工作簿
    excel->dynamicCall("Quit()");//关闭excel
    delete excel;
    excel = NULL;
}

读取excel表格内容

int MainWindow::readExcel(QString path)
{
   QAxObject *excel = NULL;
   QAxObject *workbooks = NULL;
   QAxObject *workbook = NULL;
   excel = new QAxObject(this);
   excel->setControl("Excel.Application");
   if (!excel) {
       QMessageBox::critical(NULL, "错误信息", "EXCEL对象丢失");
       return -1;
   }

   excel->dynamicCall("SetVisible(bool)", false);
   workbooks = excel->querySubObject("WorkBooks");
   workbooks->querySubObject("Add");
   workbook = workbooks->querySubObject("Open(const QString&)",path);
   QAxObject * worksheet = workbook->querySubObject("WorkSheets(int)", 1); // 获取第一个工作sheet

   QAxObject * usedrange = worksheet->querySubObject("UsedRange");//获取该sheet的使用范围对象
   QAxObject * rows = usedrange->querySubObject("Rows");
   QAxObject * columns = usedrange->querySubObject("Columns");
   /*获取行数和列数*/

   int intCols = columns->property("Count").toInt();
   int intRows = rows->property("Count").toInt();
   int intRowStart = usedrange->property("Row").toInt();
   int intColStart = usedrange->property("Column").toInt();
   /*获取excel内容*/
   for (int i = intRowStart; i < intRowStart + intRows; i++)  //行
   {
       for (int j = intColStart; j < intColStart + intCols; j++)
       {
           QAxObject *cell = worksheet->querySubObject("Cells(int,int)", i, j);
           QString value = cell->dynamicCall("Value2()").toString();
           qDebug() << __FILE__ << __LINE__ << value;
           delete cell;
       }
   }

   // 关闭excel
   workbook->dynamicCall("Close(Boolean)",true);
   excel->dynamicCall("Quit(void)");
   delete excel;
   excel = NULL;
   return 0;
}

修改excel表格单元格内容

int MainWindow::writeExcel(QString path)
{
   QAxObject *excel = NULL;
   QAxObject *workbooks = NULL;
   QAxObject *workbook = NULL;
   excel = new QAxObject(this);
   excel->setControl("Excel.Application");
   if (!excel) {
       QMessageBox::critical(NULL, "错误信息", "EXCEL对象丢失");
       return -1;
   }

   excel->dynamicCall("SetVisible(bool)", false);
   workbooks = excel->querySubObject("WorkBooks");
   workbooks->querySubObject("Add");
   workbook = workbooks->querySubObject("Open(const QString&)",path);
   QAxObject * worksheet = workbook->querySubObject("WorkSheets(int)", 1); // 获取第一个工作sheet

   QAxObject *cellInsert = worksheet->querySubObject("Cells(int,int)", 5, 5);
   cellInsert->setProperty("Value", "aaaa");  //设置单元格值
   delete cellInsert;

   // 关闭excel
   workbook->dynamicCall("Close(Boolean)",true);
   excel->dynamicCall("Quit(void)");
   delete excel;
   excel = NULL;
   return 0;
}