Friday, 09 September 2011 03:37
Excel DropDownList Sample
The sample demonstrates how to provide a dropdownlist for valid data
using System;
using Spire.Xls;
namespace DropDownList
{
class Program
{
private static void CreateSampleData(Worksheet sheet)
{
sheet.Range["D8"].Text = "Country";
//set the datasource of the E8
sheet.Range["X1"].Text = "Lucy";
sheet.Range["X2"].Text = "Bolivia";
sheet.Range["X3"].Text = "Brazil";
sheet.Range["X4"].Text = "Canada";
sheet.Range["X5"].Text = "Chile";
sheet.Range["X6"].Text = "Colombia";
sheet.Range["X7"].Text = "Cuba";
sheet.Range["X8"].Text = "Ecuador";
sheet.Range["X9"].Text = " Salvado";
sheet.Range["X10"].Text = "Guyana";
sheet.Range["X11"].Text = "Jamaicatates";
sheet.Range["X12"].Text = "Mexico";
sheet.Range["X13"].Text = "Nicaragua";
sheet.Range["X14"].Text = "Paraguay";
sheet.Range["X15"].Text = "Peru";
sheet.Range["X16"].Text = "United";
sheet.Range["X17"].Text = "Uruguay";
sheet.Range["X18"].Text = "Venezuel";
CellRange rangeName = sheet.Range["E8"];
rangeName.DataValidation.AllowType = CellDataType.Formula;
rangeName.DataValidation.DataRange = sheet.Range["X1:X18"];
rangeName.DataValidation.IgnoreBlank = true;
rangeName.Activate();
//hide column X
sheet.HideColumn(sheet.Range["X1"].Column);
sheet.Range["D10"].Text = "Capital";
//set the datasource of the E9
sheet.Range["Y1"].Text = "Buenos Aires";
sheet.Range["Y2"].Text = "La Paz";
sheet.Range["Y3"].Text = "Brasilia";
sheet.Range["Y4"].Text = "Ottawa";
sheet.Range["Y5"].Text = "Santiago";
sheet.Range["Y6"].Text = "Bagota";
sheet.Range["Y7"].Text = "Havana";
sheet.Range["Y8"].Text = "Quito";
sheet.Range["Y9"].Text = "San Salvador";
sheet.Range["Y10"].Text = "Georgetown";
sheet.Range["Y11"].Text = "Kingston";
sheet.Range["Y12"].Text = "Mexico City";
sheet.Range["Y13"].Text = "Managua";
sheet.Range["Y14"].Text = "Asuncion";
sheet.Range["Y15"].Text = "Lima";
sheet.Range["Y16"].Text = "Washington";
sheet.Range["Y17"].Text = "Montevideo";
sheet.Range["Y18"].Text = "Caracas";
//Binding the datasource
CellRange rangeCapital = sheet.Range["E10"];
rangeCapital.DataValidation.AllowType = CellDataType.Formula;
rangeCapital.DataValidation.DataRange = sheet.Range["Y1:Y18"];
rangeCapital.DataValidation.IsSuppressDropDownArrow = false;
rangeCapital.Activate();
//hide coulumn Y
sheet.HideColumn(sheet.Range["Y1"].Column);
sheet.Range["D12"].Text = "Continent";
//set the datasource of the F9
CellRange rangeContinent = sheet.Range["E12"];
rangeContinent.DataValidation.AllowType = CellDataType.Formula;
rangeContinent.DataValidation.Values = new String[] { "South America", "North America" };
rangeContinent.DataValidation.IsSuppressDropDownArrow = false;
rangeContinent.Activate();
//Set the column's width
sheet.Range["E8"].Text = "Please select the country";
sheet.Range["E10"].Text = "Please select the capital";
sheet.Range["E12"].Text = "Please select the con tinent";
sheet.AutoFitColumn(4);
sheet.AutoFitColumn(5);
//Style
sheet.Range["D8:D12"].Style.Font.IsBold = true;
sheet.Range["D8"].Style.KnownColor = ExcelColors.Color36;
sheet.Range["D10"].Style.KnownColor = ExcelColors.Color36;
sheet.Range["D12"].Style.KnownColor = ExcelColors.Color36;
sheet.Range["E8"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["E10"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["E12"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.SetRowHeight(8, 16);
sheet.SetRowHeight(10, 16);
sheet.SetRowHeight(12, 16);
}
static void Main(string[] args)
{
//Build the workbook
Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.GridLinesVisible = false;
//Write sample data
CreateSampleData(sheet);
workbook.SaveToFile("test.xls");
System.Diagnostics.Process.Start(workbook.FileName);
}
}
}
Imports Spire.Xls
Module Module1
Private Sub CreateSampleData(ByVal sheet As Worksheet)
sheet.Range("D8").Text = "Country"
'Set the datasource of D9
sheet.Range("X1").Text = "Lucy"
sheet.Range("X2").Text = "Bolivia"
sheet.Range("X3").Text = "Brazil"
sheet.Range("X4").Text = "Canada"
sheet.Range("X5").Text = "Chile"
sheet.Range("X6").Text = "Colombia"
sheet.Range("X7").Text = "Cuba"
sheet.Range("X8").Text = "Ecuador"
sheet.Range("X9").Text = " Salvado"
sheet.Range("X10").Text = "Guyana"
sheet.Range("X11").Text = "Jamaicatates"
sheet.Range("X12").Text = "Mexico"
sheet.Range("X13").Text = "Nicaragua"
sheet.Range("X14").Text = "Paraguay"
sheet.Range("X15").Text = "Peru"
sheet.Range("X16").Text = "United"
sheet.Range("X17").Text = "Uruguay"
sheet.Range("X18").Text = "Venezue"
'Binding the datasource of D9
Dim rangeCountry As CellRange = sheet.Range("E8")
rangeCountry.DataValidation.AllowType = CellDataType.Formula
rangeCountry.DataValidation.DataRange = sheet.Range("X1:X18")
rangeCountry.DataValidation.IsSuppressDropDownArrow = False
rangeCountry.DataValidation.IgnoreBlank = True
rangeCountry.Activate()
sheet.HideColumn(sheet.Range("X1").Column)
sheet.Range("D10").Text = "Capital"
'Set the datasource of E9
sheet.Range("Y1").Text = "Buenos Aires"
sheet.Range("Y2").Text = "La Paz"
sheet.Range("Y3").Text = "Brasilia"
sheet.Range("Y4").Text = "Ottawa"
sheet.Range("Y5").Text = "Santiago"
sheet.Range("Y6").Text = "Bagota"
sheet.Range("Y7").Text = "Havana"
sheet.Range("Y8").Text = "Quito"
sheet.Range("Y9").Text = "San Salvador"
sheet.Range("Y10").Text = "Georgetown"
sheet.Range("Y11").Text = "Kingston"
sheet.Range("Y12").Text = "Mexico City"
sheet.Range("Y13").Text = "Managua"
sheet.Range("Y14").Text = "Asuncion"
sheet.Range("Y15").Text = "Lima"
sheet.Range("Y16").Text = "Washington"
sheet.Range("Y17").Text = "Montevideo"
sheet.Range("Y18").Text = "Caracas"
'Binding the datasource of E9
Dim rangeCapital As CellRange = sheet.Range("E10")
rangeCapital.DataValidation.AllowType = CellDataType.Formula
rangeCapital.DataValidation.DataRange = sheet.Range("Y1:Y18")
rangeCapital.DataValidation.IsSuppressDropDownArrow = False
rangeCapital.DataValidation.IgnoreBlank = True
rangeCapital.Activate()
sheet.HideColumn(sheet.Range("Y1").Column)
sheet.Range("D12").Text = "Continent"
'Set the datasource of F9
Dim rangeContinent As CellRange = sheet.Range("E12")
rangeContinent.DataValidation.AllowType = CellDataType.Formula
rangeContinent.DataValidation.Values = New String() {"South America", "North America"}
rangeContinent.DataValidation.IsSuppressDropDownArrow = False
rangeContinent.Activate()
'Set the width of the column
sheet.Range("E8").Text = "Please select the country "
sheet.Range("E10").Text = "Please select the capital "
sheet.Range("E12").Text = "Please select the continent "
sheet.AutoFitColumn(4)
sheet.AutoFitColumn(5)
'Style
sheet.Range("D8:D12").Style.Font.IsBold = True
sheet.Range("D8").Style.KnownColor = ExcelColors.Color36
sheet.Range("D10").Style.KnownColor = ExcelColors.Color36
sheet.Range("D12").Style.KnownColor = ExcelColors.Color36
sheet.Range("E8").Style.KnownColor = ExcelColors.LightGreen1
sheet.Range("E10").Style.KnownColor = ExcelColors.LightGreen1
sheet.Range("E12").Style.KnownColor = ExcelColors.LightGreen1
sheet.SetRowHeight(8, 16)
sheet.SetRowHeight(10, 16)
sheet.SetRowHeight(12, 16)
End Sub
Sub Main()
Dim workbook As Workbook = New Workbook()
workbook.CreateEmptySheet(1)
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.GridLinesVisible = False
'Writes sample data
CreateSampleData(sheet)
workbook.SaveToFile("test.xls")
System.Diagnostics.Process.Start(workbook.FileName)
End Sub
End Module
Published in
Validation
Wednesday, 07 September 2011 06:00
Excel Read Data With 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
Published in
Formulas
Wednesday, 07 September 2011 05:39
Calculate With Formula
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
Published in
Formulas
Wednesday, 07 September 2011 04:56
Excel Data Export With Formulas
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
Tuesday, 06 September 2011 08:03
SparkLine Chart
The sample demonstrates how to insert SparkLine into an excel workbook.
using Spire.Xls;
using System.Drawing;
namespace SparkLine
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.Version = ExcelVersion.Version2010;
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
//Country
sheet.Range["A1"].Value = "Country";
sheet.Range["A2"].Value = "Cuba";
sheet.Range["A3"].Value = "Mexico";
sheet.Range["A4"].Value = "France";
sheet.Range["A5"].Value = "German";
//Jun
sheet.Range["B1"].Value = "Jun";
sheet.Range["B2"].NumberValue = 0.23;
sheet.Range["B3"].NumberValue = 0.37;
sheet.Range["B4"].NumberValue = 0.15;
sheet.Range["B5"].NumberValue = 0.25;
//Jul
sheet.Range["C1"].Value = "Jul";
sheet.Range["C2"].NumberValue = 0.1;
sheet.Range["C3"].NumberValue = 0.35;
sheet.Range["C4"].NumberValue = 0.22;
sheet.Range["C5"].NumberValue = 0.33;
//Aug
sheet.Range["D1"].Value = "Aug";
sheet.Range["D2"].NumberValue = 0.14;
sheet.Range["D3"].NumberValue = 0.36;
sheet.Range["D4"].NumberValue = 0.25;
sheet.Range["D5"].NumberValue = 0.25;
//Sep
sheet.Range["E1"].Value = "Sep";
sheet.Range["E2"].NumberValue = 0.17;
sheet.Range["E3"].NumberValue = 0.28;
sheet.Range["E4"].NumberValue = 0.39;
sheet.Range["E5"].NumberValue = 0.32;
//Style
sheet.Range["A1:E1"].Style.Font.IsBold = true;
sheet.Range["A2:E2"].Style.KnownColor = ExcelColors.LightYellow;
sheet.Range["A3:E3"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["A4:E4"].Style.KnownColor = ExcelColors.LightOrange;
sheet.Range["A5:E5"].Style.KnownColor = ExcelColors.LightTurquoise;
//Border
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
sheet.Range["B2:D5"].Style.NumberFormatIndex = 9;
SparklineGroup sparklineGroup
= sheet.SparklineGroups.AddGroup(SparklineType.Line);
SparklineCollection sparklines = sparklineGroup.Add();
sparklines.Add(sheet["B2:E2"], sheet["F2"]);
sparklines.Add(sheet["B3:E3"], sheet["F3"]);
sparklines.Add(sheet["B4:E4"], sheet["F4"]);
sparklines.Add(sheet["B5:E5"], sheet["F5"]);
workbook.SaveToFile("Sample.xlsx");
System.Diagnostics.Process.Start(workbook.FileName);
}
}
}
Imports Spire.Xls
Imports System.Drawing
Module Module1
Sub Main()
Dim workbook As New Workbook()
workbook.Version = ExcelVersion.Version2010
workbook.CreateEmptySheets(1)
Dim sheet As Worksheet = workbook.Worksheets(0)
'Country
sheet.Range("A1").Value = "Country"
sheet.Range("A2").Value = "Cuba"
sheet.Range("A3").Value = "Mexico"
sheet.Range("A4").Value = "France"
sheet.Range("A5").Value = "German"
'Jun
sheet.Range("B1").Value = "Jun"
sheet.Range("B2").NumberValue = 0.23
sheet.Range("B3").NumberValue = 0.37
sheet.Range("B4").NumberValue = 0.15
sheet.Range("B5").NumberValue = 0.25
'Jul
sheet.Range("C1").Value = "Jul"
sheet.Range("C2").NumberValue = 0.1
sheet.Range("C3").NumberValue = 0.35
sheet.Range("C4").NumberValue = 0.22
sheet.Range("C5").NumberValue = 0.33
'Aug
sheet.Range("D1").Value = "Aug"
sheet.Range("D2").NumberValue = 0.14
sheet.Range("D3").NumberValue = 0.36
sheet.Range("D4").NumberValue = 0.25
sheet.Range("D5").NumberValue = 0.25
'Sep
sheet.Range("E1").Value = "Sep"
sheet.Range("E2").NumberValue = 0.17
sheet.Range("E3").NumberValue = 0.28
sheet.Range("E4").NumberValue = 0.39
sheet.Range("E5").NumberValue = 0.32
'Style
sheet.Range("A1:E1").Style.Font.IsBold = True
sheet.Range("A2:E2").Style.KnownColor = ExcelColors.LightYellow
sheet.Range("A3:E3").Style.KnownColor = ExcelColors.LightGreen1
sheet.Range("A4:E4").Style.KnownColor = ExcelColors.LightOrange
sheet.Range("A5:E5").Style.KnownColor = ExcelColors.LightTurquoise
'Border
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
sheet.Range("B2:D5").Style.NumberFormatIndex = 9
Dim sparklineGroup As SparklineGroup = sheet.SparklineGroups.AddGroup(SparklineType.Line)
Dim sparklines As SparklineCollection = sparklineGroup.Add()
sparklines.Add(sheet("B2:E2"), sheet("F2"))
sparklines.Add(sheet("B3:E3"), sheet("F3"))
sparklines.Add(sheet("B4:E4"), sheet("F4"))
sparklines.Add(sheet("B5:E5"), sheet("F5"))
workbook.SaveToFile("Sample.xlsx")
System.Diagnostics.Process.Start(workbook.FileName)
End Sub
End Module
Published in
Charts
Tuesday, 06 September 2011 06:39
Excel Cell Names
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
Published in
Formulas
Tuesday, 30 August 2011 09:21
XLS Report Silverlight
The sample demonstrates how to work with MarkerDesign in Silverlight via Spire.XLS.
<Application xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
x:Class="Report.App">
<Application.Resources>
</Application.Resources>
</Application>
using System;
using System.Windows;
using System.Windows.Browser;
using Spire.License;
namespace Report
{
public partial class App : Application
{
public App()
{
this.Startup += this.Application_Startup;
this.Exit += this.Application_Exit;
this.UnhandledException += this.Application_UnhandledException;
InitializeComponent();
}
private void Application_Startup(object sender, StartupEventArgs e)
{
LicenseProvider.SetLicenseKey("your license key in license.elic.xml");
this.RootVisual = new MainPage();
}
private void Application_Exit(object sender, EventArgs e)
{
}
private void Application_UnhandledException(object sender, ApplicationUnhandledExceptionEventArgs e)
{
// If the app is running outside of the debugger then report the exception using
// the browser's exception mechanism. On IE this will display it a yellow alert
// icon in the status bar and Firefox will display a script error.
if (!System.Diagnostics.Debugger.IsAttached)
{
// NOTE: This will allow the application to continue running after an exception has been thrown
// but not handled.
// For production applications this error handling should be replaced with something that will
// report the error to the website and stop the application.
e.Handled = true;
Deployment.Current.Dispatcher.BeginInvoke(delegate { ReportErrorToDOM(e); });
}
}
private void ReportErrorToDOM(ApplicationUnhandledExceptionEventArgs e)
{
try
{
string errorMsg = e.ExceptionObject.Message + e.ExceptionObject.StackTrace;
errorMsg = errorMsg.Replace('"', '\'').Replace("\r\n", @"\n");
String exp = "throw new Error(\"Unhandled Error in Silverlight Application " + errorMsg + "\");";
HtmlPage.Window.Eval(exp);
}
catch (Exception)
{
}
}
}
}
Partial Public Class App
Inherits Application
public Sub New()
InitializeComponent()
End Sub
Private Sub Application_Startup(ByVal o As Object, ByVal e As StartupEventArgs) _
Handles Me.Startup
Me.RootVisual = New MainPage()
End Sub
Private Sub Application_Exit(ByVal o As Object, ByVal e As EventArgs) Handles Me.Exit
End Sub
Private Sub Application_UnhandledException(ByVal sender As object, _
ByVal e As ApplicationUnhandledExceptionEventArgs) Handles Me.UnhandledException
' If the app is running outside of the debugger then report the exception using
' the browser's exception mechanism. On IE this will display it a yellow alert
' icon in the status bar and Firefox will display a script error.
If Not System.Diagnostics.Debugger.IsAttached Then
' NOTE: This will allow the application to continue running after an exception has been thrown
' but not handled.
' For production applications this error handling should be replaced with something that will
' report the error to the website and stop the application.
e.Handled = True
Deployment.Current.Dispatcher.BeginInvoke( _
New Action(Of ApplicationUnhandledExceptionEventArgs)(AddressOf ReportErrorToDOM), e)
End If
End Sub
Private Sub ReportErrorToDOM(ByVal e As ApplicationUnhandledExceptionEventArgs)
Try
Dim errorMsg As String = e.ExceptionObject.Message + e.ExceptionObject.StackTrace
errorMsg = errorMsg.Replace(""""c, "'"c).Replace(ChrW(13) & ChrW(10), "\n")
System.Windows.Browser.HtmlPage.Window.Eval( _
"throw new Error(""Unhandled Error in Silverlight Application " + errorMsg + """);")
Catch
End Try
End Sub
End Class
<UserControl x:Class="Report.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d"
d:DesignHeight="600" d:DesignWidth="500"
xmlns:dataInput="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data.Input" xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk"
xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" >
<Grid x:Name="LayoutRoot" Background="#FF003399" Height="450" Width="500" VerticalAlignment="Top" HorizontalAlignment="Center" Loaded="LayoutRoot_Loaded">
<Grid.RowDefinitions>
<RowDefinition Height="40" MaxHeight="40" MinHeight="40" />
<RowDefinition />
<RowDefinition Height="30" MaxHeight="30" MinHeight="30" />
</Grid.RowDefinitions>
<dataInput:Label HorizontalAlignment="Center" Name="labelTitle" VerticalAlignment="Center" Content="Countries List" Foreground="White" FontWeight="Bold" FontSize="16" Grid.ColumnSpan="2" />
<data:DataGrid AutoGenerateColumns="True" Grid.Row="1" HorizontalAlignment="Stretch" Name="dataGrid" VerticalAlignment="Stretch" Margin="1" />
<Button Content="Generate" Grid.Row="2" HorizontalAlignment="Right" Margin="0,0,2,0" Name="buttonGenerate" VerticalAlignment="Center" Width="75" IsEnabled="False" Click="buttonGenerate_Click" />
</Grid>
</UserControl>
using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Windows;
using System.Windows.Controls;
using Spire.Xls;
namespace Report
{
public partial class MainPage : UserControl
{
public class Country
{
public String Name { get; set; }
public String Capital { get; set; }
public String Continent { get; set; }
public double Area { get; set; }
public long Population { get; set; }
}
private SaveFileDialog saveFileDialog = null;
private List dataSource = null;
private Workbook template = null;
public MainPage()
{
InitializeComponent();
this.saveFileDialog = new SaveFileDialog();
this.saveFileDialog.Filter = "Excel workbooks (*.xls) |*.xls";
}
private void LayoutRoot_Loaded(object sender, RoutedEventArgs e)
{
Assembly assembly = this.GetType().Assembly;
foreach (String name in assembly.GetManifestResourceNames())
{
if (name.EndsWith(".DatatableSample.xls"))
{
using (Stream stream = assembly.GetManifestResourceStream(name))
{
Workbook workbook = new Workbook();
workbook.LoadFromStream(stream);
Worksheet sheet = workbook.Worksheets[0];
this.dataSource = new List();
foreach (CellRange row in sheet.Rows)
{
if (row != null && row.Cells != null && row.Cells.Length == 5
&& !row.Cells[0].IsBlank)
{
if (row.Cells[0].Row == 1)
{
continue;
}
this.dataSource.Add(new Country()
{
Name = row.Cells[0].Value,
Capital = row.Cells[1].Value,
Continent = row.Cells[2].Value,
Area = row.Cells[3].NumberValue,
Population = Convert.ToInt64(row.Cells[4].NumberValue)
});
}
else
{
break;
}
}
this.dataGrid.ItemsSource = this.dataSource;
}
this.buttonGenerate.IsEnabled = true;
}
else if(name.EndsWith(".MarkerDesignerSample.xls"))
{
using (Stream stream = assembly.GetManifestResourceStream(name))
{
this.template = new Workbook();
this.template.LoadFromStream(stream);
}
}
}
}
private void buttonGenerate_Click(object sender, RoutedEventArgs e)
{
Worksheet worksheet = this.template.Worksheets[0];
this.template.MarkerDesigner.AddParameter("Variable1", 1234.5678);
this.template.MarkerDesigner.AddArray("Country", dataSource.ToArray());
this.template.MarkerDesigner.Apply();
worksheet.AllocatedRange.AutoFitRows();
worksheet.AllocatedRange.AutoFitColumns();
bool? result = this.saveFileDialog.ShowDialog();
if (result.HasValue && result.Value)
{
using (Stream stream = this.saveFileDialog.OpenFile())
{
this.template.SaveToStream(stream);
}
}
}
}
}
Imports System
Imports System.IO
Imports System.Net
Imports System.Reflection
Imports System.Windows
Imports System.Windows.Controls
Imports Spire.Xls
Partial Public Class MainPage
Inherits UserControl
Public Class Country
Public Property Name() As [String]
Get
Return m_Name
End Get
Set(ByVal value As [String])
m_Name = Value
End Set
End Property
Private m_Name As [String]
Public Property Capital() As [String]
Get
Return m_Capital
End Get
Set(ByVal value As [String])
m_Capital = Value
End Set
End Property
Private m_Capital As [String]
Public Property Continent() As [String]
Get
Return m_Continent
End Get
Set(ByVal value As [String])
m_Continent = Value
End Set
End Property
Private m_Continent As [String]
Public Property Area() As Double
Get
Return m_Area
End Get
Set(ByVal value As Double)
m_Area = Value
End Set
End Property
Private m_Area As Double
Public Property Population() As Long
Get
Return m_Population
End Get
Set(ByVal value As Long)
m_Population = Value
End Set
End Property
Private m_Population As Long
End Class
Private saveFileDialog As SaveFileDialog = Nothing
Private dataSource As List(Of Country) = Nothing
Private xlsTemplate As Workbook = Nothing
Public Sub New()
InitializeComponent()
Me.saveFileDialog = New SaveFileDialog()
Me.saveFileDialog.Filter = "Excel workbooks (*.xls) |*.xls"
End Sub
Private Sub LayoutRoot_Loaded(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
Dim assembly As Assembly = Me.[GetType]().Assembly
For Each name As [String] In assembly.GetManifestResourceNames()
If name.EndsWith(".DatatableSample.xls") Then
Using stream As Stream = assembly.GetManifestResourceStream(name)
Dim workbook As New Workbook()
workbook.LoadFromStream(stream)
Dim sheet As Worksheet = workbook.Worksheets(0)
Me.dataSource = New List(Of Country)()
For Each row As CellRange In sheet.Rows
If row IsNot Nothing AndAlso row.Cells IsNot Nothing AndAlso row.Cells.Length = 5 AndAlso Not row.Cells(0).IsBlank Then
If row.Cells(0).Row = 1 Then
Continue For
End If
Me.dataSource.Add(New Country() With { _
.Name = row.Cells(0).Value, _
.Capital = row.Cells(1).Value, _
.Continent = row.Cells(2).Value, _
.Area = row.Cells(3).NumberValue, _
.Population = Convert.ToInt64(row.Cells(4).NumberValue) _
})
Else
Exit For
End If
Next
Me.dataGrid.ItemsSource = Me.dataSource
End Using
Me.buttonGenerate.IsEnabled = True
ElseIf name.EndsWith(".MarkerDesignerSample.xls") Then
Using stream As Stream = assembly.GetManifestResourceStream(name)
Me.xlsTemplate = New Workbook()
Me.xlsTemplate.LoadFromStream(stream)
End Using
End If
Next
End Sub
Private Sub buttonGenerate_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
Dim worksheet As Worksheet = Me.xlsTemplate.Worksheets(0)
Me.xlsTemplate.MarkerDesigner.AddParameter("Variable1", 1234.5678)
Me.xlsTemplate.MarkerDesigner.AddArray("Country", dataSource.ToArray())
Me.xlsTemplate.MarkerDesigner.Apply()
worksheet.AllocatedRange.AutoFitRows()
worksheet.AllocatedRange.AutoFitColumns()
Dim result As System.Nullable(Of Boolean) = Me.saveFileDialog.ShowDialog()
If result.HasValue AndAlso result.Value Then
Using stream As Stream = Me.saveFileDialog.OpenFile()
Me.xlsTemplate.SaveToStream(stream)
End Using
End If
End Sub
End Class
Published in
Silverlight
Monday, 25 July 2011 09:12
XLS to PDF in C#, VB.NET
The sample demonstrates how to convert Excel workbook to PDF file via Spire.XLS.
static void Main()
{
Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();
workbook.LoadFromFile(@"DataTableSample.xls");
Spire.Xls.Converter.PdfConverter pdfConverter
= new Spire.Xls.Converter.PdfConverter(workbook);
Spire.Pdf.PdfDocument pdfDocument = new Spire.Pdf.PdfDocument();
Spire.Xls.Converter.PdfConverterSettings settings
= new Spire.Xls.Converter.PdfConverterSettings();
settings.EmbedFonts = true;
settings.TemplateDocument = pdfDocument;
pdfDocument = pdfConverter.Convert(settings);
pdfDocument.SaveToFile("XLS-to-PDF.pdf");
pdfDocument.Close();
}
Shared Sub Main()
Dim workbook As New Spire.Xls.Workbook()
workbook.LoadFromFile("DataTableSample.xls")
Dim pdfConverter As New Spire.Xls.Converter.PdfConverter(workbook)
Dim pdfDocument As New Spire.Pdf.PdfDocument()
Dim settings As New Spire.Xls.Converter.PdfConverterSettings()
settings.EmbedFonts = True
settings.TemplateDocument = pdfDocument
pdfDocument = pdfConverter.Convert(settings)
pdfDocument.SaveToFile("XLS-to-PDF.pdf")
pdfDocument.Close()
End Sub
Published in
Misc
Monday, 24 January 2011 09:52
Generate Excel Column Chart in C#, VB.NET
Excel Charts uses graphics to present data information to enable users to analyze data more intuitively. According to details of data information, users need to choose the most appropriate chart type. For example, column chart is suitable for comparison between data, while pie chart is often used to show percentage of each item among all.
Spire.XLS for .NET, a stand-alone Excel operation component designed for .NET developers, allows users to generate Excel chart, and this guide will show you how to generate excel column chart with C#, VB.NET via Spire.XLS for .NET. Users can use sheet.Charts.Add(ExcelChartType) method to add chart in Excel. Then, assign value for DataRange property of Chart class to fill information in chart area. Next, set position, chart title, axes, legend and format them for chart to make the chart be more completed and wonderful.
The following screenshot shows the result of Excel column chart generation.
Download and install Spire.XLS for .NET. Use the following code to generate Excel column chart.
[C#]
using System.Drawing;
using Spire.Xls;
namespace XLSTest
{
class Program
{
static void Main(string[] args)
{
//Load Workbook
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"E:\Work\Documents\ExcelFiles\PartSalesInfo.xlsx");
Worksheet sheet = workbook.Worksheets[0];
//Add Chart and Set Chart Data Range
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = sheet.Range["D1:E17"];
chart.SeriesDataFromRange = false;
//Chart Position
chart.LeftColumn = 1;
chart.TopRow = 19;
chart.RightColumn = 8;
chart.BottomRow = 33;
//Chart Border
chart.ChartArea.Border.Weight = ChartLineWeightType.Medium;
chart.ChartArea.Border.Color = Color.SandyBrown;
//Chart Title
chart.ChartTitle = "Parts Sales Info";
chart.ChartTitleArea.Font.FontName = "Calibri";
chart.ChartTitleArea.Font.Size = 13;
chart.ChartTitleArea.Font.IsBold = true;
//Chart Axes
chart.PrimaryCategoryAxis.Title = "Parts";
chart.PrimaryCategoryAxis.Font.Color = Color.Blue;
chart.PrimaryValueAxis.Title = "Amounts";
chart.PrimaryValueAxis.HasMajorGridLines = false;
chart.PrimaryValueAxis.MaxValue = 350;
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
//Chart Legend
chart.Legend.Position = LegendPositionType.Right;
//Save and Launch
workbook.SaveToFile("ExcelColumnChart.xlsx",ExcelVersion.Version2010);
System.Diagnostics.Process.Start("ExcelColumnChart.xlsx");
}
}
}
[VB.NET]
Imports System.Drawing
Imports Spire.Xls
Namespace XLSTest
Friend Class Program
Shared Sub Main(ByVal args() As String)
'Load Workbook
Dim workbook As New Workbook()
workbook.LoadFromFile("E:\Work\Documents\ExcelFiles\PartSalesInfo.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
'Add Chart and Set Chart Data Range
Dim chart As Chart = sheet.Charts.Add(ExcelChartType.ColumnClustered)
chart.DataRange = sheet.Range("D1:E17")
chart.SeriesDataFromRange = False
'Chart Position
chart.LeftColumn = 1
chart.TopRow = 19
chart.RightColumn = 8
chart.BottomRow = 33
'Chart Border
chart.ChartArea.Border.Weight = ChartLineWeightType.Medium
chart.ChartArea.Border.Color = Color.SandyBrown
'Chart Title
chart.ChartTitle = "Parts Sales Info"
chart.ChartTitleArea.Font.FontName = "Calibri"
chart.ChartTitleArea.Font.Size = 13
chart.ChartTitleArea.Font.IsBold = True
'Chart Axes
chart.PrimaryCategoryAxis.Title = "Parts"
chart.PrimaryCategoryAxis.Font.Color = Color.Blue
chart.PrimaryValueAxis.Title = "Amounts"
chart.PrimaryValueAxis.HasMajorGridLines = False
chart.PrimaryValueAxis.MaxValue = 350
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90
'Chart Legend
chart.Legend.Position = LegendPositionType.Right
'Save and Launch
workbook.SaveToFile("ExcelColumnChart.xlsx", ExcelVersion.Version2010)
System.Diagnostics.Process.Start("ExcelColumnChart.xlsx")
End Sub
End Class
End Namespace
Published in
Chart
Thursday, 20 January 2011 10:33
Insert Image in Excel in C#, VB.NET
Users are allowed to insert image in Excel files. With image, the appearance of Excel file will be more beautiful. Besides decorating, some images are related to data information in Excel. For example, the image will be a chart. These images can make readers learn data information more clearly. This guide will show an easy method to insert image in Excel with C#, VB.NET
Spire.XLS for .NET, a professional component to operating Excel files, enables users to insert image in Excel by using C# and VB.NET. This guide focuses on how to easily insert image in Excel by using Spire.XLS for .NET. Developers can use sheet.Pictures.Add(int toprow, int leftcolumn, filename string) method to insert image in Excel directly. Below demonstrates an Excel with an image of flower.
Download and install Spire.XLS for .NET and then use the following code to insert image.
[C#]
using Spire.Xls;
namespace InsertImage
{
class Program
{
static void Main(string[] args)
{
//Create Workbook
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
//Insert Image
sheet.Pictures.Add(1, 1, @"E:\work\sample.jpg");
//Save and Launch
workbook.SaveToFile("ExcelImage.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("ExcelImage.xlsx");
}
}
}
[VB.NET]
Imports Spire.Xls
Namespace InsertImage
Friend Class Program
Shared Sub Main(ByVal args() As String)
'Create Workbook
Dim workbook As New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)
'Insert Image
sheet.Pictures.Add(1, 1, "E:\work\sample.jpg")
'Save and Launch
workbook.SaveToFile("ExcelImage.xlsx", ExcelVersion.Version2010)
System.Diagnostics.Process.Start("ExcelImage.xlsx")
End Sub
End Class
End Namespace
Published in
Image
