The editing of Excel formulas in C#/VB.NET without MS Excel installed.

March 6, 2013 - PRLog -- Microsoft Excel is the industry standard for spreadsheet software and it gives the ability to analyze large amounts of data to discover trends and patterns that will influence decisions. MS Excel can create reports with full formatting, which include graphics, text, hyperlinks, etc. Reports can automatically analyze data, it requires only enter data and formulas for treatment. Next, you can only change the input data and Excel will automatically calculate all data for a given algorithm. Microsoft Excel is a very versatile tool and can be used for many different purposes: report, calendar, schedule, financial tools, pricelist, budget and etc.

Formula is a core of excel automation and allows to organize a complex applications. The easiest and fastest way to work with formula from C#/VB.NET/ASP.NET project is to use third-party components, like Excel .NET introduced by Elerium Software. Elerium Excel .NET component can create, read and calculate formulas.

First off all a developer must install Excel .NET to the project:
1. Download the latest version of the component from this link: http://eleriumsoft.com/Excel_NET/ExcelNET/Download.aspx.
2. Extract the downloaded archive and put the Excel.dll component into /bin folder of the project.
3. Add the component to the “using” section:
using Docs.Excel;

When the component is connected to the project, a developer can create/open excel files and work with its content, including formulas:

using System;
using System.Collections.Generic;
using System.Text;
using Docs.Excel;
namespace Formula
{
class Program
{
static void Main(string[] args)
{
//Create a new workbook.
ExcelWorkbook Wbook = new ExcelWorkbook();
//Add new worksheet to workbook.
Wbook.Worksheets.Add("Sheet1");
//Set width of columns.
Wbook.Worksheets[0].Columns[0].Width = 200;
Wbook.Worksheets[0].Columns[1].Width = 15;
Wbook.Worksheets[0].Columns[2].Width = 90;
//Column headings.
Wbook.Worksheets[0].Cells[0, 0].Value = "Formula";
Wbook.Worksheets[0].Cells[0, 2].Value = "Result";
//Writes formulas.
Wbook.Worksheets[0].Cells[1, 0].Value = "7+3=";
Wbook.Worksheets[0].Cells[1, 2].Value = "=7+3";
Wbook.Worksheets[0].Cells[2, 0].Value = "(2*2^2)%=";
Wbook.Worksheets[0].Cells[2, 2].Value = "=(2*2^2)%";
Wbook.Worksheets[0].Cells[3, 0].Value = "SUM(SIN(3);TAN(4);LOG(5;5))=";
Wbook.Worksheets[0].Cells[3, 2].Value = "=SUM(SIN(3);TAN(3);LOG(5;5))";
Wbook.Worksheets[0].Cells[4, 0].Value = "CEILING(12.35;0.1)=";
Wbook.Worksheets[0].Cells[4, 2].Value = "=CEILING(12.35;0.1)";
Wbook.Worksheets[0].Cells[5, 0].Value = "PI()=";
Wbook.Worksheets[0].Cells[5, 2].Value = "=PI()";
Wbook.Worksheets[0].Cells[6, 0].Value = "TODAY()=";
Wbook.Worksheets[0].Cells[6, 2].Value = "=TODAY()";
Wbook.Worksheets[0].Cells[6, 2].Style.StringFormat = "MMM-DD-YYYY";
Wbook.Worksheets[0].Cells[7, 0].Value = "TRUE()=";
Wbook.Worksheets[0].Cells[7, 2].Value = "=TRUE()";
// XSL
Wbook.WriteXLS(@"..\..\..\ExcelFormula.xls");
// XSLX
Wbook.WriteXLSX(@"..\..\..\ExcelFormula.xlsx");
//Open specified file in MS Excel.
System.Diagnostics.Process.Start(@"..\..\..\ExcelFormula.xlsx");
}
}
}

Supported Excel formulas:

1. Function of Date & Time: DATE / DATEVALUE / DAY / HOUR / MINUTE / MONTH / NOW / SECOND / TIME / TIMEVALUE / TODAY / WEEKDAY /YEAR
2. Financial function: SLN / SYD
3. Logical function: AND / FALSE / IF / NOT / OR / TRUE
4. Text function: CHAR / CODE / CONCATENATE / DOLLAR / EXACT / FIXED / LEFT / LEN / LOWER / MID / PROPER / REPLACE / REPT / RIGHT / SUBSTITUTE / TEXT / TRIM / UPPER / VALUE
5. Math & Trig function: ABS / ACOS / ASIN / ATAN / ATAN2 / CEILING / COMBIN / COS / COSH / DEGREES / EVEN / EXP / FACT / FLOOR / INT / LN / LOG / LOG10 /MOD / ODD / PI / POWER / PRODUCT / RADIANS / RAND / ROUND / ROUNDDOWN / ROUNDUP / SIGN / SIN / SINH / SQRT / SUM / SUMSQ / TAN / TANH / TRUNC /
6. Statistical function: AVERAGE / COUNT / MAX / MIN

About Elerium Software

Elerium Software develops professional solutions for use in .NET projects (C#, VB.NET, ASP.NET) that aimed to read/write/convert different office/web documents and formats. Elerium Software components are based on the unique design and fast algorithms that allow to be independent from the third-party applications and libraries.

For more information about the component please visit the product page:
http://www.eleriumsoft.com/Excel_NET/ExcelNET