SetArrayFormula - Not being treated as array formula

Aspose.Cells Product Family
1

Hi - using SetArrayFormula with a more complex array formula causes Excel to not treat the formula as an array formula. Upon opening the output file Excel reports an error. However, if we open the output file and click in the formula bar Excel automatically fixes the issue (or hit Ctrl+Enter). Code sample below ( using Aspose.Cells 17.9.0.0). Are we doing something wrong? Library issue? Suggestions? Thanks for your time.

Excel Error Image:
image.png (10.1 KB)

Maybe related to? (Is a private issue I cannot see)

Sample Code (subset):

using( var workbook = new Workbook() )
{
	var sheet = workbook.Worksheets[ 0 ];
	sheet.Cells[ 0, 1 ].PutValue( "Complex Formula Array" );
	sheet.Cells[ 1, 1 ].PutValue( 20 );
	sheet.Cells[ 2, 1 ].PutValue( 30 );
	sheet.Cells[ 3, 1 ].PutValue( 40 );
	sheet.Cells[ 4, 1 ].PutValue( 50 );
	sheet.Cells[ 5, 1 ].PutValue( 60 );
	sheet.Cells[ 6, 1 ].PutValue( 70 );
	sheet.Cells[ 7, 1 ].PutValue( 80 );
	sheet.Cells[ 8, 1 ].PutValue( 90 );
	sheet.Cells[ 9, 1 ].Formula = "=SUM(A1:A9)";
	sheet.Cells[ 10, 1 ].SetArrayFormula( "=SUM( IF( NOT( ISFORMULA( A2: A10 ) ), A2: A10 ) , 0)", 1, 1 );

	workbook.CalculateFormula();

	var outputPath = @"C:\Logs\asposetest.xlsx";
	workbook.Save( outputPath, SaveFormat.Xlsx );
}
2

@kmacdonald

Thanks for using Aspose APIs.

We have tested this issue with the following sample code. Please check the output Excel file generated by it. When we open it in Microsoft Excel, we found error in cell B11 and when we click the formula bar, Microsoft Excel fixes it.

Output Excel File.zip (5.7 KB)

However, the array formula gets converted to regular or normal formula. It means, Microsoft Excel does not fix the issue but converts the formula to regular formula.

If you still find the issue, then please provide us your Excel file which you create manually using Microsoft Excel and submit it to us, we will then look into this issue further and help you.

C#

Workbook workbook = new Workbook();

var sheet = workbook.Worksheets[0];
sheet.Cells[0, 1].PutValue("Complex Formula Array");
sheet.Cells[1, 1].PutValue(20);
sheet.Cells[2, 1].PutValue(30);
sheet.Cells[3, 1].PutValue(40);
sheet.Cells[4, 1].PutValue(50);
sheet.Cells[5, 1].PutValue(60);
sheet.Cells[6, 1].PutValue(70);
sheet.Cells[7, 1].PutValue(80);
sheet.Cells[8, 1].PutValue(90);
sheet.Cells[9, 1].Formula = "=SUM(A1:A9)";
sheet.Cells[10, 1].SetArrayFormula("=SUM( IF( NOT( ISFORMULA( A2: A10 ) ), A2: A10 ) , 0)", 1, 1);

workbook.CalculateFormula();

workbook.Save("out.xlsx", SaveFormat.Xlsx);
3

Hello - sorry for not giving the complete example. Below is the updated sample method. I will also upload a sample output workbook. Thank you for your help.

image.png (11.5 KB)
asposetest.zip (5.9 KB)

static void Main( string[] args )
		{
			License cellsLic = new License();
			//using Aspose.Cells v 17.9.0.0
			cellsLic.SetLicense( "Aspose.Total.lic" );

			using( var workbook = new Workbook() )
			{
				var sheet = workbook.Worksheets[ 0 ];

				//inject column A
				sheet.Cells[ 0, 0 ].PutValue( "Simple Formula Array" );
				sheet.Cells[ 1, 0 ].PutValue( 20 );
				sheet.Cells[ 2, 0 ].PutValue( 30 );
				sheet.Cells[ 3, 0 ].PutValue( 40 );
				sheet.Cells[ 4, 0 ].PutValue( 50 );
				sheet.Cells[ 5, 0 ].PutValue( 60 );
				sheet.Cells[ 6, 0 ].PutValue( 70 );
				sheet.Cells[ 7, 0 ].PutValue( 80 );
				sheet.Cells[ 8, 0 ].PutValue( 90 );
				sheet.Cells[ 9, 0 ].Formula = "=SUM(A2:A9)";
				//Simple array formulas seem to work OK
				sheet.Cells[ 10, 0 ].SetArrayFormula( "=SUM( A2: A9 ) )", 1, 1 );
				sheet.Cells[ 11, 0 ].PutValue("Notice cell A11, an array formula, evaluates without issue.");

				//inject column B
				sheet.Cells[ 0, 1 ].PutValue( "Complex Formula Array" );
				sheet.Cells[ 1, 1 ].PutValue( 20 );
				sheet.Cells[ 2, 1 ].PutValue( 30 );
				sheet.Cells[ 3, 1 ].PutValue( 40 );
				sheet.Cells[ 4, 1 ].PutValue( 50 );
				sheet.Cells[ 5, 1 ].PutValue( 60 );
				sheet.Cells[ 6, 1 ].PutValue( 70 );
				sheet.Cells[ 7, 1 ].PutValue( 80 );
				sheet.Cells[ 8, 1 ].PutValue( 90 );
				sheet.Cells[ 9, 1 ].Formula = "=SUM(A2:A9)";
				//Complex array formulas DO NOT seem to work.
				sheet.Cells[ 10, 1 ].SetArrayFormula( "=SUM( IF( NOT( ISFORMULA( A2: A10 ) ), A2: A10 ) , 0)", 1, 1 );
				sheet.Cells[ 11, 1].PutValue( "Notice cell B11, an array formula, requires me to hit Ctrl+Shift+Enter to force it to be treated as an array formula." );
				workbook.CalculateFormula();

				var outputPath = @"C:\Logs\asposetest.xlsx";
				workbook.Save( outputPath, SaveFormat.Xlsx );
			}
		}
4

@kmacdonald

Thanks for your clarification and using Aspose APIs.

We were able to observe this issue and logged it in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-45797 - SetArrayFormula - Not being treated as array formula
1 Like
5

@kmacdonald,

This is to inform you that we have fixed your issue CELLSNET-45797 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

1 Like
6

@kmacdonald

Thanks for using Aspose APIs.

Please download and try the following latest fix and let us know your feedback.

7

This seems to work in my test app. I will update my application and follow up if I see any further issues. Thank you!

8

@kmacdonald

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue seems resolved with the latest fix. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

9

The issues you have found earlier (filed as CELLSNET-45797) have been fixed in this Aspose.Cells for .NET 17.12 update.

Please also check the following article: