Merge pull request #799 from SharkMediaSport/master

Add support for repeated columns on every page when printing.
exceljs · May 7, 2019 · a7bb3aa · a7bb3aa
2 parents 2415152 + 3f7f7b4
commit a7bb3aa
Show file tree

Hide file tree

Showing 4 changed files with 109 additions and 6 deletions.
diff --git a/README.md b/README.md
@@ -302,6 +302,8 @@ worksheet.pageSetup.printArea = 'A1:G20';
 // Repeat specific rows on every printed page
 worksheet.pageSetup.printTitlesRow = '1:3';
 
+// Repeat specific columns on every printed page
+worksheet.pageSetup.printTitlesColumn = 'A:C';
 ```
 
 **Supported pageSetup settings**

diff --git a/index.d.ts b/index.d.ts
@@ -754,6 +754,11 @@ export interface PageSetup {
 	 * Repeat specific rows on every printed page, e.g. `'1:3'`
 	 */
 	printTitlesRow: string;
+
+	/**
+	 * Repeat specific columns on every printed page, e.g. `'A:C'`
+	 */
+	printTitlesColumn: string;
 }
 
 export type AutoFilter = string | {

diff --git a/lib/xlsx/xform/book/workbook-xform.js b/lib/xlsx/xform/book/workbook-xform.js
@@ -54,13 +54,28 @@ utils.inherits(WorkbookXform, BaseXform, {
         };
         printAreas.push(definedName);
       }
-      if (sheet.pageSetup && sheet.pageSetup.printTitlesRow) {
-        const titlesRows = sheet.pageSetup.printTitlesRow.split(':');
+      if (sheet.pageSetup && (sheet.pageSetup.printTitlesRow || sheet.pageSetup.printTitlesColumn)) {
+
+        let ranges = [];
+
+        if (sheet.pageSetup.printTitlesColumn) {
+
+          const titlesColumns = sheet.pageSetup.printTitlesColumn.split(':');
+          ranges.push('\'' + sheet.name + '\'!$' + titlesColumns[0] + ':$' + titlesColumns[1]);
+        }
+
+        if (sheet.pageSetup.printTitlesRow) {
+
+          const titlesRows = sheet.pageSetup.printTitlesRow.split(':');
+          ranges.push('\'' + sheet.name + '\'!$' + titlesRows[0] + ':$' + titlesRows[1]);
+        }
+
         const definedName = {
             name: '_xlnm.Print_Titles',
-            ranges: ['\'' + sheet.name + '\'!$' + titlesRows[0] + ':$' + titlesRows[1]],
+            ranges: ranges,
             localSheetId: index
         };
+
         printAreas.push(definedName);
       }
       index++;
@@ -183,9 +198,25 @@ utils.inherits(WorkbookXform, BaseXform, {
           if (!worksheet.pageSetup) {
             worksheet.pageSetup = {};
           }
-          const longRange = definedName.ranges[0].split('!');
-          const range = longRange[longRange.length - 1];
-          worksheet.pageSetup.printTitlesRow = range;
+
+          const rangeString = definedName.ranges.join(",");
+
+          const rowRangeRegex = /\$\d+:\$\d+/;
+          const rowRangeMatches = rangeString.match(rowRangeRegex);
+
+          if (rowRangeMatches && rowRangeMatches.length) {
+
+            worksheet.pageSetup.printTitlesRow = rowRangeMatches[0];
+          }
+
+          const columnRangeRegex = /\$[A-Z]+:\$[A-Z]+/;
+          const columnRangeMatches = rangeString.match(columnRangeRegex);
+
+          if (columnRangeMatches && columnRangeMatches.length) {
+
+            worksheet.pageSetup.printTitlesColumn = columnRangeMatches[0];
+          }
+
         }
       } else {
         definedNames.push(definedName);

diff --git a/spec/integration/workbook/workbook.spec.js b/spec/integration/workbook/workbook.spec.js
@@ -93,6 +93,71 @@ describe('Workbook', function() {
         .then(function(wb2) {
           var ws2 = wb2.getWorksheet('printHeader');
           expect(ws2.pageSetup.printTitlesRow).to.equal('$1:$2');
+          expect(ws2.pageSetup.printTitlesColumn).to.be.undefined;
+        });
+    });
+    it('printTitlesColumn', function() {
+      var wb = new Excel.Workbook();
+      var ws = wb.addWorksheet('printColumn');
+
+      ws.getCell('A1').value = 'This is a column repeated on every printed page';
+      ws.getCell('A2').value = 'This is a column repeated on every printed page';
+      ws.getCell('B1').value = 'This is a repeated column too';
+      ws.getCell('B2').value = 'This is a repeated column too';
+
+      ws.getCell('C1').value = 'This is a regular column';
+      ws.getCell('C2').value = 'This is a regular column';
+      ws.getCell('D1').value = 'This is a regular column';
+      ws.getCell('D2').value = 'This is a regular column';
+
+      ws.pageSetup.printTitlesRow = 'A:B';
+
+      return wb.xlsx.writeFile(TEST_XLSX_FILE_NAME)
+        .then(function() {
+          var wb2 = new Excel.Workbook();
+          return wb2.xlsx.readFile(TEST_XLSX_FILE_NAME);
+        })
+        .then(function(wb2) {
+          var ws2 = wb2.getWorksheet('printColumn');
+          expect(ws2.pageSetup.printTitlesRow).to.be.undefined;
+          expect(ws2.pageSetup.printTitlesColumn).to.equal('$A:$B');
+        });
+    });
+    it('printTitlesRowAndColumn', function() {
+      var wb = new Excel.Workbook();
+      var ws = wb.addWorksheet('printHeaderAndColumn');
+
+      ws.getCell('A1').value = 'This is a column / row repeated on every printed page';
+      ws.getCell('A2').value = 'This is a column / row repeated on every printed page';
+      ws.getCell('B1').value = 'This is a repeated column / row too';
+      ws.getCell('B2').value = 'This is a repeated column / row too';
+
+      ws.getCell('C1').value = 'This is a regular column, repeated row';
+      ws.getCell('C2').value = 'This is a regular column, repeated row';
+      ws.getCell('D1').value = 'This is a regular column, repeated row';
+      ws.getCell('D2').value = 'This is a regular column, repeated row';
+
+      ws.getCell('A3').value = 'This is a repeated column';
+      ws.getCell('B3').value = 'This is a repeated column';
+      ws.getCell('C3').value = 'This is a regular column / row';
+      ws.getCell('D3').value = 'This is a regular column / row';
+
+      ws.pageSetup.printTitlesColumn = 'A:B';
+      ws.pageSetup.printTitlesRow = '1:2';
+
+      for (var i = 0; i < 100; i++) {
+        ws.addRow(['repeated column, not repeated row', 'repeated column, not repeated row', 'no repeat', 'no repeat']);
+      }
+
+      return wb.xlsx.writeFile(TEST_XLSX_FILE_NAME)
+        .then(function() {
+          var wb2 = new Excel.Workbook();
+          return wb2.xlsx.readFile(TEST_XLSX_FILE_NAME);
+        })
+        .then(function(wb2) {
+          var ws2 = wb2.getWorksheet('printHeaderAndColumn');
+          expect(ws2.pageSetup.printTitlesRow).to.equal('$1:$2');
+          expect(ws2.pageSetup.printTitlesColumn).to.equal('$A:$B');
         });
     });