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?
C++ program to collect sheets from workbooks
C++ program to collect sheets from workbooks
B-)
Libreoffice 4.0.1 on OpenSuse 12.3
Libreoffice 4.0.1 on OpenSuse 12.3
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: C++ program to collect sheets from workbooks
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
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
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).
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
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.
We need an XDispatchHelper
Then, assuming xComponentNew is loaded, we can get all the xSource[] sheets into it with
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.
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
Reference< XSpreadsheets > newSheets = XSheetDocumentNew->getSheets();
Reference<XNameContainer> newNames(newSheets,UNO_QUERY);
newNames->insertByName(OUString::createFromAscii("SheetName"),xSheet);
insertByName will work if I create a new sheet
Code: Select all
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
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);
}
Code: Select all
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;
}
So this says we want 3 sheets, numbers 0, 2 and 4 from doc1, and 1 sheet, 0, from doc2.
Code: Select all
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);
Code: Select all
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
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.
Apache OpenOffice 4.1.1
Windows XP
Windows XP