Dynamic Formulas?

Aspose.Cells Product Family
1

Hi all,

I have been using Aspose.Excel now for a while and love it. I use Pivot Tables a lot to take raw data from one sheet and ‘organise’ it on another (in a Pivot Table). The raw data comes from an SQL database query. Often I need to do some calculations or if statements to generate columns such as order totals, tax amounts, order types, etc so I have been using formulas in the designer file. To accommodate for the number of records that come from the sql database query, I need to copy these formulas down the columns - problem is when I have a few columns with formulas, copying them to all 65,000 rows results in about a 10MB designer file, results of which are a ‘bit’ big for the average user to download.

Question is, is there a way to dynamically create formulas on the fly with Aspose Excel based on the number of records produced by my sql query? I know there is the Cell.Formula method but I need it to automatically increase the row source values (eg the formula in cell A3 = A1+A2, so I need the formula in B3 to be B1+B2, etc.)

Alex.

2

Dear Alex,

Thanks for your consideration.

How about this solution?

I will expose two static methods:

public void Cells.CellNameToIndex(string cellName, out int row, out byte column)

public string Cells.CellIndexToName(int row, byte column)

Then you can do this:

int recordCount = …;

string cellName1, cellName2;

for(int i = 0; i < recordCount; i ++)
{
cellName1 = Cells.CellIndexToName(0, (byte)i);
cellName2 = Cells.CellIndexToName(1, (byte)i);
cells[2, (byte)i].Formula = “=” + cellName1 + “+” + cellName2;
}

3

Thanks Laurence,

I actually just did a similar thing and have a solution also:

in my ‘ReportField’ table I added a ‘Formula’ field that contains codes to represent a location form the current cell. For example the formula =|L2|+4 would be parsed and the |L2| would be converted to the named location of the cell 2 columns to the left. Likewise |R2| would be converted to 2 columns to the right, |U2| would be converted to the cell 2 rows up, and |D2| would be 2 rows down. So then I set the formula of the cell to the parsed result (eg =A1+4).

Thanks anyway for the quick reponse.

Alex.

4

Dear Alex,

I am glad that you already have a solution.

Anyway, I will expose the new methods in next release. You can have a try laterly.