Iran58
یک شنبه 27 تیر 1389, 15:42 عصر
باسلام
لطفاكسي مي تواند درموردكدزيربه بنده توضيح بدهدونمونه# c آنرابراي بنده بنويسد
باتشكر
Private Sub MakeExcelReport(ByVal DS As DataSet)
Dim DateTimeCreateFile As String
Dim Src As String
Dim Target As String
Dim ExcelFileObj
Dim CurDate As String
Dim CurTime As String
Dim ObjDate As New ShamsiDate.SDate
Dim CnExcel As New OleDb.OleDbConnection
Dim CmdExcel As New OleDb.OleDbCommand
Dim SqlStr As String
Dim TempSqlStr As String
Dim Ind As Integer
Dim TempDT As New DataTable
Try
DateTimeCreateFile = MakeTimeAndDateFile()
Src = Server.MapPath("../../") + "ExcelMainFiles\DiffInEMIGAInf.xls"
Target = Server.MapPath("../../") + "Temp\DiffInEMIGAInf\DiffInEMIGAInf" + "-" + DateTimeCreateFile + ".xls"
ExcelFileObj = CreateObject("Scripting.FileSystemObject")
ExcelFileObj.CopyFile(Src, Target, True)
ExcelFileObj = Nothing
CurDate = Year(Now) & "/" & Month(Now) & "/" & Day(Now)
CurDate = ObjDate.ShamsiDate(CurDate)
CurTime = MakeCurTime()
CnExcel.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Target + ";Extended Properties=Excel 8.0;"
CnExcel.Open()
CmdExcel.Connection = CnExcel
' Cur Date
SqlStr = " Update [Sheet1$] Set F9 = '" + CurDate + "' "
SqlStr += " Where F9 = 'CurDate' "
CmdExcel.CommandText = SqlStr
CmdExcel.ExecuteNonQuery()
' Cur Time
SqlStr = " Update [Sheet1$] Set F9 = '" + CurTime + "' "
SqlStr += " Where F9 = 'CurTime' "
CmdExcel.CommandText = SqlStr
CmdExcel.ExecuteNonQuery()
SqlStr = "Insert Into [Sheet1$]("
SqlStr += " F1, F2, F3, F4, F5, F6, F7, F8, F9 "
SqlStr += ") Values ( "
TempDT = DS.Tables("CompleteTable")
For Ind = 0 To TempDT.Rows.Count - 1
TempSqlStr = (Ind + 1).ToString()
TempSqlStr += ", '" + TempDT.Rows(Ind).Item("SupplierName").ToString() + "' "
TempSqlStr += ", '" + TempDT.Rows(Ind).Item("EmigaProductCode").ToString () + "' "
TempSqlStr += ", '" + TempDT.Rows(Ind).Item("EmigaClassCode").ToString() + "' "
TempSqlStr += ", '" + TempDT.Rows(Ind).Item("BODY_NUMBER").ToString() + "' "
TempSqlStr += ", '" + TempDT.Rows(Ind).Item("Storage_SNO").ToString() + "' "
TempSqlStr += ", '" + IIf(TempDT.Rows(Ind).Item("KIT_SNO").ToString().Tr im() = "", "-", TempDT.Rows(Ind).Item("KIT_SNO").ToString().Trim() ) + "' "
TempSqlStr += ", '" + TempDT.Rows(Ind).Item("InstallType").ToString() + "' "
TempSqlStr += ", '" + TempDT.Rows(Ind).Item("InstallDate").ToString() + "' "
TempSqlStr += " ) "
CmdExcel.CommandText = SqlStr + TempSqlStr
CmdExcel.ExecuteNonQuery()
Next
CnExcel.Close()
CnExcel.Dispose()
MakeDownLoadTr("..\..\Temp\DiffInEMIGAInf\DiffInEM IGAInf" + "-" + DateTimeCreateFile + ".xls", TableErr)
Catch ex As Exception
If (CnExcel.State = ConnectionState.Open) Then
CnExcel.Close()
End If
CnExcel.Dispose()
TableErr.Rows.Add(MakeTrError(ErrorToString()))
End Try
End Sub
لطفاكسي مي تواند درموردكدزيربه بنده توضيح بدهدونمونه# c آنرابراي بنده بنويسد
باتشكر
Private Sub MakeExcelReport(ByVal DS As DataSet)
Dim DateTimeCreateFile As String
Dim Src As String
Dim Target As String
Dim ExcelFileObj
Dim CurDate As String
Dim CurTime As String
Dim ObjDate As New ShamsiDate.SDate
Dim CnExcel As New OleDb.OleDbConnection
Dim CmdExcel As New OleDb.OleDbCommand
Dim SqlStr As String
Dim TempSqlStr As String
Dim Ind As Integer
Dim TempDT As New DataTable
Try
DateTimeCreateFile = MakeTimeAndDateFile()
Src = Server.MapPath("../../") + "ExcelMainFiles\DiffInEMIGAInf.xls"
Target = Server.MapPath("../../") + "Temp\DiffInEMIGAInf\DiffInEMIGAInf" + "-" + DateTimeCreateFile + ".xls"
ExcelFileObj = CreateObject("Scripting.FileSystemObject")
ExcelFileObj.CopyFile(Src, Target, True)
ExcelFileObj = Nothing
CurDate = Year(Now) & "/" & Month(Now) & "/" & Day(Now)
CurDate = ObjDate.ShamsiDate(CurDate)
CurTime = MakeCurTime()
CnExcel.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Target + ";Extended Properties=Excel 8.0;"
CnExcel.Open()
CmdExcel.Connection = CnExcel
' Cur Date
SqlStr = " Update [Sheet1$] Set F9 = '" + CurDate + "' "
SqlStr += " Where F9 = 'CurDate' "
CmdExcel.CommandText = SqlStr
CmdExcel.ExecuteNonQuery()
' Cur Time
SqlStr = " Update [Sheet1$] Set F9 = '" + CurTime + "' "
SqlStr += " Where F9 = 'CurTime' "
CmdExcel.CommandText = SqlStr
CmdExcel.ExecuteNonQuery()
SqlStr = "Insert Into [Sheet1$]("
SqlStr += " F1, F2, F3, F4, F5, F6, F7, F8, F9 "
SqlStr += ") Values ( "
TempDT = DS.Tables("CompleteTable")
For Ind = 0 To TempDT.Rows.Count - 1
TempSqlStr = (Ind + 1).ToString()
TempSqlStr += ", '" + TempDT.Rows(Ind).Item("SupplierName").ToString() + "' "
TempSqlStr += ", '" + TempDT.Rows(Ind).Item("EmigaProductCode").ToString () + "' "
TempSqlStr += ", '" + TempDT.Rows(Ind).Item("EmigaClassCode").ToString() + "' "
TempSqlStr += ", '" + TempDT.Rows(Ind).Item("BODY_NUMBER").ToString() + "' "
TempSqlStr += ", '" + TempDT.Rows(Ind).Item("Storage_SNO").ToString() + "' "
TempSqlStr += ", '" + IIf(TempDT.Rows(Ind).Item("KIT_SNO").ToString().Tr im() = "", "-", TempDT.Rows(Ind).Item("KIT_SNO").ToString().Trim() ) + "' "
TempSqlStr += ", '" + TempDT.Rows(Ind).Item("InstallType").ToString() + "' "
TempSqlStr += ", '" + TempDT.Rows(Ind).Item("InstallDate").ToString() + "' "
TempSqlStr += " ) "
CmdExcel.CommandText = SqlStr + TempSqlStr
CmdExcel.ExecuteNonQuery()
Next
CnExcel.Close()
CnExcel.Dispose()
MakeDownLoadTr("..\..\Temp\DiffInEMIGAInf\DiffInEM IGAInf" + "-" + DateTimeCreateFile + ".xls", TableErr)
Catch ex As Exception
If (CnExcel.State = ConnectionState.Open) Then
CnExcel.Close()
End If
CnExcel.Dispose()
TableErr.Rows.Add(MakeTrError(ErrorToString()))
End Try
End Sub