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