Home > Article > Backend Development > phpExcel in packaging_PHP tutorial
phpExcel in packaging_PHP tutorial
- WBOYOriginal
- 2016-07-15 13:21:391084browse
/**
* Array generation Excel
* @author zouhao zouhao619@gmail.com
* Usage example
* $excel =new Excel();
$data=array(
Array('id'=>1,'name'=>'Destiny 1'),
array('id'=>2,'name'=>'Destiny 2')
);
$header=array('ID','Character name');
$excel->setFileName('aaa');
$excel->setTitle('Workspace 1');
$excel->create($data,$header);
*/
class Excel {
private $excelObj;
private $fileName='download.xls';
/**
* Set the file name when downloading
* @param string $fileName
*/
public function setFileName($fileName){
$this->fileName=$fileName.'.xls';
}
/**
* Set title
* @param string $title
*/
public function setTitle($title) {
$this->excelObj->getActiveSheet ()->setTitle ( $title );
}
public function __construct() {
// First cancel the automatic registration mechanism of the original framework to avoid conflict with the excel automatic loading mechanism
spl_autoload_unregister ( 'autoload' );
require LIBRARY_PATH . '/PHPExcel/PHPExcel.php';
$this->excelObj = new PHPExcel ();
}
/**
* Based on the total number, return the column array
*
* @param int $count
* @return array
*/
private function getCharByNumber($data) {
// Automatically subtract the head
$count = count ( $data ['0'] );
$keys=array();
for($number = 1; $number <=$count; $number ++) {
$divisor= intval($number / 26);
$char = chr ( 64 + $number % 26 );
$char = $divisor == 0 ? $char : chr ( 64 +$divisor) . $char;
$keys [] = $char;
}
return $keys;
}
/**
* Generate Excel table
* @param array $data Two-dimensional array
* @param array $replace Array to be replaced
*/
public function create($data,$header=array(),$replace = null) {
empty($data) and exit('No data');
$keys = $this->getCharByNumber ( $data );
$this->createHeader ( $header, $keys );
$j=0;
foreach ( $data as $i=>$vo ) {
$j=0;
foreach ( $vo as $key => $item ) {
If (isset ( $replace [$key] )){
$this->excelObj->setActiveSheetIndex (0)->setCellValue ( $keys [$j] . ($i + 2), $replace [$key] [$item] );
}else{
$this->excelObj->setActiveSheetIndex ( 0 )->setCellValue ( $keys [$j] . ($i + 2), $item );
}
++$j;
}
}
//Output to temporary buffer for download
header ( "Content-Type: application/force-download" );
header ( "Content-Type: application/octet-stream" );
header ( "Content-Type: application/download" );
header ( 'Content-Disposition:inline;filename="'.$this->fileName.'"' );
header ( "Content-Transfer-Encoding: binary" );
header ( "Cache-Control: must-revalidate, post-check=0, pre-check=0" );
header ( "Pragma: no-cache" );
$objWriter = PHPExcel_IOFactory::createWriter ( $this->excelObj, 'Excel5' );
$objWriter->save ( 'php://output' );
}
/**
* Create header
*
* @param array $data
*/
private function createHeader($header, $keys) {
$header = array_combine ( $keys, $header );
foreach ( $header as $key => $vo ) {
$this->excelObj->setActiveSheetIndex ( 0 )->setCellValue ( "{$key}1", $vo );
}
}
}
Usage example:
$excel =new Excel();
$data=array(
Array('id'=>1,'name'=>'Destiny 1'),
array('id'=>2,'name'=>'Destiny 2')
);
$header=array('ID','Character name');
$excel->setFileName('aaa');
$excel->setTitle('Workspace 1');
$excel->create($data,$header); In other words, the two-dimensional array taken from the database can be created directly,
In addition, some fields are type, which are saved as int type and represent a certain state. You can refer to the third parameter of create
Example:
$excel =new Excel();
$data=array(
Array('id'=>1,'name'=>'Destiny 1','type'=>1),
array('id'=>2,'name'=>'Destiny 2','type'=>2)
);
$replace['type']=array(1=>'Male',2=>'Cute Girl');
$header=array('ID','Character name');
$excel->setFileName('aaa');
$excel->setTitle('Workspace 1');
$excel->create($data,$header,$replace);