News Category

Formula

Formula (4)

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.
  • C#
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);
        }
    }
}

C#: Remove Formulas but Keep Values in Excel

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.

A named range in Excel is a user-defined name given to a specific cell or range of cells. It allows you to assign a meaningful and descriptive name to a set of data, making it easier to refer to that data in formulas, functions, and other parts of the spreadsheet. In this article, you will learn how to create, edit or delete named ranges in Excel in C# and VB.NET 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

Create a Named Range in Excel in C# and VB.NET

You can use the Workbook.NameRanges.Add(string name) method provided by Spire.XLS for .NET to add a named range to an Excel workbook. Once the named range is added, you can define the cell or range of cells it refers to using the INamedRange.RefersToRange property.

The following steps explain how to create a named range in Excel using Spire.XLS for .NET:

  • Initialize an instance of the Workbook class.
  • Load an Excel workbook using the Workbook.LoadFromFile() method.
  • Add a named range to the workbook using the Workbook.NameRanges.Add(string name) method.
  • Get a specific worksheet in the workbook using the Workbook.Worksheets[int index] property.
  • Set the cell range that the named range refers to using the INamedRange.RefersToRange property.
  • Save the result file using the Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;
using Spire.Xls.Core;

namespace CreateNamedRanges
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Initialize an instance of the Workbook class
            Workbook workbook = new Workbook();
            //Load an Excel workbook
            workbook.LoadFromFile(@"Sample.xlsx");

            //Add a named range to the workbook
            INamedRange namedRange = workbook.NameRanges.Add("Amount");

            //Get a specific worksheet in the workbook
            Worksheet sheet = workbook.Worksheets[0];
            
            //Set the cell range that the named range references
            namedRange.RefersToRange = sheet.Range["D2:D5"];

            //Save the result file to a specific location
            string result = "CreateNamedRange.xlsx";
            workbook.SaveToFile(result, ExcelVersion.Version2013);
            workbook.Dispose();
        }
    }
}

C#/VB.NET:  Create, Edit, or Delete Named Ranges in Excel

Edit an Existing Named Range in Excel in C# and VB.NET

After you've created a named range, you may want to modify its name or adjust the cells it refers to.

The following steps explain how to modify the name and cell references of an existing named range in Excel using Spire.XLS for .NET:

  • Initialize an instance of the Workbook class.
  • Load an Excel workbook using the Workbook.LoadFromFile() method.
  • Get a specific named range in the workbook using the Workbook.NameRanges[int index] property.
  • Modify the name of the named range using the INamedRange.Name property.
  • Modify the cells that the named range refers to using the INamedRange.RefersToRange property.
  • Save the result file using the Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;
using Spire.Xls.Core;

namespace ModifyNamedRanges
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Initialize an instance of the Workbook class
            Workbook workbook = new Workbook();
            //Load an Excel workbook
            workbook.LoadFromFile(@"CreateNamedRange.xlsx");

            //Get a specific named range in the workbook
            INamedRange namedRange = workbook.NameRanges[0];

            //Change the name of the named range
            namedRange.Name = "MonitorAmount";

            //Set the cell range that the named range references
            namedRange.RefersToRange = workbook.Worksheets[0].Range["D2"];

            //Save the result file to a specific location
            string result = "ModifyNamedRange.xlsx";
            workbook.SaveToFile(result, ExcelVersion.Version2013);
            workbook.Dispose();
        }
    }
}

C#/VB.NET:  Create, Edit, or Delete Named Ranges in Excel

Delete a Named Range from Excel in C# and VB.NET

If you have made significant changes to the structure or layout of your spreadsheet, it might be necessary to delete a named range that is no longer relevant or accurate.

The following steps explain how to delete a named range from Excel using Spire.XLS for .NET:

  • Initialize an instance of the Workbook class.
  • Load an Excel workbook using the Workbook.LoadFromFile() method.
  • Remove a specific named range by its index or name using the Workbook.NameRanges.RemoveAt(int index) or Workbook.NameRanges.Remove(string name) method.
  • Save the result file using the Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;
using Spire.Xls.Core;

namespace RemoveNamedRanges
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Initialize an instance of the Workbook class
            Workbook workbook = new Workbook();
            //Load an Excel workbook
            workbook.LoadFromFile(@"CreateNamedRange.xlsx");

            //Remove a specific named range by its index
            workbook.NameRanges.RemoveAt(0);

            //Remove a specific named range by its name
            //workbook.NameRanges.Remove("Amount");

            //Save the result file to a specific location
            string result = "RemoveNamedRange.xlsx";
            workbook.SaveToFile(result, ExcelVersion.Version2013);
            workbook.Dispose();
        }
    }
}

C#/VB.NET:  Create, Edit, or Delete Named Ranges in Excel

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.

Background

Excel is widely used to organize data manipulations like arithmetic operations. Excel provides many built-in functions which automate a number of types of calculation. Functions are pre-programmed formulate for example, the square-root function, trigonometric functions, logarithms etc. Excel has more than 300 functions covering a range of statistical, mathematical, financial and logical operations. There is no doubt that using a function offers a shortcut method.

Calculate Formulas in XLS Document

Microsoft Excel is a powerful tool which has many uses, the most basic feature of which is performing functions. The aim of this article is to help you perform simple arithmetic operations on values in programming by using excel functions. Spire.Xls for .NET can help you easily create a new excel document or load an existing excel document into program, and calculate data of designated cell by function. Applied in Console platform, WinForm and Asp.net, It provide different types of mathematical functions, statistical functions , logic functions ,and string functions to calculate data with C# codes.

The following is the method example of using Console application to show how Spire.XLS for .NET realizes the calculation formula:

Step 1: Build a console application, and add spire.XLS.dll, Spire.Common.dll assembly.

Step 2: Instantiate an object of Spire.Xls.WorkBook, and add a “WorkSheet” in WorkBook object.

[C#]
Workbook workbook = new Workbook();
Worksheet sheet = workbook. Worksheets[0];

Step 3: Set the value and format in Cell A1 and Cell A3.veiwing the C# Code.

[C#]
//set Column A, B, C width
sheet.SetColumnWidth(1, 32);
sheet.SetColumnWidth(2, 16);
sheet.SetColumnWidth(3, 16);

// Set value of Cell A1
sheet.Range[currentRow++, 1].Value = "Examples of formulas :";
// Set value of Cell A2.
sheet.Range[++currentRow, 1].Value = "Test data:";

// Set text format Of Cell A1
CellRange range = sheet.Range["A1"];
range.Style.Font.IsBold = true;
range.Style.FillPattern = ExcelPatternType.Solid;
range.Style.KnownColor = ExcelColors.LightGreen1;
range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

Step 4: Set some cells value and then to sum up some cells data and the results will be displayed in one of the cells.

[C#]
sheet.Range[currentRow, 2].NumberValue = 7.3;
sheet.Range[currentRow, 3].NumberValue = 5;
sheet.Range[currentRow, 4].NumberValue = 8.2;
sheet.Range[currentRow, 5].NumberValue = 4;
sheet.Range[currentRow, 6].NumberValue = 3;
sheet.Range[currentRow, 7].NumberValue = 11.3;
//Create arithmetic expression string about cells 

currentFormula = "=Sheet1!$B$3 + Sheet1!$C$3+Sheet1!$D$3+Sheet1!$E$3+Sheet1!$F$3+Sheet1!$G$3";
//Caculate arithmetic expression about cells 
formulaResult = workbook.CaculateFormulaValue(currentFormula);
value = formulaResult.ToString();
sheet.Range[currentRow, 2].Value = value;

Step 5: Respectively set value and text format of Cell A4, B4.

[C#]
sheet.Range[++currentRow, 1].Value = "Formulas"; ;
sheet.Range[currentRow, 2].Value = "Results";
range = sheet.Range[currentRow, 1, currentRow, 2];
range.Style.Font.IsBold = true;
range.Style.KnownColor = ExcelColors.LightGreen1;
range.Style.FillPattern = ExcelPatternType.Solid;
range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

Step 6: Realize calculation simple expression.

[C#]
// Create arithmetic tables enclosed type string
currentFormula = "=33*3/4-2+10";
sheet.Range[++currentRow, 1].Text = currentFormula;
// Caculate arithmetic expression
formulaResult = workbook.CaculateFormulaValue(currentFormula);
value = formulaResult.ToString();
sheet.Range[currentRow, 2].Value = value;

Step 7: Realize some mathematic functions.

[C#]
//absolute value function .
currentFormula = "=ABS(-1.21)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;

Step 8: Realize some logic function.

[C#]
//NOT function
//Create NOT function string 
currentFormula = "=NOT(true)";
sheet.Range[currentRow, 1].Text = currentFormula;
//Caculate NOT function
formulaResult = workbook.CaculateFormulaValue(currentFormula);
value = formulaResult.ToString();
sheet.Range[currentRow, 2].Value = value;
sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;

Step 9: Realize some string handling functions.

[C#]
//Get the substring
// Build substring function
currentFormula = "=MID(\"world\",4,2)";
sheet.Range[++currentRow, 1].Text = currentFormula;
//Caculate substring function
formulaResult = workbook.CaculateFormulaValue(currentFormula);
value = formulaResult.ToString();
sheet.Range[currentRow, 2].Value = value;
sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;

Step 10: Realize a random function.

[C#]
// Random function
// Create random function string.
currentFormula = "=RAND()";
sheet.Range[++currentRow, 1].Text = currentFormula;
//Caculate random function
formulaResult = workbook.CaculateFormulaValue(currentFormula);
value = formulaResult.ToString();
sheet.Range[currentRow, 2].Value = value;

Step 11: Save workbook object as file.

[C#]
workbook.SaveToFile("formulaTest.xls",ExcelVersion.Version97to2003);

Viewing the full c# code

[C#]
using System;
using System.Collections.Generic;
using System.Text;
using Spire.Xls;

namespace XlsCalculateFormula
{
    class Program
    {
        static void Main(string[] args)
        {
            //Instanitate an object of Spire.Xls.Workbook
            Workbook workbook = new Workbook();
            // Add a Spire.Xls.Worksheet to Spire.Xls.Workbook
            Worksheet sheet = workbook.Worksheets[0];

            int currentRow = 1;
            string currentFormula = string.Empty;
            object formulaResult = null;
            string value = string.Empty;

            // Set width respectively of Column A ,Column B,Column C 
            sheet.SetColumnWidth(1, 32);
            sheet.SetColumnWidth(2, 16);
            sheet.SetColumnWidth(3, 16);

            //Set the value of Cell A1
            sheet.Range[currentRow++, 1].Value = "Examples of formulas :";
            // Set the value of Cell A2
            sheet.Range[++currentRow, 1].Value = "Test data:";
            // Set the style of Cell A1
            CellRange range = sheet.Range["A1"];
            range.Style.Font.IsBold = true;
            range.Style.FillPattern = ExcelPatternType.Solid;
            range.Style.KnownColor = ExcelColors.LightGreen1;
            range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            // Additive operation of mutiple cells
            sheet.Range[currentRow, 2].NumberValue = 7.3;
            sheet.Range[currentRow, 3].NumberValue = 5; ;
            sheet.Range[currentRow, 4].NumberValue = 8.2;
            sheet.Range[currentRow, 5].NumberValue = 4;
            sheet.Range[currentRow, 6].NumberValue = 3;
            sheet.Range[currentRow, 7].NumberValue = 11.3;
            // Create arithmetic expression string about cells 
            currentFormula = "=Sheet1!$B$3 + Sheet1!$C$3+Sheet1!$D$3+Sheet1!$E$3+Sheet1!$F$3+Sheet1!$G$3";
            //Caculate arithmetic expression  about cells 
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;


            // Set the value and format of two head cell
            sheet.Range[++currentRow, 1].Value = "Formulas"; ;
            sheet.Range[currentRow, 2].Value = "Results";
            sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;
            range = sheet.Range[currentRow, 1, currentRow, 2];
            range.Style.Font.IsBold = true;
            range.Style.KnownColor = ExcelColors.LightGreen1;
            range.Style.FillPattern = ExcelPatternType.Solid;
            range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;
          
            // Expression caculation

            // Create arithmetic tables enclosed type string
            currentFormula = "=33*3/4-2+10";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            // Caculate arithmetic expression
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

            /// The mathematics function ///

            //Absolute value function

            // Create abosolute value function string
            currentFormula = "=ABS(-1.21)";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            // Caculate abosulte value function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;


            ///  Statistical function///

            // Sum function
            // Create sum function string
            currentFormula = "=SUM(18,29)";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            // Caculate sum function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

            ///logic function///
            
            //NOT function
            // Create NOT function string 
            currentFormula = "=NOT(true)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            //Caculate NOT function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;
            sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;

            ///String Manipulation function///          
            //Get the substring
            // Build substring function
            currentFormula = "=MID(\"world\",4,2)";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            //Caculate substring function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;
            sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;

            // Random function
            // Create random function string.
            currentFormula = "=RAND()";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            //Caculate random function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

            // Save Spire.Xls.Workbook as exel file
            workbook.SaveToFile("formulaTest2.xls",ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start("formulaTest2.xls");
        }
        }
    }

Screenshot:

formula_01

Excel is a powerful spreadsheet software with numerous features, but formulas and functions are undoubtedly among its most critical tools. They enable users to perform a wide range of mathematical, statistical, and logical operations on their data, allowing them to derive meaningful insights quickly and accurately. In this article, we will explain how to add or read formulas and functions in Excel files in C# and VB.NET 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

Add Formulas and Functions to Excel in C# and VB.NET

The Worksheet.Range[int row, int column].Formula property in Spire.XLS for .NET is used to add formulas or functions to specific cells in an Excel worksheet. The main steps are as follows:

  • Initialize an instance of the Workbook class.
  • Get a specific worksheet by its index using the Workbook.Worksheets[int index] property.
  • Add some text and numeric data to specific cells of the worksheet using the Worksheet.Range[int row, int column].Text and Worksheet.Range[int row, int column].NumberValue properties.
  • Add text and formulas to specific cells of the worksheet using the Worksheet.Range[int row, int column].Text and Worksheet.Range[int row, int column].Formula properties.
  • Add text and functions to specific cells of the worksheet using the Worksheet.Range[int row, int column].Text and Worksheet.Range[int row, int column].Formula properties.
  • Save the result file using Workbook.SaveToFile(string fileName, ExcelVersion version) method.
  • C#
  • VB.NET
using Spire.Xls;

namespace AddFormulasAndFunctions
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Initialize an instance of the Workbook class
            Workbook workbook = new Workbook();

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Declare two variables: currentRow, currentFormula
            int currentRow = 1;
            string currentFormula;

            //Add text to the worksheet and set cell style
            sheet.Range[currentRow, 1].Text = "Test Data:";
            sheet.Range[currentRow, 1].Style.Font.IsBold = true;
            sheet.Range[currentRow, 1].Style.FillPattern = ExcelPatternType.Solid;
            sheet.Range[currentRow, 1].Style.KnownColor = ExcelColors.LightGreen1;
            sheet.Range[currentRow, 1].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            //Add some numeric data to the worksheet
            sheet.Range[++currentRow, 1].NumberValue = 7.3;
            sheet.Range[currentRow, 2].NumberValue = 5;
            sheet.Range[currentRow, 3].NumberValue = 8.2;
            sheet.Range[currentRow, 4].NumberValue = 4;
            sheet.Range[currentRow, 5].NumberValue = 3;
            sheet.Range[currentRow, 6].NumberValue = 11.3;

            currentRow++;

            //Add text to the worksheet and set cell style
            sheet.Range[++currentRow, 1].Text = "Formulas";
            sheet.Range[currentRow, 2].Text = "Results";
            sheet.Range[currentRow, 1, currentRow, 2].Style.Font.IsBold = true;
            sheet.Range[currentRow, 1, currentRow, 2].Style.KnownColor = ExcelColors.LightGreen1;
            sheet.Range[currentRow, 1, currentRow, 2].Style.FillPattern = ExcelPatternType.Solid;
            sheet.Range[currentRow, 1, currentRow, 2].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            //Add text and formulas to the worksheet
            currentFormula = "=\"Hello\"";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=300";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=3389.639421";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=false";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=1+2+3+4+5-6-7+8-9";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=33*3/4-2+10";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=Sheet1!$B$2";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //Add text and Functions to the worksheet
            //AVERAGE
            currentFormula = "=AVERAGE(Sheet1!$D$2:F$2)";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //COUNT
            currentFormula = "=COUNT(3,5,8,10,2,34)";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //NOW
            currentFormula = "=NOW()";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;
            sheet.Range[currentRow, 2].Style.NumberFormat = "yyyy-MM-DD";

            //SECOND
            currentFormula = "=SECOND(0.503)";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MINUTE
            currentFormula = "=MINUTE(0.78125)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MONTH
            currentFormula = "=MONTH(9)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //DAY
            currentFormula = "=DAY(10)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //TIME
            currentFormula = "=TIME(4,5,7)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //DATE
            currentFormula = "=DATE(6,4,2)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //RAND
            currentFormula = "=RAND()";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //HOUR
            currentFormula = "=HOUR(0.5)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MOD
            currentFormula = "=MOD(5,3)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //WEEKDAY
            currentFormula = "=WEEKDAY(3)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //YEAR
            currentFormula = "=YEAR(23)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //NOT
            currentFormula = "=NOT(true)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //OR
            currentFormula = "=OR(true)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //AND
            currentFormula = "=AND(TRUE)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //VALUE
            currentFormula = "=VALUE(30)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //LEN
            currentFormula = "=LEN(\"world\")";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MID
            currentFormula = "=MID(\"world\",4,2)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //ROUND
            currentFormula = "=ROUND(7,3)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SIGN
            currentFormula = "=SIGN(4)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //INT
            currentFormula = "=INT(200)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //ABS
            currentFormula = "=ABS(-1.21)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //LN
            currentFormula = "=LN(15)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //EXP
            currentFormula = "=EXP(20)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SQRT
            currentFormula = "=SQRT(40)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //PI
            currentFormula = "=PI()";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //COS
            currentFormula = "=COS(9)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SIN
            currentFormula = "=SIN(45)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MAX
            currentFormula = "=MAX(10,30)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MIN
            currentFormula = "=MIN(5,7)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //AVERAGE
            currentFormula = "=AVERAGE(12,45)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SUM
            currentFormula = "=SUM(18,29)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //IF
            currentFormula = "=IF(4,2,2)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SUBTOTAL
            currentFormula = "=SUBTOTAL(3,Sheet1!A2:F2)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //Set width of the 1st, 2nd and 3rd columns
            sheet.SetColumnWidth(1, 32);
            sheet.SetColumnWidth(2, 16);
            sheet.SetColumnWidth(3, 16);

            //Create a cell style
            CellStyle style = workbook.Styles.Add("Style");
            //Set the horizontal alignment as left
            style.HorizontalAlignment = HorizontalAlignType.Left;
            //Apply the style to the worksheet
            sheet.ApplyStyle(style);

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

C#/VB.NET: Add or Read Formulas and Functions in Excel

Read Formulas and Functions in Excel in C# and VB.NET

To read formulas and functions in an Excel worksheet, you need to iterate through all the cells in the worksheet, after that, find the cells containing formulas or functions using the Cell.HasFormula property, then get the formulas or functions of the cells using the CellRange.Formula property. The detailed steps are as follows:

  • Initialize an instance of the Workbook class.
  • Load an Excel file using the Workbook.LoadFromFile() method.
  • Get a specific worksheet by its index using the Workbook.Worksheets[sheetIndex] property.
  • Initialize an instance of the StringBuilder class.
  • Access the used range of the worksheet using the Worksheet.AllocatedRange property.
  • Iterate through all the cells in the used range.
  • Find the cells containing formulas/functions using the Cell.HasFormula property.
  • Get the names and the formulas/functions of the cells using the CellRange.RangeAddressLocal and CellRange.Formula properties.
  • Append the cell names and formulas/functions to the StringBuilder using the StringBuilder.AppendLine() method.
  • Write the content of the StringBuilder into a .txt file using the File.WriteAllText() method.
  • C#
  • VB.NET
using Spire.Xls;
using System.IO;
using System.Text;

namespace ReadFormulasAndFunctions
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Initialize an instance of the Workbook class
            Workbook workbook = new Workbook();
            //Load an Excel file
            workbook.LoadFromFile("AddFormulasAndFunctions.xlsx");

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Initialize an instance of the StringBuilder class
            StringBuilder sb = new StringBuilder();

            //Access the used range of the worksheet
            CellRange usedRange = sheet.AllocatedRange;

            //Loop through all the cells in the used range
            foreach (CellRange cell in usedRange)
            {
                //Detect if the current cell has formula/function
                if (cell.HasFormula)
                {
                    //Get the cell name
                    string cellName = cell.RangeAddressLocal;
                    //Get the formula/function
                    string formula = cell.Formula;
                    //Append the cell name and formula/function to the StringBuilder
                    sb.AppendLine(cellName + " has a formula: " + formula);
                }
            }

            //Write the content of the StringBuilder into a .txt file
            File.WriteAllText("ReadFormulasAndFunctions.txt", sb.ToString());
        }
    }
}

C#/VB.NET: Add or Read Formulas and Functions in Excel

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.