Free Support Forum - aspose.com

SetAddInFormula() problems

Here are two, I believe, unrelated issues:

1) when trying to dynamically create a spreadsheet that contains an addin formula from the Bloomberg financial system ("=blp()" from "blp.xla") I can't get the formula to register properly (i.e., it always comes up #NAME?). other functions like the analysis toolpack functions work fine. The only difference I can see is that Bloomber doesn't install their xla in the library directory, but rather at c:\blp\api\dde\blp.xla. I'm not sure how they get that to load in Excel at startup, but they do (they used to modify a startup template, but I don't think that's true anymore).

2) when I do two consecutive calls to SetAddInFormula, some weird replacing is going on. For example:

cell1.SetAddInFormula("=blp(a1,b1)","blp.xla");

cell2.SetAddInFormula("=CUMIPMT(0.04,360,10000,3,120,0)","analysis.xla");

...yields a spreadsheet with cells as follows:

cell1: "=CUMIPMT(a1,b1)" value: "#VALUE!"

cell2: "=CUMIPMT(0.04,360,10000,3,120,0)" value: "-471992.26"

Very weird stuff. Any ideas?

Eric

Hi Eric,

For 1, what happens if you copy the blp.xla to library folder? And please use the following method to check this library: Tools-> Addins->browse and check functions in blp.xla.

For 2, which version of Aspose.Cells are you using? Could you please zip and post your blp.xla and analysis.xla here? I will check it.

1) Didn't help. :-(

2) don't need a zip as reproducible code is VERY short: (version of dll: v2.0.50727)

License license = new License();

license.SetLicense("Aspose.Cells.lic");

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

Cell cell = sheet.Cells["A1"];

cell.SetAddInFormula("blp.xla","=blp(a2,a3)");

cell = sheet.Cells["B1"];

cell.SetAddInFormula("analysis.xla", "=CUMIPMT(0.04,360,100000,3,120,0)");

workbook.Save("C:\\test.xls");

I need your xla files to do a test. Please zip and post them here. Thank you.

I can’t post blp.xla (license violation, the files are huge, you’d have to have all of Bloomberg installed for it to function and that’s not working anyway). I was thinking you needed the code to see about the weird formula replacement. That’s what the code I gave demonstrates. As far as “analysis.xla” is concerned, that’s the Analysis Toolpack that comes standard with Excel.

Please try this attached fix with following code:

License license = new License();

license.SetLicense("Aspose.Cells.lic");

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

Cell cell = sheet.Cells["A1"];

cell.SetAddInFormula(@"c:\blp\api\dde\blp.xla","=blp(a2,a3)"); // specify full path for custom addin

cell = sheet.Cells["B1"];

cell.SetAddInFormula("", "=CUMIPMT(0.04,360,100000,3,120,0)"); // don't provide path for Excel built-in addin

workbook.Save("C:\\test.xls");

With the new code I still end up with a sheet that has “=CUMIPMT(a2,a3)” in cell A1. (i.e., BLP is being replaced with CUMIPMT)

Have you tried the new version?

Whoops! I read the email digest and didn’t notice you had attached code. The new DLL makes the test case work. Great news! However, the new DLL kills our production code at every call of Worksheet.AutoFitColumn(int) or Worksheet.AutoFitColumns() throwing an InvalidCastException.
Any ideas?

We are making some feature enhancement which introduce this bug. I will fix this problem tomorrow. Thanks for your patience.

Please try this attached version.

It works!

Thanks, Laurence

Hi Laurence,

I am also having problems with the formula name being overwritten. In my case I am trying to use two different formula from the same xla addin file. No matter what i try, the name of the second formula is always overritten with the name of the first formula.

Peter

Hi Peter,

Please try this fix.

Hi Warren,

I tried that dll and now my second fomula name is replaced with the string

((#REF!))

I also have multiple cells in the same row using the same fomula. The first cell of the first formula's row contains the correct formula name but the subsequent cells on the same row contain the string

!ValidationRange

where is the name of the xls file I have saved the workbook down to.

Peter

Hi Peter,

I tested with the following codes , it works fine. Please see the attached file.

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

Aspose.Cells.Cell cell = sheet.Cells["A1"];

cell.SetAddInFormula("blp.xla", "=blp(a2,a3)");

cell = sheet.Cells["B1"];

cell.SetAddInFormula("blp.xla", "=CUMIPMT(0.04,360,100000,3,120,0)");

workbook.Save(@"F:\FileTemp\dest.xls");

Hi Warren

I think the problem I was having was related to the fact I have a second worksheet in my workbook. If you add the following line to your code before the first call to SetAddInFormula, the xls file produced is corrupt

Worksheet sheet2 = workbook.Worksheets[workbook.Worksheets.Add()];

If you create the second sheet after setting the formulas it seems to work as expected.

Peter

Add the following method and if it gets called first before the calls to SetAddInFormula it produces the output I have reported earlier.

private static void CreateDropDownValidation(Workbook workbook)
{
string[] dropDownValues = new string[] {"One", "Two", "Three"};

Worksheet sheet = workbook.Worksheets[0];
Worksheet validationWorksheet = workbook.Worksheets[workbook.Worksheets.Add()];

validationWorksheet.Cells.ImportArray(dropDownValues, 0, 0, false);
Range range = validationWorksheet.Cells.CreateRange(0, dropDownValues.Length, false);
range.Name = "ValidationRange";

Validation validation = sheet.Validations[sheet.Validations.Add()];
validation.InCellDropDown = true;
validation.Type = ValidationType.List;
validation.Operator = OperatorType.Between;
validation.Formula1 = "=ValidationRange";

sheet.Cells.ImportArray(dropDownValues, 1, 6, true);
CellArea cellArea = new CellArea();
cellArea.StartRow = 1;
cellArea.StartColumn = 6;
cellArea.EndRow = 3;
cellArea.EndColumn = 6;
validation.AreaList.Add(cellArea);
}

Hi,

Thanks for the details,

We will check and figure it out.

Thank you.

Hi,

I tested with the following codes, it works fine.

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

Aspose.Cells.Cell cell = sheet.Cells["A1"];

cell.SetAddInFormula("blp.xla", "=blp(a2,a3)");

cell = sheet.Cells["B1"];

cell.SetAddInFormula("blp.xla", "=CUMIPMT(0.04,360,100000,3,120,0)");

string[] dropDownValues = new string[] { "One", "Two", "Three" };

// Worksheet sheet = workbook.Worksheets[0];
Worksheet validationWorksheet = workbook.Worksheets[workbook.Worksheets.Add()];

validationWorksheet.Cells.ImportArray(dropDownValues, 0, 0, false);
Range range = validationWorksheet.Cells.CreateRange(0, dropDownValues.Length, false);
range.Name = "ValidationRange";

Validation validation = sheet.Validations[sheet.Validations.Add()];
validation.InCellDropDown = true;
validation.Type = ValidationType.List;
validation.Operator = OperatorType.Between;
validation.Formula1 = "=ValidationRange";

sheet.Cells.ImportArray(dropDownValues, 1, 6, true);
CellArea cellArea = new CellArea();
cellArea.StartRow = 1;
cellArea.StartColumn = 6;
cellArea.EndRow = 3;
cellArea.EndColumn = 6;
validation.AreaList.Add(cellArea);
workbook.Save(@"F:\FileTemp\dest.xls");

Do you use the template file?