sheetjs匯出xlsxl:更改欄位名稱+設定公式

更替欄位名稱、指定顯示欄位數、加入公式

by cactus77kiki

HTML

<script src="https://cdnjs.cloudflare.com/ajax/libs/vue/2.6.12/vue.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.0/xlsx.full.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js"></script>

<div id="app">
<h3>
sheetjs: change xlsx column header and add formula column
</h3>
  <button type="button" v-on:click="exportfile">
  DownloadFile
  </button>
</div>

JavaScript

/*
ref(加入excel公式): https://jsfiddle.net/sheetjs/nb2y211o/

[其他(jsfiddle)內儲存sheetjs相關程式]:
(組件-匯入xlsx) https://jsfiddle.net/cactus77kiki/om1fhzw3/
(組件-匯出xlsx) https://jsfiddle.net/cactus77kiki/rz31edgq/37/
[其他(jsfiddle)相關自己嘗試sheetjs相關程式]:
(try更替欄位名稱) https://jsfiddle.net/cactus77kiki/2jbpm678/9/
[原始try sheetjs(jsfiddle)程式]
https://jsfiddle.net/cactus77kiki/q9uyp6L8/14/
*/
function downloadxlsx(filename, sheetname, jsondata, header) {
			if (typeof XLSX == 'undefined') XLSX = require('xlsx');
			let wb = XLSX.utils.book_new();
			//let ws = XLSX.utils.json_to_sheet(jsondata);
			let ws;
			if (header) ws = XLSX.utils.json_to_sheet(jsondata, header);      
			else ws = XLSX.utils.json_to_sheet(jsondata);
      additionfun(ws, jsondata.length);	//custom content: 加入excel公式      
      //ws["F3"] = { t:'n', f:"B3-C3-D3-E3" };
			XLSX.utils.book_append_sheet(wb, ws, sheetname);
			let wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'binary' });
			saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), filename);
		}

		function s2ab(s) {
			let buf = new ArrayBuffer(s.length);
			let view = new Uint8Array(buf);
			for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
			return buf;
		}
    
    //公式欄位
    function additionfun(ws,datacnt){
      for(var idx = 0; idx<datacnt; idx++){
      	let rowindex = idx+2;
        let cellname = 'F';        
      	ws[`${cellname}${rowindex}`] = { t:'n', f:`B${rowindex}-C${rowindex}-D${rowindex}-E${rowindex}` };
      }
    	
    }
    
    var vapp = new Vue({
    	el: '#app',
      data:{
      	dataVal: [],
        titles: ['name', 'sheethr', 'holidayhr', 'leavehr', 'tmhr','calitem'],
				titleDisplay: {
						name: '姓名', sheethr: '應出勤工時', holidayhr: '國定假日', leavehr: '請假時數', tmhr: 'TM工時',calitem:'計算欄位'
					},          
      },
      methods: {
        initialize: function(){
        	this.dataVal=[
          	{idx:1, name: 'aa', sheethr:8, holidayhr: 0, leavehr: 1, tmhr: 7, calitem: ''},
         ...