change column header text - Export query Data to Excel

Question

I implemented button to export query data to Excel sheet, every thing perfect but I want to change the header text that come from database table to custom text.

what i mean that i want to change the column name from sh_id to "Shop ID" or C_id to "Customer ID".

so how I can change it ??

this is the method that i use to export data to excel :

Private Sub Excel()
Dim dataAdapter As New SqlClient.SqlDataAdapter()
Dim dataSet As New DataSet
Dim command As New SqlClient.SqlCommand
Dim datatableMain As New System.Data.DataTable()
Dim connection As New SqlClient.SqlConnection

'Assign your connection string to connection object
connection.ConnectionString = "Server = DESKTOP-TP1616Q; Database = rent_manager; Trusted_Connection=True;"
command.Connection = connection
command.CommandType = CommandType.Text
'You can use any command select
command.CommandText = "Select * from shops"
dataAdapter.SelectCommand = command

Try
    'This section help you if your language is not English.
    'System.Threading.Thread.CurrentThread.CurrentCulture =
    '        System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
    Dim oExcel As Microsoft.Office.Interop.Excel.Application
    Dim oBook As Microsoft.Office.Interop.Excel.Workbook
    Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet
    oExcel = CreateObject("Excel.Application")
    oBook = oExcel.Workbooks.Add()
    oSheet = oBook.Worksheets(1)

    Dim dc As System.Data.DataColumn
    Dim dr As System.Data.DataRow
    Dim colIndex As Integer = 0
    Dim rowIndex As Integer = 0

    'Fill data to datatable
    connection.Open()
    dataAdapter.Fill(datatableMain)
    connection.Close()


    'Export the Columns to excel file
    For Each dc In datatableMain.Columns
        colIndex = colIndex + 1
        oSheet.Cells(1, colIndex) = dc.ColumnName
    Next

    'Export the rows to excel file
    For Each dr In datatableMain.Rows
        rowIndex = rowIndex + 1
        colIndex = 0
        For Each dc In datatableMain.Columns
            colIndex = colIndex + 1
            oSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
        Next
    Next


    ' for the header 
    oSheet.Rows(1).Font.Name = "Droid Arabic Kufi"
    oSheet.Rows(1).Font.size = 11
    oSheet.Rows(1).Font.Bold = True
    Dim mycol As System.Drawing.Color = System.Drawing.ColorTranslator.FromHtml("#20b2aa")
    oSheet.Rows(1).Font.color = mycol

    ' for all the sheet without header
    'oSheet.Range("A1", "Z" & rowIndex & "").Font.Name = "Droid Arabic Kufi"
    'wSheet.Columns.AutoFit.fornt.name = "Droid Arabic Kufi"
    oSheet.Range("a2", "Z" & rowIndex & "").Font.Size = 10
    ' make the sheet Alignment center
    oSheet.Range("a1", "Z" & rowIndex & "").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter

    Dim strFileName As String = SaveFileDialog1.FileName
    Dim blnFileOpen As Boolean = False

    Try
        Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
        fileTemp.Close()
    Catch ex As Exception
        blnFileOpen = False
        Exit Sub
    End Try

    If System.IO.File.Exists(strFileName) Then
        System.IO.File.Delete(strFileName)
    End If

    oSheet.Columns.AutoFit()




    ' this add Grid line to all rows and columns 
    Dim formatRange As Microsoft.Office.Interop.Excel.Range = oSheet.UsedRange
    Dim cell As Microsoft.Office.Interop.Excel.Range = oSheet.Range("a1", "D" & rowIndex & "")
    Dim border As Microsoft.Office.Interop.Excel.Borders = cell.Borders
    border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
    border.Weight = 1.0

    ' this add header and footer when printing the sheet
    oSheet.PageSetup.CenterHeader = "&""Droid Arabic Kufi,Bold""&14مصروفات المحددة"
    oSheet.PageSetup.RightFooter = DateTime.Now
    oSheet.PageSetup.LeftFooter = "Page &P of &N"

    'make the print page horizontal
    oSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait

    'make all columns fit in one page
    oSheet.PageSetup.Zoom = False
    oSheet.PageSetup.FitToPagesWide = 1
    oSheet.PageSetup.FitToPagesTall = False

    Try
        Dim range = oSheet.FindString("shop_id", False, False)
        oSheet.Replace(range.Value, "shop_id")
    Catch e1 As System.Exception

    End Try

    'Save file in final path
    oBook.SaveAs(strFileName)
    oExcel.Workbooks.Open(strFileName)
    oExcel.Visible = True

    'Release the objects
    ReleaseObject(oSheet)
    ReleaseObject(oBook)
    ReleaseObject(oExcel)
    'Some time Office application does not quit after automation: 
    'so i am calling GC.Collect method.
    GC.Collect()

    MessageBox.Show("Export done successfully!")

Catch ex As Exception
    MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK)
End Try
End Sub

Private Sub releaseObjectt(ByVal obj As Object)
Try
    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
    obj = Nothing
Catch ex As Exception
    obj = Nothing
Finally
    GC.Collect()
End Try
End Sub

Answer 1

Change it when you define the column names:

For Each dc In datatableMain.Columns
    colIndex = colIndex + 1

    Select Case dc.ColumnName
        Case "sh_id"
            oSheet.Cells(rowIndex + 1, colIndex) = "Shop ID"
        Case "C_id"
            oSheet.Cells(rowIndex + 1, colIndex) = "Customer ID"
        Case Else
            oSheet.Cells(rowIndex + 1, colIndex) = dc.ColumnName
    End Select


Next