C#: Remove Formulas but Keep Values in Excel

When you need to share an Excel file with others but don't want to give them access to the underlying formulas, converting the formulas to numeric values ensures that the recipients can view and work with the calculated results without altering the original calculations. In this article, you will learn how to programmatically remove formulas from cells in Excel but keep the values using Spire.XLS for .NET.

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Remove Formulas from Excel Cells but Keep Values in C#

MS Excel provides the "Paste Values" function to help remove formulas while keeping the values. To implement the same functionality in C# through code, refer to the below steps.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Loop through the worksheets in the file, and then loop through the cells in each sheet.
• Determine whether the cell contains a formula using CellRange.HasFormula property.
• If yes, get the formula value using CellRange.FormulaValue property. Then clear the original formula in the cell and fill it with the formula value using CellRange.Value2 property.
• Save the result file using Workbook.SaveToFile() method.

using Spire.Xls;
using System;
namespace RemoveFormulas
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

            //Loop through worksheets
            foreach (Worksheet sheet in workbook.Worksheets)
            {
                //Loop through cells
                foreach (CellRange cell in sheet.Range)
                {
                    //Determine whether the cell contain formula
                    if (cell.HasFormula)
                    {
                        //If yes, get the formula value in the cell
                        Object value = cell.FormulaValue;

                        //Clear cell content
                        cell.Clear(ExcelClearOptions.ClearContent);

                        //Fill the formula value into the cell
                        cell.Value2 = value;
                    }
                }
            }

            //Save the result file
            workbook.SaveToFile("DeleteFormula.xlsx", ExcelVersion.Version2016);
        }
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.