如:排序,分组,数据绑定,拖动复制等。
快速启动
1:下载必须包
2:引入基础文件
<script data-jsfiddle="common" src="../demo/js/jquery.min.js"></script>
<script src="../dist/handsontable.full.js"></script>
<link rel="stylesheet" media="screen" href="../dist/handsontable.full.css">
3然后添加一个用于呈现 Excel 编辑表格的 DIV
<div id="example"></div>
4初始化
<script>
var data = [//四行五列
["", "Kia", "Nissan", "Toyota", "Honda"],
["2008", 10, 11, 12, 13],
["2009", 20, 11, 14, 13],
["2010", 30, 15, 12, 13]
];
$("#example").handsontable({
data: data,//初始化时候的数据
minSpareRows:5,//空出多少行
colHeaders:true,//显示表头
contextMenu:true//显示表头下拉菜单
})
</script>
效果
Handsontable API
先给出一些基础数据
<!doctype html>
<html>
<head>
<meta charset='utf-8'>
<title> Handsontable</title>
<script data-jsfiddle="common" src="../demo/js/jquery.min.js"></script>
<script src="../dist/handsontable.full.js"></script>
<link rel="stylesheet" media="screen" href="../dist/handsontable.full.css">
</head>
<body>
<div id="example"></div>
<script>
var data = [//四行五列
["2000", 10, 11, 12, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,6,3,5,6,3,5,3,87,6,8,5,6,6,35,6,3,6,3,8,38,3],
["2001", 10, 11, 12, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,6,3,5,6,3,5,3,87,6,8,5,6,6,35,6,3,6,3,8,38,3],
["2002", 10, 11, 12, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,6,3,5,6,3,5,3,87,6,8,5,6,6,35,6,3,6,3,8,38,3],
["2003", 10, 11, 12, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,6,3,5,6,3,5,3,87,6,8,5,6,6,35,6,3,6,3,8,38,3],
["2004", 10, 11, 12, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,6,3,5,6,3,5,3,87,6,8,5,6,6,35,6,3,6,3,8,38,3],
["2005", 10, 11, 12, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,6,3,5,6,3,5,3,87,6,8,5,6,6,35,6,3,6,3,8,38,3],
["2006", 10, 11, 12, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,6,3,5,6,3,5,3,87,6,8,5,6,6,35,6,3,6,3,8,38,3],
["2007", 10, 11, 12, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,6,3,5,6,3,5,3,87,6,8,5,6,6,35,6,3,6,3,8,38,3],
["2008", 10, 11, 12, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,6,3,5,6,3,5,3,87,6,8,5,6,6,35,6,3,6,3,8,38,3],
["2009", 20, 11, 14, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,6,3,5,6,3,5,3,8,6,78,5,6,6,35,6,3,6,3,8,38,3],
["2010", 30, 15, 12, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,6,3,52,6,3,5,3,8,6,8,5,6,56,355,6,3,66,23,8,38,3],
["2011", 20, 11, 14, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,26,3,5,26,3,5,3,8,6,8,5,56,6,35,6,3,6,23,8,38,3],
["2012", 20, 11, 14, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,666,5,8,6,3,9,6,3,5,56,3,5,3,78,6,58,55,6,6,35,6,23,6,3,8,38,3],
["2013", 20, 11, 14, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,68,6,3,9,6,3,5,26,3,5,3,8,6,8,5,6,6,35,6,3,6,3,8,38,3],
["2014", 20, 11, 14, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,6,3,5,26,3,5,3,8,6,58,5,6,6,35,6,3,6,3,8,38,3],
["2015", 20, 11, 14, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,29,26,3,5,6,3,5,3,78,76,8,5,6,6,35,6,3,6,3,8,38,3],
["2016", 20, 11, 14, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,6,3,55,6,3,5,3,8,6,28,5,6,6,35,6,3,6,3,8,38,3],
["2017", 20, 11, 14, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,43,9,6,3,5,6,3,5,3,8,6,8,5,6,6,35,6,3,6,3,8,38,3],
["2018", 20, 11, 14, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,62,3,29,6,3,5,6,3,5,3,8,6,8,5,6,6,35,6,3,6,3,8,38,3],
["2019", 20, 11, 14, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,48,6,3,9,6,3,5,6,3,5,3,8,6,8,5,6,6,35,6,3,6,3,8,38,3],
["2020", 20, 11, 14, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,68,6,3,9,6,3,5,6,3,5,3,8,6,8,5,6,6,345,6,3,64,3,8,38,3],
["2021", 20, 11, 14, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,6,3,5,6,3,5,3,8,6,8,5,6,6,35,6,3,6,3,8,38,3],
["2022", 20, 11, 14, 13,14,15,16,17,18,19,20,21,22,22,3,3,55,66,5,8,6,3,9,66,3,5,6,3,5,3,8,6,8,5,6,6,35,6,3,6,3,48,38,23]
];
$("#example").handsontable({
data: data
})
</script>
</body>
</html>
效果
数据data的介绍
数据显示
data:[[第一行数据],[第二行数据],...[第n行数据]]/对象数组
获取数据的方法:hot.getData()。
加载数据的方法:hot.loadData(data)。
当不需要显示某一列的时候可用如下格式设置:
columns:[{data:0},{data:2}]
这里就不显示第二列数据,只有第1、3列数据
现在我们在这个效果之上演示它的API
一:常规设置
1,冻结行列
1.1:fixedRowsTop 冻结行(固定顶部开始算起指定行不随垂直滚动条滚动;
1.2:fixedColumnsLeft 冻结列(固定右边开始算起指定行不随水平滚动条滚动);
2,显示表头
2.1:colHeaders:true 显示列表头, 默认false, 可取 true/fals/数组 ,当值为数组时,列头为数组的值
2.2:rowHeaders:true 显示行表头, 默认 false, 可取 true/fals/数组,当值为数组时,行头为数组的值
3:允许通过拖曳行表头/列表头来改变行列的大小
3.1: manualColumnResize:true 允许拖曳列表头,默认为false
3.2: manualRowResize:true 允许拖曳行表头,默认为false
4:显示表头下拉菜单
4.1: contextMenu:true 显示表头的下拉菜单默认false 可取 true/false/自定义数组
5:手动设置冻结列
5.1:manualColumnFreeze:true 设置true后在单元格中右击出现一个菜单,此菜单会多出一个“Unfteeze this columu”的选项,再次点击就会取消冻结动作。 默认为false
6:给当前行/列的一个样式名
6.1:currentRowClassName:"cur" 给当前行设置样式名(鼠标点击某个单元格,则整行给样式)
6.2:currentColClassName:"cur" 给当前行列设置样式名(鼠标点击某个单元格,则整行给样式)
7:排序
7.1:columnSorting:true 通过点击列表头进行排序(没有图标)
当值为true时,表示启用排序插件
当值为true时,排序插件的使用可通过点击列头文字实现。
当值为false时表示禁用排序。当值为对象时,
该对象包含两个属性:column:列数。
sortOrder:true/false,升序或降序,
true为升序排列。
当用对象启动插件后可用如下方式禁用插件:
hot.updateSettings({
columnSorting:false
});
排序的使用也可已直接调用sort()方法实现。如下操作:
if(hot.sortingEnabled){
hot.sort(行数,true/false);
//true为升序,false为降序
}
8:自适应列大少
8.1:autoColumnSize:true true/false //当值为true且列宽未设置时,自适应列大小
9:启用observeChanges插件
9.1:observeChanges:true/false //当值为true时,启用observeChanges插件
10:设置列宽
10.1:colWidths:[10,5,50],
11:延伸列的宽度
11.1:stretchH:"last" 可取 last/all/none last:延伸最后一列,all:延伸所有列,none默认不延伸。
12: 行分组或列分组
12.1:groups:[{cols:[0,2]},{cols:[3,5]},{rows:[0,4]},{rows:[5,7]}]
上面的例子介绍了4个分组,第0-2列为一组,第3-5列为第二组,第0-4行为一组,第5-7列为第二组。分组可在行头或列头看见,分组可被展开或隐藏。
13:拖动行或列到某一行或列之后
13.1:manualColumnMove:true/false 当值为true时,列可拖拽移动到指定列
13.2:manualRowMove:true/false当值为true时,行可拖拽至指定行
当属性的值为true时,行头或列头可以被拖拽移动,移动后该行或列将会被移动到指定位置,原先该行或列的后面部分自动上移或后退。移动的时候鼠标需选中行线或列线才行。
注意:
比如你移动的是列,
那么请你把鼠标放到列表头中,当出现一个手型时候可以拖动,拖动的只是数据,列表头不会跟着移动。
其他
minCols:最小列数
minRows:最小行数
minSpareCols:添加空列
maxCols:最大列数
maxRows:最大行数
minSpareRows:添加空行
用法
$("#example").handsontable({
data: data,//初始化时候的数据
fixedRowsTop:2,
fixedColumnsLeft:3,
colHeaders:data2,//显示l表头
rowHeaders:true,//显示表头
manualColumnResize:true,
manualRowResize:true,
contextMenu:true,
manualColumnFreeze:true,
manualColumnMove:true,
currentColClassName:"cur",
columnSorting:true,
/*columns:[{data:0},{data:2}],*/
autoColumnSize:true,
minSpareRows:2,
observeChanges:true,
colWidths:[10,5,50],
stretchH:"last",
groups:[{cols:[0,2]},{cols:[3,5]},{rows:[0,4]},{rows:[5,7]}],
autoColumnSize:true,
manualColumnMove:true,
manualRowMove:true
});
二:方法
为了获取这个handsontable对象,我们换一种方式初始化
var container = document.getElementById('example');
var hot = new Handsontable(container,
{
data: data
});
console.log(hot)
打印出来后,我们可以看到它提供的方法
现在我们抽出一些简单讲解下
用法
对象.方法(参数)
这里是
hot.alter('insert_row',0,2,' ',true);
1:添加行或列,删除行或列
alter (action,index,amount,source,keepEmptyRows)
action:有4个值insert_row,insert_col,remove_row,remove_col
index:行索引,当存在时在索引处插入或删除,不存在时在当前行后插入
amount:默认值为1表示插入多少行/列
source:行或列对象
keepEmptyRows:true/false,当值为true时,空行不被删除。
2:清空表数据
clear()
3:统计表格的所有列总数并返回
3.1: countRows():统计表格的总行数,并返回
console.log(hot.countCols())
4:取消当前被选中的单元格
deselectCell()
5:移除dom对象中的表格
destory()
6:去除当前编辑器,并选中当前单元格,渲染上该效果
destoryEditor(reverOriginal)
去除当前编辑器,并选中当前单元格,渲染上该效果。如果reverOriginal不是true则被编辑的数据将被保存,如果为true,则会恢复之前的数据,不保存新的数据到单元格。
7:返回一个活跃的编辑对象。
getActiveEditor()
8:根据行列索引获取单元格的值
getCopyableData(startRow,startCol,endRow,endCol)
三:一些常用例子
3.1:需要表格占满父屏幕
初始效果
![](https://i-blog.csdnimg.cn/blog_migrate/c1ac45e5ec9ef5be82e10360a7acfd4c.jpeg)
四:综合例子
写出基础html结构
<!doctype html>
<html>
<head>
<meta charset='utf-8'>
<title>Handsontable</title>
<link rel="stylesheet" media="screen" href="handsontable.full.min.css">
<script src="handsontable/handsontable.full.min.js"></script>
<script src=" http://apps.bdimg.com/libs/jquery/2.1.4/jquery.js"></script>
</head>
<body>
<div id="example" ></div>
</body>
</html>
代码
var w=jQuery(".tb_bg").width();
var h=(jQuery(window).height()-jQuery("#tableData").offset().top)-90;
hot = new Handsontable(container, {
width:w,
height:h,
stretchH: 'all'})
改后效果
参考文献
- https://github.com/handsontable/handsontable
- http://www.cnblogs.com/sily-boy/p/4761011.html
- http://docs.handsontable.com/0.19.0/AutoColumnSize.html#firstCalculation
- https://handsontable.com/examples.html?_ga=1.163459125.80588291.1462515723&headers