Spire.DataExport for .NET
Spire.DataExport for .NET (33)
Children categories
Not needing to have Microsoft Excel installed on the machine, The Spire.DataExport can create Excel spreadsheet. This sample demonstrates how to export data table into xls and create pie-3d chart.
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 cellExport2
= new Spire.DataExport.XLS.CellExport();
Spire.DataExport.XLS.WorkSheet workSheet3 = new Spire.DataExport.XLS.WorkSheet();
Spire.DataExport.XLS.Chart chart1 = new Spire.DataExport.XLS.Chart();
Spire.DataExport.XLS.ChartSeries chartSeries1 = new Spire.DataExport.XLS.ChartSeries();
Spire.DataExport.XLS.StripStyle stripStyle5 = new Spire.DataExport.XLS.StripStyle();
Spire.DataExport.XLS.StripStyle stripStyle6 = new Spire.DataExport.XLS.StripStyle();
cellExport2.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
cellExport2.AutoFitColWidth = true;
cellExport2.DataFormats.CultureName = "zh-CN";
cellExport2.DataFormats.Currency = "￥#,###,##0.00";
cellExport2.DataFormats.DateTime = "yyyy-M-d H:mm";
cellExport2.DataFormats.Float = "#,###,##0.00";
cellExport2.DataFormats.Integer = "#,###,##0";
cellExport2.DataFormats.Time = "H:mm";
cellExport2.FileName = "chart1.xls";
cellExport2.SheetOptions.AggregateFormat.Font.Name = "Arial";
cellExport2.SheetOptions.CustomDataFormat.Font.Name = "Arial";
cellExport2.SheetOptions.DefaultFont.Name = "Arial";
cellExport2.SheetOptions.FooterFormat.Font.Name = "Arial";
cellExport2.SheetOptions.HeaderFormat.Font.Name = "Arial";
cellExport2.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
cellExport2.SheetOptions.HyperlinkFormat.Font.Name = "Arial";
cellExport2.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
cellExport2.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
cellExport2.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
cellExport2.SheetOptions.NoteFormat.Font.Bold = true;
cellExport2.SheetOptions.NoteFormat.Font.Name = "Tahoma";
cellExport2.SheetOptions.NoteFormat.Font.Size = 8F;
cellExport2.SheetOptions.TitlesFormat.Font.Bold = true;
cellExport2.SheetOptions.TitlesFormat.Font.Name = "Arial";
workSheet3.AutoFitColWidth = true;
chart1.CategoryLabels.ColX = ((System.Byte)(1));
chart1.CategoryLabels.ColY = ((System.Byte)(1));
chart1.CategoryLabels.RowX = 1;
chart1.CategoryLabels.RowY = 9;
chart1.CategoryLabelsColumn = "Name";
chart1.DataRangeSheet = "charts";
chart1.Position.AutoPosition.Height = 12;
chart1.Position.AutoPosition.Top = 1;
chart1.Position.AutoPosition.Width = 6;
chartSeries1.DataColumn = "Population";
chartSeries1.DataRangeSheet = "charts";
chartSeries1.Title = "Population";
chart1.Series.Add(chartSeries1);
chart1.Style = Spire.DataExport.XLS.ChartStyle.Pie3d;
chart1.Title = "Chart demo";
workSheet3.Charts.Add(chart1);
workSheet3.FormatsExport.CultureName = "zh-CN";
workSheet3.FormatsExport.Currency = "￥#,###,##0.00";
workSheet3.FormatsExport.DateTime = "yyyy-M-d H:mm";
workSheet3.FormatsExport.Float = "#,###,##0.00";
workSheet3.FormatsExport.Integer = "#,###,##0";
workSheet3.FormatsExport.Time = "H:mm";
stripStyle5.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle5.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle5.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle5.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle5.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
stripStyle5.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
stripStyle5.Font.Name = "Arial";
stripStyle6.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle6.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle6.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle6.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle6.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise;
stripStyle6.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
stripStyle6.Font.Name = "Arial";
workSheet3.ItemStyles.Add(stripStyle5);
workSheet3.ItemStyles.Add(stripStyle6);
workSheet3.ItemType = Spire.DataExport.XLS.CellItemType.Col;
workSheet3.Options.AggregateFormat.Font.Name = "Arial";
workSheet3.Options.CustomDataFormat.Font.Name = "Arial";
workSheet3.Options.DefaultFont.Name = "Arial";
workSheet3.Options.FooterFormat.Font.Name = "Arial";
workSheet3.Options.HeaderFormat.Font.Bold = true;
workSheet3.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet3.Options.HeaderFormat.Font.Name = "Arial";
workSheet3.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet3.Options.HyperlinkFormat.Font.Name = "Arial";
workSheet3.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
workSheet3.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
workSheet3.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
workSheet3.Options.NoteFormat.Font.Bold = true;
workSheet3.Options.NoteFormat.Font.Name = "Tahoma";
workSheet3.Options.NoteFormat.Font.Size = 8F;
workSheet3.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet3.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet3.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet3.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet3.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow;
workSheet3.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
workSheet3.Options.TitlesFormat.Font.Bold = true;
workSheet3.Options.TitlesFormat.Font.Name = "Arial";
workSheet3.SheetName = "charts";
workSheet3.SQLCommand = oleDbCommand2;
workSheet3.StartDataCol = ((System.Byte)(0));
cellExport2.Sheets.Add(workSheet3);
cellExport2.SQLCommand = oleDbCommand1;
oleDbConnection1.Open();
try
{
cellExport2.SaveToFile();
}
finally
{
oleDbConnection1.Close();
}
}
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 cellExport2 As New Spire.DataExport.XLS.CellExport()
Dim workSheet3 As New Spire.DataExport.XLS.WorkSheet()
Dim chart1 As New Spire.DataExport.XLS.Chart()
Dim chartSeries1 As New Spire.DataExport.XLS.ChartSeries()
Dim stripStyle5 As New Spire.DataExport.XLS.StripStyle()
Dim stripStyle6 As New Spire.DataExport.XLS.StripStyle()
cellExport2.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
cellExport2.AutoFitColWidth = True
cellExport2.DataFormats.CultureName = "zh-CN"
cellExport2.DataFormats.Currency = "￥#,###,##0.00"
cellExport2.DataFormats.DateTime = "yyyy-M-d H:mm"
cellExport2.DataFormats.Float = "#,###,##0.00"
cellExport2.DataFormats.[Integer] = "#,###,##0"
cellExport2.DataFormats.Time = "H:mm"
cellExport2.FileName = "chart1.xls"
cellExport2.SheetOptions.AggregateFormat.Font.Name = "Arial"
cellExport2.SheetOptions.CustomDataFormat.Font.Name = "Arial"
cellExport2.SheetOptions.DefaultFont.Name = "Arial"
cellExport2.SheetOptions.FooterFormat.Font.Name = "Arial"
cellExport2.SheetOptions.HeaderFormat.Font.Name = "Arial"
cellExport2.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
cellExport2.SheetOptions.HyperlinkFormat.Font.Name = "Arial"
cellExport2.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single]
cellExport2.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
cellExport2.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
cellExport2.SheetOptions.NoteFormat.Font.Bold = True
cellExport2.SheetOptions.NoteFormat.Font.Name = "Tahoma"
cellExport2.SheetOptions.NoteFormat.Font.Size = 8.0F
cellExport2.SheetOptions.TitlesFormat.Font.Bold = True
cellExport2.SheetOptions.TitlesFormat.Font.Name = "Arial"
workSheet3.AutoFitColWidth = True
chart1.CategoryLabels.ColX = CByte(1)
chart1.CategoryLabels.ColY = CByte(1)
chart1.CategoryLabels.RowX = 1
chart1.CategoryLabels.RowY = 9
chart1.CategoryLabelsColumn = "Name"
chart1.DataRangeSheet = "charts"
chart1.Position.AutoPosition.Height = 12
chart1.Position.AutoPosition.Top = 1
chart1.Position.AutoPosition.Width = 6
chartSeries1.DataColumn = "Population"
chartSeries1.DataRangeSheet = "charts"
chartSeries1.Title = "Population"
chart1.Series.Add(chartSeries1)
chart1.Style = Spire.DataExport.XLS.ChartStyle.Pie3d
chart1.Title = "Chart demo"
workSheet3.Charts.Add(chart1)
workSheet3.FormatsExport.CultureName = "zh-CN"
workSheet3.FormatsExport.Currency = "￥#,###,##0.00"
workSheet3.FormatsExport.DateTime = "yyyy-M-d H:mm"
workSheet3.FormatsExport.Float = "#,###,##0.00"
workSheet3.FormatsExport.[Integer] = "#,###,##0"
workSheet3.FormatsExport.Time = "H:mm"
stripStyle5.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle5.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle5.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle5.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle5.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen
stripStyle5.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
stripStyle5.Font.Name = "Arial"
stripStyle6.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle6.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle6.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle6.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle6.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise
stripStyle6.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
stripStyle6.Font.Name = "Arial"
workSheet3.ItemStyles.Add(stripStyle5)
workSheet3.ItemStyles.Add(stripStyle6)
workSheet3.ItemType = Spire.DataExport.XLS.CellItemType.Col
workSheet3.Options.AggregateFormat.Font.Name = "Arial"
workSheet3.Options.CustomDataFormat.Font.Name = "Arial"
workSheet3.Options.DefaultFont.Name = "Arial"
workSheet3.Options.FooterFormat.Font.Name = "Arial"
workSheet3.Options.HeaderFormat.Font.Bold = True
workSheet3.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
workSheet3.Options.HeaderFormat.Font.Name = "Arial"
workSheet3.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
workSheet3.Options.HyperlinkFormat.Font.Name = "Arial"
workSheet3.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single]
workSheet3.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
workSheet3.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
workSheet3.Options.NoteFormat.Font.Bold = True
workSheet3.Options.NoteFormat.Font.Name = "Tahoma"
workSheet3.Options.NoteFormat.Font.Size = 8.0F
workSheet3.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
workSheet3.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
workSheet3.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
workSheet3.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
workSheet3.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow
workSheet3.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
workSheet3.Options.TitlesFormat.Font.Bold = True
workSheet3.Options.TitlesFormat.Font.Name = "Arial"
workSheet3.SheetName = "charts"
workSheet3.SQLCommand = oleDbCommand2
workSheet3.StartDataCol = CByte(0)
cellExport2.Sheets.Add(workSheet3)
cellExport2.SQLCommand = oleDbCommand1
oleDbConnection1.Open()
Try
cellExport2.SaveToFile()
Finally
oleDbConnection1.Close()
End Try
End Sub
Not needing to have Microsoft Excel installed on the machine, The Spire.DataExport can create Excel spreadsheet. This sample demonstrates how to add formulas into xls.
private void button1_Click(object sender, EventArgs e)
{
Spire.DataExport.XLS.CellExport cellExport5
= new Spire.DataExport.XLS.CellExport();
cellExport5.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
cellExport5.ColumnsWidth.AddRange(new object[] {
"100",
"200"});
cellExport5.DataExported = false;
cellExport5.DataFormats.CultureName = "zh-CN";
cellExport5.DataFormats.Currency = "￥#,###,##0.00";
cellExport5.DataFormats.DateTime = "yyyy-M-d H:mm";
cellExport5.DataFormats.Float = "#,###,##0.00";
cellExport5.DataFormats.Integer = "#,###,##0";
cellExport5.DataFormats.Time = "H:mm";
cellExport5.FileName = "formula.xls";
cellExport5.SheetOptions.AggregateFormat.Font.Name = "Arial";
cellExport5.SheetOptions.CustomDataFormat.Font.Name = "Arial";
cellExport5.SheetOptions.DefaultFont.Name = "Arial";
cellExport5.SheetOptions.FooterFormat.Font.Name = "Arial";
cellExport5.SheetOptions.HeaderFormat.Font.Name = "Arial";
cellExport5.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
cellExport5.SheetOptions.HyperlinkFormat.Font.Name = "Arial";
cellExport5.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
cellExport5.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
cellExport5.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
cellExport5.SheetOptions.NoteFormat.Font.Bold = true;
cellExport5.SheetOptions.NoteFormat.Font.Name = "Tahoma";
cellExport5.SheetOptions.NoteFormat.Font.Size = 8F;
cellExport5.SheetOptions.TitlesFormat.Font.Bold = true;
cellExport5.SheetOptions.TitlesFormat.Font.Name = "Arial";
ushort currentRow = 1;
string currentFormula = string.Empty;
Spire.DataExport.XLS.Cell cell = null;
cellExport5.Cells.Clear();
cellExport5.SetColumnWidth(1, 32);
cellExport5.SetColumnWidth(2, 16);
cellExport5.SetColumnWidth(3, 16);
cell = cellExport5.AddString(1, currentRow++, "Examples of formulas :");
cell.Format.Font.Bold = true;
cell.Format.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
cell.Format.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
cell.Format.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
cell = cellExport5.AddString(1, ++currentRow, "Test data:");
cell.Format.Font.Bold = true;
cell.Format.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
cell.Format.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
cell.Format.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
//test data
cellExport5.AddNumeric(2, currentRow, 7.3);
cellExport5.AddNumeric(3, currentRow, 5);
cellExport5.AddNumeric(4, currentRow, 8.2);
cellExport5.AddNumeric(5, currentRow, 4);
cellExport5.AddNumeric(6, currentRow, 3);
cellExport5.AddNumeric(7, currentRow++, 11.3);
cell = cellExport5.AddString(1, ++currentRow, "Formulas");
cell.Format.Font.Bold = true;
cell.Format.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
cell.Format.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
cell.Format.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
cell = cellExport5.AddString(2, currentRow, "Results");
cell.Format.Font.Bold = true;
cell.Format.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
cell.Format.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
cell.Format.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
//str.
currentFormula = "=\"hello\"";
cellExport5.AddString(1, ++currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow, "=\"hello\"");
cellExport5.AddFormula(3, currentRow, "=\"" + new string(new char[] { '\u4f60', '\u597d' }) + "\"");
//int.
currentFormula = "=300";
cellExport5.AddString(1, ++currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow, currentFormula);
// float
currentFormula = "=3389.639421";
cellExport5.AddString(1, ++currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow, currentFormula);
//bool.
currentFormula = "=false";
cellExport5.AddString(1, ++currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow, currentFormula);
currentFormula = "=1+2+3+4+5-6-7+8-9";
cellExport5.AddString(1, ++currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow, currentFormula);
currentFormula = "=33*3/4-2+10";
cellExport5.AddString(1, ++currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow, currentFormula);
// sheet reference
currentFormula = "=Sheet1!$B$3";
cellExport5.AddString(1, ++currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow, currentFormula);
// sheet area reference
currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)";
cellExport5.AddString(1, ++currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow, currentFormula);
// Functions
currentFormula = "=Count(3,5,8,10,2,34)";
cellExport5.AddString(1, ++currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=NOW()";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=SECOND(11)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=MINUTE(12)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=MONTH(9)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=DAY(10)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=TIME(4,5,7)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=DATE(6,4,2)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=RAND()";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=HOUR(12)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=TEXT(\"world\", \"$d\")";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=MOD(5,3)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=WEEKDAY(3)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=YEAR(23)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=NOT(true)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=OR(true)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=AND(TRUE)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=VALUE(30)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=LEN(\"world\")";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=MID(\"world\",4,2)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=ROUND(7,3)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=SIGN(4)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=INT(200)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=ABS(-1.21)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=LN(15)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=EXP(20)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=SQRT(40)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=PI()";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=COS(9)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=SIN(45)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=MAX(10,30)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=MIN(5,7)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=AVERAGE(12,45)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=SUM(18,29)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=IF(4,2,2)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
currentFormula = "=SUBTOTAL(3,Sheet1!B2:E3)";
cellExport5.AddString(1, currentRow, currentFormula);
cellExport5.AddFormula(2, currentRow++, currentFormula);
cellExport5.SaveToFile();
}
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cellExport5 As Spire.DataExport.XLS.CellExport
cellExport5 = New Spire.DataExport.XLS.CellExport
cellExport5.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
cellExport5.ColumnsWidth.AddRange(New Object() {"100", "200"})
cellExport5.DataExported = False
cellExport5.DataFormats.CultureName = "zh-CN"
cellExport5.DataFormats.Currency = "￥#,###,##0.00"
cellExport5.DataFormats.DateTime = "yyyy-M-d H:mm"
cellExport5.DataFormats.Float = "#,###,##0.00"
cellExport5.DataFormats.Integer = "#,###,##0"
cellExport5.DataFormats.Time = "H:mm"
cellExport5.FileName = "formula.xls"
cellExport5.SheetOptions.AggregateFormat.Font.Name = "Arial"
cellExport5.SheetOptions.CustomDataFormat.Font.Name = "Arial"
cellExport5.SheetOptions.DefaultFont.Name = "Arial"
cellExport5.SheetOptions.FooterFormat.Font.Name = "Arial"
cellExport5.SheetOptions.HeaderFormat.Font.Name = "Arial"
cellExport5.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
cellExport5.SheetOptions.HyperlinkFormat.Font.Name = "Arial"
cellExport5.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single
cellExport5.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
cellExport5.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
cellExport5.SheetOptions.NoteFormat.Font.Bold = True
cellExport5.SheetOptions.NoteFormat.Font.Name = "Tahoma"
cellExport5.SheetOptions.NoteFormat.Font.Size = 8.0!
cellExport5.SheetOptions.TitlesFormat.Font.Bold = True
cellExport5.SheetOptions.TitlesFormat.Font.Name = "Arial"
Dim currentRow As Integer = 1
Dim firstCol As UInt16 = Convert.ToUInt16(1)
Dim secondCol As UInt16 = Convert.ToUInt16(2)
Dim currentFormula As String = String.Empty
Dim cell As cell = Nothing
cellExport5.Cells.Clear()
cellExport5.SetColumnWidth(System.Convert.ToUInt16(1), System.Convert.ToUInt16(32))
cellExport5.SetColumnWidth(System.Convert.ToUInt16(2), System.Convert.ToUInt16(16))
cellExport5.SetColumnWidth(System.Convert.ToUInt16(3), System.Convert.ToUInt16(16))
cell = cellExport5.AddString(System.Convert.ToUInt16(1), System.Convert.ToUInt16(currentRow), "Examples of formulas :")
currentRow += 1
cell.Format.Font.Bold = True
cell.Format.FillStyle.Background = CellColor.LightGreen
cell.Format.FillStyle.Pattern = Pattern.Solid
cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium
cell = cellExport5.AddString(System.Convert.ToUInt16(1), System.Convert.ToUInt16(currentRow), "Test data:")
currentRow += 1
cell.Format.Font.Bold = True
cell.Format.FillStyle.Background = CellColor.LightGreen
cell.Format.FillStyle.Pattern = Pattern.Solid
cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium
'test data
cellExport5.AddNumeric(System.Convert.ToUInt16(2), System.Convert.ToUInt16(currentRow), 7.3)
cellExport5.AddNumeric(System.Convert.ToUInt16(3), System.Convert.ToUInt16(currentRow), 5)
cellExport5.AddNumeric(System.Convert.ToUInt16(4), System.Convert.ToUInt16(currentRow), 8.2)
cellExport5.AddNumeric(System.Convert.ToUInt16(5), System.Convert.ToUInt16(currentRow), 4)
cellExport5.AddNumeric(System.Convert.ToUInt16(6), System.Convert.ToUInt16(currentRow), 3)
cellExport5.AddNumeric(System.Convert.ToUInt16(7), System.Convert.ToUInt16(currentRow), 11.3)
currentRow += 1
currentRow += 1
cell = cellExport5.AddString(Convert.ToUInt16(1), Convert.ToUInt16(currentRow), "Formulas")
cell.Format.Font.Bold = True
cell.Format.FillStyle.Background = CellColor.LightGreen
cell.Format.FillStyle.Pattern = Pattern.Solid
cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium
cell = cellExport5.AddString(secondCol, Convert.ToUInt16(currentRow), "Results")
currentRow += 1
cell.Format.Font.Bold = True
cell.Format.FillStyle.Background = CellColor.LightGreen
cell.Format.FillStyle.Pattern = Pattern.Solid
cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium
'str.
currentFormula = "=""hello"""
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), "=""hello""")
cellExport5.AddFormula(Convert.ToUInt16(3), Convert.ToUInt16(currentRow), "=""" & ChrW(20320) & ChrW(22909) + """")
currentRow += 1
'int.
currentFormula = "=300"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
' float
currentFormula = "=3389.639421"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
'bool.
currentFormula = "=false"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=1+2+3+4+5-6-7+8-9"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=33*3/4-2+10"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
' sheet reference
currentFormula = "=Sheet1!$B$3"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
' sheet area reference
currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
' Functions
currentFormula = "=Count(3,5,8,10,2,34)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=NOW()"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=SECOND(11)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=MINUTE(12)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=MONTH(9)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=DAY(10)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=TIME(4,5,7)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=DATE(6,4,2)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=RAND()"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=HOUR(12)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=TEXT(""world"", ""$d"")"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=MOD(5,3)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=WEEKDAY(3)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=YEAR(23)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=NOT(true)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=OR(true)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=AND(TRUE)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=VALUE(30)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=LEN(""world"")"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=MID(""world"",4,2)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=ROUND(7,3)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=SIGN(4)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=INT(200)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=ABS(-1.21)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=LN(15)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=EXP(20)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=SQRT(40)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=PI()"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=COS(9)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=SIN(45)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=MAX(10,30)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=MIN(5,7)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=AVERAGE(12,45)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=SUM(18,29)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=IF(4,2,2)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
currentFormula = "=SUBTOTAL(3,Sheet1!B2:E3)"
cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula)
cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula)
currentRow += 1
cellExport5.SaveToFile()
End Sub
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
Tagged under
page 5
