My Blog

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.

ws.Columns(1).Insert

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