table columns to excel spreadsheet column header

[phpdragon]

I generate an excel spreadsheet dynamically from a php/html table layout and a mysql database.

The first 5 columns are fixed and then there is a dynamic number of columns after depending on how many categories are relevant to all the results with in the search parameters.

The first 4 rows are fixed for headings and display info and then the rest are dynamic based on the number of results from the search parameters.

My problem is I need to total the amounts in each column in an excel format which means I need to know which column I am in, while that is easy for the first 5 columns, the rest are dynamic so I need to know what column I am in to print the right formula.

eg column a last row formula is =SUM(A5:A<?php echo $count; ?>) and column b is =SUM(B5:B<?php echo $count; ?> etc etc

I use a while loop to print out the dynamic <td> cells for each row

Would really apreciate ideas on how to get the column headers

[Mark Baker]

How are you generating the Excel spreadsheet?

[phpdragon]

The excel spreadsheet is generated with the following header tags

 

header("Content-Type: application/vnd.ms-excel");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("content-disposition: attachment;filename=newexcelreport.xls");

 

Then I generate the table with php and mysql, that all works fine and I can get totals etc

 

I am thinking that I need some sort of auto increment on letters starting at F and going thru to ZZ to happen on each occurance of the while loop, then I could just apply auto incremented letter to each dynamically generated table cells formula. Not quite sure how to increment the letters like that tho

[phpdragon]

here is the loop I am working on, it sort of works but I havent got it quite right yet as it priints many extra blank columns to the total number of possibilities in the range, I only want as many as I have results for I have a additional column count i can use which is the number of dynamic columns added

 <?php 
$catbot="SELECT categories.category AS name, categories.categoryID AS catID, receipts.category AS checkID, receipts.transDate AS trans, receipts.companyID AS compID FROM categories LEFT JOIN receipts ON categories.categoryID=receipts.category WHERE receipts.category=categories.categoryID AND receipts.companyID='$bizID' AND receipts.transDate BETWEEN '$startDate' AND '$endDate' GROUP BY categories.category ORDER BY categories.category";
$newbot=mysql_query($catbot);
while ($newtt=mysql_fetch_array($newbot)) {
$namebot=$newtb['name'];
$catIDbot=$newtb['catID'];
for ($i='F';$i<'IV' {$i++;}
echo "<td bgcolor='#FF9999' width='150'><strong>=SUM(".$i."5:".$i.$restot.")</strong></td>";
}
?>