C reate and read Excel formula

Keywords: C# Excel

For tables with large amount of data, when we need to calculate some special values, we can effectively improve the speed and efficiency of our data processing by using formulas, and it is also very convenient for batch operations such as adding, deleting, modifying and querying later data. In addition, for the information source of some values, we can also get it by reading the formula contained in the data. The following example will share the method of creating and reading Excel formula through C ා.

Tool usage

Spire.XLS for .NET 8.0

After downloading and installing the class library, pay attention to adding the reference Spire.Xls.dll in the program (the DLL file can be obtained in the Bin folder under the installation path)

Code example (for reference)

[example 1] create Excel formula

Step 1: create a new workbook

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

Step 2: add test data and text, and set text format, etc

//Initialization currentRow,currentFormula
int currentColumn = 1;
int currentRow = 1;
string currentFormula = string.Empty;

//Set column width of 1 and 2 columns
sheet.SetColumnWidth(1, 20);
sheet.SetColumnWidth(2, 12);

//Write test data
sheet.Range[currentColumn, 1].Value = "Test data:";
sheet.Range[currentColumn, 2].NumberValue = 10;
sheet.Range[currentColumn, 3].NumberValue = 20; 
sheet.Range[currentColumn, 4].NumberValue = 30;
sheet.Range[currentColumn, 5].NumberValue = 40;
sheet.Range[currentColumn, 6].NumberValue = 50;

//Write text and format area
currentRow += 2;
sheet.Range[currentRow, 1].Value = "formula"; 
sheet.Range[currentRow, 2].Value = "Result";
CellRange 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 3: write function

//Arithmetic operation
currentFormula = "=1/2+3*4";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;

//Date function
currentFormula = "=Today()";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
sheet.Range[currentRow, 2].Style.NumberFormat = "YYYY/MM/DD";

//Time function
currentFormula = "=NOW()";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
sheet.Range[currentRow, 2].Style.NumberFormat = "H:MM AM/PM";

//IF Logic function
currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;

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

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

//Counting function
currentFormula = "=Count(B1:F1)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;

//Find the maximum function
currentFormula = "=MAX(B1:F1)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;

//Average function
currentFormula = "=AVERAGE(B1:F1)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;

//Summation function
currentFormula = "=SUM(B1:F1)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;

Step 4: save the document

workbook.SaveToFile("Excel formula.xlsx", FileFormat.Version2013);
System.Diagnostics.Process.Start("Excel formula.xlsx");

After the code is completed, debug and run the program to generate a document:

All codes:

using Spire.Xls;

namespace CreateFormula
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a new workbook, get the first sheet
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];

            //Initialization currentRow,currentFormula
            int currentColumn = 1;
            int currentRow = 1;
            string currentFormula = string.Empty;

            //Set column width of 1 and 2 columns
            sheet.SetColumnWidth(1, 20);
            sheet.SetColumnWidth(2, 12);

            //Write test data
            sheet.Range[currentColumn, 1].Value = "Test data:";
            sheet.Range[currentColumn, 2].NumberValue = 10;
            sheet.Range[currentColumn, 3].NumberValue = 20; 
            sheet.Range[currentColumn, 4].NumberValue = 30;
            sheet.Range[currentColumn, 5].NumberValue = 40;
            sheet.Range[currentColumn, 6].NumberValue = 50;

            //Write text and format area
            currentRow += 2;
            sheet.Range[currentRow, 1].Value = "formula"; 
            sheet.Range[currentRow, 2].Value = "Result";
            CellRange 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;

            //Arithmetic operation
            currentFormula = "=1/2+3*4";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //Date function
            currentFormula = "=Today()";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;
            sheet.Range[currentRow, 2].Style.NumberFormat = "YYYY/MM/DD";

            //Time function
            currentFormula = "=NOW()";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;
            sheet.Range[currentRow, 2].Style.NumberFormat = "H:MM AM/PM";

            //IF Logic function
            currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

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

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

            //Counting function
            currentFormula = "=Count(B1:F1)";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //Find the maximum function
            currentFormula = "=MAX(B1:F1)";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //Average function
            currentFormula = "=AVERAGE(B1:F1)";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //Summation function
            currentFormula = "=SUM(B1:F1)";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //Save document and open
            workbook.SaveToFile("Excel formula.xlsx", FileFormat.Version2013);
            System.Diagnostics.Process.Start("Excel formula.xlsx");
        }
    }
}

[example 2] Reading Excel formula

Step 1: instantiate the Workbook class and load the test document

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

Step 2: get the worksheet

workbook.LoadFromFile("test.xlsx");

Step 3: read formula

//ergodic[B1: B13]Cell
foreach (var cell in sheet.Range["B1:B13"])
{
    //Determine whether formula is included
    if (cell.HasFormula)
    {
        //Output cells and formulas with formulas
        string certainCell = String.Format("Cell[{0},{1}]", cell.Row, cell.Column);
        Console.WriteLine(certainCell + " Formula included: " + cell.Formula);
    }
}
Console.ReadLine();

Formula read result:

All codes:

using Spire.Xls;
using System;

namespace ReadFormula
{
    class Program
    {
        static void Main(string[] args)
        {
            //item base  Workbook
            Workbook workbook = new Workbook();

            //Load test document
            workbook.LoadFromFile("test.xlsx");

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

            //ergodic[B1: B13]Cell
            foreach (var cell in sheet.Range["B1:B13"])
            {
                //Determine whether formula is included
                if (cell.HasFormula)
                {
                    //Output cells and formulas with formulas
                    string certainCell = String.Format("Cell[{0},{1}]", cell.Row, cell.Column);
                    Console.WriteLine(certainCell + " Formula included: " + cell.Formula);
                }
            }
            Console.ReadLine();
        }
    }
}

The above is the whole content of this time about "creating and reading Excel formula by C".

(end of this paper)

Posted by Eng65 on Wed, 25 Dec 2019 07:26:54 -0800

Programmer Group

C reate and read Excel formula

Tool usage

Code example (for reference)

[example 1] create Excel formula

[example 2] Reading Excel formula

Hot Keywords