[Solved] - تغير أسماء الأعمدة عند استخراج البيانات الى excel

PHP كود :

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()

        'Assign your connection string to connection object
        command.Connection = SQL.conn
        command.CommandType = CommandType.Text
        'You can use any command select
        command.CommandText = sqlstring

        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
            SQL.conn.Open()
            dataAdapter.Fill(datatableMain)
            SQL.conn.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 releaseObject(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

PHP كود :

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

                Select Case dc.ColumnName
                    Case "id"
                        oSheet.Cells(rowIndex + 1, colIndex) = "الرقم"
                    Case "p_id"
                        oSheet.Cells(rowIndex + 1, colIndex) = "رقم الصنف"
                    Case "p_name"
                        oSheet.Cells(rowIndex + 1, colIndex) = "أسم الصنف"
                    Case "p_size"
                        oSheet.Cells(rowIndex + 1, colIndex) = "الحجم"
                    Case "p_m_price"
                        oSheet.Cells(rowIndex + 1, colIndex) = "سعر المتر"
                    Case "p_price"
                        oSheet.Cells(rowIndex + 1, colIndex) = "السعر"
                    Case "date"
                        oSheet.Cells(rowIndex + 1, colIndex) = "تاريخ الحركة"
                    Case "note"
                        oSheet.Cells(rowIndex + 1, colIndex) = "الملاحظات"
                    Case Else
                        oSheet.Cells(rowIndex + 1, colIndex) = dc.ColumnName
                End Select
            Next