3

I am creating a simple spreadsheet from a query using CF Spreadsheet functions. Code is below. I know we're on an ancient version. This is not my call. Thankfully we will finally be upgrading in a couple of months.

In the column "OMS Num", we have an alphanumeric value which is always 4 characters. Some of these are dropping the last character when adding to the row. The value itself is complete all the way up to the point of spreadsheetaddrows. If I put anything before or after it, it works fine. I've tried SpreadsheetFormatColumn as well to no avail.

I've experimenting putting other values before the last letter and the same thing happens. So "12345D" becomes "13245" "863D" becomes "863". Any clues what's happening here and how to remedy it?

Data: "OMS" field
365A
UA66
UA74
705D - Converts to 705
592A
UK71
985A
216F - Converts to 216
940C
335F - Converts to 335
337F - Converts to 337

Code snippet:

<Cfset qQuestionTypes = QueryNew("VERWMERKM,PLNNR_OMSSeqGrp,SourceType,OPS_ID,InputType,WorkCenter,OMS,OMS_ItemNbr,PartNbr,PartDesc,PartQty,HasInspectMethPic,QG_Group,ARBPL_WorkCenter","varchar, varchar,varchar,varchar, varchar,varchar, varchar,varchar,varchar, varchar,varchar, varchar,varchar,varchar")>

<cfset QueryAddRow(qQuestionTypes)>
<cfset QuerySetCell(qQuestionTypes,"VERWMERKM","A#numberformat(VERWMERKM,'0000000')#")>
<cfset QuerySetCell(qQuestionTypes,"PLNNR_OMSSeqGrp",PLNNR)>
<cfset QuerySetCell(qQuestionTypes,"SourceType",qQuestDetails.SourceType)>
<cfset QuerySetCell(qQuestionTypes,"OPS_ID",OPS_ID)>
<cfset QuerySetCell(qQuestionTypes,"inputtype",qQuestDetails.inputtype)>
<cfset QuerySetCell(qQuestionTypes,"WorkCenter",new_dummy10)>
<cfset QuerySetCell(qQuestionTypes,"OMS",TRIM(new_dummy20))>
<cfset QuerySetCell(qQuestionTypes,"OMS_ItemNbr",new_dummy40)>
<cfset QuerySetCell(qQuestionTypes,"PartNbr",qPartDetails.component_number)>
<cfset QuerySetCell(qQuestionTypes,"PartDesc",qPartDetails.Description)>
<cfset QuerySetCell(qQuestionTypes,"PartQty",qPartDetails.Qty)>
<cfset QuerySetCell(qQuestionTypes,"HasInspectMethPic",hasInspMeth)>
<cfset QuerySetCell(qQuestionTypes,"QG_Group",QualityGateRef)>
<cfset QuerySetCell(qQuestionTypes,"ARBPL_WorkCenter",ARBPL)>
<cfset QuerySetCell(qQuestionTypes,"SourceType",qQuestDetails.SourceType)>
<cfset sSpreadsheet = spreadsheetNew("QM3Details",TRUE)>
<cfset spreadsheetSetActiveSheet(sSpreadsheet,"QM3Details")>

<!--- Add header rows --->
<cfset spreadsheetAddRow(sspreadsheet, "SAP Question Nbr,OMS Seq Grp, Source Type, OPS_ID, Input Type, Work Center, OMS Num, OMS item, Part Number, Part Desc, Part Quantity, Has Insp Meth Pic,QG Group, ARBPL_Workcenter")>
<!--- Test of individual writes
<cfloop query="qQuestionTypes">
<cfset spreadsheetaddrow(sspreadSheet, '#qQuestionTypes.VERWMERKM#,#qQuestionTypes.PLNNR_OMSSeqGrp#,#qQuestionTypes.SourceType#,#qQuestionTypes.OPS_ID#,#qQuestionTypes.InputType#,#qQuestionTypes.WorkCenter#,#tostring(qQuestionTypes.OMS)#,#qQuestionTypes.OMS_ItemNbr#,#qQuestionTypes.PartNbr#,#qQuestionTypes.PartDesc#,#qQuestionTypes.PartQty#,#qQuestionTypes.HasInspectMethPic#,#qQuestionTypes.QG_Group#,#qQuestionTypes.ARBPL_WorkCenter#')>
</cfloop> --->
<cfset spreadsheetaddrows(sspreadSheet, qQuestionTypes,20,1,true)>
<!--- Write File --->
<cfset fileModDate=DateFormat(variables.dtCurrentDateTime, 'yyyymmdd_HHmmss')>
<cfset qm3filename="\somefolder\QM3_Details_#fileModDate#.xlsx">
<cfset spreadsheetWrite(sspreadsheet, qm3filename, true)>
Rob Catlin
  • 31
  • 1
  • 1
    If the problem doesn't disappear after the CF upgrade, then using the `datatype` parameter (not available until CF11) to force the column to STRING should work. In the meantime, do you still get the issue if you temporarily hardcode a string value like `"1234A"` in place of `new_dummy40` in your QuerySetCell? And could you prepend a `'` to the value as a workaround until you upgrade? eg `QuerySetCell(qQuestionTypes,"OMS_ItemNbr","'"&new_dummy40)` – Sev Roberts Apr 06 '21 at 16:05
  • CF is probably parsing "705D" and "335F" as numbers, instead of strings. i.e. "D" ==> `double`, and "F" ==> `float` (numeric types) and that's why the trailing letters are lost. Unfortunately there were a LOT of data type problems with the earlier spreadsheet functions. Upgrading is the best option, for this and security reasons. If you really can't upgrade now, you could try some of the workarounds on SO to see if any work for you, [one example](https://stackoverflow.com/a/5650675/8895292). – SOS Apr 07 '21 at 20:56
  • I don't have a copy of CF9 to test your original code, but here's a simplified example if anyone else does ... https://trycf.com/gist/985ff81ae8c3f2d44faba0754ec1605e/acf?theme=monokai – SOS Apr 07 '21 at 21:26
  • 1
    I do have access to an old CF9 test VM and can confirm that @SOS is spot on. As well as testing A-Z suffixes I also tried tripping it up with some binary, hex and exponent literals, eg `"0x1a","0b11","0xFF_EC_DE_5E","0b0010_0101","999_99_9999L","1.234e2","1.234E2","12e2","12E2"` and of those, the `0x` and `0b` strings remained intact, but the `E` numbers were parsed as exponents, eg `12E2` was converted to `1200` in the xlsx. Perhaps the more surprising thing is that it preserved the `L` in `1234L` rather than treating it as a long. Prepending a `'` to the cell value preserved all strings. – Sev Roberts Apr 08 '21 at 11:34

0 Answers0