OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

A fast algorithm for Cells.SpecialCells(xlCellTypeLastCell)

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets
View previous topic :: View next topic  
Author Message
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Sun Jul 16, 2006 4:13 pm    Post subject: A fast algorithm for Cells.SpecialCells(xlCellTypeLastCell) Reply with quote

I was an IT software engineer (now IT manager, sorry) but I keep current by tracking current technologies. I've been looking at the development of OOo over the last few years with interest, though one of the things that has put me off moving over is that I often use the VB family primarily VBA/Excel but also VBA/Outlook, VBA/Access and Vbscript. The OOo scripting paradigms are sufficiently different to cause quite a barrier to entry (BTW the OOo reference material here really needs expanding). However, I felt it was now time to get grips with OOo scripting, and one of the first thinks that I noticed was the lack of a method that I frequently use: .SpecialCells(xlCellTypeLastCell), typically where I want to munge through every line on CSV that I've just loaded and
Code:

nRows =  Cells.SpecialCells(xlCellTypeLastCell).Row – 1

is a pretty efficient way of getting a line count for a for loop. I was uncomfortable with the MoveCursorToEnd(pCellRange, pDirection) example in “Porting Excel/VBA to Calc/StarBasic” for two reasons (i) it uses a for loop search, and (ii) it includes embedded constants which are in fact wrong. So as a training exercise I tried to think of the simplest fast way of doing this and came up with this function, which typically executes in less than one SystemTick on my laptop.
Code:

Function FindLastCell() as Object
'
' Rather than use do-loop searches of the sheet, this does two searches: one Row-wise and one
' Column-wise backwards for the whole worksheet.  The trick for matching ANY non-blank cell is
' to do a Regexp search with a "match any character" pattern.  These two searches will stop on a
' cell in the last row and column respectively.  Note that this doesn't exactly match the
' VBA Cells.SpecialCells(xlCellTypeLastCell) which finds the highwater-marked last cell.
'
    Dim nLastCol As Long, nLastRow As Long
    Dim oSheet As Object, oSheetRange As Object, oSheetDesc As Object, oFound As Object

   On Error Goto FindLastCell_Error
   
   ' Set up objects for current sheet and the Search Descriptor

    oSheet = ThisComponent.CurrentSelection.getSpreadsheet()

    ' New do the biz with the double search using the /./ RegExp
   
    oSheetDesc = oSheet.createSearchDescriptor()
    With oSheetDesc
        .SearchByRow = True
        .SearchBackwards = True
        .SearchWords = False
        .SearchRegularExpression = True
        .SearchString = "."
    End With
    nLastRow = oSheet.findFirst(oSheetDesc).getRangeAddress().EndRow
    oSheetDesc.SearchByRow = False
    nLastCol = oSheet.findFirst(oSheetDesc).getRangeAddress().EndColumn
    FindLastCell = oSheet.getCellByPosition(nLastCol,nLastRow)
    Exit Function

FindLastCell_Error:
   '
   ' Errors are thrown if
   ' *   ThisComponent does not support the service "SpreadsheetDocument" (.getSpreadSheet() throws)
   ' *   The sheet has no non-blank cells (.findFirst() is Null so .getRangeAddress() throws)
   ' In these cases the correct recovery is:
   '
   FindLastCell = Nothing
End Function


This could easily be modified to do a single search of just Range("A:A") for a Row count only.

Comments and improvements?
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
Back to top
View user's profile Send private message Visit poster's website
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Mon Jul 17, 2006 4:49 pm    Post subject: Reply with quote

Sorry guys, I need to RTFM a bit more. After a bit more trawling I came across the com.sun.star.sheet.XUsedAreaCursor interface whic contains the gotoEndOfUsedArea method giving the following code. I also upped the timing loop to increase the timing precision. On my poor old laptoo, this clocks at 1.4mS to do a select based on this one rather than 7.3mS for version 0.
Code:
Function FindLastCell1( ) As Object

   Dim oSheet as Object, oCellCursor As Object, oLastCell As Object
   On Error Goto FindLastCell1_Error

   ' Set up objects for current sheet and the Search Descriptor

   oSheet = ThisComponent.CurrentSelection.getSpreadsheet()
   oCellCursor = oSheet.createCursor()
   oCellCursor.gotoEndOfUsedArea(False)
   oLastCell = oCellCursor.getRangeAddress()
   FindLastCell1 = oSheet.getCellByPosition(oLastCell.EndColumn, _
                                            oLastCell.EndRow )
    Exit Function

FindLastCell1_Error:
   '
   ' Errors are thrown if
   ' *   ThisComponent does not support the service
         "SpreadsheetDocument" (.getSpreadSheet() throws)
   ' In these cases the correct recovery is:
   '
   FindLastCell1 = Nothing
End Function

_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group