# 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  ?

Duncan
LVL 13
###### Who is Participating?

Commented:
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

Author Commented:
I want to do it in php, in excel I am realy familar with
0

Commented:
Duncan
where is the data coming from which you want to perform the calculations on?
0

Author Commented:
the I download it from other website in csv format
0

Commented:
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

Author 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

Commented:
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

Author Commented:

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

Author Commented:
Book2.csv
0

Commented:
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
*/

\$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);
``````
0

Author 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

Commented:
Sorry, I do not know what this means:
rg.offset(0.0).FormulaR1C1
0

Author Commented:
Do you know it in Excel VBA.
0

Commented:
No.  Can you please explain it in plain language?  Thanks, ~Ray
0

Author Commented: