Solved

Datatable to display only time of datetime field

Posted on 2016-08-31
19
33 Views
Last Modified: 2016-09-01
Hello,
I am exporting a row from datagridview to excel. There is a column datetime. Need to export only time part of the column to excel.
Any suggestion
0
Comment
Question by:RIAS
  • 11
  • 8
19 Comments
 

Author Comment

by:RIAS
ID: 41778180
Had something like this for the datagridview but can I apply this to datatable
 dg(int).Columns("Time").DefaultCellStyle.Format = "HH:mm"
0
 

Author Comment

by:RIAS
ID: 41778181
dtGridData.Rows(iRow).Item(iCol) Any formatting on this bit
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41778191
how do you export to Excel? Can you format the column in Excel?
0
 

Author Comment

by:RIAS
ID: 41778198
  Function ExportToExcel(ByVal intCreateNew As Integer, ByVal dtGridData As DataTable, ByVal FilePath As String, ByVal StrSheetname As String, Optional ByVal xlApp As Excel.Application = Nothing) As String
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Application.DoEvents()
        Try
            FrmMain.Cursor = Cursors.WaitCursor
            xlApp.DisplayAlerts = False
            If intCreateNew = 0 Then

                xlApp.Workbooks.Add()
            End If
            If IsNothing(FilePath) = True Then
                xlWorkSheet = Nothing
                xlWorkBook = Nothing
                xlApp = Nothing
                FrmMain.Cursor = Cursors.Default
                Return "Cancelled"
                Exit Function
            End If
            xlApp.Workbooks(1).SaveAs(FilePath)
            Application.DoEvents()
            xlWorkBook = xlApp.Workbooks.Open(FilePath)
            Application.DoEvents()
            If intCreateNew = 0 Then
                xlWorkSheet = xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count)
            Else
                xlWorkBook.Worksheets.Add(After:=xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count))
                xlWorkSheet = xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count)
            End If
            xlWorkSheet.Name = StrSheetname
            With xlWorkSheet.PageSetup
                .PrintGridlines = True
                .CenterHeader = StrSheetname
                .Zoom = False
            End With
            Dim dtRowCount As Integer = dtGridData.Rows.Count
            Dim dtColCount As Integer = dtGridData.Columns.Count

            Dim objXlColHeaderData(1, dtGridData.Columns.Count) As Object
            Application.DoEvents()
            For i As Integer = 0 To dtColCount - 1
                objXlColHeaderData(0, i) = dtGridData.Columns(i).ColumnName
            Next
            Dim objXlData(dtRowCount, dtColCount) As Object
            For iRow As Integer = 0 To dtRowCount - 1
                Application.DoEvents()
                For iCol As Integer = 0 To dtColCount - 1
                    Application.DoEvents()
                    If Not IsDBNull(dtGridData.Rows(iRow).Item(iCol)) Then
                        objXlData(iRow, iCol) = dtGridData.Rows(iRow).Item(iCol)
                    Else
                        objXlData(iRow, iCol) = ""
                    End If
                Next
            Next
            Dim xlRange As Excel.Range = xlWorkSheet.Range("A1")
            xlRange = xlRange.Resize(dtRowCount, dtColCount)
            xlRange.Value2 = objXlColHeaderData
            xlWorkSheet.Range(xlWorkSheet.Cells(1, 1), xlWorkSheet.Cells(1, dtColCount)).Font.Bold = True
            xlRange = xlWorkSheet.Range("A2")
            xlRange = xlRange.Resize(dtRowCount, dtColCount)
            ' xlRange = xlRange.Resize(dtRowCount + 1, dtColCount + 1)
            xlRange.Value2 = objXlData
            With xlWorkSheet
                .Range(.Cells(1, 1), .Cells(1, 1)).Select()
            End With
            With xlWorkSheet.Application.ActiveWindow
                .SplitColumn = 0
                .SplitRow = 1
            End With
            xlWorkSheet.Cells.EntireColumn.AutoFit()
            xlWorkSheet.Application.ActiveWindow.FreezePanes = True
            Application.DoEvents()
            xlWorkBook.Save()

        Catch ex As Exception
            MessageBox.Show(ex.Message, "ErrorIn ExportToExcel", MessageBoxButtons.OK, MessageBoxIcon.Error)
            xlWorkSheet = Nothing
            xlWorkBook = Nothing
            xlApp = Nothing
            FrmMain.Cursor = Cursors.Default
        Finally
            FrmMain.Cursor = Cursors.Default
        End Try
        Return "True"
    End Function

Open in new window

0
 

Author Comment

by:RIAS
ID: 41778201
This bit exports the rows to excel

 For iRow As Integer = 0 To dtRowCount - 1
                Application.DoEvents()
                For iCol As Integer = 0 To dtColCount - 1
                    Application.DoEvents()
                    If Not IsDBNull(dtGridData.Rows(iRow).Item(iCol)) Then
                        objXlData(iRow, iCol) = dtGridData.Rows(iRow).Item(iCol)
                    Else
                        objXlData(iRow, iCol) = ""
                    End If
                Next
            Next

Open in new window

0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41778227
if you are building a generic exporter (that will be able to export any grid whatsoever without having a knowledge of what's in it), then you better have your grid containing strings already formatted (instead of your current datetime column formatted using the grid feature).

or do you build a very custom exporter that will export that grid with no customization from the user? In that case, because you know in which Excel column your value will be, you could use Excel formatting feature.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41778239
If you go with option 2 (use Excel feature), I think the code would be (not tested):
Dim xlRange As Excel.Range = xlWorkSheet.Range("A1")
xlRange.EntireColumn.NumberFormat = "h:mm;@"

Open in new window

0
 

Author Comment

by:RIAS
ID: 41778297
Will try this Eric. Really appreciated!
0
 

Author Comment

by:RIAS
ID: 41778850
How to determine column ?
Still the same :

Date         Time      PickUpTime
42593      2.75                2.46875
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41779010
You have the column at the top no? In your case it looks like it is column B & C
0
 

Author Comment

by:RIAS
ID: 41779363
Hello Eric,
Can you specify where do I check for column B & C
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41779544
in the example above, replace A1 with B1 and another time with C1
1
 

Author Comment

by:RIAS
ID: 41779546
Will try and get right back
0
 

Author Comment

by:RIAS
ID: 41779609
xlRange = xlWorkSheet.Range("A2")

            xlRange = xlRange.Resize(dtRowCount, dtColCount)
            ' xlRange = xlRange.Resize(dtRowCount + 1, dtColCount + 1)
            xlRange.Value2 = objXlData
            With xlWorkSheet
                .Range(.Cells(1, 1), .Cells(1, 1)).Select()
                .Cells.EntireColumn.NumberFormat = "dd-MMM-yyyy hh:mm;@"
            End With

This bit worked but the date shown is
10-Feb -1999
where as the actual date in the datagridview is 2-10-1999.
Eric can you help me  fix this
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41779675
the problem with 2-10-1999 is that some cultures will interpret it as Feb 10 while others will interpret it as Oct 2.  Excel in this case use the US interpretation (MM-dd-yyyy). What is the real value. is it Feb or Oct? Can you format your datagridview as yyyy-MM-dd which is the ISO format and cannot be interpreted incorrectly?
0
 

Author Comment

by:RIAS
ID: 41779692
Eric,
This worked but,repeated code can you suggest on how can I not repeat the same code for every column

   xlRange = xlWorkSheet.Range("C1")
                    xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    xlRange = xlWorkSheet.Range("D1")
                    xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    xlRange = xlWorkSheet.Range("K1")
                    xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    xlRange = xlWorkSheet.Range("O1")
                    xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41779708
I don't think you can create a range of sparse columns
0
 

Author Comment

by:RIAS
ID: 41779711
nothing can be done to make the code non repetitive
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 41779730
you can surely loop:
For Each strX As String In New String() {"C", "D", "K", "O"}
   xlRange = xlWorkSheet.Range(strX + "1")
   xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
Next

Open in new window

0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now