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

Question

我正在尝试将对象值转换为 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
5e8468e2db05ff589ca61b30,Example Application Number 1,Preparing Documents,Mr. Harry Styles,[Niall Horan, Liam Payne, Zayn Malik, Louis Tomilson],Apple,Microsoft,XresearchGroup,YScheme

用Excel格式可能更容易理解。目前，导出后如下所示:/image/W3cMN.jpg

所需的外观为:/image/JJG0j.jpg

因此，我几乎希望将数组值保留在同一列中，而不是将它们分成不同的值，这也会在 CSV 中移动所有其他列。

Answer 1

尝试始终设置一个最小的可重现示例 ( 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, value].join('\n')

在 map 内，您可以执行以下操作:

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']"

或者你可以像这样制作 map :

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; }