It's been a very long time since I've looked at this. we've done different interfaces over the years. COM, Writing XLSX directly and LibXL
On the COM interface version I've found this code that uses TextToColumns to get rid of the little triangle in the corner for things like telephone numbers
This may or may not help with your issue but it was the thing I had at the back of my mind that might be of use
Code:
// Use TextToColums to solve the "Number as Text" errors
For iLoop2 from 1 to iMaxCols
Get ColumnNumberToLetter (iLoop2+iInsertOffsetFromFirstColumn) to sColumnLetter
// note you need to select the whole column rather than a range of cells otherwise any blank cells in range will show an error.
Get ComXlRange of hoWorksheet (sColumnLetter+":"+sColumnLetter) Nothing to vObj
If (IsComObject(vObj)) Begin
Set pvComObject of hoCells to vObj
// if the first cell in the column is empty it will raise an error
Move False to bDumyCell
Get ComXlItem of hoCells 1 1 to vObj
If (IsComObject(vObj)) Begin
Set pvComObject of (phoXLCell(Self)) to vObj
Get ComXlValue of (phoXLCell(Self)) OLEXLxlRangeValueDefault to sCellData
If (Length(sCellData)=0) Begin
// put a dummy value in the fisrt cell to prevent an error with ComXlTextToColumns
Set ComXlValue of (phoXLCell(Self)) OLEXLxlRangeValueDefault to 1
Move True to bDumyCell
End
Get ComXlSelect of hoCells to vVoid
Get ComXlTextToColumns of hoCells Nothing OLEXLxlDelimited OLEXLxlTextQualifierNone False True False False False False Nothing Nothing Nothing Nothing Nothing to vData
If (bDumyCell) Begin
Set ComXlValue of (phoXLCell(Self)) OLEXLxlRangeValueDefault to ""
End
End
End
Loop
Code:
// recursive function to calc the Excel letter reference from a given column number. Not set an upper limit but excel 2010 is 16384 (XFD)
Function ColumnNumberToLetter Integer iCol Returns String
Integer iAlpha iRemainder
String sLetters sNewLetters
If (iCol>0) Begin
If (iCol < 27) Begin
Move (Character(iCol+64)) to sLetters
End
Else Begin
Move (Mod(iCol,26)) to iRemainder
Move (iCol/26) to iAlpha
If (iRemainder=0) Begin
Move 26 to iRemainder
Decrement iAlpha
End
Get ColumnNumberToLetter iAlpha to sNewLetters
Move (sNewLetters+Character(iRemainder+64)) to sLetters
End
End
Function_Return sLetters
End_Function