DEV Community

jelizaveta
jelizaveta

Posted on

Adding or Reading Excel Formulas with C#: A Complete Guide

In office data processing tasks, Excel formulas and functions serve as the foundation for automated calculations and data analysis. For .NET developers, being able to programmatically inject formulas into Excel files or extract formula logic from existing spreadsheets is a common and practical need. This article will demonstrate how to add and read Excel formulas using C# with Free Spire.XLS for .NET , a lightweight and free component.

Preparation: Installing Free Spire.XLS

Before coding, we need to add Free Spire.XLS for .NET to our project. This component can be easily installed via the NuGet Package Manager by executing the following command in the Package Console:

Install-Package FreeSpire.XLS
Enter fullscreen mode Exit fullscreen mode

Once installed, we can access all Excel operation-related classes and methods through the using Spire.Xls; namespace in our code.

Part 1: Adding Formulas to Excel

The scenarios for adding formulas are extensive, such as batch-calculating total sales, averaging student scores, generating dynamic reports, and more. The following example demonstrates how to create a new Excel workbook, write basic data into it, and then add formulas to specific cells.

using Spire.Xls;

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

// Add basic numeric data
sheet.Range[1, 1].NumberValue = 1;
sheet.Range[1, 2].NumberValue = 2;
sheet.Range[1, 3].NumberValue = 3;
sheet.Range[1, 4].NumberValue = 4;
sheet.Range[1, 5].NumberValue = 5;
sheet.Range[1, 6].NumberValue = 6;

// Add function formula: AVERAGE
string averageFormula = "=AVERAGE(Sheet1!$A$1:A$6)";
sheet.Range[2, 1].Formula = averageFormula;

// Add arithmetic formula
string calcFormula = "=1+2+3+4+5-6-7+8-9";
sheet.Range[3, 1].Formula = calcFormula;

workbook.SaveToFile("AddFormulasAndFunctions.xlsx", ExcelVersion.Version2016);
workbook.Dispose();
Enter fullscreen mode Exit fullscreen mode

Code breakdown:

  • Use the sheet.Range[row, column].Formula property to directly assign a formula string to a cell. The syntax is identical to native Excel formulas.
  • Formulas support both absolute references (e.g., $D$2) and relative references (e.g., F$2), consistent with Excel behavior.
  • You can add not only simple arithmetic operations but also built-in functions like AVERAGE, SUM, IF, and more.

Part 2: Reading Existing Formulas from Excel

In real-world business scenarios, we often need to parse Excel reports created by others to extract calculation logic for auditing, migration, or documentation. The following code demonstrates how to load an existing Excel file, iterate through all used cells, and identify those containing formulas.

using Spire.Xls;
using System.IO;
using System.Text;

Workbook workbook = new Workbook();
workbook.LoadFromFile("Formulas.xlsx");

Worksheet sheet = workbook.Worksheets[0];
StringBuilder sb = new StringBuilder();
CellRange usedRange = sheet.AllocatedRange;

foreach (CellRange cell in usedRange)
{
    if (cell.HasFormula)
    {
        string cellName = cell.RangeAddressLocal;
        string formula = cell.Formula;
        sb.AppendLine($"{cellName} contains formula: {formula}");
    }
}

File.WriteAllText("ReadFormulasAndFunctions.txt", sb.ToString());
Enter fullscreen mode Exit fullscreen mode

Code breakdown:

  • The cell.HasFormula property is a boolean value used to quickly determine whether a cell contains a formula.
  • cell.Formula returns the formula as a string, for example =AVERAGE(D2:F2).
  • Using AllocatedRange gets the range of used data in the worksheet, avoiding iteration over the entire blank worksheet and improving efficiency.
  • The extracted formula information is ultimately written to a text file for subsequent analysis or archiving.

Part 3: Practical Application Scenarios

By mastering the addition and reading of formulas, we can build many useful automation tools:

  1. Report Generator : Automatically writes raw data into Excel templates and dynamically adds statistical formulas like SUM, COUNTIF, etc.
  2. Formula Audit Tool : Batch-reads complex formulas in financial reports to verify their logic and prevent human errors.
  3. Formula Migration Assistant : Extracts formulas from older Excel files and applies them in batch to corresponding locations in new templates.
  4. Teaching Assistant System : Automatically grades Excel assignments submitted by students by reading formulas to determine whether the solution steps are correct.

Part 4: Notes and Recommendations

  • Formula strings must start with an equals sign =; otherwise, the component will treat them as plain text.
  • Cell ranges referenced in formulas must be valid. If referencing non-existent data, Excel may display a #REF! error when opening the file.
  • Free Spire.XLS is a free version but has certain page limitations (maximum of 5 pages per worksheet). This is sufficient for most small to medium-sized projects.
  • If you need to process very large files or remove the page limitation, consider upgrading to the commercial version Spire.XLS.

Conclusion

Through the sample code in this article, we can clearly see that using Free Spire.XLS for .NET to manipulate Excel formulas in C# is highly intuitive and efficient. Whether injecting complex calculation logic into spreadsheets or reverse-engineering existing formula structures, this component provides comprehensive and easy-to-use API interfaces. I hope this article helps you handle Excel formula-related development tasks more effectively in your real-world projects.

Top comments (0)