PHPexcel生成復雜的報表表頭類例子

2024-05-04 21:49:51

字體：大中小

來源：轉載

供稿：網友

PHPexcel是一款非常不錯的excel表格操作php插件了,我們通常用它來生成excel文檔或數據轉換并導入到mysql數據庫中,下面介紹的是PHPexcel生成復雜的報表表頭類例子.

以前一直有需求,能把Execl里面的數據導入數據庫,并且把數據庫里面的數據導出到Execl中,代碼如下:

<?php

require_once dirname(__FILE__) . '/../Classes/PHPExcel/IOFactory.php';

class PHPExeclCore extends PHPExcel_IOFactory{





public static function SummerCreateExecl($Head,$data)

{

self::SummerCreateExeclHead($Head,$data,"Excel2007");

}



public static function SummerReadExecl($dir)

{

if(!file_exists($dir))

{

echo "Execl Not Exist";

}

else

{

$PHPExeclObj = self::load($dir);



$sheetCount = $PHPExeclObj->getSheetCount(); //得到Execl中包含的Sheet工作簿的數量



for($i=0;$i<$sheetCount;$i++)

{

$ActiveSheet = $PHPExeclObj->getSheet($i);

$highestRow = $ActiveSheet->getHighestRow(); // 取得總列數

$allColumn = $ActiveSheet->getHighestColumn();

  //開源軟件:Vevb.com

//通過嵌套循環來讀取sheet工作簿里面的內容

for($Col='A';$Col<$allColumn;$Col++)

{

for($Row=1;$Row<$highestRow;$Row++)

{

$Data[$Col][$Row] = $ActiveSheet->getCell($Col.$Row)->getValue();

}

}

}

}



return $Data;

}





/*

* 將數據寫入到數據表中

* $Data Array 表示要插入進Execl數據

* $RuleData Array 表示數據格式的規則數組

* $i int 表示從第幾行起的插入數據

* **/



public static function SummerInsertDateToExecl($sheet,$Head,$Data,$n=3,$RuleData=array())

{



$SimpleHead = self::getHead($Head);



$row = $n;

foreach($Data as $key=>$valueArr)

{

$m = 0;

foreach($valueArr as $k=>$v)

{

$StartCol = PHPExcel_Cell::stringFromColumnIndex($m).$row;

$sheet->getCell($StartCol)->setValue($v);

$sheet->getStyle($StartCol)->getAlignment()->applyFromArray(

array(

'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,

'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,

'rotation' => 0,

'wrap' => TRUE,

)

);





if(isset($SimpleHead[$k]['col']))

{

$m = $m + $SimpleHead[$k]['col']-1;

$endCol = PHPExcel_Cell::stringFromColumnIndex($m).$row;

$sheet->mergeCells($StartCol.":".$endCol);

}



$m++;



$type = false;



if(isset($SimpleHead[$k]['type']))

{

$type = $SimpleHead[$k]['type'];

$AllowArray = $SimpleHead[$k]['allowarray'];

}



//設置單元格的數據驗證

if($type)

{

switch ($type)

{

case 'list':

self::setSelectionRange($sheet, $StartCol,$AllowArray);

break;

case 'range':

self::setValueRange($sheet, $StartCol,$AllowArray);

break;

}

}

}

$row ++ ;

}



}



/*

* 生成Execl單元格備注

* $sheet 當前的工作簿對象

* $Cell 需要設置屬性的單元格

* $content 備注內容

* */

private static function setComment($sheet,$Cell,$content)

{

$sheet->getComment($Cell)->setAuthor('4399om');

$objCommentRichText = $sheet->getComment($Cell)->getText()->createTextRun('4399om:');

$objCommentRichText->getFont()->setBold(true);

$sheet->getComment($Cell)->getText()->createTextRun("/r/n");

$sheet->getComment($Cell)->getText()->createTextRun($content);

$sheet->getComment($Cell)->setWidth('100pt');

$sheet->getComment($Cell)->setHeight('100pt');

$sheet->getComment($Cell)->setMarginLeft('150pt');

$sheet->getComment($Cell)->getFillColor()->setRGB('EEEEEE');

}



/*

* 現在單元格的有效數據范圍，暫時僅限于數字

* $sheet 當前的工作簿對象

* $Cell 需要設置屬性的單元格

* $ValueRange array 允許輸入數組的訪問

*/

private static function setValueRange($sheet,$Cell,$ValueRange)

{

//設置單元格的的數據類型是數字，并且保留有效位數

$sheet->getStyle($Cell)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);



$ValueRange = explode(",",$ValueRange);



//開始數值有效訪問設定

$objValidation = $sheet->getCell($Cell)->getDataValidation();

$objValidation->setType( PHPExcel_Cell_DataValidation:: TYPE_WHOLE );

$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation:: STYLE_STOP );

$objValidation->setAllowBlank(true);

$objValidation->setShowInputMessage( true); //設置顯示提示信息

$objValidation->setShowErrorMessage( true); //設置顯示錯誤信息

$objValidation->setErrorTitle('輸入錯誤'); //錯誤標題

$objValidation->setError('請輸入數據范圍在從'.$ValueRange[0].'到'.$ValueRange[1].'之間的所有值'); //錯誤內容

$objValidation->setPromptTitle('允許輸入'); //設置提示標題

$objValidation->setPrompt('請輸入數據范圍在從'.$ValueRange[0].'到'.$ValueRange[1].'之間的所有值'); //提示內容

$objValidation->setFormula1($ValueRange['0']); //設置最大值

$objValidation->setFormula2($ValueRange['1']); //設置最小值



}



private static function OutinputHeader($objWriter)

{

$fileName = str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME));

header("Content-Type: application/force-download");

header("Content-Type: application/octet-stream");

header("Content-Type: application/download");

header('Content-Disposition:inline;filename="'.$fileName.'"');

header("Content-Transfer-Encoding: binary");

header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");

header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

header("Pragma: no-cache");

$objWriter->save('php://output');

exit;

}



//數據控制，設置單元格數據在一個可選方位類

private static function setSelectionRange($sheet,$Cell,$rangeStr,$Title="數據類型")

{



$objValidation = $sheet->getCell($Cell)->getDataValidation();

$objValidation -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)

-> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_STOP)

-> setAllowBlank(true)

-> setShowInputMessage(true)

-> setShowErrorMessage(true)

-> setShowDropDown(true)

-> setErrorTitle('輸入的值有誤')

-> setError('您輸入的值不在下拉框列表內.')

-> setPromptTitle('"'.$Title.'"')

-> setFormula1('"'.$rangeStr.'"');

}







/*

* 構建表頭

* */

public static function RecursionCreateExecl($head,$data)

{

$PHPExecl = new PHPExcel();



$objWriter = self::createWriter($PHPExecl, 'Excel2007');



$PHPExecl->getProperties()->setCreator("4399om")

->setLastModifiedBy("Summer")

->setTitle("Office 2007 XLSX Test Document")

->setSubject("Office 2007 XLSX Test Document")

->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")

->setKeywords("office 2007 openxml php")

->setCategory("Test result file");



$PHPExecl->setActiveSheetIndex(0);



$sheet = $PHPExecl->getActiveSheet();



self::HandleHeadToNode($sheet, $head,1,0,0);



self::SummerInsertDateToExecl($sheet,$head,$data,4);

self::OutinputHeader($objWriter);

}



private static function HandleHeadToNode($sheet,$Head,$beginRow,$col,$StartCol)

{



foreach($Head as $key=>$cells)

{

$row = $beginRow; //表示行



$beginCol = PHPExcel_Cell::stringFromColumnIndex($col).$row;



$sheet->getCell($beginCol)->setValue($cells['value']);



//設置表格樣式

$sheet->getStyle($beginCol)->getAlignment()->applyFromArray(

array(

'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,

'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,

'rotation' => 0,

'wrap' => TRUE,

)

);



$sheet->getStyle($beginCol)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_DARKGREEN);



//設置單元格的寬度

if(isset($cells['width']))

{

$Cell = $sheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($col));

$Cell->setWidth($cells['width']);

}



//哥元素打上標記

if(isset($cells['Content']))

{

self::setComment($sheet, $beginCol, $cells['Content']);

}



$merge = false; //合并單元格

if(isset($cells['col']))

{

$col += $cells['col']-1;

$merge = true;

}



if(isset($cells['row']))

{

$row += $cells['row']-1;

$merge = true;

}



if($merge)

{

$endCol = PHPExcel_Cell::stringFromColumnIndex($col).$row;

$sheet->mergeCells($beginCol.":".$endCol);

}



$row ++;

$col ++;



//表示有存在孩子節點

if(isset($cells['children']) && is_array($cells['children'])){

$cols = $StartCol;

if(!self::IsExistChildren($cells['children']))

{

$cols = $col-2;

$StartCol = $col;

}

self::HandleHeadToNode($sheet,$cells['children'],$row,$cols,$StartCol);

}else{

$StartCol = $col;

}



}



}

//判斷自己的孩子節點中是否存在孫子節點

private static function IsExistChildren($Data)

{

foreach($Data as $key=>$value)

{

if(isset($value['children']) && is_array($value['children']))

{

return true;

}

}

return false;

}





//獲取底層數據

private static function getHead($Head,&$Node=array())

{

foreach($Head as $key=>$value)

{

if(isset($value['children']) && is_array($value['children']))

{

self::getHead($value['children'],$Node);

}

else

{

$Node[] = $value;

}

}



return $Node;

}



}

$Head = array(

array('value'=>'姓名','col'=>2,'row'=>2,'width'=>20,'type'=>'list','allowarray'=>'PHP開發工程師,PHP開發'),

array('value'=>'第一天','col'=>2,'row'=>1,'width'=>20,'Content'=>'2014-12-29號',

'children'=>

array(

array('value'=>'上午','col'=>1,'width'=>20,'type'=>'range','allowarray'=>'10,100'),

array('value'=>'下午','width'=>20),

),



),

array('value'=>'第二天','col'=>2,'row'=>1,'width'=>20,

'children'=>

array(

array('value'=>'上午','width'=>20),

array('value'=>'下午','width'=>20),

),

),

);

$data = array(

array('PHP開發工程師','12','吃飯1','睡覺1','起床刷牙2','吃飯睡覺2'),

array('PHP開發工程師','25','吃飯1','睡覺1','起床刷牙2','吃飯睡覺2'),

array('PHP開發工程師','50','吃飯1','睡覺1','起床刷牙2','吃飯睡覺2'),

array('PHP開發工程師','99','吃飯1','睡覺1','起床刷牙2','吃飯睡覺2'),

array('PHP開發工程師','10','吃飯1','睡覺1','起床刷牙2','吃飯睡覺2'),

);

$Node = PHPExeclCore::RecursionCreateExecl($Head,$data);

?>