When working on my spreadsheet editor (https://masm32.com/board/index.php?msg=129817), I stumbled over a nice little quirk of Microsoft ADO (Advanced Data Objects). This is a typical sequence to get a table from e.g. an Excel file:
CoInvoke pFlds, Fields.get_Item, vIndex, addr pFld
CoInvoke pFld, Field.get_Name, addr bstrTmp
CoInvoke pFld, Field.get_Value, addr v3
The variable v3 is a VARIANT. Field.get_Value (https://docs.oracle.com/cd/A97630_01/win.920/a95895/o4o00496.htm) copies the content of a cell into a buffer.
That works fine, most of the time, and you'll find an Ole$ alias BSTR (https://www.jj2007.eu/MasmBasicQuickReference.htm#Mb1139) in your buffer, i.e. a UTF-16 representation of the cell content.
Now the quirk: if your spreadsheet was saved by good ol' M$ Excel, and there are cells in DATE format, such as 05/07/2011, then M$ ADO will not hesitate to copy a Unicode string into your buffer. And guess what? It does look like a date!
Only that it is roughly 600 years off :cool:
Lovely. So I had to code a little special handler just in case OpenAdo (https://www.jj2007.eu/MasmBasicQuickReference.htm#Mb1396)() encounters a cell with the VT_DATE format:
CoInvoke pFld, Field.get_Value, addr v3 ; no jfne: may have an error for a single cell
movzx ecx, v3.vt
.if eax==80040e21h && ecx==VT_BSTR ; highly misleading M$ "help"; in reality, this is a rare
mov v3.bstrVal, Ole$("#error#") ; "Number stored as text" error when cell has "General" property
.endif ; but gets treated as number; see Vermont in Superstore data.xls
.if ecx!=VT_BSTR
.if ecx<=VT_NULL ; VT_EMPTY=0, VT_NULL=1
and v3.bstrVal, 0 ; make it an empty string, see adoNull
.elseif ecx==VT_DATE ; M$ on Excel dates
fld v3.llVal ; Excel date format serial number on FPU
ExternDef nsPerDay:REAL4 ; ok until x.x.2500
fmul nsPerDay ; FP4(864000000000.0) ; one day
fadd FP8(94353704400000000.0) ; 1.1.1900-2 days
push eax
push eax
fistp qword ptr [esp] ; Eric Lippert: The OLE automation date format is a floating point
movlps xmm0, qword ptr [esp] ; value, counting days since midnight 30 December 1899.
pop edx ; Hours and minutes are represented as fractional days.
pop edx
void wfDate$(xmm0)
mov v3.bstrVal, eax
.else
invoke VariantChangeType, addr v3, addr v3, 13, VT_BSTR ; M$ Learn
.endif
.endif
MasmBasic 28.4.24 has it right. (http://masm32.com/board/index.php?topic=94.0)