Array Formulas in SpreadsheetML do not carry over when converting to Xlsx

Aspose.Cells Product Family
1

When a SpreadsheetML file contains an array formula it is not converted properly (formula is there but you would need to edit the cell and hit Ctrl-Shift Enter) when converting to a 97-2003 or a Xslx but calculates correctly when converting to PDF.

2

Hi,


Thank you for using Aspose.Cells and providing us your feedback.

Please provide us your sample source files and piece of runnable code that we can use to reproduce the issue. Also, I would recommend you to please download and try this latest fix: Aspose.Cells for .NET v7.2.2.2

3

Hi,


I tried to reproduce the issue as you have mentioned with the files as attached here, but couldn’t get success. You can see in the attached snapshot that the array formulas are calculated automatically in all Xls, Xlsx and PDF files. I have used the following lines of code for this:
//Instantiate LoadOptions specified by the LoadFormat.
LoadOptions loadOptions = new LoadOptions(LoadFormat.SpreadsheetML);
//Create a Workbook object and opening the file from its path
Workbook wb = new Workbook("K://B1.xml", loadOptions);
//Calculate formula
wb.CalculateFormula();

//Save as Xls
wb.Save("K://Book1.xls");
//Save as Xlsx
wb.Save("K://Book1.xlsx");
//Save as PDF
wb.Save("K://Book1.pdf");
4

Hi,

Thanks for the quick response. Here's the code reworked (originally loaded from a stream and saved to a HttpResponse object) and I've attached the original XML along with the PDF and Excel 2007 files after conversion as well.

Thanks again.

static void Main( string[] args )
		{
			string path = "c:\\doc\\";
			//Aspose.Cells.License license = new Aspose.Cells.License();
			//license.SetLicense("Aspose.Total.lic");
			Aspose.Cells.LoadOptions xl_load_options = new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.SpreadsheetML);
			
			//Original load
			//Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(this._stream, xl_load_options);
			Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(path + "uwtemplate.xml", xl_load_options);
			wb.CalculateFormula();
			wb.Worksheets[0].ViewType = Aspose.Cells.ViewType.PageBreakPreview;
			wb.Worksheets[0].Zoom = 90;
			Aspose.Cells.SaveOptions options = new Aspose.Cells.OoxmlSaveOptions(Aspose.Cells.SaveFormat.Xlsx);
			wb.Save(path + "uwtemplate.xlsx", options);
			options = new Aspose.Cells.XlsSaveOptions(Aspose.Cells.SaveFormat.Excel97To2003);
			wb.Save(path + "uwtemplate.xls", options);
			options = new Aspose.Cells.PdfSaveOptions(Aspose.Cells.SaveFormat.Pdf);
			wb.Save(path + "uwtemplate.pdf", options);
			
			
			//Original Save
			//wb.Save(this._context.Response, this._file_name, Aspose.Cells.ContentDisposition.Attachment, options);
		}
5

Hi,


Thank you for providing your support documents for reproducing the issue.

I was able to reproduce the issue at my end with the latest version of Aspose.Cells. I am forwarding these details to our development team for further investigation and resolution.We will try to fix the issue asap and will update you here.

The issue has been logged in our database as: CELLSNET-40799.
6

Hi,

We have fixed this issue. Please download and try the latest version: Aspose.Cells for .NET v7.2.2.3

7

The issues you have found earlier (filed as CELLSNET-40799) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.