1

I am using this to generate a report and export it to excel.

ALTER PROCEDURE [dbo].[USP_DaysReport]  
        @Week int= 50,
        @Year int= 2019  
AS
BEGIN  
    Declare @YearText varchar(4)
    Set @YearText = @Year

    Select Emp_ID 'EmployeeID', Employee_name EmployeeName, Week, Year,
        [ProjectName], [SubProject], Convert(datetime, Convert(varchar(11),
        Case 
            when EachDay= 'Mon' then
            dateadd(day,2 - datepart(dw, @YearText + '-01-01')
                    + (@Week-1) * 7
                    ,@YearText + '-01-01')  
            when EachDay= 'Tue' then
            dateadd(day,3 - datepart(dw, @YearText + '-01-01')
                        + (@Week-1) * 7
                        ,@YearText + '-01-01')  
        end), 103) 'Day', Hours
    from [dbo].[TimeSheet_Child] 
    Inner Join 
    (
        Select TimeID as TID, Employee_name, Week, Year, Emp_ID from [dbo].[TimeSheet_Master]
    ) tm
    ON tm.TID= [dbo].[TimeSheet_Child].TimeID
    unpivot
    (
      Hours
      for EachDay in ([Mon], [Tue])
    ) u
END

vb.net code:

ExcelLibrary.DataSetHelper.CreateWorkbook(Server.MapPath("~/Downloads/DailyEmployeeReport.xls"), DataSetDays)

Now it works perfectly except one thing i.e. the date i.e. Day field it exports to excel comes like this 413122, 423112 etc so I have to format the field in excel to date.

Bill Tür stands with Ukraine
  • 3,425
  • 30
  • 38
  • 48
scaryghost
  • 77
  • 8

2 Answers2

1

The problem is that Excel receives dates and displays them as number. You can either make the change in the Excel file or export the values in this column to excel with a stupid prefix. I bet everything works fine when you convert the date values to varchar and preceed it with for instance 'abc'.

Personally, I would choose to alter the Excel file. You can also append to files. So if you create a template with a non-date column, you can make a copy from that everytime you need to export data.

Steef
  • 303
  • 2
  • 11
0
Public Shared Function FromExcelSerialDate(ByVal SerialDate As Integer) As DateTime
    If SerialDate > 59 Then SerialDate -= 1 ''// Excel/Lotus 2/29/1900 bug
    Return New DateTime(1899, 12, 30).AddDays(SerialDate)
End Function

Add this function to your code.

Trushit Shekhda
  • 563
  • 7
  • 18