CF9 SpreadsheetAddRows Changes Values

New Here ,
Apr 05, 2021 Apr 05, 2021

Copy link to clipboard

Copied

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 field 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)>

Views

78

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Apr 11, 2021 Apr 11, 2021

Copy link to clipboard

Copied

Two questions:

1) What is your ColdFusion version and Update level?

2)  I count 14 queryNew arguments, but 15 querySetCell calls. What happens after you fix that?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 12, 2021 May 12, 2021

Copy link to clipboard

Copied

I was setting the cell "Source type" twice.  Removing results in no changes to the output.

 

Version 9,0,2,282541

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
May 13, 2021 May 13, 2021

Copy link to clipboard

Copied

LATEST

OK. In that case, you should include the datatype argument in the spreadsheet functions. For example, by using the full-argument version of the functions:

 

SpreadsheetAddrow(spreadsheetObj, data ,row, column, insert, datatype)
https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-s/spreadsheetaddrow...

(scroll to the bottom of the page and you will see an example of datatype use)

 

SpreadsheetAddrows(spreadsheetObj, data , row, column, insert, datatype, includeColumnNames)
https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-s/spreadsheetaddrow...

 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines