TableStudy.vue

<!--
 * 表格学习
  -->
<script setup lang="ts">
import { reactive, ref } from 'vue';
import { useI18n } from 'vue-i18n';
import { useMessage } from 'src/common/hooks';
import { ComTitlePage, ComUpLoader } from 'src/components';
import ICON from 'src/config/icons';

import { read, utils, writeFile } from 'xlsx';
// var XLSX = require('xlsx');

import lang from '../../lang';

const { t } = useI18n({ messages: lang });
const { warn } = useMessage();

const excelTypeList = ['xlsx', 'xls'];

const columns: any[] = [
  {
    name: 'name',
    field: 'name',
    label: '姓名',
    align: 'center',
  },
  {
    name: 'age',
    field: 'age',
    label: '年龄',
    align: 'center',
  },
  {
    name: 'sex',
    field: 'sex',
    label: '性别',
    align: 'center',
  },
  {
    name: 'salary',
    field: 'salary',
    label: '工资',
    align: 'center',
  },
  {
    name: 'hobby',
    field: 'hobby',
    label: '爱好',
    align: 'center',
  },
];

console.log('翻译', t('No file selected'));

const hasUploadXlsxMsg = ref(false);
const uploadXlsxMsg = ref(t('No file selected'));

const state = reactive({
  rows: [
    {
      name: '张三',
      sex: '男',
      age: 18,
      hobby: '摸鱼',
    },
    {
      sex: '男',
      age: 18,
      name: '孙火旺 名字很长很长名字很长很长',
    },
    {
      salary: '5000.00',
      name: '小美',
      age: 20,
      sex: '女',
    },
    {
      name: '小李',
      age: 22,
      salary: '6000.00',
      sex: '男',
    },
  ],
  viewTableHtml: null as any,
});

function onXport() {
  const table = document.getElementById('TableToExport');
  const wb = utils.table_to_book(table);
  writeFile(wb, '库存表.xlsx');
}

function uploadXlsx() {
  let _input: HTMLInputElement = document.createElement('input'); // 创建一个隐藏的input
  _input.setAttribute('type', 'file');
  _input.setAttribute(
    'accept',
    'application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
  );
  // _input.setAttribute('accept', '');

  _input.onchange = async function (e: any) {
    const file = e.target.files[0];
    const fileTypeArr = file.name.split('.');
    const fileType = fileTypeArr[fileTypeArr.length - 1];
    // console.log('上传文件 >>>>', fileType, file);

    if (excelTypeList.includes(fileType)) {
      let unit: string; // 单位
      let size: number | string = 0;
      if (file.size < 1000) {
        unit = 'B';
        size = file.size.toFixed(3);
      } else {
        const KB_size = file.size * 0.0009765625;
        if (KB_size < 1000) {
          unit = 'KB';
          size = KB_size.toFixed(3);
        } else {
          const MB_size = KB_size * 0.0009765625;
          if (MB_size < 1000) {
            unit = 'MB';
            size = MB_size.toFixed(2);
          } else {
            const GB_size = MB_size * 0.0009765625;
            if (GB_size <= 2) {
              unit = 'GB';
              size = GB_size.toFixed(2);
            } else {
              const msg = t('Select files smaller than 2GB');
              warn(msg);
              console.warn(msg);
              return;
            }
          }
        }
      }

      uploadXlsxMsg.value = `${file.name}&nbsp;&nbsp;${size}${unit}`;
      hasUploadXlsxMsg.value = true;
      const data = await file.arrayBuffer();
      const wb = read(data);
      const ws = wb.Sheets[wb.SheetNames[0]];
      const asd = utils.sheet_to_html(ws, { id: 'tabeller' });
      state.viewTableHtml = asd;
    } else {
      const msg = t('File format error', { type: fileType });
      warn(msg);
      console.warn(msg);
    }
  };
  _input.click();
}

function uploadOk(val: any) {
  console.log('uploadOk >>>>', val);
}

function exportTableData() {
  // https://docs.sheetjs.com/docs/getting-started/example/

  // 生成一个工作表
  const worksheet = utils.json_to_sheet(state.rows);

  // 创建一个新工作簿
  const workbook = utils.book_new();
  // 把工作表添加到工作簿，这个新的工作表被命名为 "StudentInfo"
  utils.book_append_sheet(workbook, worksheet, 'StudentInfo');

  // 更改标题行
  // 默认标题行是row里面对象数据第一个出现的键名
  // { origin: "A1" } 从单元格A1开始写入文本
  utils.sheet_add_aoa(worksheet, [['姓名', '性别', '年龄', '爱好', '工资']], {
    origin: 'A1',
  });

  // 有些名称比默认列宽长，可以通过设置 "!cols" 来设置列宽
  worksheet['!cols'] = [{ wch: 40 }]; // set column A width to 40 characters
  // 计算最大宽度
  // const max_width = state.rows.reduce((w, r) => Math.max(w, r.name.length), 10);
  // worksheet['!cols'] = [{ wch: max_width }];

  // 插入新的数据行
  // const ws = workbook.Sheets['StudentInfo'];
  // XLSX.utils.sheet_add_aoa(
  //   worksheet,
  //   [
  //     ['郝美丽', '女', '40', '购物', '1000000'],
  //     ['甄漂亮', '女', '30', '看电视', '2000000'],
  //   ],
  //   {
  //     origin: -1,
  //   }
  // );

  // 创建一个 XLSX 文件并尝试保存为 学生信息.xlsx
  writeFile(workbook, '学生信息.xlsx', { compression: true });
}
</script>

<template>
  <div>
    <com-title-page title="table学习" />

    <div class="my-table q-mt-md q-gutter-sm">
      <div>
        <q-btn
          :label="t('Export table data')"
          square
          push
          :icon="ICON.export"
          style="background: #4aacc5; color: white"
          @click="exportTableData"
        />
      </div>

      <div>
        <q-table :rows="state.rows" :columns="columns" row-key="name" />
      </div>
    </div>
    <div class="upload-box">
      <div style="width: 400px; height: 232px" class="q-my-md">
        <com-up-loader bg-color="#FAD4D4" dark multiple @on-ok="uploadOk" />
      </div>
      <div style="width: 400px; height: 232px" class="q-my-md">
        <com-up-loader
          bg-color="#A4BE7B"
          multiple
          accept="image/*"
          @on-ok="uploadOk"
        />
      </div>
      <div>
        <div class="upload-xlsx-box">
          <div>
            <q-btn
              :label="t('Upload files')"
              square
              push
              :icon="ICON.upload"
              style="background: #4aacc5; color: white"
              @click="uploadXlsx"
            />
          </div>
          <div>
            <q-chip :ripple="false" square>
              <span v-if="!hasUploadXlsxMsg">{{ t('No file selected') }}</span>
              <span v-else v-html="uploadXlsxMsg"></span>
            </q-chip>
          </div>
        </div>
      </div>
      <div class="view-table-box">
        <div class="view-table-inner">
          <div v-html="state.viewTableHtml" class="table-view"></div>
        </div>
      </div>
    </div>
    <div class="table-box q-mt-md q-pb-md">
      <div class="q-mb-sm">
        <q-btn
          :label="t('Export inventory table')"
          square
          push
          :icon="ICON.export"
          style="background: #4aacc5; color: white"
          @click="onXport"
        />
      </div>
      <div>
        <table id="TableToExport">
          <caption>
            库存表
          </caption>
          <thead>
            <tr>
              <th rowspan="3">货号</th>
              <th rowspan="3">颜色</th>
              <th rowspan="3">码号</th>
              <th rowspan="3">上月库存数量</th>
              <th rowspan="3">单价</th>
              <th rowspan="3">上月库存金额</th>
              <th>4月采购</th>
              <th colspan="2">4月销售</th>
              <th rowspan="3">本月库存数量</th>
              <th rowspan="3">单价</th>
              <th rowspan="3">本月库存金额</th>
            </tr>
            <tr>
              <th>采购合计</th>
              <th>4月16日</th>
              <th>5月20日</th>
            </tr>
            <tr>
              <th>数量合计</th>
              <th>数量</th>
              <th>数量</th>
            </tr>
          </thead>
          <tfoot>
            <tr>
              <td colspan="12">商品增加，直接“复制” “粘贴”即可</td>
            </tr>
            <tr>
              <td colspan="12">
                每月月初复制粘贴“上月月末库存” 数据作为 “本月月初库存” 数据
              </td>
            </tr>
          </tfoot>
          <tbody>
            <tr>
              <td rowspan="6">&nbsp;</td>
              <td rowspan="3">&nbsp;</td>
              <td>&nbsp;</td>
              <td>2</td>
              <td>2.00</td>
              <td>4.00</td>
              <td>0</td>
              <td>&nbsp;</td>
              <td>0</td>
              <td>2</td>
              <td>2.00</td>
              <td>4.00</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>2</td>
              <td>2.00</td>
              <td>4.00</td>
              <td>0</td>
              <td>&nbsp;</td>
              <td>0</td>
              <td>2</td>
              <td>2.00</td>
              <td>4.00</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>2</td>
              <td>2.00</td>
              <td>4.00</td>
              <td>0</td>
              <td>&nbsp;</td>
              <td>0</td>
              <td>2</td>
              <td>2.00</td>
              <td>4.00</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>6</td>
              <td>&nbsp;</td>
              <td>12</td>
              <td>0</td>
              <td>0</td>
              <td>0</td>
              <td>6</td>
              <td>0.00</td>
              <td>12.00</td>
            </tr>
            <tr>
              <td rowspan="5">&nbsp;</td>
              <td rowspan="3">&nbsp;</td>
              <td>&nbsp;</td>
              <td>3</td>
              <td>3.00</td>
              <td>9.00</td>
              <td>0</td>
              <td>&nbsp;</td>
              <td>0</td>
              <td>3</td>
              <td>3.00</td>
              <td>9.00</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>3</td>
              <td>3.00</td>
              <td>9.00</td>
              <td>0</td>
              <td>&nbsp;</td>
              <td>0</td>
              <td>3</td>
              <td>3.00</td>
              <td>9.00</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>3</td>
              <td>3.00</td>
              <td>9.00</td>
              <td>0</td>
              <td>&nbsp;</td>
              <td>0</td>
              <td>3</td>
              <td>3.00</td>
              <td>9.00</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>9</td>
              <td>&nbsp;</td>
              <td>27</td>
              <td>0</td>
              <td>0</td>
              <td>0</td>
              <td>9</td>
              <td>0.00</td>
              <td>27.00</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>合计</td>
              <td>&nbsp;</td>
              <td>15</td>
              <td>&nbsp;</td>
              <td>39.00</td>
              <td>0</td>
              <td>0</td>
              <td>0</td>
              <td>15</td>
              <td>&nbsp;</td>
              <td>39.00</td>
            </tr>
            <tr>
              <td rowspan="6">&nbsp;</td>
              <td rowspan="3">&nbsp;</td>
              <td>&nbsp;</td>
              <td>4</td>
              <td>4.00</td>
              <td>16.00</td>
              <td>0</td>
              <td>&nbsp;</td>
              <td>0</td>
              <td>4</td>
              <td>4.00</td>
              <td>16.00</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>4</td>
              <td>4.00</td>
              <td>16.00</td>
              <td>0</td>
              <td>&nbsp;</td>
              <td>0</td>
              <td>4</td>
              <td>4.00</td>
              <td>16.00</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>4</td>
              <td>4.00</td>
              <td>16.00</td>
              <td>0</td>
              <td>&nbsp;</td>
              <td>0</td>
              <td>4</td>
              <td>4.00</td>
              <td>16.00</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>12</td>
              <td>&nbsp;</td>
              <td>48.00</td>
              <td>0</td>
              <td>0</td>
              <td>0</td>
              <td>12</td>
              <td>&nbsp;</td>
              <td>48.00</td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>合计</td>
              <td>&nbsp;</td>
              <td>12</td>
              <td>&nbsp;</td>
              <td>48.00</td>
              <td>0</td>
              <td>0</td>
              <td>0</td>
              <td>12</td>
              <td>&nbsp;</td>
              <td>48.00</td>
            </tr>
            <tr style="background-color: #dbeef4">
              <td>总计</td>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
              <td>27</td>
              <td>&nbsp;</td>
              <td>87.00</td>
              <td>0</td>
              <td>0</td>
              <td>0</td>
              <td>27</td>
              <td>&nbsp;</td>
              <td>87.00</td>
            </tr>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</template>

<style lang="scss" scoped>
.table-box {
  display: flex;
  flex-direction: column;
  justify-content: center;
}

#TableToExport {
  text-align: center; /*文本居中*/
  border-collapse: collapse; /*表格的边框合并，如果相邻，则共用一个边框*/
  border-spacing: 0; /*设置行与单元格边框的间距。当表格边框独立（即border-collapse:separate;）此属性才起作用*/
  border: 1px solid #0d3f67;
  color: #000;
  caption {
    font-size: 28px;
    letter-spacing: 15px;
    padding: 10px 0;
    color: white;
    background-color: #4aacc5;
    border-bottom: none;
  }
  thead {
    tr {
      background-color: #dbeef4;
    }
  }
  th {
    padding: 6px 4px;
    font-size: 16px;
    min-width: 120px;
    border: 2px solid #4aacc5;
  }
  td {
    font-size: 16px;
    padding: 6px 4px;
    border: 2px solid #4aacc5;
  }
  tfoot > tr > td {
    padding: 0;
    font-size: 18px;
  }
}

.view-table-box {
  margin-top: $padding-sm;
  box-sizing: border-box;
  background-color: #dbeef4;
  max-width: 1440px;
  padding: 16px;
  overflow: hidden;
  .view-table-inner {
    overflow: auto;
  }
}

// 上传文件
.upload-xlsx-box {
  display: flex;
  flex-flow: row nowrap;
}
</style>