Excel formula getting messed up

Aspose.Cells Product Family
1

Hi,


I am working on a requirement where I am using formulas to refer to another worksheet in the excel.
The way I am generating the excel file is:
1) Generate excel files for each of the worksheets using xslt
2) Merge these excel files into the final excel file.

Hence in step1 when the excel file having the formula is generated through xslt, only the formula is generated although the worksheet referring to is not yet available in the workbook.

After merging I am using Aspose calculateformula function to evaluate the formulae in the excel sheets.

But in case if I am opening the file through Aspose after Step1 to do some modification to the file, the formulas in the file is getting messed up and the formulas are not evaluated correctly after merging of the file and CalculateFormula at Step2.

Attached File1 is the xslt transformed file.
Here is the code I am using to format the excel:

public override void FormatReport(string reportFilePath, string reportName)
{
Workbook excelReportWorkbook = new Workbook(reportFilePath);
Worksheet workSheet = excelReportWorkbook.Worksheets[0];
workSheet.AutoFitColumns(5, workSheet.Cells.MaxColumn);
workSheet.Name = reportName;
excelReportWorkbook.Save(reportFilePath, options);
}

Attached File2 is the file after applying the above the code and the formula getting messed up.

What modification can I make to the above function so that the excel formulas do not get modified while saving the workbook?


2

Hi,

Thanks for the sample files.

I tested your scenario/ case using the following sample code with our latest version/fix: Aspose.Cells for .NET (Latest Version) (please try it) and it works fine. The output file’s formulas are pretty much the same as per the original Excel file.

e.g

Sample code:

Workbook excelReportWorkbook = new Workbook(“e:\test2\File1.xls”);
Worksheet workSheet = excelReportWorkbook.Worksheets[0];
workSheet.AutoFitColumns(5, workSheet.Cells.MaxColumn);
workSheet.Name = “Sheet_1”;
excelReportWorkbook.Save(“e:\test2\outFile2.xls”, SaveFormat.Excel97To2003);

I have also attached the output file for your reference.

If you still find the issue, kindly post your complete sample code (runnable) to reproduce the issue on our end. We will check it soon.

Thank you.