Web Development Blog

In PHP, exporting data into an excel file (.csv) can be achieved very easily by sending a raw HTTP header. And having a formula in cells is no brainier either (and I only found that out after searching through google and not finding any solution – hehe).

The following is a rough example of how you do it. What I am trying to do here as an example is have an excel formula in Age column to calculate the person’s age automatically.

        header('Content-Type: application/csv');
        header('Content-Disposition: inline; filename="report.csv"');  

        // print column titles
        echo 'Name,Year born,Age' . "\n";  

        // a full numeric representation of a year, 4 digits; eg: 2007
        $year = date('Y'); 

        // $count starts from 2 because the first row is reserved for column titles
        $count = 2;  

	while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { 
 	    echo $row['name'] . ',';
            echo $row['year'] . ',';
            // formula = Current year - Bx(Year born)  B is the column position
            echo '=' . $year . '-B' . $count; 
            echo "\\n"; 
            // increment $count by 1 (same as $count = $count + 1)
            $count++; 
        }
        exit;

Technorati Tags: php, programming, csv, snippet

Found this article useful? Share it with your friends: