Solved

Datatable to display only time of datetime field

Posted on 2016-08-31
19
41 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 70

Expert Comment

by:Éric Moreau
ID: 41778191
how do you export to Excel? Can you format the column in Excel?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

856 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