[HERO92]

تفضل استاذ خضر 

كود تصدير البيانات لملف اكسل لكن بشروط

كود زر التصدير

كود :

       Dim connuser As New SqlConnection("Data Source=PC\SQLEXPRESS12;Initial Catalog=Std;Persist Security Info=True;User ID=it;Password=123")
       Dim dtMainData As New DataTable

       Dim myCriteria As String = " WHERE "
       myCriteria = myCriteria & "[SCType]='" & Me.imFF.SelectedValue.Trim & "'"
       myCriteria = myCriteria & " And [Error]='" & Me.iLe.SelectedValue.Trim & "'"

       If Idate.Text.Length > 0 And IsDate(Idate.Text) Then
           myCriteria = myCriteria & " And Batchname='" & Idate.Text & "'"
       End If

       Dim CMD As New SqlCommand("Select * From candidab" & myCriteria, connuser)
       connuser.Open()
       dtMainData.Load(CMD.ExecuteReader)
       connuser.Close()
       CMD = Nothing
       If dtMainData.Rows.Count > 0 Then
           DataGridView1.DataSource = dtMainData
           Dim sMainPath As String = "c:\Hero92\"

           Dim oListFileName = (From u In dtMainData.AsEnumerable() _
                                Select u.Field(Of String)("CenterCode")).Distinct()

           For Each CenterCode As String In oListFileName
               Dim dtData As DataTable = dtMainData.Select("CenterCode='" & CenterCode & "'").CopyToDataTable

               DatatableToExcel(dtData, sMainPath & CenterCode)
           Next

           MsgBox("Excel Exporting completed")
       Else
           MsgBox("No Records to Print")
       End If
 

كود التصدير للأكسل

كود :

 Private Sub DatatableToExcel(ByVal dtTemp As DataTable, ByVal strFileName As String)
       Dim _excel As New Microsoft.Office.Interop.Excel.Application
       Dim wBook As Microsoft.Office.Interop.Excel.Workbook
       Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

       wBook = _excel.Workbooks.Add()
       wSheet = wBook.ActiveSheet()

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

       For Each dc In dt.Columns
           colIndex = colIndex + 1
           _excel.Cells(1, colIndex) = dc.ColumnName
       Next

       For Each dr In dt.Rows
           rowIndex = rowIndex + 1
           colIndex = 0
           For Each dc In dt.Columns
               colIndex = colIndex + 1
               _excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
           Next
       Next

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

       wBook.SaveAs(strFileName)
       wBook.Close()
       _excel.Quit()
   End Sub

تقبل تحياتى وبالتوفيق