Using Dyanmic formula for fill down and fill right

Aspose.Cells Product Family
1

I would like to use Aspose dynamic formula to populate my excel sheets.

I have N X M rows for which i want to populate using my UDF formula.

i am using &=&=MyUDFFunc(0{-1},1{0})~(horizontal, shift) which is able to write the cells horizontally.

I want same dynamic formula to fill Down also so that it will populate the entire table. Is there any way we can do the same using Aspose Smartmarkers ?

I got some feedback from Amjad Sahi on stackoverflow, but didn’t get much input there.

2

@oggymishra,
Could you please share your template file, expected output file created by MS Excel, runnable console application and program output generated by Aspose.Cells. We will analyze this information and share our feedback.

3

@ahsaniqbalsidiqui Thanks for replying.

I am attaching the sample console program, sample template file, output.xlsx file generated by the code and the expectedOutput.xlsx.

Please take a look and respond back.OggyMishra_Sample_Program.zip (34.2 KB)

4

@oggymishra,
We are seeing the following view for the expected output file. Could you please confirm if is this the required output by the program?
ExpectedOutput.jpg (1.2 MB)

5

@ahsaniqbalsidiqui The #Name that you see in the excel sheet is because of unrecognized formulas, basically these are the formulas that we have in my excel addin. What we wanted to do is to span across these formula to rows and then columns, to make a table like structure.

6

@oggymishra,
We have logged the issue as “CELLSNET-47788” in our database for investigations. Once we will have some news for you, we will update you in this topic.

Moreover, if you are able to achieve the same with ms excel using fill handle or some other approach then share the details with us as it can help our investigations.

7

@ahsaniqbalsidiqui In excel inter-op we have
// copy down
range.FillDown();
//copy right
range.FillRight();

which does the same job of stretching the formula to down and right and changed the arguments accordingly.

8

@oggymishra,
Thank you for sharing more information. We have logged it with the ticket and consider it while analyzing this issue.

9

@oggymishra,

Aspose.Cells cannot process smarter makers horizontally and vertically together. Please generate repeated formula smart makers with your datasource, try the following codes with the template file:
e.g.
Sample code:

string companyInfo = "{\"TableName\":\"CompanyInfo\", \"Table\": [{\"Ticker\":\"MCD\",\"Depth\":\"IMPL\",\"Revision\":\"11/12/2020\",\"Tab\":\"Cash Flow\",\"View\":\"CD\",\"SheetName\":\"Cash Flow-CF\"}]}";
string parameterInfo = "{\"TableName\":\"Parameter\", \"Table\": [{\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}, {\"Id\":\"N_5774220\",\"Name\":\"Net income (CF)\", \"Unit\":\"$M\"}]}";

string periodInfo = "{\"TableName\":\"Period\", \"Table\": [{\"Value\":\"1Q-19\",\"Formula\":\"&=&=GetData($C$3, {0}${-3}, $C{0}, {0}${-1}, $C$5, $C$4, $C$7)\" }, {\"Value\":\"2Q-19\",\"Formula\":\"&=&=GetData($C$3, {0}${-3}, $C{0}, {0}${-1}, $C$5, $C$4, $C$7)\"}]}";
var companyInfoTable = JsonConvert.DeserializeObject<DataTableMapping>(companyInfo);
// TODO: make a consistent deserliazer to set this name directly.
companyInfoTable.Table.TableName = companyInfoTable.TableName;
var parameterTable = JsonConvert.DeserializeObject<DataTableMapping>(parameterInfo);
parameterTable.Table.TableName = parameterTable.TableName;
var periodTable = JsonConvert.DeserializeObject<DataTableMapping>(periodInfo);
periodTable.Table.TableName = periodTable.TableName;
Aspose.Cells.Cells cells = wb.Worksheets[0].Cells;
report.SetDataSource(companyInfoTable.Table);
report.SetDataSource(periodTable.Table);
report.Process(true);
report.SetDataSource(parameterTable.Table);
report.Process();