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
|
import { Application } from '@feathersjs/express';
import { ServiceMethods, Id } from '@feathersjs/feathers';
import ExcelJS from 'exceljs';
import Bluebird from 'bluebird';
import fs from 'fs';
import _ from 'lodash';
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;
}
// Fill the products section
duplicateRowWithMergedCells(sheet, 30, waybill.records.length - 1);
await Bluebird.map(waybill.records, async (record: any, index: number) => {
const row = 30 + index;
const getCol = (col: string) => sheet.getCell(`${col}${row}`);
const product = await this.app.service('products').get(record.productId);
getCol('A').value = product.name;
getCol('W').value = 'шт';
getCol('AG').value = record.quantity;
getCol('AP').value = record.price;
getCol('AZ').value = record.price * record.quantity;
getCol('BK').value = 0.2;
getCol('BT').value = record.price * record.quantity * 0.2;
getCol('CC').value = record.price * record.quantity * 1.2;
});
// Total
const totalRow = 30 + waybill.records.length;
['AG', 'AP', 'AZ', 'BT', 'CC'].forEach((col: string) => {
sheet.getCell(`${col}${totalRow}`).value = {
formula: `SUM(${col}30:${col}${totalRow - 1})`
} as any;
});
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, () => {});
});
});
};
|
