Add column to a sheet
In continuation from here…
We created a file earlier here.
Now we will try to add a new column to the existing data. Let’s say we want to add a column “Location”. One thing that we need to know that we cannot directly insert a column like we do in Interop. In Interop, it is pretty straightforward.
While using OLEDB data provider, we have to use an alternate method. We have to take the fields from the old sheet, add the new column to it and then create a new sheet. See the code below. Unfortunately you cannot delete a sheet using OLEDB data provider so I am going to clear the old sheet using DROP TABLE [SheetName$]
Imports System.Data.OleDb Public Class Form1 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" Dim NewFieldName As String = "Location" '<~~ This is the new column that we want to add Dim NewSheetName As String = "Sheet2" '<~~ Ensure the doesn't exists '~~> 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 '~~> Add column to the end olecmd.CommandText = "SELECT Sno,Employee_Name,Company,Date_Of_joining,Stipend,Stocks_Held,'' As " & _ NewFieldName & " INTO [" & NewSheetName & "] FROM [Sheet1$]" olecmd.ExecuteNonQuery() '~~> Clear Sheet1's data olecmd.CommandText = "DROP TABLE [Sheet1$]" olecmd.ExecuteNonQuery() '~~> Close the connection olecon.Close() End Sub End Class
This is how the new sheet with inserted column looks like