Creating a New Excel File and Adding Data using ACE
In continuation from here…
Click on File and click on New Project. Under Visual basic, Select Windows and then select Windows Form Application. Give the project an appropriate name and select a location where you want to save this project.
When the project opens you will be presented with Form1. For the time being just lets create 2 buttons and name then Create and Cancel. Give an appropriate heading to the form. Your form may look like this.
And now we are ready to code.
Creating our connection string
To create a new file we simply provide the name of a non existent file in the connection string. This will create a new file.
Private Const connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=C:\Book1.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
Deciding what kind of data do we want to write to the Excel File
Let’s say, we want the following fields in the Excel file.
Sno, Employee_Name, Company, Date_Of_joining, Stipend, Stocks_Held
If you notice, I have taken fields of different data type. Sno is of INT Type, Employee Name and Company is of VARCHAR Type, Date Of joining is of DATE Type, Stipend is DECIMAL Type and Stocks Held is of PERCENTAGE Type.
Now Excel has no PERCENTAGE type for fields. It has a percentage FORMATTING for NUMERIC fields and hence we will store the value of Stocks Held in DECIMAL Type. So For example 1% = 0.01
Command for writing data to the Excel File
To create a table in an Excel workbook, run the CREATE TABLE command. The syntax of the command is
CREATE TABLE Sheet1 (F1 char(255), F2 char(255))
Sheet1 is the name of the worksheet in Excel. F1,F2 etc is the Field and char(255) is the Data Type with the length that you would like to specify. This is optional. When you run this command, a new worksheet is created with the name of the table you specify in the command. Note that if the workbook for the connection does exist then it will give you an error. You may want to check for the existence of the file first and delete it before creating a new one?
This is how our final code will look like
Imports System.Data.OleDb Imports System.Data Public Class Form1 '~~> Create Button Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim olecon As OleDbConnection = New OleDbConnection Dim olecmd As OleDbCommand = New OleDbCommand Dim FilePath As String = "C:\Temp\" Dim FileName As String = "EmployeeDatabase.xlsx" '~~> Construct your connection string Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & FilePath & FileName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES;""" olecon.ConnectionString = connstring olecon.Open() olecmd.Connection = olecon '~~> Command to create the table olecmd.CommandText = "CREATE TABLE Sheet1 (Sno Int, " & "Employee_Name VARCHAR, " & "Company VARCHAR, " & "Date_Of_joining DATE, " & "Stipend DECIMAL, " & "Stocks_Held DECIMAL)" olecmd.ExecuteNonQuery() '~~> Adding Data olecmd.CommandText = "INSERT INTO Sheet1 (Sno, Employee_Name, Company,Date_Of_joining,Stipend,Stocks_Held) values " & "('1', 'Siddharth Rout', 'Defining Horizons', '20/7/2014','2000.75','0.01')" olecmd.ExecuteNonQuery() '~~> Close the connection olecon.Close() '~~> Inform User MessageBox.Show("The file " & FileName & " has been created successfully and has been placed in " & FilePath) End Sub '~~> Close Button Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click Me.Close() End Sub End Class
Now you are ready to run your project.
When executed, the program will create an Excel File as shown below.