从后端获取Excel模板

app.get('/api/download-template', (req, res) => {
    const templatePath = path.join(__dirname, './datafile/example.xlsx');
    res.download(templatePath);
  });

在前端页面，表单采用Elementui设计。利用axios接收Excel 模板文件 URL。填写表单，将信息存储在Excel模板文件中，并且下载下来。

<template>
  <div class="farther">
    <el-form ref="form" :model="form" label-width="80px">
      <el-form-item label="设备名称">
        <el-input v-model="form.eqname"></el-input>
      </el-form-item>
      <el-form-item label="规格型号">
        <el-input v-model="form.modelsize"></el-input>
      </el-form-item>
      <el-form-item label="设备编号">
        <el-input v-model="form.eqnumber"></el-input>
      </el-form-item>
      <el-form-item label="监测地点">
        <el-input v-model="form.addr"></el-input>
      </el-form-item>
      <el-form-item label="企业名称">
        <el-input v-model="form.company"></el-input>
      </el-form-item>

      <el-form-item label="运行单位">
        <el-input v-model="form.yxdw"></el-input>
      </el-form-item>

      <el-form-item label="维护内容">
        <el-input v-model="form.content"></el-input>
      </el-form-item>

      <el-form-item label="日期">
        <el-date-picker
          type="date"
          placeholder="选择日期"
          v-model="form.date"
          style="width: 100%"
        ></el-date-picker>
      </el-form-item>

      <el-form-item label="查询日志">
        <el-select v-model="form.journal" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="×"></el-option>
        </el-select>
      </el-form-item>

      <el-form-item label="检查耗材">
        <el-select v-model="form.haoc" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>

      <el-form-item label="站房卫生">
        <el-select v-model="form.weis" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="站房门窗的密封性检查">
        <el-select v-model="form.mifeng" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="供电系统">
        <el-select v-model="form.gdian" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="室内温湿度">
        <el-select v-model="form.shid" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="空调">
        <el-select v-model="form.ktiao" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="自来水供应情况">
        <el-select v-model="form.gyqk" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="采样泵采水情况">
        <el-select v-model="form.csqk" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="采样管路通畅">
        <el-select v-model="form.cytc" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="自动清洗装置运行情况">
        <el-select v-model="form.yunx" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="排水管路通畅">
        <el-select v-model="form.pais" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="清洗采样泵、过滤装置">
        <el-select v-model="form.guol" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="清洗采样管路、排水管路">
        <el-select v-model="form.caiy" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="仪器报警状态">
        <el-select v-model="form.baoj" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="仪器状态参数检查">
        <el-select v-model="form.ztai" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="仪器外观检查">
        <el-select v-model="form.wguan" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="仪器内部管路通畅">
        <el-select v-model="form.tchang" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="仪器进样、排液管路清洁检查">
        <el-select v-model="form.jiancha" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="检查电极标准液、内充液">
        <el-select v-model="form.yeti" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="清洗电极头">
        <el-select v-model="form.qingxi" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="数据采集系统报警信息">
        <el-select v-model="form.xinxi" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="数据上传情况">
        <el-select v-model="form.changchuan" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="数据采集情况">
        <el-select v-model="form.qingkuang" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="检查数采仪和仪器的连接">
        <el-select v-model="form.lianjie" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="检查上传数据和现场数据的一致性">
        <el-select v-model="form.yizhi" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item label="数据采集、传输设备电源">
        <el-select v-model="form.dainyaun" placeholder="请选择情况">
          <el-option label="正常" value="√"></el-option>
          <el-option label="不正常" value="X"></el-option>
        </el-select>
      </el-form-item>
      <el-form-item>
        <el-button type="primary" @click="downloadExcel">生成并下载</el-button>
      </el-form-item>
    </el-form>
  </div>
</template>

<script>

import * as XLSX from 'xlsx';
import http from '@/utils/request';
export default {
  data() {
    return {
      form: {
        eqname: '',
        modelsize: '',
        eqnumber: '',
        addr: '',
        company: '',
        yxdw: '',
        content: '',
        date: '',
        journal: '',
        haoc: '',
        weis: '',
        mifeng: '',
        gdian: '',
        shid: '',
        ktiao: '',
        gyqk: '',
        csqk: '',
        cytc: '',
        yunx: '',
        pais: '',
        guol: '',
        caiy: '',
        baoj: '',
        ztai: '',
        wguan: '',
        tchang: '',
        jiancha: '',
        yeti: '',
        qingxi: '',
        xinxi: '',
        changchuan: '',
        qingkuang: '',
        lianjie: '',
        yizhi: '',
        dainyaun: '',
      },
      excelTemplateUrl: '', // 后端返回的 Excel 模板文件 URL
    };
  },
  methods: {
    async downloadExcel() {
      console.log('获取模板');
      try {
        // const response = await axios.get('/api/download-template', { responseType: 'arraybuffer' });
        await http.get('/download-template', { responseType: 'arraybuffer' })
          .then(({ data }) => {
            const excelData = new Uint8Array(data);
            const workbook = XLSX.read(excelData, { type: 'array' });
            const worksheet = workbook.Sheets['Sheet1'];
            // 在 Excel 模板中填充表单数据
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.eqname]], { origin: 'C2' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.modelsize]], { origin: 'H2' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.eqnumber]], { origin: 'C3' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.addr]], { origin: 'H3' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.company]], { origin: 'C4' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.yxdw]], { origin: 'H4' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.content]], { origin: 'C5' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.date]], { origin: 'E6' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.journal]], { origin: 'D8' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.haoc]], { origin: 'D9' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.weis]], { origin: 'D10' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.mifeng]], { origin: 'D11' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.gdian]], { origin: 'D12' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.shid]], { origin: 'D13' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.ktiao]], { origin: 'D14' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.gyqk]], { origin: 'D15' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.csqk]], { origin: 'D16' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.cytc]], { origin: 'D17' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.yunx]], { origin: 'D18' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.pais]], { origin: 'D19' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.guol]], { origin: 'D20' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.caiy]], { origin: 'D21' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.baoj]], { origin: 'D22' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.ztai]], { origin: 'D23' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.wguan]], { origin: 'D24' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.tchang]], { origin: 'D25' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.jiancha]], { origin: 'D26' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.yeti]], { origin: 'D27' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.qingxi]], { origin: 'D28' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.xinxi]], { origin: 'D29' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.changchuan]], { origin: 'D30' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.qingkuang]], { origin: 'D31' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.lianjie]], { origin: 'D32' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.yizhi]], { origin: 'D33' });
            XLSX.utils.sheet_add_aoa(worksheet, [[this.form.dainyaun]], { origin: 'D34' });
            // 其他字段的填充，可以参考上面的示例进行添加

            const excelOutputData = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
            const blob = new Blob([excelOutputData], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
            const url = URL.createObjectURL(blob);

            const link = document.createElement('a');
            link.href = url;
            link.setAttribute('download', 'result.xlsx');
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
          })

      } catch (error) {
        console.error(error);
      }
    },
  },
};
</script>
<style scoped lang="less">
* {
  margin: 0;
  padding: 0;
}
.farther {
  height: 100%;
  width: 100%;
  overflow-y: scroll; /* 启用垂直滚动条 */
}
</style>