导出多个表的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>