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?
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.
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” });
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):
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:
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.
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.
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.