Formulas
Formulas (7)
The sample demonstrates how to set Excel subtotal formula via Spire.XLS.
using System.Drawing;
using Spire.Xls;
namespace Subtotal
{
class Program
{
static void Main(string[] args)
{
//create a workbook
Workbook workbook = new Workbook();
//load the workbook
workbook.LoadFromFile(@"..\..\..\..\Data\order.xls",ExcelVersion.Version97to2003);
Worksheet sheet = workbook.Worksheets[0];
//subtotal the count of order
sheet.Range["B18"].Text = "the count of order";
sheet.Range["B19"].Formula = "SUBTOTAL(2,order!B2:B17)";
//subtotal the minimum of Profits
sheet.Range["C18"].Text = "the minimum of Profits";
sheet.Range["C19"].Formula = "SUBTOTAL(5,order!C2:C17)";
//subtotal the sum of cost
sheet.Range["D18"].Text = "the sum of cost";
sheet.Range["D19"].Formula = "SUBTOTAL(9,order!D2:D17)";
sheet.AllocatedRange.AutoFitColumns();
sheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Center;
//set the style of new rows
sheet.Range["A18:D19"].Borders.LineStyle = LineStyleType.Thin;
sheet.Range["A18:D19"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
sheet.Range["A18:D19"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
sheet.Range["A18:D19"].Style.Color = Color.Bisque;
workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
System.Diagnostics.Process.Start(workbook.FileName);
}
}
}
Imports System.Drawing
Imports Spire.Xls
Namespace Subtotal
Friend Class Program
Shared Sub Main(ByVal args() As String)
'create a workbook
Dim workbook As New Workbook()
'load the workbook
workbook.LoadFromFile("..\..\..\..\Data\order.xls",ExcelVersion.Version97to2003)
Dim sheet As Worksheet = workbook.Worksheets(0)
'subtotal the count of order
sheet.Range("B18").Text = "the count of order"
sheet.Range("B19").Formula = "SUBTOTAL(2,order!B2:B17)"
'subtotal the minimum of Profits
sheet.Range("C18").Text = "the minimum of Profits"
sheet.Range("C19").Formula = "SUBTOTAL(5,order!C2:C17)"
'subtotal the sum of cost
sheet.Range("D18").Text = "the sum of cost"
sheet.Range("D19").Formula = "SUBTOTAL(9,order!D2:D17)"
sheet.AllocatedRange.AutoFitColumns()
sheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Center
'set the style of new rows
sheet.Range("A18:D19").Borders.LineStyle = LineStyleType.Thin
sheet.Range("A18:D19").Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None
sheet.Range("A18:D19").Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None
sheet.Range("A18:D19").Style.Color = Color.Bisque
workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
System.Diagnostics.Process.Start(workbook.FileName)
End Sub
End Class
End Namespace
Published in
Formulas
The sample demonstrates how to read data with fomulas
using System.IO;
using System.Windows.Forms;
using System;
using System.Data;
using Spire.Xls;
namespace DataReadWithFormulas
{
class Program
{
static void Main(string[] args)
{
DataTable dataTable = new DataTable();
using (Stream buffer = WriteFormulas())
{
//load
Workbook workbook = new Workbook();
workbook.LoadFromStream(buffer);
//calculate all cells' formula
workbook.CalculateAllValue();
//read cells' value to data table
Worksheet sheet = workbook.Worksheets[0];
dataTable.Columns.Add("Formulas", typeof(String));
dataTable.Columns.Add("Results", typeof(Object));
foreach (CellRange row in sheet["A5:B46"].Rows)
{
String formula = row.Columns[1].Formula;
Object value = row.Columns[1].FormulaValue;
dataTable.Rows.Add(formula, value);
}
}
using (Form frm1 = new Form())
{
DataGrid dataGrid = new DataGrid();
dataGrid.CaptionVisible = false;
dataGrid.ReadOnly = true;
dataGrid.DataSource = dataTable;
dataGrid.Dock = DockStyle.Fill;
frm1.Text = "Data Export with Formulas";
frm1.Width = 480;
frm1.Height = 360;
frm1.StartPosition = FormStartPosition.CenterParent;
frm1.Controls.Add(dataGrid);
frm1.ShowDialog();
}
}
private static Stream WriteFormulas()
{
//Create Excel files
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];//it's the first sheet of the worksheets
int currentRow = 1;
string currentFormula = string.Empty;
sheet.SetColumnWidth(1, 32);//SetColumnWidth(columnIndex,width);
sheet.SetColumnWidth(2, 16);
sheet.SetColumnWidth(3, 16);
sheet.Range[currentRow++, 1].Value = "Examples of formulas :";
sheet.Range[++currentRow, 1].Value = "Test data:";
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;
//test data
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;
sheet.Range[++currentRow, 1].Value = "Formulas"; ;
sheet.Range[currentRow, 2].Value = "Results";
range = sheet.Range[currentRow, 1, currentRow, 2];
//range.Value = "Formulas";
range.Style.Font.IsBold = true;
range.Style.KnownColor = ExcelColors.LightGreen1;
range.Style.FillPattern = ExcelPatternType.Solid;
range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;
//str.
currentFormula = "=\"hello\"";
sheet.Range[++currentRow, 1].Text = "=\"hello\"";
sheet.Range[currentRow, 2].Formula = currentFormula;
sheet.Range[currentRow, 3].Formula = "=\"\u4f60\u597d\"";
//int.
currentFormula = "=300";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// float
currentFormula = "=3389.639421";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
//bool.
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;
// sheet reference
currentFormula = "=Sheet1!$B$3";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// sheet area reference
currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// Functions
currentFormula = "=Count(3,5,8,10,2,34)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
currentFormula = "=NOW()";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
sheet.Range[currentRow, 2].Style.NumberFormat = "yyyy-MM-DD";
currentFormula = "=SECOND(11)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MINUTE(12)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MONTH(9)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=DAY(10)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=TIME(4,5,7)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=DATE(6,4,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=RAND()";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=HOUR(12)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MOD(5,3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=WEEKDAY(3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=YEAR(23)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=NOT(true)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=OR(true)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=AND(TRUE)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=VALUE(30)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=LEN(\"world\")";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MID(\"world\",4,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=ROUND(7,3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SIGN(4)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=INT(200)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=ABS(-1.21)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=LN(15)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=EXP(20)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SQRT(40)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=PI()";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=COS(9)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SIN(45)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MAX(10,30)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MIN(5,7)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=AVERAGE(12,45)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SUM(18,29)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=IF(4,2,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
MemoryStream buffer = new MemoryStream();
workbook.SaveToStream(buffer);
buffer.Position = 0;
return buffer;
}
}
}
Imports System.IO
Imports System.Windows.Forms
Imports Spire.Xls
Module Module1
Private Function WriteFormulas() As Stream
Dim workbook As Workbook = New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim currentRow As Integer = 1
Dim currentFormula As String = String.Empty
sheet.SetColumnWidth(1, 32)
sheet.SetColumnWidth(2, 16)
sheet.SetColumnWidth(3, 16)
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Value = "Examples of formulas :"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Value = "Test data:"
Dim range As CellRange = 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
'test data
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
currentRow = currentRow + 1
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
'str.
currentFormula = "=""hello"""
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = "=""hello"""
sheet.Range(currentRow, 2).Formula = currentFormula
sheet.Range(currentRow, 3).Formula = "=""" & ChrW(20320) & ChrW(22909) + """"
'int.
currentFormula = "=300"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' float
currentFormula = "=3389.639421"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
'bool.
currentFormula = "=false"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=1+2+3+4+5-6-7+8-9"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=33*3/4-2+10"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' sheet reference
currentFormula = "=Sheet1!$B$3"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' sheet area reference
currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' Functions
currentFormula = "=Count(3,5,8,10,2,34)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=NOW()"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
sheet.Range(currentRow, 2).Style.NumberFormat = "yyyy-MM-DD"
currentFormula = "=SECOND(11)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MINUTE(12)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MONTH(9)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=DAY(10)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=TIME(4,5,7)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=DATE(6,4,2)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=RAND()"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=HOUR(12)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MOD(5,3)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=WEEKDAY(3)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=YEAR(23)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=NOT(true)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=OR(true)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=AND(TRUE)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=VALUE(30)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=LEN(""world"")"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MID(""world"",4,2)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=ROUND(7,3)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SIGN(4)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=INT(200)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=ABS(-1.21)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=LN(15)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=EXP(20)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SQRT(40)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=PI()"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=COS(9)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SIN(45)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MAX(10,30)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MIN(5,7)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=AVERAGE(12,45)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SUM(18,29)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=IF(4,2,2)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
Dim buffer As New MemoryStream()
workbook.SaveToStream(buffer)
buffer.Position = 0
Return buffer
End Function
Sub Main()
Using buffer As Stream = WriteFormulas()
'load
Dim workbook As New Workbook()
workbook.LoadFromStream(buffer)
'calculate formula
Dim b3 As [Object] = workbook.CaculateFormulaValue("Sheet1!$B$3")
Dim c3 As [Object] = workbook.CaculateFormulaValue("Sheet1!$C$3")
Dim formula As [String] = "Sheet1!$B$3 + Sheet1!$C$3"
Dim value As [Object] = workbook.CaculateFormulaValue(formula)
Dim message As [String] = [String].Format("Sheet1!$B$3 = {0}, Sheet1!$C$3 = {1}, {2} = {3}", b3, c3, formula, value)
MessageBox.Show(message)
End Using
End Sub
End Module
The sample demonstrates how to calculate formulas
using System;
using System.IO;
using System.Windows.Forms;
using Spire.Xls;
namespace DataCalculateWithFormulas
{
class Program
{
static void Main(string[] args)
{
using (Stream buffer = WriteFormulas())
{
//load
Workbook workbook = new Workbook();
workbook.LoadFromStream(buffer);
//calculate formula
Object b3 = workbook.CaculateFormulaValue("=Sheet1!$B$3");
Object c3 = workbook.CaculateFormulaValue("=Sheet1!$C$3");
String formula = "=Sheet1!$B$3 + Sheet1!$C$3";
Object value = workbook.CaculateFormulaValue(formula);
String message
= String.Format("Sheet1!$B$3 = {0}, Sheet1!$C$3 = {1}, {2} = {3}", b3, c3, formula.Substring(1), value);
MessageBox.Show(message, "Calculate Formulas");
}
}
private static Stream WriteFormulas()
{
//Create Excel files
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];//it's the first sheet of the worksheets
int currentRow = 1;
string currentFormula = string.Empty;
sheet.SetColumnWidth(1, 32);//SetColumnWidth(columnIndex,width);
sheet.SetColumnWidth(2, 16);
sheet.SetColumnWidth(3, 16);
sheet.Range[currentRow++, 1].Value = "Examples of formulas :";
sheet.Range[++currentRow, 1].Value = "Test data:";
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;
//test data
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;
MemoryStream buffer = new MemoryStream();
workbook.SaveToStream(buffer);
buffer.Position = 0;
return buffer;
}
}
}
Imports System.IO
Imports System.Windows.Forms
Imports Spire.Xls
Module Module1
Private Function WriteFormulas() As Stream
Dim workbook As Workbook = New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim currentRow As Integer = 1
Dim currentFormula As String = String.Empty
sheet.SetColumnWidth(1, 32)
sheet.SetColumnWidth(2, 16)
sheet.SetColumnWidth(3, 16)
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Value = "Examples of formulas :"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Value = "Test data:"
Dim range As CellRange = 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
'test data
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
Dim buffer As New MemoryStream()
workbook.SaveToStream(buffer)
buffer.Position = 0
Return buffer
End Function
Sub Main()
Using buffer As Stream = WriteFormulas()
'load
Dim workbook As New Workbook()
workbook.LoadFromStream(buffer)
'calculate formula
Dim b3 As Object = workbook.CaculateFormulaValue("=Sheet1!$B$3")
Dim c3 As Object = workbook.CaculateFormulaValue("=Sheet1!$C$3")
Dim formula As String = "=Sheet1!$B$3 + Sheet1!$C$3"
Dim value As Object = workbook.CaculateFormulaValue(formula)
Dim message As String = _
String.Format("Sheet1!$B$3 = {0}, Sheet1!$C$3 = {1}, {2} = {3}", b3, c3, formula.Substring(1), value)
MessageBox.Show(message, "Calculate Formulas")
End Using
End Sub
End Module
The sample demonstrates how to export data to datatable with calculating formulas
using System.Data;
using System.IO;
using System.Windows.Forms;
using Spire.Xls;
namespace DataExportWithFormulas
{
class Program
{
static void Main(string[] args)
{
DataTable dataTable = null;
using (Stream buffer = WriteFormulas())
{
//load
Workbook workbook = new Workbook();
//load the file and import the data
workbook.LoadFromStream(buffer); //import the data of the buffer into the excel file;
//calculate all cells
workbook.CalculateAllValue();
//export
Worksheet sheet = workbook.Worksheets[0];
dataTable = sheet.ExportDataTable(sheet["A4:B46"], true, true);
}
using (Form frm1 = new Form())
{
DataGrid dataGrid = new DataGrid();
dataGrid.CaptionVisible = false;
dataGrid.ReadOnly = true;
dataGrid.DataSource = dataTable;
dataGrid.Dock = DockStyle.Fill;
frm1.Text = "Data Export with Formulas";
frm1.Width = 480;
frm1.Height = 360;
frm1.StartPosition = FormStartPosition.CenterParent;
frm1.Controls.Add(dataGrid);
frm1.ShowDialog();
}
}
private static Stream WriteFormulas()
{
//Create Excel files
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];//it's the first sheet of the worksheets
int currentRow = 1;
string currentFormula = string.Empty;
sheet.SetColumnWidth(1, 32);//SetColumnWidth(columnIndex,width);
sheet.SetColumnWidth(2, 16);
sheet.SetColumnWidth(3, 16);
sheet.Range[currentRow++, 1].Value = "Examples of formulas :";
sheet.Range[++currentRow, 1].Value = "Test data:";
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;
//test data
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;
sheet.Range[++currentRow, 1].Value = "Formulas"; ;
sheet.Range[currentRow, 2].Value = "Results";
range = sheet.Range[currentRow, 1, currentRow, 2];
//range.Value = "Formulas";
range.Style.Font.IsBold = true;
range.Style.KnownColor = ExcelColors.LightGreen1;
range.Style.FillPattern = ExcelPatternType.Solid;
range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;
//str.
currentFormula = "=\"hello\"";
sheet.Range[++currentRow, 1].Text = "=\"hello\"";
sheet.Range[currentRow, 2].Formula = currentFormula;
sheet.Range[currentRow, 3].Formula = "=\"\u4f60\u597d\"";
//int.
currentFormula = "=300";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// float
currentFormula = "=3389.639421";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
//bool.
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;
// sheet reference
currentFormula = "=Sheet1!$B$3";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// sheet area reference
currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// Functions
currentFormula = "=Count(3,5,8,10,2,34)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
currentFormula = "=NOW()";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
sheet.Range[currentRow, 2].Style.NumberFormat = "yyyy-MM-DD";
currentFormula = "=SECOND(11)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MINUTE(12)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MONTH(9)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=DAY(10)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=TIME(4,5,7)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=DATE(6,4,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=RAND()";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=HOUR(12)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MOD(5,3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=WEEKDAY(3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=YEAR(23)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=NOT(true)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=OR(true)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=AND(TRUE)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=VALUE(30)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=LEN(\"world\")";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MID(\"world\",4,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=ROUND(7,3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SIGN(4)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=INT(200)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=ABS(-1.21)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=LN(15)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=EXP(20)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SQRT(40)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=PI()";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=COS(9)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SIN(45)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MAX(10,30)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MIN(5,7)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=AVERAGE(12,45)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SUM(18,29)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=IF(4,2,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
MemoryStream buffer = new MemoryStream();
workbook.SaveToStream(buffer);
buffer.Position = 0;
return buffer;
}
}
}
Imports System.IO
Imports Spire.Xls
Imports System.Windows.Forms
Module Module1
Private Function WriteFormulas() As Stream
Dim workbook As Workbook = New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim currentRow As Integer = 1
Dim currentFormula As String = String.Empty
sheet.SetColumnWidth(1, 32)
sheet.SetColumnWidth(2, 16)
sheet.SetColumnWidth(3, 16)
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Value = "Examples of formulas :"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Value = "Test data:"
Dim range As CellRange = 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
'test data
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
currentRow = currentRow + 1
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
'str.
currentFormula = "=""hello"""
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = "=""hello"""
sheet.Range(currentRow, 2).Formula = currentFormula
sheet.Range(currentRow, 3).Formula = "=""" & ChrW(20320) & ChrW(22909) + """"
'int.
currentFormula = "=300"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' float
currentFormula = "=3389.639421"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
'bool.
currentFormula = "=false"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=1+2+3+4+5-6-7+8-9"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=33*3/4-2+10"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' sheet reference
currentFormula = "=Sheet1!$B$3"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' sheet area reference
currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' Functions
currentFormula = "=Count(3,5,8,10,2,34)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=NOW()"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
sheet.Range(currentRow, 2).Style.NumberFormat = "yyyy-MM-DD"
currentFormula = "=SECOND(11)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MINUTE(12)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MONTH(9)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=DAY(10)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=TIME(4,5,7)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=DATE(6,4,2)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=RAND()"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=HOUR(12)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MOD(5,3)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=WEEKDAY(3)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=YEAR(23)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=NOT(true)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=OR(true)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=AND(TRUE)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=VALUE(30)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=LEN(""world"")"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MID(""world"",4,2)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=ROUND(7,3)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SIGN(4)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=INT(200)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=ABS(-1.21)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=LN(15)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=EXP(20)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SQRT(40)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=PI()"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=COS(9)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SIN(45)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MAX(10,30)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MIN(5,7)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=AVERAGE(12,45)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SUM(18,29)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=IF(4,2,2)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
Dim buffer As New MemoryStream()
workbook.SaveToStream(buffer)
buffer.Position = 0
Return buffer
End Function
Sub Main()
Dim dataTable As DataTable
Using buffer As Stream = WriteFormulas()
'load
Dim workbook As New Workbook()
workbook.LoadFromStream(buffer)
'calculate all cells
workbook.CalculateAllValue()
'export
Dim sheet As Worksheet = workbook.Worksheets(0)
dataTable = sheet.ExportDataTable(sheet("A4:B46"), True, True)
End Using
Using frm1 As New Form()
Dim dataGrid As New DataGrid()
dataGrid.CaptionVisible = False
dataGrid.ReadOnly = True
dataGrid.DataSource = dataTable
dataGrid.Dock = DockStyle.Fill
frm1.Text = "Data Export with Formulas"
frm1.Width = 480
frm1.Height = 360
frm1.StartPosition = FormStartPosition.CenterParent
frm1.Controls.Add(dataGrid)
frm1.ShowDialog()
End Using
End Sub
End Module
Published in
Formulas
Tagged under
The sample demonstrates how to define named cell references or ranges in excel workbook.
using System;
using Spire.Xls;
using System.Drawing;
namespace Names
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\MiscDataTable.xls");
Worksheet sheet = workbook.Worksheets[0];
sheet.InsertRow(1, 2);
sheet.Rows[0].RowHeight = 16;
//Style
sheet.Range["A1:B1"].Style.Font.IsBold = true;
sheet.Range["A3:E3"].Style.KnownColor = ExcelColors.LightOrange;
//set the color of le event/odd row color
int index = 4;
while (sheet.Range[String.Format("A{0}", index)].HasString)
{
sheet.Range[String.Format("A{0}:E{0}", index)].Style.KnownColor
= index % 2 == 0 ? ExcelColors.PaleBlue : ExcelColors.LightTurquoise;
index++;
}
//define named cell ranges
sheet.Names.Add("Countries", sheet[String.Format("A4:A{0}", index - 1)]);
sheet.Names.Add("Cities", sheet[String.Format("B4:B{0}", index - 1)]);
sheet.Names.Add("Continents", sheet[String.Format("C4:C{0}", index - 1)]);
sheet.Names.Add("Area", sheet[String.Format("D4:D{0}", index - 1)]);
sheet.Names.Add("Population", sheet[String.Format("E4:E{0}", index - 1)]);
sheet.Names.Add("NumberOfCountries", sheet[String.Format("A{0}", index)]);
//references of names
sheet.Range["A1"].Value = "Number of Countries:";
sheet.Range["B1"].Formula = "=NumberOfCountries";
sheet[String.Format("A{0}", index)].Formula = "=COUNTA(Countries)";
sheet[String.Format("D{0}", index)].Formula = "=SUM(Area)";
sheet[String.Format("E{0}", index)].Formula = "=SUM(Population)";
//style
sheet.Rows[index - 1].RowHeight = 16;
String range = String.Format("A{0}:E{0}", index);
sheet.Range[range].Style.Font.IsBold = true;
sheet.Range[range].Style.KnownColor = ExcelColors.LightOrange;
sheet.Range[range].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 0);
sheet.Range[range].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thick;
sheet.Range[range].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 0);
sheet.Range[range].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
sheet.Range[range].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 0);
sheet.Range[range].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
sheet.Range[range].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 0);
sheet.Range[range].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
workbook.SaveToFile("Sample.xls");
System.Diagnostics.Process.Start(workbook.FileName);
}
}
}
Imports Spire.Xls
Imports System.Drawing
Module Module1
Sub Main()
Dim workbook As New Spire.Xls.Workbook()
workbook.LoadFromFile("..\..\MiscDataTable.xls")
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.InsertRow(1, 2)
sheet.Rows(0).RowHeight = 16
'Style
sheet.Range("A1:B1").Style.Font.IsBold = True
sheet.Range("A3:E3").Style.KnownColor = ExcelColors.LightOrange
Dim index As Integer = 4
While sheet.Range([String].Format("A{0}", index)).HasString
sheet.Range([String].Format("A{0}:E{0}", index)).Style.KnownColor _
= If(index Mod 2 = 0, ExcelColors.PaleBlue, ExcelColors.LightTurquoise)
index += 1
End While
'define named cell ranges
sheet.Names.Add("Countries", sheet([String].Format("A4:A{0}", index - 1)))
sheet.Names.Add("Cities", sheet([String].Format("B4:B{0}", index - 1)))
sheet.Names.Add("Continents", sheet([String].Format("C4:C{0}", index - 1)))
sheet.Names.Add("Area", sheet([String].Format("D4:D{0}", index - 1)))
sheet.Names.Add("Population", sheet([String].Format("E4:E{0}", index - 1)))
sheet.Names.Add("NumberOfCountries", sheet([String].Format("A{0}", index)))
'references of names
sheet.Range("A1").Value = "Number of Countries:"
sheet.Range("B1").Formula = "=NumberOfCountries"
sheet([String].Format("A{0}", index)).Formula = "=COUNTA(Countries)"
sheet([String].Format("D{0}", index)).Formula = "=SUM(Area)"
sheet([String].Format("E{0}", index)).Formula = "=SUM(Population)"
'style
sheet.Rows(index - 1).RowHeight = 16
Dim range As [String] = [String].Format("A{0}:E{0}", index)
sheet.Range(range).Style.Font.IsBold = True
sheet.Range(range).Style.KnownColor = ExcelColors.LightOrange
sheet.Range(range).Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 0)
sheet.Range(range).Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thick
sheet.Range(range).Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 0)
sheet.Range(range).Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
sheet.Range(range).Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 0)
sheet.Range(range).Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
sheet.Range(range).Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 0)
sheet.Range(range).Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
workbook.SaveToFile("Sample.xls")
System.Diagnostics.Process.Start(workbook.FileName)
End Sub
End Module
The sample demonstrates how to write formulas into spreadsheet..
private void ExcelDocViewer( string fileName )
{
try
{
System.Diagnostics.Process.Start(fileName);
}
catch{}
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
int currentRow = 1;
string currentFormula = string.Empty;
sheet.SetColumnWidth(1, 32);
sheet.SetColumnWidth(2, 16);
sheet.SetColumnWidth(3, 16);
sheet.Range[currentRow++,1].Value = "Examples of formulas :";
sheet.Range[++currentRow,1].Value = "Test data:";
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;
//test data
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;
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;
//str.
currentFormula = "=\"hello\"";
sheet.Range[++currentRow, 1].Text = "=\"hello\"";
sheet.Range[currentRow, 2].Formula = currentFormula;
sheet.Range[currentRow, 3].Formula = "=\"" + new string(new char[] { '\u4f60', '\u597d' }) + "\"";
//int.
currentFormula = "=300";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// float
currentFormula = "=3389.639421";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
//bool.
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;
// sheet reference
currentFormula = "=Sheet1!$B$3";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// sheet area reference
currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// Functions
currentFormula = "=Count(3,5,8,10,2,34)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
currentFormula = "=NOW()";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
sheet.Range[currentRow, 2].Style.NumberFormat = "yyyy-MM-DD";
currentFormula = "=SECOND(11)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MINUTE(12)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MONTH(9)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=DAY(10)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=TIME(4,5,7)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=DATE(6,4,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=RAND()";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=HOUR(12)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MOD(5,3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=WEEKDAY(3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=YEAR(23)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=NOT(true)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=OR(true)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=AND(TRUE)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=VALUE(30)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=LEN(\"world\")";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MID(\"world\",4,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=ROUND(7,3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SIGN(4)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=INT(200)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=ABS(-1.21)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=LN(15)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=EXP(20)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SQRT(40)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=PI()";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=COS(9)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SIN(45)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MAX(10,30)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MIN(5,7)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=AVERAGE(12,45)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SUM(18,29)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=IF(4,2,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SUBTOTAL(3,Sheet1!B2:E3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
Private Sub ExcelDocViewer(ByVal fileName As String)
Try
System.Diagnostics.Process.Start(fileName)
Catch
End Try
End Sub
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim workbook As Workbook = New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim currentRow As Integer = 1
Dim currentFormula As String = String.Empty
sheet.SetColumnWidth(1, 32)
sheet.SetColumnWidth(2, 16)
sheet.SetColumnWidth(3, 16)
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Value = "Examples of formulas :"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Value = "Test data:"
Dim range As CellRange = 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
'test data
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
currentRow = currentRow + 1
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
'str.
currentFormula = "=""hello"""
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = "=""hello"""
sheet.Range(currentRow, 2).Formula = currentFormula
sheet.Range(currentRow, 3).Formula = "=""" & ChrW(20320) & ChrW(22909) + """"
'int.
currentFormula = "=300"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' float
currentFormula = "=3389.639421"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
'bool.
currentFormula = "=false"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=1+2+3+4+5-6-7+8-9"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=33*3/4-2+10"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' sheet reference
currentFormula = "=Sheet1!$B$3"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' sheet area reference
currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' Functions
currentFormula = "=Count(3,5,8,10,2,34)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=NOW()"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
sheet.Range(currentRow, 2).Style.NumberFormat = "yyyy-MM-DD"
currentFormula = "=SECOND(11)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
'currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MINUTE(12)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MONTH(9)"
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=DAY(10)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=TIME(4,5,7)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=DATE(6,4,2)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=RAND()"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=HOUR(12)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MOD(5,3)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=WEEKDAY(3)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=YEAR(23)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=NOT(true)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=OR(true)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=AND(TRUE)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=VALUE(30)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=LEN(""world"")"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MID(""world"",4,2)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=ROUND(7,3)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SIGN(4)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=INT(200)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=ABS(-1.21)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=LN(15)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=EXP(20)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SQRT(40)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=PI()"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=COS(9)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SIN(45)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MAX(10,30)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MIN(5,7)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=AVERAGE(12,45)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SUM(18,29)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=IF(4,2,2)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SUBTOTAL(3,Sheet1!B2:E3)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
workbook.SaveToFile("Sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub
The sample demonstrates how to read formulas from spreadsheet.
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\..\..\..\..\Data\ReadFormulasSmple.xls");
Worksheet sheet = workbook.Worksheets[0];
textBox1.Text = sheet.Range["C5"].Formula;
textBox2.Text = sheet.Range["C5"].FormulaNumberValue.ToString();
}
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim workbook As Workbook = New Workbook()
workbook.LoadFromFile("..\..\..\..\..\..\Data\ReadFormulasSmple.xls")
Dim sheet As Worksheet = workbook.Worksheets(0)
textBox1.Text = sheet.Range("C5").Formula
textBox2.Text = sheet.Range("C5").FormulaNumberValue.ToString()
End Sub
page
