求助 qt 调用excel [复制链接]

环境ＶＳ２００３＋ＱＴ４.０商业版
#include "excel.h"

#include <ActiveQt/QAxFactory>
#include <qaxobject.h>
#include <QtGui>
//#include <ActiveQt>


excel::excel(QWidget *parent)
    : QWidget(parent)
{
    ui.setupUi(this);


QAxObject* excel = new QAxObject( "Excel.Application", this ); //获取一个Excel对象

excel->dynamicCall( "SetVisible(bool)", TRUE ); //设置为可见
QAxObject *workbooks = excel->querySubObject( "Workbooks" ); //得到Workbooks集合的指针

QAxObject *workbook = workbooks->querySubObject( "Open(const QString&)", "C:\\data\\A.xls" ); //打开硬盘上的一个Excel文档

QAxObject *sheets = workbook->querySubObject( "Sheets" ); //得到Sheets对象的指针

QAxObject *StatSheet = sheets->querySubObject( "Item(const QVariant&)", QVariant("stat") );    //得到名为stat的一个sheet的指针

StatSheet->dynamicCall( "Select()" ); //选择名为stat的sheet，使之可见 

QAxObject *range = StatSheet->querySubObject( "Range(const QVariant&)", QVariant( QString("A1:A1")));  //选择A1:A1这个range对象

range->dynamicCall( "Clear()" ); // 清除range对象

range->dynamicCall( "SetValue(const QVariant&)", QVariant(5) ); //将该range对象的值设为5

excel->dynamicCall(" SetScreenUpdating(bool)",true);
//存檔
workbook->querySubObject( "SaveAs(const QString&)","C:\\data\\A.xls"  );
//退出
excel->dynamicCall( " Quit()");

}

编译在link时总是报有无发解析的外部符号，请高手支招

需要链接QAxContainer库，
要在.pro文件中添加下列一行："CONFIG +=qaxcontainer"
再qmake编译生成VS工程,再编译,应该没问题.
但是你那个例子有问题.

这种方法不可取.

QSqlDatabase+QSqlQuery可以解决

请问如何动态创建excel文件？ 我的代码不时会生成一些数据，我想创建不同的excel文件来保存每次产生的数据，但是现在好像只能访问操作已经存在的excel文件

使用个变通的方法吧。你自带一个空EXCEL文件比如叫empty.xls，放在某个固定目录下，比如../tpl/empty.xls，在需要新建的时候，将其拷贝到另一个地方，然后打开，然后写东西。

引用第4楼playlikun于2010-02-09 11:48发表的  :
请问如何动态创建excel文件？ 我的代码不时会生成一些数据，我想创建不同的excel文件来保存每次产生的数据，但是现在好像只能访问操作已经存在的excel文件

使用个变通的方法吧。你自带一个空EXCEL文件比如叫empty.xls，放在某个固定目录下，比如../tpl/empty.xls，在需要新建的时候，将其拷贝到另一个地方，然后打开，然后写东西。

给楼主发一段代码：
.pro文件中加：
CONFIG += qaxcontainer

下面是 .cpp
#ifdef Q_WS_WIn
#include <windows.h>
#include <ActiveQt>
#endif


        //{{{
        QAxObject *excel = new QAxObject("Excel.Application");
        QAxObject *workbooks = NULL;
        QAxObject *workbook = NULL;
        QAxObject *sheets = NULL;
        QAxObject *sheet = NULL;
        QAxObject *range = NULL;
        QAxObject *cell = NULL;
        
        if (!excel)
        {
            QMessageBox::critical(this, "提示", "excel对象丢失, 跳过当前文件");
            continue;
        }
        excel->dynamicCall("SetVisible(bool)", false);
        workbooks = excel->querySubObject("Workbooks");
        if (!workbooks)
        {
            QMessageBox::critical(this, "提示",
                    "workbooks对象丢失, 跳过当前文件");
            excel->dynamicCall("Quit()");
            delete excel;
            continue;
        }
        workbook = workbooks
            ->querySubObject("Open(QString)", slFile);
        if (!workbook)
        {
            QMessageBox::critical(this, "提示",
                    "workbook对象丢失, 跳过当前文件");
            excel->dynamicCall("Quit()");
            delete workbooks;
            delete excel;
            continue;
        }
        sheets = workbook->querySubObject("Sheets");
        if (!sheets)
        {
            QMessageBox::critical(this, "提示",
                    "sheets对象丢失, 跳过当前文件");
            excel->dynamicCall("Quit()");
            delete workbook;
            delete workbooks;
            delete excel;
            continue;
        }
        sheet = sheets->querySubObject("Item(int)", 1);
        if (!sheet)
        {
            QMessageBox::critical(this, "提示", "sheet对象丢失, 跳过当前文件");
            excel->dynamicCall("Quit()");
            delete sheets;
            delete workbook;
            delete workbooks;
            delete excel;
            continue;
        }
        sheet->dynamicCall("Select()");
        cell = sheet->querySubObject("UsedRange");
        rows = cell->querySubObject("Rows")->dynamicCall("count").toInt();
        columns = cell->querySubObject("Columns")
            ->dynamicCall("count").toInt();
        delete cell;
        
        //判断表中i行columns列是否有数据
        bool hasData = false;
        for (int j = 1; j < columns; j++)
        {
            range = sheet->querySubObject("Cells(int, int)", 2, j);
            if (range->dynamicCall("Value()").toString().trimmed()
                    .isEmpty())
            {
                delete range;
                continue;
            }
            else
            {
                hasData = true;
                delete range;
                break;
            }
        }
        if (!hasData)
        {
            progress.setText("文档中没有数据,跳过...");
            QApplication::processEvents();
            excel->dynamicCall("Quit()");
            delete sheet;
            delete sheets;
            delete workbook;
            delete workbooks;
            delete excel;
            continue;
        }
        //}}}
        
        QString strSerial;
        //导入数据到缓存
        for (int k = 2; k <= rows; k++)
        {
            progress.setText(QString("正在读取第%1条记录\n文件名: %2")
                    .arg(k - 1).arg(slFile));
            QApplication::processEvents();

            serialNumber++;
            strSerial = QString::number(serialNumber);
            strSerial.prepend(
                    QString(4-QString::number(serialNumber).size(), '0'));
            range = sheet->querySubObject("Cells(int, int)", k, 1);
            
            rowMap["mia_f12"] = range->dynamicCall("Value()")
                .toString().trimmed();
            delete range;
            
  
            range = sheet->querySubObject("Cells(int, int)", k, 2);
            rowMap["mia_f13"] = range->dynamicCall("Value()")
                .toString().trimmed();
            delete range;


            range = sheet->querySubObject("Cells(int, int)", k, 3);
            rowMap["mia_f9"] = range->dynamicCall("Value()")
                .toString().trimmed();
            delete range;


            range = sheet->querySubObject("Cells(int, int)", k, 4);
            rowMap["mia_f1"] = range->dynamicCall("Value()")
                .toString().trimmed();
            delete range;


            range = sheet->querySubObject("Cells(int, int)", k, 5);
            rowMap["mia_f4"] = range->dynamicCall("Value()")
                .toString().trimmed();
            delete range;


            range = sheet->querySubObject("Cells(int, int)", k, 6);
            rowMap["mia_f5"] = range->dynamicCall("Value()")
                .toString().trimmed().replace('-', "");
            delete range;

            range = sheet->querySubObject("Cells(int, int)", k, 7);
            rowMap["mia_f14"] = range->dynamicCall("Value()")
                .toString().trimmed();
            delete range;


            range = sheet->querySubObject("Cells(int, int)", k, 8);
            rowMap["mia_f2"] = range->dynamicCall("Value()")
                .toString().trimmed().replace("-", "");
            delete range;


            range = sheet->querySubObject("Cells(int, int)", k, 9);
            rowMap["mia_d1"] = range->dynamicCall("Value()")
                .toString().trimmed();
            delete range;


            range = sheet->querySubObject("Cells(int, int)", k, 10);
            rowMap["mia_d2"] = range->dynamicCall("Value()")
                .toString().trimmed();

            range = sheet->querySubObject("Cells(int, int)", k, 11);
            rowMap["mia_d3"] = range->dynamicCall("Value()")
                .toString().trimmed();
            delete range;


            range = sheet->querySubObject("Cells(int, int)", k, 12);
            rowMap["mia_d4"] = range->dynamicCall("Value()")
                .toString().trimmed();
            delete range;

            /*
            
            range = sheet->querySubObject("Cells(int, int)", i, 13);
            rowMap["mia_f8"] = range->dynamicCall("Value()")
                .toString().trimmed();
            delete range;
            */

            range = sheet->querySubObject("Cells(int, int)", k, 14);
            rowMap["mia_f15"] = range->dynamicCall("Value()")
                .toString().trimmed().replace("-", "");
            delete range;

            {
                // 12, 13, 9, 14
            QString strTemp2 = rowMap["mia_f12"];
            // 如不在系统已有标签表中，则记录增加
            if (!strTemp2.isEmpty()
                    && !mapLabel[1001].contains(strTemp2)
                    && !mapLabelNew[1001].contains(strTemp2))
            {
                mapLabelNew[1001] << strTemp2;
            }

            strTemp2 = rowMap["mia_f13"];
            // 如不在系统已有标签表中，则记录增加
            if (!strTemp2.isEmpty()
                    && !mapLabel[1002].contains(strTemp2)
                    && !mapLabelNew[1002].contains(strTemp2))
            {
                mapLabelNew[1002] << strTemp2;
            }

            strTemp2 = rowMap["mia_f9"];
            // 如不在系统已有标签表中，则记录增加
            if (!strTemp2.isEmpty()
                    && !mapLabel[1003].contains(strTemp2)
                    && !mapLabelNew[1003].contains(strTemp2))
            {
                mapLabelNew[1003] << strTemp2;
            }

            strTemp2 = rowMap["mia_f14"];
            // 如不在系统已有标签表中，则记录增加
            if (!strTemp2.isEmpty()
                    && !mapLabel[1004].contains(strTemp2)
                    && !mapLabelNew[1004].contains(strTemp2))
            {
                mapLabelNew[1004] << strTemp2;
            }
            }
            dataList.push_back(rowMap);
        }
        excel->dynamicCall("Quit()");
        delete sheet;
        delete sheets;
        delete workbook;
        delete workbooks;
        delete excel;

谢谢 XChinux 的提示，我今天试试看

这几天做了一个demo试了一下，可以在写数据的时候从指定位置复制一个excel模板文件过来，就当是“动态生成”的吧，但是在完成excel数据写入，调用excel->dynamicCall("Quit()")的时候，总是会弹出一个提示是否保存修改结果的对话框，这个是excel自带的，我的本意是这个excel的产生对用户应该是不可见的，有什么办法可以让这个对话框不弹出而直接保存数据呢？

已经解决了，保存的时候，另存一个指定文件名就ok了，也可以输出到指定的文件夹里
workbook->dynamicCall( "SaveAs(const QString&)","D:\\QT_project\\justfortest\\excels\\xxxxxx.xls");

非常不错，收藏了