由于業務需要,我們需要從業務中匯總數據,并生成Excel文件。
思路是這樣的
PHP要導出Excel表格文件->找一個好用的第三方庫吧->在Composer的Packages里找一個吧->PHPExcel這么多收藏,就它了!
PHPExcel is a library written in pure PHP and providing a set of html' target='_blank'>classes that allow you to write to and read from different spreadsheet file formats, like Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML, … This project is built around Microsoft’s OpenXML standard and PHP.
由于比較簡單,就不翻了。。。
PHP最低版本5.2.0
PHP擴展如下
接下來就是檢查服務器上的PHP版本和安裝擴展
php -v
php -m | grep -E 'zip|xml|gd2'
我們直接采用composer進行安裝
composer require phpoffice/phpexcel
我們的業務需求是生成excel文件,也就是擴展名為xls的文件,所以我們定位到的是項目給的事例中的Examples/14excel5.php
下面是事例源碼,我會在適當的注釋。
<?php/** * PHPExcel * * Copyright (c) 2006 - 2015 PHPExcel * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA * * @category PHPExcel * @package PHPExcel * @copyright Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel) * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL * @version ##VERSION##, ##DATE## *//** Error reporting */error_reporting(E_ALL);ini_set('display_errors', TRUE);ini_set('display_startup_errors', TRUE);define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');date_default_timezone_set('Europe/London'); //設置時區,我們根據自己項目所處的位置設置就好,不用跟本例一致include '05featuredemo.inc.php'; //引入其他文件,在這個文件里進行了文件內容的填充/** PHPExcel_IOFactory */require_once dirname(__FILE__) . '/../Classes/PHPExcel/IOFactory.php'; //引入數據流IO工廠類文件// Save Excel 95 fileecho date('H:i:s') , ' Write to Excel5 format' , EOL;$callStartTime = microtime(true);$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //創建一個文件寫入對象$objWriter->save(str_replace('.php', '.xls', __FILE__)); //寫入文件$callEndTime = microtime(true);$callTime = $callEndTime - $callStartTime; //計算耗時echo date('H:i:s') , ' File written to ' , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , ' seconds' , EOL;// Echo memory usageecho date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , ' MB' , EOL;// Echo memory peak usageecho date('H:i:s') , ' Peak memory usage: ' , (memory_get_peak_usage(true) / 1024 / 1024) , ' MB' , EOL;// Echo doneecho date('H:i:s') , ' Done writing file' , EOL;echo 'File has been created in ' , getcwd() , EOL;所以上面的文件的作用是對做好的文件進行格式轉換存儲,下面繼續看05featuredemo.inc.php.
<?php/** * PHPExcel * * Copyright (c) 2006 - 2015 PHPExcel * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA * * @category PHPExcel * @package PHPExcel * @copyright Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel) * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL * @version ##VERSION##, ##DATE## *//** Error reporting */error_reporting(E_ALL);/** Include PHPExcel */require_once dirname(__FILE__) . '/../Classes/PHPExcel.php'; //引入核心庫// Create new PHPExcel objectecho date('H:i:s') , ' Create new PHPExcel object' , EOL;$objPHPExcel = new PHPExcel(); //創建一個PHPExcel對象// Set document properties 設置文件信息echo date('H:i:s') , ' Set document properties' , EOL;$objPHPExcel->getProperties()->setCreator('Maarten Balliauw') ->setLastModifiedBy('Maarten Balliauw') ->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');// Create a first sheet, representing sales dataecho date('H:i:s') , ' Add some data' , EOL;$objPHPExcel->setActiveSheetIndex(0);$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Invoice');$objPHPExcel->getActiveSheet()->setCellValue('D1', PHPExcel_Shared_Date::PHPToExcel( gmmktime(0,0,0,date('m'),date('d'),date('Y')) ));//設置具體的數值$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15); //設置展示格式$objPHPExcel->getActiveSheet()->setCellValue('E1', '#12566');$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Product Id');$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Description');$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Price');$objPHPExcel->getActiveSheet()->setCellValue('D3', 'Amount');$objPHPExcel->getActiveSheet()->setCellValue('E3', 'Total');$objPHPExcel->getActiveSheet()->setCellValue('A4', '1001');$objPHPExcel->getActiveSheet()->setCellValue('B4', 'PHP for dummies');$objPHPExcel->getActiveSheet()->setCellValue('C4', '20');$objPHPExcel->getActiveSheet()->setCellValue('D4', '1');$objPHPExcel->getActiveSheet()->setCellValue('E4', '=IF(D4<>'',C4*D4,'')'); //支持直接寫入公式$objPHPExcel->getActiveSheet()->setCellValue('A5', '1012');$objPHPExcel->getActiveSheet()->setCellValue('B5', 'OpenXML for dummies');$objPHPExcel->getActiveSheet()->setCellValue('C5', '22');$objPHPExcel->getActiveSheet()->setCellValue('D5', '2');$objPHPExcel->getActiveSheet()->setCellValue('E5', '=IF(D5<>'',C5*D5,'')');$objPHPExcel->getActiveSheet()->setCellValue('E6', '=IF(D6<>'',C6*D6,'')');$objPHPExcel->getActiveSheet()->setCellValue('E7', '=IF(D7<>'',C7*D7,'')');$objPHPExcel->getActiveSheet()->setCellValue('E8', '=IF(D8<>'',C8*D8,'')');$objPHPExcel->getActiveSheet()->setCellValue('E9', '=IF(D9<>'',C9*D9,'')');$objPHPExcel->getActiveSheet()->setCellValue('D11', 'Total excl.:');$objPHPExcel->getActiveSheet()->setCellValue('E11', '=SUM(E4:E9)'); //支持直接寫入Excel函數$objPHPExcel->getActiveSheet()->setCellValue('D12', 'VAT:');$objPHPExcel->getActiveSheet()->setCellValue('E12', '=E11*0.21');$objPHPExcel->getActiveSheet()->setCellValue('D13', 'Total incl.:');$objPHPExcel->getActiveSheet()->setCellValue('E13', '=E11+E12');// Add commentecho date('H:i:s') , ' Add comments' , EOL;$objPHPExcel->getActiveSheet()->getComment('E11')->setAuthor('PHPExcel');$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('PHPExcel:');$objCommentRichText->getFont()->setBold(true);$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('');$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');$objPHPExcel->getActiveSheet()->getComment('E12')->setAuthor('PHPExcel');$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun('PHPExcel:');$objCommentRichText->getFont()->setBold(true);$objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun('');$objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun('Total amount of VAT on the current invoice.');$objPHPExcel->getActiveSheet()->getComment('E13')->setAuthor('PHPExcel');$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('PHPExcel:');$objCommentRichText->getFont()->setBold(true);$objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('');$objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('Total amount on the current invoice, including VAT.');$objPHPExcel->getActiveSheet()->getComment('E13')->setWidth('100pt');$objPHPExcel->getActiveSheet()->getComment('E13')->setHeight('100pt');$objPHPExcel->getActiveSheet()->getComment('E13')->setMarginLeft('150pt');$objPHPExcel->getActiveSheet()->getComment('E13')->getFillColor()->setRGB('EEEEEE');// Add rich-text stringecho date('H:i:s') , ' Add rich-text string' , EOL;$objRichText = new PHPExcel_RichText(); //創建富文本對象$objRichText->createText('This invoice is ');$objPayable = $objRichText->createTextRun('payable within thirty days after the end of the month');$objPayable->getFont()->setBold(true);$objPayable->getFont()->setItalic(true);$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );$objRichText->createText(', unless specified otherwise on the invoice.');$objPHPExcel->getActiveSheet()->getCell('A18')->setValue($objRichText);// Merge cellsecho date('H:i:s') , ' Merge cells' , EOL;$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');$objPHPExcel->getActiveSheet()->mergeCells('A28:B28'); // Just to test...$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28'); // Just to test...// Protect cellsecho date('H:i:s') , ' Protect cells' , EOL;$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');// Set cell number formatsecho date('H:i:s') , ' Set cell number formats' , EOL;$objPHPExcel->getActiveSheet()->getStyle('E4:E13')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);// Set column widthsecho date('H:i:s') , ' Set column widths' , EOL;$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);// Set fontsecho date('H:i:s') , ' Set fonts' , EOL;$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()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);$objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);$objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);// Set alignmentsecho date('H:i:s') , ' Set alignments' , EOL;$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);$objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);$objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);$objPHPExcel->getActiveSheet()->getStyle('B5')->getAlignment()->setShrinkToFit(true);// Set thin black border outline around column 設置復雜的格式echo date('H:i:s') , ' Set thin black border outline around column' , EOL;$styleThinBlackBorderOutline = array( 'borders' => array( 'outline' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000'), ), ),);$objPHPExcel->getActiveSheet()->getStyle('A4:E10')->applyFromArray($styleThinBlackBorderOutline);// Set thick brown border outline around 'Total'echo date('H:i:s') , ' Set thick brown border outline around Total' , EOL;$styleThickBrownBorderOutline = array( 'borders' => array( 'outline' => array( 'style' => PHPExcel_Style_Border::BORDER_THICK, 'color' => array('argb' => 'FF993300'), ), ),);$objPHPExcel->getActiveSheet()->getStyle('D13:E13')->applyFromArray($styleThickBrownBorderOutline);// Set fillsecho date('H:i:s') , ' Set fills' , EOL;$objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getFill()->getStartColor()->setARGB('FF808080');// Set style for header row using alternative methodecho date('H:i:s') , ' Set style for header row using alternative method' , EOL;$objPHPExcel->getActiveSheet()->getStyle('A3:E3')->applyFromArray( array( 'font' => array( 'bold' => true ), 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT, ), 'borders' => array( 'top' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN ) ), 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array( 'argb' => 'FFA0A0A0' ), 'endcolor' => array( 'argb' => 'FFFFFFFF' ) ) ));$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray( array( 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT, ), 'borders' => array( 'left' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN ) ) ));$objPHPExcel->getActiveSheet()->getStyle('B3')->applyFromArray( array( 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT, ) ));$objPHPExcel->getActiveSheet()->getStyle('E3')->applyFromArray( array( 'borders' => array( 'right' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN ) ) ));// Unprotect a cellecho date('H:i:s') , ' Unprotect a cell' , EOL;$objPHPExcel->getActiveSheet()->getStyle('B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);// Add a hyperlink to the sheetecho date('H:i:s') , ' Add a hyperlink to an external website' , EOL;$objPHPExcel->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('http://www.phpexcel.net'); /加入超鏈接$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website');$objPHPExcel->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);echo date('H:i:s') , ' Add a hyperlink to another cell on a different worksheet within the workbook' , EOL;$objPHPExcel->getActiveSheet()->setCellValue('E27', 'Terms and conditions');$objPHPExcel->getActiveSheet()->getCell('E27')->getHyperlink()->setUrl('sheet://'Terms and conditions'!A1');$objPHPExcel->getActiveSheet()->getCell('E27')->getHyperlink()->setTooltip('Review terms and conditions');$objPHPExcel->getActiveSheet()->getStyle('E27')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);// Add a drawing to the worksheet echo date('H:i:s') , ' Add a drawing to the worksheet' , EOL;$objDrawing = new PHPExcel_Worksheet_Drawing(); //添加圖片$objDrawing->setName('Logo');$objDrawing->setDescription('Logo');$objDrawing->setPath('./images/officelogo.jpg');$objDrawing->setHeight(36);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());// Add a drawing to the worksheetecho date('H:i:s') , ' Add a drawing to the worksheet' , EOL;$objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('Paid');$objDrawing->setDescription('Paid');$objDrawing->setPath('./images/paid.png');$objDrawing->setCoordinates('B15');$objDrawing->setOffsetX(110);$objDrawing->setRotation(25);$objDrawing->getShadow()->setVisible(true);$objDrawing->getShadow()->setDirection(45);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());// Add a drawing to the worksheetecho date('H:i:s') , ' Add a drawing to the worksheet' , EOL;$objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('PHPExcel logo');$objDrawing->setDescription('PHPExcel logo');$objDrawing->setPath('./images/phpexcel_logo.gif');$objDrawing->setHeight(36);$objDrawing->setCoordinates('D24');$objDrawing->setOffsetX(10);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());// Play around with inserting and removing rows and columnsecho date('H:i:s') , ' Play around with inserting and removing rows and columns' , EOL;$objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10);$objPHPExcel->getActiveSheet()->removeRow(6, 10);$objPHPExcel->getActiveSheet()->insertNewColumnBefore('E', 5);$objPHPExcel->getActiveSheet()->removeColumn('E', 5);// Set header and footer. When no different headers for odd/even are used, odd header is assumed.echo date('H:i:s') , ' Set header/footer' , EOL;$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BInvoice&RPrinted on &D');$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');// Set page orientation and sizeecho date('H:i:s') , ' Set page orientation and size' , EOL;$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);// Rename first worksheetecho date('H:i:s') , ' Rename first worksheet' , EOL;$objPHPExcel->getActiveSheet()->setTitle('Invoice');// Create a new worksheet, after the default sheetecho date('H:i:s') , ' Create a second Worksheet object' , EOL;$objPHPExcel->createSheet();// Llorem ipsum... 很長的文本$sLloremIpsum = 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Vivamus eget ante. Sed cursus nunc semper tortor. Aliquam luctus purus non elit. Fusce vel elit commodo sapien dignissim dignissim. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Curabitur accumsan magna sed massa. Nullam bibendum quam ac ipsum. Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Proin augue. Praesent malesuada justo sed orci. Pellentesque lacus ligula, sodales quis, ultricies a, ultricies vitae, elit. Sed luctus consectetuer dolor. Vivamus vel sem ut nisi sodales accumsan. Nunc et felis. Suspendisse semper viverra odio. Morbi at odio. Integer a orci a purus venenatis molestie. Nam mattis. Praesent rhoncus, nisi vel mattis auctor, neque nisi faucibus sem, non dapibus elit pede ac nisl. Cras turpis.';// Add some data to the second sheet, resembling some different data typesecho date('H:i:s') , ' Add some data' , EOL;$objPHPExcel->setActiveSheetIndex(1);$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Terms and conditions');$objPHPExcel->getActiveSheet()->setCellValue('A3', $sLloremIpsum);$objPHPExcel->getActiveSheet()->setCellValue('A4', $sLloremIpsum);$objPHPExcel->getActiveSheet()->setCellValue('A5', $sLloremIpsum);$objPHPExcel->getActiveSheet()->setCellValue('A6', $sLloremIpsum);// Set the worksheet tab colorecho date('H:i:s') , ' Set the worksheet tab color' , EOL;$objPHPExcel->getActiveSheet()->getTabColor()->setARGB('FF0094FF');;// Set alignmentsecho date('H:i:s') , ' Set alignments' , EOL;$objPHPExcel->getActiveSheet()->getStyle('A3:A6')->getAlignment()->setWrapText(true);// Set column widthsecho date('H:i:s') , ' Set column widths' , EOL;$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(80);// Set fontsecho date('H:i:s') , ' Set fonts' , EOL;$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('Candara');$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);$objPHPExcel->getActiveSheet()->getStyle('A3:A6')->getFont()->setSize(8);// Add a drawing to the worksheetecho date('H:i:s') , ' Add a drawing to the worksheet' , EOL;$objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('Terms and conditions');$objDrawing->setDescription('Terms and conditions');$objDrawing->setPath('./images/termsconditions.jpg');$objDrawing->setCoordinates('B14');$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());// Set page orientation and sizeecho date('H:i:s') , ' Set page orientation and size' , EOL;$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);// Rename second worksheetecho date('H:i:s') , ' Rename second worksheet' , EOL;$objPHPExcel->getActiveSheet()->setTitle('Terms and conditions');// Set active sheet index to the first sheet, so Excel opens this as the first sheet$objPHPExcel->setActiveSheetIndex(0);根據上面的事例,我們想要的效果都能實現了。 :)
不過要注意的是,demo上實現了功能,并不帶代表著代碼粘過來就能用。
別忘了我們使用composer加載的,demo是直接加載的文件,細微的差別需要在實踐上進一步體會。
use PHPExcel;use PHPExcel_IOFactory;use PHPExcel_Style_Color;use PHPExcel_RichText;use PHPExcel_Style_NumberFormat;
雖然文檔不清晰,但是給的例子還是很不錯的。
//設置文件基本數據 $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator('Calvin Lee') ->setLastModifiedBy('Calvin Lee') ->setTitle('PHPExcel輸入文件測試用例') ->setSubject('PHPExcel') ->setDescription('這是一個測試用例,導出之后要求能用Office2007打開') ->setKeywords('測試 PHP PHPExcel') ->setCategory('Test'); //設置正在編輯和默認選中的標簽 $objPHPExcel->setActiveSheetIndex(0); //合并第一行,寫入標題,設置成粗體 $objPHPExcel->getActiveSheet()->mergeCells('A1:E1'); $objRichText = new PHPExcel_RichText(); $objPayable = $objRichText->createTextRun('用戶交易訂單匯總'); $objPayable->getFont()->setBold(true); $objPayable->getFont()->setItalic(true); $objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_RED ) ); $objPHPExcel->getActiveSheet()->getCell('A1')->setValue($objRichText); //設置第二行名稱,設置寬度 $objPHPExcel->getActiveSheet()->setCellValue('B2', '成交總單數/單'); $objPHPExcel->getActiveSheet()->setCellValue('C2', '成交總金額/元'); $objPHPExcel->getActiveSheet()->setCellValue('E2', '平均訂單金額/元'); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); //模擬數據循環寫入數據和每行平均的公式 $rand_num = rand(5,10); //設置隨機數更接近實際情況 for($i=1 ; $i <= $rand_num; $i++){ //隨機生成一個名字,:) $rand_name = $this->_makeRandName(); $m = $i + 2 ; $objPHPExcel->getActiveSheet()->setCellValue('A'.$m, $rand_name); $objPHPExcel->getActiveSheet()->setCellValue('B'.$m, rand(20,100)); $objPHPExcel->getActiveSheet()->setCellValue('C'.$m, rand(2000,10000)); $objPHPExcel->getActiveSheet()->setCellValue('E'.$m, '=IF(D'.$m.'<>'0',C'.$m.'/B'.$m.','0')'); //由于平均數得到的會是小數,需要設置單元格格式,四舍五入小數點兩位機進行展示 $objPHPExcel->getActiveSheet()->getStyle('E'.$m)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00); } //處理最后一行的匯總信息 $last_num = $rand_num + 4; $rand_num_last = $rand_num +2; $objPHPExcel->getActiveSheet()->setCellValue('B'.$last_num, '=SUM(B3:B'.$rand_num_last .')'); $objPHPExcel->getActiveSheet()->setCellValue('C'.$last_num, '=SUM(C3:C'.$rand_num_last .')'); $objPHPExcel->getActiveSheet()->setCellValue('E'.$last_num, '=AVERAGE(E3:E'.$rand_num_last.')'); //設置平均數 //由于平均數得到的會是小數,需要設置單元格格式,四舍五入小數點兩位機進行展示 $objPHPExcel->getActiveSheet()->getStyle('E'.$last_num)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00); //設置所在標簽的屬性 $objPHPExcel->getActiveSheet()->setTitle('用戶匯總'); //創建一個寫入對象 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //保存文件 $objWriter->save('測試導出數據'.microtime(true).'.xls');
順便解決了平均數的問題~
剛才在測試插入隨機數據的時候,順便弄了一個隨機生成英文名的方法,順便分享出來
/** * 隨機生成一個英文名字 */ private function _makeRandName(){ $female_first_name_arr=[ 'Mary', 'Patricia', 'Linda', 'Barbara', 'Elizabeth', 'Jennifer', 'Maria', 'Susan','Margaret', 'Dorothy', 'Lisa', 'Nancy', 'Karen', 'Betty', 'Helen', 'Sandra', 'Donna','Carol', 'Ruth', 'Sharon', 'Michelle', 'Laura', 'Sarah', 'Kimberly', 'Deborah', 'Jessica','Shirley', 'Cynthia', 'Angela', 'Melissa', 'Brenda', 'Amy', 'Anna', 'Rebecca', 'Virginia','Kathleen', 'Pamela', 'Martha', 'Debra', 'Amanda', 'Stephanie', 'Carolyn', 'Christine','Marie', 'Janet', 'Catherine', 'Frances', 'Ann', 'Joyce', 'Diane', 'Alice', 'Julie','Heather', 'Teresa', 'Doris', 'Gloria', 'Evelyn', 'Jean', 'Cheryl', 'Mildred', 'Katherine','Joan', 'Ashley', 'Judith', 'Rose', 'Janice', 'Kelly', 'Nicole', 'Judy', 'Christina','Kathy', 'Theresa', 'Beverly', 'Denise', 'Tammy', 'Irene', 'Jane', 'Lori', 'Rachel','Marilyn', 'Andrea', 'Kathryn', 'Louise', 'Sara', 'Anne', 'Jacqueline', 'Wanda', 'Bonnie','Julia', 'Ruby', 'Lois', 'Tina', 'Phyllis', 'Norma', 'Paula', 'Diana', 'Annie', 'Lillian','Emily', 'Robin', 'Peggy', 'Crystal', 'Gladys', 'Rita', 'Dawn', 'Connie', 'Florence','Tracy', 'Edna', 'Tiffany', 'Carmen', 'Rosa', 'Cindy', 'Grace', 'Wendy', 'Victoria', 'Edith', 'Kim', 'Sherry', 'Sylvia', 'Josephine', 'Thelma', 'Shannon', 'Sheila', 'Ethel', 'Ellen','Elaine', 'Marjorie', 'Carrie', 'Charlotte', 'Monica', 'Esther', 'Pauline', 'Emma','Juanita', 'Anita', 'Rhonda', 'Hazel', 'Amber', 'Eva', 'Debbie', 'April', 'Leslie', 'Clara','Lucille', 'Jamie', 'Joanne', 'Eleanor', 'Valerie', 'Danielle', 'Megan', 'Alicia', 'Suzanne','Michele', 'Gail', 'Bertha', 'Darlene', 'Veronica', 'Jill', 'Erin', 'Geraldine', 'Lauren','Cathy', 'Joann', 'Lorraine', 'Lynn', 'Sally', 'Regina', 'Erica', 'Beatrice', 'Dolores','Bernice', 'Audrey', 'Yvonne', 'Annette', 'June', 'Samantha', 'Marion', 'Dana', 'Stacy','Ana', 'Renee', 'Ida', 'Vivian', 'Roberta', 'Holly', 'Brittany', 'Melanie', 'Loretta','Yolanda', 'Jeanette', 'Laurie', 'Katie', 'Kristen', 'Vanessa', 'Alma', 'Sue', 'Elsie','Beth', 'Jeanne' ]; $male_first_name_arr=[ 'James', 'John', 'Robert', 'Michael', 'William', 'David', 'Richard', 'Charles', 'Joseph','Thomas', 'Christopher', 'Daniel', 'Paul', 'Mark', 'Donald', 'George', 'Kenneth', 'Steven','Edward', 'Brian', 'Ronald', 'Anthony', 'Kevin', 'Jason', 'Matthew', 'Gary', 'Timothy','Jose', 'Larry', 'Jeffrey', 'Frank', 'Scott', 'Eric', 'Stephen', 'Andrew', 'Raymond','Gregory', 'Joshua', 'Jerry', 'Dennis', 'Walter', 'Patrick', 'Peter', 'Harold', 'Douglas','Henry', 'Carl', 'Arthur', 'Ryan', 'Roger', 'Joe', 'Juan', 'Jack', 'Albert', 'Jonathan','Justin', 'Terry', 'Gerald', 'Keith', 'Samuel', 'Willie', 'Ralph', 'Lawrence', 'Nicholas','Roy', 'Benjamin', 'Bruce', 'Brandon', 'Adam', 'Harry', 'Fred', 'Wayne', 'Billy', 'Steve', 'Louis', 'Jeremy', 'Aaron', 'Randy', 'Howard', 'Eugene', 'Carlos', 'Russell', 'Bobby','Victor', 'Martin', 'Ernest', 'Phillip', 'Todd', 'Jesse', 'Craig', 'Alan', 'Shawn','Clarence', 'Sean', 'Philip', 'Chris', 'Johnny', 'Earl', 'Jimmy', 'Antonio', 'Danny','Bryan', 'Tony', 'Luis', 'Mike', 'Stanley', 'Leonard', 'Nathan', 'Dale', 'Manuel', 'Rodney','Curtis', 'Norman', 'Allen', 'Marvin', 'Vincent', 'Glenn', 'Jeffery', 'Travis', 'Jeff','Chad', 'Jacob', 'Lee', 'Melvin', 'Alfred', 'Kyle', 'Francis', 'Bradley', 'Jesus', 'Herbert','Frederick', 'Ray', 'Joel', 'Edwin', 'Don', 'Eddie', 'Ricky', 'Troy', 'Randall', 'Barry', 'Alexander', 'Bernard', 'Mario', 'Leroy', 'Francisco', 'Marcus', 'Micheal', 'Theodore','Clifford', 'Miguel', 'Oscar', 'Jay', 'Jim', 'Tom', 'Calvin', 'Alex', 'Jon', 'Ronnie','Bill', 'Lloyd', 'Tommy', 'Leon', 'Derek', 'Warren', 'Darrell', 'Jerome', 'Floyd', 'Leo','Alvin', 'Tim', 'Wesley', 'Gordon', 'Dean', 'Greg', 'Jorge', 'Dustin', 'Pedro', 'Derrick','Dan', 'Lewis', 'Zachary', 'Corey', 'Herman', 'Maurice', 'Vernon', 'Roberto', 'Clyde','Glen', 'Hector', 'Shane', 'Ricardo', 'Sam', 'Rick', 'Lester', 'Brent', 'Ramon', 'Charlie','Tyler', 'Gilbert', 'Gene' ]; $last_name_arr=[ 'Smith', 'Johnson', 'Williams', 'Jones', 'Brown', 'Davis', 'Miller', 'Wilson', 'Moore','Taylor', 'Anderson', 'Thomas', 'Jackson', 'White', 'Harris', 'Martin', 'Thompson', 'Garcia','Martinez', 'Robinson', 'Clark', 'Rodriguez', 'Lewis', 'Lee', 'Walker', 'Hall', 'Allen','Young', 'Hernandez', 'King', 'Wright', 'Lopez', 'Hill', 'Scott', 'Green', 'Adams', 'Baker','Gonzalez', 'Nelson', 'Carter', 'Mitchell', 'Perez', 'Roberts', 'Turner', 'Phillips','Campbell', 'Parker', 'Evans', 'Edwards', 'Collins', 'Stewart', 'Sanchez', 'Morris', 'Rogers', 'Reed', 'Cook', 'Morgan', 'Bell', 'Murphy', 'Bailey', 'Rivera', 'Cooper','Richardson', 'Cox', 'Howard', 'Ward', 'Torres', 'Peterson', 'Gray', 'Ramirez', 'James','Watson', 'Brooks', 'Kelly', 'Sanders', 'Price', 'Bennett', 'Wood', 'Barnes', 'Ross','Henderson', 'Coleman', 'Jenkins', 'Perry', 'Powell', 'Long', 'Patterson', 'Hughes','Flores', 'Washington', 'Butler', 'Simmons', 'Foster', 'Gonzales', 'Bryant', 'Alexander','Russell', 'Griffin', 'Diaz', 'Hayes', 'Myers', 'Ford', 'Hamilton', 'Graham', 'Sullivan','Wallace', 'Woods', 'Cole', 'West', 'Jordan', 'Owens', 'Reynolds', 'Fisher', 'Ellis','Harrison', 'Gibson', 'Mcdonald', 'Cruz', 'Marshall', 'Ortiz', 'Gomez', 'Murray', 'Freeman','Wells', 'Webb', 'Simpson', 'Stevens', 'Tucker', 'Porter', 'Hunter', 'Hicks', 'Crawford','Henry', 'Boyd', 'Mason', 'Morales', 'Kennedy', 'Warren', 'Dixon', 'Ramos', 'Reyes', 'Burns','Gordon', 'Shaw', 'Holmes', 'Rice', 'Robertson', 'Hunt', 'Black', 'Daniels', 'Palmer','Mills', 'Nichols', 'Grant', 'Knight', 'Ferguson', 'Rose', 'Stone', 'Hawkins', 'Dunn','Perkins', 'Hudson', 'Spencer', 'Gardner', 'Stephens', 'Payne', 'Pierce', 'Berry','Matthews', 'Arnold', 'Wagner', 'Willis', 'Ray', 'Watkins', 'Olson', 'Carroll', 'Duncan','Snyder', 'Hart', 'Cunningham', 'Bradley', 'Lane', 'Andrews', 'Ruiz', 'Harper', 'Fox','Riley', 'Armstrong', 'Carpenter', 'Weaver', 'Greene', 'Lawrence', 'Elliott', 'Chavez','Sims', 'Austin', 'Peters', 'Kelley', 'Franklin', 'Lawson' ]; //隨機組合生成名字 $male_or_female = rand(0,1);//隨機男女 $first_name =$male_or_female? $female_first_name_arr[array_rand($female_first_name_arr)]:$male_first_name_arr[array_rand($male_first_name_arr)]; $last_name =$last_name_arr[array_rand($last_name_arr)]; $full_name = $first_name.' '.$last_name; return $full_name; }看到這個類的注釋,已經維護9年了,真是很佩服一直維護這個庫的開發者們~
隨著開發工作的不斷深入,使用工具的門檻越來越低,學用工具的速度也越來越快~
鄭重聲明：本文版權歸原作者所有，轉載文章僅為傳播更多信息之目的，如作者信息標記有誤，請第一時間聯系我們修改或刪除，多謝。
新聞熱點
疑難解答
