add up values in a column in excel?

[muppet77]

I am trying to find a loop type script that starts at a particular row in a particular column in an excel file and keeps adding values in each row until the last value is reached.

 

I have searched and am pretty new to loops. can anyone point me in the right direction please?

[chrisguk]

Maybe you should ask this question in MrExcel.com instead of a PHP Coding forum?

[muppet77]

thanks but i'd like to manipulate the data in php rather than in excel. 

[Jessica]

If you don't understand how loops work you will probably need to learn basic development before attempting this. How are you currently using PHP to read the .xls

[muppet77]

I am using excelreader to do this.

 

I have cracked how to grab a cell and allocate it a variable.

 

I would just need the loop to fit around it.

 

 

[muppet77]

ok, i borrowed and adapted some code

 

<?php
    // include class file
require_once 'excel_reader2.php';
    
    // This could be post or get data from a form if you need to make it look for something other than id 65.
    $search_var = 65;
    // initialize reader object
    
    // Use the construct to read the file FALSE means php will not get the cell format data such as font color etc..
    $excel = new Spreadsheet_Excel_Reader('testdata.xlsx', FALSE);
    
    // get total number of rows in spreadsheet
    $tot = $excel -> rowcount($sheet_index = 0);
    
    // don't really need this bit but it makes it easier to follow in big scripts
    $cells = $excel -> sheets[0]['cells'];
    // Start the loop on the first row 
    for ($row = 1; $row <= $tot; $row++)
    {
        // the number 1 in $cells[$row][1] relates to the colummn as this is a multi dimentional array.
        // You could make this a variable so you have more adaptable search options
        
          $total = $total + $cells[$row][1];
          
        }
        
    echo $total;
    ?>    

 

but it returns

 

The filename testdata.xlsx is not readable

 

it's a basic file with A1:A10 filled with 1 and B1:B10 filled with 2

 

Excel_reader2.php is

 

/**

* A class for reading Microsoft Excel (97/2003) Spreadsheets.

*

* Version 2.21

*

* Enhanced and maintained by Matt Kruse < http://mattkruse.com >

* Maintained at http://code.google.com/p/php-excel-reader/

*

* Format parsing and MUCH more contributed by:

*    Matt Roxburgh < http://www.roxburgh.me.uk >

*

* DOCUMENTATION

* =============

*  http://code.google.com/p/php-excel-reader/wiki/Documentation

*

* CHANGE LOG

* ==========

*  http://code.google.com/p/php-excel-reader/wiki/ChangeHistory

*

* DISCUSSION/SUPPORT

* ==================

*  http://groups.google.com/group/php-excel-reader-discuss/topics

[muppet77]

SOLVED!

 

I resaved the Excel file as Microsoft Excel 97-2003 Worksheet (.xls)

 

rather than

 

Microsoft Excel Worksheet (.xlsx)

 

and changed the extension in the code from .xlsx to .xls and it worked!

 

what a cracking php code!