逗号数组元素符US preadsheet [英] comma in array element breaks spreadsheet

问题描述

在将数据添加到一个数组中，元素之一在其价值逗号。值的一个例子是在103修剪痕，96和90。

使用以下code以添加数组元素对于s preadsheet目的，partdescription元件中，如上所述，有其数据跨越在s preadsheet多个列。它被作为单独的元素处理，一个也没有。

 ＆LT;！---创建文件名可变---＆GT;
    ＆LT; CFSET filenametouse ='PartLevel_Report/＆GT;
    ＆LT;！---设置目录和完整的文件路径---＆GT;
    ＆所述; CFSET theDir = GetDirectoryFromPath（GetCurrentTemplatePath（））/＆GT;
    ＆LT;！---附加文件扩展名完整的文件路径和文件名---＆GT;
    ＆LT; CFSET theFile = theDir＆安培; filenametouse＆安培; .xls的/＆GT;    ＆LT; CFLOCK NAME =fileActionSentItemsTYPE =独家超时=30throwontimeout =真＆GT;
        ＆LT; CFSET US preadsheetObj = S preadsheetNew（）＆GT;
        ＆所述; CFSET FCOL = {}＆GT;
        ＆LT; CFSET fcol.dataformat =@＆GT;        ＆LT;！---创建列标题 - ＆GT;
        ＆LT; CFSET US preadsheetAddRow（S preadsheetObj，部分##，##重新排序，说明斌##，当前INV，上演，分配，可用INV，货架计数，总发货，共＃。专卖店＃，共有序，平均每家商店，订货时间（天数），低位标记，房屋剩余的##总）GT。            ＆LT; CFOUTPUT查询=的getParts组=partnum＆GT;    ＆LT; CFSET aColumns = [partnum，shortchar08，partdescription，binlist，inventory.currinv，staged.stagedqty，alloc.allocqty，可用，shelfCount，shipdtl.shipqty，getNumberofStores.numStores，tordered，APS，paddedLeadTime，LWM，storesRemain /＆GT ;    ＆LT;！---列数据添加到S preadsheet ---＆GT;
    ＆LT; CFSET US preadsheetAddRow（S preadsheetObj，ArrayToList（aColumns））/＆GT;
＆LT; / CFOUTPUT＆GT;        ＆LT;！---生成在S preadsheet ---＆GT;
        ＆LT; CFS preadsheet行动=写文件名=＃theFile＃NAME =S preadsheetObjSHEETNAME =工作表Sheet1覆盖=真/＆GT;
 

我怎么可以缓解这个问题？

解决：我的变量设置为用分号全部换成逗号partdescription。现在，数据显示所有在同一列

 ＆LT; CFSET cleanDesc = rereplace（partdescription，，全）＆GT;
    ＆LT; CFSET aColumns = [partnum，shortchar08，cleanDesc，binlist，inventory.currinv，staged.stagedqty，alloc.allocqty，可用，shelfCount，shipdtl.shipqty，getNumberofStores.numStores，tordered，APS，paddedLeadTime，LWM，storesRemain /＆GT ;
 

解决方案

你试过取值preadsheetAddRows（）呢？在整个查询通过？也许这将是假定变通方法，取值preadsheetAddRows（）不只是叫取值preadsheetAddRow（）之下。

由于取值preadsheetAddRow（）不接受，大多数CF列表功能支持可选的分隔符，我想您也能逃脱逗号成别的东西，然后取代它成为使用逗号取值preadsheetsetcellvalue（）？

您可能要提交功能请求到Adobe为在取值preadsheetAddRow（）功能可选的分隔符。

When adding data to an array, one of the elements has commas in its value. An example of the value is "Trim marks at 103, 96, and 90".

Using the following code to add the array elements to the spreadsheet object, the partdescription element, as described above, has its data span multiple columns in the spreadsheet. It is handled as separate elements and not one.

<!---Create file name variable--->
    <cfset filenametouse = 'PartLevel_Report' />
    <!---Set directory and full file path--->
    <cfset theDir = GetDirectoryFromPath(GetCurrentTemplatePath()) /> 
    <!---Attach file extension to full file path and file name--->
    <cfset theFile = theDir & filenametouse & ".xls" /> 

    <cflock name="fileActionSentItems" type="exclusive" timeout="30" throwontimeout="true">
        <cfset SpreadsheetObj = spreadsheetNew()>
        <cfset fcol = {}>
        <cfset fcol.dataformat = "@">

        <!---Create the column headings--->
        <cfset SpreadsheetAddRow(SpreadsheetObj, "Part ##, Reorder ##, Description, Bin ##, Current Inv., Staged, Allocations, Available Inv., Shelf Count, Total Shipped, Total ## of Stores, Total Ordered, Avg. Per Store, Lead Time (in days), Low Water Mark, Total ## of Stores Remaining")>

            <cfoutput query="getParts" group="partnum">

    <cfset aColumns = [ partnum , shortchar08 , partdescription , binlist , inventory.currinv , staged.stagedqty , alloc.allocqty , available , shelfCount , shipdtl.shipqty , getNumberofStores.numStores , tordered , APS, paddedLeadTime, LWM , storesRemain] />

    <!---add the column data to the spreadsheet--->
    <cfset SpreadsheetAddRow(SpreadsheetObj, ArrayToList(aColumns)) />
</cfoutput>

        <!---Generate the spreadsheet--->
        <cfspreadsheet action="write" filename="#theFile#" name="SpreadsheetObj" sheetname="Sheet1" overwrite="true" />

How may I alleviate this issue?

Solved: I set a variable to the partdescription with all commas replaced with semicolons. Now the data appears all in the same column:

 <cfset cleanDesc = rereplace(partdescription, ",", ";", "all")>
    <cfset aColumns = [ partnum , shortchar08 , cleanDesc , binlist , inventory.currinv , staged.stagedqty , alloc.allocqty , available , shelfCount , shipdtl.shipqty , getNumberofStores.numStores , tordered , APS, paddedLeadTime, LWM , storesRemain] />

解决方案

Have you tried SpreadsheetAddRows() instead? Pass in the whole query? Maybe that will be a workaround assuming that SpreadsheetAddRows() doesn't just call SpreadsheetAddRow() underneath.

Since SpreadsheetAddRow() does not accept an optional delimiter that most CF list feature supports, I guess alternatively you can escape the comma into something else, then replace it as a comma using spreadsheetsetcellvalue()?

You may want to file a feature request to Adobe for an optional delimiter for the SpreadsheetAddRow() function.

这篇关于逗号数组元素符US preadsheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！