| View previous topic :: View next topic |
| Author |
Message |
StefanbNamed
General User
Joined: 02 Jul 2007
Posts: 10
|
 Posted: Mon Aug 06, 2007 3:51 am Post subject: Calc: Search and Replace |
 |
|
Dear Forum,
I'm a newbie concerning OOO programming. I have a very standard question and I have tried to find some answers on the forum but was not able to locate the correct one.
I have an .ODS sheet in which I have some values in a cell. I would like to replace the content of the cell with another content.
So for example: in cell "F9"(of Sheet 1) I have the value %%ClientName%%, I would like to replace %%ClientName%% by "Stefan".
This is my code (JAVA):
| Code: | com.sun.star.util.XReplaceable xReplaceable = xReplaceable = (com.sun.star.util.XReplaceable)
UnoRuntime.queryInterface(com.sun.star.util.XReplaceable.class, xCellRanges);
com.sun.star.util.XReplaceDescriptor xReplaceDescriptor = xReplaceDescriptor = xReplaceable.createReplaceDescriptor();
xReplaceDescriptor.setSearchString(key); //key would be "%%ClientName%% "
xReplaceDescriptor.setReplaceString(value); //value would be 'Stefan'
xReplaceDescriptor.setPropertyValue("SearchWords", new Boolean(true)); //also tried with false
int nCount = xReplaceable.replaceAll(xReplaceDescriptor); |
Unfortunately nCount is always 0 and nothing has been replaced.
Does anybody have a clue how to solve this basic problem?
Thanks in advance,
Stefan |
|
| Back to top |
|
 |
StefanbNamed
General User
Joined: 02 Jul 2007
Posts: 10
|
| Posted: Wed Aug 29, 2007 7:00 am Post subject: Search and Replace |
|
|
Dear Forum,
I still am not able to search and replace values in my Calc.
For example I have in cell C5 the word "%%ClientName%%" and I would like to change it by "Stefan".
Can anybody help me to give me simple example?
Thanks in advance,
Stefan Tysbaert
Belgium |
|
| Back to top |
|
|
DiGro
Super User
Joined: 02 Jun 2004
Posts: 1208
Location: Hoorn NH, The Netherlands
|
| Posted: Wed Aug 29, 2007 9:21 am Post subject: Re: Search and Replace |
|
|
| StefanbNamed wrote: | | Can anybody help me to give me simple example? |
This should replace %%ClientName%% on page 1 to Stefan
(It will replace ALL occurences of %%ClientName%% on that page)
| Code: | Sub ReplaceInCalc
Dim oDoc As Object
Dim oSheet As Object
Dim oReplace As Object 'Replace Descriptor (ScCellSearchObj)
Dim lNumReplace As Long
oDoc = ThisComponent
oSheet = oDoc.Sheets.getByIndex(0)
oReplace = oSheet.createReplaceDescriptor()
oReplace.SearchCaseSensitive = True
oReplace.SearchWords = False
oReplace.SearchRegularExpression = False
oReplace.SearchBackwards = False
oReplace.SearchSimilarity = False
oReplace.SearchSimilarityRelax = False
oReplace.SearchStyles = False
oReplace.SearchByRow = True
oReplace.SearchString = "%%ClientName%%"
oReplace.ReplaceString = "Stefan"
lNumReplace = oSheet.replaceAll(oReplace)
End Sub |
_________________
DiGro
Windows 7 Home Premium and OOo 3.3 NL (Dutch)
Last edited by DiGro on Wed Aug 29, 2007 10:17 am; edited 1 time in total |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Aug 29, 2007 9:47 am Post subject: |
|
|
| I was about to post something similar as DiGiro. There is another property "SearchType", which can be zero or one, analogue to the dialog's "search in formulas or values". |
|
| Back to top |
|
|
StefanbNamed
General User
Joined: 02 Jul 2007
Posts: 10
|
| Posted: Wed Aug 29, 2007 11:02 pm Post subject: thnx |
|
|
Thank you,
I will try it and will come back on this issue ASAP
Stefan
Belgium |
|
| Back to top |
|
|
vbms
Power User
Joined: 13 Aug 2007
Posts: 63
|
| Posted: Thu Aug 30, 2007 12:46 am Post subject: |
|
|
or perhaps if you want to replace multiple placeholders dirtily you could do something link this
| Code: |
private void parseCalc (XComponent documentComponent) throws Exception {
// get the spread sheets form the document component
XSpreadsheetDocument xSpreadsheetDocument = (XSpreadsheetDocument)
UnoRuntime.queryInterface(XSpreadsheetDocument.class, documentComponent);
XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
XIndexAccess xIndexedSheets = (XIndexAccess)
UnoRuntime.queryInterface(XIndexAccess.class, xSpreadsheets);
for (int isheet=0; isheet<xIndexedSheets.getCount(); isheet++) {
// get the current sheet
Any t_spreadsheet = (Any) xIndexedSheets.getByIndex(isheet);
XSpreadsheet xSpreadsheet = (XSpreadsheet)
UnoRuntime.queryInterface(XSpreadsheet.class, t_spreadsheet);
// create a cell range with entire used table in it
XSheetCellCursor xSheetCellCursor = xSpreadsheet.createCursor();
XUsedAreaCursor xUsedAreaCursor = (XUsedAreaCursor)
UnoRuntime.queryInterface(XUsedAreaCursor.class, xSheetCellCursor);
xUsedAreaCursor.gotoStartOfUsedArea(false);
xUsedAreaCursor.gotoEndOfUsedArea(true);
// get the data in the table as 2d array
XCellRange xCellRange = (XCellRange)
UnoRuntime.queryInterface(XCellRange.class, xUsedAreaCursor);
XCellRangeData xCellRangeData = (XCellRangeData)
UnoRuntime.queryInterface(XCellRangeData.class, xCellRange);
Object [][] obj_cellData = xCellRangeData.getDataArray();
for (int iy=0; iy<obj_cellData.length; iy++)
for (int ix=0; ix<obj_cellData[iy].length; ix++) {
obj_cellData[iy][ix] = (Object) replaceNameValueTokens
((String)obj_cellData[iy][ix]);
}
xCellRangeData.setDataArray(obj_cellData);
}
}
|
|
|
| Back to top |
|
|
|
