使用字符串生成器将XML放在单个csv/xls列中不起作用 [英] Using string builder to place XML in single csv/xls column not working

问题描述

我在处理XML响应并将其格式化为CSV时遇到问题，然后可以将其作为XLS打开，然后将整个响应查看到单个单元格中.我知道..也不是我该怎么做，但是他们得到了他们所要求的.

I am having an issue with an XML response and formatting it into CSV which can then later be opened as an XLS and see the entire response into a single cell. I know.. its not how I would do it either, but they get what they ask for.

到目前为止，我已经尝试使用字符串生成器.这已经成功地将响应格式化为单行字符串，我已经通过将其写入文本文件并将其复制到Eclipse中进行了测试.当我在XML周围放置单引号时，它会变成字符串.

So far I have tried to use a string builder. This has been successful in formatting the response into a single line string, I have tested this by writing it to a text file and copying it to Eclipse.. when I place single quotes around the XML it turns to a string.

当尝试以单行格式获取相同的响应并将其粘贴到csv文件中时..csv文件在XML字符串中以逗号分隔，并将响应跨数十个单元格放置.

When trying to take this same response in its single line format and stick it into a csv file.. the csv file is breaking on comma's in the XML string and placing the response across several dozen cells.

    BufferedReader br = new BufferedReader(new FileReader(new File('responseXml.txt')));
    String l;
    StringBuilder sb = new StringBuilder();


    while((l=br.readLine())!= null){sb.append(l.trim());
    File respfile = new File("outresp.txt")
    respfile.append(l)
    println respfile.text
//verified single line string  
    respContents = new File("outresp.txt").text  
                    }

    File file = new File('outXML.csv')
    file.append(respContents)
    println file.text   

// open csv still broke across many lines

我想要的是将单个xml字符串放入单个xls单元中.

What I would like is a single xml string into a single xls cell.

推荐答案

要将值放入csv(excel)的单列中，您有两种选择:

to fit value into single column in csv (excel) you have two choices:

1. 删除所有新行(\r\n)和逗号(,)
2. 将每个双引号(")替换为两个("")，并用双引号包装整个值.

1. remove all new lines (\r\n) and comas (,)
2. replace each doublequote (") with two ones ("") and wrap whole value with doublequotes.

第二个变体允许您将原始(多行)字符串格式保留在一个excel单元格中.

the second variant allows you to keep the original (multiline) string format in one excel cell.

这是第二个变体的代码:

here is the code for second variant:

//assume you have whole xml in responseXml variable
def responseXml = '''<?xml version="1.0"?>
<aaa text="hey, you">
    <hello name="world"/>
</aaa>
'''

//take xml string in double quotes and escape all doublequotes
responseXml = '"'+ responseXml.replaceAll(/"/,'""') + '"' 

def csv = new File('/11/1.csv')
csv.setText("col1,col2,xml\nfoo,bar") // emulate some existing file
csv.append(",${responseXml}\n")

结果:

这篇关于使用字符串生成器将XML放在单个csv/xls列中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！