Styjun

I am going to Log my process parameters in SQL and generate Excel Reports.
The Programming is done in VB.Net

we are going to log Date_Time in 'YYYY-MM-DD HH:mm:ss.ms' example:'2018-11-27 21:41:24.170' (Here ms is milisec.)
But while exporting with excel it comes in '27/11/18 21:41:24.000' not able to show millisecond data(here it is .170) .
So, Can you please let me know how i can show date&Time including millisecond in Excel.

Excel Setting for Date Column : dd/mm/yy hh:mm:ss.000

Please find Database snap , Generated report snap and Vb.net program for your reference.

Please let me know your suggestions So,it will resolve my issue.

01 .DatabseImage

02.Generated Report

 Imports System.Data
Imports System.Data.SqlClient
' For th is need to add Microsoft Excel componant from project > Add Ref > Com
Imports Excelr = Microsoft.Office.Interop.Excel

Module Module1
 Dim conn As SqlConnection
 Dim path As String = "C:mysettxtupConnectionstring.txt"
 Dim objectreader As New System.IO.StreamReader(path)
 Dim connetionString As String

 Dim adpt As New SqlDataAdapter
 Dim ds As New DataSet

 ' used for excel report
 Dim r, c As Integer
 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
 Sub Main()
 '----- Read SQl Connection from Text File------
 connetionString = objectreader.ReadLine
 conn = New SqlConnection(connetionString)
 'Dim trn_Log1, trn_Log2 As ADODB.Recordset
 '************************************************************************************************************
 ' Set the Format type and the CustomFormat string.
 'Start date 
 ' DateTimePickerST.Format = DateTimePickerFormat.Custom
 ' DateTimePickerST.CustomFormat = "MM/dd/yyyy H:mm"
 'Clear connection and dataset before open
 conn.Close()
 ds.Clear()

 'To handle exception use here Try catach
 Try
 'Open connection 
 conn.Open()
 'Dim query As String

 ' Dim query As String = "Select * from ProDataTbl1Sec where Date_Time between '" & RStartDate.Text & "'and'" & REndDate.Text & "'order by date_time asc"

 Dim query As String = "SELECT * FROM ProDataTbl1Sec WHERE Date_Time >= dateadd(hour,-24,getdate())"
 'Dim query As String = "SELECT * FROM ProDataTbl1Sec"

 adpt.SelectCommand = New SqlCommand(query, conn)

 ds = New DataSet("wincc")
 adpt.Fill(ds)


 Dim i, j As Integer

 ' Standard for Excel application
 Dim xlApp As Excelr.Application
 Dim xlWorkBook As Excelr.Workbook
 Dim xlWorkSheet As Excelr.Worksheet
 'Dim shXL As Excel.Worksheet
 'xlWorkSheet.Visible = True

 Dim misValue As Object = System.Reflection.Missing.Value


 ' Dim xlSourceRange As Excel.Range
 xlApp = New Excelr.Application

 ' ------- Read Sample Report Location from Text file ------

 'Dim path As String = "C:Pallmysetupconnectionstring.txt"
 'Dim objectreader As New System.IO.StreamReader(path)
 'connetionString = objectreader.ReadLine
 'conn = New SqlConnection(connetionString)

 Dim Srpath As String = "C:mysettxtupsamplereport.txt"
 Dim Srobjectreader As New System.IO.StreamReader(Srpath)
 Dim Srpathstring As String = Srobjectreader.ReadLine


 ' ------- Read Sample Report Location from Text file ------
 xlWorkBook = xlApp.Workbooks.Add(Srpathstring & "SampleReport")

 ' ------- Save as Report Location from Text file ------
 Dim Slrpath As String = "C:mysettxtupSavereport.txt"
 Dim Slrobjectreader As New System.IO.StreamReader(Slrpath)
 Dim Slrpathstring As String = Slrobjectreader.ReadLine

 'xlWorkSheet = xlWorkBook.Sheets("D:PallReportSampleReport")
 xlWorkSheet = xlWorkBook.Sheets("Report")

 r = ds.Tables(0).Rows.Count + 7 ' 7 add here because we start 1 record from 8th row
 c = ds.Tables(0).Columns.Count

 Console.WriteLine(r)
 Console.WriteLine(c)

 Console.WriteLine("Work in Process")

 ' Print Table Row with column of worksheet
 For i = 0 To ds.Tables(0).Rows.Count - 1
 'For j = 0 To ds.Tables(0).Columns.Count - 1
 For j = 0 To ds.Tables(0).Columns.Count - 1


 xlWorkSheet.Cells(i + 8, j + 1) = ds.Tables(0).Rows(i).Item(j)
 xlWorkSheet.Columns("A:I").EntireColumn.AutoFit()
 'xlWorkSheet.Columns("A").EntireColumn.farmat()
 Next

 Console.WriteLine(i)
 Next
 ' Save Excel Sheet
 '~~> Save Worksheet file to the following location
 'xlWorkSheet.SaveAs("E:VBReportvbexcel_" + currentdate.ToString() + "xlsx")
 Dim currentdate As String = String.Format("0:ddMMyyyy-Hmm ", DateTime.Now)
 ' Excel Animation
 'xlWorkSheet.Columns("A:I").EntireColumn.AutoFit()
 With xlWorkSheet
 .Range("A2").Value() = "Date : " & currentdate
 End With

 ' ------- Save as Report Location from Text file ------
 'RLocation.Text = " Report save in " & Slrpathstring
 xlWorkSheet.SaveAs(Slrpathstring & "ParagDailyReport_" & currentdate & ".xlsx")

 xlWorkBook.Close()
 xlApp.Quit()

 releaseObject(xlApp)
 releaseObject(xlWorkBook)
 releaseObject(xlWorkSheet)

 'close connection , dadaptor and clear dataset
 conn.Close()
 adpt.Dispose()
 ds.Dispose()


 Catch ex As Exception

 MsgBox(ex.ToString())

 End Try
 End Sub

End Module