导出多个表的excel文件

2021/4/27 18:58:20

本文主要是介绍导出多个表的excel文件,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

<template>

  <div class="horizontalDiv">
    <div style="display:none" id="horizontalDiv1"></div>
    <div style="display:none" id="horizontalDiv2"></div>
    <div style="display:none" id="horizontalDiv3"></div>
    <div style="display:none" id="horizontalDiv4"></div>
    <div style="display:none" id="horizontalDiv5"></div>
  </div>
</template>

<script>
import XLSX from 'xlsx';

export default {
  name: 'horizontal',
  methods: {
    horizontalTables(hdata, standardName) {
      let sheetNames = ['层楼板投影面积统计表', '层楼梯、挑板、水平其他构件投影面积统计表', '层梁投影面积统计表', '层预制楼板、楼梯、阳台板投影面积统计表', '层预制梁、沉箱、空调板、飘窗板、挑板投影面积统计表'];
      sheetNames = sheetNames.map(item => standardName + item);

      let sheet1Str;
      let sheet1Start;
      let sheet1Center;
      let sheet1end;
      sheet1Start = '<table style="text-align:center" align="center" id="sheet1" cellspacing="0" cellpadding="0" border="1"> <tbody>';

      let sheet11;
      let sheet12;
      let sheet13;
      sheet11 = `
            <tr>
              <td align="center" colspan="4">${sheetNames[0]}</td>
            </tr>
            <tr>
              <td align="center" colspan="2">编号</td>
              <td align="center" colspan="2">投影面积(m2)</td> 
            </tr>`;
      for (let j = 0; j < hdata.unprefab.floor.length; j += 1) {
        sheet12 += `<tr>
              <td colspan="2">${hdata.unprefab.floor[j].compNo || '-'}</td>
              <td colspan="2">${hdata.unprefab.floor[j].parArea || '-'}</td>
            </tr> `;
      }
      // sheet13 = '<tr><td colspan="4"></td></tr>';
      sheet1Center += sheet11 + sheet12 + sheet13;

      sheet1end = '</tbody></table>';
      sheet1Str = sheet1Start + sheet1Center + sheet1end;

      let sheet2Str;
      let sheet2Start;
      let sheet2Center;
      let sheet2end;
      sheet2Start = '<table align="center" id="sheet2" cellspacing="0" cellpadding="0" border="1"> <tbody>';

      let sheet21;
      let sheet22;
      let sheet23;
      sheet21 = `
            <tr>
              <td align="center" colspan="4">${sheetNames[1]}</td>
            </tr>
            <tr>
             <td align="center" colspan="2">编号</td>
              <td align="center" colspan="2">投影面积(m2)</td> 
            </tr>`;
      for (let j = 0; j < hdata.unprefab.other.length; j += 1) {
        sheet22 += `<tr>
              <td colspan="2">${hdata.unprefab.other[j].compNo || '-'}</td>
              <td colspan="2">${hdata.unprefab.other[j].parArea || '-'}</td>
            </tr> `;
      }
      // sheet23 = '<tr><td colspan="4"></td></tr>';
      sheet2Center += sheet21 + sheet22 + sheet23;

      sheet2end = '</tbody></table>';
      sheet2Str = sheet2Start + sheet2Center + sheet2end;

      let sheet3Str;
      let sheet3Start;
      let sheet3Center;
      let sheet3end;
      sheet3Start = '<table align="center" id="sheet3" cellspacing="0" cellpadding="0" border="1"> <tbody>';

      let sheet31;
      let sheet32;
      let sheet33;
      sheet31 = `
            <tr>
              <td align="center" colspan="4">${sheetNames[2]}</td>
            </tr>
            <tr>
              <td colspan="2">编号</td>
              <td colspan="2">投影面积(m2)</td> 
            </tr>`;
      for (let j = 0; j < hdata.unprefab.beam.length; j += 1) {
        sheet32 += `<tr>
               <td colspan="2">${hdata.unprefab.beam[j].compNo || '-'}</td>
              <td colspan="2">${hdata.unprefab.beam[j].parArea || '-'}</td>
            </tr> `;
      }
      // sheet23 = '<tr><td colspan="4"></td></tr>';
      sheet3Center += sheet31 + sheet32 + sheet33;
      sheet3Str = sheet3Start + sheet3Center + sheet3end;

      let sheet4Str;
      let sheet4Start;
      let sheet4Center;
      let sheet4end;
      sheet4Start = '<table align="center" id="sheet4" cellspacing="0" cellpadding="0" border="1"> <tbody>';
      let sheet41;
      let sheet42;
      let sheet43;
      sheet41 = `
            <tr>
             <td align="center" colspan="4">${sheetNames[3]}</td>
            </tr>
            <tr>
              <td colspan="2">编号</td>
              <td colspan="2">投影面积(m2)</td> 
            </tr>`;
      for (let j = 0; j < hdata.prefab.floor.length; j += 1) {
        sheet42 += `<tr>
                <td colspan="2">${hdata.prefab.floor[j].compNo || '-'}</td>
              <td colspan="2">${hdata.prefab.floor[j].parArea || '-'}</td>
            </tr> `;
      }
      // sheet23 = '<tr><td colspan="4"></td></tr>';
      sheet4Center += sheet41 + sheet42 + sheet43;

      sheet4end = '</tbody></table>';
      sheet4Str = sheet4Start + sheet4Center + sheet4end;


      let sheet5Str;
      let sheet5Start;
      let sheet5Center;
      let sheet5end;
      sheet5Start = '<table align="center" id="sheet5" cellspacing="0" cellpadding="0" border="1"> <tbody>';
      let sheet51;
      let sheet52;
      let sheet53;
      sheet51 = `
            <tr>
             <td align="center" colspan="4">${sheetNames[4]}</td>
            </tr>
            <tr>
              <td colspan="2">编号</td>
              <td colspan="2">投影面积(m2)</td> 
            </tr>`;
      for (let j = 0; j < hdata.prefab.beam.length; j += 1) {
        sheet52 += `<tr>
                <td colspan="2">${hdata.prefab.beam[j].compNo || '-'}</td>
              <td colspan="2">${hdata.prefab.beam[j].parArea || '-'}</td>
            </tr> `;
      }
      // sheet23 = '<tr><td colspan="4"></td></tr>';
      sheet5Center += sheet51 + sheet52 + sheet53;

      sheet5end = '</tbody></table>';
      sheet5Str = sheet5Start + sheet5Center + sheet5end;


      document.getElementById('horizontalDiv1').innerHTML = sheet1Str;
      document.getElementById('horizontalDiv2').innerHTML = sheet2Str;
      document.getElementById('horizontalDiv3').innerHTML = sheet3Str;
      document.getElementById('horizontalDiv4').innerHTML = sheet4Str;
      document.getElementById('horizontalDiv5').innerHTML = sheet5Str;
      const sheet1 = XLSX.utils.table_to_sheet(document.getElementById('sheet1'));
      const sheet2 = XLSX.utils.table_to_sheet(document.getElementById('sheet2'));
      const sheet3 = XLSX.utils.table_to_sheet(document.getElementById('sheet3'));
      const sheet4 = XLSX.utils.table_to_sheet(document.getElementById('sheet4'));
      const sheet5 = XLSX.utils.table_to_sheet(document.getElementById('sheet5'));
      // console.log(XLSX);
      const wb = XLSX.utils.book_new();

      XLSX.utils.book_append_sheet(wb, sheet1, sheetNames[0]);
      XLSX.utils.book_append_sheet(wb, sheet2, sheetNames[1]);
      XLSX.utils.book_append_sheet(wb, sheet3, sheetNames[2]);
      XLSX.utils.book_append_sheet(wb, sheet4, sheetNames[3]);
      XLSX.utils.book_append_sheet(wb, sheet5, sheetNames[4]);

      const workbookBlob = this.workbook2blob(wb);
      this.openDownloadDialog(workbookBlob, `${standardName}层预制水平构件应用比例计算统计表.xlsx`);
    },
    openDownloadDialog (blob, fileName) {
      if (typeof blob === 'object' && blob instanceof Blob) {
        // eslint-disable-next-line no-param-reassign
        blob = URL.createObjectURL(blob); // 创建blob地址
      }
      let aLink = document.createElement('a');
      aLink.href = blob;
      // HTML5新增的属性,指定保存文件名,可以不要后缀,注意,有时候 file:///模式下不会生效
      aLink.download = fileName || '';
      let 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);
    },
    workbook2blob (workbook) {
    // 生成excel的配置项
      let wopts = {
        // 要生成的文件类型
        bookType: 'xlsx',
        // 是否生成Shared String Table,官方解释是,如果开启生成速度会下降,但在低版本IOS设备上有更好的兼容性
        bookSST: false,
        type: 'binary',
      };
      let wbout = XLSX.write(workbook, wopts);
      // 将字符串转ArrayBuffer
      function s2ab (s) {
        let buf = new ArrayBuffer(s.length);
        let view = new Uint8Array(buf);
        // eslint-disable-next-line no-bitwise
        for (let i = 0; i !== s.length; i += 1) view[i] = s.charCodeAt(i) & 0xff;
        return buf;
      }
      let buf = s2ab(wbout);
      let blob = new Blob([buf], {
        type: 'application/octet-stream',
      });
      return blob;
    },
  },
  created() {

  },
};
</script>
<style scoped lang="scss">
  // @import '../../../mixinCss/mixin';
  table td{
    text-align: center !important;
  }

</style>

  

<template>
  <div class="horizontalDiv">     <div style="display:none" id="horizontalDiv1"></div>     <div style="display:none" id="horizontalDiv2"></div>     <div style="display:none" id="horizontalDiv3"></div>     <div style="display:none" id="horizontalDiv4"></div>     <div style="display:none" id="horizontalDiv5"></div>   </div> </template>
<script> import XLSX from 'xlsx';
export default {   name: 'horizontal',   methods: {     horizontalTables(hdata, standardName) {       let sheetNames = ['层楼板投影面积统计表', '层楼梯、挑板、水平其他构件投影面积统计表', '层梁投影面积统计表', '层预制楼板、楼梯、阳台板投影面积统计表', '层预制梁、沉箱、空调板、飘窗板、挑板投影面积统计表'];       sheetNames = sheetNames.map(item => standardName + item);
      let sheet1Str;       let sheet1Start;       let sheet1Center;       let sheet1end;       sheet1Start = '<table style="text-align:center" align="center" id="sheet1" cellspacing="0" cellpadding="0" border="1"> <tbody>';
      let sheet11;       let sheet12;       let sheet13;       sheet11 = `             <tr>               <td align="center" colspan="4">${sheetNames[0]}</td>             </tr>             <tr>               <td align="center" colspan="2">编号</td>               <td align="center" colspan="2">投影面积(m2)</td>              </tr>`;       for (let j = 0; j < hdata.unprefab.floor.length; j += 1) {         sheet12 += `<tr>               <td colspan="2">${hdata.unprefab.floor[j].compNo || '-'}</td>               <td colspan="2">${hdata.unprefab.floor[j].parArea || '-'}</td>             </tr> `;       }       // sheet13 = '<tr><td colspan="4"></td></tr>';       sheet1Center += sheet11 + sheet12 + sheet13;
      sheet1end = '</tbody></table>';       sheet1Str = sheet1Start + sheet1Center + sheet1end;
      let sheet2Str;       let sheet2Start;       let sheet2Center;       let sheet2end;       sheet2Start = '<table align="center" id="sheet2" cellspacing="0" cellpadding="0" border="1"> <tbody>';
      let sheet21;       let sheet22;       let sheet23;       sheet21 = `             <tr>               <td align="center" colspan="4">${sheetNames[1]}</td>             </tr>             <tr>              <td align="center" colspan="2">编号</td>               <td align="center" colspan="2">投影面积(m2)</td>              </tr>`;       for (let j = 0; j < hdata.unprefab.other.length; j += 1) {         sheet22 += `<tr>               <td colspan="2">${hdata.unprefab.other[j].compNo || '-'}</td>               <td colspan="2">${hdata.unprefab.other[j].parArea || '-'}</td>             </tr> `;       }       // sheet23 = '<tr><td colspan="4"></td></tr>';       sheet2Center += sheet21 + sheet22 + sheet23;
      sheet2end = '</tbody></table>';       sheet2Str = sheet2Start + sheet2Center + sheet2end;
      let sheet3Str;       let sheet3Start;       let sheet3Center;       let sheet3end;       sheet3Start = '<table align="center" id="sheet3" cellspacing="0" cellpadding="0" border="1"> <tbody>';
      let sheet31;       let sheet32;       let sheet33;       sheet31 = `             <tr>               <td align="center" colspan="4">${sheetNames[2]}</td>             </tr>             <tr>               <td colspan="2">编号</td>               <td colspan="2">投影面积(m2)</td>              </tr>`;       for (let j = 0; j < hdata.unprefab.beam.length; j += 1) {         sheet32 += `<tr>                <td colspan="2">${hdata.unprefab.beam[j].compNo || '-'}</td>               <td colspan="2">${hdata.unprefab.beam[j].parArea || '-'}</td>             </tr> `;       }       // sheet23 = '<tr><td colspan="4"></td></tr>';       sheet3Center += sheet31 + sheet32 + sheet33;       sheet3Str = sheet3Start + sheet3Center + sheet3end;
      let sheet4Str;       let sheet4Start;       let sheet4Center;       let sheet4end;       sheet4Start = '<table align="center" id="sheet4" cellspacing="0" cellpadding="0" border="1"> <tbody>';       let sheet41;       let sheet42;       let sheet43;       sheet41 = `             <tr>              <td align="center" colspan="4">${sheetNames[3]}</td>             </tr>             <tr>               <td colspan="2">编号</td>               <td colspan="2">投影面积(m2)</td>              </tr>`;       for (let j = 0; j < hdata.prefab.floor.length; j += 1) {         sheet42 += `<tr>                 <td colspan="2">${hdata.prefab.floor[j].compNo || '-'}</td>               <td colspan="2">${hdata.prefab.floor[j].parArea || '-'}</td>             </tr> `;       }       // sheet23 = '<tr><td colspan="4"></td></tr>';       sheet4Center += sheet41 + sheet42 + sheet43;
      sheet4end = '</tbody></table>';       sheet4Str = sheet4Start + sheet4Center + sheet4end;

      let sheet5Str;       let sheet5Start;       let sheet5Center;       let sheet5end;       sheet5Start = '<table align="center" id="sheet5" cellspacing="0" cellpadding="0" border="1"> <tbody>';       let sheet51;       let sheet52;       let sheet53;       sheet51 = `             <tr>              <td align="center" colspan="4">${sheetNames[4]}</td>             </tr>             <tr>               <td colspan="2">编号</td>               <td colspan="2">投影面积(m2)</td>              </tr>`;       for (let j = 0; j < hdata.prefab.beam.length; j += 1) {         sheet52 += `<tr>                 <td colspan="2">${hdata.prefab.beam[j].compNo || '-'}</td>               <td colspan="2">${hdata.prefab.beam[j].parArea || '-'}</td>             </tr> `;       }       // sheet23 = '<tr><td colspan="4"></td></tr>';       sheet5Center += sheet51 + sheet52 + sheet53;
      sheet5end = '</tbody></table>';       sheet5Str = sheet5Start + sheet5Center + sheet5end;

      document.getElementById('horizontalDiv1').innerHTML = sheet1Str;       document.getElementById('horizontalDiv2').innerHTML = sheet2Str;       document.getElementById('horizontalDiv3').innerHTML = sheet3Str;       document.getElementById('horizontalDiv4').innerHTML = sheet4Str;       document.getElementById('horizontalDiv5').innerHTML = sheet5Str;       const sheet1 = XLSX.utils.table_to_sheet(document.getElementById('sheet1'));       const sheet2 = XLSX.utils.table_to_sheet(document.getElementById('sheet2'));       const sheet3 = XLSX.utils.table_to_sheet(document.getElementById('sheet3'));       const sheet4 = XLSX.utils.table_to_sheet(document.getElementById('sheet4'));       const sheet5 = XLSX.utils.table_to_sheet(document.getElementById('sheet5'));       // console.log(XLSX);       const wb = XLSX.utils.book_new();
      XLSX.utils.book_append_sheet(wb, sheet1, sheetNames[0]);       XLSX.utils.book_append_sheet(wb, sheet2, sheetNames[1]);       XLSX.utils.book_append_sheet(wb, sheet3, sheetNames[2]);       XLSX.utils.book_append_sheet(wb, sheet4, sheetNames[3]);       XLSX.utils.book_append_sheet(wb, sheet5, sheetNames[4]);
      const workbookBlob = this.workbook2blob(wb);       this.openDownloadDialog(workbookBlob, `${standardName}层预制水平构件应用比例计算统计表.xlsx`);     },     openDownloadDialog (blob, fileName) {       if (typeof blob === 'object' && blob instanceof Blob) {         // eslint-disable-next-line no-param-reassign         blob = URL.createObjectURL(blob); // 创建blob地址       }       let aLink = document.createElement('a');       aLink.href = blob;       // HTML5新增的属性,指定保存文件名,可以不要后缀,注意,有时候 file:///模式下不会生效       aLink.download = fileName || '';       let 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);     },     workbook2blob (workbook) {     // 生成excel的配置项       let wopts = {         // 要生成的文件类型         bookType: 'xlsx',         // 是否生成Shared String Table,官方解释是,如果开启生成速度会下降,但在低版本IOS设备上有更好的兼容性         bookSST: false,         type: 'binary',       };       let wbout = XLSX.write(workbook, wopts);       // 将字符串转ArrayBuffer       function s2ab (s) {         let buf = new ArrayBuffer(s.length);         let view = new Uint8Array(buf);         // eslint-disable-next-line no-bitwise         for (let i = 0; i !== s.length; i += 1) view[i] = s.charCodeAt(i) & 0xff;         return buf;       }       let buf = s2ab(wbout);       let blob = new Blob([buf], {         type: 'application/octet-stream',       });       return blob;     },   },   created() {
  }, }; </script> <style scoped lang="scss">   // @import '../../../mixinCss/mixin';   table td{     text-align: center !important;   }
</style>

这篇关于导出多个表的excel文件的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程