This module uses the DevExpress Spreadsheet API to change formula calculation modes in a predefined Excel workbook.
Use the Calculation Settings section to specify whether the workbook should automatically recalculate formulas when data changes. This setting is in effect in Excel documents. The demo also shows you how to force formula recalculation from code.
Use the Process and Save as ... dropdown button to select the output format, change the formula calculation mode, and download the result.
Select a Document
InvestmentPortfolio.xlsx
Calculation Settings
using DevExpress.Spreadsheet;
Stream CalculateWorkbook(Stream inputStream, CalculationMode? documentCalculationMode, bool exportToPdf, DocumentFormat outputFormat) {
using Workbook workbook = new Workbook();
// Load the document
workbook.LoadDocument(inputStream);
// You can use different methods to force recalculation depending on your usage scenario.
// The "Calculate" method recalculates values in cells marked for calculation and "CalculateAlways" cells:
// -The cell contains a volatile function (IFunction.Volatile)
// -The cell references another "CalculateAlways" cell
// -The cell contains a circular reference
workbook.Calculate();
// The "CalculateFull" method forces recalculation of all spreadsheet cells regardless of whether the cell is marked for calculation.
//workbook.CalculateFull();
// The "CalculateFullRebuild" method forces a full recalculation of all spreadsheet cells and rebuilds the dependency tree (by default).
// This method behavior depends on the DocumentOptions.CalculationEngineType value.
// When DocumentOptions.CalculationEngineType is set to CalculationEngineType.Recursive CalculateFullRebuild and CalculateFull are equivalent.
//workbook.CalculateFullRebuild();
// Change the document calculation mode stored in the resulting document.
if(documentCalculationMode.HasValue)
workbook.DocumentSettings.Calculation.Mode = documentCalculationMode.Value;
// Create output stream
var outputStream = new MemoryStream();
// Save document in target format
if(exportToPdf)
workbook.ExportToPdf(outputStream);
else
workbook.SaveDocument(outputStream, outputFormat);
outputStream.Seek(0, SeekOrigin.Begin);
return outputStream;
}