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.