Solved

# Excel calculation in php

Posted on 2011-05-06
666 Views
Last Modified: 2012-05-11
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
Question by:duncanb7
15 Comments

LVL 50

Accepted Solution

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

LVL 13

Author Comment

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

LVL 4

Assisted Solution

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

LVL 13

Author Comment

the I download it from other website in csv format
0

LVL 107

Assisted Solution

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

LVL 13

Author Comment

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

LVL 107

Assisted Solution

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

LVL 13

Author Comment

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

LVL 13

Author Comment

Please download attach for csv format
Book2.csv
0

LVL 107

Assisted Solution

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

LVL 13

Author Comment

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

LVL 107

Assisted Solution

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

LVL 13

Author Comment

Do you know it in Excel VBA.
0

LVL 107

Assisted Solution

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

LVL 13

Author Closing Comment

Thanks for your reply
0

## Join & Write a Comment Already a member? Login.

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

#### 754 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!