文件导入导出功能

技术
字数统计: 1.9k   |   阅读时长≈ 10 分钟

一、安装

1
2
npm install file-saver --save
npm install xlsx --save

二、创建工具函数

为了使用方便，可以在 src/utils 下封装一个工具文件 Export2Excel.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
/* eslint-disable */
import { saveAs } from "file-saver";
import * as XLSX from "xlsx";

function generateArray(table) {
  var out = [];
  var rows = table.querySelectorAll("tr");
  var ranges = [];
  for (var R = 0; R < rows.length; ++R) {
    var outRow = [];
    var row = rows[R];
    var columns = row.querySelectorAll("td");
    for (var C = 0; C < columns.length; ++C) {
      var cell = columns[C];
      var colspan = cell.getAttribute("colspan");
      var rowspan = cell.getAttribute("rowspan");
      var cellValue = cell.innerText;
      if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;

      //Skip ranges
      ranges.forEach(function (range) {
        if (
          R >= range.s.r &&
          R <= range.e.r &&
          outRow.length >= range.s.c &&
          outRow.length <= range.e.c
        ) {
          for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
        }
      });

      //Handle Row Span
      if (rowspan || colspan) {
        rowspan = rowspan || 1;
        colspan = colspan || 1;
        ranges.push({
          s: {
            r: R,
            c: outRow.length,
          },
          e: {
            r: R + rowspan - 1,
            c: outRow.length + colspan - 1,
          },
        });
      }

      //Handle Value
      outRow.push(cellValue !== "" ? cellValue : null);

      //Handle Colspan
      if (colspan) for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
    }
    out.push(outRow);
  }
  return [out, ranges];
}

function datenum(v, date1904) {
  if (date1904) v += 1462;
  var epoch = Date.parse(v);
  return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}

function sheet_from_array_of_arrays(data, opts) {
  var ws = {};
  var range = {
    s: {
      c: 10000000,
      r: 10000000,
    },
    e: {
      c: 0,
      r: 0,
    },
  };
  for (var R = 0; R != data.length; ++R) {
    for (var C = 0; C != data[R].length; ++C) {
      if (range.s.r > R) range.s.r = R;
      if (range.s.c > C) range.s.c = C;
      if (range.e.r < R) range.e.r = R;
      if (range.e.c < C) range.e.c = C;
      var cell = {
        v: data[R][C],
      };
      if (cell.v == null) continue;
      var cell_ref = XLSX.utils.encode_cell({
        c: C,
        r: R,
      });

      if (typeof cell.v === "number") cell.t = "n";
      else if (typeof cell.v === "boolean") cell.t = "b";
      else if (cell.v instanceof Date) {
        cell.t = "n";
        cell.z = XLSX.SSF._table[14];
        cell.v = datenum(cell.v);
      } else cell.t = "s";

      ws[cell_ref] = cell;
    }
  }
  if (range.s.c < 10000000) ws["!ref"] = XLSX.utils.encode_range(range);
  return ws;
}

function Workbook() {
  if (!(this instanceof Workbook)) return new Workbook();
  this.SheetNames = [];
  this.Sheets = {};
}

function s2ab(s) {
  var buf = new ArrayBuffer(s.length);
  var view = new Uint8Array(buf);
  for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
  return buf;
}

export function export_table_to_excel(id) {
  var theTable = document.getElementById(id);
  var oo = generateArray(theTable);
  var ranges = oo[1];

  /* original data */
  var data = oo[0];
  var ws_name = "SheetJS";

  var wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);

  /* add ranges to worksheet */
  // ws['!cols'] = ['apple', 'banan'];
  ws["!merges"] = ranges;

  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name] = ws;

  var wbout = XLSX.write(wb, {
    bookType: "xlsx",
    bookSST: false,
    type: "binary",
  });

  saveAs(
    new Blob([s2ab(wbout)], {
      type: "application/octet-stream",
    }),
    "test.xlsx"
  );
}

export function export_json_to_excel({
  multiHeader = [],
  header,
  data,
  filename,
  merges = [],
  autoWidth = true,
  bookType = "xlsx",
} = {}) {
  /* original data */
  filename = filename || "excel-list";
  data = [...data];
  data.unshift(header);

  for (let i = multiHeader.length - 1; i > -1; i--) {
    data.unshift(multiHeader[i]);
  }

  var ws_name = "SheetJS";
  var wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);

  if (merges.length > 0) {
    if (!ws["!merges"]) ws["!merges"] = [];
    merges.forEach((item) => {
      ws["!merges"].push(XLSX.utils.decode_range(item));
    });
  }

  if (autoWidth) {
    /*设置worksheet每列的最大宽度*/
    const colWidth = data.map((row) =>
      row.map((val) => {
        /*先判断是否为null/undefined*/
        if (val == null) {
          return {
            wch: 10,
          };
        } else if (val.toString().charCodeAt(0) > 255) {
          /*再判断是否为中文*/
          return {
            wch: val.toString().length * 2,
          };
        } else {
          return {
            wch: val.toString().length,
          };
        }
      })
    );
    /*以第一行为初始值*/
    let result = colWidth[0];
    for (let i = 1; i < colWidth.length; i++) {
      for (let j = 0; j < colWidth[i].length; j++) {
        if (result[j]["wch"] < colWidth[i][j]["wch"]) {
          result[j]["wch"] = colWidth[i][j]["wch"];
        }
      }
    }
    ws["!cols"] = result;
  }

  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name] = ws;

  var wbout = XLSX.write(wb, {
    bookType: bookType,
    bookSST: false,
    type: "binary",
  });
  saveAs(
    new Blob([s2ab(wbout)], {
      type: "application/octet-stream",
    }),
    `${filename}.${bookType}`
  );
}

三、导出功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<template>
  <el-button type="primary" @click="exportTable">导出</el-button>
</template>
<script setup>
// 导出功能
// 用户菜单表头与数据字段对应关系
const userMenuHeader = {
  流程模板: "wf_type_name",
  审批编号: "apply_id",
  发起人: "staff_name",
  审批流程标题: "apply_title",
  审批金额: "apply_fee",
  发起时间: "create_date",
  流程状态: "proc_state_name",
  当前环节: "node_name",
  处理人: "username",
};
const exportTable = () => {
  //  做操作
  const excelData = [];
  // 这里后端返回的数据不能直接用作excel的data，不然浏览器会卡死，必须先将数据转化为表格需要的[[]]的形式
  tableData.value.forEach((item) => {
    const arr = [];
    for (const key in userMenuHeader) {
      arr.push(item[userMenuHeader[key]]);
    }
    excelData.push(arr);
  });

  // 懒加载
  try {
    import("@/utils/Export2Excel").then(async (excel) => {
      excel.export_json_to_excel({
        header: Object.keys(userMenuHeader),
        data: excelData,
        filename: "费用冲调审批流程查询清单",
        autoWidth: true,
        bookType: "xlsx",
      });
    });
  } catch {
    ElMessage.error("导出失败，请重试！");
  }
};
</script>

四、导入功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
<template>
  <el-upload
    ref="uploadRef"
    class="upload-demo"
    v-model:file-list="accList"
    :on-remove="handleRemove"
    :on-change="handleChangeBusiness"
    :http-request="upload"
    action="#"
    :auto-upload="false"
    style="display: flex; margin-bottom: 10px"
  >
    <template #trigger>
      <el-button type="primary" :icon="Document" style="margin-right: 150px"
        >选择文件</el-button
      >
    </template>
    <template #tip v-if="!accList.length">
      <div class="el-upload__tip" style="margin: 10px 0 0 -445px">
        未选择任何文件
      </div>
    </template>
    <el-button
      class="ml-3"
      :icon="Upload"
      type="success"
      @click="getServByAccNbrImport"
      style="margin-top: 2px"
    >
      批量导入费用争议数据
    </el-button>
    <el-button type="primary" :icon="Download"
      ><el-link href="/approve/static/费用争议导入模板.xls"
        >下载附件模板</el-link
      ></el-button
    >
  </el-upload>
</template>
<script setup>
// 批量上传
// 上传的文件
const accList = ref([]);
const businessFileList = ref([]);
const accountDetailList = ref([]); // 这是账期明细
// 进出物品对应关系
const businessRelation = {
  0: "bz",
  1: "phone_number",
  2: "spje",
  3: "serv_id",
  4: "acct_id",
  5: "cust_name",
  6: "acct_name",
  7: "sqr",
  8: "sqrdh",
  9: "sqyy",
  10: "sqyy_name",
  11: "oper_type",
  12: "oper_type_name",
  13: "ctlx_id",
  14: "ctlx",
  15: "phone_local",
};
const accountDetailRelation = {
  0: "phone_number",
  1: "fee_month",
  2: "charge",
  3: "fee_acct_type",
  4: "fee_acct_type_name",
};
const handleChangeBusiness = (file) => {
  const reader = new FileReader();
  reader.onload = async (e) => {
    const data = e.target.result;
    const workbook = XLSX.read(data, { type: "array" });
    const sheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheetName];
    const json = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
    json.forEach((item) => {
      const obj = {};
      for (const key in businessRelation) {
        obj[businessRelation[key]] = item[key];
      }
      businessFileList.value.push(obj);
    });
    // 对businessFileList去重
    businessFileList.value = [...new Set(businessFileList.value)];
    // 把这个数据传给后端，获取到后端返回的数据，渲染列表
    // 因为表格中可能会有空白的数据，上传上来的数据就会有undefined，所以要过滤掉
    businessFileList.value = businessFileList.value.filter((item) => item);
    // 因为批量上传的没有acc_nbr，所以要把phone_number赋值给acc_nbr
    businessFileList.value.forEach((item) => {
      item.acc_nbr = item.phone_number;
    });
    const sheetName1 = workbook.SheetNames[1];
    const worksheet1 = workbook.Sheets[sheetName1];
    const json1 = XLSX.utils.sheet_to_json(worksheet1, { header: 1 });
    json1.forEach((item) => {
      const obj = {};
      for (const key in accountDetailRelation) {
        obj[accountDetailRelation[key]] = item[key];
      }
      accountDetailList.value.push(obj);
    });
    // 对accountDetailList去重
    accountDetailList.value = [...new Set(accountDetailList.value)];
    accountDetailList.value = accountDetailList.value.filter((item) => item);
    // 将accountDetailList与businessFileList关联
    businessFileList.value.forEach((item) => {
      item.fee_list_month = accountDetailList.value.filter((item2) => {
        return item.phone_number === item2.phone_number;
      });
    });
  };

  reader.readAsArrayBuffer(file.raw);
};
const getServByAccNbrImport = async () => {
  if (businessFileList.value.length === 0) {
    ElMessage.error("请先选择文件");
    return;
  }
  if (businessFileList.value.length > 2000) {
    ElMessage.error(
      "上传的数据超过2000条，可能会导致查询时间过长，请分批次上传！"
    );
    return;
  }
  applyForm.feeListData = businessFileList.value;
};
</script>

最终的效果是这样的：可以看到，每一行数据下面关联了一个子表

image-20240313093547041

  • 版权声明： 本博客所有文章除特别声明外，著作权归作者所有。转载请注明出处！
  • Copyrights © 2023-2025 congtianfeng
  • 访问人数: | 浏览次数: