利用js写了一个处理excel小工具
根据excel的列进行分类然后生成一个新的excel,利用的是 xlsx.js来进行读取然后二重循环进行分类。
-原excel格式
目标是把它按照company进行分类,然后分别生成一个excel,如图
我觉着使用SQL内连接也可以做到,但是对于代码门外汉肯定不会不多bb直接上代码(有点硬编码谅解)
```javascript
<!DOCTYPE html>
<!--[if lt IE 7 ]><html class="ie ie6" lang="en"> <![endif]-->
<!--[if IE 7 ]><html class="ie ie7" lang="en"> <![endif]-->
<!--[if IE 8 ]><html class="ie ie8" lang="en"> <![endif]-->
<!--[if (gte IE 9)|!(IE)]><!--><html lang="en"> <!--<![endif]-->
<head>
<!-- Basic Page Needs
================================================== -->
<meta charset="utf-8">
<title>zSeeds - Free Html5 Templates</title>
<meta name="description" content="Free Responsive Html5 Css3 Templates">
<meta name="author" content="">
<!-- Mobile Specific Metas
================================================== -->
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<!-- CSS
================================================== -->
<!-- JS -->
<script src="js/xlsx.js"></script>
<script src=""></script>
</head>
<body id="page-top1" class="sub-page">
<section id="page-content">
<div class="wrap-container zerogrid">
<div id="contact-area">
<h4 class="">接口</h4>
<input type="file" accept=".xls,.xlsx" id="file"/>
<input type="text" accept=".xls,.xlsx" id="text1"/>
</div>
</div>
</div>
</section>
<!-- ========== Scripts ========== -->
<script src="js/jquery-1.9.1.min.js"></script>
<script src="js/bootstrap.js"></script>
<script type="text/javascript">
/**
* @description: sonme EXCEL tool
* @author: zhuang
* @Email: 418665906@qq.com
* @date: 2021 04.15
*/
var carData;
$(document).ready(function(){
var wb;//读取完成的数据
var rABS = false; //是否将文件读取为二进制字符串
function fixdata(data) { //文件流转BinaryString
var o = "",
l = 0,
w = 10240;
for(; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)));
o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)));
return o;
}
$("#file").change(function(){
if(!this.files) {
return;
}
var f = this.files[0];
var reader = new FileReader();
reader.onload = function(e) {
var data = e.target.result;
//console.log(data);
if(rABS) {
wb = XLSX.read(btoa(fixdata(data)),{
type: 'base64'
});
} else {
wb = XLSX.read(data,{
type: 'binary'
});
console.log(wb);
//遍历两次 ,然后特殊标记排除,然后分类成二维数组
// eval("A1")
var t= 1;
var obj2 = Object.assign({}, wb.Sheets);
var obj3 = Object.assign({}, wb.Sheets);
// obj2.Sheet1.="22";
// console.log(obj2);
var s= new Array();
var l = 0;
var m = 0;
var jk = 2;
var k = '';
//清洗对象
var obj3 = {
Sheet1:{
A1 :obj2.Sheet1["A1"],
B1 :obj2.Sheet1["B1"],
C1 :obj2.Sheet1["C1"],
D1 :obj2.Sheet1["D1"],
E1 :obj2.Sheet1["E1"],
F1 :obj2.Sheet1["F1"],
G1 :obj2.Sheet1["G1"],
H1 :obj2.Sheet1["H1"],
I1 :obj2.Sheet1["I1"],
J1 :obj2.Sheet1["J1"],
K1 :obj2.Sheet1["K1"],
L1 :obj2.Sheet1["L1"],
M1 :obj2.Sheet1["M1"],
N1 :obj2.Sheet1["N1"],
O1 :obj2.Sheet1["O1"],
P1 :obj2.Sheet1["P1"],
Q1 :obj2.Sheet1["Q1"],
R1 :obj2.Sheet1["R1"],
S1 :obj2.Sheet1["S1"],
T1 :obj2.Sheet1["T1"],
U1 :obj2.Sheet1["U1"],
V1 :obj2.Sheet1["V1"],
W1 :obj2.Sheet1["W1"],
X1 :obj2.Sheet1["X1"],
Y1 :obj2.Sheet1["Y1"],
Z1 :obj2.Sheet1["Z1"]
}
};
obj3.Sheet1["!margins"]=obj2.Sheet1["!margins"];
obj3.Sheet1["!ref"]=obj2.Sheet1["!ref"];
console.log(obj3);
var lie = document.getElementById("text1").value;
for (var i=2;i;i++){
if (wb.Sheets.Sheet1.hasOwnProperty(lie+i)){
//obj3 = Object.assign({}, obj4);
if (wb.Sheets.Sheet1[lie+i]['w']=="#"){
continue;
}
else{
s[l] = new Array();
k = wb.Sheets.Sheet1[lie+i]['w'];
console.log(k);
for (var j=2;j;j++){
if (wb.Sheets.Sheet1.hasOwnProperty(lie+j)){
if (wb.Sheets.Sheet1[lie+j]['w']=="#"){
continue;
} else{
if (k==wb.Sheets.Sheet1[lie+j]['w']){
obj3.Sheet1["A"+jk] = obj2.Sheet1["A"+j];
obj3.Sheet1["B"+jk] = obj2.Sheet1["B"+j];
obj3.Sheet1["C"+jk] = obj2.Sheet1["C"+j];
obj3.Sheet1["D"+jk] = obj2.Sheet1["D"+j];
obj3.Sheet1["E"+jk] = obj2.Sheet1["E"+j];
obj3.Sheet1["F"+jk] = obj2.Sheet1["F"+j];
obj3.Sheet1["G"+jk] = obj2.Sheet1["G"+j];
obj3.Sheet1["H"+jk] = obj2.Sheet1["H"+j];
obj3.Sheet1["I"+jk] = obj2.Sheet1["I"+j];
obj3.Sheet1["J"+jk] = obj2.Sheet1["J"+j];
obj3.Sheet1["K"+jk] = obj2.Sheet1["K"+j];
obj3.Sheet1["L"+jk] = obj2.Sheet1["L"+j];
obj3.Sheet1["M"+jk] = obj2.Sheet1["M"+j];
obj3.Sheet1["N"+jk] = obj2.Sheet1["N"+j];
obj3.Sheet1["O"+jk] = obj2.Sheet1["O"+j];
obj3.Sheet1["P"+jk] = obj2.Sheet1["P"+j];
obj3.Sheet1["Q"+jk] = obj2.Sheet1["Q"+j];
obj3.Sheet1["R"+jk] = obj2.Sheet1["R"+j];
obj3.Sheet1["S"+jk] = obj2.Sheet1["S"+j];
obj3.Sheet1["T"+jk] = obj2.Sheet1["T"+j];
obj3.Sheet1["U"+jk] = obj2.Sheet1["U"+j];
obj3.Sheet1["V"+jk] = obj2.Sheet1["V"+j];
obj3.Sheet1["W"+jk] = obj2.Sheet1["W"+j];
obj3.Sheet1["X"+jk] = obj2.Sheet1["X"+j];
obj3.Sheet1["Y"+jk] = obj2.Sheet1["Y"+j];
obj3.Sheet1["Z"+jk] = obj2.Sheet1["Z"+j];
console.log(j);
wb.Sheets.Sheet1[lie+j]['w'] = "#";
s[l][m] = j;
m++;
jk++;
}
}
}else {
break;
}
}
console.log("ooooooooooooooooooo");
console.log(obj3);
openDownloadDialog( sheet2blob(obj3.Sheet1),k+".XLSX");
obj3 = {
Sheet1:{
A1 :obj2.Sheet1["A1"],
B1 :obj2.Sheet1["B1"],
C1 :obj2.Sheet1["C1"],
D1 :obj2.Sheet1["D1"],
E1 :obj2.Sheet1["E1"],
F1 :obj2.Sheet1["F1"],
G1 :obj2.Sheet1["G1"],
H1 :obj2.Sheet1["H1"],
I1 :obj2.Sheet1["I1"],
J1 :obj2.Sheet1["J1"],
K1 :obj2.Sheet1["K1"],
L1 :obj2.Sheet1["L1"],
M1 :obj2.Sheet1["M1"] ,
N1 :obj2.Sheet1["N1"],
O1 :obj2.Sheet1["O1"],
P1 :obj2.Sheet1["P1"],
Q1 :obj2.Sheet1["Q1"],
R1 :obj2.Sheet1["R1"],
S1 :obj2.Sheet1["S1"],
T1 :obj2.Sheet1["T1"],
U1 :obj2.Sheet1["U1"],
V1 :obj2.Sheet1["V1"],
W1 :obj2.Sheet1["W1"],
X1 :obj2.Sheet1["X1"],
Y1 :obj2.Sheet1["Y1"],
Z1 :obj2.Sheet1["Z1"]
}
};
obj3.Sheet1["!margins"]=obj2.Sheet1["!margins"];
obj3.Sheet1["!ref"]=obj2.Sheet1["!ref"];
jk=2;
l++;
}
} else {
break;
}
}
}
console.log(s);
// carData就是我们需要的JSON数据
// carData = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[1]]);
};
if(rABS) {
reader.readAsArrayBuffer(f);
} else {
reader.readAsBinaryString(f);
}
})
});
// csv转sheet对象
function csv2sheet(csv) {
var sheet = {}; // 将要生成的sheet
csv = csv.split('\n');
csv.forEach(function(row, i) {
row = row.split(',');
if(i == 0) sheet['!ref'] = 'A1:'+String.fromCharCode(65+row.length-1)+(csv.length-1);
row.forEach(function(col, j) {
sheet[String.fromCharCode(65+j)+(i+1)] = {v: col};
});
});
return sheet;
}
// 将一个sheet转成最终的excel文件的blob对象,然后利用URL.createObjectURL下载
// 将一个sheet转成最终的excel文件的blob对象,然后利用URL.createObjectURL下载
function sheet2blob(sheet, sheetName) {
sheetName = sheetName || 'Sheet1';
var workbook = {
SheetNames: [sheetName],
Sheets: {}
};
workbook.Sheets[sheetName] = sheet;
// 生成excel的配置项
var wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table,官方解释是,如果开启生成速度会下降,但在低版本IOS设备上有更好的兼容性
type: 'binary'
};
var wbout = XLSX.write(workbook, wopts);
var blob = new Blob([s2ab(wbout)], {type:"application/octet-stream"});
// 字符串转ArrayBuffer
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
return blob;
}
// 将一个sheet转成最终的excel文件的blob对象,然后利用URL.createObjectURL下载
function f2b(xsl) {
var wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table,官方解释是,如果开启生成速度会下降,但在低版本IOS设备上有更好的兼容性
type: 'binary'
};
var wbout = XLSX.write(xsl, wopts);
var blob = new Blob([s2ab(wbout)], {type:"application/octet-stream"});
// 字符串转ArrayBuffer
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
return blob;
}
/**
* 通用的打开下载对话框方法,没有测试过具体兼容性
* @param url 下载地址,也可以是一个blob对象,必选
* @param saveName 保存文件名,可选
*/
function openDownloadDialog(url, saveName)
{
if(typeof url == 'object' && url instanceof Blob)
{
url = URL.createObjectURL(url); // 创建blob地址
}
var aLink = document.createElement('a');
aLink.href = url;
aLink.download = saveName || ''; // HTML5新增的属性,指定保存文件名,可以不要后缀,注意,file:///模式下不会生效
var event;
if(window.MouseEvent) event = new MouseEvent('click');
else
{
event = document.createEvent('MouseEvents');
event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
}
aLink.dispatchEvent(event);
}
// 传入csv,执行后就会弹出下载框
function exportExcel(csv) {
var sheet = csv2sheet(csv);
var blob = sheet2blob(sheet);
openDownloadDialog(blob, '导出.xlsx');
}
</script>
</div>
</body></html>