QT excel操作汇总

1.基本读取写入文件操作
2.保存时弹出保存对话框问题
3.取消保存对话框后文件不保存的问题

读写Excel

1.pro文件添加

CONFIG += qaxcontainer

2.头文件

#include <ActiveQt/QAxObject>

3.读取文件流程

建立进程并打开文件，获取表格属性

//建立excel进程并获取表格总行列数
    QAxObject excel("Excel.Application");     //建立进程
    excel.setProperty("Visible",false);    //不知道
    QAxObject *workbooks = excel.querySubObject("WorkBooks");  //后面操作都在这里面
    QString initFile=QDir::currentPath();   //文件当前路径
    initFile+="/debug/Table.xlsx";
  //initFile+="/Table.xlsx";
    workbooks->dynamicCall("Open (const QString&)",initFile);    //打开文件
    QAxObject *workbook = excel.querySubObject("ActiveWorkBook");//获取活动工作簿
    QAxObject *worksheet=workbook->querySubObject("WorkSheets(int)",1);//第一个工作表
    QAxObject *used_range = worksheet->querySubObject("UsedRange");
    QAxObject *rows = used_range->querySubObject("Rows");    
    QAxObject *columns = used_range->querySubObject("Columns");
    int row_start = used_range->property("Row").toInt();  //获取起始行
    int column_start = used_range->property("Column").toInt();  //获取起始列
    int row_count = rows->property("Count").toInt();  //获取行数
    int column_count = columns->property("Count").toInt();  //获取列数
    qDebug()<<"起始行:"<<row_start<<"起始列:"<<column_start<<"行数:"<<row_count<<"列数:"<<column_count;

读取文件

QAxObject *rangeData = worksheet->querySubObject("Cells(int,int)",row,1); //获取cell的值   row,1是坐标
QString DataVal = rangeData->dynamicCall("Value2()").toString();   //转格式
Data[row-1]=DataVal;   //取出

关闭文档结束excel线程

//关闭文档结束excel线程
    workbooks->dynamicCall("Close(Boolean)",false);           //关闭文档
    excel.dynamicCall("Quit(void)");                            //退出excel线程
    //delete excel; //若创建excel时使用指针则需要删除指针  QAxObject *excel = new QAxObject(this);

4.改写文件

建立进程并打开文件，获取表格属性同上

改写文件

QAxObject *cell = worksheet->querySubObject("Cells(int,int)", row, 1);
cell->setProperty("Value", Data[row-1]);  //设置单元格值

保存文件

excel.setProperty("DisplayAlerts", false);/*不显示任何警告信息，如果为true，那么关闭时会出现类似“是否保存对某某文件的更改”*/
workbook->dynamicCall("Save()");  //保存活动工作簿文件  这句不能错

//关闭文档结束excel线程
workbooks->dynamicCall("Close(Boolean)",false);           //关闭文档
excel.dynamicCall("Quit(void)");                            //退出excel线程

5.汇总例程

void MainWindow::writeToFile(QString *Data,QString *name,int len)
{
  //建立excel进程并获取表格总行列数
    QAxObject excel("Excel.Application");
    excel.setProperty("Visible",false);
    QAxObject *workbooks = excel.querySubObject("WorkBooks");
    QString initFile=QDir::currentPath();
    initFile+="/debug/Table.xlsx";
  //initFile+="/Table.xlsx";
    workbooks->dynamicCall("Open (const QString&)",initFile);
    QAxObject *workbook = excel.querySubObject("ActiveWorkBook");//获取活动工作簿
    QAxObject *worksheet=workbook->querySubObject("WorkSheets(int)",1);//第一个工作表
    int row_count=Data->length();
     //将表格内容读取并提取至程序内
    for(int row=1;row<row_count+1;row++)
      {
        QAxObject *cell = worksheet->querySubObject("Cells(int,int)", row, 1);
        cell->setProperty("Value", Data[row-1]);  //设置单元格值
        cell = worksheet->querySubObject("Cells(int,int)", row, 2);
        cell->setProperty("Value", name[row-1]);  //设置单元格值
        qDebug()<<row<<Data[row-1]<<"  "<<name[row-1];
      }
    excel.setProperty("DisplayAlerts", false);/*不显示任何警告信息，如果为true，那么关闭时会出现类似“是否保存对某某文件的更改”*/
    workbook->dynamicCall("Save()");  //保存活动工作簿文件
    //关闭文档结束excel线程
    workbooks->dynamicCall("Close(Boolean)",false);           //关闭文档
    excel.dynamicCall("Quit(void)");                            //退出excel线程

}
void MainWindow::readfromFile(QString *Data,QString *name,int len)
{
  //建立excel进程并获取表格总行列数
    QAxObject excel("Excel.Application");
    excel.setProperty("Visible",false);
    QAxObject *workbooks = excel.querySubObject("WorkBooks");
    QString initFile=QDir::currentPath();
    initFile+="/debug/Table.xlsx";
  //initFile+="/Table.xlsx";
    workbooks->dynamicCall("Open (const QString&)",initFile);
    QAxObject *workbook = excel.querySubObject("ActiveWorkBook");//获取活动工作簿
    QAxObject *worksheet=workbook->querySubObject("WorkSheets(int)",1);//第一个工作表
    QAxObject *used_range = worksheet->querySubObject("UsedRange");
    QAxObject *rows = used_range->querySubObject("Rows");
    QAxObject *columns = used_range->querySubObject("Columns");
    int row_start = used_range->property("Row").toInt();  //获取起始行
    int column_start = used_range->property("Column").toInt();  //获取起始列
    int row_count = rows->property("Count").toInt();  //获取行数
    int column_count = columns->property("Count").toInt();  //获取列数
    qDebug()<<"起始行:"<<row_start<<"起始列:"<<column_start<<"行数:"<<row_count<<"列数:"<<column_count;
   //依据表格大小重新定义缓存区数组大小
    Data->resize(row_count);
    name->resize(row_count);
     //将表格内容读取并提取至程序内
    for(int row=row_start;row<row_count+1;row++)
      {
        QAxObject *rangeData = worksheet->querySubObject("Cells(int,int)",row,1); //获取cell的值
        QAxObject *rangeName = worksheet->querySubObject("Cells(int,int)",row,2); //获取cell的值
        QString DataVal = rangeData->dynamicCall("Value2()").toString();
        QString NameVal = rangeName->dynamicCall("Value2()").toString();
        Data[row-1]=DataVal;
        name[row-1]=NameVal;
        qDebug()<<row<<Data[row-1]<<"  "<<name[row-1];
        delete rangeData;delete rangeName;
      }
    //关闭文档结束excel线程
    workbooks->dynamicCall("Close(Boolean)",false);           //关闭文档
    excel.dynamicCall("Quit(void)");                            //退出excel线程
}