- Demo
- C# source
- VB.Net source
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
