Re: [Libreoffice] [PUSHED] How to create get an XWorksheet from a XSpreadsheet?

Hi Markus
 On 15/04/11 01:40, Markus Mohrhard wrote:
> And again with the patches.
>
> 2011/4/15 Markus Mohrhard <markus.mohrhard@googlemail.com 
> <mailto:markus.mohrhard@googlemail.com>>
>
>     Hi Noel,
>
>     here is the patch without the conversion from XSpreadsheet to
>     XWorksheet.
>
>     I found some nice Code in ScVbaWorkbook::getActiveSheet() where
>     nearly the same thing was done. But the following attempt didn't
>     work, and created always an error message in getDocUnoModule()
>
>     uno::Reference<frame::XModel> xNewModel( getCurrentExcelDoc(
>     mxContext ), uno::UNO_SET_THROW );
>     ScDocShell* pShell = excel::getDocShell( xNewModel );
>     if( !pShell )
>     throw uno::RuntimeException();
>     ScDocument* pDoc = pShell->GetDocument();
>     String aCodeName;
>     pDoc->GetCodeName((SCTAB)0 ,aCodeName);
>     return uno::Reference< excel::XWorksheet >( getUnoDocModule(
>     aCodeName, pShell ), uno::UNO_QUERY_THROW );
>
>     With a debugger you see that pShell points to the correct
>     ScDocShell and aCodeName points to the CodeName of the ScTable but
>     it still won't work.
>
>     Perhabs you know why my idea won't work. Nearly the same code
>     works fine with ScVbaWorkbook::getActiveSheet()
ok, first using getCurrentExcelDoc is not really recommended ( when we 
actually know the target document ) 'active' is a little arbitrary and I 
am not sure in this case whether there is a timing issue between the 
document being created and being recognised as 'active'. But regardless 
of that the getUnoDocModule is not going to succeed as the (new) 
document is not vba enabled. Some post processing of the document is 
needed to ensure that works, like I said.. if you are interested in 
hacking on that as a separate issue then I can give you pointers how 
that is done ( actually it is already done in ScVbaWorkbooks ( see 
setUpDocumentModules method ) ) but that method would need to be 
generally available in excelvbahelper I think
>     Patch is under LGPLv3+/MPL.
>
>     Regards,
>     Markus
>
>     P.S. Even some really strange ways of using the
>     setUpDocumentModules(const uno::Reference<
>     sheet::XSpreadsheetDocument >) of vbaworkbooks.cxx produces an
>     error. But this seems a good way to solve the problem. If you can
>     give me any hints why this won't work I will try again on the
>     conversion
hmm not without looking more closely at that ( which I will try to do 
later ) it imo should work

now on to the patch :-)

in vbaworksheet.cxx

+    //#TODO #FIXME
+    //return the new XWorksheet
+    return;

I meant to not return NULL but pass NULL as the parent of the Worksheet 
;-)  fixed that ( of course we need to look into the problems mentioned 
above to see what we can do to get the correct parent )

A few more changes were needed to vbaworksheets, mostly these changes 
were required because on testing it became clear that things weren't 
working exactly as expected and there are a few bugs e.g.calling
   Worksheets.Copy()
creates a copy of the sheets in a new workbook but there are in reverse 
order

Similarly when calling
   Worksheets.Copy before:= Worksheets(2)
the sheets are also copied in reverse order. I fixed those 2 problems 
and additionally reworked the method and removed one of the loops in the 
ScVbaWorksheets::Copy method

thanks again for this useful patch, look forward to you next contribution

Noel