Explore the Possibilities of the What-If Analysis Scenario Manager in Excel Using C#
Live Chat Icon For mobile
Live Chat Icon
Popular Categories.NET  (178).NET Core  (29).NET MAUI  (214)Angular  (110)ASP.NET  (51)ASP.NET Core  (82)ASP.NET MVC  (89)Azure  (41)Black Friday Deal  (1)Blazor  (225)BoldSign  (15)DocIO  (24)Essential JS 2  (109)Essential Studio  (200)File Formats  (68)Flutter  (133)JavaScript  (225)Microsoft  (120)PDF  (81)Python  (1)React  (105)Streamlit  (1)Succinctly series  (131)Syncfusion  (936)TypeScript  (33)Uno Platform  (3)UWP  (4)Vue  (45)Webinar  (52)Windows Forms  (61)WinUI  (71)WPF  (162)Xamarin  (161)XlsIO  (38)Other CategoriesBarcode  (5)BI  (29)Bold BI  (8)Bold Reports  (2)Build conference  (9)Business intelligence  (55)Button  (4)C#  (156)Chart  (138)Chart of the week  (53)Cloud  (15)Company  (443)Dashboard  (8)Data Science  (3)Data Validation  (8)DataGrid  (67)Development  (652)Doc  (8)DockingManager  (1)eBook  (99)Enterprise  (22)Entity Framework  (7)Essential Tools  (14)Excel  (43)Extensions  (22)File Manager  (7)Gantt  (19)Gauge  (12)Git  (5)Grid  (31)HTML  (13)Installer  (2)Knockout  (2)Language  (1)LINQPad  (1)Linux  (2)M-Commerce  (1)Metro Studio  (11)Mobile  (512)Mobile MVC  (9)OLAP server  (1)Open source  (1)Orubase  (12)Partners  (21)PDF viewer  (43)Performance  (12)PHP  (2)PivotGrid  (4)Predictive Analytics  (6)Report Server  (3)Reporting  (10)Reporting / Back Office  (11)Rich Text Editor  (12)Road Map  (12)Scheduler  (54)Security  (4)SfDataGrid  (9)Silverlight  (21)Sneak Peek  (31)Solution Services  (4)Spreadsheet  (11)SQL  (14)Stock Chart  (1)Surface  (4)Tablets  (5)Theme  (12)Tips and Tricks  (112)UI  (393)Uncategorized  (68)Unix  (2)User interface  (68)Visual State Manager  (2)Visual Studio  (31)Visual Studio Code  (19)Web  (609)What's new  (333)Windows 8  (19)Windows App  (2)Windows Phone  (15)Windows Phone 7  (9)WinRT  (26)
Explore the Possibilities of the What-If Analysis Scenario Manager in Excel Using C#

Explore the Possibilities of the What-If Analysis Scenario Manager in Excel Using C#

What-if analysis in Excel is a powerful tool for individuals and organizations to use to explore various scenarios, predict the impact of choices, and make decisions confidently. It allows you to create scenarios with different sets of values for the same cells in a worksheet. With this feature, a user can view multiple forecasts of data in a single Excel document.

From the 2023 Volume 3 release onward, the Syncfusion Excel Library (XlsIO) supports a what-if analysis scenario manager feature in Excel documents using C#.

Enjoy a smooth experience with Syncfusion’s Excel Library! Get started with a few lines of code and without Microsoft or interop dependencies.

Users can perform the following actions using the what-if analysis scenario manager:

  • Create scenarios with different sets of values and apply them to view the forecast results.
  • Apply scenarios and save the results as separate Excel documents.
  • Protect or hide the scenarios to prevent users from changing them.

Let’s see how to perform these actions in an Excel document using C#.

Note: If you are new to our Excel Library, following our getting started guide is highly recommended.

Creating scenarios

You can create multiple scenarios in an Excel document, and each scenario can have up to 32 variable cell ranges with values.

The following code example illustrates how to create what-if analysis scenarios in an Excel document.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;

    FileStream inputStream = new FileStream("WhatIfAnalysisTemplate.xlsx", FileMode.Open, FileAccess.Read);
    IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
    inputStream.Dispose();

    IWorksheet worksheet = workbook.Worksheets[0];

    // Access the collection of scenarios in the worksheet.
    IScenarios scenarios = worksheet.Scenarios;

    //Initialize list objects with different values for scenarios.
    List<object> currentChangePercentage_Values = new List<object> { 0.23, 0.8, 1.1, 0.5, 0.35, 0.2};
    List<object> increasedChangePercentage_Values = new List<object> { 0.45, 0.56, 0.9, 0.5, 0.58, 0.43};
    List<object> decreasedChangePercentage_Values = new List<object> { 0.3, 0.2, 0.5, 0.3, 0.5, 0.23};
    List<object> currentQuantity_Values = new List<object> { 1500, 3000, 5000, 4000, 500, 4000 };
    List<object> increasedQuantity_Values = new List<object> { 1000, 5000, 4500, 3900, 10000, 8900 };
    List<object> decreasedQuantity_Values = new List<object> { 1000, 2000, 3000, 3000, 300, 4000 };

    //Add scenarios in the worksheet with different values for the same cells.
    scenarios.Add("Current % of Change", worksheet.Range["F5:F10"], currentChangePercentage_Values);
    scenarios.Add("Increased % of Change", worksheet.Range["F5:F10"], increasedChangePercentage_Values);
    scenarios.Add("Decreased % of Change", worksheet.Range["F5:F10"], decreasedChangePercentage_Values);
    scenarios.Add("Current Quantity", worksheet.Range["D5:D10"], currentQuantity_Values);
    scenarios.Add("Increased Quantity", worksheet.Range["D5:D10"], increasedQuantity_Values);
    scenarios.Add("Decreased Quantity", worksheet.Range["D5:D10"], decreasedQuantity_Values);


    //Saving the workbook as a stream.
    using (FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite))
    {
        workbook.SaveAs(stream);
    }

}

The following is the output image.

Creating scenarios using what-if analysis feature in an Excel document
Creating scenarios using what-if analysis feature in an Excel document

Handle Excel files like a pro with Syncfusion’s C# Excel Library, offering well-documented APIs for each functionality.

Applying scenarios

You can get a cell value after applying specific scenarios in the worksheet. You can save each scenario’s results as a separate Excel document. This way, the resultant document can be shared with other users.

The following code example illustrates how to apply scenarios in a worksheet and resave the results in separate Excel documents.

//Access the collection of scenarios in the worksheet.
IScenarios scenarios = worksheet.Scenarios;

for (int pos =0; pos < scenarios.Count; pos++)
{
    //Apply scenarios.
    scenarios[pos].Show();

    IWorkbook newBook = excelEngine.Excel.Workbooks.Create(0);

    IWorksheet newSheet = newBook.Worksheets.AddCopy(worksheet);        

    newSheet.Name = scenarios[pos].Name;

    //Saving the new workbook as a stream.
    using (FileStream stream = new FileStream(scenarios[pos].Name + ".xlsx", FileMode.Create, FileAccess.ReadWrite))
    {
        newBook.SaveAs(stream);
    }

    //To restore the cell values from the previous scenario results.
    scenarios["Current % of Change"].Show();
    scenarios["Current Quantity"].Show();
}

Refer to the following images. Here, we’ve applied the Current % of Change and Current Quantity scenarios. 

Excel document before applying scenarios
Excel document before applying scenarios
Excel document after applying the scenarios
Excel document after applying the scenarios

Experience Syncfusion's Excel Framework in action through interactive demos, giving you the confidence to implement it in your projects immediately.

Protect scenarios

You can protect a scenario in an Excel document to keep other users from modifying it. By default, the scenarios are protected when the sheet is protected. This can be disabled by making the IScenario.Locked property false.

The following code example illustrates how to protect scenarios in an Excel document.

IWorksheet worksheet = workbook.Worksheets[0];

//To make a scenario editable after protecting the sheet set scenarios[0].Locked = false;

//Enable worksheet protection.
worksheet. Protect("scenario");

The following is the output image. You can see that the edit option is disabled.

Protecting scenarios in an Excel document
Protecting scenarios in an Excel document

Hiding scenarios

You can also hide a scenario in an Excel document, so only specific users can view the scenario results. The following code example illustrates how to hide a scenario in an Excel document.

//Access the collection of scenarios in the worksheet.
IScenarios scenarios = worksheet.Scenarios;

//Disable the protection for a specific scenario.
scenarios["Increased % of Change"].Hidden = true;

worksheet. Protect("Scenario");

Refer to the following output image. In it, we’ve hidden the Increased % of Change scenario.

Hiding a scenario in an Excel document
Hiding a scenario in an Excel document

GitHub samples

You can download examples of the C# what-if analysis scenario manager for Excel from this GitHub page.

Don't settle for ordinary spreadsheet solutions. Switch to Syncfusion and upgrade the way you handle Excel files in your apps!

Conclusion

Thanks for reading! In this blog, we’ve explored the new C# what-if analysis scenario manager feature in the Syncfusion Excel Library. This tool allows you to modify cell values and observe the resulting impact on worksheet formulas.

Take a moment to peruse the documentation, where you’ll find other Excel Library options and features like conditional formattingtables, pivot tables, and charts. Using this library, you can also export Excel data to  PDFimagedata tableCSVTSVHTMLcollections of objectsODSJSON, and more file formats.

Are you already a Syncfusion user? You can download the product setup here. If you’re not a Syncfusion user yet, you can download a free 30-day trial here.

Please let us know in the comments section below if you have any questions about these features. You can also contact us through our support forum, support portal, or feedback portal. We are always happy to assist you!

Related blogs

Tags:

Share this post:

Popular Now

Be the first to get updates

Subscribe RSS feed

Be the first to get updates

Subscribe RSS feed