SetAddInFormula() problems

emookie · July 19, 2007, 9:08am

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

Laurence · July 19, 2007, 9:28am

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.

emookie · July 19, 2007, 9:56am

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");

Laurence · July 19, 2007, 10:26am

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

emookie · July 19, 2007, 10:34am

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.

Laurence · July 23, 2007, 2:20am

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");

emookie · July 23, 2007, 8:19am

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)

Laurence · July 23, 2007, 9:07am

Have you tried the new version?

emookie · July 23, 2007, 9:46am

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?

Laurence · July 23, 2007, 10:01am

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

Laurence · July 24, 2007, 2:27am

Please try this attached version.

emookie · July 25, 2007, 8:40am

It works!

Thanks, Laurence

peterglennane · November 8, 2007, 4:28am

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

Warren · November 9, 2007, 2:09am

Hi Peter,

Please try this fix.

peterglennane · November 9, 2007, 5:09am

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

Warren · November 9, 2007, 6:02am

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");

peterglennane · November 9, 2007, 10:52am

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

peterglennane · November 9, 2007, 11:23am

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);
}

amjad.sahi · November 9, 2007, 11:57am

Hi,

Thanks for the details,

We will check and figure it out.

Thank you.

Warren · November 11, 2007, 5:09am

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?