Sunday, 01 August 2010 18:05
Data Export DBF in C#, VB.NET
This sample demonstrates how to export data table into dbf file.
The picture above represents using Excel application to open the result dbf file.
private void btnDBF_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;
Spire.DataExport.DBF.DBFExport dbfExport1
= new Spire.DataExport.DBF.DBFExport();
dbfExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
dbfExport1.FileName = "sample.dbf";
dbfExport1.SQLCommand = oleDbCommand1;
oleDbConnection1.Open();
dbfExport1.SaveToFile();
}
Private Sub btnDBF_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDBF.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 dbfExport1 As New Spire.DataExport.DBF.DBFExport()
dbfExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
dbfExport1.FileName = "sample.dbf"
dbfExport1.SQLCommand = oleDbCommand1
oleDbConnection1.Open()
dbfExport1.SaveToFile()
End Sub
Published in
Demo
Sunday, 01 August 2010 17:53
Data Export Clipboard in C#, VB.NET
This sample demonstrates how to export data table into clipboard.
private void btnClipboard_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;
Spire.DataExport.Clipboard.ClipboardExport clipboardExport1
= new Spire.DataExport.Clipboard.ClipboardExport();
clipboardExport1.DataFormats.CultureName = "zh-CN";
clipboardExport1.DataFormats.Currency = "c";
clipboardExport1.DataFormats.DateTime = "yyyy-M-d H:mm";
clipboardExport1.DataFormats.Float = "g";
clipboardExport1.DataFormats.Integer = "g";
clipboardExport1.DataFormats.Time = "H:mm";
clipboardExport1.ShowContent = true;
clipboardExport1.SQLCommand = oleDbCommand1;
oleDbConnection1.Open();
clipboardExport1.SaveToFile();
}
Private Sub btnClipboard_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClipboard.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 clipboardExport1 As New Spire.DataExport.Clipboard.ClipboardExport()
clipboardExport1.DataFormats.CultureName = "zh-CN"
clipboardExport1.DataFormats.Currency = "c"
clipboardExport1.DataFormats.DateTime = "yyyy-M-d H:mm"
clipboardExport1.DataFormats.Float = "g"
clipboardExport1.DataFormats.[Integer] = "g"
clipboardExport1.DataFormats.Time = "H:mm"
clipboardExport1.ShowContent = True
clipboardExport1.SQLCommand = oleDbCommand1
oleDbConnection1.Open()
clipboardExport1.SaveToFile()
End Sub
Published in
Demo
Sunday, 01 August 2010 17:47
Data Export RTF in C#, VB.NET
This sample demonstrates how to export data table into rtf file.
private void btnRTF_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;
Spire.DataExport.RTF.RTFExport rtfExport1
= new Spire.DataExport.RTF.RTFExport();
rtfExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
rtfExport1.DataFormats.CultureName = "zh-CN";
rtfExport1.DataFormats.Currency = "c";
rtfExport1.DataFormats.DateTime = "yyyy-M-d H:mm";
rtfExport1.DataFormats.Float = "g";
rtfExport1.DataFormats.Integer = "g";
rtfExport1.DataFormats.Time = "H:mm";
rtfExport1.FileName = "sample.doc";
rtfExport1.RTFOptions.DataStyle.Font = new System.Drawing.Font("Arial", 10F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World);
rtfExport1.RTFOptions.FooterStyle.Font = new System.Drawing.Font("Arial", 10F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World);
rtfExport1.RTFOptions.HeaderStyle.Font = new System.Drawing.Font("Arial", 10F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World);
rtfExport1.RTFOptions.TitleStyle.Alignment = Spire.DataExport.RTF.RtfTextAlignment.Center;
rtfExport1.RTFOptions.TitleStyle.Font = new System.Drawing.Font("Arial", 10F, System.Drawing.FontStyle.Bold);
rtfExport1.SQLCommand = oleDbCommand1;
oleDbConnection1.Open();
rtfExport1.SaveToFile();
}
Private Sub btnRTF_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRTF.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 rtfExport1 As New Spire.DataExport.RTF.RTFExport()
rtfExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
rtfExport1.DataFormats.CultureName = "zh-CN"
rtfExport1.DataFormats.Currency = "c"
rtfExport1.DataFormats.DateTime = "yyyy-M-d H:mm"
rtfExport1.DataFormats.Float = "g"
rtfExport1.DataFormats.[Integer] = "g"
rtfExport1.DataFormats.Time = "H:mm"
rtfExport1.FileName = "sample.doc"
rtfExport1.RTFOptions.DataStyle.Font = New System.Drawing.Font("Arial", 10.0F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World)
rtfExport1.RTFOptions.FooterStyle.Font = New System.Drawing.Font("Arial", 10.0F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World)
rtfExport1.RTFOptions.HeaderStyle.Font = New System.Drawing.Font("Arial", 10.0F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World)
rtfExport1.RTFOptions.TitleStyle.Alignment = Spire.DataExport.RTF.RtfTextAlignment.Center
rtfExport1.RTFOptions.TitleStyle.Font = New System.Drawing.Font("Arial", 10.0F, System.Drawing.FontStyle.Bold)
rtfExport1.SQLCommand = oleDbCommand1
oleDbConnection1.Open()
rtfExport1.SaveToFile()
End Sub
Published in
Demo
Sunday, 01 August 2010 17:22
Data Export PDF in C#, VB.NET
This sample demonstrates how to export data table into pdf file.
private void btnPDF_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;
Spire.DataExport.PDF.PDFExport pdfExport1 = new Spire.DataExport.PDF.PDFExport();
pdfExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
pdfExport1.DataFormats.CultureName = "zh-CN";
pdfExport1.DataFormats.Currency = "c";
pdfExport1.DataFormats.DateTime = "yyyy-M-d H:mm";
pdfExport1.DataFormats.Float = "g";
pdfExport1.DataFormats.Integer = "g";
pdfExport1.DataFormats.Time = "H:mm";
pdfExport1.FileName = "sample.pdf";
pdfExport1.PDFOptions.DataFont.CustomFont = new System.Drawing.Font("Arial", 10F);
pdfExport1.PDFOptions.FooterFont.CustomFont = new System.Drawing.Font("Arial", 10F);
pdfExport1.PDFOptions.HeaderFont.CustomFont = new System.Drawing.Font("Arial", 10F);
pdfExport1.PDFOptions.PageOptions.Format = Spire.DataExport.PDF.PageFormat.User;
pdfExport1.PDFOptions.PageOptions.Height = 11.67;
pdfExport1.PDFOptions.PageOptions.MarginBottom = 0.78;
pdfExport1.PDFOptions.PageOptions.MarginLeft = 1.17;
pdfExport1.PDFOptions.PageOptions.MarginRight = 0.57;
pdfExport1.PDFOptions.PageOptions.MarginTop = 0.78;
pdfExport1.PDFOptions.PageOptions.Width = 10.25;
pdfExport1.PDFOptions.TitleFont.CustomFont = new System.Drawing.Font("Arial", 10F);
pdfExport1.SQLCommand = oleDbCommand1;
oleDbConnection1.Open();
pdfExport1.SaveToFile();
}
Private Sub btnPDF_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPDF.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 pdfExport1 As New Spire.DataExport.PDF.PDFExport()
pdfExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
pdfExport1.DataFormats.CultureName = "zh-CN"
pdfExport1.DataFormats.Currency = "c"
pdfExport1.DataFormats.DateTime = "yyyy-M-d H:mm"
pdfExport1.DataFormats.Float = "g"
pdfExport1.DataFormats.[Integer] = "g"
pdfExport1.DataFormats.Time = "H:mm"
pdfExport1.FileName = "sample.pdf"
pdfExport1.PDFOptions.DataFont.CustomFont = New System.Drawing.Font("Arial", 10.0F)
pdfExport1.PDFOptions.FooterFont.CustomFont = New System.Drawing.Font("Arial", 10.0F)
pdfExport1.PDFOptions.HeaderFont.CustomFont = New System.Drawing.Font("Arial", 10.0F)
pdfExport1.PDFOptions.PageOptions.Format = Spire.DataExport.PDF.PageFormat.User
pdfExport1.PDFOptions.PageOptions.Height = 11.67
pdfExport1.PDFOptions.PageOptions.MarginBottom = 0.78
pdfExport1.PDFOptions.PageOptions.MarginLeft = 1.17
pdfExport1.PDFOptions.PageOptions.MarginRight = 0.57
pdfExport1.PDFOptions.PageOptions.MarginTop = 0.78
pdfExport1.PDFOptions.PageOptions.Width = 10.25
pdfExport1.PDFOptions.TitleFont.CustomFont = New System.Drawing.Font("Arial", 10.0F)
pdfExport1.SQLCommand = oleDbCommand1
oleDbConnection1.Open()
pdfExport1.SaveToFile()
End Sub
Published in
Demo
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 16:21
Data Export Bar Chart in C#, VB.NET
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 bar 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 cellExport4
= new Spire.DataExport.XLS.CellExport();
Spire.DataExport.XLS.WorkSheet workSheet4 = new Spire.DataExport.XLS.WorkSheet();
Spire.DataExport.XLS.Chart chart2 = new Spire.DataExport.XLS.Chart();
Spire.DataExport.XLS.ChartSeries chartSeries2 = new Spire.DataExport.XLS.ChartSeries();
Spire.DataExport.XLS.WorkSheet workSheet5 = new Spire.DataExport.XLS.WorkSheet();
Spire.DataExport.XLS.StripStyle stripStyle7 = new Spire.DataExport.XLS.StripStyle();
Spire.DataExport.XLS.StripStyle stripStyle8 = new Spire.DataExport.XLS.StripStyle();
cellExport4.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
cellExport4.AutoFitColWidth = true;
cellExport4.DataFormats.CultureName = "zh-CN";
cellExport4.DataFormats.Currency = "￥#,###,##0.00";
cellExport4.DataFormats.DateTime = "yyyy-M-d H:mm";
cellExport4.DataFormats.Float = "#,###,##0.00";
cellExport4.DataFormats.Integer = "#,###,##0";
cellExport4.DataFormats.Time = "H:mm";
cellExport4.FileName = "chart2.xls";
cellExport4.SheetOptions.AggregateFormat.Font.Name = "Arial";
cellExport4.SheetOptions.CustomDataFormat.Font.Name = "Arial";
cellExport4.SheetOptions.DefaultFont.Name = "Arial";
cellExport4.SheetOptions.FooterFormat.Font.Name = "Arial";
cellExport4.SheetOptions.HeaderFormat.Font.Name = "Arial";
cellExport4.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
cellExport4.SheetOptions.HyperlinkFormat.Font.Name = "Arial";
cellExport4.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
cellExport4.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
cellExport4.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
cellExport4.SheetOptions.NoteFormat.Font.Bold = true;
cellExport4.SheetOptions.NoteFormat.Font.Name = "Tahoma";
cellExport4.SheetOptions.NoteFormat.Font.Size = 8F;
cellExport4.SheetOptions.TitlesFormat.Font.Bold = true;
cellExport4.SheetOptions.TitlesFormat.Font.Name = "Arial";
chart2.AutoColor = false;
chart2.CategoryLabels.ColX = ((System.Byte)(1));
chart2.CategoryLabels.ColY = ((System.Byte)(1));
chart2.CategoryLabels.RowX = 1;
chart2.CategoryLabels.RowY = 9;
chart2.CategoryLabelsColumn = "Name";
chart2.DataRangeSheet = "Sheet 2";
chart2.Position.AutoPosition.Height = 23;
chart2.Position.AutoPosition.Left = 1;
chart2.Position.AutoPosition.Top = 1;
chart2.Position.AutoPosition.Width = 11;
chart2.Position.CustomPosition.X1 = ((System.Byte)(1));
chart2.Position.CustomPosition.X2 = ((System.Byte)(15));
chart2.Position.CustomPosition.Y1 = 1;
chart2.Position.CustomPosition.Y2 = 30;
chartSeries2.Color = Spire.DataExport.XLS.CellColor.Tan;
chartSeries2.DataColumn = "Area";
chartSeries2.DataRangeSheet = "Sheet 2";
chartSeries2.Title = "Population";
chart2.Series.Add(chartSeries2);
chart2.Style = Spire.DataExport.XLS.ChartStyle.Bar;
chart2.Title = "Chart demo";
workSheet4.Charts.Add(chart2);
workSheet4.DataExported = false;
workSheet4.FormatsExport.CultureName = "zh-CN";
workSheet4.FormatsExport.Currency = "￥#,###,##0.00";
workSheet4.FormatsExport.DateTime = "yyyy-M-d H:mm";
workSheet4.FormatsExport.Float = "#,###,##0.00";
workSheet4.FormatsExport.Integer = "#,###,##0";
workSheet4.FormatsExport.Time = "H:mm";
workSheet4.Options.AggregateFormat.Font.Name = "Arial";
workSheet4.Options.CustomDataFormat.Font.Name = "Arial";
workSheet4.Options.DefaultFont.Name = "Arial";
workSheet4.Options.FooterFormat.Font.Name = "Arial";
workSheet4.Options.HeaderFormat.Font.Bold = true;
workSheet4.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet4.Options.HeaderFormat.Font.Name = "Arial";
workSheet4.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet4.Options.HyperlinkFormat.Font.Name = "Arial";
workSheet4.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
workSheet4.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
workSheet4.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
workSheet4.Options.NoteFormat.Font.Bold = true;
workSheet4.Options.NoteFormat.Font.Name = "Tahoma";
workSheet4.Options.NoteFormat.Font.Size = 8F;
workSheet4.Options.TitlesFormat.Font.Bold = true;
workSheet4.Options.TitlesFormat.Font.Name = "Times New Roman";
workSheet4.Options.TitlesFormat.Font.Size = 13F;
workSheet4.SheetName = "charts";
workSheet4.StartDataCol = ((System.Byte)(0));
workSheet5.AutoFitColWidth = true;
workSheet5.FormatsExport.CultureName = "zh-CN";
workSheet5.FormatsExport.Currency = "￥#,###,##0.00";
workSheet5.FormatsExport.DateTime = "yyyy-M-d H:mm";
workSheet5.FormatsExport.Float = "#,###,##0.00";
workSheet5.FormatsExport.Integer = "#,###,##0";
workSheet5.FormatsExport.Time = "H:mm";
stripStyle7.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle7.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle7.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle7.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle7.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
stripStyle7.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
stripStyle7.Font.Name = "Arial";
stripStyle8.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle8.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle8.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle8.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
stripStyle8.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise;
stripStyle8.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
stripStyle8.Font.Name = "Arial";
workSheet5.ItemStyles.Add(stripStyle7);
workSheet5.ItemStyles.Add(stripStyle8);
workSheet5.ItemType = Spire.DataExport.XLS.CellItemType.Col;
workSheet5.Options.AggregateFormat.Font.Name = "Arial";
workSheet5.Options.CustomDataFormat.Font.Name = "Arial";
workSheet5.Options.DefaultFont.Name = "Arial";
workSheet5.Options.FooterFormat.Font.Name = "Arial";
workSheet5.Options.HeaderFormat.Font.Name = "Arial";
workSheet5.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet5.Options.HyperlinkFormat.Font.Name = "Arial";
workSheet5.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
workSheet5.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
workSheet5.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
workSheet5.Options.NoteFormat.Font.Bold = true;
workSheet5.Options.NoteFormat.Font.Name = "Tahoma";
workSheet5.Options.NoteFormat.Font.Size = 8F;
workSheet5.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet5.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet5.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet5.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
workSheet5.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow;
workSheet5.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
workSheet5.Options.TitlesFormat.Font.Bold = true;
workSheet5.Options.TitlesFormat.Font.Name = "Arial";
workSheet5.SheetName = "Sheet 2";
workSheet5.SQLCommand = oleDbCommand2;
workSheet5.StartDataCol = ((System.Byte)(0));
cellExport4.Sheets.Add(workSheet4);
cellExport4.Sheets.Add(workSheet5);
cellExport4.SQLCommand = oleDbCommand1;
oleDbConnection1.Open();
try
{
cellExport4.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 cellExport4 As New Spire.DataExport.XLS.CellExport()
Dim workSheet4 As New Spire.DataExport.XLS.WorkSheet()
Dim chart2 As New Spire.DataExport.XLS.Chart()
Dim chartSeries2 As New Spire.DataExport.XLS.ChartSeries()
Dim workSheet5 As New Spire.DataExport.XLS.WorkSheet()
Dim stripStyle7 As New Spire.DataExport.XLS.StripStyle()
Dim stripStyle8 As New Spire.DataExport.XLS.StripStyle()
cellExport4.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
cellExport4.AutoFitColWidth = True
cellExport4.DataFormats.CultureName = "zh-CN"
cellExport4.DataFormats.Currency = "￥#,###,##0.00"
cellExport4.DataFormats.DateTime = "yyyy-M-d H:mm"
cellExport4.DataFormats.Float = "#,###,##0.00"
cellExport4.DataFormats.[Integer] = "#,###,##0"
cellExport4.DataFormats.Time = "H:mm"
cellExport4.FileName = "chart2.xls"
cellExport4.SheetOptions.AggregateFormat.Font.Name = "Arial"
cellExport4.SheetOptions.CustomDataFormat.Font.Name = "Arial"
cellExport4.SheetOptions.DefaultFont.Name = "Arial"
cellExport4.SheetOptions.FooterFormat.Font.Name = "Arial"
cellExport4.SheetOptions.HeaderFormat.Font.Name = "Arial"
cellExport4.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
cellExport4.SheetOptions.HyperlinkFormat.Font.Name = "Arial"
cellExport4.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single]
cellExport4.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
cellExport4.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
cellExport4.SheetOptions.NoteFormat.Font.Bold = True
cellExport4.SheetOptions.NoteFormat.Font.Name = "Tahoma"
cellExport4.SheetOptions.NoteFormat.Font.Size = 8.0F
cellExport4.SheetOptions.TitlesFormat.Font.Bold = True
cellExport4.SheetOptions.TitlesFormat.Font.Name = "Arial"
chart2.AutoColor = False
chart2.CategoryLabels.ColX = CByte(1)
chart2.CategoryLabels.ColY = CByte(1)
chart2.CategoryLabels.RowX = 1
chart2.CategoryLabels.RowY = 9
chart2.CategoryLabelsColumn = "Name"
chart2.DataRangeSheet = "Sheet 2"
chart2.Position.AutoPosition.Height = 23
chart2.Position.AutoPosition.Left = 1
chart2.Position.AutoPosition.Top = 1
chart2.Position.AutoPosition.Width = 11
chart2.Position.CustomPosition.X1 = CByte(1)
chart2.Position.CustomPosition.X2 = CByte(15)
chart2.Position.CustomPosition.Y1 = 1
chart2.Position.CustomPosition.Y2 = 30
chartSeries2.Color = Spire.DataExport.XLS.CellColor.Tan
chartSeries2.DataColumn = "Area"
chartSeries2.DataRangeSheet = "Sheet 2"
chartSeries2.Title = "Population"
chart2.Series.Add(chartSeries2)
chart2.Style = Spire.DataExport.XLS.ChartStyle.Bar
chart2.Title = "Chart demo"
workSheet4.Charts.Add(chart2)
workSheet4.DataExported = False
workSheet4.FormatsExport.CultureName = "zh-CN"
workSheet4.FormatsExport.Currency = "￥#,###,##0.00"
workSheet4.FormatsExport.DateTime = "yyyy-M-d H:mm"
workSheet4.FormatsExport.Float = "#,###,##0.00"
workSheet4.FormatsExport.[Integer] = "#,###,##0"
workSheet4.FormatsExport.Time = "H:mm"
workSheet4.Options.AggregateFormat.Font.Name = "Arial"
workSheet4.Options.CustomDataFormat.Font.Name = "Arial"
workSheet4.Options.DefaultFont.Name = "Arial"
workSheet4.Options.FooterFormat.Font.Name = "Arial"
workSheet4.Options.HeaderFormat.Font.Bold = True
workSheet4.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
workSheet4.Options.HeaderFormat.Font.Name = "Arial"
workSheet4.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
workSheet4.Options.HyperlinkFormat.Font.Name = "Arial"
workSheet4.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single]
workSheet4.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
workSheet4.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
workSheet4.Options.NoteFormat.Font.Bold = True
workSheet4.Options.NoteFormat.Font.Name = "Tahoma"
workSheet4.Options.NoteFormat.Font.Size = 8.0F
workSheet4.Options.TitlesFormat.Font.Bold = True
workSheet4.Options.TitlesFormat.Font.Name = "Times New Roman"
workSheet4.Options.TitlesFormat.Font.Size = 13.0F
workSheet4.SheetName = "charts"
workSheet4.StartDataCol = CByte(0)
workSheet5.AutoFitColWidth = True
workSheet5.FormatsExport.CultureName = "zh-CN"
workSheet5.FormatsExport.Currency = "￥#,###,##0.00"
workSheet5.FormatsExport.DateTime = "yyyy-M-d H:mm"
workSheet5.FormatsExport.Float = "#,###,##0.00"
workSheet5.FormatsExport.[Integer] = "#,###,##0"
workSheet5.FormatsExport.Time = "H:mm"
stripStyle7.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle7.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle7.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle7.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle7.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen
stripStyle7.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
stripStyle7.Font.Name = "Arial"
stripStyle8.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle8.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle8.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle8.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
stripStyle8.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise
stripStyle8.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
stripStyle8.Font.Name = "Arial"
workSheet5.ItemStyles.Add(stripStyle7)
workSheet5.ItemStyles.Add(stripStyle8)
workSheet5.ItemType = Spire.DataExport.XLS.CellItemType.Col
workSheet5.Options.AggregateFormat.Font.Name = "Arial"
workSheet5.Options.CustomDataFormat.Font.Name = "Arial"
workSheet5.Options.DefaultFont.Name = "Arial"
workSheet5.Options.FooterFormat.Font.Name = "Arial"
workSheet5.Options.HeaderFormat.Font.Name = "Arial"
workSheet5.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue
workSheet5.Options.HyperlinkFormat.Font.Name = "Arial"
workSheet5.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single]
workSheet5.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left
workSheet5.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top
workSheet5.Options.NoteFormat.Font.Bold = True
workSheet5.Options.NoteFormat.Font.Name = "Tahoma"
workSheet5.Options.NoteFormat.Font.Size = 8.0F
workSheet5.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
workSheet5.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
workSheet5.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
workSheet5.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
workSheet5.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow
workSheet5.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
workSheet5.Options.TitlesFormat.Font.Bold = True
workSheet5.Options.TitlesFormat.Font.Name = "Arial"
workSheet5.SheetName = "Sheet 2"
workSheet5.SQLCommand = oleDbCommand2
workSheet5.StartDataCol = CByte(0)
cellExport4.Sheets.Add(workSheet4)
cellExport4.Sheets.Add(workSheet5)
cellExport4.SQLCommand = oleDbCommand1
oleDbConnection1.Open()
Try
cellExport4.SaveToFile()
Finally
oleDbConnection1.Close()
End Try
End Sub
Published in
Cell
Sunday, 01 August 2010 16:02
Data Export Pie3D Chart in C#, VB.NET
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
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
Wednesday, 28 July 2010 07:18
Data Export Html in C#, VB.NET
This sample demonstrates how to export data table to HTML.
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;
Spire.DataExport.HTML.HTMLExport htmlExport1
= new Spire.DataExport.HTML.HTMLExport();
htmlExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
htmlExport1.DataFormats.CultureName = "zh-CN";
htmlExport1.DataFormats.Currency = "c";
htmlExport1.DataFormats.DateTime = "yyyy-M-d H:mm";
htmlExport1.DataFormats.Float = "g";
htmlExport1.DataFormats.Integer = "g";
htmlExport1.DataFormats.Time = "H:mm";
htmlExport1.FileName = "test.html";
htmlExport1.HtmlTableOptions.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(0)), ((System.Byte)(122)), ((System.Byte)(236)));
htmlExport1.HtmlTableOptions.FontColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), ((System.Byte)(255)), ((System.Byte)(255)));
htmlExport1.HtmlTableOptions.HeadersBackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), ((System.Byte)(0)), ((System.Byte)(0)));
htmlExport1.HtmlTableOptions.HeadersFontColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), ((System.Byte)(255)), ((System.Byte)(255)));
htmlExport1.HtmlTableOptions.OddBackColor = System.Drawing.Color.FromArgb(((System.Byte)(0)), ((System.Byte)(107)), ((System.Byte)(206)));
htmlExport1.HtmlTextOptions.BackgroundColor = System.Drawing.Color.FromArgb(((System.Byte)(51)), ((System.Byte)(51)), ((System.Byte)(153)));
htmlExport1.HtmlTextOptions.Font = new System.Drawing.Font("Arial", 8F);
htmlExport1.HtmlTextOptions.FontColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), ((System.Byte)(255)), ((System.Byte)(255)));
htmlExport1.HtmlTextOptions.LinkActiveColor = System.Drawing.Color.FromArgb(((System.Byte)(0)), ((System.Byte)(255)), ((System.Byte)(0)));
htmlExport1.HtmlTextOptions.LinkColor = System.Drawing.Color.FromArgb(((System.Byte)(105)), ((System.Byte)(239)), ((System.Byte)(125)));
htmlExport1.HtmlTextOptions.LinkVisitedColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), ((System.Byte)(0)), ((System.Byte)(255)));
htmlExport1.SQLCommand = oleDbCommand1;
oleDbConnection1.Open();
try
{
htmlExport1.HtmlStyle = Spire.DataExport.HTML.HtmlStyle.Plain;
htmlExport1.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 htmlExport1 As New Spire.DataExport.HTML.HTMLExport()
htmlExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
htmlExport1.DataFormats.CultureName = "zh-CN"
htmlExport1.DataFormats.Currency = "c"
htmlExport1.DataFormats.DateTime = "yyyy-M-d H:mm"
htmlExport1.DataFormats.Float = "g"
htmlExport1.DataFormats.[Integer] = "g"
htmlExport1.DataFormats.Time = "H:mm"
htmlExport1.FileName = "test.html"
htmlExport1.HtmlTableOptions.BackColor = System.Drawing.Color.FromArgb(CByte(0), CByte(122), CByte(236))
htmlExport1.HtmlTableOptions.FontColor = System.Drawing.Color.FromArgb(CByte(255), CByte(255), CByte(255))
htmlExport1.HtmlTableOptions.HeadersBackColor = System.Drawing.Color.FromArgb(CByte(255), CByte(0), CByte(0))
htmlExport1.HtmlTableOptions.HeadersFontColor = System.Drawing.Color.FromArgb(CByte(255), CByte(255), CByte(255))
htmlExport1.HtmlTableOptions.OddBackColor = System.Drawing.Color.FromArgb(CByte(0), CByte(107), CByte(206))
htmlExport1.HtmlTextOptions.BackgroundColor = System.Drawing.Color.FromArgb(CByte(51), CByte(51), CByte(153))
htmlExport1.HtmlTextOptions.Font = New System.Drawing.Font("Arial", 8.0F)
htmlExport1.HtmlTextOptions.FontColor = System.Drawing.Color.FromArgb(CByte(255), CByte(255), CByte(255))
htmlExport1.HtmlTextOptions.LinkActiveColor = System.Drawing.Color.FromArgb(CByte(0), CByte(255), CByte(0))
htmlExport1.HtmlTextOptions.LinkColor = System.Drawing.Color.FromArgb(CByte(105), CByte(239), CByte(125))
htmlExport1.HtmlTextOptions.LinkVisitedColor = System.Drawing.Color.FromArgb(CByte(255), CByte(0), CByte(255))
htmlExport1.SQLCommand = oleDbCommand1
oleDbConnection1.Open()
Try
htmlExport1.HtmlStyle = Spire.DataExport.HTML.HtmlStyle.Plain
htmlExport1.SaveToFile()
Finally
oleDbConnection1.Close()
End Try
End Sub
Published in
Html
Wednesday, 28 July 2010 07:05
Data Export Cell in C#, VB.NET
Not needing to have Microsoft Excel installed on the machine, The Spire.DataExport can create Excel spreadsheet.
private void btnRun_Click(object sender, System.EventArgs e)
{
oleDbConnection1.Open();
try
{
cellExport1.SaveToFile();
}
finally
{
this.oleDbConnection1.Close();
}
}
private void Form1_Load(object sender, System.EventArgs e)
{
LoadData();
}
private void button2_Click(object sender, System.EventArgs e)
{
LoadData();
}
private void btnAbout_Click(object sender, System.EventArgs e)
{
Close();
}
private void button1_Click(object sender, System.EventArgs e)
{
oleDbConnection1.Open();
try
{
cellExport2.SaveToFile();
}
finally
{
this.oleDbConnection1.Close();
}
}
private void button3_Click(object sender, System.EventArgs e)
{
oleDbConnection1.Open();
try
{
cellExport3.SaveToFile();
}
finally
{
this.oleDbConnection1.Close();
}
}
private void button4_Click(object sender, System.EventArgs e)
{
oleDbConnection1.Open();
try
{
cellExport4.SaveToFile();
}
finally
{
this.oleDbConnection1.Close();
}
}
private void button5_Click(object sender, System.EventArgs e)
{
ushort currentRow = 1;
string currentFormula = string.Empty;
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 = CellColor.LightGreen;
cell.Format.FillStyle.Pattern = Pattern.Solid;
cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium;
cell = cellExport5.AddString(1,++currentRow,"Test data:");
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(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 = CellColor.LightGreen;
cell.Format.FillStyle.Pattern = Pattern.Solid;
cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium;
cell = cellExport5.AddString(2,currentRow, "Results");
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(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);
this.cellExport5.SaveToFile();
}
private void cellExport1_GetDataParams(object sender, Spire.DataExport.EventArgs.DataParamsEventArgs e)
{
if ((e.Sheet == 0) && (e.Col == 6))
{
e.FormatText = cellExport1.DataFormats.Currency;
}
}
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
oleDbConnection1.Open()
Try
cellExport1.SaveToFile()
Finally
Me.oleDbConnection1.Close()
End Try
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
LoadData()
End Sub
Private Sub button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button2.Click
LoadData()
End Sub
Private Sub btnAbout_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAbout.Click
Close()
End Sub
Private Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.Click
oleDbConnection1.Open()
Try
cellExport2.SaveToFile()
Finally
Me.oleDbConnection1.Close()
End Try
End Sub
Private Sub button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button3.Click
oleDbConnection1.Open()
Try
cellExport3.SaveToFile()
Finally
Me.oleDbConnection1.Close()
End Try
End Sub
Private Sub button4_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button4.Click
oleDbConnection1.Open()
Try
cellExport4.SaveToFile()
Finally
Me.oleDbConnection1.Close()
End Try
End Sub
Private Sub button5_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button5.Click
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
Me.cellExport5.SaveToFile()
End Sub
Private Sub cellExport1_GetDataParams(ByVal sender As Object, ByVal e As Spire.DataExport.EventArgs.DataParamsEventArgs) Handles cellExport1.GetDataParams, cellExport3.GetDataParams
If (e.Sheet = 0) AndAlso (e.Col = 6) Then
e.FormatText = cellExport1.DataFormats.Currency
End If
End Sub
Published in
Cell