Export Data from Database to Excel via SQL Command and Customize Style of it For VB.NET without Automation

Introduction

This article introduces how to export data from database to XLS with SQL Command without Automation.

For a programmer, in order to export data to a specified file format, you don't need to write a plenty of code, you just need to write connection string and a command to select data and specify a file format to show the data with a free component. In this example, I will show:

1. How to make a connection with database.
2. How to get data source from database with a SQL Command.
3. How to export the data source from database.
4. How to set the export format.
5. How to export the data to many formats.(Word, Excel, PDF...)

Five simple steps:

1. Make a connection to the database
2. Select Data Source with SQL Command
3. Set the export format (Supported Format: XLS, PDF, MS Word and so on)
4. Customize style and apply it
5. Save to a file and launch it

And now, I will introduce you the way step by step. Before doing this, you may first download the free component here:

http://www.e-iceblue.com/Download/download-dataexport-for-net-now.html

After installing it, you may add reference the DLL file into your project. Then go to the steps.

Step 1: Make a connection to the database

You may choose a database to export data from. The database may be SQL Client, OLE DB or Access. Then you may create a connection to the database by inputting the connection string. Here I use OLE DB connection for example. The connection string is made of two parts. The former is the provider of your database, the latter is the data source. I just specify an exact path of my Access database.
The code:

'Make a connection with the database
Dim oleDbConnection As OleDbConnection = New System.Data.OleDb.OleDbConnection()

'The connection string
oleDbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb"

Step 2: Select Data Source with SQL Command

Choose the data source with SQL Command. In my Access database, I just choose the customers table with Name, Gender, Birthday, E-mail and shopping columns in it. Just as the following image shows:

The code:

'SQL Command
Dim oleDbCommand As OleDbCommand = New System.Data.OleDb.OleDbCommand()
oleDbCommand.CommandText = "select Name,Gender,Birthday,[E-mail],Shopping from customers"

After giving the SQL Command, don't forget to combine the SQL Command with the connection string. Using the following code:

oleDbCommand.Connection = oleDbConnection

Step 3: Set the export format

Many popular formats provided for you (Supported Format: XLS, PDF, MS Word, HTML, MS clipboard ,XML, DBF, SQL Script, SYLK, DIF, CSV) as following:

I choose XLS to export data to. Just use the code:

'Define export way
Dim cellExport As New Spire.DataExport.XLS.CellExport()
Dim workSheet1 As New Spire.DataExport.XLS.WorkSheet()

Step 4: Customize style and apply it

According to your requirement, you can customize the style. First, go setting the title format:

Code:

'Title format

workSheet1.AutoFitColWidth = True
workSheet1.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
workSheet1.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
workSheet1.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
workSheet1.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium
workSheet1.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.SkyBlue
workSheet1.Options.TitlesFormat.Font.Color = Spire.DataExport.XLS.CellColor.LightOrange
workSheet1.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
workSheet1.Options.TitlesFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Center
workSheet1.Options.TitlesFormat.Font.Size = 11.0F

Then is your data format in XLS, according to your requirement, you may decide how many styles you should define. Here I define five styles:

'Define styles

Dim stripStyle1 As New Spire.DataExport.XLS.StripStyle()
Dim stripStyle2 As New Spire.DataExport.XLS.StripStyle()
Dim stripStyle3 As New Spire.DataExport.XLS.StripStyle()
Dim stripStyle4 As New Spire.DataExport.XLS.StripStyle()
Dim stripStyle5 As New Spire.DataExport.XLS.StripStyle()

Set different background colors and font colors of each style, in addition you can set other format for your own need:

'Customize style1

stripStyle1.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen
stripStyle1.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
stripStyle1.Font.Color = XLS.CellColor.Brown
stripStyle1.Font.Size = 11.0F

'Customize style2
stripStyle2.FillStyle.Background = Spire.DataExport.XLS.CellColor.Yellow
stripStyle2.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
stripStyle2.Font.Color = XLS.CellColor.DarkTeal
stripStyle2.Font.Size = 11.0F

'Customize style3

stripStyle3.FillStyle.Background = Spire.DataExport.XLS.CellColor.Color4
stripStyle3.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
stripStyle3.Font.Color = XLS.CellColor.Red
stripStyle3.Font.Size = 10.7F

'Customize style4

stripStyle4.FillStyle.Background = Spire.DataExport.XLS.CellColor.Color8
stripStyle4.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
stripStyle4.Font.Color = XLS.CellColor.Plum
stripStyle4.Font.Size = 10.4F

'Customize style5

stripStyle5.FillStyle.Background = Spire.DataExport.XLS.CellColor.PaleBlue
stripStyle5.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid
stripStyle5.Font.Color = XLS.CellColor.DarkBlue
stripStyle5.Font.Size = 10.1F

To make the table more beautiful, I set the borders color of it with four directions: Bottom, Top, Left and Right:

'Set the border color of the cell
workSheet1.Options.CustomDataFormat.Borders.Bottom.Color = Spire.DataExport.XLS.CellColor.Blue
workSheet1.Options.CustomDataFormat.Borders.Top.Color = Spire.DataExport.XLS.CellColor.Blue
workSheet1.Options.CustomDataFormat.Borders.Left.Color = Spire.DataExport.XLS.CellColor.Blue
workSheet1.Options.CustomDataFormat.Borders.Right.Color = Spire.DataExport.XLS.CellColor.Blue

Step 5: Save to a file and launch it

The last step is to show your data result with your specified file format(XLS). It will be saved in your work place (in the Debug file of your project) by default.

'Save to file
cellExport.SaveToFile("Customers.xls")

Use the following code to launch it after running it:

'Open the file after export
cellExport.ActionAfterExport = ActionType.OpenView

The result: