Hi,
Our company is using the Aspose Cells smartmarker engine to generate report output. It works well and suits our purposes for the most part. Some of our reports make use of the skip and noadd parameters to spread item data across several rows. This also works well. The issue I have come across is that dynamic formulas seem to ignore the skip and noadd params. Dynamic formulas will just generate for each excel row in order.
If you look at the attached InputTemplate(For Aspose).xlsx you can see that we use several dynamic formulas and also use the (skip:3,noadd, copystyle) options. If you look at the output file (ReportOutput(For Aspose).xlsx) you can see that using the (skip:3,noadd,copystyle) params we have successfully spread our complex data across 3 rows for each item - except for the dynamic formulas. They do not skip - the dynamic formula is just copied sequentially to the fist 22 rows. You can see this well if you look at Column D. You can see that the formula is copied 22 times in order from Row 8 through Row 31 - once for each item of data in the report. (We do a double merge so some of the cells get overwritten with new data from our secondary source but you can see that the last dynamic formula is in Row 31). If the formula were honoring the skip:3 and noadd params, we should be able to see the formulas all the way to Row 118 like the rest of the data.
Can you tell me if this is a known problem? Or if it is fixed in your latest version? Or if there is a work around?
Thank you
Hi DL Fontaine,
Thanks for your posting and using Aspose.Cells.
Kindly download and try the latest version: Aspose.Cells for .NET 8.0.0 and see if it fixes your issue.
If your issue persists, then kindly provide us simpler smart marker file with your sample data and also provide us actual and expected output file. You can create both of them manually in MS-Excel and post them here for our reference. It will help us look into your issue precisely and we will be able to fix it.
Thanks for the reply but why can’t you just try it in one of your sample programs? I’m sure you have them since you are software support? Our company builds software and when a customer asks if a certain issue is a defect, support tests it on our latest version. Much easier for you than for me.
Our software is complex - it would not be possible to give you an exact sample of our code.
All you would need to do is plug a dynamic formula into one of your samples on your website that uses the skip option and see if the dynamic formulas end up on the right row. That is what I want. For the formulas to end up in the correct row - just like the data does.
I DID provide you with an actual output file. The expected output would be for the formulas to end up going all the way to the last row just like the data. I could copy and paste the formulas all the way down to the end and theat would be the expected output.
Please read the post again.
Hi DL Fontaine,
Thanks for your posting and using Aspose.Cells.
We were able to observe this issue using the latest version: Aspose.Cells for .NET 8.0.0. Smart marker dynamic formulas ignore skip, copystyle and no add parameters.
I have tested this issue with the attached input.xlsx file. DataSheet contains the test data and TestSheet contains the test smart markers. The smart markers in column A, B, C are working fine while the dynamic formula smart marker in column D is not working fine. Dynamic formulas are copied sequentially from Row 1 till 22.
I have used the following code. I have also attached the output.xlsx file for a reference.
C#
Workbook workbook = new Workbook(“input.xlsx”);
Worksheet sheet = workbook.Worksheets[“DataSheet”];
DataTable dt= sheet.Cells.ExportDataTable(0, 0, 23, 9, true);
dt.TableName = “Rows”;
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = workbook;
designer.SetDataSource(dt);
designer.Process();
workbook.Save(“output.xlsx”);
Hi,
Thanks for using Aspose.Cells.
If you want apply setting to dynamic formulas ,please split the settings with
‘~’, such as
&=&=SUBSTITUTE(O{r},";
",CHAR(10))~(skip:3,noadd,copystyle)
Otherwise we could not know whether
(skip:3,noadd,copystyle) is a part of formula or not.
Great! that worked like a charm! thank you very much!
Hi,