Skip to main content

Generating Excel reports using PHP

Hey,

Everybody is very keen to make use of the functionality that we have in php.

A end product of every web application is reports..without reports its like a waste to have all the functionalities.

One most important thing I need to highlight here is if anyone is thinking to extract report out of php then the first step you need to follow is using the

BIFF WRITER , this is a excellent set of files used to extract reports in php, just modify your bit and you can take out the report with this hep files very easily.

You can modify the headers that you need to send to the excel and then you can send all the data to the excel.

While you are creating a new php page for the report generation.

DO NOT FORGET TO MENTION or INCLUDE THE BELOW FILES FROM BIFF WRITER


require_once('Worksheet.php');
require_once('Workbook.php');


function HeaderingExcel($filename)
{
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$filename" );
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
header("Pragma: public");

/*header("Content-disposition: attachment; filename=Report.xls");
header("Content-type: application/vnd.ms-excel");
readfile(dirname(__FILE__) . "/Report.xls");*/
}


With the following piece of code you can modify the excel header



// HTTP headers
HeaderingExcel('Master.xls');

// Creating a workbook
$workbook = new Workbook("-");
// 0x0B for green
// 0x28 for blue
// 0x02 for red
// 0x37 for bottom

$formatot1 =& $workbook->add_format();
$formatot1->set_size(9);
$formatot1->set_align('left');
$formatot1->set_align('top');
$formatot1->set_color('black');
$formatot1->set_pattern();
$formatot1->set_fg_color(0x09);
$formatot1->set_border(1);
$formatot1->set_bold(1);
$formatot1->set_underline(0);
$formatot1->set_text_wrap(0);
$formatot1->set_italic();

$formatot2 =& $workbook->add_format();
$formatot2->set_size(9);
$formatot2->set_align('left');
$formatot2->set_align('top');
$formatot2->set_color('black');
$formatot2->set_pattern();
$formatot2->set_fg_color(0x09);
$formatot2->set_border(1);
$formatot2->set_bold(0);
$formatot2->set_underline(0);
$formatot2->set_text_wrap(0);
$formatot2->set_italic();

$formatotgreen =& $workbook->add_format();
$formatotgreen->set_size(9);
$formatotgreen->set_align('center');
$formatotgreen->set_align('top');
$formatotgreen->set_color('black');
$formatotgreen->set_pattern();
$formatotgreen->set_fg_color(0x0B);
$formatotgreen->set_border(1);
$formatotgreen->set_bold(1);
$formatotgreen->set_underline(0);
$formatotgreen->set_text_wrap(1);
$formatotgreen->set_italic();

$formatotorange =& $workbook->add_format();
$formatotorange->set_size(9);
$formatotorange->set_align('center');
$formatotorange->set_align('top');
$formatotorange->set_color('black');
$formatotorange->set_pattern();
$formatotorange->set_fg_color(0x33);
$formatotorange->set_border(1);
$formatotorange->set_bold(1);
$formatotorange->set_underline(0);
$formatotorange->set_text_wrap(1);
$formatotorange->set_italic();

$formatotred =& $workbook->add_format();
$formatotred->set_size(9);
$formatotred->set_align('center');
$formatotred->set_align('top');
$formatotred->set_color('black');
$formatotred->set_pattern();
$formatotred->set_fg_color(0x02);
$formatotred->set_border(1);
$formatotred->set_bold(1);
$formatotred->set_underline(0);
$formatotred->set_text_wrap(1);
$formatotred->set_italic();

$formatotblue =& $workbook->add_format();
$formatotblue->set_size(9);
$formatotblue->set_align('center');
$formatotblue->set_align('top');
$formatotblue->set_color('black');
$formatotblue->set_pattern();
$formatotblue->set_fg_color(0x28);
$formatotblue->set_border(1);
$formatotblue->set_bold(1);
$formatotblue->set_underline(0);
$formatotblue->set_text_wrap(1);
$formatotblue->set_italic();

$formatotother =& $workbook->add_format();
$formatotother->set_size(9);
$formatotother->set_align('center');
$formatotother->set_align('top');
$formatotother->set_color('black');
$formatotother->set_pattern();
$formatotother->set_fg_color(0x09);
$formatotother->set_border(1);
$formatotother->set_bold(1);
$formatotother->set_underline(0);
$formatotother->set_text_wrap(1);
$formatotother->set_italic();

$formatotsno =& $workbook->add_format();
$formatotsno->set_size(9);
$formatotsno->set_align('center');
$formatotsno->set_align('top');
$formatotsno->set_color('black');
$formatotsno->set_pattern();
$formatotsno->set_fg_color(0x1A);
$formatotsno->set_border(1);
$formatotsno->set_bold(0);
$formatotsno->set_underline(0);
$formatotsno->set_text_wrap(1);

$formatotgreentext =& $workbook->add_format();
$formatotgreentext->set_size(9);
$formatotgreentext->set_align('left');
$formatotgreentext->set_align('top');
$formatotgreentext->set_color('black');
$formatotgreentext->set_pattern();
$formatotgreentext->set_fg_color(0x0B);
$formatotgreentext->set_border(1);
$formatotgreentext->set_bold(0);
$formatotgreentext->set_underline(0);
$formatotgreentext->set_text_wrap(1);

$formatotorangetext =& $workbook->add_format();
$formatotorangetext->set_size(9);
$formatotorangetext->set_align('left');
$formatotorangetext->set_align('top');
$formatotorangetext->set_color('black');
$formatotorangetext->set_pattern();
$formatotorangetext->set_fg_color(0x33);
$formatotorangetext->set_border(1);
$formatotorangetext->set_bold(0);
$formatotorangetext->set_underline(0);
$formatotorangetext->set_text_wrap(1);

$formatotredtext =& $workbook->add_format();
$formatotredtext->set_size(9);
$formatotredtext->set_align('left');
$formatotredtext->set_align('top');
$formatotredtext->set_color('black');
$formatotredtext->set_pattern();
$formatotredtext->set_fg_color(0x02);
$formatotredtext->set_border(1);
$formatotredtext->set_bold(0);
$formatotredtext->set_underline(0);
$formatotredtext->set_text_wrap(1);

$formatotbluetext =& $workbook->add_format();
$formatotbluetext->set_size(9);
$formatotbluetext->set_align('left');
$formatotbluetext->set_align('top');
$formatotbluetext->set_color('black');
$formatotbluetext->set_pattern();
$formatotbluetext->set_fg_color(0x28);
$formatotbluetext->set_border(1);
$formatotbluetext->set_bold(0);
$formatotbluetext->set_underline(0);
$formatotbluetext->set_text_wrap(1);

$formatotothertext =& $workbook->add_format();
$formatotothertext->set_size(9);
$formatotothertext->set_align('left');
$formatotothertext->set_align('top');
$formatotothertext->set_color('black');
$formatotothertext->set_pattern();
$formatotothertext->set_fg_color(0x09);
$formatotothertext->set_border(1);
$formatotothertext->set_bold(0);
$formatotothertext->set_underline(0);
$formatotothertext->set_text_wrap(1);

$formatotcounttext =& $workbook->add_format();
$formatotcounttext->set_size(9);
$formatotcounttext->set_align('center');
$formatotcounttext->set_align('top');
$formatotcounttext->set_color('black');
$formatotcounttext->set_pattern();
$formatotcounttext->set_fg_color(0x37);
$formatotcounttext->set_border(1);
$formatotcounttext->set_bold(0);
$formatotcounttext->set_underline(0);
$formatotcounttext->set_text_wrap(1);

$formatotorangecounttext =& $workbook->add_format();
$formatotorangecounttext->set_size(9);
$formatotorangecounttext->set_align('center');
$formatotorangecounttext->set_align('top');
$formatotorangecounttext->set_color('black');
$formatotorangecounttext->set_pattern();
$formatotorangecounttext->set_fg_color(0x33);
$formatotorangecounttext->set_border(1);
$formatotorangecounttext->set_bold(0);
$formatotorangecounttext->set_underline(0);
$formatotorangecounttext->set_text_wrap(1);

$formatotbluecounttext =& $workbook->add_format();
$formatotbluecounttext->set_size(9);
$formatotbluecounttext->set_align('center');
$formatotbluecounttext->set_align('top');
$formatotbluecounttext->set_color('black');
$formatotbluecounttext->set_pattern();
$formatotbluecounttext->set_fg_color(0x28);
$formatotbluecounttext->set_border(1);
$formatotbluecounttext->set_bold(0);
$formatotbluecounttext->set_underline(0);
$formatotbluecounttext->set_text_wrap(1);

$formatotcrosstext =& $workbook->add_format();
$formatotcrosstext->set_size(9);
$formatotcrosstext->set_align('center');
$formatotcrosstext->set_align('top');
$formatotcrosstext->set_color('black');
$formatotcrosstext->set_pattern();
$formatotcrosstext->set_fg_color(0x09);
$formatotcrosstext->set_border(1);
$formatotcrosstext->set_bold(0);
$formatotcrosstext->set_underline(0);
$formatotcrosstext->set_text_wrap(1);

$formatoth1 =& $workbook->add_format();
$formatoth1->set_size(9);
$formatoth1->set_align('center');
$formatoth1->set_align('top');
$formatoth1->set_color('black');
$formatoth1->set_pattern();
$formatoth1->set_fg_color(0x33);
$formatoth1->set_border(1);
$formatoth1->set_bold(1);
$formatoth1->set_underline(0);
$formatoth1->set_text_wrap(1);

$formatoth2 =& $workbook->add_format();
$formatoth2->set_size(9);
$formatoth2->set_align('center');
$formatoth2->set_align('top');
$formatoth2->set_color('black');
$formatoth2->set_pattern();
$formatoth2->set_fg_color(0x0D);
$formatoth2->set_border(1);
$formatoth2->set_bold(0);
$formatoth2->set_underline(0);
$formatoth2->set_text_wrap(1);

$formatoth3 =& $workbook->add_format();
$formatoth3->set_size(9);
$formatoth3->set_align('center');
$formatoth3->set_align('top');
$formatoth3->set_color('black');
$formatoth3->set_pattern();
$formatoth3->set_fg_color(0x0D);
$formatoth3->set_border(1);
$formatoth3->set_bold(0);
$formatoth3->set_underline(0);
$formatoth3->set_text_wrap(1);
$formatoth3->rotation=90;

$formatoth4 =& $workbook->add_format();
$formatoth4->set_size(9);
$formatoth4->set_align('center');
$formatoth4->set_align('top');
$formatoth4->set_color('black');
$formatoth4->set_pattern();
$formatoth4->set_fg_color(0x33);
$formatoth4->set_border(1);
$formatoth4->set_bold(0);
$formatoth4->set_underline(0);
$formatoth4->set_text_wrap(1);
$formatoth4->rotation=90;

$formatoth5 =& $workbook->add_format();
$formatoth5->set_size(9);
$formatoth5->set_align('center');
$formatoth5->set_align('top');
$formatoth5->set_color('black');
$formatoth5->set_pattern();
$formatoth5->set_fg_color(0x16);
$formatoth5->set_border(1);
$formatoth5->set_bold(0);
$formatoth5->set_underline(0);
$formatoth5->set_text_wrap(1);
$formatoth5->rotation=90;



$worksheet1 =& $workbook->add_worksheet("Master");

$myArrWidth=array(17.25,18.13,13,14,14,14,14,14,14,14,14,14,14,14,21.13,25.5,16,16,38,14,14,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,11,27,30,22,16,16,16,16,66,20.5,42.38,8,25,16.38,16.38,16.38,16.38,16.38);

$startcolumn=0;
$startrow=10;

$myArr=array(Write the names that you need to provide in the header cell)

------------PUT YOUR CONDITIONS AND THEN CALL THE DATA-------------



$worksheet1->write_string($rno, 11, $myArrVal[11],$formatotsno);
$worksheet1->write_string($rno, 19, $myArrVal[19],$formatotsno);
$worksheet1->write_string($rno, 20, $myArrVal[20],$formatotsno);

if($myArrVal[51] != "0")
$worksheet1->write_string($rno, 42, $myArrVal[51],$formatotsno);
else
$worksheet1->write_string($rno, 42, "",$formatotsno);

if($myArrVal[52] != "0")
$worksheet1->write_string($rno, 43, $myArrVal[52],$formatotsno);
else
$worksheet1->write_string($rno, 43, "",$formatotsno);
if($myArrVal[53] != "0")
$worksheet1->write_string($rno, 44, $myArrVal[53],$formatotsno);
else
$worksheet1->write_string($rno, 44, "",$formatotsno);
if($myArrVal[54] != "0")
$worksheet1->write_string($rno, 45, $myArrVal[54],$formatotsno);
else
$worksheet1->write_string($rno, 45, "",$formatotsno);
if($myArrVal[55] != "0")
$worksheet1->write_string($rno, 46, $myArrVal[55],$formatotsno);
else
$worksheet1->write_string($rno, 46, "",$formatotsno);

$worksheet1->write_string($rno, 21, $myArrVal[21],$formatotsno);
$worksheet1->write_string($rno, 22, $myArrVal[22],$formatotsno);
$worksheet1->write_string($rno, 23, $myArrVal[23],$formatotsno);
$worksheet1->write_string($rno, 24, $myArrVal[24],$formatotsno);
$worksheet1->write_string($rno, 25, $myArrVal[25],$formatotsno);
$worksheet1->write_string($rno, 26, $myArrVal[26],$formatotsno);
$worksheet1->write_string($rno, 27, $myArrVal[27],$formatotsno);
$worksheet1->write_string($rno, 28, $myArrVal[28],$formatotsno);
//$worksheet1->write_string($rno, 29, $myArrVal[29],$formatotsno);
$worksheet1->write_string($rno, 29, $myArrVal[30],$formatotsno);
//$worksheet1->write_string($rno, 31, $myArrVal[31],$formatotsno);
//$worksheet1->write_string($rno, 32, $myArrVal[32],$formatotsno);
$worksheet1->write_string($rno, 30, $myArrVal[33],$formatotsno);
$worksheet1->write_string($rno, 31, $myArrVal[34],$formatotsno);
//$worksheet1->write_string($rno, 35, $myArrVal[35],$formatotsno);
//$worksheet1->write_string($rno, 36, $myArrVal[36],$formatotsno);
//$worksheet1->write_string($rno, 37, $myArrVal[37],$formatotsno);
$worksheet1->write_string($rno, 32, $myArrVal[38],$formatotsno);
$worksheet1->write_string($rno, 33, $myArrVal[39],$formatotsno);
$worksheet1->write_string($rno, 34, $myArrVal[40],$formatotsno);
//$worksheet1->write_string($rno, 41, $myArrVal[41],$formatotsno);
$worksheet1->write_string($rno, 35, $myArrVal[42],$formatotsno);
//$worksheet1->write_string($rno, 43, $myArrVal[43],$formatotsno);
//$worksheet1->write_string($rno, 44, $myArrVal[44],$formatotsno);
$worksheet1->write_string($rno, 36, $myArrVal[45],$formatotsno);
$worksheet1->write_string($rno, 37, $myArrVal[46],$formatotsno);

$worksheet1->write_string($rno, 38, $myArrVal[47],$formatotsno);
$worksheet1->write_string($rno, 39, $myArrVal[48],$formatotsno);







$workbook->close();



AND YOU ARE DONE!!

HAPPY REPORTING !! :)

Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

STRESS MANAGEMENT

STRESS Stress is an unpleasant fact of life.  We all experience it for various reasons,  and we all try to come up with ways of  coping with it—some with more success  than others. So what exactly is stress  doing to your mind (and body)  when you're staring down a deadline?  And what can you do to power through it? What Stress Actually Does  to You and What You Can  Do About It The real problem with stress is that, for such a well  understood and universally experienced condition,  as a society we deal with it so poorly that it leads  to many of our most lethal illnesses and  long-term health problems. High blood  pressure, heart disease, cancer, stroke,  obesity, and insomnia are all medical  conditions across the spectrum that  can be related to or directly influenced  by high stress as an environmental conditio...
Post a Comment
Read more

Jquery Carousels

Jquery Carousels we all love carousels they are a fantastic way to give the effects we want our visitors to have, more than that we have multiple components to show like images, links, text etc. There are lot many ways to achieve it, Jquery is ofcourse the best possible option available outside. We all search for lot of ems it can be Jquery Flexslider  or Jcarousel Lite  whatever you choose, customization is required, in this tutorial I am not going to focus on how to install these libraries rather one step ahead, to let you know how these carousels can be called multiple times in the same page having their controls working respectively for each carousel instance. So, lets get started with the HTML <div id="sideRight"> <div id="first">       <!-- Do not change the class and tag type, as this will remain as it is for all the following divisions-->       <p class="containheader">Plans for you <a ...
Post a Comment
Read more

SugarCRM Footer Logo Remove

Hi Googler, Let us discuss about how to remove the SugarCRM Footer logo.. You all are aware of the power of the SugarCRM Tool and also must be very eager to remove the footer in order to make it look more professional. So below mentioned are some tricks for removing the footer from SugarCRM Community edition. 1) O pen-modules/Administration/ updater_utils.php Add-exit() ; in between function check_now() and return . By doing this u can remove 'powered by sugar crm' footer logo. 2) Go to modules/Trackers/Tracker.php, line 128, in the 'logPage' function. Drop the 'echo' statement. 3) Now to root(Sugarfolder)\include\mvc\view\sugarview.php and modify the line array(show header => true, show subpanel => true...........and so on) and make the changes to showfooter=>true to showfooter=>false And then enjoy by making it as your own built tool. As per the requests, make a reply and then I'll post the code and path to let you know how to write a cust...
10 comments
Read more