Programming

VB.NET - How to Export Data to Excel with Bar Chart

November 29, 2011

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

 

Access and Export Data to Access with C#

November 17, 2011

Access

Access, developed by Microsoft, one of Office applications, is a relational database management system (RDBMS).

Access offers seven things to build objects of database system. They are table, query, form, report, page, macro and template. Also, in order to standardize operation to save, query data, design interface and generate report, it provides users with various guides, builders and templates. Access is very convenient and easy to use because users don’t need to write code ...


Continue reading...
 

VB.NET-Add Text Watermark in Word

November 7, 2011

Traditionally, watermark is formed when marking paper. It is not printed on the face of paper but in the middle. Therefore, it can present others from forging the paper which watermark has been added.

Gradually, watermark is applied for electronic documents. As is known, along with sharp development of internet, people often share useful material with Word or other documents online. However, some people may steal the documents. So, the original owners need to take measures to protect the...


Continue reading...
 

C#--Set Word Font and Color

October 26, 2011

Generally speaking, we may set formatting for a Word document to make it be more readable, especially font and color. For example, if there are several sections in the document, we may set different font or color for the section title. Sometimes, in order to highlight key words, we can set it with bigger font size or bright color. With well formatting, readers can learn more clearly about the idea of each part and the layout will be more appealed after printing.

How to set Word font and ...


Continue reading...
 

(C#) Convert Word to Tiff Image

October 14, 2011

Tiff is one kind of image format to save photo and art pictures. This format is flexible and adaptable. It can deal with several images and data in one file through “tag” in file header. Tag can indicate size of image and define how to arrange image data.

Actually, Tiff is the most complicated bitmap file format. One tiff image can occupy large space because it saves lots of contents. At present, it is widely applied to saving and changing images which has high picture quality.

So...


Continue reading...
 

Use Mail Merge to Generate Files with C# in Word

September 28, 2011

Mail Merge is very useful for generating template file in bulk, such as transcript, questionnaire, invoice and so on.

How to use mail merge in MS Office? Firstly, we need to create a template, which is used to generate frame and main contents of file. Then, we need to have data source. Generally speaking, we can export data from database or create a new Excel file with essential data information. Secondly, set mail merge type and link to data source. Finally, generate mail merge files an...


Continue reading...
 

The Method to Import Data to Excel from DataTable and Export Excel Data

September 16, 2011

Microsoft Excel is an essential tool for office workers. It is very powerful on managing and calculating data. For example, if there are some data about sales information, we can use Excel to calculate average sales and total sales within one quarter. Also, it is convenient for printing if the data in Excel is well formatted.

Actually, if we want to display and operate data, we must import data to Excelfirstly. Where can we get the data? Sometimes, we can type data in Excel, but most of ...


Continue reading...
 

Method Write and Read Excel Image with C#

September 5, 2011

We can find that the blog post with images is more attractive than plain text post. Therefore, people may insert some appropriate images in their posts or documents. Generally speaking, the images are related to contents. For example, if your document has mentioned a series of data, you can insert one image, like analysis chart to make reader be clearer about the data information.

Although we can display data information with chart, sometimes we may need to insert image which shows data ...


Continue reading...
 

How to Draw Image in PDF with C#

August 4, 2011

It is necessary to have some images in one document to show information more intuitively. Also, the beautiful images with words are more appealed to readers than text only.

Generally speaking, we can insert images in documents directly. And then adjust images’ size or direction according to the requirements. But sometimes, there are not appropriate images for contents so that we may need to draw one. For example, if there must be a chart in a document, we should draw one based on the d...


Continue reading...
 

How to Create PDF Template Document with C#

July 21, 2011

Template is offered to people who don’t know how to set format for one document or table. Users can fill with contents according to the template or modify it to get better format.

Generally speaking, one template document may include font style and color, spacing and alignment. If the document has several pages, the template may include page number, header and footer of each page.

PDF is one of the most frequently used formats. Therefore, I want to talk something about PDF template,...


Continue reading...
 

This free website was made using Yola.

No HTML skills required. Build your website in minutes.

Go to www.yola.com and sign up today!

Make a free website with Yola