Programming

In 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