/
import-export.service.ts
139 lines (133 loc) · 5.02 KB
/
import-export.service.ts
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
// eslint-disable-next-line eslint-comments/disable-enable-pair
/* eslint-disable no-await-in-loop,@typescript-eslint/explicit-function-return-type,no-restricted-syntax */
import { Injectable } from '@nestjs/common';
import { read, utils, write } from 'xlsx';
import { LoggerFactory } from '../common/logger';
import { DBHelper } from '../core/db';
import { r } from '../common/helpers';
const logger = LoggerFactory.getLogger('ImportExportService');
@Injectable()
export class ImportExportService {
// 获取repo
private async getRepository(model: string) {
const modelName = DBHelper.getModelNameObject(model, '');
return DBHelper.repo(modelName);
// return DBHelper.extractAsunaSchemas(repository,{ module: 'www__', prefix: 't_' });
}
// 获取字段名称
private async getSchemas(repository) {
const schemas = DBHelper.extractAsunaSchemas(repository, { module: '', prefix: 't' });
const res = [];
schemas.forEach(value => {
if (
value.name !== 'ordinal' &&
value.name !== 'logoAlt' &&
value.name !== 'videos' &&
value.name !== 'coverAlt' &&
value.name !== 'studentAlt' &&
value.name !== 'isPublished' &&
value.name !== 'isFeatured' &&
value.name !== 'offers' &&
value.config.info !== null &&
value.config.info !== undefined &&
value.config.info.type !== 'Image' &&
value.config.info.name !== null &&
value.config.info.name !== undefined
) {
res.push(value);
}
});
return res;
}
// 导入Excel
async importExcel(fileBuffer: any, modelName: string) {
const workbook = read(fileBuffer, {});
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
const jsonArray = utils.sheet_to_json(worksheet, { header: 1 });
const repository = await this.getRepository(modelName);
const schemas = await this.getSchemas(repository);
const status = [];
for (let row = 1; row < jsonArray.length; row += 1) {
const entity = repository.create();
for (const [column, element] of schemas.entries()) {
if (jsonArray[0][column] === element.config.info.name) {
const keyName = element.name;
let value = jsonArray[row][column];
// 如果是外键关系表,则需要处理外键表数据
if (element.config.selectable !== undefined) {
const tempRepo = await this.getRepository(element.config.selectable);
if (!element.config.many) {
const res = await tempRepo.findOne({ name: jsonArray[row][column] } as any);
value = res;
} else {
// 处理多对多关系
const content: string = jsonArray[row][column];
if (content !== undefined) {
const contentArray = content.split('、');
const resArray = [];
contentArray.forEach(async temp => {
const res = await tempRepo.findOne({ name: temp.trim() } as any);
if (res !== undefined) {
resArray.push(res);
}
});
value = resArray;
}
}
}
// 如果表中已有该数据则删除
if (keyName === 'name') {
const res = await repository.findOne({ name: jsonArray[row][column] } as any);
if (res !== undefined) {
await repository.remove(res);
}
}
// logger.debug(`${modelName} set ${keyName} to ${value}`);
entity[keyName] = value;
}
}
logger.verbose(`save ${modelName}: ${r(entity)} by ${r(jsonArray[row])}`);
const saveRes = await repository.save(entity);
status.push(saveRes);
}
return status;
}
// 导出Excel
exportExcel(json: any[]): any {
const ss = utils.json_to_sheet(json); // 通过工具将json转表对象'
const keys = Object.keys(ss).sort(); // 排序 [需要注意,必须从A1开始]
// 构建 workbook 对象
const workbook = {
// 定义 作文档
SheetNames: ['sheet1'], // 定义表明
Sheets: {
sheet1: { ...ss, }, // 表对象[注意表明]
},
};
const buf = write(workbook, { type: 'buffer', bookType: 'xlsx' });
return buf;
}
// 导出Excel模板
async exportModel(tableName: string) {
const repository = await this.getRepository(tableName);
const schemas = await this.getSchemas(repository);
const json = [];
schemas.forEach(value => {
const temp = [];
temp[value.config.info.name] = null;
json.push(temp);
});
const ss = utils.json_to_sheet(json); // 通过工具将json转表对象
// const keys = Object.keys(ss).sort(); // 排序 [需要注意,必须从A1开始]
// 构建 workbook 对象
const workbook = {
// 定义 作文档
SheetNames: ['sheet1'], // 定义表明
Sheets: {
sheet1: { ...ss, }, // 表对象[注意表明]
},
};
const buf = write(workbook, { type: 'buffer', bookType: 'xlsx' });
return buf;
}
}