Javascript 格式化从电子表格中读取的数字

Formatting numbers read from spreadsheet

提问by jburgess

I'm writing a script that creates fixed-width-text output of the contents of a Google Apps Spreadsheet. I retrieve all the values of the current range using the range.getValues()method, then I loop through everything a couple times and generate a block of text that looks like a nicely-formatted table when pasted into a fixed-width-font email.

我正在编写一个脚本，用于创建 Google Apps 电子表格内容的固定宽度文本输出。我使用该range.getValues()方法检索当前范围的所有值，然后将所有内容循环几次并生成一个文本块，当粘贴到固定宽度字体的电子邮件中时，该文本块看起来像一个格式良好的表格。

The only issue I'm having is that I cannot replicate the number formatting. I can get the number formatting string using range.getNumberFormats(), but I cannot find a method for applying that formatting string within code. I tried using the TEXT (value, format) spreadsheet function, but apparently Google Apps Script does not yet support calling spreadsheet functions from within the JavaScript code itself (see this issuefor proof).

我遇到的唯一问题是我无法复制数字格式。我可以使用 获取数字格式字符串range.getNumberFormats()，但找不到在代码中应用该格式字符串的方法。我尝试使用 TEXT (value, format) 电子表格函数，但显然 Google Apps Script 尚不支持从 JavaScript 代码本身调用电子表格函数（请参阅此问题以获取证明）。

回答by Mogsdad

As of December 2015, the kludgy work-arounds can be discarded. Google has provided two methods to retrieve the literal string display values from cells. They haven't provided documentation yet, so here's a summary:

截至2015 年 12 月，可以丢弃笨拙的变通方法。Google 提供了两种方法来从单元格中检索文字字符串显示值。他们还没有提供文档，所以这里是一个摘要：

Range.getDisplayValue()
Returns the display value of the top-left cell in the range, as a String, containing the text value shown on the Sheets UI. Empty cells will return an empty string.

Range.getDisplayValue()
以字符串形式返回范围内左上角单元格的显示值，其中包含表格 UI 上显示的文本值。空单元格将返回一个空字符串。

Range.getDisplayValues()
Returns the rectangular grid of display values for this range. Returns a two-dimensional array of Strings, indexed by row, then by column. Empty cells will be represented by an empty string in the array. Remember that while a range index starts at 1, 1, the JavaScript array will be indexed from [0][0].

Range.getDisplayValues()
返回此范围的显示值的矩形网格。返回一个二维字符串数组，按行索引，然后按列索引。空单元格将由数组中的空字符串表示。请记住，虽然范围索引从 1, 1 开始，但 JavaScript 数组将从 [0][0] 开始索引。

Example:

例子：

Say we have a range in a spreadsheet that we are interested in, like this:

假设我们在电子表格中有一个我们感兴趣的范围，如下所示：

Those four cells include two of the trickiest formats to work around; date/time and scientific notation. But with the new methods, there's nothing to it.

这四个单元格包括两种最棘手的格式；日期/时间和科学记数法。但是有了新方法，就没什么了。

function demoDisplayValue() {
  var range = SpreadsheetApp.getActiveSheet().getRange("A1:B2");

  Logger.log( range.getDisplayValue() );
  Logger.log( range.getDisplayValues() );
}

Log:

日志：

[16-01-14 13:04:15:864 EST] 1/14/2016 0:00:00
[16-01-14 13:04:15:865 EST] [[1/14/2016 0:00:00, 5.13123E+35], [.53, 1,123 lb]]

回答by Orion Palmer

I found a JavaScript method for setting the number of digits after the decimal point called toFixed().

我找到了一种用于设置小数点后位数的 JavaScript 方法，称为 toFixed()。

Here's the documentation: http://www.w3schools.com/jsref/jsref_tofixed.asp

这是文档：http: //www.w3schools.com/jsref/jsref_tofixed.asp

num.toFixed(x) //where x = the number of digits after the decimal point.

My Google Apps script needed to return a message with a dollar value pulled from my spreadsheet. It looked terrible with 10 digits after the decimal point. This method applied to my variable solved the problem. I just added a $ in the text portion of the label.

我的 Google Apps 脚本需要返回一条消息，其中包含从我的电子表格中提取的美元值。小数点后 10 位数字看起来很糟糕。这种应用于我的变量的方法解决了这个问题。我只是在标签的文本部分添加了一个 $。

Hope this helps!

希望这可以帮助！

回答by Mogsdad

The list of pre-supplied formats is here. For some formats, a javascript equivalent is relatively straight-forward. For others, it's extremely difficult. And handling user-defined custom formats - good luck with that.

预先提供的格式列表在这里。对于某些格式，javascript 等效项相对简单。对于其他人来说，这是非常困难的。并处理用户定义的自定义格式 - 祝你好运。

Here's a screenshot showing cell content that has been replicated as html - not fixed, as you are doing, yet using the formats from the spreadsheet.

这是一个屏幕截图，显示已复制为 html 的单元格内容 - 不是固定的，正如您所做的那样，而是使用电子表格中的格式。

There are Google Apps Script helper functions that make it easier, Utilities.formatString()and Utilities.formatDate().

有 Google Apps Script 帮助函数可以让操作变得更简单，Utilities.formatString()而Utilities.formatDate().

For dates & times, like "h:mm:ss am/pm", the spreadsheet formats are almost what is needed for the utility - I've found that you just need to tweak the am/pm designation for some formats:

对于日期和时间，例如"h:mm:ss am/pm"，电子表格格式几乎是该实用程序所需的格式 - 我发现您只需要调整某些格式的 am/pm 名称：

  var format = cell.getNumberFormat();
  var jsFormat = format.replace('am/pm','a');
  var jsDate = Utilities.formatDate(
          date,
          this.tzone,
          jsFormat);

For dollars, e.g. "\"$\"#,##0.00":

对于美元，例如"\"$\"#,##0.00"：

  var dollars = Utilities.formatString("$%.2f", num);

For percent-formatted numbers, e.g. "0.00%":

对于百分比格式的数字，例如"0.00%"：

  var matches = format.match(/\.(0*?)%/);
  var fract = matches ? matches[1].length : 0;     // Fractional part
  var percent = Utilities.formatString("%.Xf%".replace('X',String(fract)), 100*num);

For exponentials, like "0.000E+00", utilize the javascript built-in toExponential(), and tweak the output to look more like the spreadsheet:

对于指数，例如"0.000E+00"，利用内置的 javascript toExponential()，并调整输出以使其看起来更像电子表格：

if (format.indexOf('E') !== -1) {
  var fract = format.match(/\.(0*?)E/)[1].length;  // Fractional part
  return num.toExponential(fract).replace('e','E');
}

You can just do a string comparison against the stored spreadsheet formats to pick the right converter.

您只需对存储的电子表格格式进行字符串比较即可选择正确的转换器。

And what do I mean by extremely difficult? Try to get a formatter that comes up with the exact same numerator and denominator choices Sheets makes for # ?/?and # ??/??! In the spreadsheet, it's a matter of formatting - in a script, it's much more...

我所说的极其困难是什么意思？尝试获得一个格式化程序，该格式化程序具有与 Sheets 所做的完全相同的分子和分母选择，# ?/?并且# ??/??! 在电子表格中，这是格式化问题 - 在脚本中，它更多......

回答by mpsbhat

Try the following google apps script

试试下面的谷歌应用程序脚本

function NumberFormat()
{var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 var cell = sheet.getRange("A1:B10");
 // Always show 2 decimal points
 cell.setNumberFormat("0,00,000.00");
}

回答by Henrique G. Abreu

Indeed, you can't call spreadsheet functions directly. But you can set the format using setNumberFormatsnormally. Take a look at the docs here.

实际上，您不能直接调用电子表格函数。但您可以setNumberFormats正常使用设置格式。看看这里的文档。