exceljs - npm Package Compare versions

Comparing version 3.3.1 to 3.4.0

dist/es5/csv/csv.js

@@ -19,4 +19,6 @@ "use strict";
 
-var dayjs = require('dayjs');
+var customParseFormat = require('dayjs/plugin/customParseFormat');
 
+var dayjs = require('dayjs').extend(customParseFormat);
+
 var StreamBuf = require('../utils/stream-buf');
@@ -130,3 +132,3 @@
       var worksheet = this.workbook.addWorksheet(options.sheetName);
-      var dateFormats = options.dateFormats || [dayjs.ISO_8601, 'MM-DD-YYYY', 'YYYY-MM-DD'];
+      var dateFormats = options.dateFormats || ['YYYY-MM-DD[T]HH:mm:ss', 'MM-DD-YYYY', 'YYYY-MM-DD'];
 
@@ -144,5 +146,17 @@ var map = options.map || function (datum) {
 
-        var dt = dayjs(datum, dateFormats, true);
+        var dt = dateFormats.reduce(function (matchingDate, currentDateFormat) {
+          if (matchingDate) {
+            return matchingDate;
+          }
 
-        if (dt.isValid()) {
+          var dayjsObj = dayjs(datum, currentDateFormat, true);
+
+          if (dayjsObj.isValid()) {
+            return dayjsObj;
+          }
+
+          return null;
+        }, null);
+
+        if (dt) {
           return new Date(dt.valueOf());
@@ -149,0 +163,0 @@ }

dist/es5/doc/data-validations.js

@@ -1,2 +0,2 @@
-'use strict';
+"use strict";
 
@@ -3,0 +3,0 @@ function _classCallCheck(instance, Constructor) { if (!(instance instanceof Constructor)) { throw new TypeError("Cannot call a class as a function"); } }

dist/es5/doc/range.js

@@ -1,2 +0,2 @@
-'use strict';
+"use strict";
 
@@ -189,2 +189,11 @@ function _classCallCheck(instance, Constructor) { if (!(instance instanceof Constructor)) { throw new TypeError("Cannot call a class as a function"); } }
   }, {
+    key: "forEachAddress",
+    value: function forEachAddress(cb) {
+      for (var col = this.left; col <= this.right; col++) {
+        for (var row = this.top; row <= this.bottom; row++) {
+          cb(colCache.encodeAddress(row, col), row, col);
+        }
+      }
+    }
+  }, {
     key: "top",
@@ -191,0 +200,0 @@ get: function get() {

dist/es5/stream/xlsx/workbook-writer.js

@@ -137,16 +137,20 @@ "use strict";
                 _context.next = 4;
-                return this._commitWorksheets();
+                return this.addMedia();
 
               case 4:
                 _context.next = 6;
-                return Promise.all([this.addContentTypes(), this.addApp(), this.addCore(), this.addSharedStrings(), this.addStyles(), this.addWorkbookRels()]);
+                return this._commitWorksheets();
 
               case 6:
                 _context.next = 8;
+                return Promise.all([this.addContentTypes(), this.addApp(), this.addCore(), this.addSharedStrings(), this.addStyles(), this.addWorkbookRels()]);
+
+              case 8:
+                _context.next = 10;
                 return this.addWorkbook();
 
-              case 8:
+              case 10:
                 return _context.abrupt("return", this._finalize());
 
-              case 9:
+              case 11:
               case "end":
@@ -166,2 +170,16 @@ return _context.stop();
   }, {
+    key: "addImage",
+    value: function addImage(image) {
+      var id = this.media.length;
+      this.media.push(Object.assign({}, image, {
+        type: 'image'
+      }));
+      return id;
+    }
+  }, {
+    key: "getImage",
+    value: function getImage(id) {
+      return this.media[id];
+    }
+  }, {
     key: "addWorksheet",
@@ -283,3 +301,4 @@ value: function addWorksheet(name, options) {
           sharedStrings: _this4.sharedStrings,
-          commentRefs: _this4.commentRefs
+          commentRefs: _this4.commentRefs,
+          media: _this4.media
         };
@@ -297,9 +316,45 @@ var xform = new ContentTypesXform();
   }, {
+    key: "addMedia",
+    value: function addMedia() {
+      var _this5 = this;
+
+      return Promise.all(this.media.map(function (medium, idx) {
+        medium.name = "image".concat(idx + 1, ".").concat(medium.extension);
+
+        if (medium.type === 'image') {
+          var filename = "xl/media/".concat(medium.name);
+
+          if (medium.filename) {
+            return _this5.zip.file(medium.filename, {
+              name: filename
+            });
+          }
+
+          if (medium.buffer) {
+            return _this5.zip.append(medium.buffer, {
+              name: filename
+            });
+          }
+
+          if (medium.base64) {
+            var dataimg64 = medium.base64;
+            var content = dataimg64.substring(dataimg64.indexOf(',') + 1);
+            return _this5.zip.append(content, {
+              name: filename,
+              base64: true
+            });
+          }
+        }
+
+        throw new Error('Unsupported media');
+      }));
+    }
+  }, {
     key: "addApp",
     value: function addApp() {
-      var _this5 = this;
+      var _this6 = this;
 
       return new Promise(function (resolve) {
         var model = {
-          worksheets: _this5._worksheets.filter(Boolean)
+          worksheets: _this6._worksheets.filter(Boolean)
         };
@@ -309,3 +364,3 @@ var xform = new AppXform();
 
-        _this5.zip.append(xml, {
+        _this6.zip.append(xml, {
           name: 'docProps/app.xml'
@@ -320,9 +375,9 @@ });
     value: function addCore() {
-      var _this6 = this;
+      var _this7 = this;
 
       return new Promise(function (resolve) {
         var coreXform = new CoreXform();
-        var xml = coreXform.toXml(_this6);
+        var xml = coreXform.toXml(_this7);
 
-        _this6.zip.append(xml, {
+        _this7.zip.append(xml, {
           name: 'docProps/core.xml'
@@ -337,3 +392,3 @@ });
     value: function addSharedStrings() {
-      var _this7 = this;
+      var _this8 = this;
 
@@ -343,5 +398,5 @@ if (this.sharedStrings.count) {
           var sharedStringsXform = new SharedStringsXform();
-          var xml = sharedStringsXform.toXml(_this7.sharedStrings);
+          var xml = sharedStringsXform.toXml(_this8.sharedStrings);
 
-          _this7.zip.append(xml, {
+          _this8.zip.append(xml, {
             name: '/xl/sharedStrings.xml'
@@ -359,3 +414,3 @@ });
     value: function addWorkbookRels() {
-      var _this8 = this;
+      var _this9 = this;
 
@@ -396,3 +451,3 @@ var count = 1;
 
-        _this8.zip.append(xml, {
+        _this9.zip.append(xml, {
           name: '/xl/_rels/workbook.xml.rels'
@@ -427,14 +482,14 @@ });
     value: function _finalize() {
-      var _this9 = this;
+      var _this10 = this;
 
       return new Promise(function (resolve, reject) {
-        _this9.stream.on('error', reject);
+        _this10.stream.on('error', reject);
 
-        _this9.stream.on('finish', function () {
-          resolve(_this9);
+        _this10.stream.on('finish', function () {
+          resolve(_this10);
         });
 
-        _this9.zip.on('error', reject);
+        _this10.zip.on('error', reject);
 
-        _this9.zip.finalize();
+        _this10.zip.finalize();
       });
@@ -441,0 +496,0 @@ }

dist/es5/stream/xlsx/worksheet-writer.js

 "use strict";
 
+function ownKeys(object, enumerableOnly) { var keys = Object.keys(object); if (Object.getOwnPropertySymbols) { var symbols = Object.getOwnPropertySymbols(object); if (enumerableOnly) symbols = symbols.filter(function (sym) { return Object.getOwnPropertyDescriptor(object, sym).enumerable; }); keys.push.apply(keys, symbols); } return keys; }
+
+function _objectSpread(target) { for (var i = 1; i < arguments.length; i++) { var source = arguments[i] != null ? arguments[i] : {}; if (i % 2) { ownKeys(source, true).forEach(function (key) { _defineProperty(target, key, source[key]); }); } else if (Object.getOwnPropertyDescriptors) { Object.defineProperties(target, Object.getOwnPropertyDescriptors(source)); } else { ownKeys(source).forEach(function (key) { Object.defineProperty(target, key, Object.getOwnPropertyDescriptor(source, key)); }); } } return target; }
+
+function _defineProperty(obj, key, value) { if (key in obj) { Object.defineProperty(obj, key, { value: value, enumerable: true, configurable: true, writable: true }); } else { obj[key] = value; } return obj; }
+
 function _classCallCheck(instance, Constructor) { if (!(instance instanceof Constructor)) { throw new TypeError("Cannot call a class as a function"); } }
@@ -169,20 +175,7 @@
 
-    this.autoFilter = options.autoFilter || null; // start writing to stream now
+    this.autoFilter = options.autoFilter || null;
+    this._media = []; // start writing to stream now
 
-    this._writeOpenWorksheet(); // background
+    this._writeOpenWorksheet();
 
-
-    if (options.background && options.background.type === 'image') {
-      var imageName = this._workbook.addMedia(options.background);
-
-      var pictureId = this._sheetRelsWriter.addMedia({
-        Target: "../media/".concat(imageName),
-        Type: RelType.Image
-      });
-
-      this._background = {
-        rId: pictureId
-      };
-    }
-
     this.startedData = false;
@@ -425,2 +418,15 @@ }
       this._merges.push(dimensions);
+    } // =========================================================================
+
+  }, {
+    key: "addBackgroundImage",
+    value: function addBackgroundImage(imageId) {
+      this._background = {
+        imageId: imageId
+      };
+    }
+  }, {
+    key: "getBackgroundImageId",
+    value: function getBackgroundImageId() {
+      return this._background && this._background.imageId;
     } // ================================================================================
@@ -577,3 +583,18 @@
       if (this._background) {
-        this.stream.write(xform.picture.toXml(this._background));
+        if (this._background.imageId !== undefined) {
+          var image = this._workbook.getImage(this._background.imageId);
+
+          var pictureId = this._sheetRelsWriter.addMedia({
+            Target: "../media/".concat(image.name),
+            Type: RelType.Image
+          });
+
+          this._background = _objectSpread({}, this._background, {
+            rId: pictureId
+          });
+        }
+
+        this.stream.write(xform.picture.toXml({
+          rId: this._background.rId
+        }));
       }
@@ -580,0 +601,0 @@ }

dist/es5/utils/under-dash.js

@@ -56,2 +56,8 @@ "use strict";
   },
+  keyBy: function keyBy(a, p) {
+    return a.reduce(function (o, v) {
+      o[v[p]] = v;
+      return o;
+    }, {});
+  },
   isEqual: function isEqual(a, b) {
@@ -93,2 +99,7 @@ var aType = _typeof(a);
     return html.replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;').replace(/"/g, '&quot;').replace(/'/g, '&#039;');
+  },
+  strcmp: function strcmp(a, b) {
+    if (a < b) return -1;
+    if (a > b) return 1;
+    return 0;
   }
@@ -95,0 +106,0 @@ };

dist/es5/xlsx/xform/sheet/data-validations-xform.js

@@ -21,2 +21,8 @@ "use strict";
 
+function ownKeys(object, enumerableOnly) { var keys = Object.keys(object); if (Object.getOwnPropertySymbols) { var symbols = Object.getOwnPropertySymbols(object); if (enumerableOnly) symbols = symbols.filter(function (sym) { return Object.getOwnPropertyDescriptor(object, sym).enumerable; }); keys.push.apply(keys, symbols); } return keys; }
+
+function _objectSpread(target) { for (var i = 1; i < arguments.length; i++) { var source = arguments[i] != null ? arguments[i] : {}; if (i % 2) { ownKeys(source, true).forEach(function (key) { _defineProperty(target, key, source[key]); }); } else if (Object.getOwnPropertyDescriptors) { Object.defineProperties(target, Object.getOwnPropertyDescriptors(source)); } else { ownKeys(source).forEach(function (key) { Object.defineProperty(target, key, Object.getOwnPropertyDescriptor(source, key)); }); } } return target; }
+
+function _defineProperty(obj, key, value) { if (key in obj) { Object.defineProperty(obj, key, { value: value, enumerable: true, configurable: true, writable: true }); } else { obj[key] = value; } return obj; }
+
 var _ = require('../../../utils/under-dash');
@@ -26,4 +32,8 @@
 
+var colCache = require('../../../utils/col-cache');
+
 var BaseXform = require('../base-xform');
 
+var Range = require('../../../doc/range');
+
 function assign(definedName, attributes, name, defaultValue) {
@@ -60,2 +70,73 @@ var value = attributes[name];
 
+function optimiseDataValidations(model) {
+  // Squeeze alike data validations together into rectangular ranges
+  // to reduce file size and speed up Excel load time
+  var dvList = _.map(model, function (dataValidation, address) {
+    return {
+      address: address,
+      dataValidation: dataValidation,
+      marked: false
+    };
+  }).sort(function (a, b) {
+    return _.strcmp(a.address, b.address);
+  });
+
+  var dvMap = _.keyBy(dvList, 'address');
+
+  var matchCol = function matchCol(addr, height, col) {
+    for (var i = 0; i < height; i++) {
+      var otherAddress = colCache.encodeAddress(addr.row + i, col);
+
+      if (!model[otherAddress] || !_.isEqual(model[addr.address], model[otherAddress])) {
+        return false;
+      }
+    }
+
+    return true;
+  };
+
+  return dvList.map(function (dv) {
+    if (!dv.marked) {
+      var addr = colCache.decodeAddress(dv.address); // iterate downwards - finding matching cells
+
+      var height = 1;
+      var otherAddress = colCache.encodeAddress(addr.row + height, addr.col);
+
+      while (model[otherAddress] && _.isEqual(dv.dataValidation, model[otherAddress])) {
+        height++;
+        otherAddress = colCache.encodeAddress(addr.row + height, addr.col);
+      } // iterate rightwards...
+
+
+      var width = 1;
+
+      while (matchCol(addr, height, addr.col + width)) {
+        width++;
+      } // mark all included addresses
+
+
+      for (var i = 0; i < height; i++) {
+        for (var j = 0; j < width; j++) {
+          otherAddress = colCache.encodeAddress(addr.row + i, addr.col + j);
+          dvMap[otherAddress].marked = true;
+        }
+      }
+
+      if (height > 1 || width > 1) {
+        var bottom = addr.row + (height - 1);
+        var right = addr.col + (width - 1);
+        return _objectSpread({}, dv.dataValidation, {
+          sqref: "".concat(dv.address, ":").concat(colCache.encodeAddress(bottom, right))
+        });
+      }
+
+      return _objectSpread({}, dv.dataValidation, {
+        sqref: dv.address
+      });
+    }
+
+    return null;
+  }).filter(Boolean);
+}
+
 var DataValidationsXform =
@@ -75,10 +156,9 @@ /*#__PURE__*/
     value: function render(xmlStream, model) {
-      var count = model && Object.keys(model).length;
+      var optimizedModel = optimiseDataValidations(model);
 
-      if (count) {
+      if (optimizedModel.length) {
         xmlStream.openNode('dataValidations', {
-          count: count
+          count: optimizedModel.length
         });
-
-        _.each(model, function (value, address) {
+        optimizedModel.forEach(function (value) {
           xmlStream.openNode('dataValidation');
@@ -126,3 +206,3 @@
 
-          xmlStream.addAttribute('sqref', address);
+          xmlStream.addAttribute('sqref', value.sqref);
           (value.formulae || []).forEach(function (formula, index) {
@@ -132,3 +212,3 @@ xmlStream.openNode("formula".concat(index + 1));
             if (value.type === 'date') {
-              xmlStream.writeText(utils.dateToExcel(formula));
+              xmlStream.writeText(utils.dateToExcel(new Date(formula)));
             } else {
@@ -142,3 +222,2 @@ xmlStream.writeText(formula);
         });
-
         xmlStream.closeNode();
@@ -158,3 +237,3 @@ }
             this._address = node.attributes.sqref;
-            var definedName = node.attributes.type ? {
+            var dataValidation = node.attributes.type ? {
               type: node.attributes.type,
@@ -167,9 +246,9 @@ formulae: []
             if (node.attributes.type) {
-              assignBool(definedName, node.attributes, 'allowBlank');
+              assignBool(dataValidation, node.attributes, 'allowBlank');
             }
 
-            assignBool(definedName, node.attributes, 'showInputMessage');
-            assignBool(definedName, node.attributes, 'showErrorMessage');
+            assignBool(dataValidation, node.attributes, 'showInputMessage');
+            assignBool(dataValidation, node.attributes, 'showErrorMessage');
 
-            switch (definedName.type) {
+            switch (dataValidation.type) {
               case 'any':
@@ -181,12 +260,12 @@ case 'list':
               default:
-                assign(definedName, node.attributes, 'operator', 'between');
+                assign(dataValidation, node.attributes, 'operator', 'between');
                 break;
             }
 
-            assign(definedName, node.attributes, 'promptTitle');
-            assign(definedName, node.attributes, 'prompt');
-            assign(definedName, node.attributes, 'errorStyle');
-            assign(definedName, node.attributes, 'errorTitle');
-            assign(definedName, node.attributes, 'error');
-            this._definedName = definedName;
+            assign(dataValidation, node.attributes, 'promptTitle');
+            assign(dataValidation, node.attributes, 'prompt');
+            assign(dataValidation, node.attributes, 'errorStyle');
+            assign(dataValidation, node.attributes, 'errorTitle');
+            assign(dataValidation, node.attributes, 'error');
+            this._dataValidation = dataValidation;
             return true;
@@ -212,2 +291,4 @@ }
     value: function parseClose(name) {
+      var _this = this;
+
       switch (name) {
@@ -218,8 +299,16 @@ case 'dataValidations':
         case 'dataValidation':
-          if (!this._definedName.formulae || !this._definedName.formulae.length) {
-            delete this._definedName.formulae;
-            delete this._definedName.operator;
+          if (!this._dataValidation.formulae || !this._dataValidation.formulae.length) {
+            delete this._dataValidation.formulae;
+            delete this._dataValidation.operator;
           }
 
-          this.model[this._address] = this._definedName;
+          if (this._address.includes(':')) {
+            var range = new Range(this._address);
+            range.forEachAddress(function (address) {
+              _this.model[address] = _this._dataValidation;
+            });
+          } else {
+            this.model[this._address] = this._dataValidation;
+          }
+
           return true;
@@ -232,3 +321,3 @@
 
-            switch (this._definedName.type) {
+            switch (this._dataValidation.type) {
               case 'whole':
@@ -251,3 +340,3 @@ case 'textLength':
 
-            this._definedName.formulae.push(formula);
+            this._dataValidation.formulae.push(formula);
 
@@ -254,0 +343,0 @@ return true;

dist/es5/xlsx/xform/style/styles-xform.js

@@ -171,2 +171,4 @@ "use strict";
       });
+
+      this.weakMap = new WeakMap();
     }
@@ -173,0 +175,0 @@ }, {

lib/csv/csv.js

 const fs = require('fs');
 const fastCsv = require('fast-csv');
-const dayjs = require('dayjs');
+const customParseFormat = require('dayjs/plugin/customParseFormat');
+const dayjs = require('dayjs').extend(customParseFormat);
 const StreamBuf = require('../utils/stream-buf');
@@ -54,3 +55,3 @@
 
-    const dateFormats = options.dateFormats || [dayjs.ISO_8601, 'MM-DD-YYYY', 'YYYY-MM-DD'];
+    const dateFormats = options.dateFormats || ['YYYY-MM-DD[T]HH:mm:ss', 'MM-DD-YYYY', 'YYYY-MM-DD'];
     const map =
@@ -66,4 +67,13 @@ options.map ||
         }
-        const dt = dayjs(datum, dateFormats, true);
-        if (dt.isValid()) {
+        const dt = dateFormats.reduce((matchingDate, currentDateFormat) => {
+          if (matchingDate) {
+            return matchingDate;
+          }
+          const dayjsObj = dayjs(datum, currentDateFormat, true);
+          if (dayjsObj.isValid()) {
+            return dayjsObj;
+          }
+          return null;
+        }, null);
+        if (dt) {
           return new Date(dt.valueOf());
@@ -70,0 +80,0 @@ }

lib/doc/data-validations.js

@@ -1,3 +0,1 @@
-'use strict';
-
 class DataValidations {
@@ -4,0 +2,0 @@ constructor(model) {

lib/doc/range.js

@@ -1,3 +0,1 @@
-'use strict';
-
 const colCache = require('./../utils/col-cache');
@@ -244,4 +242,12 @@
   }
+
+  forEachAddress(cb) {
+    for (let col = this.left; col <= this.right; col++) {
+      for (let row = this.top; row <= this.bottom; row++) {
+        cb(colCache.encodeAddress(row, col), row, col);
+      }
+    }
+  }
 }
 
 module.exports = Range;

lib/stream/xlsx/workbook-writer.js

@@ -100,4 +100,12 @@ const fs = require('fs');
     await this.promise;
+    await this.addMedia();
     await this._commitWorksheets();
-    await Promise.all([this.addContentTypes(), this.addApp(), this.addCore(), this.addSharedStrings(), this.addStyles(), this.addWorkbookRels()]);
+    await Promise.all([
+      this.addContentTypes(),
+      this.addApp(),
+      this.addCore(),
+      this.addSharedStrings(),
+      this.addStyles(),
+      this.addWorkbookRels(),
+    ]);
     await this.addWorkbook();
@@ -118,2 +126,12 @@ return this._finalize();
 
+  addImage(image) {
+    const id = this.media.length;
+    this.media.push(Object.assign({}, image, {type: 'image'}));
+    return id;
+  }
+  
+  getImage(id) {
+    return this.media[id];
+  }
+
   addWorksheet(name, options) {
@@ -202,2 +220,3 @@ // it's possible to add a worksheet with different than default
         commentRefs: this.commentRefs,
+        media: this.media,
       };
@@ -211,2 +230,25 @@ const xform = new ContentTypesXform();
 
+  addMedia() {
+    return Promise.all(
+      this.media.map((medium, idx) => {
+        medium.name = `image${idx+1}.${medium.extension}`;
+        if (medium.type === 'image') {
+          const filename = `xl/media/${medium.name}`;
+          if (medium.filename) {
+            return this.zip.file(medium.filename, {name: filename});
+          }
+          if (medium.buffer) {
+            return this.zip.append(medium.buffer, {name: filename});
+          }
+          if (medium.base64) {
+            const dataimg64 = medium.base64;
+            const content = dataimg64.substring(dataimg64.indexOf(',') + 1);
+            return this.zip.append(content, {name: filename, base64: true});
+          }
+        }
+        throw new Error('Unsupported media');
+      })
+    );
+  }
+
   addApp() {
@@ -213,0 +255,0 @@ return new Promise(resolve => {

lib/stream/xlsx/worksheet-writer.js

@@ -153,17 +153,7 @@ const _ = require('../../utils/under-dash');
 
+    this._media = [];
+
     // start writing to stream now
     this._writeOpenWorksheet();
 
-    // background
-    if (options.background && options.background.type === 'image') {
-      const imageName = this._workbook.addMedia(options.background);
-      const pictureId = this._sheetRelsWriter.addMedia({
-        Target: `../media/${imageName}`,
-        Type: RelType.Image,
-      });
-      this._background = {
-        rId: pictureId,
-      };
-    }
-
     this.startedData = false;
@@ -433,4 +423,17 @@ }
 
+  // =========================================================================
 
+  addBackgroundImage(imageId) {
+    this._background = {
+      imageId,
+    };
+  }
+  
+
+  getBackgroundImageId() {
+    return this._background && this._background.imageId;
+  }
+  
   // ================================================================================
+
   _write(text) {
@@ -576,3 +579,16 @@ xmlBuffer.reset();
     if (this._background) {
-      this.stream.write(xform.picture.toXml(this._background));
+      
+      if(this._background.imageId !== undefined){
+        const image = this._workbook.getImage(this._background.imageId);
+        const pictureId = this._sheetRelsWriter.addMedia({
+          Target: `../media/${image.name}`,
+          Type: RelType.Image,
+        });
+        
+        this._background = {
+          ...this._background,
+          rId: pictureId,
+        };
+      }
+      this.stream.write(xform.picture.toXml({rId: this._background.rId}));
     }
@@ -579,0 +595,0 @@ }

lib/utils/under-dash.js

@@ -44,2 +44,9 @@ const _ = {
 
+  keyBy(a, p) {
+    return a.reduce((o, v) => {
+      o[v[p]] = v;
+      return o;
+    }, {});
+  },
+
   isEqual: function isEqual(a, b) {
@@ -86,4 +93,10 @@ const aType = typeof a;
   },
+
+  strcmp(a, b) {
+    if (a < b) return -1;
+    if (a > b) return 1;
+    return 0;
+  },
 };
 
 module.exports = _;

lib/xlsx/xform/sheet/data-validations-xform.js

 const _ = require('../../../utils/under-dash');
 const utils = require('../../../utils/utils');
+const colCache = require('../../../utils/col-cache');
 const BaseXform = require('../base-xform');
+const Range = require('../../../doc/range');
 
@@ -31,2 +33,68 @@ function assign(definedName, attributes, name, defaultValue) {
 
+function optimiseDataValidations(model) {
+  // Squeeze alike data validations together into rectangular ranges
+  // to reduce file size and speed up Excel load time
+  const dvList = _
+    .map(model, (dataValidation, address) => ({
+      address,
+      dataValidation,
+      marked: false,
+    }))
+    .sort((a, b) => _.strcmp(a.address, b.address));
+  const dvMap = _.keyBy(dvList, 'address');
+  const matchCol = (addr, height, col) => {
+    for (let i = 0; i < height; i++) {
+      const otherAddress = colCache.encodeAddress(addr.row + i, col);
+      if (!model[otherAddress] || !_.isEqual(model[addr.address], model[otherAddress])) {
+        return false;
+      }
+    }
+    return true;
+  };
+  return dvList
+    .map(dv => {
+      if (!dv.marked) {
+        const addr = colCache.decodeAddress(dv.address);
+
+        // iterate downwards - finding matching cells
+        let height = 1;
+        let otherAddress = colCache.encodeAddress(addr.row + height, addr.col);
+        while (model[otherAddress] && _.isEqual(dv.dataValidation, model[otherAddress])) {
+          height++;
+          otherAddress = colCache.encodeAddress(addr.row + height, addr.col);
+        }
+
+        // iterate rightwards...
+
+        let width = 1;
+        while (matchCol(addr, height, addr.col + width)) {
+          width++;
+        }
+
+        // mark all included addresses
+        for (let i = 0; i < height; i++) {
+          for (let j = 0; j < width; j++) {
+            otherAddress = colCache.encodeAddress(addr.row + i, addr.col + j);
+            dvMap[otherAddress].marked = true;
+          }
+        }
+
+        if ((height > 1) || (width > 1)) {
+          const bottom = addr.row + (height - 1);
+          const right = addr.col + (width - 1);
+          return {
+            ...dv.dataValidation,
+            sqref: `${dv.address}:${colCache.encodeAddress(bottom, right)}`,
+          };
+        }
+        return {
+          ...dv.dataValidation,
+          sqref: dv.address,
+        };
+      }
+      return null;
+    })
+    .filter(Boolean);
+}
+
 class DataValidationsXform extends BaseXform {
@@ -38,8 +106,9 @@ get tag() {
   render(xmlStream, model) {
-    const count = model && Object.keys(model).length;
-    if (count) {
-      xmlStream.openNode('dataValidations', {count});
+    const optimizedModel = optimiseDataValidations(model);
+    if (optimizedModel.length) {
+      xmlStream.openNode('dataValidations', {count: optimizedModel.length});
 
-      _.each(model, (value, address) => {
+      optimizedModel.forEach(value => {
         xmlStream.openNode('dataValidation');
+
         if (value.type !== 'any') {
@@ -76,7 +145,7 @@ xmlStream.addAttribute('type', value.type);
         }
-        xmlStream.addAttribute('sqref', address);
+        xmlStream.addAttribute('sqref', value.sqref);
         (value.formulae || []).forEach((formula, index) => {
           xmlStream.openNode(`formula${index + 1}`);
           if (value.type === 'date') {
-            xmlStream.writeText(utils.dateToExcel(formula));
+            xmlStream.writeText(utils.dateToExcel(new Date(formula)));
           } else {
@@ -101,18 +170,13 @@ xmlStream.writeText(formula);
         this._address = node.attributes.sqref;
-        const definedName = node.attributes.type
-          ? {
-            type: node.attributes.type,
-            formulae: [],
-          }
-          : {
-            type: 'any',
-          };
+        const dataValidation = node.attributes.type ?
+          {type: node.attributes.type, formulae: []} :
+          {type: 'any'};
 
         if (node.attributes.type) {
-          assignBool(definedName, node.attributes, 'allowBlank');
+          assignBool(dataValidation, node.attributes, 'allowBlank');
         }
-        assignBool(definedName, node.attributes, 'showInputMessage');
-        assignBool(definedName, node.attributes, 'showErrorMessage');
+        assignBool(dataValidation, node.attributes, 'showInputMessage');
+        assignBool(dataValidation, node.attributes, 'showErrorMessage');
 
-        switch (definedName.type) {
+        switch (dataValidation.type) {
           case 'any':
@@ -123,12 +187,12 @@ case 'list':
           default:
-            assign(definedName, node.attributes, 'operator', 'between');
+            assign(dataValidation, node.attributes, 'operator', 'between');
             break;
         }
-        assign(definedName, node.attributes, 'promptTitle');
-        assign(definedName, node.attributes, 'prompt');
-        assign(definedName, node.attributes, 'errorStyle');
-        assign(definedName, node.attributes, 'errorTitle');
-        assign(definedName, node.attributes, 'error');
+        assign(dataValidation, node.attributes, 'promptTitle');
+        assign(dataValidation, node.attributes, 'prompt');
+        assign(dataValidation, node.attributes, 'errorStyle');
+        assign(dataValidation, node.attributes, 'errorTitle');
+        assign(dataValidation, node.attributes, 'error');
 
-        this._definedName = definedName;
+        this._dataValidation = dataValidation;
         return true;
@@ -154,9 +218,17 @@ }
       case 'dataValidations':
+
         return false;
       case 'dataValidation':
-        if (!this._definedName.formulae || !this._definedName.formulae.length) {
-          delete this._definedName.formulae;
-          delete this._definedName.operator;
+        if (!this._dataValidation.formulae || !this._dataValidation.formulae.length) {
+          delete this._dataValidation.formulae;
+          delete this._dataValidation.operator;
         }
-        this.model[this._address] = this._definedName;
+        if (this._address.includes(':')) {
+          const range = new Range(this._address);
+          range.forEachAddress(address => {
+            this.model[address] = this._dataValidation;
+          });
+        } else {
+          this.model[this._address] = this._dataValidation;
+        }
         return true;
@@ -166,3 +238,3 @@ case 'formula1':
         let formula = this._formula.join('');
-        switch (this._definedName.type) {
+        switch (this._dataValidation.type) {
           case 'whole':
@@ -181,3 +253,3 @@ case 'textLength':
         }
-        this._definedName.formulae.push(formula);
+        this._dataValidation.formulae.push(formula);
         return true;
@@ -184,0 +256,0 @@ }

lib/xlsx/xform/style/styles-xform.js

@@ -85,2 +85,4 @@ /* eslint-disable max-classes-per-file */
     this._addFill({type: 'pattern', pattern: 'gray125'});
+
+    this.weakMap = new WeakMap();
   }
@@ -87,0 +89,0 @@

package.json

 {
   "name": "exceljs",
-  "version": "3.3.1",
+  "version": "3.4.0",
   "description": "Excel Workbook Manager - Read and Write xlsx and csv Files.",
@@ -5,0 +5,0 @@ "private": false,

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

5805007

increased by0.62%

Lines of code

105340

increased by0.4%

Number of lines in readme file

2423

increased by0.04%

No dependency changes