C++ program to collect sheets from workbooks

[MTPenguin]

I am trying to write a program that will collect / copy specific spreadsheets from workbooks.

So far I am able to find the workbooks and the specific sheets. Now I need to collect them together into a new workbook.

I've been able to find code to get my sheet copied.

Any any=xIndex->getByIndex(0);

//...then create an instance of Xspreadsheet, able to manage
//single worksheets;
Reference<XSpreadsheet> xSheet;

//finally, assign the first worksheet to xSheet
any >>= xSheet;


And I have a new workbook.

Reference<XSpreadsheetDocument> xSheetDocumentNew (xComponentNew,UNO_QUERY);


Now, how do I add this xSheet to xSheetDocumentNew?

[Charlie Young]

I've been looking at this, and it is trickier than I thought it would be at first.

The XSpreadsheets collection supports XNameContainer, which provides the insertByName method, but if I try to do it with an Any referring to an existing sheet

Code: Select all   Expand viewCollapse view

Reference< XSpreadsheets > newSheets = XSheetDocumentNew->getSheets();
Reference<XNameContainer> newNames(newSheets,UNO_QUERY);
newNames->insertByName(OUString::createFromAscii("SheetName"),xSheet);


I get an IllegalArgumentException. I suspect I may be missing something. and I'm still looking for it.

insertByName will work if I create a new sheet

Code: Select all   Expand viewCollapse view

Reference< XMultiServiceFactory > xMSF(xComponentNew, UNO_QUERY);
Reference<XInterface> iSheet = xMSF->createInstance(OUString::createFromAscii("com.sun.star.sheet.Spreadsheet"));
Any xSheet ;
xSheet <= iSheet;
newNames->insertByName(OUString::createFromAscii("SheetName"),xSheet);


but that's not what you're looking for, of course.

The only way I can see to accomplish the task is to do the programmatic equivalent of Move/Copy Sheet, and I don't think there is an API method for doing this, so we have to resort to the dispatcher. Here, the parameter xComponent is the source document, SourceSheet is the name of the sheet in the source document, and TargetDoc is the title of xComponentNew (see below).

Code: Select all   Expand viewCollapse view

void SheetCopy(Reference< XDispatchHelper > xDispatchHelper, Reference<XDesktop> xDesktop, Reference<XComponent> xComponent, OUString SourceSheet, OUString TargetDoc)
{
   Reference< XSpreadsheetDocument > oDoc (xComponent, UNO_QUERY);
   Reference<XSpreadsheets> oSheets = oDoc->getSheets();
   Reference<XNameAccess> nSheets(oSheets,UNO_QUERY);
   Any anySheet = nSheets->getByName(SourceSheet);
   Reference< XModel > xModel(xComponent, UNO_QUERY);
   Reference< XController > xController = xModel->getCurrentController();
   Reference< XSelectionSupplier > xSelect(xController, UNO_QUERY);
   Reference< XFrame > xFrame = xController->getFrame();
   Reference<XFramesSupplier> xFrameSetter(xDesktop,UNO_QUERY);
   xFrameSetter->setActiveFrame(xFrame);
   xSelect->select(anySheet);
   Sequence < ::com::sun::star::beans::PropertyValue > args(3);
   args[0].Name = OUString(RTL_CONSTASCII_USTRINGPARAM("DocName"));
   args[0].Value <<= TargetDoc;
   args[1].Name = OUString(RTL_CONSTASCII_USTRINGPARAM("Index"));
   args[1].Value <<= (sal_Int32) 1;
   args[2].Name = OUString(RTL_CONSTASCII_USTRINGPARAM("Copy"));
   args[2].Value <<= sal_True;
   Reference< XDispatchProvider > xDispatchProvider(xFrame,UNO_QUERY);
   xDispatchHelper->executeDispatch(xDispatchProvider,
                OUString(RTL_CONSTASCII_USTRINGPARAM(".uno:Move")),
                OUString(),
                0,
                args);
}


Now, it turns out that xComponentNew may have two different titles, one being the usual window title (ThisComponent.Title, to revert to Basic lingo), and another one contained in ThisComponent.Args (an array of PropertyValues). The title we need to supply to the "DocName" property in the above function is this latter one, so we need something like

Code: Select all   Expand viewCollapse view

OUString getArgTitle(Reference< XComponent > xComponent)
{
   OUString DocTitle;
   long i;
   Reference<XModel> xModel(xComponent,UNO_QUERY);
   Sequence < ::com::sun::star::beans::PropertyValue > args = xModel->getArgs();
   i = 0;
   while(args[i].Name != OUString(RTL_CONSTASCII_USTRINGPARAM("Title")))
   {
      i++;
   }
   args[i].Value >>= DocTitle;
   return DocTitle;
}


Then we need some way to identify our source documents and sheets. Here I ran into a limitation of c++, where it won't allow a variable length array as a structure member (yes I thought about using a vector, but I still would have problems initializing it), so I decided to include a fixed size array, 256 still being the maximum number of sheets allowed in a Calc document, I believe. Then I include nSheets specifying the number of sheets.

So this says we want 3 sheets, numbers 0, 2 and 4 from doc1, and 1 sheet, 0, from doc2.

Code: Select all   Expand viewCollapse view


struct SourceSheets {
   char *FileName;
   int nSheets;
   int SheetNumbers[256];
} xSource[] = {{"file:///C:/.../doc1.ods",3,{0,2,4}},
       {"file:///C:/.../doc2.ods",1,{0}}};
      
long nSource = sizeof(xSource)/sizeof(struct SourceSheets);


We need an XDispatchHelper

Code: Select all   Expand viewCollapse view

Reference< XDispatchHelper > xDispatchHelper( rOfficeServiceManager->createInstance(
                                        OUString( RTL_CONSTASCII_USTRINGPARAM(
                                        "com.sun.star.frame.DispatchHelper" ))), UNO_QUERY );


Then, assuming xComponentNew is loaded, we can get all the xSource[] sheets into it with

Code: Select all   Expand viewCollapse view


      Reference< XComponent > xComponentOld;
      Reference< XSpreadsheetDocument > XSheetDocumentOld;
      Reference< XSpreadsheets > oldSheets;
      Reference< XSpreadsheet > oldSheet;
      Any anyoldSheet;
      OUString oldSheetName;
      Reference<XCloseable> xClose;
      
      for(i = 0;i < nSource;i++)
      {
         xComponentOld = rComponentLoader->loadComponentFromURL(
            OUString::createFromAscii(xSource[i].FileName),
            OUString::createFromAscii("_blank"),
            0,
            args);
         
         XSheetDocumentOld = Reference<XSpreadsheetDocument>(xComponentOld,UNO_QUERY);
         oldSheets = XSheetDocumentOld->getSheets();
         iSheets = Reference<XIndexAccess>(oldSheets,UNO_QUERY);
         
         for(j = 0;j < xSource[i].nSheets;j++)
         {
            anyoldSheet = iSheets->getByIndex(xSource[i].SheetNumbers[j]);
            oldSheetName = Reference<XNamed>(anyoldSheet,UNO_QUERY)->getName();
            SheetCopy(xDispatchHelper, xDesktop, xComponentOld, oldSheetName, getArgTitle(xComponentNew));
         }
         
         xClose = Reference<XCloseable>(xComponentOld,UNO_QUERY);
         xClose->close(sal_True);
      }


I hope this addresses your problem, but it wouldn't bother me in the least if someone came up with something simpler.

Edit: I should also add that there are other problems with copying sheets between documents, at least two that I can think of.

1) If the source sheet contains formulas referring to other sheets in the source document, these will likely break, producing #REF! errors.

2) If the source sheet contains formulas using user defined functions from the source document's libraries, and the target document doesn't have the same functions defined, these will produce #MACRO? errors.