如何在ColdFusion中的.xlsx文件中替换逗号？ [英] How to replace comma in .xlsx file in ColdFusion?

问题描述

我有一个excel文件，在某些字段中可以包含逗号。如果我想使用 cfspreadsheet 将我的文件转换为 csv ，这可能会导致问题。我想知道是否有办法用替换或 convert 所有逗号。在替换所有逗号后，我将能够使用 cfspreadsheet 创建 csv 。这是我的代码如何读取我的文件：

I have excel file that can contain commas in some fields. This can cause problem if I want to use cfspreadsheet to convert my file to csv. I was wondering if there is the way to replace or convert all commas with the \. After I replace all commas then I will be able to use cfspreadsheet to create csv. Here is my code how I read my file:

<cfspreadsheet action = "read" format="csv" src="filePath\myFile.xlsx" name="csvvar">

如果任何人可以帮助解决这个问题，请让我知道。谢谢。

If anyone can help with this problem please let me know. Thank you.

推荐答案

从Excel转换为查询。然后，在每行的单元格数据中，将，替换为\。类似这样的

Convert from Excel to query. Then, in the cell data of each row, replace "," by "\". Something like this

<cfspreadsheet 
action = "read" 
src="filePath\myFile.xlsx" 
query="excelquery" 
sheet="1">

<!--- Create CSV file in current directory--->
<cffile action="write" file="#expandpath('result.csv')#" output="">

<cfset columns = arraynew(1)>
  <!--- Store the list of column names as an array --->
<cfset columns = listToArray(excelquery.ColumnList)>

<cfoutput query="excelquery">
<cfset rowList = "">

<cfloop from="1" to="#arraylen(columns)#" index="n">
    <cfset colName = columns[n]>
    <cfset cellData = evaluate("#colName#[currentrow]")>

    <!--- Replace , by \ in each cell --->
    <cfset cellData = replace(cellData, ",", "\", "all")>

    <!--- Comma-separated row data --->
    <cfset rowList = listAppend(rowList,cellData)>
</cfloop>

<!--- Place a carriage-return at the end of the row --->
<cfset rowList = rowList & '<br>'>

<!--- Append row to CSV file --->
<cffile action="append" file="#expandpath('result.csv')#" output="#rowList#" >
</cfoutput> 

这篇关于如何在ColdFusion中的.xlsx文件中替换逗号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！