Click here to Skip to main content
15,886,091 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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 Vb.net program for your reference.

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

VB.Net Code *********

<pre lang="vb"><pre>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:\mysettxtup\Connectionstring.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:\Pall\mysetup\connectionstring.txt"
            'Dim objectreader As New System.IO.StreamReader(path)
            'connetionString = objectreader.ReadLine
            'conn = New SqlConnection(connetionString)

            Dim Srpath As String = "C:\mysettxtup\samplereport.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:\mysettxtup\Savereport.txt"
            Dim Slrobjectreader As New System.IO.StreamReader(Slrpath)
            Dim Slrpathstring As String = Slrobjectreader.ReadLine

            'xlWorkSheet = xlWorkBook.Sheets("D:\Pall\Report\SampleReport")
            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:\VB\Report\vbexcel_" + 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


What I have tried:

Please find Vb.net program for your reference.

Please let me know your suggestions So,it will resolve my issue
Posted
Updated 26-Mar-19 5:29am
Comments
[no name] 26-Mar-19 11:13am    
A lot of useless code not related to the "problem". Can't tell if the date is to or from Excel. What's with the precision anyway? Pass it as a formatted string if "date" does not compute.

1 solution

Don't do it like that. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

You may well find that if you parse your textboxes to DateTime values - including ms - and pass those as parameters, Excel may accept them.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900