是这样,通常情况下标出重复项和去重复项这种比较简单的工作交给excel就可以了,但是excel有时候会出现无法处理的情况,比如说一串数字太长,会自动用科学计数法来表示,这个时候如果标出重复项就会出现乱标记的情况,如果将多位的数字转换成字符串也会时好时坏,百度搜了许久,无果。
我默默的问候了一下excel的开发者的母亲,然后决定自己搞一个,于是就又多了一个小工具:
<!DOCTYPE html>
<html>
<head>
<title>标出重复项</title>
<meta charset="utf-8" />
<style type="text/css">
.shower{
width:400px;
height:400px;
margin:10px auto;
padding:0;
font-size:10px;
font-family:宋体;
}
button{
margin:10px;
}
</style>
</head>
<body style="width:100%;height:100%;margin:0;">
<div style="line-height:50px;text-align:center;width:100%;height:50px;color:white;background-color:deepskyblue;font-weight:bold;">标出重复项</div>
<table style="border-collapse:collapse;width:100%;height:100%;text-align:center;">
<tr>
<td colspan="2">请将excel内容复制粘贴到左边再点击相关功能</td>
</tr>
<tr>
<td><textarea id="in" class="shower"></textarea><br></td>
<td><div id="out" class="shower" contenteditable="true" style="overflow:auto;border:1px solid gray;background-color:#d7edf8"></div></td>
</tr>
<tr>
<td><button onclick="layout()">标出</button><button onclick="deleteduplicates()">去重</button></td>
<td><button onclick="copy()">复制结果到剪贴板</button></td>
</tr>
<tr>
<td colspan="2" id="say"></td>
</tr>
</table>
</body>
<script type="text/javascript">
function copy() {
var div = id('out');
if (document.body.createTextRange) {
var range = document.body.createTextRange();
range.moveToElementText(div);
range.select();
} else if (window.getSelection) {
var selection = window.getSelection();
var range = document.createRange();
range.selectNodeContents(div);
selection.removeAllRanges();
selection.addRange(range);
/*if(selection.setBaseAndExtent){
selection.setBaseAndExtent(text, 0, text, 1);
}*/
} else {
console.warn("none");
}
document.execCommand("Copy"); // 执行浏览器复制命令
var d = new Date();
id("say").innerHTML = d.getHours() + ':' + d.getMinutes() + ':' + d.getSeconds() + ' 已复制到剪贴板';
}
function id(x){
return document.getElementById(x);
}
var _2darr = [];
var hash = {};
function store(){
_2darr = [];
hash = {};
var invalue = '';
if(window.navigator.userAgent.indexOf("MSIE")>=1){
invalue = (id("in").value+'\r').split('\n');
}
else{
invalue = (id("in").value).split('\n');
}
for(var i=0;i<invalue.length;i++){
_2darr[i] = [];
var ti = invalue[i].split('\t');
for(var j=0;j<ti.length;j++){
_2darr[i].push(ti[j]);
}
}
}
function layout(){
store();
for(var i=0;i<_2darr.length;i++){
var mark = '';
for(var j=0;j<_2darr[i].length;j++){
mark += _2darr[i][j];
}
if(mark in hash){
hash[mark]+=1;
}
else{
hash[mark]=1;
}
}
var len = _2darr[0].length;
var tmp = '<table style="border-collapse:collapse;font-family:宋体;font-size:10px;">'
for(var i=0;i<_2darr.length;i++){
tmp += '<tr>';
var mark = '';
for(var k=0;k<_2darr[i].length;k++){
mark += _2darr[i][k];
}
for(var j=0;j<len;j++){
tmp += '<td style="height:12px;padding:0;border:1px solid;background-color:'+ (hash[mark]>1?'orange':'') +'"><span>'+ (_2darr[i][j]==undefined?'':_2darr[i][j]) +'</span></td>';
}
tmp += '</tr>';
}
tmp += '</table>';
id("out").innerHTML = tmp;
id("say").innerHTML = '总条目数:'+_2darr.length;
}
function deleteduplicates(){
store();
for(var i=0;i<_2darr.length;i++){
var mark = '';
for(var j=0;j<_2darr[i].length;j++){
mark += _2darr[i][j];
}
if(mark in hash){
hash[mark]+=1;
}
else{
hash[mark]=1;
}
}
var len = _2darr[0].length;
var hash2 = {};
var tmp = '<table style="border-collapse:collapse;font-family:宋体;font-size:10px;">'
var sum = 0;
for(var i=0;i<_2darr.length;i++){
var mark = '';
for(var k=0;k<_2darr[i].length;k++){
mark += _2darr[i][k];
}
if(mark in hash2){
hash2[mark]+=1;
}
else{
hash2[mark]=1;
}
if(hash2[mark]&&hash2[mark]==1){
sum+=1;
tmp += '<tr>'
for(var j=0;j<len;j++){
tmp += '<td style="height:12px;padding:0;border:1px solid;"><span>'+ (_2darr[i][j]==undefined?'':_2darr[i][j]) +'</span></td>';
}
tmp += '</tr>';
}
}
tmp += '</table>';
id("out").innerHTML = tmp;
id("say").innerHTML = '去重后总条目数:'+sum;
}
</script>
</html>