db.php for database operation class, $config for database configuration, phpexcel version phpexcel_1.8.0, PHP code:
$dir=dirname(__file__);require $dir. "/phpexcel/db.php";require $dir. "/phpexcel/phpexcel.php";$object=NewPhpexcel ();$db=Newdb$config);$objSheet=$object-Getactivesheet ();//Text Center and text settings$objSheet->getdefaultstyle ()->getalignment ()->setvertical (Phpexcel_style_alignment::vertical_center), Sethorizontal (phpexcel_style_alignment::horizontal_center);$objSheet->getdefaultstyle ()->getfont ()->setname ("Microsoft Jas Black")->setsize (14);$objSheet->getstyle ("a2:x2")->getfont ()->setsize ()->setbold (TRUE);$objSheet->getstyle ("a3:x3")->getfont ()->setsize (+)->setbold (TRUE);//Set Row Height$objSheet->getrowdimension (2)->setrowheight (35);$objSheet->getrowdimension (3)->setrowheight (25);$objSheet->getrowdimension (4)->setrowheight (20);//Fill Color$objSheet->getstyle ("a2:x2")->getfill ()->setfilltype (phpexcel_style_fill::fill_solid)->getStartColor () Setrgb (' ffff00 ');$objSheet->getstyle ("a3:x3")->getfill ()->setfilltype (phpexcel_style_fill::fill_solid)->getStartColor () Setrgb (' ff0000 ');//Sheet name Long$objSheet->settitle ("Score Table");//Check all grades.$i= 0;$data _g=$db-Getallgrade ();foreach($data _g as $g _k=$g _v) { //find several classes per grade $data _c=$db->getallclassbygrade ($g _v[' Grade ']); //get grade cells and fill cells $gradeIndex=$i* *; $grade= Getcells ($gradeIndex); $objSheet->setcellvalue ($grade." 2 ",$g _v[' Grade ']. " Grade); foreach($data _c as $c _v) { //Check all student scores $data=$db->getdatabyclass ($c _v[' Class '],$g _v[' Grade ']); //get the name and the cell where the score is $nameIndex=$i* *; $scoreIndex=$i*2+1; $index _c= Getcells ($nameIndex); $score= Getcells ($scoreIndex); //Fill class rows $objSheet->setcellvalue ($index _c." 3 ",$c _v[' class ']. Class); //Fill Column Name $objSheet->setcellvalue ($index _c." 4 "," Name ")->setcellvalue ($score." 4 "," Score "); //$objSheet->getstyle ($index _c)->getnumberformat ()->setformatcode (Phpexcel_style_numberformat:: Format_text); Line Wrap//$objSheet->getstyle ($nameIndex)->getalignment ()->setwraptext (true); Start exporting Content $j= 5; foreach($data as $v) { $objSheet->setcellvalue ($index _c.$j,$v[' Name ']) ->setcellvalue ($score.$j, ‘ ‘.$v[' Score ']); //Set Row Height $objSheet->getrowdimension ($j)->setrowheight (20); $j++; } $i++; //Merging class cells $endClass= Getcells ($i*2-1); $objSheet->mergecells ($index _c." 3: ".$endClass." 3 "); //set the border of a class $styleArray= BorderStyle (' 0000FF '); $objSheet->getstyle ($index _c." 3: ".$endClass." 3 ")->applyfromarray ($styleArray); } //Combined Grade cell $endGrade= Getcells ($i*2-1); $objSheet->mergecells ($grade." 2: ".$endGrade." 2 "); //set the border of a grade $styleArray= BorderStyle (' 00ff00 '); $objSheet->getstyle ($grade." 2: ".$endGrade." 2 ")->applyfromarray ($styleArray);}//get a A-z alphabetical column by passing in Parameters (1-26)functionGetcells ($index) { $str=RangeAZ); return $str[$index];}//Set Border StylefunctionBorderStyle ($color) { $styleArray=Array( ' Borders ' =Array( ' Outline ' =Array( ' Style ' = Phpexcel_style_border::border_thick, ' color ' =Array(' rgb ' = =$color), ), ), ); return $styleArray;}Header(' Content-type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet ');Header(' content-disposition:attachment;filename= ' 01simple.xlsx "');Header(' Cache-control:max-age=0 ');$objWriter= Phpexcel_iofactory::createwriter ($object, ' Excel5 ');$objWriter->save (' php://output ');
Database structure:
:
Phpexcel Generate Excel Statistics table