Export Data From Database to Excel Via SQL Command Without Automation

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

For a programmer, to export data to a specified file format, you don't need to write a substantial amount of code, you just need to write a 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 a database.
2. How to get a data source from the database with a SQL Command.
3. How to export the data source from the 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 a Data Source with a SQL Command
3. Set the export format. (Supported formats: XLS, PDF, Microsoft Word and so on.)
4. Customize style and apply it
5. Save to a file and launch it

And now, I will introduce you to the way to do it 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 a reference to 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 an 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 chose 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 are provided for you (the supported formats are: XLS, PDF, Microsoft Word, HTML, Microsoft clipboard, XML, DBF, SQL Script, SYLK, DIF, CSV) as follows:

I chose 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 requirements, you can customize the style. First, to set 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 various 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 results 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