| View previous topic :: View next topic |
| Author |
Message |
groupie
General User
Joined: 26 Jan 2008
Posts: 27
|
 Posted: Fri Mar 27, 2009 10:04 pm Post subject: Text cursors in Calc cells |
 |
|
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 |
|
 |
vitcaro
OOo Advocate
Joined: 20 Feb 2007
Posts: 256
Location: Italy
|
| Posted: Sat Mar 28, 2009 1:03 am Post subject: |
|
|
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 |
|
|
groupie
General User
Joined: 26 Jan 2008
Posts: 27
|
| Posted: Sat Mar 28, 2009 1:08 am Post subject: |
|
|
| 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 |
|
|
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2478
Location: Chonburi Thailand Asia
|
| Posted: Sat Mar 28, 2009 1:29 am Post subject: |
|
|
| 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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sat Mar 28, 2009 3:49 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
|
|
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
|
