01-02-18, 03:10 AM
(آخر تعديل لهذه المشاركة : 02-02-18, 12:34 AM {2} بواسطة Rabeea Qbaha.)
السلام عليكم
عندي كود استخدمة لاستخراج جميع البيانات من query الى excel
الكود يعمل بشكل ممتاز، لكن كيف استطيع تغير عناوين الاعمده التي من ال table
يعني اخلي أسم العمود من c_name الى اسم العميل
هذا الكود المستعمل :
عندي كود استخدمة لاستخراج جميع البيانات من query الى excel
الكود يعمل بشكل ممتاز، لكن كيف استطيع تغير عناوين الاعمده التي من ال table
يعني اخلي أسم العمود من c_name الى اسم العميل
هذا الكود المستعمل :
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