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
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
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
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
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
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
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
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
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.
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"); } } }
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
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.
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"); } } }
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