_Excel_BookNew($oExcel) DateTime issue - (Moved)

[DougK]

I wrote an application that reads a User's full name, their Employee number, where they work and a Date/Time, which is written to an SQL Database,
This works great, now I am working on another app that reads the SQL database into a 2DArray.  This part of it appears to be working.

Then I am using the _Excel_BookNew($oExcel) to send the data to the array and then to an Excel Spreadsheet.
This is working with the exception of the date/Time field.

Below are some screenshots of what I am seeing;

This is from the SQL Database -

 

This is the 2D Array.  I think this is where the issue is. - 

And this is what I see in Excel - 

I don't/ know if it is my database insertion of Date/Time, or it is the conversion from Array to Excel.

This is my code for insertion into the database with the line creating the variable for Date/Time;

Local $datetime = _DateTimeFormat(_NowCalc(), 0)
               local $RecAdd =_SQL_Execute(-1,"INSERT INTO dbo.Work_Location (Zone,FullName,EmployeeID,WorkLocation,Date) VALUES ('" & $Zone & "' ,'" & $FullName & "' ,'" & $EmpID & "' ,'Office','" & $datetime & "');")

Here is my code to read the database into the 2DArray and then export to Excel.

Local $aData,$iRows,$iColumns;Variables to store the array data in to and the row count and the column count
    $GetDB = _SQL_GetTable2D(-1,"SELECT * FROM dbo.Work_Location;",$aData,$iRows,$iColumns)
      If $GetDB = $SQL_OK then _arrayDisplay($aData,"2D (" & $iRows & " Rows) (" & $iColumns & " Columns)"  )
      $oExcel = _Excel_Open(False)
      $oBook = _Excel_BookNew($oExcel)
      _Excel_RangeWrite($oBook,Default,$aData)
      _Excel_BookSaveAs($oBook,@ScriptDir & '\test5.xlsx',$xlOpenXMLWorkbook)
      _Excel_Close($oExcel)

Anyone have any thoughts on this?

Thank you in advance for your assistance.

[Melba23]

Moved to the appropriate AutoIt General Help and Support forum, as the Developer General Discussion forum very clearly states:

Quote

General development and scripting discussions.

---

Do not create AutoIt-related topics here, use the AutoIt General Help and Support or AutoIt Technical Discussion forums.

Moderation Team

[seadoggie01]

You'll need to add the formatting back before you write to Excel. The format you can see in your database works for excel as well. Right now, it looks to excel like you're entering a very large number because there's no date separation. You can either find a way that reading from the database returns the format to AutoIt or you can fix it in AutoIt with a series of StringSplits on each row of data

[Nine]

Maybe this :

#include <Excel.au3>

Local $aData = [[1, "Douglas", 12345, "Office", 20220311140853]]

 Local $oExcel = _Excel_Open()
 Local $oBook = _Excel_BookNew($oExcel)
 
 $aData[0][4] = StringRegExpReplace(String($aData[0][4]),"(.{4})(.{2})(.{2})(.{2})(.{2})(.{2})","$1-$2-$3 $4:$5:$6")
 $oBook.Activesheet.Columns("E").ColumnWidth = 20
 $oBook.Activesheet.Columns("E").NumberFormat = "yyyy-mm-dd hh:mm:ss"

 _Excel_RangeWrite($oBook,Default,$aData)

 

Edited March 11, 2022 by Nine

[water]

More details about formatting an Excel Range can be found in the wiki.