如何获得Google可视化表格，以便在存在格式化的单元格值时进行排序？ [英] How do I get a Google visualization Table to sort on formatted cell values when present?

问题描述

我使用大量Google可视化表格在单页多标签网页应用中显示各种数据。其中一些表格具有使用格式化值的列。当用户单击这些列的列标题时，数据将根据基础值的排序顺序排序，而不是格式化值的排序顺序。这会导致在某些情况下数据不会显示给用户进行排序。

我正在寻找解决这个问题的方法，可以在所有表​​中重用最好通过编写一个可附加到每个表的事件监听器来处理所有的细节，而不管在各种关联的数据表的各个列中使用的数据类型，而不管任何给定的列是否使用格式化的值。我确实有条件，如果列中的任何单元格使用格式化的值，那么该列中的所有单元格都会这样做，但我没有每列使用格式化值的条件。如果一列没有使用格式化的值，那么我想根据列中数据的类型（例如数字，字符串，日期等）进行正常排序。

<作为一个例子，我可以有如下的DataTable。 （这些数据不是实际的数据，但镜像情况对我来说是有问题的。）

  var dataTable = new google.visualization。 DataTable（{
 cols：[
 {type：'number'，label：'ID'}，
 {type：'number'，label：'Places'}，
 {type：'string'，label：'Things'} 
]，
 rows：[
 {c：[{v：1}，{v：102735，f：'Harbour Place '}，{v：'pet'，f：'Dalmation'}]}，
 {c：[{v：2}，{v：163848，f：'Alphaville'}，{v：'my '，f：'Top Favorites'}}}，
 {c：[{v：3}，{v：113787，f：'Beta City'}，{v：'ten'，f：'Best东西'}]} 
] 
}）; 
  

如果没有进行任何特殊配置，此表（如果在ID列上升序排列）看起来像这正如用户所期望的那样：

  ID地点事物
 ----------- ----------------------- 
 1 Harbour Place Dalmation 
 2 Alphaville热门收藏
 Beta City Best Things 
  

如果用户点击了Places列的标题，那么表看起来像这样，因为升序排序对基础数值执行：

  ID地点事物
 ----------- ----------------------- 
 1 Harbour Place Dalmation 
 3 Beta City Best Things 
 2 Alphaville Top Favorites 
  

然而，用户希望表格看起来像这样：

  ID位置事物
 ----------------------------- ----- 
 2 Alphaville热门收藏
 Beta City Best Things 
 1 Harbor Place Dalmation 
  

如果用户单击东西列的标题时，由于基础字符串值的排序顺序，表格会按如下方式升序排列：

  ID Places Places 
 ---------------------------------- 
 2 Alphaville热门收藏
 1 Harbor Place Dalmation 
 Beta City Best Things 
  

用户期待这样的：

  ID地方事情
 --------------- ------------------- 
 Beta City Best Things 
 1 Harbour Place Dalmation 
 2 Alphaville最喜欢的
  code> 

我在StackOverflow和Google上搜索过，没有发现有关这种特殊情况的任何讨论。所以我一直在编写我自己的事件监听器，但是当试图将它写成可以重用于我的任何表时，它很快就变得非常复杂。所以我错过了一些简单的东西？有没有其他人有过这样的情况并解决了它;如果是这样，你做了什么？

解决方案

您可以使用 DataView Class 设置行顺序



然后用DataView绘制图表



table.draw（dataView，options）;

获取行的顺序在'sort'中事件是棘手的部分，

，因为对于格式化值没有任何便利方法，例如 getDistinctValues

在下面的工作片段中，格式化的值被提取到一个数组中，

sorted，然后 getFilteredRows 用于获取格式化值的行索引

  google.charts.load（'current'，{callback：function（）{var dataTable = new google.visualization.DataTable {type：'number'，label：'ID'}，{type：'number'，label：'Places'}，{type：'string'，label：'Things'}]，rows：[{c：[ {v：2}，{v：163848，{v： f：'Alphaville'}，{v：'my'，f：'热门收藏夹'}}}，{c：[{v：3}，{v：113787，f：'Beta City'}，{v： '十'，f：'最好的东西'}]}]}）; //使用DataView设置行的顺序var dataView = new google.visualization.DataView（dataTable）; dataView.setRows（[1，2，0]）; var table = new google.visualization.Table（document.getElementById（'chart_div'））; var options = {//使用event设置排序顺序：'event'，//设置列的箭头sortColumn：1，sortAscending：true}; google.visualization.events.addListener（table，'sort'，function（props）{var sortValues = []; var sortRows = []; var sortDirection =（props.ascending）？1：-1; // load values into （var i = 0; i< dataTable.getNumberOfRows（）; i ++）{sortValues.push（{v：dataTable.getValue（i，props.column），f：dataTable.getFormattedValue（i，props.column ）}）;} sortValues.sort（function（row1，row2）{return row1.f.localeCompare（row2.f）* sortDirection;}）; //设置行索引sortValues.forEach（function（sortValue）{sortRows.push （dataTable.getFilteredRows（[{column：props.column，value：sortValue.v}]）[0]）;}）; //使用DataView设置行的顺序dataView.setRows（sortRows）; // set column arrow options.sortColumn = props.column; options.sortAscending = props.ascending; table.draw（dataView，options）;}）; table.draw（dataView，options）; }，packages：['table']}）;   
 

I'm using numerous Google visualization Tables to display a variety of data in a single-paged, multi-tabbed web app. Some of these tables have columns that use formatted values. When a user clicks on the column header for these columns, the data is sorted according to the sort order of the underlying values rather than the sort order of the formatted values. This results in some cases in the data not appearing to the user to be sorted.

I'm looking for a way to fix this that can be reused across all my Tables, preferably by writing one event listener that can be attached to each Table and will handle all of the specifics regardless of the data types used in the various columns of the various associated DataTables, and regardless of whether any given column uses formatted values. I do have the condition that if any cell in a column uses a formatted value, then all of the cells in that column do, but I do not have the condition that every column uses formatted values. If a column does not use formatted values, then I want the normal sort based on the type of data in the column (e.g., number, string, date, etc.).

As an example, I could have a DataTable like the following. (This data is not actual data, but mirror situations that are problematic for me.)

var dataTable = new google.visualization.DataTable({
    cols: [
        {type: 'number', label: 'ID'},
        {type: 'number', label: 'Places'},
        {type: 'string', label: 'Things'}
    ],
    rows: [
        {c:[{v:1},{v:102735,f:'Harbor Place'},{v:'pet',f:'Dalmation'}]},
        {c:[{v:2},{v:163848,f:'Alphaville'},{v:'my',f:'Top Favorites'}]},
        {c:[{v:3},{v:113787,f:'Beta City'},{v:'ten',f:'Best Things'}]}
    ]
});

Without my doing any special configuration, this table, if sorted ascending on the ID column, would look like this, as the user would expect:

ID   Places          Things
----------------------------------
1    Harbor Place    Dalmation
2    Alphaville      Top Favorites
3    Beta City       Best Things

If the user clicks on the header for the Places column, the table then looks like this, because the ascending sort is performed on the underlying numeric values:

ID   Places          Things
----------------------------------
1    Harbor Place    Dalmation
3    Beta City       Best Things
2    Alphaville      Top Favorites

The user, however, expects the table to look like this:

ID   Places          Things
----------------------------------
2    Alphaville      Top Favorites
3    Beta City       Best Things
1    Harbor Place    Dalmation

If the user clicks on the header for the Things column, the table would sort ascending as follows, because of the sort order of the underlying string values:

ID   Places          Things
----------------------------------
2    Alphaville      Top Favorites
1    Harbor Place    Dalmation
3    Beta City       Best Things

The user is expecting this:

ID   Places          Things
----------------------------------
3    Beta City       Best Things
1    Harbor Place    Dalmation
2    Alphaville      Top Favorites

I've searched on StackOverflow and on Google and haven't found any discussion of this particular situation. So I've been working on writing my own event listener, but it quickly became very involved when trying to write it as something that can be reused for any of my Tables. So am I missing something simple? Has anyone else had a situation like this and resolved it; if so, what did you do?

解决方案

you can use the DataView Class to set the row order

dataView.setRows([1, 2, 0]);

then draw the chart with the DataView

table.draw(dataView, options);

getting the order of the rows during the 'sort' event is the tricky part,
since there aren't any convenience methods for formatted values, such as getDistinctValues

in the following working snippet, the formatted values are extracted into an array,
sorted, then getFilteredRows is used to get the row index of the formatted value

google.charts.load('current', {
  callback: function () {
    var dataTable = new google.visualization.DataTable({
      cols: [
        {type: 'number', label: 'ID'},
        {type: 'number', label: 'Places'},
        {type: 'string', label: 'Things'}
      ],
      rows: [
        {c:[{v:1},{v:102735,f:'Harbor Place'},{v:'pet',f:'Dalmation'}]},
        {c:[{v:2},{v:163848,f:'Alphaville'},{v:'my',f:'Top Favorites'}]},
        {c:[{v:3},{v:113787,f:'Beta City'},{v:'ten',f:'Best Things'}]}
      ]
    });

    // use DataView to set order of rows
    var dataView = new google.visualization.DataView(dataTable);
    dataView.setRows([1, 2, 0]);

    var table = new google.visualization.Table(document.getElementById('chart_div'));
    var options = {
      // use event to set order
      sort: 'event',

      // set column arrow
      sortColumn: 1,
      sortAscending: true
    };

    google.visualization.events.addListener(table, 'sort', function(props) {
      var sortValues = [];
      var sortRows = [];
      var sortDirection = (props.ascending) ? 1 : -1;

      // load values into sortable array
      for (var i = 0; i < dataTable.getNumberOfRows(); i++) {
        sortValues.push({
          v: dataTable.getValue(i, props.column),
          f: dataTable.getFormattedValue(i, props.column)
        });
      }
      sortValues.sort(function (row1, row2) {
        return row1.f.localeCompare(row2.f) * sortDirection;
      });

      // set row indexes
      sortValues.forEach(function (sortValue) {
        sortRows.push(dataTable.getFilteredRows([{column: props.column, value: sortValue.v}])[0]);
      });

      // use DataView to set order of rows
      dataView.setRows(sortRows);

      // set column arrow
      options.sortColumn = props.column;
      options.sortAscending = props.ascending;

      table.draw(dataView, options);
    });

    table.draw(dataView, options);
  },
  packages: ['table']
});

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>

这篇关于如何获得Google可视化表格，以便在存在格式化的单元格值时进行排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！