How to determine used area for a XSpreadsheet?

The Application Programming Interface and the OASIS Open Document Format

How to determine used area for a XSpreadsheet?

Postby digitalsanctum » Wed Oct 02, 2013 11:44 pm

I'm currently using the following to determine the used area for a spreadsheet:

Code: Select all   Expand viewCollapse view
private XUsedAreaCursor getUsedAreaCursor(XSpreadsheet sheet) throws Exception {
        // Find the used area for the spreadsheet
        XSheetCellCursor cursor = sheet.createCursor();
        XUsedAreaCursor usedCursor = unoProvider.queryInterface(XUsedAreaCursor.class, cursor);
        usedCursor.gotoStartOfUsedArea(true);
        usedCursor.gotoEndOfUsedArea(true);
        return usedCursor;
}


Then, based on this I'm calculating a scaled print area. The issue is that XUsedAreaCursor doesn't seem to account for merged cells.

Is there a more accurate way to determine the used area which also accounts for merged cells?

Thanks!
CentOS 6, OpenOffice 3.1
digitalsanctum
 
Posts: 1
Joined: Wed Oct 02, 2013 11:36 pm

Re: How to determine used area for a XSpreadsheet?

Postby Charlie Young » Sat Oct 05, 2013 4:25 pm

For me at least, you need to present an example in which merged cells are not being properly accounted for.
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: How to determine used area for a XSpreadsheet?

Postby Charlie Young » Sat Oct 05, 2013 9:19 pm

Charlie Young wrote:For me at least, you need to present an example in which merged cells are not being properly accounted for.


Guessing that the problem is when the merged cells are on the periphery of the used area, you might try

Code: Select all   Expand viewCollapse view
private XUsedAreaCursor getUsedAreaCursor(XSpreadsheet sheet) throws Exception {
        // Find the used area for the spreadsheet
        XSheetCellCursor cursor = sheet.createCursor();
        XUsedAreaCursor usedCursor = unoProvider.queryInterface(XUsedAreaCursor.class, cursor);
        usedCursor.gotoStartOfUsedArea(true);
        usedCursor.gotoEndOfUsedArea(true);
        cursor.collapseToMergedArea();
        return usedCursor;
}


Note that collapseToMergedArea() is a method of XSheetCellCursor and not XUsedAreaCursor.
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: How to determine used area for a XSpreadsheet?

Postby Villeroy » Sun Oct 06, 2013 4:57 pm

I put some data in B9:E29 and then merged B29:E32.
The used area is B9:B29 which is "optically wrong".
Now I can .collapseToMergedArea() which expands the range to B9:E32.
Short Basic demo:
Code: Select all   Expand viewCollapse view
Sub Main
sh = thiscomponent.sheets.getbyindex(0)
curs = sh.createCursor()
curs.gotoStartOfUsedArea(0)
curs.gotoEndOfUsedArea(1)
print curs.AbsoluteName
curs.collapseToMergedArea()
print curs.AbsoluteName
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27753
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 1 guest