Coldfusion SpreadsheetAddRow（）-如何使用逗号消除值 [英] Coldfusion SpreadsheetAddRow() - How to get around values with commas

问题描述

我已经在网络上阅读了其他一些解决此问题的方法，但是在我的特定情况下没有帮助。我遍历一个查询，并在每次迭代时使用sheetsheetAddRow（）将行添加到Excel电子表格中。问题是任何带有逗号的值都会导致CF抛出字符串索引超出范围：-1错误。即使我将这些值用单引号引起来，也会发生这种情况。像这样的东西：

I've read some of the other solutions to this problem on the web, but none of the help in my particular situation. I'm looping over a query and using spreadsheetAddRow() to add rows to my Excel spreadsheet with each iteration. The problem is that any value with a comma in it causes CF to throw a "String index out of range: -1" error. This happens even though I'm wrapping these values in single quotes. So something like:

<cfset spreadsheetAddRow(s, "'foobar','foo,bar'")>

第一个值很好，但是第二个值抛出错误。即使我使用变量而不是字符串文字，也会发生这种情况：

The first value is fine but the second value trows an error. This happens even if I use variables instead of string literals:

<cfset val1 = "foobar">
<cfset val2 = "foo,bar">
<cfset spreadsheetAddRow(s, "'#val1#','#val2#'")>

或者如果我尝试建议的方法此处在页面的最底部。

Or if I try the method suggested here at the very bottom of the page.

我会使用电子表格AddRows（）而是一次抓取查询的所有行，但是问题出在我想显示电子表格信息的方式上。对于查询中的每一行，我在Excel工作表上进行三行操作-一行用于查询行中的某些值，一行用于查询行中的其他值，然后是空白行。

I would use spreadsheetAddRows() instead to grab all rows of the query at once, but the problem is with the way I want to display the info the spread sheet. For each row in my query, I am making 3 rows on the Excel sheet -- one for some values in the query row, one below it for some other values in the query row, and then a blank row.

我将尝试使用SpreadsheetSetCellValue（），其中，对于每个单元格值，我都会寻找一个逗号，用一个特殊字符临时替换它，然后在电子表格AddRow（）之后返回并用逗号替换该行中该特殊字符的所有实例。但这是低效率的，而且很hacky。有什么办法可以转义逗号，以便CF识别逗号是值的一部分？

I'm going to try using SpreadsheetSetCellValue(), where for each cell value I look for a comma, temporarily substitute it with a special character, and then after the spreadsheetAddRow() go back and replace all instances of that special character in the row with a comma. But that's inefficient and hacky. Is there any way to "escape" commas, so that CF recognizes the comma is a part of the value?

推荐答案

您说过

对于查询中的每一行，我在Excel工作表中进行三行操作-其中一行用于查询行中的某些值，另一行用于其他行中的其他值

For each row in my query, I am making 3 rows on the Excel sheet -- one for some values in the query row, one below it for some other values in the query row, and then a blank row.

我建议您简单地按照自己的意愿去做。像这样：

I suggest that you simply do what you say you want to do. Something like this:

<cfset currentSpreadSheetRow = 0>
<cfloop query = "yourQuery">
  <cfset columnNumber = 1>
  <cfloop list = "#yourQuery.columnlist#" index = "field">
    <cfset SpreadsheetSetCellValue(yourSheet,
    yourQuery[field][currentRow]
    , currentSpreadSheetRow + 1
    , columnNumber) >
    <cfset columnNumber +=1>
  </cfloop>
  <!--- code for second row --->
  <cfset currentSpreadSheetRow += 3>
</cfloop>

这是通常的想法。您可以更改详细信息以适合您的特定要求。

This is the general idea. You can change the details to suit your specific requirements.

这篇关于Coldfusion SpreadsheetAddRow（）-如何使用逗号消除值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！