转换为 CSV 时如何将数组值保持在同一列中

Question

我正在尝试将 Object 值转换为 CSV，但 join 方法将我的数组值分隔到 Excel 中的不同列中。关于如何避免这种情况的任何想法？

功能：

window.downloadCsv = function(records) {

    console.log(records);

    const array = [Object.keys(records)].concat(records);

    console.log(array);

    let result = array.map(it => {

        let objectValues = Object.values(it);

        for (let i = 0; i < objectValues.length; i++) {

            if (Array.isArray(objectValues[i])) {

                //Keep it as array

            }

        }

        return objectValues;

    }).join('\n');

    console.log(result);

    let hiddenElement = document.createElement('a');

    hiddenElement.href = 'data:text/csv;charset=utf-8,' + encodeURI(result);

    hiddenElement.target = '_blank';

    hiddenElement.download = 'records.csv';

    hiddenElement.click();

};

可能的记录输入：

id: "5e8468e2db05ff589ca61b30"

title: "Example Application Number 1"

status: "Preparing Documents"

principalInvestigator: "Mr. Harry Styles"

coInvestigators: ["Niall Horan, Liam Payne, Zayn Malik, Louis Tomilson"]

partners: null

funder: "EPSRC Standard research"

researchGroup: "MedEng"

scheme: "Travel Grant"

requestedAmount: null

estimatedAmount: 1234

submissionDate: "2020-03-23T00:00:00.000+01:00"

startDate: "2020-03-29T00:00:00.000+01:00"

estimatedDuration: null

endDate: null

facility: null

comments: null

dateCreated: "2020-04-01T12:11:46.783+02:00"

lastUpdated: "2020-04-01T12:11:46.783+02:00"

dateDeleted: null

__proto__: Object

结果的当前输出：

id,title,status,principalInvestigator,coInvestigators,partners,funder,researchGroup,scheme

5e8468e2db05ff589ca61b30,Example Application Number 1,Preparing Documents,Mr. Harry Styles,Niall Horan, Liam Payne, Zayn Malik, Louis Tomilson

期望的输出：

id,title,status,principalInvestigator,coInvestigators,partners,funder,researchGroup,scheme

Excel 格式可能更容易理解。目前，导出后是这样的：https ://imgur.com/2I8h9kl

所需的外观是：https ://imgur.com/bFUKQY2

因此，我几乎希望将数组值保留在同一列中，而不是将它们分成不同的列，这也会在CSV.

Answer 1

尝试将数组转换为字符串，然后用另一个分隔符替换逗号，例如分号 - 这样 csv 解释器不会拆分数组中的值并将其计为单个项目let records = {id: "5e8468e2db05ff589ca61b30",title: "Example Application Number 1",status: "Preparing Documents",principalInvestigator: "Mr. Harry Styles",coInvestigators: ["Niall Horan, Liam Payne, Zayn Malik, Louis Tomilson"]}&nbsp; &nbsp; const array = [Object.keys(records)].concat(records);&nbsp; &nbsp;let result = array.map(it => {&nbsp; &nbsp; &nbsp; &nbsp; let objectValues = Object.values(it);&nbsp; &nbsp; &nbsp; &nbsp; for (let i = 0; i < objectValues.length; i++) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (Array.isArray(objectValues[i])) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;objectValues[i]= objectValues[i]=(`[${objectValues[i].join(';')}]`).replace(/,/g, ';')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; return objectValues;&nbsp; &nbsp; }).join('\n');&nbsp; &nbsp; console.log(result);

Answer 2

我个人建议稍微改变你的解决方案。您不需要连接键和值，这会使.map()不必要的复杂化。这是我要做的：var keys = Object.keys(records);// we take the array of values, but we transform any array// in a string of values separated by +var values = Object.values(records).map(record => {&nbsp; &nbsp; if (Array.isArray(record))&nbsp; &nbsp; &nbsp; &nbsp; return record.join("+");&nbsp; &nbsp; return record;});// Now we can just put everything togethervar result = keys.join(",") + "\n" + values.join(",");console.log(result);

Answer 3

尝试始终设置一个最小的可重现示例（https://stackoverflow.com/help/minimal-reproducible-example）这是我的：最重要的部分是：const headers = Object.keys(records).join(',')const values = Object.values(records).map(child => {&nbsp; &nbsp; if (child instanceof Array){&nbsp; &nbsp; &nbsp; //return child.join('; ');&nbsp; &nbsp; &nbsp; const str = JSON.stringify(child).replace(/"/g, "'");&nbsp; &nbsp; &nbsp; return `"${str}"`;&nbsp; &nbsp; }else{&nbsp; &nbsp; &nbsp; return child;&nbsp; &nbsp; }}).join(',')const result = [headers, values].join('\n')我们将键和值分别放入一个数组中，然后将它们放入一个数组中并用新行连接它们[headers, values].join('\n')在地图内，您可以执行以下任一操作：const values = Object.values(records).map(child => {&nbsp; &nbsp; if (child instanceof Array){&nbsp; &nbsp; &nbsp; const str = JSON.stringify(child).replace(/"/g, "'");&nbsp; &nbsp; &nbsp; return `"${str}"`;&nbsp; &nbsp; }else{&nbsp; &nbsp; &nbsp; return child;&nbsp; &nbsp; }}).join(',')这使得数组字符串在 Excel 中显示如下："['Niall Horan','Liam Payne','Zayn Malik','Louis Tomilson']"或者你可以像这样做地图：const values = Object.values(records).map(child => {&nbsp; &nbsp; if (child instanceof Array){&nbsp; &nbsp; &nbsp; return child.join('; ');&nbsp; &nbsp; }else{&nbsp; &nbsp; &nbsp; return child;&nbsp; &nbsp; }}).join(',')然后 Excel 中的输出是这样的（除非您使用该语言环境，否则分号不会被读取为列分隔符 - 即德语语言环境）："Niall Horan; Liam Payne; Zayn Malik; Louis Tomilson"const recordObj = {&nbsp; id: "5e8468e2db05ff589ca61b30",&nbsp; title: "Example Application Number 1",&nbsp; status: "Preparing Documents",&nbsp; principalInvestigator: "Mr. Harry Styles",&nbsp; coInvestigators: ["Niall Horan", "Liam Payne", "Zayn Malik", "Louis Tomilson"],&nbsp; partners: null,&nbsp; funder: "EPSRC Standard research",&nbsp; researchGroup: "MedEng",&nbsp; scheme: "Travel Grant",&nbsp; requestedAmount: null,&nbsp; estimatedAmount: 1234,&nbsp; submissionDate: "2020-03-23T00:00:00.000+01:00",&nbsp; startDate: "2020-03-29T00:00:00.000+01:00",&nbsp; estimatedDuration: null,&nbsp; endDate: null,&nbsp; facility: null,&nbsp; comments: null,&nbsp; dateCreated: "2020-04-01T12:11:46.783+02:00",&nbsp; lastUpdated: "2020-04-01T12:11:46.783+02:00",&nbsp; dateDeleted: null}downloadCsv(recordObj)function downloadCsv(records) {&nbsp; &nbsp; //console.log(records);&nbsp; &nbsp; const headers = Object.keys(records).join(',')&nbsp; &nbsp; const values = Object.values(records).map(child => {&nbsp; &nbsp; &nbsp; &nbsp; if (child instanceof Array){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //return child.join('; ');&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; const str = JSON.stringify(child).replace(/"/g, "'");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return `"${str}"`;&nbsp; &nbsp; &nbsp; &nbsp; }else{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return child;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; }).join(',')&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; const result = [headers, values].join('\n')&nbsp; &nbsp; //console.log(headers);&nbsp; &nbsp; //console.log(values);&nbsp; &nbsp; console.log(result);&nbsp; &nbsp; let hiddenElement = document.createElement('a');&nbsp; &nbsp; hiddenElement.href = 'data:text/csv;charset=utf-8,' + encodeURI(result);&nbsp; &nbsp; hiddenElement.target = '_blank';&nbsp; &nbsp; hiddenElement.download = 'records.csv';&nbsp; &nbsp; hiddenElement.click();}.as-console-wrapper { max-height: 100% !important; top: 0; }