Use Data Markers to update cell formula

Aspose.Cells Product Family
1

Hi Team,

I am using Aspose Cells for excel.
Can we use Data Markers to update cell formula for rows inside a sheet ?


2

Hi,


Thanks for your query.

I am not entirely certain about your query. Do you mean to use Smart Markers to update formulas in the cells or series’ data marker inside a chart? Could you elaborate it more and provide us a sample Excel file(s) to demonstrate your requirements, we will check it soon. Also, provide details on how to implement using MS Excel options/features if you are talking about data markers of a charts’ series especially.

Thank you.
3

Yes, we want to update cell formulas using smart markers. Can you please help on this ?

4

Hi,

We have recently supported “formula” tag/parameter in Smart Markers. Please try our latest version Aspose.Cells for .NET v17.4.6:
Aspose.Cells for .NET v17.4.6 (.NET 2.0) compiled in .NET Framework 2.0.
Aspose.Cells for .NET v17.4.6 (.NET 4.0) compiled in .NET Framework 4.0.

See the following sample with the attached template file (containing smart markers) for your reference:
e.g
Sample code:

DataTable dataTable = new DataTable("Table1");

dataTable.Columns.Add(new DataColumn("Quantity", typeof(int)));

dataTable.Columns.Add(new DataColumn("UnitPrice", typeof(int)));

dataTable.Columns.Add(new DataColumn("Amount", typeof(string)));

int[] q1 = { 2, 3, 4, 1, 5 };

int[] up1 = { 100, 200, 300, 400, 500 };

string[] a1 = { "=A2*B2", "=A3*B3", "=A4*B4", "=A5*B5", "=A6*B6" };

DataRow dataRow;

DateTime currentDate = DateTime.Now;

for (int i = 0; i < 5; i++)

{

dataRow = dataTable.NewRow();

dataRow["Quantity"] = q1[i];

dataRow["UnitPrice"] = up1[i];

dataRow["Amount"] = a1[i];

dataTable.Rows.Add(dataRow);

}

Workbook workbook = new Workbook("e:\\test2\\sm_formulas1.xlsx");

WorkbookDesigner designer = new WorkbookDesigner();

designer.Workbook = workbook;

designer.SetDataSource(dataTable);

designer.Process(true);

OoxmlSaveOptions so = new OoxmlSaveOptions();


workbook.Save(“e:\test2\out1.xlsx”);

Hope, this helps a bit.

Thank you.

5

Hi Aspose Team,


Thanks for the reply with details.
Sample excel where we need to update formulas using smart markers is attached. We need to update formulas in sheet1 from row 3 onward having same formulas as in row 3. Those formulas will come from smart markers / data source.
Is this possible to do using aspose cells?
6

Hi,


Thanks for the template file and details.

Well, yes, it should work fine. Once you process smart markers in the second sheet and data is filled (from data source) into the relevant cells in that worksheet, the formulas in the first worksheet should be calculated and data should pasted accordingly as per your formulas in the sheet.

Let us know if you find any issue.

Thank you.
7

Hi Amjad,


Please have attached sample application for issue we are facing while using the approach you told earlier using v17.4.0 of aspose cells.
Issue is - Excel file is generated but cells having formulas [which are updated using Smart Markers] is not showing formula result in file. It shows formula in cell value. Please see attached output file generated.

Please help us on this.
8

Hi,

Thanks for the sample project and template file.

Well, as we told you earlier we just recently supported “formula” tag/parameter in Smart Markers feature. So, please try our latest fix: Aspose.Cells for .NET v17.4.6 instead of Aspose.Cells for .NET v17.4.0:
Aspose.Cells for .NET v17.4.6 (.NET 2.0) compiled in .NET Framework 2.0.
Aspose.Cells for .NET v17.4.6 (.NET 4.0) compiled in .NET Framework 4.0.
(Note: please choose any of the above mentioned fixes for your underlying .NET framework version)

I have tested using v17.4.6 and it works fine. Moreover, you got to change the line of code in your project:
i.e.,

var a = new { Code = “A1”, Peer = “A Peer”, CurrentFirmValue = 27966.74M, CurrentEBITDA = 3886M, CurrentEPS =
1.417M, StockPrice = 21.13M, EstimatedEBITDA = 4125.82767M, EstimatedEPS = 1.64407M };

to:

var a = new { Code = “A1”, Peer = ““A Peer””,
CurrentFirmValue = 27966.74M, CurrentEBITDA = 3886M, CurrentEPS = 1.417M,
StockPrice = 21.13M, EstimatedEBITDA = 4125.82767M, EstimatedEPS = 1.64407M };

Hope, this helps a bit.

Thank you.

9

Hi,


What is the reason to change code to - Peer = ““A Peer”” ? I didn’t understood this change. Can you please let us know more on this?
10

Hi,


Well, you got to embed the string inside quotes to make it a valid MS Excel formula. For confirmation, please open a new Workbook in MS Excel (manually) and then insert the formula, i.e., “=A Peer” into formula bar, you will get an error shown as: “#NAME?”. But when you insert “=“A Peer”” into the formula bar, it calculates it fine.

Thank you.
11

Ok. Understood that. We can surround formula with quotes also. Is this right ?

12

Hi,


Well, yes, when you use a constant string in the formula you will alway put it in the qutoes. And, a formula/function itself is rather a (formula) string (that starts with “=” sign) which should be set/written using quotes in your code:
e.g
"=your_formula"

Thank you.