下载excel

步骤

• 生成数据
• 添加到dom中

  public createTable() {
  	// 获取本地存储数据
  	let div = document.createElement('div');
  	div.className = 'download_excel';
  	document.body.appendChild(div);
  	let dataAll = [this.space_data,[{items:this.material_data}],this.print_data];
  	dataAll.forEach((val, index) => {
  		let option = "";
  		option = `<table id="tbl${index}" class="table2excel">
                  <tr>
                      ${index !== 1 ? '<th>房间</th>' : ''}
                      <td>类别</td>
                      <td>名称</td>
                      <td>品牌</td>
                      <td>规格</td>
                      <td>数量</td>
                      <td>单价</td>
                      <td>损耗率</td>
                      <td>总计</td>
                      <td>备注</td>
                  </tr>`;
  		val.forEach((data) => {
  			let num = -1;
  			data.items.forEach((item) => {
  				if (item.attrition === 5) {
  					num++;
  					option += `<tr>
                                     	${index !== 1 ? `<td>${num === 0 ? data.roomName : ''}</td>` : ''}
                                      <td>${item.name}</td>
                                      <td>${item.product_brand}</td>
                                      <td>${item.product_width}x${item.product_length}</td>
                                      <td>${item.amount_int}</td>
                                      <td>￥ ${item.price} 元/${item.unit}</td>
                                      <td>${item.attrition}%</td>
                                      <td>￥${item.total_prices.toFixed(0)}</td>
                                      <td>${item.remark.replace(/<br\/>/,'')}</td>
                                  </tr>`;
  				}
  			});
  		});
  		div.innerHTML += option;
  	});
  }
  

• 点击下载。采用的是data协议方式：对于支持data协议的浏览器，可以将html或是csv先用js base64处理，然后前缀data:application/vnd.ms-excel;base64,，即可使浏览器将其中的数据当做excel来处理，浏览器将提示下载或打开excel文件
• btoa() 方法用于创建一个 base-64 编码的字符串。该方法使用 "A-Z", "a-z", "0-9", "+", "/" 和 "=" 字符来编码字符串。base-64 解码使用方法是 atob() 。

let uri = 'data:application/vnd.ms-excel;base64,',
	tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'
		+ '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>'
		+ '<Styles>'
		+ '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'
		+ '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>'
		+ '</Styles>'
		+ '{worksheets}</Workbook>'
	, tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>'
	, tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>'
	, base64 = function (s) {
		return window.btoa(unescape(encodeURIComponent(s)));
	}
	, format = function (s, c) {
		return s.replace(/{(\w+)}/g, function (m, p) {
			return c[p];
		});
	};
	let ctx: any;
	let workbookXML: any;
	let worksheetsXML = "";
	let rowsXML = "";
	let tablesName = ['tbl0','tbl1','tbl2'];
	let appname = 'Excel';
	let wsnames = ['空间类型','材料类型','图纸类型'];
// , '材料类型', '图纸类型'  , 'tbl1', 'tbl2'
	let tables: any[] = [];
	for (let i = 0; i < tablesName.length; i++) {
		if (!tables[i]) tables[i] = document.getElementById(tablesName[i]);
		for (let j = 0; j < tables[i].rows.length; j++) {
			rowsXML += '<Row>';
			for (let k = 0; k < tables[i].rows[j].cells.length; k++) {
				let dataType = tables[i].rows[j].cells[k].getAttribute("data-type");
				let dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");
				let dataValue = tables[i].rows[j].cells[k].getAttribute("data-value");
				dataValue = (dataValue) ? dataValue : tables[i].rows[j].cells[k].innerHTML;
				let dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");
				dataFormula = (dataFormula) ? dataFormula : (appname === 'Calc' && dataType === 'DateTime') ? dataValue : null;
				ctx = {
					attributeStyleID: (dataStyle === 'Currency' || dataStyle === 'Date') ? ' ss:StyleID="' + dataStyle + '"' : ''
					,
					nameType: (dataType === 'Number' || dataType === 'DateTime' || dataType === 'Boolean' || dataType === 'Error') ? dataType : 'String'
					,
					data: (dataFormula) ? '' : dataValue
					,
					attributeFormula: (dataFormula) ? ' ss:Formula="' + dataFormula + '"' : ''
				};
				rowsXML += format(tmplCellXML, ctx);
			}
			rowsXML += '</Row>';
		}
		ctx = {rows: rowsXML, nameWS: wsnames[i] || 'Sheet' + i};
		worksheetsXML += format(tmplWorksheetXML, ctx);
		rowsXML = "";
	}

	ctx = {created: (new Date()).getTime(), worksheets: worksheetsXML};
	workbookXML = format(tmplWorkbookXML, ctx);


	let link = document.createElement("A") as any;
	link.href = uri + base64(workbookXML);
	link.download = '算量清单.xls' || 'Workbook.xls';
	link.target = '_blank';
	document.body.appendChild(link);
	link.click();
	document.body.removeChild(link);
	let div = document.querySelector('.download_excel');
	document.body.removeChild(div);