exceljs · alubbe · Mar 29, 2020 · Feb 24, 2020
diff --git a/lib/stream/xlsx/worksheet-writer.js b/lib/stream/xlsx/worksheet-writer.js
@@ -30,6 +30,7 @@ const PageMarginsXform = require('../../xlsx/xform/sheet/page-margins-xform');
 const PageSetupXform = require('../../xlsx/xform/sheet/page-setup-xform');
 const AutoFilterXform = require('../../xlsx/xform/sheet/auto-filter-xform');
 const PictureXform = require('../../xlsx/xform/sheet/picture-xform');
+const ConditionalFormattingsXform = require('../../xlsx/xform/sheet/cf/conditional-formattings-xform');
 
 // since prepare and render are functional, we can use singletons
 const xform = {
@@ -45,6 +46,7 @@ const xform = {
   pageSeteup: new PageSetupXform(),
   autoFilter: new AutoFilterXform(),
   picture: new PictureXform(),
+  conditionalFormattings: new ConditionalFormattingsXform(),
 };
 
 // ============================================================================================
@@ -95,6 +97,9 @@ class WorksheetWriter {
     this._formulae = {};
     this._siFormulae = 0;
 
+    // keep a record of conditionalFormattings
+    this.conditionalFormatting = [];
+
     // for default row height, outline levels, etc
     this.properties = Object.assign(
       {},
@@ -203,6 +208,7 @@ class WorksheetWriter {
     // this._writeDimensions();
 
     this._writeHyperlinks();
+    this._writeConditionalFormatting();
     this._writeDataValidations();
     this._writeSheetProtection();
     this._writePageMargins();
@@ -415,6 +421,23 @@ class WorksheetWriter {
     this._merges.push(dimensions);
   }
 
+  // ===========================================================================
+  // Conditional Formatting
+  addConditionalFormatting(cf) {
+    this.conditionalFormatting.push(cf);
+  }
+
+  removeConditionalFormatting(filter) {
+    console.log('conditionalFormatting', this.conditionalFormatting);
+    if (typeof filter === 'number') {
+      this.conditionalFormatting.splice(filter, 1);
+    } else if (filter instanceof Function) {
+      this.conditionalFormatting = this.conditionalFormatting.filter(filter);
+    } else {
+      this.conditionalFormatting = [];
+    }
+  }
+
   // =========================================================================
 
   addBackgroundImage(imageId) {
@@ -549,6 +572,14 @@ class WorksheetWriter {
     this.stream.write(xform.hyperlinks.toXml(this._sheetRelsWriter._hyperlinks));
   }
 
+  _writeConditionalFormatting() {
+    const options = {
+      styles: this._workbook.styles,
+    };
+    xform.conditionalFormattings.prepare(this.conditionalFormatting, options);
+    this.stream.write(xform.conditionalFormattings.toXml(this.conditionalFormatting));
+  }
+
   _writeDataValidations() {
     this.stream.write(xform.dataValidations.toXml(this.dataValidations.model));
   }

diff --git a/spec/integration/workbook-xlsx-writer/workbook-xlsx-writer.spec.js b/spec/integration/workbook-xlsx-writer/workbook-xlsx-writer.spec.js
@@ -506,5 +506,28 @@ describe('WorkbookWriter', () => {
       const imageData = await fsReadFileAsync(IMAGE_FILENAME);
       expect(Buffer.compare(imageData, image.buffer)).to.equal(0);
     });
+
+    it('with conditional formatting', async () => {
+      const options = {
+        filename: TEST_XLSX_FILE_NAME,
+        useStyles: true,
+        useSharedStrings: true,
+      };
+      const wb = testUtils.createTestBook(
+        new ExcelJS.stream.xlsx.WorkbookWriter(options),
+        'xlsx',
+        ['conditionalFormatting']
+      );
+
+      return wb
+        .commit()
+        .then(() => {
+          const wb2 = new ExcelJS.Workbook();
+          return wb2.xlsx.readFile(TEST_XLSX_FILE_NAME);
+        })
+        .then(wb2 => {
+          testUtils.checkTestBook(wb2, 'xlsx', ['conditionalFormatting']);
+        });
+    });
   });
 });
diff --git a/spec/utils/data/conditional-formatting.json b/spec/utils/data/conditional-formatting.json
@@ -0,0 +1,74 @@
+{
+    "expression": {
+        "ref": "A1:E7",
+        "rules": [
+            {
+                "type": "expression",
+                "formulae": [
+                    "MOD(ROW()+COLUMN(),2)=0"
+                ],
+                "style": {
+                    "fill": {
+                        "type": "pattern",
+                        "pattern": "solid",
+                        "bgColor": {
+                            "argb": "FF00FF00"
+                        }
+                    }
+                }
+            },
+            {
+                "type": "expression",
+                "priority": 2,
+                "formulae": [
+                    "TRUE"
+                ],
+                "style": {
+                    "fill": {
+                        "type": "pattern",
+                        "pattern": "solid",
+                        "bgColor": {
+                            "argb": "FFFF0000"
+                        }
+                    }
+                }
+            }
+        ]
+    },
+    "top10": {
+        "ref": "A8:E15",
+        "rules": [
+            {
+                "type": "top10",
+                "percent": true,
+                "rank": 10,
+                "style": {
+                    "font": {
+                        "bold": true
+                    }
+                }
+            },
+            {
+                "type": "top10",
+                "percent": true,
+                "bottom": true,
+                "rank": 10,
+                "style": {
+                    "font": {
+                        "italic": true
+                    }
+                }
+            }
+        ]
+    },
+    "types": [
+        "expression",
+        "cellIs",
+        "top10",
+        "aboveAverage",
+        "colorScale",
+        "iconSet",
+        "containsText",
+        "timePeriod"
+    ]
+}
diff --git a/spec/utils/index.js b/spec/utils/index.js
@@ -10,6 +10,7 @@ const Column = verquire('doc/column');
 
 const testSheets = {
   dataValidations: require('./test-data-validation-sheet'),
+  conditionalFormatting: require('./test-conditional-formatting-sheet'),
   values: require('./test-values-sheet'),
   splice: require('./test-spliced-sheet'),
 };
@@ -53,6 +54,9 @@ module.exports = {
   styles: tools.fix(require('./data/styles.json')),
   properties: tools.fix(require('./data/sheet-properties.json')),
   pageSetup: tools.fix(require('./data/page-setup.json')),
+  conditionalFormatting: tools.fix(
+    require('./data/conditional-formatting.json')
+  ),
 
   createTestBook(workbook, docType, sheets) {
     const options = getOptions(docType);

diff --git a/spec/utils/test-conditional-formatting-sheet.js b/spec/utils/test-conditional-formatting-sheet.js
@@ -0,0 +1,36 @@
+const tools = require('./tools');
+
+const self = {
+  conditionalFormattings: tools.fix(require('./data/conditional-formatting')),
+  getConditionalFormatting(type) {
+    return self.conditionalFormattings[type] || null;
+  },
+  addSheet(wb) {
+    const ws = wb.addWorksheet('conditional-formatting');
+    const {types} = self.conditionalFormattings;
+    types.forEach(type => {
+      const conditionalFormatting = self.getConditionalFormatting(type);
+      if (conditionalFormatting) {
+        ws.addConditionalFormatting(conditionalFormatting);
+      }
+    });
+  },
+
+  checkSheet(wb) {
+    const ws = wb.getWorksheet('conditional-formatting');
+    expect(ws).to.not.be.undefined();
+    expect(ws.conditionalFormattings).to.not.be.undefined();
+    (ws.conditionalFormattings && ws.conditionalFormattings).forEach(item => {
+      const type = item.rules && item.rules[0].type;
+      const conditionalFormatting = self.getConditionalFormatting(type);
+      expect(item).to.have.property('ref');
+      expect(item).to.have.property('rules');
+      expect(self.conditionalFormattings[type]).to.have.property('ref');
+      expect(self.conditionalFormattings[type]).to.have.property('rules');
+      expect(item.ref).to.deep.equal(conditionalFormatting.ref);
+      expect(item.rules.length).to.equal(conditionalFormatting.rules.length);
+    });
+  },
+};
+
+module.exports = self;