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

Free Support Forum - aspose.com

Exception when using dynamic formula for Smart Markers

I’m creating a worksheet using smart markers, but when I try to add a dynamic formula, I get an exception:
“Object reference not set to an instance of an object.”
System.Exception System.NullReferenceException
Stack Trace: StackTrace " at Aspose.Cells.WorkbookDesigner.xbd3c5d4bb9388cf9(ICollection x8a0b266419f09a55, ArrayList xf2a25b503c953254)\r\n at Aspose.Cells.WorkbookDesigner.x1d0794d77ef25d7b(Cells x77bb6a53fbd162d0, Int32 xa806b754814b9ae0, ArrayList x59cd53e74984f0dd)\r\n at Aspose.Cells.WorkbookDesigner.xda4090820d40dd27(Int32 x7ac6da855c54c2a5, Boolean x251a1d4551a5fee6)\r\n at Aspose.Cells.WorkbookDesigner.Process(Boolean isPreserved)\r\n

I’ve tried variations of which columns to include in the dynamic formula, but all result in the same exception when calling Designer.Process. If I remove the dynamic formula, the smartMarkers succeed.

Is there anything else on the worksheet that needs to be set or configured for this to work?

My dynamic formula for testing is something very simple like:
&=&=C{r}+1 even &=&=C{r} fails, but without it, the smart marker places value in C correctly.

The smart marker for Column C is &=SummaryData.ResponseCount(numeric). In the custom object, the ResponseCount is a decimal type.

Also, is it possible to combine data in smart marker with dynamic formula:
&=&=C{r}*&=SummaryData.ResponseCount




Hi,

1) I’ve tried variations of which columns to include in the dynamic
formula, but all result in the same exception when calling
Designer.Process. If I remove the dynamic formula, the smartMarkers
succeed.

Is there anything else on the worksheet that needs to be set or configured for this to work?

My dynamic formula for testing is something very simple like:
&=&=C{r}+1 even &=&=C{r} fails, but without it, the smart marker places value in C correctly.

Well, I tried implementing your scenario using the following code with v4.8.1.x and it works fine.

sample code:


//Instantiate the workbookdesigner object.
WorkbookDesigner report = new WorkbookDesigner();
//Get the first worksheet(default sheet) in the workbook.
Aspose.Cells.Worksheet w = report.Workbook.Worksheets[0];

//Input some markers to the cells.
w.Cells[“A1”].PutValue(“Test”);
w.Cells[“A2”].PutValue("&=MyProduct.Name");
w.Cells[“B2”].PutValue("&=MyProduct.Age");
w.Cells[“C2”].PutValue("&=&=B{r}+1");

//Instantiate the list collection based on the custom class.
IList list = new List();
//Provide values for the markers using the custom class object.
list.Add(new MyProduct(“Simon”, 30));
list.Add(new MyProduct(“Johnson”, 33));

//Set the data source.
report.SetDataSource(“MyProduct”, list);

//Process the markers.
report.Process(false);

//Save the excel file.
report.Workbook.Save(“f:\test\outfile.xls”);

If you still find any issue kindly create a simple test code(as above) and post it here to show the issue, we will check it soon.

2) The smart marker for Column C is
&=SummaryData.ResponseCount(numeric). In the custom object, the
ResponseCount is a decimal type.

Also, is it possible to combine data in smart marker with dynamic formula:
&=&=C{r}*&=SummaryData.ResponseCount

Well, this is not supported and you need to have some markers in hidden columns then use dynamic formula accordingly e.g
&=&=C{r}*C{r}


Thank you.





Hi,

I’ve read in several Aspose posts that it is not possible to use smark markers in dynamic formulae. I suppose that this feature has been proposed/requested by many other Aspose users.

The workaround proposed, is to use hidden columns whose values are then used in the dynamic formula. Even if this works, it is not an elegant solution, especially when generated Excel documents are sent to our demanding clients.

We are planning to renew our current Aspose subscription in order to benefit of the new features of Aspose Cells.

Regarding this matter, we would like to know if the Aspose team intends to add the use of smark markers in dynamic formulae as a new feature to Aspose Cells. If yes, what could be the release date of this feature ?

Thanks in advance for your reply.

Kind regards.
Sh@h

Hi,

Well, dynamic formulas are supported but you need to follow some standard to use them in Smart Markers, some examples are given below:
&=&=A{r}*B{r}
&=&=SUM(B{-2}:B{-1})
&=&=FREQUENCY(A{r}:B{r},C{r}:D{r})
&=&=REPLACE(A{r},1,1,“Test”)
&=&=Concatenate(A{r}, "Some text ")
etc.

Anyways, we will get back to you soon.

Thank you.