phpExcel匯出excel加超連結的實例程式碼

//寫excel
//包含類別
require_once( 'Classes/PHPExcel.php');
require_once('Classes/PHPExcel/Writer/Excel2007.php');
$objPHPExcel = new PHPExcel();
/* *
* phpExcel匯出excel
* by bbs.it-home.org
*/
//設定屬性檔案屬性
$objPHPExcel->getProperties()->setCreator(“Maarten Balliauw”);
$objPHPExcel->getProperties ()->setLastModifiedBy(“Maarten Balliauw”);
$objPHPExcel->getProperties()->setTitle(“Office 2007 XLSX 測試文件”);
$objPHPExcel->getProperties( Office 2007 XLSX 測試文檔」);
$objPHPExcel->getProperties()->setDescription(“Office 2007 XLSX 的測試文檔，使用PHP 類別產生。”);
$objPHPExcel->getProperties( setKeywords(“office 2007 openxml php”);
$objPHPExcel->getProperties()->setCategory(“測試結果檔案”);
//新增一些資料來新增資料
$objPHPExcel->設定ActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1′, 'Hello');//可以指定位置
$objPHPExcel->getActiveSheet()- >setCellValue('A2′,真的）;
$objPHPExcel->getActiveSheet()->setCellValue('A3', false);
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'world!');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 2);
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('D2', 'world!');
//迴圈
for($i = 1;$i$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i );
$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, '測試值') ;
}
//日期格式化
$objPHPExcel->getActiveSheet()->setCellValue('D1', time());
$objPHPExcel->getActiveSheet()->getStyle('D1′)- >getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
/ /Add comment 新增註解
$objPHPExcel->getActiveSheet(-100 setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('PHPExcel:');
 getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('E11′)->getText()->createTextRun(“rn”);
$objPHPExcel->getActiveSheet ()->getComment('E11′)->getText()->createTextRun('目前發票上的總金額，不含增值稅。');
//新增富文本字串新增文字可設定樣式
$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell('A18′) );
$objRichText->createText('這張發票是');
$objPayable = $objRichText->createTextRun('月底後三十天內付款');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()- >setItalic(true);
$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );
$objRichText->createText(',除非發票上另有說明。 ');
//合併儲存格合併分隔儲存格
$objPHPExcel->; getActiveSheet()->mergeCells('A18:E22');
$objPHPExcel- >getActiveSheet()->unmergeCells('A18:E22′);
//保護單元格
$objPHPExcel->; getActiveSheet()->getProtection()->setSheet( true);//需要設定為true才能啟用任何工作表保護！
$objPHPExcel->getActiveSheet()->protectCells('A3:E13′, 'PHPExcel');
//設定單元格編號格式數字結構
$objPHPExcel- >getActiveSheet()->getStyle('E4′)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet( getStyle('E4′), 'E5:E13′ );
< ;p>//設定列寬度設定列寬度
$objPHPExcel->getActiveSheet()->getColumnDimension('B' )->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
//設定字體
 $ objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('B1′)->getFont ()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('B1′)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()- >getStyle('B1′)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('B1′)->getFont()->getor() ->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
//設定對齊方式
$objPHPExcel->getActiveSheet()->getStyle('D11′)->getAlignment()-> setHorizo​​​​ntal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('A18′)->getAlignment()->setHorizo​​​​nfiduzo​​n; $objPHPExcel->getActiveSheet()->getStyle('A18′)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet( getAlignment()->setWrapText(true);
//設定列borders 已知列
$objPHPExcel->getActiveSheet()->getStyle('A4′)->getBorders( )->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A10′)->getorderSPH)-> :BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E10′)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_BorderBORDER_THIN)::BORDER_THIN)
;$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHP%( E13′)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
//設定邊框顏色為自己的顏色
$objPHPExcel->geteSheet ()->getStyle('D13′)->getBorders()->getLeft()->getColor( )->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13 ')->getBorders()->getTop()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()- >getBottom()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('E13′)->getBorders()->getRight()->getColor( )->setARGB('FF993300′);
//設定填充設定填充
$objPHPExcel->getActiveSheet()->getStyle('A1′)->getFill()- >setFillType(PHPExcel_Style_Fill::填充_固體）;
$objPHPExcel->getActiveSheet()->getStyle('A1′)->getFill()->getStartColor()->setARGB('FF808080′) p>
 //為工作表新增超連結連結
$objPHPExcel->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
$objPHPExcel- >getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('http://www.phpexcel.net');
$objPHPExcel->getActiveSheet()->getCell('E26 ')->getHyperlink()->setTooltip('導航到網站');
$objPHPExcel->getActiveSheet()->getStyle('E26′)->getAlignment()->setHorizo​​ntal(PHPExcel_Style_Alignment()->setHorizo​​​​ntal(PHPExcel_Style_Alignment: :HORIZONTAL_RIGHT);
/ /在工作表中加入繪圖新增圖片
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo')
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/officelogo.jpg');
$objDrawing->setHeight(36);
$objDrawing ->setCoordinates('B15');
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
//嘗試插入和刪除行與列
$objPHPExcel-> ;getActiveSheet()->insertNewRowBefore(6, 10);
$objPHPExcel->getActiveSheet()->removeRow(6, 10); objobjo> getActiveSheet()->insertNewColumnBefore('E', 5);
$objPHPExcel->getActiveSheet()->removeColumn('E', 5);
///新增條件格式
$objConditional1 = new PHPExcel_Style_Conditional() ;
$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);
$dition_Conpgenal1-dob​​perator​​al(Boper); jConditional1->setCondition( '0');
$objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
$objConditional1->getStyle()->Font() ->setBold(true);
//設定autofilter自動過濾
$objPHPExcel-> ;getActiveSheet()->setAutoFilter('A1:C9');
//隱藏「電話」與「傳真」欄位隱藏欄位
$objPHPExcel->getActiveSheet ()->getColumnDimension('C')->setVisible(false);
$objPHPExcel-> getActiveSheet()->getColumnDimension('D')->setVisible(false);
//設定文件安全設定文件安全性
$objPHPExcel->getSecurity()->setLockWindows( true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword(“PHPExcel”);
/ /設定工作表安全性
$objPHPExcel-> getActiveSheet()->getProtection()->setPassword('PHPExcel');
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true) ;// 應該啟用此功能才能啟用以下任何功能！
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(trueInsertRows(true)

$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);
//計算資料計算
echo 'Value of B14 [=COUNT (B2:B12) ]: ' . $objPHPExcel->getActiveSheet()->getCell('B14')->getCalculatedValue() 。 「rn」;
//設定大綱層級
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);
$objPHPExcel- >getActiveSheet()->getColumnDimension('E')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setCollapsed(true);
//凍結窗格
$objPHPExcel- >getActiveSheet()->freezePane('A2′);
//在頂部重複的行
$objPHPExcel ->getActiveSheet()->getPageSetup( )->setRowsToRepeatAtTopByStartAndEnd(1, 1);
//設定資料驗證驗證輸入值
$objValidation = $objPHPExcel->getActiveet( ->getCell( 'B3')->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE );
$objValidation->setErrorStyle(Excel_Cell_ValidationdobjValidation->setErrorStyle(Excel_Cell_ValidationValcel_Cell_Data; >setAllowBlank(true);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setErrorTitle('objorTitle('objor); ->setError('數字不允許！');
$objValidation->setPromptTitle('允許輸入');
$objValidation->setPrompt('只允許使用10 到20 之間的數字。 ');
$objValidation->setFormula1(10);
$objValidation->setFormula2(20);
$objPHPExcel->getActiveSheet()->getCell('B3′)->setDataValidation($$ objValidation);
//建立一個新的工作表，之後預設工作表建立新的工作標籤
$objPHPExcel->createSheet(); bbs.it-home.org
$objPHPExcel->setActiveSheetIndex(1);
//設定頁首和頁尾。當奇數/偶數沒有使用不同的標頭時，假定為奇數標頭。 頁首頁腳
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&H請將此文件視為機密！');
$objPHPExcel->getActiveSheet()->getHeaderFooter($objPHPExcel->getActiveSheet()->getHeaderFooter($objPHPExcel->getActiveSheet()->getHeaderFooter( )->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');< ;/p>
//設定頁面方向與大小方向大小
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE)； ;
//重新命名工作表重新命名工作表標籤
$objPHPExcel->getActiveSheet()->setTitle('Simple');
 //將活動工作表索引設定為第一個工作表，因此Excel 將其作為第一個工作表開啟sheet
$objPHPExcel->setActiveSheetIndex(0);
/ /儲存Excel 2007 檔案保存
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__); >//保存Excel 5 檔案保存
require_once( '類別/PHPExcel/Writer/Excel5.php');
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
//1.6.2新版保存
require_once( '類別/PHPExcel/IOFactory.php');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xls', __FILE__));//讀取excel
//包含類別
require_once('Classes/PHPExcel/Reader/Excel2007.php');
$objReader = new PHPExcel_Reader_Excel2007;>$objReader = new PHPExcel_Reader_Excel2007;
$objPHPExcel = $objReader->load(“05featuredemo.xlsx”);
 //csv讀寫器
require_once(“05featuredemo.inc.php”);
require_once(“05featuredemo.inc.php”);
require_once(“05featuredemo.inc.php”);
require_once(“05featuredemo.inc.php”);
require_once(“05featuredemo.inc.php”);
require_once(“05featuredemo.inc.php”);
require_once('Classes/PHPExcel/Writer/CSV.php');
require_once('Classes/PHPExcel/Reader/CSV.php');
require_once('Classes/PHPExcel/Writer/Excel2007.phpquire' );
//寫入CSV格式寫
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$objWriter->setDelimiter(';');
$objWriter->setDelimiter(';');
$ objWriter->setEnclosure(”);
$objWriter->setLineEnding(“rn”);
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.php', '.csv', __FILE__));
//從CSV格式讀取讀取
$objReader = new PHPExcel_Reader_CSV () ;
$objReader->setDelimiter(‘;’);
$objReader->setEnclosure(”);
$objReader->setLineEnding(“rn”);
$objReader->setSheetIndex(0);
$objPHPExcelFromCSV = $objReader->load(str_replace('.php', '.csv', __FILE__));
//寫入Excel2007格式
 $objWriter2007 =新PHPExcel_Writer_Excel2007($objPHPExcelFromCSV);
$objWriter2007->save(str_replace('.php', '.xlsx', __FILE__));
//寫html
require_once(“05featuredemo.inc.php ” ”);
require_once('Classes/PHPExcel/Writer/HTML.php');
//寫入HTML格式
$objWriter = new PHPExcel_Writer_HTML($objPHPExcelobjWriter = new PHPExcel_Writer_HTML($objPHPExcelobjWriter = new PHPExcel_Writer_HTML($objPHPExcelobjWriter = new PHPExcel_Writer_HTML($objPHPExcelobjWriter = new PHPExcel_Writer_HTML($objPHPExcel ;
$objWriter->setSheetIndex(0)
$objWriter->save(str_replace('.php', '.htm', __FILE__));
//寫入pdf
require_once(“05featuredemo.inc.php”）；
require_once('Classes/PHPExcel/IOFactory.php');
//寫入PDF格式
 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF '）;
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.php', '.pdf, $objWriter->save(str__ ));
//回顯記憶體使用熱點
echo date('H:i:s') . “記憶體使用熱點：”。 MBrn”;