News Category

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

2023-04-27 08:43:00 Written by  Administrator
Rate this item
(4 votes)

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.

Additional Info

  • tutorial_title:
Last modified on Thursday, 27 April 2023 00:48