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
|
import { Application } from '@feathersjs/express';
import { ServiceMethods, Id } from '@feathersjs/feathers';
import ExcelJS from 'exceljs';
import _ from 'lodash';
import fs from 'fs';
import { WaybillSchema } from '../models/waybill/waybill.schema';
const duplicateRowWithMergedCells = (sheet: any, row: number, count: number) => {
sheet.duplicateRow(row, count, true);
const merges: string[] = sheet.model.merges;
const rowMerges = merges.filter(range => range.match(`\\w+${row}:\\w+${row}`));
_.times(count, index => {
const newRow = row + index + 1;
// Unmerge everything in a newRow so we dont run into conflicts
merges
.filter(range => range.match(`\\w+${newRow}:\\w+${newRow}`))
.forEach(range => sheet.unMergeCells(range));
// Merge the same cells as in the initial row
rowMerges
.map(range => range.replace(new RegExp(`${row}`, 'g'), `${newRow}`))
.forEach(range => sheet.mergeCells(range));
});
};
class Spreadsheets implements Partial<ServiceMethods<any>> {
app!: Application;
setup(app: Application) {
this.app = app;
}
async get(id: Id) {
const waybill: any = await this.app.service('waybills').get(id);
const workbook = new ExcelJS.Workbook();
await workbook.xlsx.readFile('./documents/waybill-template.xltx');
const sheet = workbook.getWorksheet('Вертикальная');
if (waybill.operation === 'in') {
sheet.getCell('Z5').value = waybill.contractor.vatId;
} else {
sheet.getCell('AS5').value = waybill.contractor.vatId;
}
duplicateRowWithMergedCells(sheet, 30, waybill.records.length - 1);
waybill.records.forEach((record: any, index: number) => {
const row = 30 + index;
const getCol = (col: string) => sheet.getCell(`${col}${row}`);
getCol('A').value = record.productId;
getCol('W').value = 'шт';
getCol('AG').value = record.quantity;
getCol('AP').value = record.price;
});
const fileName = `./documents/${waybill._id}.xlsx`;
await workbook.xlsx.writeFile(fileName);
return fileName;
}
}
export default (app: Application): void => {
app.use('/spreadsheets', new Spreadsheets, (req, res) => {
const fileName = res.data;
res.download(fileName, err => {
if (err) console.log(err);
fs.unlink(fileName, () => {});
});
});
};
|
