phpexcel類別函式庫的基本用法,phpexcel入門範例大全

//寫入excel檔案
//包含類別
require_once('Classes/ PHPExcel. php');
require_once('Classes/PHPExcel/Writer/Excel2007.php');
$objPHPExcel = new PHPExcel();
//設定properties 設定檔案屬性
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Ballia9");setTitle("Office 2007 XLSX 測試文件");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX 測試文件");
$objPHPExcel-get>Properties() "Office 2007 XLSX 的測試文檔，使用PHP 類別產生。");
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
$objPHPExcel-get>Properties( setCategory("測試結果檔");
//加入一些資料加入資料
$ objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()-> setCellValue('A1', 'Hello');//可以指定位置
$objPHPExcel->getActiveSheet() ->setCellValue('A2', true);
$objPHPExcel->getActiveSheet()->setCellValue ('A3', false);
$objPHPExcel->getActiveSheet()->setCellValue ('B2', '世界!');
$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);
//新增註解
 $objPHPExcel-geteCommet(M 'E11')->setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E11') ->getText()->createTextRun('PHPExcel:');
$objCommentRichText->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 ) );
$objRich>create"有說明。 ');< ;/p>
//合併單元格合併分離單元格
$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->getActivelicpcel->getp-Sexcel-v >getStyle('E4'), 'E5:E13' ) ;
//設定列寬度設定列寬度
$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()->setUnderline(PHPExcel_Style_Font::oPH-SINGA); getActiveSheet()-> getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
//設定對齊方式設定對齊
 $objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizo​​​​ntal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPHPExcel-getS8 ')->getAlignment()->setHorizo​​ntal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment(-Pexim)d-Pic VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setWrapText(true);
 //設定列邊框已知欄位
$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border) ::BORDER_THIN);getStyle('A10')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E10')( )->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet ()->getStyle('D13')->getorderLes()-> :BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
//設定邊框顏色熟悉顏色
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->getColor()-> ;setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB ('FF993300');
$objPHPExcel->getActive ()->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::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()-> getStartColor()->setARGB('FF808080');
//為工作表新增超連結新增連結
$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::HORIZONTAL_RIGHT);
//在工作表中加入繪圖新增圖片
$objDrawing =//在工作表中加入繪圖新增圖片
$objDrawing = new PHPDceling = newHet_heet_Works_heet_heet_ );
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/officelogo.jpg ');
$objDrawing->setHeight(36);
$objDrawing->setCoordinates('B15');
$objDrawing->setOffsetX(110);
$objDrawing-setRotation(
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing ->setWorksheet($objPHPExcel-get>Sheet(objDraweet( );
//嘗試插入和刪除行和列
$objPHPExcel->getActiveSheet()- >insertNewRowBefore(6, 10);
$objPHPExcel->getActiveSheet() ->removeRow(6, 10);
$objPHPExcel->getActiveSheet()->insertNewColumnBefore('E', 5);
$objPHPExcel->getActiveSheet()->removemove ;
//新增條件格式
$objConditional1 = new PHPExcel_Style_Conditional();
$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);$objConditional1->setCondition('0 '） ;
$objConditional1->getStyle()->getFont()->getCol_m)-> ;
$objConditional1->getStyle()->getFont ()->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( )->WosetWocelbook( );
//設定工作表安全性
$objPHPExcel->getActiveSheet()->getProtection()- >setPassword('PHPExcel');
$objPHPExcel->getActiveSheet ()->getProtection()->setSheet(true);// 應該啟用此功能才能啟用以下任何功能！
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);getActiveSheet()->getProtection()->setFormatCells(true);
//計算資料計算
echo 'B14的值[ =COUNT(B2:B12)]: ' . $objPHPExcel->getActiveSheet()->getCell('B14')->getCalculatedValue() 。 “rn”;
//設定大綱層級
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);
$objPHPExcel->getActiveSheet()->getActiveSheet()-> setOutlineLevel(1);
$objPHPExcel->getActiveSheet()-> ;getColumnDimension('E')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumn' >setCollapsed(true);
//凍結窗格
$objPHPExcel->getActiveSheet()->freezePane('A2');
 //在頂部重複的行
$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);
//設定資料驗證驗證輸入值
$ objValidation = $objPHPExcel->getActiveSheet()->getCell('B3')->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE ); PHPExcel_Cell_DataValidation::STYLE_STOP );
$objValidation->setAllowBlank(true);
$objValidation->setShowInputMessage(true);
$objValidation->setShowInputMessage(true);
$objValidation->setSashom-Message(trueE)-Ladm-Tad-uset-Tun​​rd-Message(Moo​​pet-Mr.> ('輸入錯誤');
$objValidation->setError('數字不允許！');
$objValidation->setPromptTitle('允許輸入');
$objValidation->setPrompt('僅僅允許使用10 到20 之間的數字。 ');
$objValidation->setFormula1(10);
$objValidation->setFormula2(20);
 $objPHPExcel->getActiveSheet()->getCell('B3')->setDataValidation($$ objValidation);
//建立一個新的工作表，預設工作表建立後新的工作標籤
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex (1);
//設定頁首和頁尾。當奇數/偶數沒有使用不同的標頭時，假定為奇數標頭。 頁眉頁腳
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&H請將此文件視為機密！');
$objPHPExcel->getActiveSheet()->setOddHeader()->setOddHeader()->setOddHeader()->setOddHeader()->setOddHeader()->setOddHeader()->setOddHeader()->setOddHeader()->setOddHeader()->setOddHeader()->setOddHeader()->setOddHeader()->setOddHeader()->setOddHeader()->setOddHeader()->setOddHeD()->setOddHeader()->setOddHea()-> '&C&H請將此文件視為機密！ ');
$objPHPExcel->getActiveSheet()-> ;getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');
//設定頁面方向與大小方向大小
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Works::heet_PageSetSetSCA );
$objPHPExcel->getActiveSheet()->getPageSetup ()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
/重新命名工作表重新命名工作表標籤>$objPHPExcel->getActiveSheet()->setTitle( 'Simple');
//將活動工作表索引設定為第一個工作表，以便Excel 將其作為第一個工作表開啟
$objPHPExcel->setActiveSheetIndex(0);
//儲存Excel 2007 檔案儲存
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); objWriter->save(str_replace('.php', '. xlsx', __FILE__));

//儲存Excel 5 檔案儲存
require_once('Classes/PHPExcel/Writer/Excel5.php' );
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel) ;
$objWriter->save(str_replace('.php', '.xls', __FILE__));
 //1.6.2新版保存
require_once ('Classes/PHPExcel/IOFactory.php');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');讀excel
//包含類別
require_once('Classes/PHPExcel/Reader/Excel2007. php');
$objReader = new PHPExcel_Reader_Excel2007;
$objPHPExcel = $objReader->load("05featuredemo.xlsx");
Readercsvrequire_once('Classes/PHPExcel/Writer/CSV.php');
require_once('Classes/PHPExcel/Reader/CSV.php');
require_once('Classes /PHPExcel/Writer/ Excel2007.php');
//寫入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 = new PHPExcel_Writer_Excel2007($objPHPExcelFromCSV); .php', '.xlsx', __FILE__));
寫html
require_once("05featuredemo.inc.php");
require_once('Classes/PHPExcel/Writer/HTML.php');
//寫入HTML格式
$objWriter = new PHPExcel_Wter_HTHTML (Wter_HTHTML $objPHPExcel);
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.php', '.htm', __FILE__));
寫pdf寫pdf
require_once("05featuredemo.inc.php");
require_once('Classes/PHPExcel/IOFactory.php');
//寫入PDF格式
$ objWriter =FactorExcel_IOter =Factory: :createWriter($objPHPExcel, 'PDF');
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.php', '.pdf', __FILE__));//回顯記憶體峰值使用
echo date('H:i:s') . “記憶體使用峰值：”。 （記憶體取得峰值使用情況（真）/ 1024 / 1024）。 " MBrn";