I'm loading data from my database, and exporting to an Excel file via a method I found on this site: http://www.appservnetwork.com/modules.php?name=News&file=article&sid=8
It works, but what I want to do now is format the text before it exports - change the font and text size. Does anybody have any ideas on how to do this?
I have written Excel spreadsheets from PHP 5 using the PEAR :: Package :: Spreadsheet_Excel_Writer classes. You can do a lot with this package.
Depending on the speed in which you wish to deploy your solution, one method is to just use the HTML table tag, store all your data in tables using style markup, and then use PHP header's option to force the browser to save is as a .xls file.
For proof of concept, copy this code into notepad, save as .xls, and then open with Excel:
<table>
<tr><th>Column 1</th><th>Column 2</th></tr>
<tr><td style="font-size:200%">Answer 1</td><td style="color:#f00">Answer 2</td></tr>
<tr><td colspan="2" style="font-weight:bold">Answer 3 with 2 columns</td></tr>
</table>
Its not the most elegant solution, but it will absolutely suit your needs.
PHPExcel provides you with the facilities to do this and more.
Edit: This project is now deprecated.
The TinyButStrong template system (www.tinybutstrong.com) has an optional plugin that allows you to write to excel templates.
I've used this a great deal. It's particularly neat in that you can set up the page to write to either HTML or Excel depending on a flag - so typically my reports offer 'display on page' and 'write to excel' options.
<?php
include '../include/config.php';
$sql = "SELECT SQL_CALC_FOUND_ROWS
".DB_TBL_TUTORS_RECAPS.".id,
".DB_TBL_TUTORS_RECAPS.".date_of_tutoring_session,
".DB_TBL_TUTORS_RECAPS.".created,
".DB_TBL_TUTORS_RECAPS.".hours_tutored,
".DB_TBL_TUTORS_RECAPS.".billable_travel,
".DB_TBL_TUTORS_RECAPS.".billable_additional,
".DB_TBL_TUTORS_RECAPS.".type_of_tutoring,
".DB_TBL_TUTORS_RECAPS.".additional_comments,
".DB_TBL_TUTORS_RECAPS.".total_billable,
".DB_TBL_TUTORS_RECAPS.".materials_covered_during_session,
".DB_TBL_TUTORS_RECAPS.".next_scheduled_session_location,
".DB_TBL_TUTORS_RECAPS.".rate,
".DB_TBL_TUTORS_RECAPS.".tutor_pay_rate,
".DB_TBL_APPLICANTS.".first_name as tutor_first_name,
".DB_TBL_APPLICANTS.".last_name as tutor_last_name,
".DB_TBL_PIPELINE.".tutor_match_notes,
".DB_TBL_PIPELINE.".date_of_submission,
".DB_TBL_PIPELINE.".tutor_name,
".DB_TBL_PIPELINE.".tutor_id,
".DB_TBL_CLIENTS.".id as client_id,
".DB_TBL_CLIENTS.".first_name,
".DB_TBL_CLIENTS.".last_name,
".DB_TBL_CLIENTS.".location_name,
".DB_TBL_CLIENTS.".last_name,
".DB_TBL_CHILDREN.".id as child_id,
".DB_TBL_CHILDREN.".last_name as last,
".DB_TBL_CHILDREN.".first_name as first
FROM
".DB_TBL_TUTORS_RECAPS."
LEFT JOIN ".DB_TBL_PIPELINE." ON ".DB_TBL_PIPELINE.".id= ".DB_TBL_TUTORS_RECAPS.".pipeline_id
LEFT JOIN ".DB_TBL_CHILDREN." ON ".DB_TBL_CHILDREN.".id= ".DB_TBL_TUTORS_RECAPS.".child_id
LEFT JOIN ".DB_TBL_CLIENTS." ON ".DB_TBL_CLIENTS.".id= ".DB_TBL_TUTORS_RECAPS.".client_id
LEFT JOIN ".DB_TBL_TUTORS." ON ".DB_TBL_TUTORS_RECAPS.".tutor_id= ".DB_TBL_TUTORS.".id
LEFT JOIN ".DB_TBL_APPLICANTS." ON ".DB_TBL_APPLICANTS.".id= ".DB_TBL_TUTORS.".applicant_id
WHERE
" . DB_TBL_CLIENTS . ".status = 'Existing' AND
" . DB_TBL_APPLICANTS . ".status = 'Existing' AND
" . DB_TBL_PIPELINE . ".status = 'Existing' AND
" . DB_TBL_TUTORS_RECAPS . ".is_deleted = '0'
GROUP BY " . DB_TBL_TUTORS_RECAPS . ".id
ORDER BY " . DB_TBL_TUTORS_RECAPS . ".created DESC ";
$totallogs = $db->query($sql);
$filename = "Tutoring_Log.xls";
$contents = "Recap# \t Tutor Name \t Client Name \t Child Name \t Type of tutoring \t Date of Tutoring session \t Hours tutored \t Billable Travel \t Billable Additional \t Total Billable \t Client Rate \t Tutor Pay Rate \t \n";
$contents .= " \n";
while($tutorRecords = $db->fetchNextObject($totallogs)){
$contents .= "".$tutorRecords->id." \t ".$tutorRecords->tutor_first_name.' '.$tutorRecords->tutor_last_name." \t ".$tutorRecords->first_name.' '.$tutorRecords->last_name." \t ".$tutorRecords->first.' '.$tutorRecords->last." \t ".$globalsConstant['type_of_tutoring'][$tutorRecords->type_of_tutoring]." \t ".date(MDY,$tutorRecords->date_of_tutoring_session)." \t ".str_replace('.',':',$tutorRecords->hours_tutored)." \t ".str_replace('.',':',$tutorRecords->billable_travel)." \t ".str_replace('.',':',$tutorRecords->billable_additional)." \t ".str_replace('.',':',$tutorRecords->total_billable)." \t ".CURRENCY.$tutorRecords->rate." \t ".CURRENCY.$tutorRecords->tutor_pay_rate." \t \n";
}
header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
echo $contents;
?>
