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

Text cursors in Calc cells

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
groupie
General User
General User


Joined: 26 Jan 2008
Posts: 27

PostPosted: Fri Mar 27, 2009 10:04 pm    Post subject: Text cursors in Calc cells Reply with quote

So I have a spreadsheet that has a columns that contains cell content such as

text <b>should be bold</b> more text

And I want to write a macro that (1) finds the <b> and </b> (2) bolds the text between (3) removes the <b> and </b>.

Here's what I wrote:



Code:
Function FindTextRange(oDoc As Object, sTag As String) As Integer
   Dim vSearch, vFound, counter As Integer
   counter = 0
   oText = oDoc.getText()
   vSearchStart = oDoc.createSearchDescriptor()
   StartTag = "<" & sTag & ">"
   EndTag = "</" & sTag & ">"
   With vSearchStart
      .SearchString = StartTag
      .SearchWords = False
      .SearchCaseSensitive = False
   End With
   vSearchEnd = oDoc.createSearchDescriptor()
   With vSearchEnd
      .SearchString = EndTag
      .SearchWords = False
      .SearchCaseSensitive = False
   End With
   vFoundStart = oDoc.findFirst(vSearchStart)
   Do While Not IsNull(vFoundStart)
      start = vFoundStart.getStart()

         print vFoundStart.getString()
         vFoundStart.setString("")

      vFoundEnd = oText.findFirst(vSearchEnd)
      finish = vFoundEnd.GetEnd()
      vFoundEnd.setString("")
      
      oTCurs = oText.CreateTextCursorByRange(start)
      oTCurs.gotoRange(finish, True)
      oTCurs.setString("")
      
      oTCurs.gotoRange(start, False)
      oTCurs.gotoRange(finish, True)
      oTCurs.CharWeight = com.sun.star.awt.FontWeight.BOLD
      counter = counter + 1
      
      vFoundStart = oDoc.findNext(vFoundStart.End, vSearchStart)
   Loop
   FindTextRange = counter
End Function

sub convertndb
   oTSV = thiscomponent
   ndb = oTSV.Sheets( 0 )
   rng = GetUsedRange( ndb )
   lastcol = rng.Columns.getCount() - 1
   lastrow = rng.Rows.getCount() - 1
   ix = 9
   fp = 0
   for iy = 1 to lastrow
      scell = ndb.getCellByPosition(ix, iy)
      content = scell.getText()
      fp = fp + FindTextRange(scell, "b")
   next
   inputbox "bolded: ", "ShowBolded", fp
end sub


The problem is that even though the findFirst succeeds (we get in the while loop), the first cell is wiped out, by the setString. The preceding debug print statement shows that the whole content of the cell is found, rather than just the "<b>", so that explains why the setString wipes it out, I guess. But I thought, hoped, and found examples of code like this, so I'm confused why the findFirst result would be the whole content of the cell.
Back to top
View user's profile Send private message
vitcaro
OOo Advocate
OOo Advocate


Joined: 20 Feb 2007
Posts: 256
Location: Italy

PostPosted: Sat Mar 28, 2009 1:03 am    Post subject: Reply with quote

I have not tested your code, but may be you must create the SearchDescriptor at level of sheet in place of at level of cell:
Code:

dim vSearchStart 'definition at module level
........
sub convertndb
..............
ndb = oTSV.Sheets( 0 )
vSearchStart = ndb.createSearchDescriptor()
Back to top
View user's profile Send private message
groupie
General User
General User


Joined: 26 Jan 2008
Posts: 27

PostPosted: Sat Mar 28, 2009 1:08 am    Post subject: Reply with quote

Quote:
I have not tested your code, but may be you must create the SearchDescriptor at level of sheet in place of at level of cell:

Maybe so, but then how do I search in just one cell?
Back to top
View user's profile Send private message
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Sat Mar 28, 2009 1:29 am    Post subject: Reply with quote

groupie wrote:
how do I search in just one cell?

Create the SearchDescriptor on a cell range.


See example:

Code:
oSheet = ThisComponent.getSheets().getByIndex(0)
oRange = oSheet.getCellRangeByName("F7:H11")
oFoundCell = SimpleSheetSearch("41", oRange, False)


in Andrew's Macro Document
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Mar 28, 2009 3:49 am    Post subject: Reply with quote

If your problem is related to Make *part of* a string in a cell bold using a function, you can use the search result containing all the cells as a container of c.s.s.sheet.SheetCellRanges.
Loop through the collection which yields single ranges, loop through the cells of each range and "dive into" the cell getting it's Text-object.

This one handles one range, all cells in the range, the text within the cell, creating a text cursor, looping through paragraphs and differently formatted portions within each paragraph: http://www.oooforum.org/forum/viewtopic.phtml?t=47340
Create a new search descriptor for the XText in the cell.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API 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