node-file-xlsx.html

<script type="text/javascript">
    RED.nodes.registerType('file-xlsx',{
        category: '💾_data',
        color: '#3FADB5',
        defaults: { 
            name:       { value: undefined },
            workbook:   { value: undefined },
            worksheet:  { value: undefined },
            range:   { value: undefined },
            action:   { value: 'set' },
            clear:    {value: false},
            line:    { value: false},
            column:  { value: false},
            fields:  { value: 'all' },
            save:    { value: true},
            saveloc:   { value: "_sheet"},
            selfields: {value: [""]},
            cell_l: { value: undefined},
            cell_c: { value: undefined},
            input:   { value: "payload" },
            output:  { value: "payload" },
            savelocType:  {value: 'global'},
            inputType:  {value: 'msg'},
            outputType: {value: 'msg'},
            worksheetType:  {value: 'str'},
            workbookType:  {value: 'str'},
            rangeType:  {value: 'str'},
            cell_lType: {value: 'str'},
            cell_cType: {value: 'str'}
            
        },
        inputs:  1,
        outputs: 2,
        outputLabels: ["stdout", "stderr"],
        icon: "xlsx.svg",
        align: "left",
        paletteLabel: 'XLSX file',
        label: function() { 
            return (this.name) || "XLSX";
        },
        oneditprepare: function() {
            $("#node-input-name").typedInput({  default: 'str',  types: ['str'],  type:'str' });
            $("#node-input-workbook").typedInput({  default: 'str',  types: ['str','msg','global'],  typeField: $("#node-input-workbookType") });
            $("#node-input-worksheet").typedInput({  default: 'str',  types: ['str','msg','global'],  typeField: $("#node-input-worksheetType") });
            
            $("#node-input-range").typedInput({  default: 'str',  types: ['str','msg','global'],  typeField: $("#node-input-rangeType") });
            $("#node-input-cell_l").typedInput({  default: 'str',  types: ['str','msg','global'],  typeField: $("#node-input-cell_lType") });
            $("#node-input-cell_c").typedInput({  default: 'str',  types: ['str','msg','global'],  typeField: $("#node-input-cell_cType") });
            $("#node-input-saveloc").typedInput({  default: 'global',  types: ['msg','global'],  typeField: $("#node-input-savelocType") });
            $("#node-input-input").typedInput({  default: 'msg',  types: ['msg','global'], typeField: $("#node-input-inputType")  });
            $("#node-input-output").typedInput({  default: 'msg',  types: ['msg','global'], typeField: $("#node-input-outputType")  });

            $("#node-input-save").change( function() {
                $('.save').hide();
                if ($(this).is(":checked")) {
                    $(".save").show();
                    $("#node-input-saveloc").typedInput('show');
                }
            }).change();

            $("#node-input-action").change(function() {
                $(".set, .get, .ctn, .cell").hide();
                if ($(this).val() === "set") {
                    $(".set").show();
                    if ($("#node-input-fields").val() === "select") $(".ctn").show();
                    $("#node-input-input").typedInput('show');
                }
                else if ($(this).val() === "get") $(".get").show();
                else if ($(this).val() === "cell") {
                    $(".cell").show();
                    $("#node-input-cell_c").typedInput('show');
                    $("#node-input-cell_l").typedInput('show');
                }
            });

            $("#node-input-fields").change(function() {
                $(".ctn").hide();
                if ($(this).val() === "select") $(".ctn").show();
            });


            $("#node-input-selfields-ctn").css('min-height','100px').editableList({
                addItem: function ( row, index, data ) {
                    var content = data.field || '';
                    var hiddenField = $('<span/>', {}).appendTo(row);
                    var titleField =  $('<input/>',  { class:"node-input-property-ctn",  type:"text", style:"width:90%", value: content,  placeholder:"Key"}).appendTo(row);
                },
                sortable: true,
                removable: true
            });

            for (var i=0; i<this.selfields.length; i++) {      
                var field = this.selfields[i];
                $("#node-input-selfields-ctn").editableList('addItem', { field: field, i:i });
            }
        },
        oneditsave: function(){
            let fields = $("#node-input-selfields-ctn").editableList('items'),
                results = new Array();

            fields.each(function(i) {
                var content = $(this).find('.node-input-property-ctn').val();
                if (content) results.push(content);
            });
            this.selfields = (results.length === 0) ? [""] : results;
            
        }
    });
</script>

<script type="text/x-red" data-template-name="file-xlsx">
    <div class="form-row">
        <label for="node-input-name"><i class="icon-tag"></i> Name</label>
        <input type="text" id="node-input-name" placeholder="Excel" style="width:70%;">
    </div>

    <br>
    <b>File settings </b>

    <div class="form-row">
        <br>
        <label for="node-input-workbook"><i class="fa fa-table"></i> File</label>
        <input type="text" id="node-input-workbook" style="width: 70%" placeholder="Path to file">
        <input type="hidden" id="node-input-workbookType">
    </div>
    <div class="form-row">
        <label for="node-input-worksheet"><i class="fa fa-table"></i> Worksheet</label>
        <input type="text" id="node-input-worksheet" style="width: 70%" placeholder="Worksheet ID/name">
        <input type="hidden" id="node-input-worksheetType">
    </div>
    <div class="form-row">
        <label for="node-input-range"><i class="fa fa-table"></i> Range</label>
        <input type="text" id="node-input-range" style="width: 70%" placeholder="Range (empty: auto)">
        <input type="hidden" id="node-input-rangeType">
    </div>
    <div class="form-row">
        <label for="node-input-save"><i class="fa fa-save"></i> Save</label>
        <input type="checkbox" style="width: auto; vertical-align:top;" id="node-input-save"> <span>Save sheet's data in memory</span>
    </div>
    <div class="form-row save">
        <input type="text" id="node-input-saveloc" style="width: 70%; margin-left: 105px;" placeholder="">
        <input type="hidden" id="node-input-savelocType">
    </div>

    <br>
    <b>Request settings</b>

    <div class="form-row">
        <br>
        <label for="node-input-action"><i class="fa fa-tasks"></i> Action</label>
        <select id="node-input-action" style="width: 70%">
            <option value="set">Set data</option>
            <option value="get">Get data</option>
            <option value="cell">Get cell</option>
            <option value="clear">Clear data</option>
        </select>
    </div>

    <div class="form-row new get set">
        <label for="node-input-column"><i class="fa fa-tags"></i> Labels</label>
        <input type="checkbox" style="width: auto; vertical-align:top;" id="node-input-line"> <span>First line for labels</span>
        <input type="checkbox" style="width: auto; vertical-align:top;  margin-left:25px;" id="node-input-column"> <span>First column for labels</span>
    </div>

    <div class="form-row set">
        <label for="node-input-input"><i class="fa fa-sign-in"></i> Input</label>
        <input type="text" id="node-input-input" style="width: 70%" placeholder="payload"/> 
        <input type="hidden" id="node-input-inputType">
    </div>

    <div class="form-row set">
        <label for="node-input-fields"><i class="fa fa-tags"></i> Fields</label>
        <select id="node-input-fields" style="width: 70%">
            <option value="all">All</option>
            <option value="select">Select</option>
        </select>
    </div>

    <div class="form-row cell">
        <label for="node-input-cell_c"><i class="fa fa-table"></i> Cell</label>
        <input type="text" id="node-input-cell_c" style="width: 35%" placeholder="Column"/>
        <input type="text" id="node-input-cell_l" style="width: 34%" placeholder="Line"/> 
        <input type="hidden" id="node-input-cell_lType">
        <input type="hidden" id="node-input-cell_cType">
    </div>

    <div class="form-row ctn" style="width:70%; margin-left: 105px;">
        <ol id="node-input-selfields-ctn"></ol>
        <!-- <textarea id="node-input-selfields" rows="5" style="width:70%; margin-left: 105px;" placeholder="path.to.field"></textarea> -->
    </div>

    <div class="form-row">
        <label for="node-input-output"><i class="fa fa-sign-out"></i> Output</label>
        <input type="text" id="node-input-output" style="width: 70%" placeholder="payload"/>
        <input type="hidden" id="node-input-outputType">
    </div>
</script>

<style>
    .help-section > ol > h4,
    .help-section > h3 { font-weight: bold; }
</style>

<script type="text/x-red" data-help-name="file-xlsx">

    <p>Manipulate a XLSX file. </p>

    <h3>Outputs</h3>
    <ol class="node-ports">
        <li>Standard output</li>
        <li>Standard error</li>
    </ol>

    <h3>Details</h3>
    <p>Properties</p>
    <dl class="message-properties">
        <dt>File<span class="property-type">string</span></dt>
        <dd>the file location (path)</dd>
        <dt>Worksheet<span class="property-type">string</span></dt>
        <dd>the tab/sheet name or ID</dd>
        <dt>Range<span class="property-type">string</span></dt>
        <dd>the range to get/set/update (ex: "A1:C2"); leave empty to use an automatic range</dd>
        <dt>Output<span class="property-type">string</span></dt>
        <dd>where to store the results</dd>
    </dl>
    <p>In order to avoid processing the same operation several times, the node can <i>save</i> the data in the given field.
        <b>Note:</b> Please delete its content every time the sheet's data has been changed! Otherwise, it will not be uploaded.
        This can be done with a simple "inject" node followed by a "change" node (ex: delete global._sheet).
    </p>
    <p>
        <code>Set data</code> updates the value of the range. The <i>input</i> has to be an <b>array</b> or a <b>JSON object</b>. If the input
        is an object, it is possible to use the object keys as labels, eg first line and/or column. Note that it is only possible to set data by lines, 
        and not by columns. You can process a <code>Clear data</code> action before setting new data to ensure the range is correct.
    </p>
    <p>
        <b>Note:</b> You can chose to only write some <i>fields</i> of the input object in the spreadsheet. Chose these fields as: if the input object is 
        <code>msg.payload = { "monday": 3, "tuesday": 25, "wednesday": 12, "thursday": 6, "friday": 2" }</code> then I can chose to write only the <i>monday</i>
        and <i>tuesday</i> fields in my worksheet.
    </p>
    <p>
        <code>Get data</code> gets the value of the range. Check <i>labels</i> for line and/or column if the first line and/or the first column in your 
        spreadsheet is used for labels. The keys of the resulting objects will be adjusted accordingly.
    </p>
    <p>
        <code>Get cell</code> gets the value of a cell. Precise line and column labels. Both should be precised. If nothing is found, the result is "Not found".
    </p>

    <h3>References</h3>
    <ul>
        <li><a href="http://sheetjs.com/opensource">SheetJS</a> - documentation</li>
        <li><a href="https://github.com/NGRP/node-red-contrib-viseo/">VISEO BotMaker</a> - the nodes github repository</li>
    </ul>

</script>