How to add conditional formatting to an Excel file in Javascript?

Question

How should I add conditional formatting to some columns in an Excel file that I generate in Node.js? When I add the xml to the worksheet like this answer I get errors in Excel, thought the file opens. I generate the file using xlsx in Node.js so the Excel file looks like this inside:

Sample.xlsx
- [Content_Types].xml
- ...
- xl
  - _rels
  - sharedString.xml
  - styles.xml
  - theme
  - workbook.xml
  - worksheets
    - sheet1.xml

I write my conditional formatting into sheet1.xml, here is the code to do this:

var fs = require("fs");
var jszip = require("jszip");
var CONDITIONAL_FORMATTING = '<conditionalFormatting sqref="A1">' +
  '<cfRule type="expression" dxfId="0" priority="1"><formula>LEN(A1)&' +
  'gt;2</formula></cfRule></conditionalFormatting>';
var WORKSHEET1 = 'xl/worksheets/sheet1.xml';

fs.readFile("excel/rewrite.xlsx", function(err, data) {
    jszip.loadAsync(data).
        then(function(zip) {
            rezip = zip;
            return zip.file(WORKSHEET1).async("string");
        }).then(function (worksheet) {
            var pos = worksheet.indexOf('<pageMargins');
            if(pos > 0) {
                worksheet = worksheet.substring(0,pos) +
                  CONDITIONAL_FORMATTING + worksheet.substring(pos);
            } 
            rezip.file(WORKSHEET1,worksheet);
            rezip.generateNodeStream({type:'nodebuffer',streamFiles:true})
                .pipe(fs.createWriteStream('out.xlsx'))
                .on('finish', function () {
                    console.log('done');    
                });
        });
});

Michael Shopsin · Accepted Answer · 2016-06-08 15:37:05Z

I figured out how to add conditional formatting to columns in Excel so I'm sharing my solution. This code reads an Excel file generated by xlsx and adds conditional formatting to columns A and C. The trick is that styles.xml and sheet1.xml both need to be modified so that the conditional formatting has a style to apply.

var fs = require("fs");
var jszip = require("jszip");
var WORKSHEET1 = 'xl/worksheets/sheet1.xml';
var STYLES = 'xl/styles.xml';
var STYLE_WITHOUT_CONDITIONAL_FORMATTING = '<dxfs count="0"/>';
var STYLE_WITH_CONDITIONAL_FORMATTING = '<dxfs count="1"><dxf><font>' + 
   '<color rgb="FF9C0006"/></font><fill><patternFill>' +
   '<bgColor rgb="FFFFC7CE"/></patternFill></fill></dxf></dxfs>';
var zip;

function buildConditionalFormulas(worksheet,columnFormats) {
    var conditionalFormatting = '';
    var i;
    for(i = 0; i < columnFormats.length; i++) {
        conditionalFormatting += '<conditionalFormatting sqref="' + columnFormats[i].column +
            '1:' + columnFormats[i].column + '200"><cfRule type="expression" dxfId="0" ' +
            ' priority="1"><formula>LEN(' + columnFormats[i].column + '1)&gt;' +
            columnFormats[i].maximum + '</formula></cfRule></conditionalFormatting>';
    }
    conditionalFormatting += '<pageMargins';
    return worksheet.replace('<pageMargins',conditionalFormatting);
}

fs.readFile("excel/rewrite.xlsx", function(err, data) {
    jszip.loadAsync(data).
        then(function(ziper) {
            zip = ziper;
            return zip.file(WORKSHEET1).async("string");
        }).then(function (worksheet) {
            worksheet = buildConditionalFormulas(worksheet,[{column:'A',maximum:5},{column:'C',maximum:10}]);
            zip.file(WORKSHEET1,worksheet);
            return zip.file(STYLES).async("string");
        }).then(function (styles) {
            styles = styles.replace(STYLE_WITHOUT_CONDITIONAL_FORMATTING,STYLE_WITH_CONDITIONAL_FORMATTING);
            zip.file(STYLES,styles);
            zip.generateNodeStream({type:'nodebuffer',streamFiles:true})
                .pipe(fs.createWriteStream('out.xlsx'))
                .on('finish', function () {
                    console.log('done');
                });
        });
});

Collectives™ on Stack Overflow

How to add conditional formatting to an Excel file in Javascript?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related