13-04-16, 07:33 PM
تفضل استاذ خضر
كود تصدير البيانات لملف اكسل لكن بشروط
كود زر التصدير
كود التصدير للأكسل
تقبل تحياتى وبالتوفيق
كود تصدير البيانات لملف اكسل لكن بشروط
كود زر التصدير
كود :
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تقبل تحياتى وبالتوفيق
