Hi.
We’re currently using Aspose.Cells version 23.10.0 accodring to NuGet.
I’m writting an API that needs to generate a speadsheet that contains some formulas. My code looks as below. However, whenever I open the spreadsheet, the formulas display as text rather than to be executed (See attached image).
NOTES:
- The “workbook.Save” bit was included to make sure that the issue is not something that was introduced on client side.
- I did confirm, stepping through the code, that the “cells.Formula” code was hit at the appropriate places.
- The bug only happens on formulas generated by ASPOSE. Formulas added manually in the file while in Excel functions as expected.
- I did check the Auto Calculate setting in Excel
- I’ve added the all the ASPOSE code I could find to force it to calculate, with no effect.
What am I missing?
public async Task<byte[]> DownloadDeviationReport([FromBody] DeviationReportInput input)
{
List<Task> asyncCalls = new List<Task>();
DeviationReportDTO deviationData = null;
List<InvestmentProjectionItem> projection = null;
List<RiskCategoryAllocationAdjustment> riskCategoryAllocationAdjustment = null;
asyncCalls.Add(Task.Run(async () => {deviationData = (await GetDeviationReportData(input));}));
asyncCalls.Add(Task.Run(async () => {riskCategoryAllocationAdjustment = (await GetRiskCategoryAllocationAdjustment(input.AccountID, input.SelectedEntities,null, input.AnalysisID, input.ReportDate, input.NonAccount)).ToList();}));
await Task.WhenAll(asyncCalls.ToArray());
var workbook = new Workbook();
Aspose.Cells.Style cellStyleAccounting = CreateStyle(workbook, false, false, TextAlignmentType.Right, TextAlignmentType.Top, 9, FontUnderlineType.None, "###,###,###,###,###,##0", null);
Aspose.Cells.Style cellStyleAccountingTotal = CreateStyle(workbook, false, true, TextAlignmentType.Right, TextAlignmentType.Top, 9, FontUnderlineType.None, "###,###,###,###,###,##0", null);
Aspose.Cells.Style cellStylePercentage = CreateStyle(workbook, false, false, TextAlignmentType.Right, TextAlignmentType.Top, 9, FontUnderlineType.None, "##0.00 %", null);
Aspose.Cells.Style cellStyleMainHeading = CreateStyle(workbook, false, true, TextAlignmentType.Center, TextAlignmentType.Top, 14, FontUnderlineType.None, "", null);
Aspose.Cells.Style cellStyleClientHeading = CreateStyle(workbook, false, true, TextAlignmentType.Left, TextAlignmentType.Top, 11, FontUnderlineType.None, "", null);
Aspose.Cells.Style cellStyleHeading = CreateStyle(workbook, true, true, TextAlignmentType.Left, TextAlignmentType.Top, 9, FontUnderlineType.None, "", Color.LightGray);
Aspose.Cells.Style cellStyleText = CreateStyle(workbook, false, false, TextAlignmentType.Left, TextAlignmentType.Top, 9, FontUnderlineType.None, "", null);
Aspose.Cells.Style cellStyleColumnHeading = CreateStyle(workbook, true, true, TextAlignmentType.Left, TextAlignmentType.Top, 9, FontUnderlineType.None, "", Color.LightGray, true);
Aspose.Cells.Style cellStyleDate = CreateStyle(workbook, false, false, TextAlignmentType.Left, TextAlignmentType.Top, 9, FontUnderlineType.None, "yyyy-mm-dd", null);
int row = 0;
int totalRow= 0;
Worksheet sheet = workbook.Worksheets.Add("Deviation Report");
workbook.Worksheets.RemoveAt(0);
CellSetValue(ref sheet, row, 0, cellStyleMainHeading, "Deviation Report");
row++;
//data_risk_categories -> FinancialPlanning/GetRiskCategoryAllocationAdjustment
CellSetValue(ref sheet, row, 0, cellStyleHeading, "Category");
CellSetValue(ref sheet, row, 1, cellStyleHeading, "Term (Yrs) Houseview");
CellSetValue(ref sheet, row, 2, cellStyleHeading, "Term (Yrs) Applied");
CellSetValue(ref sheet, row, 3, cellStyleHeading, "Adjusted Amount");
decimal appliedAmountTotal = 0;
for (int i = 0; i < riskCategoryAllocationAdjustment.Count; i++)
{
row++;
CellSetValue(ref sheet, row, 0, cellStyleText, riskCategoryAllocationAdjustment[i].RiskCategory);
CellSetValue(ref sheet, row, 1, cellStyleText, riskCategoryAllocationAdjustment[i].Term == -1 ? 100 : riskCategoryAllocationAdjustment[i].Term);
CellSetValue(ref sheet, row, 2, cellStyleText, riskCategoryAllocationAdjustment[i].AdjustmentTerm);
CellSetValue(ref sheet, row, 3, cellStyleAccounting, riskCategoryAllocationAdjustment[i].AdjustmentAmount);
appliedAmountTotal = appliedAmountTotal + (decimal)(riskCategoryAllocationAdjustment[i].AdjustmentAmount != null ? riskCategoryAllocationAdjustment[i].AdjustmentAmount : 0);
}
row++;
CellSetValue(ref sheet, row, 3, cellStyleAccountingTotal, appliedAmountTotal);
totalRow = row;
row++;
row++;
CellSetValue(ref sheet, row, 1, cellStyleHeading, "Asset Category/Fund");
CellSetValue(ref sheet, row, 2, cellStyleHeading, "Current");
CellSetValue(ref sheet, row, 3, cellStyleHeading, "Target");
CellSetValue(ref sheet, row, 4, cellStyleHeading, "Deviation Amount");
CellSetValue(ref sheet, row, 5, cellStyleHeading, "Deviation %");
CellSetValue(ref sheet, row, 6, cellStyleHeading, "New Allocation");
CellSetValue(ref sheet, row, 7, cellStyleHeading, "New Deviation Amount");
CellSetValue(ref sheet, row, 8, cellStyleHeading, "New Deviation %");
List<DeviationReportAsset> deviations = deviationData.DeviationReportAssets;
for (int i = 0; i < deviations.Count; i++)
{
row++;
CellSetValue(ref sheet, row, 0, cellStyleText, deviations[i].InHouseview == false ? "Out of houseview" : Math.Abs(deviations[i].DeviationPc) >= 10 ? "10% Deviation" : "");
CellSetValue(ref sheet, row, 1, cellStyleText, deviations[i].AssetCategory);
CellSetValue(ref sheet, row, 2, cellStyleAccounting, deviations[i].CurrentAmount);
CellSetValue(ref sheet, row, 3, cellStyleAccounting, deviations[i].TargetAmount);
CellSetValue(ref sheet, row, 4, cellStyleAccounting, deviations[i].DeviationAmount);
CellSetValue(ref sheet, row, 5, cellStylePercentage, deviations[i].DeviationPc / 100);
CellSetValue(ref sheet, row, 6, cellStyleAccounting, (decimal)0);
CellSetValue(ref sheet, row, 7, cellStyleAccounting, "=G" + (row + 1) + "-D" + (row + 1));
CellSetValue(ref sheet, row, 8, cellStylePercentage, "=H" + (row + 1) + "/D" + (totalRow +1));
/*
CellSetValue(ref sheet, row, 0, cellStyleText, deviationData[i].RiskCategory);
CellSetValue(ref sheet, row, 1, cellStyleText, riskCategoryAllocationAdjustment[i].Term == -1 ? 100 : riskCategoryAllocationAdjustment[i].Term);
CellSetValue(ref sheet, row, 2, cellStyleText, riskCategoryAllocationAdjustment[i].AdjustmentTerm);
CellSetValue(ref sheet, row, 3, cellStyleAccounting, riskCategoryAllocationAdjustment[i].AdjustmentAmount);
appliedAmountTotal = appliedAmountTotal + (decimal)(riskCategoryAllocationAdjustment[i].AdjustmentAmount != null ? riskCategoryAllocationAdjustment[i].AdjustmentAmount : 0);
*/
}
sheet.PageSetup.Orientation = PageOrientationType.Landscape;
sheet.PageSetup.FitToPagesWide = 1;
sheet.PageSetup.FitToPagesTall = 0;
sheet.AutoFitRows();
sheet.AutoFitColumns();
CellSetValue(ref sheet, row + 0, 20, cellStyleAccounting, "cellStyleAccounting");
CellSetValue(ref sheet, row + 1, 20, cellStyleAccountingTotal, "cellStyleAccountingTotal");
CellSetValue(ref sheet, row + 2, 20, cellStyleMainHeading, "cellStyleMainHeading");
CellSetValue(ref sheet, row + 3, 20, cellStyleClientHeading, "cellStyleClientHeading");
CellSetValue(ref sheet, row + 4, 20, cellStyleHeading, "cellStyleHeading");
CellSetValue(ref sheet, row + 5, 20, cellStyleText, "cellStyleText");
CellSetValue(ref sheet, row + 6, 20, cellStyleColumnHeading, "cellStyleColumnHeading");
CellSetValue(ref sheet, row + 7, 20, cellStyleDate, "cellStyleDate");
CalculationOptions options = new CalculationOptions();
workbook.Settings.FormulaSettings.CalculationId = "";
workbook.Settings.FormulaSettings.CalculateOnOpen = true;
workbook.Settings.FormulaSettings.ForceFullCalculation = true;
workbook.CalculateFormula();
workbook.Save(@"C:\For Attachment\Test.xlsx", SaveFormat.Xlsx);
return WorkbookToByteArray(workbook);
}
private void CellSetValue(ref Worksheet sheet, int row, int column, Aspose.Cells.Style style, object value)
{
Cell cell = sheet.Cells[row, column];
cell.SetStyle(style);
if (value.GetType() == typeof(String) && value.ToString().StartsWith("="))
{
cell.Formula = value.ToString();
}
else
{
cell.PutValue(value);
}
cell.PutValue(value);
}
TestXlsx.png (76.5 KB)