copy/move excel sheet with QT

[gerardpuducul]

Hi all


I manipulate excel with qt an all is working almost well.

Just one issue : I don't success to cpoy/move a sheet in the current workbook

I have try :
sheet->dynamicCall("Copy");
sheet->dynamicCall("Move");

but the result is a copy/move to a new workbook

HOw can I solve this?

Thanks

QAxObject* sheet;
   QStringList sheets_name;
   QAxObject* workbooks;
   QAxObject* workbook;
   QAxObject*sheets;
 
   QString campaign_sheet_name;
   QAxObject * excel_cell ;
   QAxWidget* excel;
 
   qint16 xl_test_start_line = 16;
   qint16 sheet_number;
 
   //Launch Excel
   if (ui->check_button_excel->isChecked())
   {
       excel = new QAxWidget("Excel.Application");
       workbooks = excel->querySubObject( "Workbooks" );
       workbook = workbooks->querySubObject( "Open(const QString&)",excel_path  );
       sheets = workbook->querySubObject( "Worksheets" );
       if ( ui->radioButton_XLVisible->isChecked())
           excel->setProperty("Visible", true);
       int count = sheets->dynamicCall("Count()").toInt();
    count = sheets->property("Count").toInt();
 
    //Get sheets name list
    for (int i = 1; i <= count; i++)
       {
            QAxObject * worksheet = workbook->querySubObject("Worksheets(int)", i);
            sheets_name.push_back(worksheet->property("Name").toString());
            number_of_test.push_back(0);
       }
     sheet = sheets->querySubObject( "Item( int )", 5 );
   }
 
  sheet->dynamicCall("Copy");

QAxObject* sheet;
   QStringList sheets_name;
   QAxObject* workbooks;
   QAxObject* workbook;
   QAxObject*sheets;

   QString campaign_sheet_name;
   QAxObject * excel_cell ;
   QAxWidget* excel;

   qint16 xl_test_start_line = 16;
   qint16 sheet_number;

   //Launch Excel
   if (ui->check_button_excel->isChecked())
   {
       excel = new QAxWidget("Excel.Application");
       workbooks = excel->querySubObject( "Workbooks" );
       workbook = workbooks->querySubObject( "Open(const QString&)",excel_path  );
       sheets = workbook->querySubObject( "Worksheets" );
       if ( ui->radioButton_XLVisible->isChecked())
           excel->setProperty("Visible", true);
       int count = sheets->dynamicCall("Count()").toInt();
    count = sheets->property("Count").toInt();

    //Get sheets name list
    for (int i = 1; i <= count; i++)
       {
            QAxObject * worksheet = workbook->querySubObject("Worksheets(int)", i);
            sheets_name.push_back(worksheet->property("Name").toString());
            number_of_test.push_back(0);
       }
     sheet = sheets->querySubObject( "Item( int )", 5 );
   }

  sheet->dynamicCall("Copy");

To copy to clipboard, switch view to plain text mode 

[gerardpuducul]

Nobody has met this kind of issue?

Any suggestion?

Thanks

[CharlesZuo]

I think you may have to pass the before and after parameters into the copy method. otherwise, the sheet will be copied to a new workbook.

[gerardpuducul]

hi and thanks to spend some time on my problem.
I had already think about using Before and after parameter but don't success to do anything
HOw can I pass this parameter?

I had try :
worksheet->dynamicCall("Copy (const QVariant&)","After:=Sheets(3)");
result :NOTHING COPY
or
worksheet->dynamicCall("Copy After:=Sheets(3)");
result :NOTHING COPY

Any idea?

[amleto]

the docs are quite clear on how to use arguments with dynamicCall

http://doc.qt.io/qt-4.8/qaxbase.html

QVariant one;
QVariant two;
worksheet->dynamicCall("Copy", one, two);

[gerardpuducul]

thanks for your answer!

My source code are in post 1. I don't understood why you don't have seen it.

So I know how to pass arg to dynamic call but i don't success to apply them to have working copy in the same worksheet in excel. ( or maybe argument are wrong)

But anyway i had try your tipo :

and replace line 36 of my sources code in post 1
by

QVariant one = "After:=";
   QVariant two = "Sheets(5)";
   worksheet->dynamicCall("Copy", one, two);

QVariant one = "After:=";
   QVariant two = "Sheets(5)";
   worksheet->dynamicCall("Copy", one, two);

To copy to clipboard, switch view to plain text mode 

and nothing is copy

Any suggestion?

[amleto]

It looks like you don't know how Copy works.

here is the help
http://msdn.microsoft.com/en-us/libr...(v=vs.80).aspx


Looks like this is no longer Qt programming question...

[gerardpuducul]

Hi,

Thanks a lot yours link. It help me to find the solution. I need to pass the object worksheet for before or after parameter;

so instead of

sheet->dynamicCall("Copy");

sheet->dynamicCall("Copy");

To copy to clipboard, switch view to plain text mode 

I do

worksheet_copy_after = sheets->querySubObject("Item( int )", 5 );
 QVariant param1 = worksheet_copy_after->asVariant();
 worksheet_to_copy->dynamicCall("Copy (const QVariant&)",param1);

worksheet_copy_after = sheets->querySubObject("Item( int )", 5 );
 QVariant param1 = worksheet_copy_after->asVariant();
 worksheet_to_copy->dynamicCall("Copy (const QVariant&)",param1);

To copy to clipboard, switch view to plain text mode 

and the copy occurs in the same worksheets!!!

Thanks

[gerardpuducul]

In fact I success to copy before but not After

according to : http://msdn.microsoft.com/en-US/libr...(v=vs.80).aspx

I try to pass en empty Qvaraint to the first parameter but nothing happen.

worksheet_copy_after = sheets->querySubObject("Item( int )", 5 );
QVariant param1 = "missing";
QVariant param2 = worksheet_copy_after->asVariant();
 worksheet_to_copy->dynamicCall("Copy (const QVariant&, const QVariant&)",param1,param2);

worksheet_copy_after = sheets->querySubObject("Item( int )", 5 );
QVariant param1 = "missing";
QVariant param2 = worksheet_copy_after->asVariant();
 worksheet_to_copy->dynamicCall("Copy (const QVariant&, const QVariant&)",param1,param2);

To copy to clipboard, switch view to plain text mode 

How can I do?

Thanks

[amleto]

use a null QVariant, not a QVariant with a string="missing"

[gerardpuducul]

I had already try with NULL variant (and a lot of stupid test that i prefer to not post here)

but nothing is working

worksheet_copy_after = sheets->querySubObject("Item( int )", 5 );
QVariant param1 = NULL;
QVariant param2 = worksheet_copy_after->asVariant();
worksheet_to_copy->dynamicCall("Copy (const QVariant&, const QVariant&)",param1,param2);

worksheet_copy_after = sheets->querySubObject("Item( int )", 5 );
QVariant param1 = NULL;
QVariant param2 = worksheet_copy_after->asVariant();
worksheet_to_copy->dynamicCall("Copy (const QVariant&, const QVariant&)",param1,param2);

To copy to clipboard, switch view to plain text mode 

any suggestion?

thanks

[amleto]

QVariant param1 = NULL;

does that even compile? I don't believe it is a null variant though - if it compiles it will be a QVariant of type string with string = "", or a QVariant of type int with value = 0, or something like that.

This is how you make a null variant:

QVariant param;

[gerardpuducul]

Of course it compiles. I will not post something which not compile.

worksheet_copy_after = sheets->querySubObject("Item( int )", 5 );
QVariant param1;
QVariant param2 = worksheet_copy_after->asVariant();
worksheet_to_copy->dynamicCall("Copy (const QVariant&, const QVariant&)",param1,param2);

worksheet_copy_after = sheets->querySubObject("Item( int )", 5 );
QVariant param1;
QVariant param2 = worksheet_copy_after->asVariant();
worksheet_to_copy->dynamicCall("Copy (const QVariant&, const QVariant&)",param1,param2);

To copy to clipboard, switch view to plain text mode 

Doesn't work anymore!

[amleto]

only this idea, I don't know if it will work at all:

QString after = worksheet_copy_after->asVariant().toString(); // don't know if this works!
QString call("Copy(missing,%1)");
call = call.arg(after);
 
activeX->dynamicCall(call.toLatin1().constData());

QString after = worksheet_copy_after->asVariant().toString(); // don't know if this works!
QString call("Copy(missing,%1)");
call = call.arg(after);

activeX->dynamicCall(call.toLatin1().constData());

To copy to clipboard, switch view to plain text mode 

I thought of that after reading http://msdn.microsoft.com/en-us/libr...(v=vs.80).aspx and http://qt-project.org/doc/qt-4.8/qax...ml#dynamicCall. The problem is how to deal with 'missing' correctly. If you can think of some way to access System.Type.Missing then that will probably help.

[wysota]

QVariant param1 = NULL;

does that even compile? I don't believe it is a null variant though - if it compiles it will be a QVariant of type string with string = "", or a QVariant of type int with value = 0, or something like that.

This is how you make a null variant:

QVariant param;

NULL is:

#define NULL 0L

#define NULL 0L

To copy to clipboard, switch view to plain text mode 

therefore this:

QVariant param1 = NULL;

QVariant param1 = NULL;

To copy to clipboard, switch view to plain text mode 

stores an integer of value "0" in the variant.

[gerardpuducul]

QString after = worksheet_copy_after->asVariant().toString(); // don't know if this works!
QString call("Copy(missing,%1)");
call = call.arg(after);

QString after = worksheet_copy_after->asVariant().toString(); // don't know if this works!
QString call("Copy(missing,%1)");
call = call.arg(after);

To copy to clipboard, switch view to plain text mode 

seems working !

activeX->dynamicCall(call.toLatin1().constData());

activeX->dynamicCall(call.toLatin1().constData());

To copy to clipboard, switch view to plain text mode 

but what is your activeX?

[amleto]

worksheet_to_copy