[V14 新功能预览] Multiple Column Picker 多列下拉列表

在 V14 的新版本中，SpreadJS 将支持以表格的形式展示下拉列表，如下图



用户可以通过设定数据源的方式自定义下拉列表内容，支持以下两种类型：

	设置 ColumnBindingInfo	未设置 ColumnBindingInfo
一维数组	根据列信息绑定数据	自动绑定数据
由公式返回的数组	根据列信息绑定计算后的数据数组	计算后的第一行作为列信息，从第二行开始作为数据源

通过鼠标滚动，并选择一行数据返回

通过键盘上下选择行，并通过回车键选择数据

实例代码：

# 数据源为数组

1. let dataSource = [
   
2.     {
   
3.         "Film": "27 Dresses",
   
4.         "Genre": "Comedy",
   
5.         "Lead Studio": "Fox",
   
6.         "Audience Score %": 71,
   
7.         "Profitability": 5.34,
   
8.         "Rating": 40,
   
9.         "Worldwide Gross": 160.30,
   
10.         "Year": 2008
    
11.     }
    
12. ];
    
13. let colInfos = [
    
14.     { name: "Film", displayName: "FILM", size: "2*" },
    
15.     { name: "Genre", displayName: "GENRE", size: "*" },
    
16.     { name: "Lead Studio", size: "*" },
    
17.     { name: "Audience Score %", size: 120},
    
18.     { name: "Worldwide Gross", size: 100, formatter: "$#,##0.00"},
    
19.     { name: "Year", size: 50 }
    
20. ];
    
21. let style = new GC.Spread.Sheets.Style();
    
22. style.cellButtons = [
    
23.     {
    
24.         imageType: GC.Spread.Sheets.ButtonImageType.dropdown,
    
25.         command: "openMultiColumn",
    
26.         useButtonStyle: true,
    
27.     }
    
28. ];
    
29. style.dropDowns = [
    
30.     {
    
31.         type: GC.Spread.Sheets.DropDownType.multiColumn,
    
32.         option: {
    
33.             width: 700,
    
34.             height: 400,
    
35.             dataSource: dataSource,
    
36.             bindingInfos: colInfos
    
37.         }
    
38.     }
    
39. ];
    
40. sheet.setStyle(0, 0, style);

复制代码

# 数据源为区域公式

1. let dataSource = [
   
2.     {
   
3.         "Film": "27 Dresses",
   
4.         "Genre": "Comedy",
   
5.         "Lead Studio": "Fox",
   
6.         "Audience Score %": 71,
   
7.         "Profitability": 5.34,
   
8.         "Rating": 40,
   
9.         "Worldwide Gross": 160.30,
   
10.         "Year": 2008
    
11.     }
    
12. ];
    
13. let colInfos = [
    
14.     { name: "Film", displayName: "FILM", size: "2*" },
    
15.     { name: "Genre", displayName: "GENRE", size: "*" },
    
16.     { name: "Lead Studio", size: "*" },
    
17.     { name: "Audience Score %", size: 120},
    
18.     { name: "Worldwide Gross", size: 100, formatter: "$#,##0.00"},
    
19.     { name: "Year", size: 50 }
    
20. ];
    
21. let style = new GC.Spread.Sheets.Style();
    
22. style.cellButtons = [
    
23.     {
    
24.         imageType: GC.Spread.Sheets.ButtonImageType.dropdown,
    
25.         command: "openMultiColumn",
    
26.         useButtonStyle: true,
    
27.     }
    
28. ];
    
29. style.dropDowns = [
    
30.     {
    
31.         type: GC.Spread.Sheets.DropDownType.multiColumn,
    
32.         option: {
    
33.             width: 700,
    
34.             height: 400,
    
35.             dataSource: dataSource,
    
36.             bindingInfos: colInfos
    
37.         }
    
38.     }
    
39. ];
    
40. sheet.setStyle(0, 0, style);
    
41. 2. DataSource is range formula:
    
42. 
    
43. // The data source
    
44. sheet.setText(0, 0, "27 Dresses");
    
45. sheet.setText(0, 1, "Comedy");
    
46. sheet.setText(0, 2, "Fox");
    
47. sheet.setText(1, 0, "(500) Days of Summer");
    
48. sheet.setText(1, 1, "Comedy");
    
49. sheet.setText(1, 2, "Fox");
    
50. 
    
51. let dataSource = "Sheet1!$A$1:$C$2";
    
52. let colInfos = [
    
53.     { name: "Film", displayName: "FILM", size: "2*" },
    
54.     { name: "Genre", displayName: "GENRE", size: "*" },
    
55.     { name: "Lead Studio", size: "*" },
    
56. ];
    
57. let style = new GC.Spread.Sheets.Style();
    
58. style.cellButtons = [
    
59.     {
    
60.         imageType: GC.Spread.Sheets.ButtonImageType.dropdown,
    
61.         command: "openMultiColumn",
    
62.         useButtonStyle: true,
    
63.     }
    
64. ];
    
65. style.dropDowns = [
    
66.     {
    
67.         type: GC.Spread.Sheets.DropDownType.multiColumn,
    
68.         option: {
    
69.             dataSource: dataSource,
    
70.             bindingInfos: colInfos
    
71.         }
    
72.     }
    
73. ];
    
74. sheet.setStyle(0, 0, style);

复制代码

# 数据源为表格区域

1. var data = [
   
2.     { name: "Yang", age: 24 },
   
3.     { name: "Wang", age: 35 },
   
4.     { name: "Zhang", age: 20 }
   
5. ];
   
6. sheet.setDataSource(new GC.Spread.Sheets.Bindings.CellBindingSource({
   
7.     name: "Yang",
   
8.     age: 24,
   
9.     country: "China",
   
10.     city: "Xi'an",
    
11.     ds: data
    
12. }));
    
13. var table = sheet.tables.add("tableSource", 6, 0, 1, 1);
    
14. table.bindingPath("ds");
    
15. 
    
16. var style = new GC.Spread.Sheets.Style();
    
17. style.cellButtons = [
    
18.     {
    
19.         imageType: GC.Spread.Sheets.ButtonImageType.dropdown,
    
20.         command: "openMultiColumn",
    
21.         useButtonStyle: true,
    
22.     }
    
23. ];
    
24. style.dropDowns = [
    
25.     {
    
26.         type: GC.Spread.Sheets.DropDownType.multiColumn,
    
27.         option: {
    
28.             width: 180,
    
29.             height: 120,
    
30.             dataSource: "=tableSource[[#Headers], [#Data]]"
    
31.         }
    
32.     }
    
33. ];
    
34. sheet.setStyle(1, 6, style);
    
35. sheet.getCell(1, 6).wordWrap(true);
    
36. sheet.setColumnWidth(6, 400);
    
37. sheet.setRowHeight(1, 50);

复制代码

# 数据源是公式返回数组

1. var colInfos = [
   
2.     { name: "Rate1", size: "*" },
   
3.     { name: "Rate2", size: "*" },
   
4.     { name: "Rate3", size: "*" },
   
5. ];
   
6. var style = new GC.Spread.Sheets.Style();
   
7. style.cellButtons = [
   
8.     {
   
9.         imageType: GC.Spread.Sheets.ButtonImageType.dropdown,
   
10.         command: "openMultiColumn",
    
11.         useButtonStyle: true,
    
12.     }
    
13. ];
    
14. style.dropDowns = [
    
15.     {
    
16.         type: GC.Spread.Sheets.DropDownType.multiColumn,
    
17.         option: {
    
18.             width: 400,
    
19.             height: 120,
    
20.             dataSource: "=RANDARRAY(3,3)",
    
21.             bindingInfos: colInfos
    
22.         }
    
23.     }
    
24. ];
    
25. sheet.setStyle(1, 6, style);
    
26. sheet.getCell(1, 6).wordWrap(true);
    
27. sheet.setColumnWidth(6, 400);
    
28. sheet.setRowHeight(1, 50);

复制代码