Formula with Ampersand (&)

Aspose.Cells Product Family
1

Hi,

I am trying to use a formula such as "Online Store Purchase Invoice No " & A2

where A2 is a CELL reference,
which works in Excel but generates an error when using Aspose Excel.

Could you check this out for me?? I am using 1.8.3.0

Thanks,
Alex.

2

Hi Alex,

I have fixed this problem but the fix is not released yet. It will be available before the start of next week.

If you are in urgent need of it, please let me know.

3

Can wait, thanks Laurence.

4

Hi Alex,

Please download hotfix 1.8.7 and have a try.

5

Thanks Laurence - seems to work fine except that text is converted to uppercase???

eg:
="Online Order No " & A1

converts to the following in Excel using Aspose.Excel (as an example):
ONLINE ORDER NO 005

Can you check that out for me?
Thanks,
Alex.

6

Sorry, a small bug.

Please download hotfix 1.8.7.1 and have a try.

7

@alexidm.au,
Aspose.Excel is discontinued now and a new product Aspose.Cells is introduced. This new product contains a complete set of features supported by the latest versions of MS Excel. Formulas are supported by Aspose.Cells as demonstrated below:

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();

// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[0];

// Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);

// Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);

// Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);

// Adding a SUM formula to "A4" cell
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";

// Calculating the results of formulas
workbook.CalculateFormula();

// Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();

// Saving the Excel file
workbook.Save(dataDir + "output.xls");

Here is a detailed article for formulas:
Using Formulas or Functions to Process Data

The latest version of this new product can be downloaded here:
Aspose.Cells for .NET (Latest Version)

A ready to run solution is available here.