CODEWITHSUNDEEP
phpexport-to-excel
Pamela
Pamela

Reputation: 255

Format text in Excel file via PHP

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?

Upvotes: 5

Views: 32415

Answers (6)

Peter Howe
Peter Howe

Reputation: 1403

PHPExcel provides you with the facilities to do this and more.

Edit: This project is now deprecated.

Upvotes: 3

raj
raj

Reputation: 9

<?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;
      ?>

Upvotes: 0

pitje
pitje

Reputation:

I've used php_writeexcel a few times, and liked it a lot! :)

Upvotes: 1

Cruachan
Cruachan

Reputation: 15971

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.

Upvotes: 0

Eric Caron
Eric Caron

Reputation: 6273

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.

Upvotes: 6

Bill Karwin
Bill Karwin

Reputation: 562230

I have written Excel spreadsheets from PHP 5 using the PEAR :: Package :: Spreadsheet_Excel_Writer classes. You can do a lot with this package.

Upvotes: 6

Related Questions