Solved

Datatable to display only time of datetime field

Posted on 2016-08-31
19
57 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
Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

 

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

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 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