纯前端下载表格多sheet

最新推荐文章于 2022-11-25 13:12:35 发布

lvxinaidou

最新推荐文章于 2022-11-25 13:12:35 发布

阅读量1.1k

点赞数 1

本文链接：https://blog.csdn.net/lvxinaidou/article/details/122714714

版权

Angular 同时被 3 个专栏收录

41 篇文章 1 订阅

订阅专栏

JavaScript

27 篇文章 0 订阅

订阅专栏

TypeScript

22 篇文章 0 订阅

订阅专栏

<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title></title>
</head>

<body>
	<script src="js/xlsx.min.js"></script>
	<script src="js/xlsx.full.min.js"></script>
	<script src="js/xlsx.core.min.js"></script>
	<style>
		

	</style>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.0/xlsx.full.min.js"></script>
	<!--<script src="js/cpexcel.js"></script>
	<script src="js/ods.js"></script>-->
	<!--<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.0/xlsx.min.js"></script>
	
	<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.0/xlsx.core.min.js"></script>-->
    <script>
        //如果使用 FileSaver.js 就不要同时使用以下函数
        function saveAs(obj, fileName) {//当然可以自定义简单的下载文件实现方式 
            var tmpa = document.createElement("a");
            tmpa.download = fileName || "下载";
            tmpa.href = URL.createObjectURL(obj); //绑定a标签
            tmpa.click(); //模拟点击实现下载
            setTimeout(function () { //延时释放
                URL.revokeObjectURL(obj); //用URL.revokeObjectURL()来释放这个object URL
            }, 100);
        }       
      var testArray = [
					{'测点名称':'CD01', '沉降':2,'温度':6,'角度':7,"时间":12},
					{'测点名称':null,'沉降':2,'温度':6,'角度':7,"时间":null},
					{'测点名称':null,'沉降':2,'温度':6,'角度':7,"时间":null},
					{'测点名称':null,'沉降':2,'温度':6,'角度':7,"时间":null},
					{'测点名称':'CD02', '沉降':4,'温度':12,'角度':14,"时间":12},
					{'测点名称':'CD03', '沉降':4,'温度':12,'角度':14,"时间":12}
				];
		  var testArray2 = [
					{'测点名称':'CD04', '沉降':2,'温度':6,'角度':7,"时间":13},
					{'测点名称':null,'沉降':2,'温度':6,'角度':7,"时间":null},
					{'测点名称':null,'沉降':2,'温度':6,'角度':7,"时间":null},
					{'测点名称':null,'沉降':2,'温度':6,'角度':7,"时间":null},
					{'测点名称':'CD05', '沉降':4,'温度':12,'角度':14,"时间":13},
					{'测点名称':'CD06', '沉降':4,'温度':12,'角度':14,"时间":13}
				];
				
				
        const wopts = { bookType: 'xlsx', bookSST: true, type: 'binary',cellStyles: true};//这里的数据是用来定义导出的格式类型 
        function downloadExl(data,data2,type) {
        	//多sheets应用
            var wb = { 
            	SheetNames: ['2018-10-01','2018-10-16'], 
            	Sheets: {
//          		 '2018-10-01': Object.assign({},data,{'A1':'E7'}),   
//          		 '2018-10-16':Object.assign({},data2,{'A1':'E7'})
            	},
            	Props: {} 
            };
            data = XLSX.utils.json_to_sheet(data); 
            data["!merges"] = [
                {s: {r: 1, c: 0}, e: {r: 4, c: 0}},
				{s: {r: 1, c: 4}, e: {r: 4, c: 4}},
            ];
            data['!cols'] = ([
            	{
            		wpx:200
            	},
            	{
            		wpx:200
            	}
            ]);
            
            wb.Sheets['2018-10-01'] = data;
            console.log(data);
          
            data2 = XLSX.utils.json_to_sheet(data2); 
            data2["!merges"] = [
                {s: {r: 1, c: 0}, e: {r: 4, c: 0}},
				{s: {r: 1, c: 4}, e: {r: 4, c: 4}},
            ];
            console.log(data2);
            wb.Sheets['2018-10-16'] = data2;
            
            //设置样式
            
			for(cell  in data){  
				if(cell != '!merges' && cell != '!ref') {
					console.log(data[cell]);
					data[cell].s = {
					  	font: {
				              name: '宋体',
				              sz: 14,
				              color: {rgb: "#00000000"},
				              bold: false,
				              italic: false,
				              underline: false
				        },
			            alignment: {
			              horizontal: "center" ,
			              vertical: "center"
			            },
			            fill: { bgColor: { indexed: 64 }}
			  		};   
				}
			}
			for(cell  in data2){   
			  if(cell != '!merges' && cell != '!ref') {
			  		console.log(data2[cell]);
					data2[cell].s = {
					  	font: {
				              name: '宋体',
				              sz: 14,
				              color: {rgb: "#00000000"},
				              bold: false,
				              italic: false,
				              underline: false
				        },
			            alignment: {
			              horizontal: "center" ,
			              vertical: "center"
			            },
			             fill: { bgColor: { indexed: 64 }}
			  		};   
				}
			}

            
            saveAs(
            	new Blob([s2ab(XLSX.write(wb, wopts))], { type: "application/octet-stream"}), 
            	"徐汇区地铁站测点数据" + '.' + (wopts.bookType == "biff2" ? "xls" : wopts.bookType)
            	);
        }
        //格式转化
        function s2ab(s) {
            if (typeof ArrayBuffer !== 'undefined') {
                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;
            } else {
                var buf = new Array(s.length);
                for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;
                return buf;
            }
        }
    </script>
    <button onclick="downloadExl(testArray,testArray2)">导出</button>
</body>
</html>