• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 686
  • Last Modified:

Excel calculation in php

Dear Experts,
I would like to know how you do faster calculation in php if you have to
do such as simple calculation ,column 4=coumnl1*column2*column3 and then do summation of column4 for 5000 rows.
In excel VBA, I will use rg.offset(0,0).FormulaR1C1="rc2*rc3*rc4", where rg as range from A1 to A5000
something like, it will copy the formula into every cell of 5000 rows in Excel,
and speed is really  fast to get the final answer.
How about we do it in php, we just save all column and rows cells into array
then loop all row cells and do basis calculation. Any other method available will
be fastest in php  ?

Please advise
Duncan
0
duncanb7
Asked:
duncanb7
7 Solutions
 
barry houdiniCommented:
Hello Duncan,

Sorry don't know php but if you are doing this in Excel it can be achieved with a single formula, i.e. using SUMPRODUCT

=SUMPRODUCT(B1:B5000,C1:C5000,D1:D5000)

regards, barry
0
 
duncanb7Author Commented:
I want to do it in php, in excel I am realy familar with
0
 
dmgroomCommented:
Duncan
where is the data coming from which you want to perform the calculations on?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
duncanb7Author Commented:
the I download it from other website in csv format
0
 
Ray PaseurCommented:
Please show us some of the test data, thanks.

You can probably use fgetcsv() to read the lines of CSV data into an array.  You can probably use array_sum() to add up the values in the array.  You can keep a running total with the math operator +=

For 5,000 lines of four columns each, optimization is not worth the effort.  That is a very tiny amount of data by modern standards.
0
 
duncanb7Author Commented:
Yes, it also use fgetcsv() function, but just want to know  beside using arrary, any other method could
be faster since I need to handle one cell by cells for all rows that is time consuming, RIght ?

Could you think if convert csv file into xml or mysql format and do php mysqy would be faster ?
0
 
Ray PaseurCommented:
No, a data conversion to a different format would be a non-value-added part of the exercise.  Please show us some of the test data, thanks.
0
 
duncanb7Author Commented:

Please download the attach data  file foror 5000*5 cells.

If you have faster method to calculate, E1=A1*B1*C1*D1 from 1 to 5000 by Php or others
Please let us know it i

Book2.xls
0
 
duncanb7Author Commented:
Please download attach for csv format
Book2.csv
0
 
Ray PaseurCommented:
I used a small subset of the data to create and test this proof-of-concept script.

The script is here:
http://www.laprbass.com/RAY_temp_duncanb7.php

The test data is here:
http://www.laprbass.com/RAY_temp_duncanb7.csv
<?php // RAY_temp_duncanb7.php
error_reporting(E_ALL);


// DEMONSTRATE HOW TO USE fgetcsv() TO READ A FILE


/* DATA LOOKS LIKE THIS:
1,2,102,1102,224808
2,3,103,1103,681654
3,4,104,1104,1377792
4,5,105,1105,2320500
5,6,106,1106,3517080
6,7,107,1107,4974858
7,8,108,1108,6701184
8,9,109,1109,8703432
9,10,110,1110,10989000
10,11,111,1111,13565310
11,12,112,1112,16439808
*/

// START WITH ZERO
$tot = 0;
$fp = fopen('RAY_temp_duncanb7.csv', 'r');
while (!feof($fp))
{
    // GET AN ARRAY
    $csv = fgetcsv($fp);

    // REMOVE THE RIGHTMOST COLUMN (E1=A1*B1*C1*D1)
    array_pop($csv);

    // MULTIPLY THE CONTENTS OF THE ARRAY
    $prd = array_product($csv);

    // SEE THE ARRAYS AND PRODUCTS
    echo PHP_EOL . '<br/>';
    var_dump($csv);
    echo PHP_EOL . '<br/>';
    var_dump($prd);

    // SUM OF PRODUCTS
    $tot += $prd;

    // SEE THE SUM
    echo PHP_EOL . '<br/>';
    var_dump($tot);
}

echo "<br/><br/>" . PHP_EOL;
echo "THE FINAL VALUE IS ";
echo number_format($tot);

Open in new window

0
 
duncanb7Author Commented:
it seems it is really good.
Do you think PHP can do everhting as VBA's rg.offset(0.0).FormulaR1C1 ?
If yes that will be prefect
0
 
Ray PaseurCommented:
Sorry, I do not know what this means:
rg.offset(0.0).FormulaR1C1
0
 
duncanb7Author Commented:
Do you know it in Excel VBA.
0
 
Ray PaseurCommented:
No.  Can you please explain it in plain language?  Thanks, ~Ray
0
 
duncanb7Author Commented:
Thanks for your reply
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now