Start Building Professional
Web Apps Today


 
Categories Question details Back To List
Question  posted by John on Sep 16, 2008 11:16
open dhtmlx forum
math calculation

I used Math formulas with Script to calculate the product of two columns (price *quantity)
---- mygrid.setColTypes("ed,ed,ed,ed[=c1*c2]");

Also, I make a subtotal to sum the value of three rows by using math calculation in xml file.The coding is below:
when i input new values in price column and quantity column, the subtotal will be updated.However,there is no change in "amount "column where I set Math formulas here. why the product of column 1 value and column 2 value is not changed?

THANK A LOT


<?xml version="1.0" encoding="UTF-8" ?>
<rows>
<row id="r1">
<cell>100%</cell>
<cell>1.0</cell>
<cell>2.00</cell>
<cell>2.00</cell>
<cell></cell>
</row>

<row id="r2">
<cell>100%</cell>
<cell>3</cell>
<cell>4.00</cell>
<cell>2.00</cell>
<cell></cell>
</row>

<row id="r3">
<cell>100%</cell>
<cell>1</cell>
<cell>0.5</cell>
<cell>2</cell>
<cell></cell>
</row>

<row class="sub_total" id="sub1">
<cell>subtotal</cell>
<cell type="math">=[[r1,1]]+[[r2,1]]+[[r3,1]]</cell>
<cell type="math">=[[r1,2]]+[[r2,2]]+[[r3,2]]</cell>
<cell type="math">=[[r1,3]]+[[r2,3]]+[[r3,3]]</cell>

</row>
</rows>





--------------------------------------------------------------------------------------------------------------------

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
    <title>For demo purpose only :: &1</title>
    <link rel='STYLESHEET' type='text/css' href='codebase/dhtmlxgrid.css'>
</head>

<body>
    
    <h1>Math calculations</h1>
    <p>
Calculate values with Math formulas. Edit formulas on the fly
    </p>
    
    <script src='codebase/dhtmlxcommon.js'></script>
    <script src='codebase/dhtmlxgrid.js'></script>        
    <script src='codebase/dhtmlxgridcell.js'></script>
    <script src='codebase/dhtmlxgrid_math.js'></script>
    
<div id="gridbox" width="535px" height="250px" style="background-color:white;"></div>
            
<br>


<script>
mygrid = new dhtmlXGridObject('gridbox');


mygrid.setHeader("Percent,price,Quantity,Amount");

mygrid.setInitWidths("50,100,100,100");
mygrid.setColAlign("right,left,right,right");
    
    
mygrid.setSizes();


//5th column is math
mygrid.setColTypes("ed,ed,ed,ed[=c1*c2]");


//round all calculation to 2 digits after point


mygrid.init();

    mygrid.loadXML("get.xml");
</script>

</body>
</html>
Answer posted by dhtmlx support on Sep 18, 2008 03:34
XML with data shouldn't contain values for cells which should be calculated. In other words your XML should be as follows (it seems you just forgot to delete them, as you have more cell tags per row than expected for some reason):


<?xml version="1.0" encoding="UTF-8" ?>
<rows>
<row id="r1">
<cell>100%</cell>
<cell>1.0</cell>
<cell>2.00</cell>
<cell></cell>
<cell></cell>
</row>

<row id="r2">
<cell>100%</cell>
<cell>3</cell>
<cell>4.00</cell>
<cell></cell>
<cell></cell>
</row>

<row id="r3">
<cell>100%</cell>
<cell>1</cell>
<cell>0.5</cell>
<cell></cell>
<cell></cell>
</row>

<row class="sub_total" id="sub1">
<cell>subtotal</cell>
<cell type="math">=[[r1,1]]+[[r2,1]]+[[r3,1]]</cell>
<cell type="math">=[[r1,2]]+[[r2,2]]+[[r3,2]]</cell>
<cell type="math">=[[r1,3]]+[[r2,3]]+[[r3,3]]</cell>

</row>
</rows>
Answer posted by John on Sep 18, 2008 04:09

Our company bought one  license of professional version of your product. When we are using your product,in our development project, we face some technical problems about math calculation.

We make one subtotal to sum the value of each row   and  the product of two columns (Price column* Quantity column=Amount column)   respectively using dhtmlgrid 's math formula .When enter some values quantity column or price column, it is expected the values in Total and Amount column will be updated immediately. However ,amount values are NOT UPDATED .Our development project is in urgent production phase. Please answer our inquries as fast as you can. Thank a lot.  The following sample.zip  file is for your reference .

Attachments (1)
samples.zip163.93 Kb
Answer posted by john on Sep 18, 2008 08:17

I get another grid calculation problems

I used math formula in grid to calculate the sum of rows' values. First math formula calculate the sum of first three rows. Second formula calculate the sum of first three rows. it is expected the value of both sum will be updated when I enter some value into frist three rows. However first sum doesn't work and it cannot calculate the new sum. 

I want to know whether the grid can support two or more calculations involved same cells and Is there any method to do two or more calculation involved same cells .My coding is below. Thank a lot .

 

 

<?xml version="1.0" encoding="UTF-8" ?>
<rows>
<row id="r1">
<cell>100%</cell>
<cell>1.0</cell>
<cell>2.00</cell>
<cell></cell>
</row>
<row id="r2">
<cell>100%</cell>
<cell>3</cell>
<cell>4.00</cell>
<cell></cell>
</row>
<row id="r3">
<cell>100%</cell>
<cell>1</cell>
<cell>0.5</cell>
<cell></cell>
</row>
<row class="sub_total2" id="sub1">
<cell>subtotal</cell>
<cell type="math">=[[r1,1]]+[[r2,1]]+[[r3,1]]</cell>
<cell type="math">=[[r1,2]]+[[r2,2]]+[[r3,2]]</cell>
<cell type="math">=[[r1,3]]+[[r2,3]]+[[r3,3]]</cell>

</row>
<row id="r4">
<cell>100%</cell>
<cell>1</cell>
<cell>0.5</cell>
<cell></cell>
</row>
<row id="r5">
<cell>100%</cell>
<cell>1</cell>
<cell>0.5</cell>
<cell></cell>
</row>

<row class="sub_total" id="sub1">
<cell>subtotal</cell>
<cell type="math">=[[r1,1]]+[[r2,1]]+[[r3,1]]+[[r4,1]]+[[r5,1]]</cell>
<cell type="math">=[[r1,2]]+[[r2,2]]+[[r3,2]]+[[r4,2]]+[[r5,2]]</cell>
<cell type="math">=[[r1,3]]+[[r2,3]]+[[r3,3]]+[[r4,3]]+[[r5,3]]</cell>

</row>

</rows>

 

Attachments (1)
sample2.rar149.69 Kb
Answer posted by Support on Sep 19, 2008 09:13
Yes, the current grid version allows only one math operation for a certain cell.

We have created a sample which demonstrates how all operations can be run when a cell is edited (in case of your xml structure). 
Attachments (1)
sample.zip1.47 Kb
Answer posted by John on Sep 19, 2008 09:37

Thank you for your fast reply.I doubt whether it can work or not when I add more subtotal (math formula) or I added more calculation about the product of two columns (ed[=c1*c2])

For example I add more columns. if i set  (ed[=c5*c6]) in column 7 , can it work or not? Because we need to do a lot of calculation on product of two column. thank a lot.

 

 

Answer posted by John on Sep 19, 2008 23:50
First ,thank you for your fast answer. However, the problem cannot be solved completely.
When I add more rows or/and columns in xml files and grids, the calculation problem come out again.
 
First problem come out  when I enter some new value in  ' price1' column and/or '  quantity1' column.
Although the subtotal of 'price 1'column  and 'quantity1 'columns can be updated in real time ,Subtotal of Amount1 column (product of  ' price1' column and'  quantity1' column) cannot be updated.
 
Second problem come out when add more rows or/and columns in xml files and grids.
 
when i enter some value in' price2' column and'  quantity2' column, 'Amount2' column (product of  ' price2' column and'  quantity2' column) and  'Amount2' column's subtotal cannot be updated. But I have already set mygrid.setColTypes('ed,ed,ed,ed[=c1*c2],ed,ed,ed[=c4*c5]');  in grid script.
 
 
Attached sample for your reference. Thank a lot.
 
Attachments (1)
sample_Updated.rar151.23 Kb
Answer posted by John on Sep 20, 2008 00:08
attached sample for your reference
Attachments (1)
sample_Updated.rar151.23 Kb
Answer posted by john on Sep 22, 2008 03:57

I have new question on mygrid.attachFooter. I find the total of "amount " column cannot be updated when i enter some value into price and quantity column.
My coding is attached.Thank a lot.


<script type="text/javascript" src="codebase/dhtmlxgrid_filter.js"></script>
 
mygrid.attachFooter("Total,{#stat_total},{#stat_total},{#stat_total},{#stat_total},{#stat_total},{#stat_total} ") ;

 

Attachments (1)
sample_Updated.zip172.57 Kb
Answer posted by john on Sep 22, 2008 23:42

Dear Support Team ,

I have following questions.Thanks

1.) How can I export grid to excel?????

2.)is there any method to differ subtotal cell from other cells and will count it while summating in my sample?????

3.)I used mygrid.attachEvent method to call two fuctions respectively in doAfterXMLLoading().calculateFooterValues() is for counting the total of each column and function(stage,id,ind) is for calculating the subtotal.

 


when I edit some cells, the total of "amount" field  only updated on second editing of cells.How can I update the total of "amount field" each editing rather than second editing??????Please refer to the attachment.Thanks

 

 

function doAfterXMLLoading(){

  /*gets all sub_total rows*/
  mygrid.forEachRow(getSub)
  
  /*this event is called when cell is edited*/
  //mygrid.attachEvent("onEditCell",calculateFooterValues);
  mygrid.attachEvent("onEditCell",function(stage,id,ind){
  
   if(stage == 2){
   var cell = mygrid.cells(id,3).cell;
                         var cell2 = mygrid.cells(id,6).cell;
   
    mygrid.cells(id,3).setValue(mygrid._calcSCL(cell));
                                 mygrid.cells(id,6).setValue(mygrid._calcSCL(cell2));
                          
   
    for(var i = 0; i < arr.length; i++){
     var cell = mygrid.cells(arr[i],ind).cell;
                                 
     mygrid.cells(arr[i],ind).setValue(mygrid._calcSCL(cell));
    
                                      }

   

                           for(var i = 0; i <= arr.length; i++){
     var cell2 = mygrid.cells(arr[i],ind+1).cell;
                                 
     mygrid.cells(arr[i],ind+1).setValue(mygrid._calcSCL(cell2));
    
                                      }

             
                                 
                                 

 

   }

 


mygrid.attachEvent("onEdit",calculateFooterValues);

   return true
  } )
  
}
 

 

 


function getSub(id){
 var class_name = mygrid.getRowAttribute(id,"class")
 if(class_name&&(class_name.indexOf("sub_total")!=-1))
  arr[arr.length] = id;
}

Attachments (1)
sample_last_updated2.rar152.40 Kb
Answer posted on Sep 23, 2008 02:39

Your sample throughs some error so I couldn't get it working. But the problem is in the way you set event handlers. I would not use attachEvent for calculateFooterValues, instead just call it at the end of function you set on onEditCell event while grid initialization.

As to your other questions:

1. you can save grid content as CSV (see export/import to CSV). Excel can open CSV files

2. As you probably see in email support response - you can try using treegrid and keep these values on different levels. Or you can use grid nd keep these valus in different columns

Also, please use single way of getting support - either KB or email. In other cases different people spend time with same questions from you. Thank you.

Answer posted by dhtmlx support on Sep 23, 2008 05:26
>> Your sample throughs some error so I couldn't get it working.

There is an issue in the code. Please, try to use:
 
for(var i = 0; i <arr.length; i++){
     var cell2 = mygrid.cells(arr[i],ind+1).cell;
     mygrid.cells(arr[i],ind+1).setValue(mygrid._calcSCL(cell2));
}

instead of

for(var i = 0; i <=arr.length; i++){
     var cell2 = mygrid.cells(arr[i],ind+1).cell;
     mygrid.cells(arr[i],ind+1).setValue(mygrid._calcSCL(cell2));
}

But probably this code is not necessary.

>> you can save grid content as CSV (see export/import to CSV). Excel can open CSV file

Grid is a client-side (JavaScript) component. So it doesn't provide an opportunity to export data directly to Excel.
 
But grid's API allows to serialize grid to the csv string:
 
    var csv_str = mygrid.serializeToCSV();
 
And this string can be passed to some server-side program.
 
Please, see sample of csv serialization in the documentation dhtmlxGrid/samples/clipboard/pro_clipboard.html

>> Is there any method to differ subtotal cell from other cells and will count it while summating in my sample????

You can use the function from the provided sample ( getSub ). It allows to get the row ids of subtotal cells.

And  in this case  sumColumn can be as follows:

function sumColumn(ind){
    var out = 0;
    for(var i=0;i<arr.length;i++){
            out+= parseFloat(mygrid.cells(arr[i],ind).getValue())

    }
    return out;
}
Where arr is got in getSub() function. Please, see the sample provided before.

Answer posted by John on Sep 24, 2008 02:05

I used the following coding to calculate the total of all the rows. But I want to skip some subtotal rows. How can I skip some row of subtotal in function sumColumn() ??? i.e. <row class="sub_total2" id="sub1">   Thanks.


   

 

 

 function sumColumn(ind){
        var out = 0;
       
   
    
    for(var i=0;i<mygrid.getRowsNum();i++){
       

      //  var value=mygrid.cells2(i,ind).getValue()
     
    
       
  if (!isNaN(value)&&!value==""){
     
 
 
    out+= parseFloat(mygrid.cells2(i,ind).getValue())
           
           
        
           
   }
     }
  
  
  
  
  
  
        return out;
    }

 

 

 

xml

------------------------------------------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8" ?>
<rows>
<row id="r1">
<cell>100%</cell>
<cell>1.0</cell>
<cell>2.00</cell>
<cell></cell>
<cell>1.0</cell>
<cell>2.00</cell>
<cell></cell>
</row>
<row id="r2">
<cell>100%</cell>
<cell>3</cell>
<cell>4.00</cell>
<cell></cell>
<cell>3</cell>
<cell>4.00</cell>
<cell></cell>
</row>
<row id="r3">
<cell>100%</cell>
<cell>1</cell>
<cell>0.5</cell>
<cell></cell>
<cell>1</cell>
<cell>0.5</cell>
<cell></cell>
</row>
<row class="sub_total2" id="sub1">
<cell>subtotal</cell>
<cell type="math">=[[r1,1]]+[[r2,1]]+[[r3,1]]</cell>
<cell type="math">=[[r1,2]]+[[r2,2]]+[[r3,2]]</cell>
<cell type="math">=[[r1,3]]+[[r2,3]]+[[r3,3]]</cell>
<cell type="math">=[[r1,4]]+[[r2,4]]+[[r3,4]]</cell>
<cell type="math">=[[r1,5]]+[[r2,5]]+[[r3,5]]</cell>
<cell type="math">=[[r1,6]]+[[r2,6]]+[[r3,6]]</cell>
</row>

Answer posted by dhtmlx support on Sep 24, 2008 07:14

In this case sumColumn can be as follows:


function sumColumn(ind){
    var out = 0;
    mygrid.forEachRow(function(id){   
        var cl = mygrid.getRowAttribute(id,"class");
        if(cl&&cl.indexOf("subtotal2")!=-1){
            var value = mygrid.cells(id,ind);
            if (!isNaN(value)&&!value=="")
                out+= parseFloat(value);
        }
     }
    return out;
}
Answer posted by john on Sep 24, 2008 09:23

 

thank for your answer.I have questions about export grid into CSV . can it work if i just copy following coding to my html file????Thx

 mygrid.setCSVDelimiter(",");//chnage csv delimiter
    var csvNew = mygrid.serializeToCSV();//serialize to CSV with tab as delimiter

 

 

Answer posted by dhtmlx support on Sep 26, 2008 03:26
This approach allows to serialize grid into csv string. But dhtmlxgrid_nxml.js should be included.