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
LVL 13
duncanb7Asked:
Who is Participating?
 
barry houdiniConnect With a Mentor 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
 
duncanb7Author Commented:
I want to do it in php, in excel I am realy familar with
0
 
dmgroomConnect With a Mentor Commented:
Duncan
where is the data coming from which you want to perform the calculations on?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
duncanb7Author Commented:
the I download it from other website in csv format
0
 
Ray PaseurConnect With a Mentor 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
 
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 PaseurConnect With a Mentor 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
 
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 PaseurConnect With a Mentor 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
*/

// 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 PaseurConnect With a Mentor Commented:
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 PaseurConnect With a Mentor Commented:
No.  Can you please explain it in plain language?  Thanks, ~Ray
0
 
duncanb7Author Commented:
Thanks for your reply
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.