ImportFormulaArray?

Aspose.Cells Product Family
1

Is it (or would it be) possible to import formulas from an array? I was trying to get around the InsertRow problem (formulas not copied automatically) by creating a “Dynamic Formula” feature into my data marker system (a predefined string gets replaced with the current row number) and hoped I could just dump the dynamically created formulas into the sheet.

Thanks,

–Tomi B.

2

Well, I got around this by replacing two lines of code with two other lines of code :slight_smile: Since I was creating the array in a loop I just changed it a bit to put the values straight into the sheet.

3

This is a feature that I am interested in as well. I currently have an XLS file which I use as a template and I import my values. I would like to be able to import the formula column as well so that when the user opens the file and changes a number then the formula column will automatically update.

Is there a way to do this without creating a Loop and using the CellsIdea.Formula property?

4

Thanks for your suggestion.

This method will be added in the future release. It will be available in next week.

5

@arhoads76,
Aspose.Cells has replaced Aspose.Excel that is no more actively developed now. You can perform all the advanced operations using Aspose.Cells those are supported by different versions of MS Excel. Using this new product you can import formula array using Cells.ImportFormulaArray(). Here is an example that demonstrates the importing of formula error.

Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];

//Get the cells collection in the sheet
Cells cells = sheet.Cells;
           
for (int i = 0; i < 10; i++)
    sheet.Cells[i, 0].Value = i * 2;

for (int i = 0; i < 10; i++)
    sheet.Cells[i, 1].Value = i * 2;
//D8 to I8
string[] strArray = new string[] {"=A1+B1", "=A2+B2", "=A3+B3", "=A4+B4", "=A5+B5", "=A6+B6", "=A7+B7", "=A8+B8", "=A9+B9", "=A10+B10"};
Range range = cells.CreateRange("C1", "C10");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, true);

workbook.Save("ImportFormulaArray.xlsx");

Import Formula Array

For more information on importing data into worksheet, follow the below link:
Import Data into Worksheet

You can freely download the Aspose.Cells for .NET(Latest version) to run this application.

A runnable solution can be downloaded here for detailed testing of this new product.