feat: initalize the spreadsheet

2 files changed, 42 insertions, 4 deletions

diff --git a/documents/waybill-template.xltx b/documents/waybill-template.xltx
new file mode 100644
index 0000000..9e6754c
--- /dev/null
+++ b/documents/waybill-template.xltx
diff --git a/src/services/spreadsheets.service.ts b/src/services/spreadsheets.service.ts
index 437decc..b33a653 100644
--- a/src/services/spreadsheets.service.ts
+++ b/src/services/spreadsheets.service.ts
@@ -1,8 +1,29 @@
 import { Application } from '@feathersjs/express';
 import { ServiceMethods, Id } from '@feathersjs/feathers';
 import ExcelJS from 'exceljs';
+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;
@@ -12,15 +33,32 @@ class Spreadsheets implements Partial<ServiceMethods<any>> {
   }
 
   async get(id: Id) {
-    const waybill: WaybillSchema = await this.app.service('waybills').get(id);
+    const waybill: any = await this.app.service('waybills').get(id);
     const workbook = new ExcelJS.Workbook();
-    const sheet = workbook.addWorksheet('Накладная');
+    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}`);
 
-    waybill.records.forEach(record => {
-      sheet.addRow([record.productId, record.quantity, record.price]);
+      getCol('A').value = record.productId;
+      getCol('W').value = 'шт';
+      getCol('AG').value = record.quantity;
+      getCol('AP').value = record.price;
     });
 
     await workbook.xlsx.writeFile('./documents/waybill.xlsx');
+    console.log('written!');
     return 'written!';
   }
 }