Solved

Datatable to display only time of datetime field

Posted on 2016-08-31
19
44 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 70

Expert Comment

by:Éric Moreau
ID: 41778191
how do you export to Excel? Can you format the column in Excel?
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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 70

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 70

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
 
LVL 70

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 70

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 70

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 70

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 70

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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