View is compromised when data is exported to the templated xls file using ACE OLEDB

Question

Hi Everyone,
My task is to export the data to xls/ xlsx/ xlsm file. I am successfully done with xlsx and xlsm file but facing a problem with xls.

I have sample xls file having template which looks just as below:

I am sharing the code.

Here, GetXLFile method uses a SaveFileDialog which allows user to select extension( i.e. xls/xlsx/xlsm) and select the location of xls file which contains exported data. Then it saves the given file name and path to "xlFile" string. After that, it copies the templated sample xls file to xlFile.

FillDGVDataToDataTbl fills the datagridview's data to a datatable named as "dataTbl".

After this, I am exporting the dataTbl's data into a selected range to xlFile. "InsertRecords" inserts the data to each row and "InsertColumns" inserts the columns in the first row of the sheet after the template.

      Public Sub ExporttoExcelFile(ByVal dgv As DataGridView)  
            Dim dataTbl As New System.Data.DataTable  
            Dim xlFile As String = String.Empty  
            GetXLFile(xlFile)  
           FilDGVDataToDataTbl(dgv, dataTbl)  
           Dim ext As String = Path.GetExtension(xlFile)  
           If ext.Equals(".xlsx") Or ext.Equals(".xlsm") Then  
                 connStr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = '" & xlFile & "';" &  
                                  " Extended Properties = 'Excel 12.0; HDR = NO; MAXSCANROWS = 0';"  
            ElseIf ext.Equals(".xls") Then  
                  connStr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = '" & xlFile & "';" &  
                                   " Extended Properties = 'Excel 8.0; HDR = NO; MAXSCANROWS = 0';"  
            End If  
            connStr = String.Format(connStr)  
            Try  
                conn = New OleDb.OleDbConnection(connStr)  
                conn.Open()  
               cmd = New OleDbCommand()  
               cmd.Connection = conn         
               Dim Tbls = conn.GetSchema("Tables", Nothing)  
               Dim xlSheet As String  
               xlSheet = Tbls.Rows.Item(0)(2).ToString  '("TABLE_NAME").ToString()  
        '  If excelSheets(0).EndsWith("$") Then  
               Dim lastCol As Char = ChrW(65 + dataTbl.Columns.Count - 1)  
               xlSheet = xlSheet.Replace("'", "")  
               InsertRecords(dataTbl, xlSheet, lastCol)  
          Catch ex As Runtime.Exception  
                MsgBox(resManager.GetString("ErrorOccurredWhileExportingPartsListToExcel"), , Me.Text)  
          Catch ex As System.Exception  
                MsgBox(resManager.GetString("ErrorOccurredWhileExportingPartsListToExcel"), , Me.Text)  
          Finally  
                conn.Close()  
                conn.Dispose()  
          End Try  
      End Sub  
Private Sub InsertRecords(ByVal dataTbl As System.Data.DataTable, ByVal xlSheet As String, ByVal lastCol As Char)  
          InsertColumns(xlSheet, dataTbl, lastCol)  
        For i = 0 To dataTbl.Rows.Count - 1  
            query = $"INSERT INTO [{xlSheet}A{i + 3}:{lastCol}{i + 3}] VALUES ("  
            For j = 0 To d![96102-desired-view.png][2]ataTbl.Columns.Count - 1  
                If j = dataTbl.Columns.Count - 1 Then  
                    query += " ? );"  
                Else  
                    query += " ?,"  
                End If  
            Next  
            cmd.CommandText = query  
            For j = 0 To dataTbl.Columns.Count - 1  
                Dim str As String = dataTbl.Rows(i)(j).ToString()  
                cmd.Parameters.AddWithValue("@p" & j, str)  
            Next  
            Try  
                cmd.ExecuteNonQuery()  
                cmd.Parameters.Clear()  
            Catch ex As Autodesk.AutoCAD.Runtime.Exception  
                MsgBox(resManager.GetString("ErrorOccurredWhileExportingPartsListToExcel"), , Me.Text)  
            Catch ex As System.Exception  
                MsgBox(resManager.GetString("ErrorOccurredWhileExportingPartsListToExcel"), , Me.Text)  
            End Try  
        Next  
    End Sub  
 Private Sub InsertColumns(ByVal xlSheet As String, ByVal dataTbl As System.Data.DataTable, ByVal lastCol As Char)  
        Dim columnNames As String = ""  
        query = $"INSERT INTO [{xlSheet}A2:{lastCol}2] VALUES ("  
        For i = 0 To dataTbl.Columns.Count - 1  
            If i = dataTbl.Columns.Count - 1 Then  
                query += " ? );"  
            Else  
                query += " ?,"  
            End If  
        Next  
        cmd.CommandText = query  
        If dataTbl.Columns.Count <> 0 Then  
            For i = 0 To dataTbl.Columns.Count - 1  
                Dim str As String = dataTbl.Columns(i).ToString()  
                cmd.Parameters.AddWithValue("@p" & i, str)  
            Next  
            Try  
                cmd.ExecuteNonQuery()  
                cmd.Parameters.Clear()  
            Catch ex As Runtime.Exception  
                MsgBox(resManager.GetString("ErrorOccurredWhileExportingPartsListToExcel"), , Me.Text)  
            Catch ex As System.Exception  
                MsgBox(resManager.GetString("ErrorOccurredWhileExportingPartsListToExcel"), , Me.Text)  
            End Try  
        End If  
    End Sub  

All this working well but there is some problem with view.

The desired view is :

But, the xlFile looks like this:

( Please pay attention to font of "company name" in first row and merged row colors of columns B, F and H. Also the header's font is changed in these columns.)

I didn't made any other changes to xlFile.
Can anyone tell me how can get the desired view?

Thanks in advance.