当前位置：首页 > news >正文

html表格table导出excel,主从表格式,带样式.自动分列

news 来源：原创 2025/5/10 10:29:16

html的table导出成excel, vue模板
项目使用xlsx-js-style

源代码从https://github.com/gitbrent/xlsx-js-style/releases/tag/v1.2.0 下载
用里面的dist目录下的文件即可. 复制到vue项目的public目录下的XLSX目录下.

在index.hml中引入js脚本, 为啥要在这里引入? 是因为这里引入可以极大的减少打包时间. 并且提高页面浏览的速度. 一但把XLSX打包到一起, 系统将反应迟钝,所有我建议将这个模块的js独立使用.
把文件放入vue项目的public目录下, 打包发布后再复制到项目根目录下.

在index.hml

<!-- add the shim first -->
<script type="text/javascript" src="/XLSX/shim.min.js"></script>
<!-- after the shim is referenced, add the library -->
<script type="text/javascript" src="/XLSX/xlsx.full.min.js"></script>
<script type="text/javascript" src="/XLSX/xlsx.bundle.js"></script>

vue页面脚本

<template>  <a-modal v-model="ShowExportWin" title=" Export Or Print Data"  :dialog-style="{ top: '10px' }" :width="'80%'"> <a-button @click="ExportExcel()">导出到Excel</a-button><div id="excelDataContainer" v-html="TableHTML" style="overflow: auto;width: 100%; border: 1px solid black;"></div></a-modal>
</template>
<script  >
// import html2excel from 'html2excel';
// import XLSX from 'xlsx';
// import * as XLSX from 'xlsx';
// import { utils, writeFileXLSX } from 'xlsx-js-style'
// import XLSX from '@/components/xlsx.full.min.js';
//建议不要在这里import , 一但把XLSX打包到一起,  系统将反应迟钝. 太大了.
export default {name: "PrintOrExport",data() {return {      ShowExportWin :false,MasterTableName: "",SlaveTableName: "",TableHTML:"",MasterFields: [],SlaveFields: [],MasterData: {// "订单编号": "20250104001",// "客户名称": "李四",// "下单时间": "2025-01-04 14:30:00"},SlaveDatas: [// { "商品名称": "笔记本电脑", "数量": 1, "单价": 5000 },// { "商品名称": "鼠标", "数量": 2, "单价": 50 }], }},mounted() {},methods: {ExportExcel() {const table = document.getElementById('excelDataContainer').firstChild;const wb = XLSX.utils.book_new();const ws = XLSX.utils.table_to_sheet(table);var maxColIndex =  0;// 遍历表格的所有行和列for (let rowIndex = 0; rowIndex < table.rows.length; rowIndex++) {const row = table.rows[rowIndex];for (let colIndex = 0; colIndex < row.cells.length; colIndex++) {const cell = row.cells[colIndex];var cellObj = {v: cell.textContent,t:'s',s: {alignment: { wrapText: true, horizontal: 'center', vertical: 'center' },border: { top: { style: 'thin' }, bottom: { style: 'thin' }, left: { style: 'thin' }, right: { style: 'thin' } },}, }; if (cell.tagName === 'TH') { // 处理表头cellObj.s.fill= { fgColor: { rgb: '0000FF' } };cellObj.s.font= { color: { rgb: 'FFFFFF' } };// cellObj.s.border= { top: { style: 'thin' }, bottom: { style: 'thin' }, left: { style: 'thin' }, right: { style: 'thin' } };cellObj.s.alignment.horizontal= 'right';// cellObj.s.alignment= { wrapText: true };}const cellRef = XLSX.utils.encode_cell({ c: colIndex, r: rowIndex });ws[cellRef] = cellObj;if (colIndex > maxColIndex) {maxColIndex = colIndex;}}}// 设置列的最大宽度不超过150const numCols = maxColIndex;const cols = Array.from({ length: numCols }, () => ({ wch: 150 / 7.5 })); // 大致计算，1字符宽度约7.5pxws['!cols'] = cols;XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');XLSX.writeFile(wb, this.MasterTableName + "_" + this.MasterData["__id"] + '_TableData.xlsx'); },exportData(MasterTableName,SlaveTableName,MasterFields, SlaveFields,  MasterData, SlaveDatas) {this.MasterTableName = MasterTableName;this.SlaveTableName = SlaveTableName;this.MasterFields = [...MasterFields];this.SlaveFields = [...SlaveFields];this.MasterData = {...MasterData};this.SlaveDatas = [...SlaveDatas];this.ShowExportWin = true;console.log("mounted");this.RenderTableData();//this.$forceUpdate();console.log("exportData");  },RenderTableData(){ this.TableHTML = this.generateExcelHTML();   },H2V(keys,details_count){var COL_COUNTR = 3;//定死3列与界面显示一致 Math.floor(details_count/2); //计算有几列, 一个字段占2列.var col = 1;var col_count = Math.floor(keys.length / COL_COUNTR);//one_column_lengthvar duoyu = keys.length % COL_COUNTR;var cut1 = col_count;var cut2 = col_count;var cut3 = col_count;//2.先把数组分割成3列if(duoyu==1){cut1 = col_count+1;}else if(duoyu==2){cut1  = col_count+1;cut2  = col_count+1;}var colarr1 = keys.splice(0, cut1) ;//this.splitArray(keys, cut1); var colarr2 = keys.splice(0, cut2) ;//this.splitArray(keys, cut2);  var colarr3 = keys.splice(0, cut3) ;//this.splitArray(keys, cut3);// debugger;// 再把每列的数组合并成一个数组 var newKeys =[];for(var i=0;i<colarr1.length;i++){newKeys.push(colarr1[i]);if(i < colarr2.length){newKeys.push(colarr2[i]);}if(i < colarr3.length){newKeys.push(colarr3[i]);}}return newKeys;},// 生成主表单的HTML表格行结构generateMainFormTableRow(data,keys) { //var keys = Object.keys(data);keys = this.H2V(keys);let rowHTML = '<tr>';for (var i = 0; i < keys.length; i++) {if (i > 0 && i % 3 === 0) {rowHTML += '</tr><tr>';}const key = keys[i];rowHTML += `<th class="mHead">${key}</th><td class="mRow">${data[key]}</td>`;}if(keys.length % 3 == 1){rowHTML += '<th class="mHead"> </th><td class="mRow"> </td>';rowHTML += '<th class="mHead"> </th><td class="mRow"> </td>';}else if(keys.length % 3 == 2){rowHTML += '<th class="mHead"> </th><td class="mRow"> </td>';}rowHTML += '</tr>';// debugger;return rowHTML;},// 生成明细数据的HTML表格行结构generateDetailTableRow(data,keys) {let rowHTML = '<tr>';keys.forEach(key => {rowHTML += `<td class="dRow">${data[key]}</td>`;});rowHTML += '</tr>';return rowHTML;},isHiddenContorl(f) { if (f.FieldName.indexOf("_") >= 0 )   return true; //_开头的字段 不显示//0和-1 不显示 if (f.DisplayWidth <= 0) return true; //显示宽度为0 不显示if (f.EditControl == "隐藏控件") return true;if (f.EditControl == "None") return true;if (f.EditControl == "无") return true;if (f.EditControl == "") return true;if (f.EditControl == null) return true;if (f.EditControl == undefined) return false;if (f.FieldName == null || f.FieldName == undefined || f.FieldName == "") return false; return false; },getNotHideFields(fields) {var tempArray = [];//先取需要显示的字段 for (var i = 0; i < fields.length; i++){var f = fields[i];if(this.isHiddenContorl(f)){continue;} tempArray.push(f.FieldName); }return tempArray;},// 生成明细数据的HTML表格头结构generateDetailTableHeader(keys) {let headHTML = '<tr>';keys.forEach(key => {headHTML += `<th  class="dHead">${key}</th>`;});headHTML += '</tr>';return headHTML;},// 生成完整的HTML表格结构，包含主表单和明细数据generateExcelHTML() {var tableHTML = '<table border="1">';var masterFields = this.getNotHideFields(this.MasterFields);var slaveFields = this.getNotHideFields(this.SlaveFields);// 主表单表头//tableHTML += '<tr><th colspan="2">' + this.MasterTableName + '</th></tr>';tableHTML += this.generateMainFormTableRow(this.MasterData,masterFields);// 明细表头// tableHTML += '<tr><th colspan="3">明细</th></tr>';// tableHTML += '<tr><th>商品名称</th><th>数量</th><th>单价</th></tr>';tableHTML += this.generateDetailTableHeader(slaveFields);// 明细数据行this.SlaveDatas.forEach(item => {tableHTML += this.generateDetailTableRow(item,slaveFields);});tableHTML += '</table>';return tableHTML;},printData() {   console.log("printData");}}
}   </script>
<style>
.mHead,.mRow,.dRow,.dHead{border: 1px solid black;padding: 5px;max-width: 200px;
}
.mRow{border: 1px solid black;
}
.dRow{border: 1px solid black;
}
.mHead,.dHead{background-color: #3278fc;color: white;
}
</style>

应用demo

import PrintOrExport from "./PrintOrExport.vue";  <PrintOrExport ref="PrintOrExportVue"></PrintOrExport>this.$refs["PrintOrExportVue"].exportData(this.MasterTableName,this.SlaveTableName,this.MasterFields, this.SlaveFields,  this.MasterData, this.SlaveDatas );

在index.hml

相关文章：