xsanisty / calx.js Goto Github PK

I noticed SUMIF was always returning 0. It seems the Range is being set to an object and not to an array causing the length property to error with an undefined error.

SUMIF : function(range, criteria) {
var result = 0;
for (var i = 0; i < range.length; i++) {
result += (eval(range[i] + criteria)) ? range[i] : 0;
}
return result;
},

should be

SUMIF : function(rangeData, criteria) {
var result = 0;
var range = utility.objectToArray(rangeData);
for (var i = 0; i < range.length; i++) {
result += (eval(range[i] + criteria)) ? range[i] : 0;
}
return result;
},

I'm riding a code where I need the IF returns a value instead of true and false
data-formula="IF(A1>A2, 15, 20"
Not = True and False

Hello

Can jquery-calx be used to process an existing xslx?

Hi,
Please check cell.fx.recyncFormula function. the code below
this.formula = this.el.attr('data-formula');
will set this.formula as undefined if the element doesn't contain data-formula.

 However the undefined value is not able to clear the cell processed flag. 

in sheet.fx.clearProcessFlag function, only the formula properties is false of cell
will be marked as processed and and not affected:

Thanks,
Bo

2.2.7 version
have a problem with dynamic_form

I entry price and qty

add new item
and entry price
have error in calculate. All numbers are 0

2.2.6 version and 2.2.5 version it is working

Hello,

I have a product form with multiple fields - product qty, price and VAT (%).
At the end of form I have 3 hidden fields - total amount of all products with incl. VAT, total amount without VAT. and 3th field - the amount of VAT.

For the third field I just take out the amount without VAT from the final amount incl. VAT and receive the amount of VAT.

The data format of all fields is "0,0[.]00".

And the problem:

Have one product - the price is 213, 5 qty, 20% VAT.
The result is: amount excl. VAT = 1,065, amount incl. VAT = 1,278 and amount of VAT is 213.

Everything is correctly.

BUT when I change the qty to 3

The result is: amount excl. VAT = 639, amount incl. VAT = 766,80 and amount of VAT is 76041,00.

Can you tell me where I wrong.

In order for angular to process changes to an input field that is set programmatically an 'input' event needs to be fired on the updated form item.

Example:

this will not trigger an angular update
$("#foo").val("x");

However:

this will
$("#foo").val("x");
$("#foo").triggerHandler('input');

In the
cell.fx.renderComputedValue

the following code can be changed to trigger angular changes with the addition of the bolded values:

if(isFormTag){
            if(this.isCheckbox){
                this.el.prop('checked', (originalVal == this.el.val()));
            }else if(tagName == 'select'){
                this.el.val(originalVal);
newline         this.el.triggerHandler('input');
            }else if(tagName == 'input' || tagName == 'textarea'){
                this.el.val(formattedVal);
newline         this.el.triggerHandler('input');
            }
}

PS: I realize this is an enhancement I was unsure how to tag it as such.

The call stack is full of cell.fx.checkCircularReference. Rerunning with checkCircularReference: false, fails similarly on cell.fx.processDependency.

There are about 4500 cells, defined in the data: property, not on DOM elements. No circular dependencies, I think (the original Excel didn't have any). Perhaps I've just reached a natural limit? Could dependency handling be flattened, i.e. turned into an iterative rather than recursive algorithm?

Here: http://spreadsheet.aviscuriosa.com/browserscript-calx2.0.html

Hi,

My application has always worked correctly. From today I find this console error "z (...). Unformat is not a function".
Could you give me some indication to go back to the cause?

thank you

Is there a way to trigger the recalculation of all the fields of the form? Say if I want to bind it so that it recalculates on users typing (with a second delay for example); I'm just concerned about users seeing the wrong values if they go right for the submit button right after entering data and the field never losing focus forcing a recalculation.

Thanks!

Is there a limit to number of data-cells supported? i was able to resolve issue below, but a radio button stopped working
I have js code:
td data-cell="E6" id="E6" data-formula="B6+C6+D6" data-format="$ 0,0"

the E6 is not being updated, when B6,C6,D6 change.
the intial sum when page loaded remains unchanged

When loaded in Firefox 37.0.1 the error is shown inside Firebug JavaScript console.

when checkbox is present, calculation is running properly when checkbox is checked, but when it's unchecked, the calculation result is not updated

Hello.

In order to access the internal formulas when registering custom formulas it would be nice to have access to the formula object by a getter function equivalent to getUtility().
I have localized the formulas to the german language by mapping the german formula names to the internal formulas. For this I have appended a getFormula() function. It would be nice, if this would be added to the main branch.

getFormula : function () { return formula; },

Regards
Tom

The provided sample doesn't for some reason render donut, pie and bar charts (see picture below) - like the online example does.

Also, in the online example source I can't see where the flot script (jquery.flot.min.js or similar) is called. What am I missing?

Thanks

why the data-cell must be macth [A-Z]+\d+ ?

how to confgure like taxMny1 reg: [A-Za-z]+\d+ or [A-Za-z]+\d+[A-Za-z]+

i found data-cell attr value only work such as A1 A2 K3 TEST1 , but no work like money1 tableName1_price

I used calx to build a simple interactive web spreadsheet. The user will be entering values (with autoCalculate false) and after performing some calculations, the user will be re-entering values in the same input fields to recalculate to see the effect of the changes.

(1)When modifying the contents of an input field (on focus) i need the cursor to display at the end position and not at the beginning position - is there an option to allow for that? Or, better yet, (2) is there a way to allow the the input field contents to be "emptied" or "deleted" on focus IF new content is entered (and preserved on focus if new content is not entered? Thank you.

See title...

Hi, on data-format attribute, if the attribute value is the empty string "" then get error undefined format error that broke the run of the library.

Thanks for your work.
Giorgio

Hi is it possible to add a data-formula which depends on another data-formula ?
for example i have a snippet given in the same order.

 <td><input id="C0" data-format="0[.]00000" class="C0" data-formula="$A0*(2^($B0/53))" value="$A0*(2^($B0/53))" type="text"></td>
       <td><input id="D0" data-formula="1200*$C0/$C1" value="1200*$C0/$C1" type="text"></td>

 <td><input id="C1" data-format="0[.]00000" class="C1" data-formula="$A1*(2^($B1/53))" value="$A1*(2^($B1/53))" type="text"></td>
       <td><input id="D1" data-formula="1200*$C1/$C2" value="1200*$C1/$C2" type="text"></td>

Thanks for your great lib!
Is it possible to have a , (comma) instead of a . (dot) as decimal indicator?
Tried some combinations with data-format, but couldn't get it.

Thanks!

I am attempting to use the excel function '&' to add text to a formula:
in excel: data-formula='IF(D8="ON","Alternative 3:
 "&M76-1&"",IF(D8="AB","Alternative 3 "&M76-1&"","NA"))'
D8 = ON
MA76 = 10

result = #ERROR!

desired result: Alternative 3: 9

how would I go about combining text + formula in calx?

The default usage is

data-format="$ 0,0[.]00"

but when I try to use something else, like EURO currency symbol

data-format="€ 0,0[.]00"

the result is only a number, the currency symbol is missing.

Hi,

I'm using dynamic forms with three rows. When I set a value in a row, a cell of previous row sets its value to zero.
Can you help me, please?

<td><input class="form-control input-sm text-right" data-cell="A'+i+'" data-format="0"></td>\ <td><input class="form-control input-sm" data-cell="B'+i+'"></td>\ <td><input class="form-control input-sm text-right" data-cell="C'+i+'" data-format="$ 0,0.00"></td>\ <td><input class="form-control input-sm text-right" data-cell="D'+i+'" data-format="0[.]00"></td>\ <td><input class="form-control input-sm text-right" data-cell="E'+i+'" data-format="0,0[.]00 %"></td>\ <td><input class="form-control input-sm text-right" data-cell="F'+i+'" data-format="$ 0,0.00" data-formula="D'+i+'*(C'+i+'-(C'+i+'*E'+i+'))"></td>\

Jquery-calx works just fine when the form is created. But when form gets updated/edited, the cell won't recalculate itself even when the related cells changed number. How to fix this problem?

Hi
I have a html form with about 60 calculations , 7 inputs , multiple dependencies between cells.
All the calculations were perfect , but one cell is not getting updated on change of parent dependency.
On initial page load, it has correct value, but on change of input, this one cell's value is not getting updated.
while debugging noticed in calx console logs that this cell is not being called as dependent.
how is it that it was calculated initially, but will not change on further change.
The cells with similar formula different cell inputs change and update.
Any suggestions

currently, the method name inside the utility object is sometime misleading, the method name doesn't describe what id does. so it need to be renamed to be meaningful name

HI,
I am trying to implement.Sum Logic.
Eg: 2 Sheets (Sheet1 & Sheet2)
Sheet1: Dynamic Table which will have rows inserted.
Sheet2: Constant Table
Both are working fine.
Problem: Sheet2 needs an Sum value from Sheet1 colA.

1. Sum(#Sheet2:ColA1:ColA10) this is constant
2. where Sheet1 is Dynamic so every time when i add new row to the sheet1 then i need to change the formula of sheet2.

My request is can we have logic which even excel has like Sum(#Sheet2:ColA:ColA) which will consider entire column and sums. It will helps in lots of workaround externally. Even we can pull data from multiple sheets dynamically.

Hi,

Thanks for the development. I have a query regarding the order of columns and rows as I have a scenario whereby a table cell which is dependant on another is not calculating until I perform a second calculate.

Lets say I have a table as below. On the first pass data-cell A1 will be empty, then calling calculate will update it with the correct value.

Changing the order so that the cell is in bottom right instead of top left "fixes" the issue by forcing it to be the last thing calculated. Do we have any functionality to cope with this scenario since I cannot move the order of my cells and calling calculate again will not be good for performance.

<table id="sheet1" style="width:100%;">
    <thead>
        <tr></tr>
        <tr>
            <th>A</th>
            <th>B</th>
            <th>C</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td data-cell="a1" data-formula="a2+a3+a4"></td>
            <td data-cell="b1"></td>
            <td data-cell="c1"></td>
        </tr>
        <tr>
            <td data-cell="a2" data-formula="b2*c2"></td>
            <td data-cell="b2">4.23</td>
            <td data-cell="c2">150</td>
        </tr>
        <tr>
            <td data-cell="a3" data-formula="b3*c3"></td>
            <td data-cell="b3">2.23</td>
            <td data-cell="c3">100</td>
        </tr>
        <tr>
            <td data-cell="a4" data-formula="b4*c4"></td>
            <td data-cell="b4">1.23</td>
            <td data-cell="c4">250</td>
        </tr>
    </tbody>
</table>

<script>
    $('#calx_form').calx({
        data : {
            B1 : {value: '100'},
            B2 : {value: '200'},
            B3 : {formula: 'B1+B2'},
            B4 : {formula: 'B3^2'}
        }
    });
</script>

for above script, can i revise this script to if I check the checkbox (assume B1 is the cell of a checkbox), B1 value is 10, then if i uncheck the checkbox, then B1 value is 20?

calx currently unbind onchange event of all involved element when initialized, causing some trouble when used together with jquery mobile or other plugin,

although it can be tricked by initialize calx first before loading or initializing other plugin, but it's not a pretty way to solve the problem

We encountered a strange issue where if you have autoCalculateTrigger: keyup and input="number" field involved in a formula it will displace the position of the cursor after you type a decimal.

So after you type the decimal(.) the cursor goes to the left of it: 11|.
As you can tell it would be hard for a user to quickly type in decimals using a numpad as numbers after the decimal would come in before. Also we need to use input="number" as our forms are used in a mobile context and we need that number keyboard to come up.

We removed autoCalculateTrigger for now and rely on blur().

Thanks.

Hi,

I don't whether its an issue or my mistake. I am using calx for a form which populates values using ajaxx. My problem is when i click or infocus of populated textboxes , filled values are changing to zero and no calculation is happening ?

my code is given below which is using your sample

</head>

<body>
    <form id="calx">
    <table>
        <thead>
        <tr>
            <td style="width:250px">Item Name</td>
            <td style="width:80px">Price</td>
            <td style="width:30px">Qty</td>
             <td style="width:30px">rate</td>
            <td style="width:30px">Discount</td>
            <td style="width:150px">Sub Total</td>
        </tr>
        </thead>
        <tbody>
        <tr>
            <td><input id="item1" type="text" name="item[]" value="" onblur="getbookvalues(this.value,this.id)" /></td>
            <td><input id="price1" type="text" name="price[]" value="" data-format="00.00" /></td>
            <td><input id="qty1" type="text" name="qty[]" value="" data-format="0" /></td>
            <td><input id="rate1" type="text" name="rate[]" value=""  readonly="true" tabindex="-1" data-format="00.00" /></td>
            <td><input id="disc1" type="text" name="disc[]" value="" data-format="0.00" /></td>
            <td><input id="subtotal1" type="text" name="subtotal[]" value="" data-format="00.00" data-formula="($price1*$qty1)*($rate1)*(1-$disc1/100)" /></td>
        </tr>
        <tr>
            <td><input id="item2" type="text" name="item[]" value="LCD Monitor BENQ 21"" /></td>
            <td><input id="price2" type="text" name="price[]" value="250" data-format="$ 0,0[.]00" /></td>
            <td><input id="qty2" type="text" name="qty[]" value="1" data-format="0" /></td>
            <td><input id="disc2" type="text" name="disc[]" value="0.1" data-format="0.00 %" /></td>
            <td><input id="subtotal2" type="text" name="subtotal[]" value="" data-format="$ 0,0[.]00" data-formula="($price2*$qty2)*(1-$disc2)" /></td>
        </tr>
        <tr>
            <td><input id="item3" type="text" name="item[]" value="LiteOn DVD RW+" /></td>
            <td><input id="price3" type="text" name="price[]" value="120" data-format="$ 0,0[.]00" /></td>
            <td><input id="qty3" type="text" name="qty[]" value="1" data-format="0" /></td>
            <td><input id="disc3" type="text" name="disc[]" value="0.2" data-format="0.00 %" /></td>
            <td><input id="subtotal3" type="text" name="subtotal[]" value="" data-format="$ 0,0[.]00" data-formula="($price3*$qty3)*(1-$disc3)" /></td>
        </tr>
        <tr>
            <td><input id="item4" type="text" name="item[]" value="MotherBoard Gigabyte G5-XXX" /></td>
            <td><input id="price4" type="text" name="price[]" value="140" data-format="$ 0,0[.]00" /></td>
            <td><input id="qty4" type="text" name="qty[]" value="1" data-format="0" /></td>
            <td><input id="disc4" type="text" name="disc[]" value="0.05" data-format="0.00 %" /></td>
            <td><input id="subtotal4" type="text" name="subtotal[]" value="" data-format="$ 0,0[.]00" data-formula="($price4*$qty4)*(1-$disc4)" /></td>
        </tr>
        <tr>
            <td><input id="item5" type="text" name="item[]" value="PSU 600 Watt" /></td>
            <td><input id="price5" type="text" name="price[]" value="30" data-format="$ 0,0[.]00" /></td>
            <td><input id="qty5" type="text" name="qty[]" value="1" data-format="0" /></td>
            <td><input id="disc5" type="text" name="disc[]" value="0" data-format="0.00 %" /></td>
            <td><input id="subtotal5" type="text" name="subtotal[]" value="" data-format="$ 0,0[.]00" data-formula="($price5*$qty5)*(1-$disc5)" /></td>
        </tr>

<tr>
    <td><input id="calculate" type="button" value="calculate" /></td>
    <td><input id="price_total" type="text" name="price_total" data-format="$ 0,0[.]00" data-formula="($price1+$price2+$price3+$price4+$price5)" /></td>
    <td colspan="2">Total Price :</td>
    <td><input id="total" type="text" name="total" value="" data-format="$ 0,0[.]00" data-formula="($subtotal1+$subtotal2+$subtotal3+$subtotal4+$subtotal5)" /></td>
</tr>
</tbody>

In pt-br locale, thousand delimiter is ., while decimal delimiter is ,.

Case:

<form id="calx_form">
    <input type="tel" data-cell="A0" data-format="0">
    <input type="tel" data-cell="A1" data-format="0.00%">
</form>

1. Type in A0, say "2,02"
2. TAB. A0 displays 2 (2.02 internally saved)
3. Type in A1, say "2,03"
4. shift-tab. A1 becomes 2,03%. A0 becomes "2.02"
5. tab. A0 becomes 202.
6. shift-tab. A1 becomes 203%.

Workaround:

    this.el.on('calx.getValue', 'input[data-cell]', function(){
        var cellAddr    = $(this).attr('data-cell'),
            currentCell = currentSheet.cells[cellAddr],
            cellValue   = currentCell.getValue(),
            cellFormat  = currentCell.getFormat();

        //removed % case here        

        if(!currentCell.isCheckbox){
            //from currentCell.el.val( cellValue );
            currentCell.el.val( numeral(cellValue).format(cellFormat) );
        }

    });

Remove "%" handling case. Apply formatted value to cell. This is not 100% correct because now the user never sees the real value of the cell when selected, but it was ok for my use case. A workaround...

Hi,
I try to take advantage of the keyboard to navigate through the fields. But I have a problem. There's an error "sheet is not defined".
Here is the code (from my page https://www.fluidtopics.com/roi-2/) :

jQuery('#roi').on('keydown', 'input', function(e) {
    var self = jQuery(this)
      , form = self.parents('form:eq(0)')
      , focusable
      , next
      ;
    if (e.keyCode == 13) {
        console.log("enter pressed");
        jQuery(".roiquestion:visible").toggle("slide", { direction: "left" }, 300, function(){
            jQuery(this).next(".roiquestion").toggle("slide", { direction: "right" }, 300);
        });
        focusable = form.find('input').filter(':visible');
        console.log(focusable);
        next = focusable.eq(focusable.index(this)+1);
        if (next.length) {
            next.focus();
        } else {
            sheet.calculate();
            jQuery(".roiquestions").fadeOut(function(){
                jQuery(".roiresult").delay(400).fadeIn();
            });
        }
        return false;
    }
});

Thanks a lot for your help !
PS : I'm working with the 2.0.5 version as this code doesn't work at all with the last revision.

I am trying to figure out how to use server side calcs. There is a link in the samples but the file does not exist.

Calculating 2.3 + 2.4 returns 4.699999999999999 instead of 4.7

About floating-point arithmetic operations go to:
http://floating-point-gui.de/
http://www.w3schools.com/js/js_numbers.asp

Steps to reproduce (on the demo web of calx2 jquery):

1. go to http://prototype.xsanisty.com/calx2/
2. introduce 2.3 + 2.4 in the formula textbox
3. the result shown 4.699999999999999 (the expected result 4.7)

Steps to reproduce (in JavaScript code):
calc.calx ('calculate', "2.3 + 2.4") // returns 4.699999999999999 (instead of 4.7)

I personally use it in a validation and I can not use it with the Excel ROUND function because the formulas are created by the users.
e.g. IF (2.3 + 2.4 = 4.7, "correct", "incorrect") // returns "incorrect" (instead of "correct")

This is not that complicated a form. Can't understand why this is happening
Fiddle: https://jsfiddle.net/Mrbaseball34/dtbygwpb/

It also clears the value entered into the field. Try putting value in ECI Amount field then tab out.

What am I doing wrong?

Hello Ikhsan -
I would like to offer my assistance in helping to build the WordPress plugin.

Do you need help with it?

github username: rayflores

Thanks, Ray Flores

Hello,

I noticed that #19 addresses kinda the same issue.

Even if I use utilities.ArrayMerge there are issues with the condition.
For example, what if I wanted to write AVERAGEIF(A1:B2, "!= 'N/A'").
This will fail because if one of the cells contain text, the eval will throw an exception because of:
eval(N/A != 'N/A')
Uncaught ReferenceError: N is not defined(…)

Also things like !=, &&, || are not very Excel like, but I can work around that by providing documentation to my users.

In the end what I want to do is use AVERAGE a bunch of cells but skip the ones with text and/or empty fields.

Thanks.

!ERROR is shown in IE when VLOOKUP is used. Is there a way to fix this?

Do you have an example of producing a series of results? I tried to adapt the dynamic example, but I need the opposite. One formula to loop though a series of answers and display rows. I was able to hard code the results but I would like it to be dynamic.

Then any ideas on how to get the same data and pass it to a chart plug-in to make a graph over time?

I was wondering if you could add support for webkit's "up/down" arrows that are added to fields. Here is a hack that I've implemented, which works fine but is very expensive:

// here we are attaching calx to both keyup (when we are just going up/down with keyboard arrows), as well as webkit's "up/down" arrows on the form fields

$("input[type=number]").bind('keyup input', function(){
// works fine, without visible latency
$('#table_id').calx();
});

Not sure if it is a bug or it just hasn't been implemented yet but I was trying to use calx with number fields where you can use the up/down arrows but it doesn't seem to support them as whenever the number field lost focus it would reset the number field back to "0". I changed to standard "text" field and it worked fine.

Hi, is there a way I can print text in an IF statement result, eg if the statement is true, print "Success!" ? Similar to how Excel does this when you put text in single quotes within a formula.

Thanks

Hello guys,

I have a problem with Calx.
I create my own dynamic form. For more easly using, I write the product line (contains input fields for name, qty, price, tax) in TPL file and call it with AJAX when user click on "plus" button and insert it after last product row.

The first product row without problem - perfectly calculate price, qty, tax.
Problem start when I add new product row with AJAX. When I just click in some of input fields in the new row, I receive couple errors from Calx.

I check the data-cell identifiter of the old row and the new row - they are different.

is there any limitation to define data-cell ?

I tried Number_One ,NumberOne,Number,NUMBERONEall these case are not working.
OnlyNUMBERONE1is working.
Is this necessary to add a number with data-sell value ?

Please implement the COUNTIF function

https://exceljet.net/formula/count-cells-that-contain-specific-text

Example

A B C
1 2 "asdasd"
3 2 "asddsa"
3 2 "dddddd"

=COUNTIF(A:A, 3)
=> 2

=COUNTIF(B:B, 2)
=> 3

=COUNTIF(C:C, "asd*")
=> 2

I have a formula like (1 - (IND34 / IND33)) * 100
Currently if I don't input value for IND34 and IND33 the result return 100 => this is wrong.
It should be #DIV/0 or ERROR.
How to fix this issue?

Using the current version of calx with this very simple code:

var calc  = $('.table').calx();
var sheet = calc.calx('getSheet');
sheet.getCell('A4');
cell.calculate();

and I get this error cell.calculate is not a function
A4 is just a sum data-cell="A4" data-formula="SUM(A1:A3)" data-format="0[.]00"