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
Sunday, 01 August 2010 16:42
Data Export Style in C#, VB.NET
How to export data table to Excel file and set cell style.
private void button1_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection oleDbConnection1
= new System.Data.OleDb.OleDbConnection();
oleDbConnection1.ConnectionString
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\..\..\Database\demo.mdb";
System.Data.OleDb.OleDbCommand oleDbCommand1
= new System.Data.OleDb.OleDbCommand();
oleDbCommand1.CommandText = "select * from parts";
oleDbCommand1.Connection = oleDbConnection1;
System.Data.OleDb.OleDbCommand oleDbCommand2
= new System.Data.OleDb.OleDbCommand();
oleDbCommand2.CommandText = "select * from country";
oleDbCommand2.Connection = oleDbConnection1;
Spire.DataExport.XLS.CellExport cellExport3
= new Spire.DataExport.XLS.CellExport();
Spire.DataExport.XLS.WorkSheet workSheet6 = new Spire.DataExport.XLS.WorkSheet();
Spire.DataExport.XLS.ColumnFormat columnFormat1 = new Spire.DataExport.XLS.ColumnFormat();
Spire.DataExport.XLS.ColumnFormat columnFormat2 = new Spire.DataExport.XLS.ColumnFormat();
Spire.DataExport.XLS.ColumnFormat columnFormat3 = new Spire.DataExport.XLS.ColumnFormat();
Spire.DataExport.XLS.ColumnFormat columnFormat4 = new Spire.DataExport.XLS.ColumnFormat();
Spire.DataExport.XLS.ColumnFormat columnFormat5 = new Spire.DataExport.XLS.ColumnFormat();
cellExport3.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
cellExport3.DataFormats.CultureName = "zh-CN";
cellExport3.DataFormats.Currency = "￥#,###,##0.00";
cellExport3.DataFormats.DateTime = "yyyy-M-d H:mm";
cellExport3.DataFormats.Float = "#,###,##0.00";
cellExport3.DataFormats.Integer = "#,###,##0";
cellExport3.DataFormats.Time = "H:mm";
cellExport3.FileName = "misc.xls";
cellExport3.SheetOptions.AggregateFormat.Font.Name = "Arial";
cellExport3.SheetOptions.CustomDataFormat.Font.Name = "Arial";
cellExport3.SheetOptions.DefaultFont.Name = "Arial";
cellExport3.SheetOptions.FooterFormat.Font.Name = "Arial";
cellExport3.SheetOptions.HeaderFormat.Font.Name = "Arial";
cellExport3.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
cellExport3.SheetOptions.HyperlinkFormat.Font.Name = "Arial";
cellExport3.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
cellExport3.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
cellExport3.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
cellExport3.SheetOptions.NoteFormat.Font.Bold = true;
cellExport3.SheetOptions.NoteFormat.Font.Name = "Tahoma";
cellExport3.SheetOptions.NoteFormat.Font.Size = 8F;
cellExport3.SheetOptions.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.Gray40Percent;
cellExport3.SheetOptions.TitlesFormat.Font.Bold = true;
cellExport3.SheetOptions.TitlesFormat.Font.Name = "Arial";
columnFormat1.FieldName = "PartNo";
columnFormat1.Font.Bold = true;
columnFormat1.Font.Name = "Arial";
columnFormat2.FieldName = "VendorNo";
columnFormat2.Font.Color = Spire.DataExport.XLS.CellColor.Color1;
columnFormat2.Font.Italic = true;
columnFormat2.Font.Name = "Arial";
columnFormat3.FieldName = "Description";
columnFormat3.Font.Name = "Arial";
columnFormat3.Font.Strikeout = true;
columnFormat3.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.DoubleAccounting;
columnFormat4.FieldName = "OnHand";
columnFormat4.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise;
columnFormat4.FillStyle.Foreground = Spire.DataExport.XLS.CellColor.Pink;
columnFormat4.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.ThinGorizontal;
columnFormat4.Font.Name = "Arial";
columnFormat5.FieldName = "ListPrice";
columnFormat5.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow;
columnFormat5.FillStyle.Foreground = Spire.DataExport.XLS.CellColor.SkyBlue;
columnFormat5.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.ThinVertical;
columnFormat5.Font.Name = "Arial";
workSheet6.ColumnFormats.Add(columnFormat1);
workSheet6.ColumnFormats.Add(columnFormat2);
workSheet6.ColumnFormats.Add(columnFormat3);
workSheet6.ColumnFormats.Add(columnFormat4);
workSheet6.ColumnFormats.Add(columnFormat5);
workSheet6.FormatsExport.CultureName = "zh-CN";
workSheet6.FormatsExport.Currency = "￥#,###,##0.00";
workSheet6.FormatsExport.DateTime = "yyyy-M-d H:mm";
workSheet6.FormatsExport.Float = "#,###,##0.00";
workSheet6.FormatsExport.Integer = "#,###,##0";
workSheet6.FormatsExport.Time = "H:mm";
workSheet6.Options.AggregateFormat.Font.Name = "Arial";
workSheet6.Options.CustomDataFormat.Font.Name = "Arial";
workSheet6.Options.DefaultFont.Name = "Arial";
workSheet6.Options.FooterFormat.Font.Name = "Arial";
workSheet6.Options.HeaderFormat.Font.Name = "Arial";
workSheet6.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet6.Options.HyperlinkFormat.Font.Name = "Arial";
workSheet6.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
workSheet6.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
workSheet6.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
workSheet6.Options.NoteFormat.Font.Bold = true;
workSheet6.Options.NoteFormat.Font.Name = "Tahoma";
workSheet6.Options.NoteFormat.Font.Size = 8F;
workSheet6.Options.TitlesFormat.Font.Bold = true;
workSheet6.Options.TitlesFormat.Font.Name = "Arial";
workSheet6.SheetName = "Sheet 1";
workSheet6.SQLCommand = oleDbCommand1;
workSheet6.StartDataCol = ((System.Byte)(0));
cellExport3.Sheets.Add(workSheet6);
cellExport3.GetDataParams += new Spire.DataExport.Delegates.DataParamsEventHandler(this.cellExport3_GetDataParams);
oleDbConnection1.Open();
try
{
cellExport3.SaveToFile();
}
finally
{
oleDbConnection1.Close();
}
}
private void cellExport3_GetDataParams(object sender, Spire.DataExport.EventArgs.DataParamsEventArgs e)
{
if ((e.Sheet == 0) && (e.Col == 6))
{
e.FormatText = (sender as Spire.DataExport.XLS.WorkSheet).ExportCell.DataFormats.Currency;
}
}
Private WithEvents cellExport3 As Spire.DataExport.XLS.CellExport
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim oleDbConnection1 As New System.Data.OleDb.OleDbConnection()
oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\..\..\Database\demo.mdb"
Dim oleDbCommand1 As New System.Data.OleDb.OleDbCommand()
oleDbCommand1.CommandText = "select * from parts"
oleDbCommand1.Connection = oleDbConnection1
Dim oleDbCommand2 As New System.Data.OleDb.OleDbCommand()
oleDbCommand2.CommandText = "select * from country"
oleDbCommand2.Connection = oleDbConnection1
Dim workSheet6 As New Spire.DataExport.XLS.WorkSheet()
Dim columnFormat1 As New Spire.DataExport.XLS.ColumnFormat()
Dim columnFormat2 As New Spire.DataExport.XLS.ColumnFormat()
Dim columnFormat3 As New Spire.DataExport.XLS.ColumnFormat()
Dim columnFormat4 As New Spire.DataExport.XLS.ColumnFormat()
Dim columnFormat5 As New Spire.DataExport.XLS.ColumnFormat()
cellExport3 = New Spire.DataExport.XLS.CellExport
cellExport3.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
cellExport3.DataFormats.CultureName = "zh-CN"
cellExport3.DataFormats.Currency = "￥#,###,##0.00"
cellExport3.DataFormats.DateTime = "yyyy-M-d H:mm"
cellExport3.DataFormats.Float = "#,###,##0.00"
cellExport3.DataFormats.[Integer] = "#,###,##0"
cellExport3.DataFormats.Time = "H:mm"
cellExport3.FileName = "misc.xls"
cellExport3.SheetOptions.AggregateFormat.Font.Name = "Arial"
cellExport3.SheetOptions.CustomDataFormat.Font.Name = "Arial"
cellExport3.SheetOptions.DefaultFont.Name = "Arial"
cellExport3.SheetOptions.FooterFormat.Font.Name = "Arial"
cellExport3.SheetOptions.HeaderFormat.Font.Name = "Arial"
cellExport3.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
cellExport3.SheetOptions.HyperlinkFormat.Font.Name = "Arial"
cellExport3.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single]
cellExport3.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
cellExport3.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
cellExport3.SheetOptions.NoteFormat.Font.Bold = True
cellExport3.SheetOptions.NoteFormat.Font.Name = "Tahoma"
cellExport3.SheetOptions.NoteFormat.Font.Size = 8.0F
cellExport3.SheetOptions.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.Gray40Percent
cellExport3.SheetOptions.TitlesFormat.Font.Bold = True
cellExport3.SheetOptions.TitlesFormat.Font.Name = "Arial"
columnFormat1.FieldName = "PartNo"
columnFormat1.Font.Bold = True
columnFormat1.Font.Name = "Arial"
columnFormat2.FieldName = "VendorNo"
columnFormat2.Font.Color = Spire.DataExport.XLS.CellColor.Color1
columnFormat2.Font.Italic = True
columnFormat2.Font.Name = "Arial"
columnFormat3.FieldName = "Description"
columnFormat3.Font.Name = "Arial"
columnFormat3.Font.Strikeout = True
columnFormat3.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.DoubleAccounting
columnFormat4.FieldName = "OnHand"
columnFormat4.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise
columnFormat4.FillStyle.Foreground = Spire.DataExport.XLS.CellColor.Pink
columnFormat4.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.ThinGorizontal
columnFormat4.Font.Name = "Arial"
columnFormat5.FieldName = "ListPrice"
columnFormat5.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow
columnFormat5.FillStyle.Foreground = Spire.DataExport.XLS.CellColor.SkyBlue
columnFormat5.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.ThinVertical
columnFormat5.Font.Name = "Arial"
workSheet6.ColumnFormats.Add(columnFormat1)
workSheet6.ColumnFormats.Add(columnFormat2)
workSheet6.ColumnFormats.Add(columnFormat3)
workSheet6.ColumnFormats.Add(columnFormat4)
workSheet6.ColumnFormats.Add(columnFormat5)
workSheet6.FormatsExport.CultureName = "zh-CN"
workSheet6.FormatsExport.Currency = "￥#,###,##0.00"
workSheet6.FormatsExport.DateTime = "yyyy-M-d H:mm"
workSheet6.FormatsExport.Float = "#,###,##0.00"
workSheet6.FormatsExport.[Integer] = "#,###,##0"
workSheet6.FormatsExport.Time = "H:mm"
workSheet6.Options.AggregateFormat.Font.Name = "Arial"
workSheet6.Options.CustomDataFormat.Font.Name = "Arial"
workSheet6.Options.DefaultFont.Name = "Arial"
workSheet6.Options.FooterFormat.Font.Name = "Arial"
workSheet6.Options.HeaderFormat.Font.Name = "Arial"
workSheet6.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
workSheet6.Options.HyperlinkFormat.Font.Name = "Arial"
workSheet6.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single]
workSheet6.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
workSheet6.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
workSheet6.Options.NoteFormat.Font.Bold = True
workSheet6.Options.NoteFormat.Font.Name = "Tahoma"
workSheet6.Options.NoteFormat.Font.Size = 8.0F
workSheet6.Options.TitlesFormat.Font.Bold = True
workSheet6.Options.TitlesFormat.Font.Name = "Arial"
workSheet6.SheetName = "Sheet 1"
workSheet6.SQLCommand = oleDbCommand1
workSheet6.StartDataCol = CByte(0)
cellExport3.Sheets.Add(workSheet6)
oleDbConnection1.Open()
Try
cellExport3.SaveToFile()
Finally
oleDbConnection1.Close()
End Try
End Sub
Private Sub cellExport3_GetDataParams(ByVal sender As Object, ByVal e As Spire.DataExport.EventArgs.DataParamsEventArgs) Handles cellExport3.GetDataParams
If (e.Sheet = 0) AndAlso (e.Col = 6) Then
e.FormatText = cellExport3.DataFormats.Currency
End If
End Sub
Published in
Cell
Sunday, 01 August 2010 15:29
Data Export Multiple Sheets in C#, VB.NET
How to export multiple data tables to different worksheets.
private void button1_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection oleDbConnection1
= new System.Data.OleDb.OleDbConnection();
oleDbConnection1.ConnectionString
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\..\..\Database\demo.mdb";
System.Data.OleDb.OleDbCommand oleDbCommand1
= new System.Data.OleDb.OleDbCommand();
oleDbCommand1.CommandText = "select * from parts";
oleDbCommand1.Connection = oleDbConnection1;
System.Data.OleDb.OleDbCommand oleDbCommand2
= new System.Data.OleDb.OleDbCommand();
oleDbCommand2.CommandText = "select * from country";
oleDbCommand2.Connection = oleDbConnection1;
Spire.DataExport.XLS.CellExport cellExport1
= new Spire.DataExport.XLS.CellExport();
Spire.DataExport.XLS.WorkSheet workSheet1 = new Spire.DataExport.XLS.WorkSheet();
Spire.DataExport.XLS.StripStyle stripStyle1 = new Spire.DataExport.XLS.StripStyle();
Spire.DataExport.XLS.StripStyle stripStyle2 = new Spire.DataExport.XLS.StripStyle();
Spire.DataExport.XLS.WorkSheet workSheet2 = new Spire.DataExport.XLS.WorkSheet();
Spire.DataExport.XLS.StripStyle stripStyle3 = new Spire.DataExport.XLS.StripStyle();
Spire.DataExport.XLS.StripStyle stripStyle4 = new Spire.DataExport.XLS.StripStyle();
cellExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
cellExport1.AutoFitColWidth = true;
cellExport1.AutoFormula = true;
cellExport1.DataFormats.CultureName = "zh-CN";
cellExport1.DataFormats.Currency = "$#,###,##0.00";
cellExport1.DataFormats.DateTime = "yyyy-M-d H:mm";
cellExport1.DataFormats.Float = "#,###,##0.00";
cellExport1.DataFormats.Integer = "#,###,##0";
cellExport1.DataFormats.Time = "H:mm";
cellExport1.FileName = "Sheets.xls";
cellExport1.SheetOptions.AggregateFormat.Font.Name = "Arial";
cellExport1.SheetOptions.CustomDataFormat.Font.Name = "Arial";
cellExport1.SheetOptions.DefaultFont.Name = "Arial";
cellExport1.SheetOptions.FooterFormat.Font.Name = "Arial";
cellExport1.SheetOptions.HeaderFormat.Font.Name = "Arial";
cellExport1.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
cellExport1.SheetOptions.HyperlinkFormat.Font.Name = "Arial";
cellExport1.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
cellExport1.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
cellExport1.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
cellExport1.SheetOptions.NoteFormat.Font.Bold = true;
cellExport1.SheetOptions.NoteFormat.Font.Name = "Tahoma";
cellExport1.SheetOptions.NoteFormat.Font.Size = 8F;
cellExport1.SheetOptions.TitlesFormat.Font.Bold = true;
cellExport1.SheetOptions.TitlesFormat.Font.Name = "Arial";
workSheet1.AutoFitColWidth = true;
workSheet1.FormatsExport.CultureName = "zh-CN";
workSheet1.FormatsExport.Currency = "￥#,###,##0.00";
workSheet1.FormatsExport.DateTime = "yyyy-M-d H:mm";
workSheet1.FormatsExport.Float = "#,###,##0.00";
workSheet1.FormatsExport.Integer = "#,###,##0";
workSheet1.FormatsExport.Time = "H:mm";
stripStyle1.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle1.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle1.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle1.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle1.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
stripStyle1.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
stripStyle1.Font.Name = "Arial";
stripStyle2.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle2.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle2.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle2.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle2.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise;
stripStyle2.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
stripStyle2.Font.Name = "Arial";
workSheet1.ItemStyles.Add(stripStyle1);
workSheet1.ItemStyles.Add(stripStyle2);
workSheet1.ItemType = Spire.DataExport.XLS.CellItemType.Col;
workSheet1.Options.AggregateFormat.Font.Name = "Arial";
workSheet1.Options.CustomDataFormat.Font.Name = "Arial";
workSheet1.Options.DefaultFont.Name = "Arial";
workSheet1.Options.FooterFormat.Font.Name = "Arial";
workSheet1.Options.HeaderFormat.Font.Bold = true;
workSheet1.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet1.Options.HeaderFormat.Font.Name = "Arial";
workSheet1.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet1.Options.HyperlinkFormat.Font.Name = "Arial";
workSheet1.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
workSheet1.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
workSheet1.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
workSheet1.Options.NoteFormat.Font.Bold = true;
workSheet1.Options.NoteFormat.Font.Name = "Tahoma";
workSheet1.Options.NoteFormat.Font.Size = 8F;
workSheet1.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet1.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet1.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet1.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet1.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow;
workSheet1.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
workSheet1.Options.TitlesFormat.Font.Bold = true;
workSheet1.Options.TitlesFormat.Font.Name = "Arial";
workSheet1.SheetName = "parts";
workSheet1.SQLCommand = oleDbCommand1;
workSheet1.StartDataCol = ((System.Byte)(0));
workSheet2.AutoFitColWidth = true;
workSheet2.FormatsExport.CultureName = "zh-CN";
workSheet2.FormatsExport.Currency = "￥#,###,##0.00";
workSheet2.FormatsExport.DateTime = "yyyy-M-d H:mm";
workSheet2.FormatsExport.Float = "#,###,##0.00";
workSheet2.FormatsExport.Integer = "#,###,##0";
workSheet2.FormatsExport.Time = "H:mm";
stripStyle3.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle3.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle3.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle3.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle3.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
stripStyle3.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
stripStyle3.Font.Name = "Arial";
stripStyle4.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle4.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle4.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle4.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle4.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise;
stripStyle4.FillStyle.Foreground = Spire.DataExport.XLS.CellColor.White;
stripStyle4.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
stripStyle4.Font.Name = "Arial";
workSheet2.ItemStyles.Add(stripStyle3);
workSheet2.ItemStyles.Add(stripStyle4);
workSheet2.ItemType = Spire.DataExport.XLS.CellItemType.Col;
workSheet2.Options.AggregateFormat.Font.Name = "Arial";
workSheet2.Options.CustomDataFormat.Font.Name = "Arial";
workSheet2.Options.DefaultFont.Name = "Arial";
workSheet2.Options.FooterFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet2.Options.FooterFormat.Font.Name = "Arial";
workSheet2.Options.HeaderFormat.Font.Bold = true;
workSheet2.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet2.Options.HeaderFormat.Font.Name = "Arial";
workSheet2.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet2.Options.HyperlinkFormat.Font.Name = "Arial";
workSheet2.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
workSheet2.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
workSheet2.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
workSheet2.Options.NoteFormat.Font.Bold = true;
workSheet2.Options.NoteFormat.Font.Name = "Tahoma";
workSheet2.Options.NoteFormat.Font.Size = 8F;
workSheet2.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet2.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet2.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet2.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet2.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow;
workSheet2.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
workSheet2.Options.TitlesFormat.Font.Bold = true;
workSheet2.Options.TitlesFormat.Font.Name = "Arial";
workSheet2.SheetName = "country";
workSheet2.SQLCommand = oleDbCommand2;
workSheet2.StartDataCol = ((System.Byte)(0));
cellExport1.Sheets.Add(workSheet1);
cellExport1.Sheets.Add(workSheet2);
cellExport1.SQLCommand = oleDbCommand1;
cellExport1.GetDataParams += new Spire.DataExport.Delegates.DataParamsEventHandler(cellExport1_GetDataParams);
oleDbConnection1.Open();
try
{
cellExport1.SaveToFile();
}
finally
{
oleDbConnection1.Close();
}
}
private void cellExport1_GetDataParams(object sender, Spire.DataExport.EventArgs.DataParamsEventArgs e)
{
if ((e.Sheet == 0) && (e.Col == 6))
{
e.FormatText = (sender as Spire.DataExport.XLS.WorkSheet).ExportCell.DataFormats.Currency;
}
}
Private WithEvents cellExport1 As Spire.DataExport.XLS.CellExport
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim oleDbConnection1 As System.Data.OleDb.OleDbConnection
oleDbConnection1 = New System.Data.OleDb.OleDbConnection
oleDbConnection1.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\..\..\Database\demo.mdb"
Dim oleDbCommand1 As System.Data.OleDb.OleDbCommand
oleDbCommand1 = New System.Data.OleDb.OleDbCommand
oleDbCommand1.CommandText = "select * from parts"
oleDbCommand1.Connection = oleDbConnection1
Dim oleDbCommand2 As System.Data.OleDb.OleDbCommand
oleDbCommand2 = New System.Data.OleDb.OleDbCommand
oleDbCommand2.CommandText = "select * from country"
oleDbCommand2.Connection = oleDbConnection1
Dim WorkSheet1 As Spire.DataExport.XLS.WorkSheet = New Spire.DataExport.XLS.WorkSheet
Dim StripStyle1 As Spire.DataExport.XLS.StripStyle = New Spire.DataExport.XLS.StripStyle
Dim StripStyle2 As Spire.DataExport.XLS.StripStyle = New Spire.DataExport.XLS.StripStyle
Dim WorkSheet2 As Spire.DataExport.XLS.WorkSheet = New Spire.DataExport.XLS.WorkSheet
Dim StripStyle3 As Spire.DataExport.XLS.StripStyle = New Spire.DataExport.XLS.StripStyle
Dim StripStyle4 As Spire.DataExport.XLS.StripStyle = New Spire.DataExport.XLS.StripStyle
cellExport1 = New Spire.DataExport.XLS.CellExport
cellExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
cellExport1.AutoFitColWidth = True
cellExport1.AutoFormula = True
cellExport1.DataFormats.CultureName = "zh-CN"
cellExport1.DataFormats.Currency = "$#,###,##0.00"
cellExport1.DataFormats.DateTime = "yyyy-M-d H:mm"
cellExport1.DataFormats.Float = "#,###,##0.00"
cellExport1.DataFormats.Integer = "#,###,##0"
cellExport1.DataFormats.Time = "H:mm"
cellExport1.FileName = "Sheets.xls"
cellExport1.SheetOptions.AggregateFormat.Font.Name = "Arial"
cellExport1.SheetOptions.CustomDataFormat.Font.Name = "Arial"
cellExport1.SheetOptions.DefaultFont.Name = "Arial"
cellExport1.SheetOptions.FooterFormat.Font.Name = "Arial"
cellExport1.SheetOptions.HeaderFormat.Font.Name = "Arial"
cellExport1.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
cellExport1.SheetOptions.HyperlinkFormat.Font.Name = "Arial"
cellExport1.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single
cellExport1.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
cellExport1.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
cellExport1.SheetOptions.NoteFormat.Font.Bold = True
cellExport1.SheetOptions.NoteFormat.Font.Name = "Tahoma"
cellExport1.SheetOptions.NoteFormat.Font.Size = 8.0!
cellExport1.SheetOptions.TitlesFormat.Font.Bold = True
cellExport1.SheetOptions.TitlesFormat.Font.Name = "Arial"
WorkSheet1.AutoFitColWidth = True
WorkSheet1.FormatsExport.CultureName = "zh-CN"
WorkSheet1.FormatsExport.Currency = "￥#,###,##0.00"
WorkSheet1.FormatsExport.DateTime = "yyyy-M-d H:mm"
WorkSheet1.FormatsExport.Float = "#,###,##0.00"
WorkSheet1.FormatsExport.Integer = "#,###,##0"
WorkSheet1.FormatsExport.Time = "H:mm"
StripStyle1.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle1.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle1.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle1.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle1.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen
StripStyle1.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
StripStyle1.Font.Name = "Arial"
StripStyle2.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle2.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle2.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle2.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle2.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise
StripStyle2.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
StripStyle2.Font.Name = "Arial"
WorkSheet1.ItemStyles.Add(StripStyle1)
WorkSheet1.ItemStyles.Add(StripStyle2)
WorkSheet1.ItemType = Spire.DataExport.XLS.CellItemType.Col
WorkSheet1.Options.AggregateFormat.Font.Name = "Arial"
WorkSheet1.Options.CustomDataFormat.Font.Name = "Arial"
WorkSheet1.Options.DefaultFont.Name = "Arial"
WorkSheet1.Options.FooterFormat.Font.Name = "Arial"
WorkSheet1.Options.HeaderFormat.Font.Bold = True
WorkSheet1.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
WorkSheet1.Options.HeaderFormat.Font.Name = "Arial"
WorkSheet1.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
WorkSheet1.Options.HyperlinkFormat.Font.Name = "Arial"
WorkSheet1.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single
WorkSheet1.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
WorkSheet1.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
WorkSheet1.Options.NoteFormat.Font.Bold = True
WorkSheet1.Options.NoteFormat.Font.Name = "Tahoma"
WorkSheet1.Options.NoteFormat.Font.Size = 8.0!
WorkSheet1.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
WorkSheet1.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
WorkSheet1.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
WorkSheet1.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
WorkSheet1.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow
WorkSheet1.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
WorkSheet1.Options.TitlesFormat.Font.Bold = True
WorkSheet1.Options.TitlesFormat.Font.Name = "Arial"
WorkSheet1.SheetName = "parts"
WorkSheet1.SQLCommand = oleDbCommand1
WorkSheet1.StartDataCol = CType(0, Byte)
WorkSheet2.AutoFitColWidth = True
WorkSheet2.FormatsExport.CultureName = "zh-CN"
WorkSheet2.FormatsExport.Currency = "￥#,###,##0.00"
WorkSheet2.FormatsExport.DateTime = "yyyy-M-d H:mm"
WorkSheet2.FormatsExport.Float = "#,###,##0.00"
WorkSheet2.FormatsExport.Integer = "#,###,##0"
WorkSheet2.FormatsExport.Time = "H:mm"
StripStyle3.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle3.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle3.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle3.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle3.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen
StripStyle3.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
StripStyle3.Font.Name = "Arial"
StripStyle4.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle4.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle4.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle4.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
StripStyle4.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise
StripStyle4.FillStyle.Foreground = Spire.DataExport.XLS.CellColor.White
StripStyle4.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
StripStyle4.Font.Name = "Arial"
WorkSheet2.ItemStyles.Add(StripStyle3)
WorkSheet2.ItemStyles.Add(StripStyle4)
WorkSheet2.ItemType = Spire.DataExport.XLS.CellItemType.Col
WorkSheet2.Options.AggregateFormat.Font.Name = "Arial"
WorkSheet2.Options.CustomDataFormat.Font.Name = "Arial"
WorkSheet2.Options.DefaultFont.Name = "Arial"
WorkSheet2.Options.FooterFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
WorkSheet2.Options.FooterFormat.Font.Name = "Arial"
WorkSheet2.Options.HeaderFormat.Font.Bold = True
WorkSheet2.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
WorkSheet2.Options.HeaderFormat.Font.Name = "Arial"
WorkSheet2.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
WorkSheet2.Options.HyperlinkFormat.Font.Name = "Arial"
WorkSheet2.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single
WorkSheet2.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
WorkSheet2.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
WorkSheet2.Options.NoteFormat.Font.Bold = True
WorkSheet2.Options.NoteFormat.Font.Name = "Tahoma"
WorkSheet2.Options.NoteFormat.Font.Size = 8.0!
WorkSheet2.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
WorkSheet2.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
WorkSheet2.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
WorkSheet2.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
WorkSheet2.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow
WorkSheet2.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
WorkSheet2.Options.TitlesFormat.Font.Bold = True
WorkSheet2.Options.TitlesFormat.Font.Name = "Arial"
WorkSheet2.SheetName = "country"
WorkSheet2.SQLCommand = oleDbCommand2
WorkSheet2.StartDataCol = CType(0, Byte)
cellExport1.Sheets.Add(WorkSheet1)
cellExport1.Sheets.Add(WorkSheet2)
cellExport1.SQLCommand = oleDbCommand1
oleDbConnection1.Open()
Try
cellExport1.SaveToFile()
Finally
oleDbConnection1.Close()
End Try
End Sub
Private Sub cellExport1_GetDataParams(ByVal sender As Object, ByVal e As Spire.DataExport.EventArgs.DataParamsEventArgs) Handles cellExport1.GetDataParams
If (e.Sheet = 0) AndAlso (e.Col = 6) Then
e.FormatText = cellExport1.DataFormats.Currency
End If
End Sub
Published in
Cell