We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Find and Replace inner texts

Aspose.Cells Product Family
#1

Hi. I worked in Aspose Words and now I need to work on Aspose Cells. Currently I need to do a program that find special word in the worksheets and replace them. As Aspose Word does there is an Equivalent Class or Method IReplacingCallback or maybe something based on Regular Expression to find those word?


Thank you
#2

Hi,


Please see the document for reference:
http://www.aspose.com/docs/display/cellsnet/Find+or+Search+Data
There are number of options available to find a string/value / part of string/value in the cells in the worksheet. You may use Cells.FindXXXX methods.

Thank you.
#3

Hi,

Thanks for using Aspose.Cells.

Please also check the Offline - Aspose.Cells for .NET Demos to getfamiliar of Aspose.Cells API(s) quickly.

These demos are available in C# and you can run them in Visual Studio 2005 , 2008 or 2010 or 2012 or 2015 or 2019 .

Please read the readme.txt file before running the demos.

You will also find there a Find & Replace demo as discussed in this article.

#4

Hi, Thank you for both responses. I read about Smart Markers but I have some questions:


1. I Tried to replace the following text contained in a cell: “Personal Monthly Budget &=$namePerson” with this code:
designer.SetDataSource(“namePerson”, rand.Next(9999));

Is there any way to change the variable or only can be change the variable if it is alone in the cell?

2.How I can change a value that is contained in a formula? for example
&==C12+C16+ &=$innerFormula
I tried this but occurs the same problem of the question 1.

3. Like the method PutValue that has the parameter isConverted there is a form to replace values with Smart Markers with the cell format?. I make this question because my test the replaces don't keeps the original format.

Thank you
#5

Hi,


1) Please use the Variables markers in the proper way/format, see the sample code below for Variable Array example. Your variable marker should be in proper format.

Sample code:
e.g

WorkbookDesigner report = new WorkbookDesigner();
Aspose.Cells.Worksheet w = report.Workbook.Worksheets[0];
w.Cells[“A1”].PutValue("&=$VariableArray");
report.SetDataSource(“VariableArray”, new string[] { “English”, “Arabic”, “Hindi”, “Urdu”, “French” });
report.Process(false);
report.Workbook.Save(“e:\test2\out_varaiblearray.xls”);

2) Same is the case as 1). Your dynamic formula markers should be in proper format. The way you are doing is not supported as the rows are inserted dynamically when the markers are processed and the data is filled row by row.

You have to use dynamic formula feature of Smart Markers accordingly for your needs, see an example below, you need to set your markers in the following way.

Excel Template:
e.g
A B

&=DataSource.FieldName &=&=(A{r}*100)


(where r refers to the current row, you may use r +/- i (where i refers to any number/offset))

Moreover, you may use Concatenate function to mix or concatenate some text with the values e.g (A refers to A column and r refers to the current row):

&=&=Concatenate(A{r}, "Some text ")

Also, Smart marker dynamic formula (mixed) in a cell can be: &=&=TEXT(H{r},“0”)&"Y "&TEXT(I{r},“0”)&“M”. Another example is: &=&=IF(K{r}=0,-1,(L{r}-F{r})/F{r})

3) You may use “copystyle” parameter, see the document (especially under the “Parameters” sub heading):
http://www.aspose.com/docs/display/cellsnet/Smart+Markers




#6

Thank you Amjad for your response.


For the first question my really problem is that the variable for the Smart Markers is not the only string in the cell, for examplate if I have this text in a cell “The color of the [productName] is red” I want to replace the “[productName]” for the database name of the product. There is any way to acomplish this using the Smart Markers?

I currently have a problem using the Cells.Find method when is used with Formulas and Regex. I have this formula en a cell:
=C12+C16+VALUE("[innerFormula]")
And this is the program code:

FindOptions findOptions = new FindOptions() { LookAtType = Aspose.Cells.LookAtType.EntireContent, LookInType = LookInType.OnlyFormulas, SearchNext = true, RegexKey = true };
cellFinded = cells.Find(@"[([\w])*]", null, findOptions))

but it don’t find anything. Can you help me with this issue?

Thank you
#7

Hi,


1) For your first question, I am afraid, currently you cannot add text to conventional smart markers. As a workaround you may have a column (that should be hidden in the template file, in this column you will use smart marker to get productName). Now you may use Concatenate function in the following way for the dynamic formula marker e.g

e.g
H (this column can be hidden) J

&=DataSource.ProductName &=&=Concatenate(“The color of the “, H{r}, " is red”)

2) I am not sure how you are using / how could you use your formula i.e…, “=C12+C16+VALUE(”[innerFormula]”)" in MS Excel. Please give us your template Excel file (with the formula) and sample code or prefrably a sample console application (you may zip it prior attaching here) to show the issue, we will check it soon.

Thank you.
#8

No problem Amjad. In the zip it’s the program and the file. The formula it’s at O11 cell.


Than you for your help
#9

Hi,

Thanks for your posting and using Aspose.Cells.

It seems, you have forgotten to attach your complete project. Your attachment is just 475 bytes which contains a solution file of size 875 compressed bytes.

Please see the following screenshot for your reference and attach your complete archive to look into this issue.

Screenshot:

#10

You are right, my bad. Here its the correct zip file

#11

Hi,


Thanks for sharing the sample project with template file.

I can notice the issue as you have pointed out after an initial test by using your sample project. It looks like the Find method searches values fine but not formulas. We will investigate it soon.

Output Console by running the project:

Normal
Values
Personal Monthly Budget [namePerson]
[waterP]
[waterA]
[cableP]
[cableA]
[wasteP]
[wasteA]
Formulas
System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative
and less than the size of the collection.

I have logged a ticket with an id: “CELLSNET-41192” for your issue. We will look into it soon.

Thank you.
#12

Hi,

We have fixed this issue.

Please download and try this fix: Aspose.Cells for .NET (Latest Version) and let us know your feedback.

#13

The issues you have found earlier (filed as CELLSNET-41192) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.