转换为 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: nullfunder: "EPSRC Standard research"researchGroup: "MedEng"scheme: "Travel Grant"requestedAmount: nullestimatedAmount: 1234submissionDate: "2020-03-23T00:00:00.000+01:00"startDate: "2020-03-29T00:00:00.000+01:00"estimatedDuration: nullendDate: nullfacility: nullcomments: nulldateCreated: "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,scheme5e8468e2db05ff589ca61b30,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,schemeExcel 格式可能更容易理解。目前，导出后是这样的：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"]}

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

   let result = array.map(it => {

        let objectValues = Object.values(it);

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

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

               objectValues[i]= objectValues[i]=(`[${objectValues[i].join(';')}]`).replace(/,/g, ';')

            }

        }

        return objectValues;

    }).join('\n');

    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 => {

    if (Array.isArray(record))

        return record.join("+");

    return record;

});

// Now we can just put everything together

var 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 => {

    if (child instanceof Array){

      //return child.join('; ');

      const str = JSON.stringify(child).replace(/"/g, "'");

      return `"${str}"`;

    }else{

      return child;

    }

}).join(',')

const result = [headers, values].join('\n')

我们将键和值分别放入一个数组中，然后将它们放入一个数组中并用新行连接它们[headers, values].join('\n')

在地图内，您可以执行以下任一操作：

const values = Object.values(records).map(child => {

    if (child instanceof Array){

      const str = JSON.stringify(child).replace(/"/g, "'");

      return `"${str}"`;

    }else{

      return child;

    }

}).join(',')

这使得数组字符串在 Excel 中显示如下：

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

或者你可以像这样做地图：

const values = Object.values(records).map(child => {

    if (child instanceof Array){

      return child.join('; ');

    }else{

      return child;

    }

}).join(',')

然后 Excel 中的输出是这样的（除非您使用该语言环境，否则分号不会被读取为列分隔符 - 即德语语言环境）：

"Niall Horan; Liam Payne; Zayn Malik; Louis Tomilson"

const recordObj = {

  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

}

downloadCsv(recordObj)

function downloadCsv(records) {

    //console.log(records);

    const headers = Object.keys(records).join(',')

    const values = Object.values(records).map(child => {

        if (child instanceof Array){

          //return child.join('; ');

          const str = JSON.stringify(child).replace(/"/g, "'");

          return `"${str}"`;

        }else{

          return child;

        }

        

    }).join(',')

    

    const result = [headers, values].join('\n')

    //console.log(headers);

    //console.log(values);

    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();

}

.as-console-wrapper { max-height: 100% !important; top: 0; }