In this tutorial, we will learn how to remove Excel formula without removing contents in C#. Sometimes we have to share Excel files with others containing calculated values but we do not want to expose our formulas. This requires to delete the Excel formula but keep the contents in C# as described in the following steps.
Steps to Remove Excel Formula but Keep Value using C#
- Add Aspose.Cells for .NET to project using NuGet package manager
- Add reference for System and Aspose.Cells namespace
- Instantiate license to avoid trial version watermark
- Load the workbook containing data and formula
- Get the reference of the cell whose formula is to be removed
- Store value in a temporary variable and remove formula
- Reset cell value using the temporary variable
- Save the workbook
The above steps describe the process to remove Excel formula but keep the value in C#. We access the desired cell from the worksheet and save its value in a temporary variable. Then we remove the formula from the cell and fill it with value again from the temporary variable. Finally, we save the workbook.
Code to Remove Excel Formula and keep the value in C#
| using System; | |
| using Aspose.Cells; | |
| namespace RemoveExcelFormulaWithoutRemovingContentsInCSharp | |
| { | |
| class Program | |
| { | |
| static void Main(string[] args) | |
| { | |
| // Instantiate license to avoid trial version watermark | |
| License license = new License(); | |
| license.SetLicense("Aspose.Cells.lic"); | |
| // Load the workbook containing data and formula | |
| Workbook workbookWithFormula = new Workbook("WorkbookWithFormula.xlsx"); | |
| // Get the reference of the cell whose formula is to be removed | |
| Cell cellWithFormula = workbookWithFormula.Worksheets[0].Cells["C1"]; | |
| // Store value in a temporary variable | |
| Object tempData = cellWithFormula.Value; | |
| // Remove formula | |
| cellWithFormula.Formula = ""; | |
| // Reset cell value using the temporary variable | |
| cellWithFormula.Value = tempData; | |
| // Save the workbook | |
| workbookWithFormula.Save("WorkbookWithoutFormula.xlsx"); | |
| } | |
| } | |
| } |
The above sample code removes formula from a single cell. However, if you want to remove formulas from the entire workbook, you may use WorkSheet.Cells.RemoveFormulas() that removes all formulas from the entire workbook and replaces each cell value with the respective formula result. The following code can be used to delete excel formulas but keep the contents in C# from the entire workbook.
Code to Remove Excel Formulas but keep the value using C#
| using System; | |
| using Aspose.Cells; | |
| namespace RemoveAllExcelFormulasWithoutRemovingContentsInCSharp | |
| { | |
| class Program | |
| { | |
| static void Main(string[] args) | |
| { | |
| // To avoid trial version watermark first instantiate the license | |
| License license = new License(); | |
| license.SetLicense("Aspose.Cells.lic"); | |
| // Load the target workbook from which all formulas are to be deleted | |
| Workbook workbookWithFormula = new Workbook("WorkbookWithFormulas.xlsx"); | |
| // Iterate through all the worksheets to remove formulas | |
| foreach(Worksheet worksheet in workbookWithFormula.Worksheets) | |
| worksheet.Cells.RemoveFormulas(); | |
| // Save the resultant workbook | |
| workbookWithFormula.Save("WorkbookWithoutFormulas.xlsx"); | |
| } | |
| } | |
| } |
Note that neither we need MS Excel nor interop is used for this operation. Once the Excel file is ready you can also convert it to other formats also as described in the article on how to create XPS from excel in C#.
