javascript - Google Table Chart CSV 返回科学记数法而不是字符串

Question

我有一个脚本，可以在单击“下载 CSV”时创建 csv 文件。

当partId都是数字时，有没有办法让CSV将其识别为'string'(在DataTable中定义)科学记数法？

查看图片csv结果列B:

• 第 4 行 partId 以 1 开头 -> 显示为 1.23457E+12
• 第 5 行 partId 以 0 开头 -> 显示为 1.23457E+11(前导零被删除)

提前致谢。

// Load the Visualization API and the corechart package.
google.charts.load('current', {
  'packages': ['corechart', 'table', 'gauge', 'controls']
});

// Set a callback to run when the Google Visualization API is loaded.
google.charts.setOnLoadCallback(gChart0);

function gChart0() {
  drawChart();
}

function drawChart() {

  var result = [{
    "calendarWeek": "2017-W30",
    "partId": '1234567890xxx',
    "someNumber": 0 
  }, {
    "calendarWeek": "2017-W30",
    "partId": '1234567890yyy',
    "someNumber": 0 
  }, {
    "calendarWeek": "2017-W30",
    "partId": '1234567890111',
    "someNumber": 0 
  }, {
    "calendarWeek": "2017-W30",
    "partId": '0123456789222',
    "someNumber": 0 
  }];

  //Create DataTable
  var data = new google.visualization.DataTable();
  data.addColumn('string', 'Calendar Week');
  data.addColumn('string', 'Part Id');
  data.addColumn('number', 'Some Number');

  var dataArray = [];
  $.each(result, function(i, obj) {
    dataArray.push([
      obj.calendarWeek,
      obj.partId,
      obj.someNumber
    ]);
  });
  data.addRows(dataArray);

  //Options

  var dashboard = new google.visualization.Dashboard(document.getElementById('div_dashboard'));

  var categoryPicker1 = new google.visualization.ControlWrapper({
    controlType: 'StringFilter',
    containerId: 'div_categoryPicker1',
    options: {
      filterColumnIndex: 1,
      matchType: 'any',
      ui: {
        labelStacking: 'vertical',
        allowTyping: false,
        allowMultiple: false,
        allowNone: true
      }
    }
  });

  var table = new google.visualization.ChartWrapper({
    chartType: 'Table',
    containerId: 'div_table',
    options: {
      width: '100%',
      height: 'auto',
      page: 'enable',
      pageSize: '15',
      sort: 'enable',
      allowHtml: true
    }
  });

  google.visualization.events.addOneTimeListener(dashboard, 'ready', function() {
    document.getElementById("a_exportCSV").addEventListener("click", function() {
      var csvData = table.getDataTable();
      lib_export_CSV("a_exportCSV", csvData);
    });
  });

  dashboard.bind([categoryPicker1], [table]);
  dashboard.draw(data);

} //END  function drawChart()

function lib_export_CSV(elementID, data) {
  //This version takes care of adding the column names
  //https://stackoverflow.com/a/42956427/7763687

  var csvColumns;
  var csvContent;
  var downloadLink;

  // build column headings
  csvColumns = '';
  for (var i = 0; i < data.getNumberOfColumns(); i++) {
    csvColumns += data.getColumnLabel(i);
    if (i < data.getNumberOfColumns() - 1) {
      csvColumns += ',';
    }
  }
  csvColumns += '\n';

  // get data rows - .replace('#', '') because this one breaks encodeURI(csvContent) 
  csvContent = csvColumns + google.visualization.dataTableToCsv(data).replace('#', '');

  //New Download Link	- works in chrome and mozilla
  downloadLink = document.getElementById(elementID);
  downloadLink.href = 'data:text/csv;charset=utf-8,' + encodeURI(csvContent);
  downloadLink.download = 'data.csv';
  downloadLink.target = '_blank';
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.2.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>

<div id="div_dashboard"></div><br />
<div id="div_categoryPicker1"></div><br />
<div id="div_table"></div><br />
<a id="a_exportCSV" href="">Download CSV</a>

Answer 1

我无法找到使用 CSV 的解决方案，
其中包含所有数字的 partId 显示为字符串。

但是，如果 CSV 的目的是将数据下载到 Excel，
我们可以使用 XML Spreadsheet 2003 格式。
(这是一种旧格式，但仍然有效，我们可以用文本构建它)
即使文件扩展名是 .xml，默认情况下它应该在 Excel 中打开。

有一些限制，但主要是高级功能，
例如形状、图像、表格等。

此外，还有一些选项可以按照您想要的任何方式设置工作簿和工作表的格式。

以下是一些引用 Material ，可帮助您了解各种选项和技术。
XML Spreadsheet Reference
Introduction to SpreadsheetML

请参阅以下工作片段。
在这里，我使用 HTML 模板来存储 xml 并构建电子表格。
我只提供了构建和下载电子表格所需的最少内容。
但是，partId 列会根据需要显示。

// Load the Visualization API and the corechart package.
google.charts.load('current', {
  'packages': ['corechart', 'table', 'gauge', 'controls']
});

// Set a callback to run when the Google Visualization API is loaded.
google.charts.setOnLoadCallback(gChart0);

function gChart0() {
  drawChart();
}

function drawChart() {

  var result = [{
    "calendarWeek": "2017-W30",
    "partId": '1234567890xxx',
    "someNumber": 0
  }, {
    "calendarWeek": "2017-W30",
    "partId": '1234567890yyy',
    "someNumber": 0
  }, {
    "calendarWeek": "2017-W30",
    "partId": '1234567890111',
    "someNumber": 0
  }, {
    "calendarWeek": "2017-W30",
    "partId": '0123456789222',
    "someNumber": 0
  }];

  //Create DataTable
  var data = new google.visualization.DataTable();
  data.addColumn('string', 'Calendar Week');
  data.addColumn('string', 'Part Id');
  data.addColumn('number', 'Some Number');

  var dataArray = [];
  $.each(result, function(i, obj) {
    dataArray.push([
      obj.calendarWeek,
      obj.partId,
      obj.someNumber
    ]);
  });
  data.addRows(dataArray);

  //Options

  var dashboard = new google.visualization.Dashboard(document.getElementById('div_dashboard'));

  var categoryPicker1 = new google.visualization.ControlWrapper({
    controlType: 'StringFilter',
    containerId: 'div_categoryPicker1',
    options: {
      filterColumnIndex: 1,
      matchType: 'any',
      ui: {
        labelStacking: 'vertical',
        allowTyping: false,
        allowMultiple: false,
        allowNone: true
      }
    }
  });

  var table = new google.visualization.ChartWrapper({
    chartType: 'Table',
    containerId: 'div_table',
    options: {
      width: '100%',
      height: 'auto',
      page: 'enable',
      pageSize: '15',
      sort: 'enable',
      allowHtml: true
    }
  });

  google.visualization.events.addOneTimeListener(dashboard, 'ready', function() {
    document.getElementById("a_exportXML").addEventListener("click", function() {
      var csvData = table.getDataTable();
      lib_export_XML("a_exportXML", csvData);
    });
  });

  dashboard.bind([categoryPicker1], [table]);
  dashboard.draw(data);

} //END  function drawChart()

function lib_export_XML(elementID, data) {
  var blobExcelFile;
  var browserIsIE;
  var downloadLink;
  var formatDateISO;
  var xlCols;
  var xlRows;
  var xlSheet;
  var xlStyle;
  var xlType;
  var xlWb;

  // determine if current browser is IE
  browserIsIE = false || !!document.documentMode;

  // create google date formatter
  formatDateISO = new google.visualization.DateFormat({
    pattern: 'yyyy-MM-ddThh:mm:ssZ'
  });

  // build column headings
  xlRows = '';
  xlCols = '';
  for (var i = 0; i < data.getNumberOfColumns(); i++) {
    xlCols += renderTemplate('template-excel-cell', {
      Style: 's10',
      Type: 'String',
      Value: data.getColumnLabel(i)
    });
  }
  xlRows += renderTemplate('template-excel-row', {
    Columns: xlCols
  });

  // build rows
  for (var r = 0; r < data.getNumberOfRows(); r++) {
    xlCols = '';
    for (var c = 0; c < data.getNumberOfColumns(); c++) {
      // set style, type based on column type
      switch (data.getColumnType(c)) {
        case 'string':
          xlStyle = 's11';
          xlType = 'String';
          break;

        case 'number':
          xlStyle = 's12';
          xlType = 'Number';
          break;

        default:
          xlStyle = 's10';
          xlType = 'String';
      }

      // add column
      xlCols += renderTemplate('template-excel-cell', {
        Style: xlStyle,
        Type: xlType,
        Value: data.getValue(r, c)
      });
    }

    // add row
    xlRows += renderTemplate('template-excel-row', {
      Columns: xlCols
    });
  }

  xlSheet = renderTemplate('template-excel-sheet', {
    ColumnCount: data.getNumberOfColumns(),
    Name: 'Data',
    RowCount: data.getNumberOfRows() + 1,
    Rows: xlRows
  });

  xlWb = renderTemplate('template-excel-wb', {
    Author: 'WhiteHat',
    Created: formatDateISO.formatValue(new Date()),
    Company: 'Acme',
    Sheets: xlSheet
  });

  blobExcelFile = new Blob([xlWb], {type: 'data:application/vnd.ms-excel'});

  if (browserIsIE) {
    window.navigator.msSaveBlob(blobExcelFile, 'data.xml');
  } else {
    downloadLink = document.getElementById(elementID);
    downloadLink.href = URL.createObjectURL(blobExcelFile);
    downloadLink.download = 'data.xml';
  }
}

// render html template
function renderTemplate(templateId, templateValues) {
  var templateText;   // html string to return
  var templateValue;  // html value

  // get template html
  templateText = document.getElementById(templateId).innerHTML;

  // replace place holders with values
  if (templateValues) {
    for (var propHandle in templateValues) {
      if (templateValues.hasOwnProperty(propHandle)) {
        templateValue = '';
        if (templateValues[propHandle] !== null) {
          templateValue = templateValues[propHandle].toString();
        }
        if (templateValue.indexOf('$') > -1) {
          templateValue = templateValue.replace(new RegExp('\\$', 'g'), '$$$');
        }
        if (templateText.indexOf('{{' + propHandle + '}}') > -1) {
          templateText = templateText.replace(new RegExp('{{' + propHandle + '}}', 'g'), templateValue);
        }
      }
    }
  }
  return templateText.trim();
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>

<div id="div_dashboard">
  <div id="div_categoryPicker1"></div>
  <div id="div_table"></div>
</div>
<a id="a_exportXML" href="#">Download XML</a>

<!-- template: excel template - workbook -->
<script id="template-excel-wb" type="text/html">
  <?xml version="1.0"?>
  <?mso-application progid="Excel.Sheet"?>
  <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:html="http://www.w3.org/TR/REC-html40">
    <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
      <Author>{{Author}}</Author>
      <LastAuthor>{{Author}}</LastAuthor>
      <Created>{{Created}}</Created>
      <Company>{{Company}}</Company>
      <Version>14.00</Version>
    </DocumentProperties>
    <x:ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
      <x:ProtectStructure>False</ProtectStructure>
      <x:ProtectWindows>False</ProtectWindows>
    </x:ExcelWorkbook>
    <Styles>
      <!-- Default style -->
      <Style ss:ID="Default" ss:Name="Normal">
        <Alignment ss:Horizontal="Left" ss:Vertical="Center" />
        <Borders/>
        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000" />
        <Interior/>
        <NumberFormat/>
        <Protection/>
      </Style>

      <!-- General format -->
      <Style ss:ID="s10">
      </Style>

      <!-- Text format -->
      <Style ss:ID="s11">
        <NumberFormat ss:Format="@"/>
      </Style>

      <!-- Accounting format -->
      <Style ss:ID="s12">
        <NumberFormat ss:Format="_(* #,##0.0_);_(* \(#,##0.0\);_(* &quot;-&quot;??_);_(@_)"/>
      </Style>
    </Styles>
    {{Sheets}}
  </Workbook>
</script>

<!-- template: excel template - worksheet -->
<script id="template-excel-sheet" type="text/html">
  <ss:Worksheet ss:Name="{{SheetName}}">
    <ss:Table ss:ExpandedColumnCount="{{ColumnCount}}" ss:ExpandedRowCount="{{RowCount}}" x:FullColumns="1" x:FullRows="1">
      {{Rows}}
    </ss:Table>
    <x:WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
      <x:ProtectObjects>False</x:ProtectObjects>
      <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
  </ss:Worksheet>
</script>

<!-- template: excel template - row -->
<script id="template-excel-row" type="text/html">
  <ss:Row ss:AutoFitHeight="1">{{Columns}}</ss:Row>
</script>

<!-- template: excel template - cell value -->
<script id="template-excel-cell" type="text/html">
  <ss:Cell ss:StyleID="{{Style}}"><ss:Data ss:Type="{{Type}}">{{Value}}</ss:Data></ss:Cell>
</script>