Solved

Datatable to display only time of datetime field

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

816 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

8 Experts available now in Live!

Get 1:1 Help Now