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");

[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

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);


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/library/microsoft.office.tools.excel.worksheet.copy(v=vs.8 0).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");

I do


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

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/library/microsoft.office.tools.excel.worksheet.move(v=vs.8 0).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);


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);

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);

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());


I thought of that after reading http://msdn.microsoft.com/en-us/library/ms178843(v=vs.80).aspx and http://qt-project.org/doc/qt-4.8/qaxbase.html#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

therefore this:


QVariant param1 = NULL;

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);

seems working !


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

but what is your activeX?

[amleto]

worksheet_to_copy