VB.NET - How to Export Data to Excel with Bar Chart
November 29, 2011In MS Excel, we can create charts according to imported data. Actually, a chart likes an image and it can present data more intuitively. Excel provides several chart styles. One of the most frequently used charts is bar chart.
Bar chart is used to show comparison between items. The items are presented by vertical or horizontal bars. There are three elements to draw a bar chart, group number, group width and group limit.
In this post, I want to introduce the method to export data to Excel and draw bar chart according to data by using VB.NET.
Firstly, I will download install a component, Spire.DataExport on my computer because this method based on it.
Secondly, add its DLL file after creating a project.
Thirdly, design form. Add group box for data source. Add datagirdview for loading data. Add button to run. Add cellExport which is provided by Spire.DataExport.
Fourthly, use the following code.
The code shows details about how to export data to Excel with Bar Chart with VB.NET.
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
Posted by SunnyWhite. Posted In : Export Data