Range of a function

Aspose.Cells Product Family
1

We need to do a SUM in excel but we dont know the range of our data.

For example, if i have a excel with a column where i have data in B1,B2 and B3 I will write: =SUBTOTAL(9,B1:B3) but with Aspose.Cells I have in B1 &=Datos.Valor but i dont know how many rows i will have. How can i write the function? &=&=SUBTOTAL(9,?:?)

I attached my excel with aspose and my output excel.

My problem is the range when i write the function if i dont know the number of my rows.

2

Hi,


Please check:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/smart-markers.html

I think for your need, you may use Smart Markers’s Grouping Data feature. See the contents + exmple under “Grouping Data” sub-topic there. You should especially check the example of smart markers for the second sheet there. The screen shots of the template and output sheets are also there, so you can input your smart markers (in the template file) accordingly for your case.

Thank you.

3

About the information in this web http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/smart-markers.html to get sum result I have to write; &=subtotal9:Order Details.OrderID

In my excel, I write &=subtotal9:Datos.CD_Importe_Total_Subvencion
Why dont run, in my output excel, the function? It gets like a text cell (You can see it in my attached excel, the yellow cell).

Is it probably that it isnt work fine as a result of the cell format? I tried with cell general and cell numeric too.

Can you update my excel with the correct function?

Thanks, IASOFT.

4

Hi,


Well, for “&=subtotal9:Order Details.OrderID”, you may see that this marker is there in UnitPrice column under smart marker i.e. “&=[Order Details].UnitPrice”. The marker “&=subtotal9:Order Details.OrderID” refers that UnitPrice values would be summed group by OrderID column as you may see the example in the topic.

I think you should amend your smart markers accordingly as the marker i.e. “&=subtotal9:Datos.CD_Importe_Total_Subvencion” refers that you are doing group by that column. But this is summary column rather than group by column.

If you still could not evaluate, kindly simplified your example (it will be good for us) and create a C#/VB console application, zip it and post it here with all the files. The project should be runnable, so that we can check it soon.

Thank you.
5

Hi,

I understand your reply but we dont want to do group. Is it necessary?

Can I do a sum of a field without do a group? I only want to know this, and if it is possible, How?

Thanks,

IASOFT.

6

Hi,


I think you may try some grouping option accordingly:
e.g
&=TableName.FieldName(group:repeat) while specifying the group by column, there are some other options too. e.g
normal,merge etc.

I am not sure if you don’t specify the group by column, it will work precisely and accurately, but, you may try.

Thank you.
7

Hi, thanks
I have a group as in the example.
In my code, The first worksheet doesn't work correctly, but the second is ok because It shows the sum for every column.
Please, what is my error in the first worksheet?
&=Datos.CD_Presupuesto_Protegido(subtotal9:Datos.Nombre_Area).
I would like use the first option.
I attached the files (groups.xls as template y output.xls as output of my program)

Thanks,
IASOFT

8

Hi,


For your inserted marker like “&=Datos.CD_Presupuesto_Protegido(subtotal9:Datos.Nombre_Area)”:
According to Smart Markers standard, I am afraid, you cannot use grouping options in this format. Your second worksheet has correct smart markers that follow the Smart Markers standards.

Thanks for your understanding.

9

Hi,
In the page http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/smart-markers.html, the format "&=Datos.CD_Presupuesto_Protegido(subtotal9:Datos.Nombre_Area)": is the same as in the first example.

I copy the text: "In the first worksheet, we put some smart markers with grouping parameters as shown in the figure below. We place three smart markers (having grouping parameters) i.e. &=[Order Details].OrderID(group:merge,skip:1), &=[Order Details].Quantity(subtotal9:Order Details.OrderID), &=[Order Details].UnitPrice(subtotal9:Order Details.OrderID) into A5, B5 and C5 cells respectively."

¿what is my error?

Thanks,
IASOFT

10

Hi,


We recommend you to try with our latest version v7.0.1:
http://www.aspose.com/community/files/51/.net-components/aspose.cells-for-.net/entry328122.aspx
If you still find the issue, kindly simplify your example. We request you to create a sample console demo application, zip it and post it here (with all the files - input and output files) to show the issue. You may use MS Access database table(s) or alternatively create DataTable dynamically to set the source for the smart markers you insert into the template files. It will help us to analyze your issue soon.

Thank you.