/** * Generate export fields for EXCEL. * * @param GridField $gridField * @return array */ public function generateExportFileData($gridField) { $excelColumns = $this->exportColumns ? $this->exportColumns : singleton($gridField->getModelClass())->summaryFields(); $objPHPExcel = new \PHPExcel\Spreadsheet(); $worksheet = $objPHPExcel->getActiveSheet()->setTitle($gridField->getModelClass()); $col = 'A'; foreach ($excelColumns as $columnSource => $columnHeader) { $heading = !is_string($columnHeader) && is_callable($columnHeader) ? $columnSource : $columnHeader; $worksheet->setCellValue($col . '1', $heading); $col++; } $worksheet->freezePane('A2'); $items = $gridField->getManipulatedList(); // @todo should GridFieldComponents change behaviour based on whether others are available in the config? foreach ($gridField->getConfig()->getComponents() as $component) { if ($component instanceof GridFieldFilterHeader || $component instanceof GridFieldSortableHeader) { $items = $component->getManipulatedData($gridField, $items); } } $row = 2; foreach ($items->limit(null) as $item) { $columnData = array(); $col = 'A'; foreach ($excelColumns as $columnSource => $columnHeader) { if (!is_string($columnHeader) && is_callable($columnHeader)) { if ($item->hasMethod($columnSource)) { $relObj = $item->{$columnSource}(); } else { $relObj = $item->relObject($columnSource); } $value = $columnHeader($relObj); } else { $value = $gridField->getDataFieldValue($item, $columnSource); } $worksheet->getCell($col . $row)->setValueExplicit($value, PHPExcel\Cell\DataType::TYPE_STRING); $col++; } $row++; $item->destroy(); } $writer = PHPExcel\IOFactory::createWriter($objPHPExcel, 'Excel2007'); ob_start(); $writer->save('php://output'); $data = ob_get_clean(); return $data; }
* @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 PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // 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 data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', '-0.5')->setCellValue('A2', '-0.25')->setCellValue('A3', '0.0')->setCellValue('A4', '0.25')->setCellValue('A5', '0.5')->setCellValue('A6', '0.75')->setCellValue('A7', '1.0')->setCellValue('A8', '1.25'); $objPHPExcel->getActiveSheet()->getStyle('A1:A8')->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_PERCENTAGE_00); // Add conditional formatting echo date('H:i:s'), " Add conditional formatting", EOL; $objConditional1 = new \PHPExcel\Style\Conditional(); $objConditional1->setConditionType(\PHPExcel\Style\Conditional::CONDITION_CELLIS)->setOperatorType(\PHPExcel\Style\Conditional::OPERATOR_LESSTHAN)->addCondition('0'); $objConditional1->getStyle()->getFont()->getColor()->setARGB(\PHPExcel\Style\Color::COLOR_RED); $objConditional3 = new \PHPExcel\Style\Conditional(); $objConditional3->setConditionType(\PHPExcel\Style\Conditional::CONDITION_CELLIS)->setOperatorType(\PHPExcel\Style\Conditional::OPERATOR_GREATERTHANOREQUAL)->addCondition('1');
/** 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 />'); /** PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Set timezone echo date('H:i:s'), " Set timezone", EOL; date_default_timezone_set('UTC'); // Set value binder echo date('H:i:s'), " Set value binder", EOL; \PHPExcel\Cell::setValueBinder(new \PHPExcel\Cell\AdvancedValueBinder()); // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // 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"); // Set default font echo date('H:i:s'), " Set default font", EOL; $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10); // Set column widths echo date('H:i:s'), " Set column widths", EOL; $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(14); // Add some data, resembling some different data types echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->getActiveSheet()->setCellValue('A1', 'String value:')->setCellValue('B1', 'Mark Baker'); $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Numeric value #1:')->setCellValue('B2', 12345);
/* After doing some test, I've got these results benchmarked for writing to Excel2007: Number of rows Seconds to generate 200 3 500 4 1000 6 2000 12 4000 36 8000 64 15000 465 */ // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set 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 echo date('H:i:s'), " Add data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', "Firstname"); $objPHPExcel->getActiveSheet()->setCellValue('B1', "Lastname"); $objPHPExcel->getActiveSheet()->setCellValue('C1', "Phone"); $objPHPExcel->getActiveSheet()->setCellValue('D1', "Fax"); $objPHPExcel->getActiveSheet()->setCellValue('E1', "Is Client ?"); // Hide "Phone" and "fax" column echo date('H:i:s'), " Hide 'Phone' and 'fax' columns", EOL; $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);
* @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); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // 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 echo date('H:i:s'), " Add data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', "Cell B3 and B5 contain data validation...")->setCellValue('A3', "Number:")->setCellValue('B3', "10")->setCellValue('A5', "List:")->setCellValue('B5', "Item A")->setCellValue('A7', "List #2:")->setCellValue('B7', "Item #2")->setCellValue('D2', "Item #1")->setCellValue('D3', "Item #2")->setCellValue('D4', "Item #3")->setCellValue('D5', "Item #4")->setCellValue('D6', "Item #5"); // Set data validation echo date('H:i:s'), " Set data validation", EOL; $objValidation = $objPHPExcel->getActiveSheet()->getCell('B3')->getDataValidation(); $objValidation->setType(\PHPExcel\Cell\DataValidation::TYPE_WHOLE); $objValidation->setErrorStyle(\PHPExcel\Cell\DataValidation::STYLE_STOP); $objValidation->setAllowBlank(true); $objValidation->setShowInputMessage(true); $objValidation->setShowErrorMessage(true);
* @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); date_default_timezone_set('Europe/London'); if (PHP_SAPI == 'cli') { die('This example should only be run from a Web Browser'); } /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties $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"); // Add some data $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Hello')->setCellValue('B2', 'world!')->setCellValue('C1', 'Hello')->setCellValue('D2', 'world!'); // Miscellaneous glyphs, UTF-8 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A4', 'Miscellaneous glyphs')->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç'); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Simple'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client’s web browser (OpenDocument) header('Content-Type: application/vnd.oasis.opendocument.spreadsheet'); header('Content-Disposition: attachment;filename="01simple.ods"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed
* @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 PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // 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 data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Description')->setCellValue('B1', 'Amount'); $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Paycheck received')->setCellValue('B2', 100); $objPHPExcel->getActiveSheet()->setCellValue('A3', 'Cup of coffee bought')->setCellValue('B3', -1.5); $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Cup of coffee bought')->setCellValue('B4', -1.5); $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Cup of tea bought')->setCellValue('B5', -1.2); $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Found some money')->setCellValue('B6', 8); $objPHPExcel->getActiveSheet()->setCellValue('A7', 'Total:')->setCellValue('B7', '=SUM(B2:B6)'); // Set column widths echo date('H:i:s'), " Set column widths", EOL;
* 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__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // 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 data echo 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');
* @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); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("PHPOffice")->setLastModifiedBy("PHPOffice")->setTitle("PHPExcel Test Document")->setSubject("PHPExcel Test Document")->setDescription("Test document for PHPExcel, generated using PHP classes.")->setKeywords("Office PHPExcel php")->setCategory("Test result file"); function transpose($value) { return array($value); } // Add some data $continentColumn = 'D'; $column = 'F'; // Set data for dropdowns foreach (glob('./data/continents/*') as $key => $filename) { $continent = pathinfo($filename, PATHINFO_FILENAME); echo "Loading {$continent}", EOL; $continent = str_replace(' ', '_', $continent);
error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); mt_srand(1234567890); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // List functions echo date('H:i:s'), " List implemented functions", EOL; $objCalc = \PHPExcel\Calculation::getInstance(); print_r($objCalc->listFunctionNames()); // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Add some data, we will use some formulas here echo date('H:i:s'), " Add some data and formulas", EOL; $objPHPExcel->getActiveSheet()->setCellValue('A14', 'Count:')->setCellValue('A15', 'Sum:')->setCellValue('A16', 'Max:')->setCellValue('A17', 'Min:')->setCellValue('A18', 'Average:')->setCellValue('A19', 'Median:')->setCellValue('A20', 'Mode:'); $objPHPExcel->getActiveSheet()->setCellValue('A22', 'CountA:')->setCellValue('A23', 'MaxA:')->setCellValue('A24', 'MinA:'); $objPHPExcel->getActiveSheet()->setCellValue('A26', 'StDev:')->setCellValue('A27', 'StDevA:')->setCellValue('A28', 'StDevP:')->setCellValue('A29', 'StDevPA:'); $objPHPExcel->getActiveSheet()->setCellValue('A31', 'DevSq:')->setCellValue('A32', 'Var:')->setCellValue('A33', 'VarA:')->setCellValue('A34', 'VarP:')->setCellValue('A35', 'VarPA:'); $objPHPExcel->getActiveSheet()->setCellValue('A37', 'Date:'); $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Range 1')->setCellValue('B2', 2)->setCellValue('B3', 8)->setCellValue('B4', 10)->setCellValue('B5', True)->setCellValue('B6', False)->setCellValue('B7', 'Text String')->setCellValue('B9', '22')->setCellValue('B10', 4)->setCellValue('B11', 6)->setCellValue('B12', 12); $objPHPExcel->getActiveSheet()->setCellValue('B14', '=COUNT(B2:B12)')->setCellValue('B15', '=SUM(B2:B12)')->setCellValue('B16', '=MAX(B2:B12)')->setCellValue('B17', '=MIN(B2:B12)')->setCellValue('B18', '=AVERAGE(B2:B12)')->setCellValue('B19', '=MEDIAN(B2:B12)')->setCellValue('B20', '=MODE(B2:B12)'); $objPHPExcel->getActiveSheet()->setCellValue('B22', '=COUNTA(B2:B12)')->setCellValue('B23', '=MAXA(B2:B12)')->setCellValue('B24', '=MINA(B2:B12)'); $objPHPExcel->getActiveSheet()->setCellValue('B26', '=STDEV(B2:B12)')->setCellValue('B27', '=STDEVA(B2:B12)')->setCellValue('B28', '=STDEVP(B2:B12)')->setCellValue('B29', '=STDEVPA(B2:B12)'); $objPHPExcel->getActiveSheet()->setCellValue('B31', '=DEVSQ(B2:B12)')->setCellValue('B32', '=VAR(B2:B12)')->setCellValue('B33', '=VARA(B2:B12)')->setCellValue('B34', '=VARP(B2:B12)')->setCellValue('B35', '=VARPA(B2:B12)'); $objPHPExcel->getActiveSheet()->setCellValue('B37', '=DATE(2007, 12, 21)')->setCellValue('B38', '=DATEDIF( DATE(2007, 12, 21), DATE(2007, 12, 22), "D" )')->setCellValue('B39', '=DATEVALUE("01-Feb-2006 10:06 AM")')->setCellValue('B40', '=DAY( DATE(2006, 1, 2) )')->setCellValue('B41', '=DAYS360( DATE(2002, 2, 3), DATE(2005, 5, 31) )'); $objPHPExcel->getActiveSheet()->setCellValue('C1', 'Range 2')->setCellValue('C2', 1)->setCellValue('C3', 2)->setCellValue('C4', 2)->setCellValue('C5', 3)->setCellValue('C6', 3)->setCellValue('C7', 3)->setCellValue('C8', '0')->setCellValue('C9', 4)->setCellValue('C10', 4)->setCellValue('C11', 4)->setCellValue('C12', 4); $objPHPExcel->getActiveSheet()->setCellValue('C14', '=COUNT(C2:C12)')->setCellValue('C15', '=SUM(C2:C12)')->setCellValue('C16', '=MAX(C2:C12)')->setCellValue('C17', '=MIN(C2:C12)')->setCellValue('C18', '=AVERAGE(C2:C12)')->setCellValue('C19', '=MEDIAN(C2:C12)')->setCellValue('C20', '=MODE(C2:C12)');
* @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); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // 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"); // Add some data, we will use some formulas here echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Sum:'); $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Range #1')->setCellValue('B2', 3)->setCellValue('B3', 7)->setCellValue('B4', 13)->setCellValue('B5', '=SUM(B2:B4)'); echo date('H:i:s'), " Sum of Range #1 is ", $objPHPExcel->getActiveSheet()->getCell('B5')->getCalculatedValue(), EOL; $objPHPExcel->getActiveSheet()->setCellValue('C1', 'Range #2')->setCellValue('C2', 5)->setCellValue('C3', 11)->setCellValue('C4', 17)->setCellValue('C5', '=SUM(C2:C4)'); echo date('H:i:s'), " Sum of Range #2 is ", $objPHPExcel->getActiveSheet()->getCell('C5')->getCalculatedValue(), EOL; $objPHPExcel->getActiveSheet()->setCellValue('A7', 'Total of both ranges:'); $objPHPExcel->getActiveSheet()->setCellValue('B7', '=SUM(B5:C5)'); echo date('H:i:s'), " Sum of both Ranges is ", $objPHPExcel->getActiveSheet()->getCell('B7')->getCalculatedValue(), EOL; $objPHPExcel->getActiveSheet()->setCellValue('A8', 'Minimum of both ranges:'); $objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C4)');
* @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 PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // 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 echo date('H:i:s'), " Add data and page breaks", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', "Firstname")->setCellValue('B1', "Lastname")->setCellValue('C1', "Phone")->setCellValue('D1', "Fax")->setCellValue('E1', "Is Client ?"); // Add data for ($i = 2; $i <= 50; $i++) { $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, "FName {$i}"); $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, "LName {$i}"); $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, "PhoneNo {$i}"); $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, "FaxNo {$i}"); $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, true); // Add page breaks every 10 rows
* @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); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // 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"); // Add some data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0); $sharedStyle1 = new \PHPExcel\Style(); $sharedStyle2 = new \PHPExcel\Style(); $sharedStyle1->applyFromArray(array('fill' => array('type' => \PHPExcel\Style\Fill::FILL_SOLID, 'color' => array('argb' => 'FFCCFFCC')), 'borders' => array('bottom' => array('style' => \PHPExcel\Style\Border::BORDER_THIN), 'right' => array('style' => \PHPExcel\Style\Border::BORDER_MEDIUM)))); $sharedStyle2->applyFromArray(array('fill' => array('type' => \PHPExcel\Style\Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF00')), 'borders' => array('bottom' => array('style' => \PHPExcel\Style\Border::BORDER_THIN), 'right' => array('style' => \PHPExcel\Style\Border::BORDER_MEDIUM)))); $objPHPExcel->getActiveSheet()->duplicateStyle($sharedStyle1, "A1:T100"); $objPHPExcel->getActiveSheet()->duplicateStyle($sharedStyle2, "C5:R95"); // Save Excel 2007 file echo date('H:i:s'), " Write to Excel2007 format", EOL; $callStartTime = microtime(true);
* @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); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("PHPExcel Test Document")->setSubject("PHPExcel Test Document")->setDescription("Test document for PHPExcel, generated using PHP classes.")->setKeywords("office PHPExcel php")->setCategory("Test result file"); // Add some data echo date('H:i:s'), " Add some data", EOL; $html1 = '<font color="#0000ff"> <h1 align="center">My very first example of rich text<br />generated from html markup</h1> <p> <font size="14" COLOR="rgb(0,255,128)"> <b>This block</b> contains an <i>italicized</i> word; while this block uses an <u>underline</u>. </font> </p> <p align="right"><font size="9" color="red"> I want to eat <ins><del>healthy food</del> <strong>pizza</strong></ins>.
* @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); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // 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"); // Add some data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Firstname:')->setCellValue('A2', 'Lastname:')->setCellValue('A3', 'Fullname:')->setCellValue('B1', 'Maarten')->setCellValue('B2', 'Balliauw')->setCellValue('B3', '=B1 & " " & B2'); // Define named ranges echo date('H:i:s'), " Define named ranges", EOL; $objPHPExcel->addNamedRange(new \PHPExcel\NamedRange('PersonName', $objPHPExcel->getActiveSheet(), 'B1')); $objPHPExcel->addNamedRange(new \PHPExcel\NamedRange('PersonLN', $objPHPExcel->getActiveSheet(), 'B2')); // Rename named ranges echo date('H:i:s'), " Rename named ranges", EOL; $objPHPExcel->getNamedRange('PersonName')->setName('PersonFN'); // Rename worksheet
* @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); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // 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"); // Set default font echo date('H:i:s'), " Set default font", EOL; $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')->setSize(10); // Add some data, resembling some different data types echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->getActiveSheet()->setCellValue('A1', 'String')->setCellValue('B1', 'Simple')->setCellValue('C1', 'PHPExcel'); $objPHPExcel->getActiveSheet()->setCellValue('A2', 'String')->setCellValue('B2', 'Symbols')->setCellValue('C2', '!+&=()~§±æþ'); $objPHPExcel->getActiveSheet()->setCellValue('A3', 'String')->setCellValue('B3', 'UTF-8')->setCellValue('C3', 'Создать MS Excel Книги из PHP скриптов'); $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Number')->setCellValue('B4', 'Integer')->setCellValue('C4', 12); $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Number')->setCellValue('B5', 'Float')->setCellValue('C5', 34.56); $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Number')->setCellValue('B6', 'Negative')->setCellValue('C6', -7.89); $objPHPExcel->getActiveSheet()->setCellValue('A7', 'Boolean')->setCellValue('B7', 'True')->setCellValue('C7', true);
* @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); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("PHPOffice")->setLastModifiedBy("PHPOffice")->setTitle("PHPExcel Test Document")->setSubject("PHPExcel Test Document")->setDescription("Test document for PHPExcel, generated using PHP classes.")->setKeywords("Office PHPExcel php")->setCategory("Test result file"); // Add some data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Hello')->setCellValue('B2', 'world!'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Set the page layout view as page layout $objPHPExcel->getActiveSheet()->getSheetView()->setView(PHPExcel\Worksheet\SheetView::SHEETVIEW_PAGE_LAYOUT); // Save Excel 2007 file echo date('H:i:s'), " Write to Excel2007 format", EOL; $objWriter = \PHPExcel\IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); echo date('H:i:s'), " File written to ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;
/** * Loads Spreadsheet from file * * @param string $pFilename * @return Spreadsheet * @throws Exception */ public function load($pFilename) { // Check if file exists if (!file_exists($pFilename)) { throw new Exception("Could not open " . $pFilename . " for reading! File does not exist."); } // Initialisations $excel = new \PHPExcel\Spreadsheet(); $excel->removeSheetByIndex(0); if (!$this->readDataOnly) { $excel->removeCellStyleXfByIndex(0); // remove the default style $excel->removeCellXfByIndex(0); // remove the default style } $zipClass = \PHPExcel\Settings::getZipClass(); $zip = new $zipClass(); $zip->open($pFilename); // Read the theme first, because we need the colour scheme when reading the styles $wbRels = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "xl/_rels/workbook.xml.rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); foreach ($wbRels->Relationship as $rel) { switch ($rel["Type"]) { case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme": $themeOrderArray = array('lt1', 'dk1', 'lt2', 'dk2'); $themeOrderAdditional = count($themeOrderArray); $xmlTheme = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "xl/{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); if (is_object($xmlTheme)) { $xmlThemeName = $xmlTheme->attributes(); $xmlTheme = $xmlTheme->children("http://schemas.openxmlformats.org/drawingml/2006/main"); $themeName = (string) $xmlThemeName['name']; $colourScheme = $xmlTheme->themeElements->clrScheme->attributes(); $colourSchemeName = (string) $colourScheme['name']; $colourScheme = $xmlTheme->themeElements->clrScheme->children("http://schemas.openxmlformats.org/drawingml/2006/main"); $themeColours = array(); foreach ($colourScheme as $k => $xmlColour) { $themePos = array_search($k, $themeOrderArray); if ($themePos === false) { $themePos = $themeOrderAdditional++; } if (isset($xmlColour->sysClr)) { $xmlColourData = $xmlColour->sysClr->attributes(); $themeColours[$themePos] = $xmlColourData['lastClr']; } elseif (isset($xmlColour->srgbClr)) { $xmlColourData = $xmlColour->srgbClr->attributes(); $themeColours[$themePos] = $xmlColourData['val']; } } self::$theme = new Excel2007\Theme($themeName, $colourSchemeName, $themeColours); } break; } } $rels = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "_rels/.rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); foreach ($rels->Relationship as $rel) { switch ($rel["Type"]) { case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties": $xmlCore = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); if (is_object($xmlCore)) { $xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/"); $xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/"); $xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties"); $docProps = $excel->getProperties(); $docProps->setCreator((string) self::getArrayItem($xmlCore->xpath("dc:creator"))); $docProps->setLastModifiedBy((string) self::getArrayItem($xmlCore->xpath("cp:lastModifiedBy"))); $docProps->setCreated(strtotime(self::getArrayItem($xmlCore->xpath("dcterms:created")))); //! respect xsi:type $docProps->setModified(strtotime(self::getArrayItem($xmlCore->xpath("dcterms:modified")))); //! respect xsi:type $docProps->setTitle((string) self::getArrayItem($xmlCore->xpath("dc:title"))); $docProps->setDescription((string) self::getArrayItem($xmlCore->xpath("dc:description"))); $docProps->setSubject((string) self::getArrayItem($xmlCore->xpath("dc:subject"))); $docProps->setKeywords((string) self::getArrayItem($xmlCore->xpath("cp:keywords"))); $docProps->setCategory((string) self::getArrayItem($xmlCore->xpath("cp:category"))); } break; case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties": $xmlCore = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); if (is_object($xmlCore)) { $docProps = $excel->getProperties(); if (isset($xmlCore->Company)) { $docProps->setCompany((string) $xmlCore->Company); } if (isset($xmlCore->Manager)) { $docProps->setManager((string) $xmlCore->Manager); } } break; case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties": $xmlCore = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); if (is_object($xmlCore)) { $docProps = $excel->getProperties(); foreach ($xmlCore as $xmlProperty) { $cellDataOfficeAttributes = $xmlProperty->attributes(); if (isset($cellDataOfficeAttributes['name'])) { $propertyName = (string) $cellDataOfficeAttributes['name']; $cellDataOfficeChildren = $xmlProperty->children('http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes'); $attributeType = $cellDataOfficeChildren->getName(); $attributeValue = (string) $cellDataOfficeChildren->{$attributeType}; $attributeValue = \PHPExcel\Document\Properties::convertProperty($attributeValue, $attributeType); $attributeType = \PHPExcel\Document\Properties::convertPropertyType($attributeType); $docProps->setCustomProperty($propertyName, $attributeValue, $attributeType); } } } break; //Ribbon //Ribbon case "http://schemas.microsoft.com/office/2006/relationships/ui/extensibility": $customUI = $rel['Target']; if (!is_null($customUI)) { $this->readRibbon($excel, $customUI, $zip); } break; case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument": $dir = dirname($rel["Target"]); $relsWorkbook = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$dir}/_rels/" . basename($rel["Target"]) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships"); $sharedStrings = array(); $xpath = self::getArrayItem($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']")); $xmlStrings = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$dir}/{$xpath['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); if (isset($xmlStrings) && isset($xmlStrings->si)) { foreach ($xmlStrings->si as $val) { if (isset($val->t)) { $sharedStrings[] = \PHPExcel\Shared\StringHelper::controlCharacterOOXML2PHP((string) $val->t); } elseif (isset($val->r)) { $sharedStrings[] = $this->parseRichText($val); } } } $worksheets = array(); $macros = $customUI = null; foreach ($relsWorkbook->Relationship as $ele) { switch ($ele['Type']) { case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet": $worksheets[(string) $ele["Id"]] = $ele["Target"]; break; // a vbaProject ? (: some macros) // a vbaProject ? (: some macros) case "http://schemas.microsoft.com/office/2006/relationships/vbaProject": $macros = $ele["Target"]; break; } } if (!is_null($macros)) { $macrosCode = $this->getFromZipArchive($zip, 'xl/vbaProject.bin'); //vbaProject.bin always in 'xl' dir and always named vbaProject.bin if ($macrosCode !== false) { $excel->setMacrosCode($macrosCode); $excel->setHasMacros(true); //short-circuit : not reading vbaProject.bin.rel to get Signature =>allways vbaProjectSignature.bin in 'xl' dir $Certificate = $this->getFromZipArchive($zip, 'xl/vbaProjectSignature.bin'); if ($Certificate !== false) { $excel->setMacrosCertificate($Certificate); } } } $styles = array(); $cellStyles = array(); $xpath = self::getArrayItem($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']")); $xmlStyles = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$dir}/{$xpath['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $numFmts = null; if ($xmlStyles && $xmlStyles->numFmts[0]) { $numFmts = $xmlStyles->numFmts[0]; } if (isset($numFmts) && $numFmts !== null) { $numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main"); } if (!$this->readDataOnly && $xmlStyles) { foreach ($xmlStyles->cellXfs->xf as $xf) { $numFmt = \PHPExcel\Style\NumberFormat::FORMAT_GENERAL; if ($xf["numFmtId"]) { if (isset($numFmts)) { $tmpNumFmt = self::getArrayItem($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]")); if (isset($tmpNumFmt["formatCode"])) { $numFmt = (string) $tmpNumFmt["formatCode"]; } } // We shouldn't override any of the built-in MS Excel values (values below id 164) // But there's a lot of naughty homebrew xlsx writers that do use "reserved" id values that aren't actually used // So we make allowance for them rather than lose formatting masks if ((int) $xf["numFmtId"] < 164 && \PHPExcel\Style\NumberFormat::builtInFormatCode((int) $xf["numFmtId"]) !== '') { $numFmt = \PHPExcel\Style\NumberFormat::builtInFormatCode((int) $xf["numFmtId"]); } } $quotePrefix = false; if (isset($xf["quotePrefix"])) { $quotePrefix = (bool) $xf["quotePrefix"]; } $style = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection, "quotePrefix" => $quotePrefix); $styles[] = $style; // add style to cellXf collection $objStyle = new \PHPExcel\Style(); self::readStyle($objStyle, $style); $excel->addCellXf($objStyle); } foreach ($xmlStyles->cellStyleXfs->xf as $xf) { $numFmt = \PHPExcel\Style\NumberFormat::FORMAT_GENERAL; if ($numFmts && $xf["numFmtId"]) { $tmpNumFmt = self::getArrayItem($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]")); if (isset($tmpNumFmt["formatCode"])) { $numFmt = (string) $tmpNumFmt["formatCode"]; } elseif ((int) $xf["numFmtId"] < 165) { $numFmt = \PHPExcel\Style\NumberFormat::builtInFormatCode((int) $xf["numFmtId"]); } } $cellStyle = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection, "quotePrefix" => $quotePrefix); $cellStyles[] = $cellStyle; // add style to cellStyleXf collection $objStyle = new \PHPExcel\Style(); self::readStyle($objStyle, $cellStyle); $excel->addCellStyleXf($objStyle); } } $dxfs = array(); if (!$this->readDataOnly && $xmlStyles) { // Conditional Styles if ($xmlStyles->dxfs) { foreach ($xmlStyles->dxfs->dxf as $dxf) { $style = new \PHPExcel\Style(false, true); self::readStyle($style, $dxf); $dxfs[] = $style; } } // Cell Styles if ($xmlStyles->cellStyles) { foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) { if (intval($cellStyle['builtinId']) == 0) { if (isset($cellStyles[intval($cellStyle['xfId'])])) { // Set default style $style = new \PHPExcel\Style(); self::readStyle($style, $cellStyles[intval($cellStyle['xfId'])]); // normal style, currently not using it for anything } } } } } $xmlWorkbook = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); // Set base date if ($xmlWorkbook->workbookPr) { \PHPExcel\Shared\Date::setExcelCalendar(\PHPExcel\Shared\Date::CALENDAR_WINDOWS_1900); if (isset($xmlWorkbook->workbookPr['date1904'])) { if (self::boolean((string) $xmlWorkbook->workbookPr['date1904'])) { \PHPExcel\Shared\Date::setExcelCalendar(\PHPExcel\Shared\Date::CALENDAR_MAC_1904); } } } $sheetId = 0; // keep track of new sheet id in final workbook $oldSheetId = -1; // keep track of old sheet id in final workbook $countSkippedSheets = 0; // keep track of number of skipped sheets $mapSheetId = array(); // mapping of sheet ids from old to new $charts = $chartDetails = array(); if ($xmlWorkbook->sheets) { foreach ($xmlWorkbook->sheets->sheet as $eleSheet) { ++$oldSheetId; // Check if sheet should be skipped if (isset($this->loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->loadSheetsOnly)) { ++$countSkippedSheets; $mapSheetId[$oldSheetId] = null; continue; } // Map old sheet id in original workbook to new sheet id. // They will differ if loadSheetsOnly() is being used $mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets; // Load sheet $docSheet = $excel->createSheet(); // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet // references in formula cells... during the load, all formulae should be correct, // and we're simply bringing the worksheet name in line with the formula, not the // reverse $docSheet->setTitle((string) $eleSheet["name"], false); $fileWorksheet = $worksheets[(string) self::getArrayItem($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")]; $xmlSheet = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$dir}/{$fileWorksheet}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $sharedFormulas = array(); if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') { $docSheet->setSheetState((string) $eleSheet["state"]); } if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) { if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) { $docSheet->getSheetView()->setZoomScale(intval($xmlSheet->sheetViews->sheetView['zoomScale'])); } if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) { $docSheet->getSheetView()->setZoomScaleNormal(intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])); } if (isset($xmlSheet->sheetViews->sheetView['view'])) { $docSheet->getSheetView()->setView((string) $xmlSheet->sheetViews->sheetView['view']); } if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) { $docSheet->setShowGridLines(self::boolean((string) $xmlSheet->sheetViews->sheetView['showGridLines'])); } if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) { $docSheet->setShowRowColHeaders(self::boolean((string) $xmlSheet->sheetViews->sheetView['showRowColHeaders'])); } if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) { $docSheet->setRightToLeft(self::boolean((string) $xmlSheet->sheetViews->sheetView['rightToLeft'])); } if (isset($xmlSheet->sheetViews->sheetView->pane)) { if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) { $docSheet->freezePane((string) $xmlSheet->sheetViews->sheetView->pane['topLeftCell']); } else { $xSplit = 0; $ySplit = 0; if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) { $xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']); } if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) { $ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']); } $docSheet->freezePaneByColumnAndRow($xSplit, $ySplit); } } if (isset($xmlSheet->sheetViews->sheetView->selection)) { if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) { $sqref = (string) $xmlSheet->sheetViews->sheetView->selection['sqref']; $sqref = explode(' ', $sqref); $sqref = $sqref[0]; $docSheet->setSelectedCells($sqref); } } } if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) { if (isset($xmlSheet->sheetPr->tabColor['rgb'])) { $docSheet->getTabColor()->setARGB((string) $xmlSheet->sheetPr->tabColor['rgb']); } } if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr['codeName'])) { $docSheet->setCodeName((string) $xmlSheet->sheetPr['codeName']); } if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) { if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && !self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryRight'])) { $docSheet->setShowSummaryRight(false); } else { $docSheet->setShowSummaryRight(true); } if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && !self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryBelow'])) { $docSheet->setShowSummaryBelow(false); } else { $docSheet->setShowSummaryBelow(true); } } if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) { if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && !self::boolean((string) $xmlSheet->sheetPr->pageSetUpPr['fitToPage'])) { $docSheet->getPageSetup()->setFitToPage(false); } else { $docSheet->getPageSetup()->setFitToPage(true); } } if (isset($xmlSheet->sheetFormatPr)) { if (isset($xmlSheet->sheetFormatPr['customHeight']) && self::boolean((string) $xmlSheet->sheetFormatPr['customHeight']) && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) { $docSheet->getDefaultRowDimension()->setRowHeight((double) $xmlSheet->sheetFormatPr['defaultRowHeight']); } if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) { $docSheet->getDefaultColumnDimension()->setWidth((double) $xmlSheet->sheetFormatPr['defaultColWidth']); } if (isset($xmlSheet->sheetFormatPr['zeroHeight']) && (string) $xmlSheet->sheetFormatPr['zeroHeight'] == '1') { $docSheet->getDefaultRowDimension()->setZeroHeight(true); } } if (isset($xmlSheet->cols) && !$this->readDataOnly) { foreach ($xmlSheet->cols->col as $col) { for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) { if ($col["style"] && !$this->readDataOnly) { $docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"])); } if (self::boolean($col["bestFit"])) { //$docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setAutoSize(true); } if (self::boolean($col["hidden"])) { // echo \PHPExcel\Cell::stringFromColumnIndex($i), ': HIDDEN COLUMN',PHP_EOL; $docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setVisible(false); } if (self::boolean($col["collapsed"])) { $docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setCollapsed(true); } if ($col["outlineLevel"] > 0) { $docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"])); } $docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"])); if (intval($col["max"]) == 16384) { break; } } } } if (isset($xmlSheet->printOptions) && !$this->readDataOnly) { if (self::boolean((string) $xmlSheet->printOptions['gridLinesSet'])) { $docSheet->setShowGridlines(true); } if (self::boolean((string) $xmlSheet->printOptions['gridLines'])) { $docSheet->setPrintGridlines(true); } if (self::boolean((string) $xmlSheet->printOptions['horizontalCentered'])) { $docSheet->getPageSetup()->setHorizontalCentered(true); } if (self::boolean((string) $xmlSheet->printOptions['verticalCentered'])) { $docSheet->getPageSetup()->setVerticalCentered(true); } } if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) { foreach ($xmlSheet->sheetData->row as $row) { if ($row["ht"] && !$this->readDataOnly) { $docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"])); } if (self::boolean($row["hidden"]) && !$this->readDataOnly) { $docSheet->getRowDimension(intval($row["r"]))->setVisible(false); } if (self::boolean($row["collapsed"])) { $docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true); } if ($row["outlineLevel"] > 0) { $docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"])); } if ($row["s"] && !$this->readDataOnly) { $docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"])); } foreach ($row->c as $c) { $r = (string) $c["r"]; $cellDataType = (string) $c["t"]; $value = null; $calculatedValue = null; // Read cell? if ($this->getReadFilter() !== null) { $coordinates = \PHPExcel\Cell::coordinateFromString($r); if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) { continue; } } // echo 'Reading cell ', $coordinates[0], $coordinates[1], PHP_EOL; // print_r($c); // echo PHP_EOL; // echo 'Cell Data Type is ', $cellDataType, ': '; // // Read cell! switch ($cellDataType) { case "s": // echo 'String', PHP_EOL; if ((string) $c->v != '') { $value = $sharedStrings[intval($c->v)]; if ($value instanceof \PHPExcel\RichText) { $value = clone $value; } } else { $value = ''; } break; case "b": // echo 'Boolean', PHP_EOL; if (!isset($c->f)) { $value = self::castToBoolean($c); } else { // Formula $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToBoolean'); if (isset($c->f['t'])) { $att = array(); $att = $c->f; $docSheet->getCell($r)->setFormulaAttributes($att); } // echo '$calculatedValue = ', $calculatedValue, PHP_EOL; } break; case "inlineStr": // echo 'Inline String', PHP_EOL; if (isset($c->f)) { $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToError'); } else { $value = $this->parseRichText($c->is); } break; case "e": // echo 'Error', PHP_EOL; if (!isset($c->f)) { $value = self::castToError($c); } else { // Formula $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToError'); // echo '$calculatedValue = ', $calculatedValue, PHP_EOL; } break; default: // echo 'Default', PHP_EOL; if (!isset($c->f)) { // echo 'Not a Formula', PHP_EOL; $value = self::castToString($c); } else { // echo 'Treat as Formula', PHP_EOL; // Formula $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToString'); // echo '$calculatedValue = ', $calculatedValue, PHP_EOL; } break; } // echo 'Value is ', $value, PHP_EOL; // Check for numeric values if (is_numeric($value) && $cellDataType != 's') { if ($value == (int) $value) { $value = (int) $value; } elseif ($value == (double) $value) { $value = (double) $value; } elseif ($value == (double) $value) { $value = (double) $value; } } // Rich text? if ($value instanceof \PHPExcel\RichText && $this->readDataOnly) { $value = $value->getPlainText(); } $cell = $docSheet->getCell($r); // Assign value if ($cellDataType != '') { $cell->setValueExplicit($value, $cellDataType); } else { $cell->setValue($value); } if ($calculatedValue !== null) { $cell->setCalculatedValue($calculatedValue); } // Style information? if ($c["s"] && !$this->readDataOnly) { // no style index means 0, it seems $cell->setXfIndex(isset($styles[intval($c["s"])]) ? intval($c["s"]) : 0); } } } } $conditionals = array(); if (!$this->readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) { foreach ($xmlSheet->conditionalFormatting as $conditional) { foreach ($conditional->cfRule as $cfRule) { if (((string) $cfRule["type"] == \PHPExcel\Style\Conditional::CONDITION_NONE || (string) $cfRule["type"] == \PHPExcel\Style\Conditional::CONDITION_CELLIS || (string) $cfRule["type"] == \PHPExcel\Style\Conditional::CONDITION_CONTAINSTEXT || (string) $cfRule["type"] == \PHPExcel\Style\Conditional::CONDITION_EXPRESSION) && isset($dxfs[intval($cfRule["dxfId"])])) { $conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule; } } } foreach ($conditionals as $ref => $cfRules) { ksort($cfRules); $conditionalStyles = array(); foreach ($cfRules as $cfRule) { $objConditional = new \PHPExcel\Style\Conditional(); $objConditional->setConditionType((string) $cfRule["type"]); $objConditional->setOperatorType((string) $cfRule["operator"]); if ((string) $cfRule["text"] != '') { $objConditional->setText((string) $cfRule["text"]); } if (count($cfRule->formula) > 1) { foreach ($cfRule->formula as $formula) { $objConditional->addCondition((string) $formula); } } else { $objConditional->addCondition((string) $cfRule->formula); } $objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]); $conditionalStyles[] = $objConditional; } // Extract all cell references in $ref $cellBlocks = explode(' ', str_replace('$', '', strtoupper($ref))); foreach ($cellBlocks as $cellBlock) { $docSheet->getStyle($cellBlock)->setConditionalStyles($conditionalStyles); } } } $aKeys = ["sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells"]; if (!$this->readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) { foreach ($aKeys as $key) { $method = "set" . ucfirst($key); $docSheet->getProtection()->{$method}(self::boolean((string) $xmlSheet->sheetProtection[$key])); } } if (!$this->readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) { $docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true); if ($xmlSheet->protectedRanges->protectedRange) { foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) { $docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true); } } } if ($xmlSheet && $xmlSheet->autoFilter && !$this->readDataOnly) { $autoFilterRange = (string) $xmlSheet->autoFilter["ref"]; if (strpos($autoFilterRange, ':') !== false) { $autoFilter = $docSheet->getAutoFilter(); $autoFilter->setRange($autoFilterRange); foreach ($xmlSheet->autoFilter->filterColumn as $filterColumn) { $column = $autoFilter->getColumnByOffset((int) $filterColumn["colId"]); // Check for standard filters if ($filterColumn->filters) { $column->setFilterType(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_FILTER); $filters = $filterColumn->filters; if (isset($filters["blank"]) && $filters["blank"] == 1) { // Operator is undefined, but always treated as EQUAL $column->createRule()->setRule(null, '')->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_FILTER); } // Standard filters are always an OR join, so no join rule needs to be set // Entries can be either filter elements foreach ($filters->filter as $filterRule) { // Operator is undefined, but always treated as EQUAL $column->createRule()->setRule(null, (string) $filterRule["val"])->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_FILTER); } // Or Date Group elements foreach ($filters->dateGroupItem as $dateGroupItem) { $column->createRule()->setRule(null, array('year' => (string) $dateGroupItem["year"], 'month' => (string) $dateGroupItem["month"], 'day' => (string) $dateGroupItem["day"], 'hour' => (string) $dateGroupItem["hour"], 'minute' => (string) $dateGroupItem["minute"], 'second' => (string) $dateGroupItem["second"]), (string) $dateGroupItem["dateTimeGrouping"])->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP); } } // Check for custom filters if ($filterColumn->customFilters) { $column->setFilterType(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER); $customFilters = $filterColumn->customFilters; // Custom filters can an AND or an OR join; // and there should only ever be one or two entries if (isset($customFilters["and"]) && $customFilters["and"] == 1) { $column->setJoin(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND); } foreach ($customFilters->customFilter as $filterRule) { $column->createRule()->setRule((string) $filterRule["operator"], (string) $filterRule["val"])->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); } } // Check for dynamic filters if ($filterColumn->dynamicFilter) { $column->setFilterType(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER); // We should only ever have one dynamic filter foreach ($filterColumn->dynamicFilter as $filterRule) { $column->createRule()->setRule(null, (string) $filterRule["val"], (string) $filterRule["type"])->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER); if (isset($filterRule["val"])) { $column->setAttribute('val', (string) $filterRule["val"]); } if (isset($filterRule["maxVal"])) { $column->setAttribute('maxVal', (string) $filterRule["maxVal"]); } } } // Check for dynamic filters if ($filterColumn->top10) { $column->setFilterType(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER); // We should only ever have one top10 filter foreach ($filterColumn->top10 as $filterRule) { $column->createRule()->setRule(isset($filterRule["percent"]) && $filterRule["percent"] == 1 ? \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT : \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE, (string) $filterRule["val"], isset($filterRule["top"]) && $filterRule["top"] == 1 ? \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP : \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM)->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_TOPTENFILTER); } } } } } if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->readDataOnly) { foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) { $mergeRef = (string) $mergeCell["ref"]; if (strpos($mergeRef, ':') !== false) { $docSheet->mergeCells((string) $mergeCell["ref"]); } } } if ($xmlSheet && $xmlSheet->pageMargins && !$this->readDataOnly) { $docPageMargins = $docSheet->getPageMargins(); $docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"])); $docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"])); $docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"])); $docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"])); $docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"])); $docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"])); } if ($xmlSheet && $xmlSheet->pageSetup && !$this->readDataOnly) { $docPageSetup = $docSheet->getPageSetup(); if (isset($xmlSheet->pageSetup["orientation"])) { $docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]); } if (isset($xmlSheet->pageSetup["paperSize"])) { $docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"])); } if (isset($xmlSheet->pageSetup["scale"])) { $docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), false); } if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) { $docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), false); } if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) { $docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), false); } if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) && self::boolean((string) $xmlSheet->pageSetup["useFirstPageNumber"])) { $docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"])); } } if ($xmlSheet && $xmlSheet->headerFooter && !$this->readDataOnly) { $docHeaderFooter = $docSheet->getHeaderFooter(); if (isset($xmlSheet->headerFooter["differentOddEven"]) && self::boolean((string) $xmlSheet->headerFooter["differentOddEven"])) { $docHeaderFooter->setDifferentOddEven(true); } else { $docHeaderFooter->setDifferentOddEven(false); } if (isset($xmlSheet->headerFooter["differentFirst"]) && self::boolean((string) $xmlSheet->headerFooter["differentFirst"])) { $docHeaderFooter->setDifferentFirst(true); } else { $docHeaderFooter->setDifferentFirst(false); } if (isset($xmlSheet->headerFooter["scaleWithDoc"]) && !self::boolean((string) $xmlSheet->headerFooter["scaleWithDoc"])) { $docHeaderFooter->setScaleWithDocument(false); } else { $docHeaderFooter->setScaleWithDocument(true); } if (isset($xmlSheet->headerFooter["alignWithMargins"]) && !self::boolean((string) $xmlSheet->headerFooter["alignWithMargins"])) { $docHeaderFooter->setAlignWithMargins(false); } else { $docHeaderFooter->setAlignWithMargins(true); } $docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader); $docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter); $docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader); $docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter); $docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader); $docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter); } if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->readDataOnly) { foreach ($xmlSheet->rowBreaks->brk as $brk) { if ($brk["man"]) { $docSheet->setBreak("A{$brk['id']}", \PHPExcel\Worksheet::BREAK_ROW); } } } if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->readDataOnly) { foreach ($xmlSheet->colBreaks->brk as $brk) { if ($brk["man"]) { $docSheet->setBreak(\PHPExcel\Cell::stringFromColumnIndex((string) $brk["id"]) . "1", \PHPExcel\Worksheet::BREAK_COLUMN); } } } if ($xmlSheet && $xmlSheet->dataValidations && !$this->readDataOnly) { foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) { // Uppercase coordinate $range = strtoupper($dataValidation["sqref"]); $rangeSet = explode(' ', $range); foreach ($rangeSet as $range) { $stRange = $docSheet->shrinkRangeToFit($range); // Extract all cell references in $range foreach (\PHPExcel\Cell::extractAllCellReferencesInRange($stRange) as $reference) { // Create validation $docValidation = $docSheet->getCell($reference)->getDataValidation(); $docValidation->setType((string) $dataValidation["type"]); $docValidation->setErrorStyle((string) $dataValidation["errorStyle"]); $docValidation->setOperator((string) $dataValidation["operator"]); $docValidation->setAllowBlank($dataValidation["allowBlank"] != 0); $docValidation->setShowDropDown($dataValidation["showDropDown"] == 0); $docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0); $docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0); $docValidation->setErrorTitle((string) $dataValidation["errorTitle"]); $docValidation->setError((string) $dataValidation["error"]); $docValidation->setPromptTitle((string) $dataValidation["promptTitle"]); $docValidation->setPrompt((string) $dataValidation["prompt"]); $docValidation->setFormula1((string) $dataValidation->formula1); $docValidation->setFormula2((string) $dataValidation->formula2); } } } } // Add hyperlinks $hyperlinks = array(); if (!$this->readDataOnly) { // Locate hyperlink relations if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") { $hyperlinks[(string) $ele["Id"]] = (string) $ele["Target"]; } } } // Loop through hyperlinks if ($xmlSheet && $xmlSheet->hyperlinks) { foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) { // Link url $linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships'); foreach (\PHPExcel\Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) { $cell = $docSheet->getCell($cellReference); if (isset($linkRel['id'])) { $hyperlinkUrl = $hyperlinks[(string) $linkRel['id']]; if (isset($hyperlink['location'])) { $hyperlinkUrl .= '#' . (string) $hyperlink['location']; } $cell->getHyperlink()->setUrl($hyperlinkUrl); } elseif (isset($hyperlink['location'])) { $cell->getHyperlink()->setUrl('sheet://' . (string) $hyperlink['location']); } // Tooltip if (isset($hyperlink['tooltip'])) { $cell->getHyperlink()->setTooltip((string) $hyperlink['tooltip']); } } } } } // Add comments $comments = array(); $vmlComments = array(); if (!$this->readDataOnly) { // Locate comment relations if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") { $comments[(string) $ele["Id"]] = (string) $ele["Target"]; } if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") { $vmlComments[(string) $ele["Id"]] = (string) $ele["Target"]; } } } // Loop through comments foreach ($comments as $relName => $relPath) { // Load comments file $relPath = \PHPExcel\Shared\File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath); $commentsFile = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $relPath)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); // Utility variables $authors = array(); // Loop through authors foreach ($commentsFile->authors->author as $author) { $authors[] = (string) $author; } // Loop through contents foreach ($commentsFile->commentList->comment as $comment) { if (!empty($comment['authorId'])) { $docSheet->getComment((string) $comment['ref'])->setAuthor($authors[(string) $comment['authorId']]); } $docSheet->getComment((string) $comment['ref'])->setText($this->parseRichText($comment->text)); } } // Loop through VML comments foreach ($vmlComments as $relName => $relPath) { // Load VML comments file $relPath = \PHPExcel\Shared\File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath); $vmlCommentsFile = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $relPath)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); $shapes = $vmlCommentsFile->xpath('//v:shape'); foreach ($shapes as $shape) { $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); if (isset($shape['style'])) { $style = (string) $shape['style']; $fillColor = strtoupper(substr((string) $shape['fillcolor'], 1)); $column = null; $row = null; $clientData = $shape->xpath('.//x:ClientData'); if (is_array($clientData) && !empty($clientData)) { $clientData = $clientData[0]; if (isset($clientData['ObjectType']) && (string) $clientData['ObjectType'] == 'Note') { $temp = $clientData->xpath('.//x:Row'); if (is_array($temp)) { $row = $temp[0]; } $temp = $clientData->xpath('.//x:Column'); if (is_array($temp)) { $column = $temp[0]; } } } if ($column !== null && $row !== null) { // Set comment properties $comment = $docSheet->getCommentByColumnAndRow((string) $column, $row + 1); $comment->getFillColor()->setRGB($fillColor); // Parse style $styleArray = explode(';', str_replace(' ', '', $style)); foreach ($styleArray as $stylePair) { $stylePair = explode(':', $stylePair); if ($stylePair[0] == 'margin-left') { $comment->setMarginLeft($stylePair[1]); } if ($stylePair[0] == 'margin-top') { $comment->setMarginTop($stylePair[1]); } if ($stylePair[0] == 'width') { $comment->setWidth($stylePair[1]); } if ($stylePair[0] == 'height') { $comment->setHeight($stylePair[1]); } if ($stylePair[0] == 'visibility') { $comment->setVisible($stylePair[1] == 'visible'); } } } } } } // Header/footer images if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->readDataOnly) { if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $vmlRelationship = ''; foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") { $vmlRelationship = self::dirAdd("{$dir}/{$fileWorksheet}", $ele["Target"]); } } if ($vmlRelationship != '') { // Fetch linked images $relsVML = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels')), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $drawings = array(); foreach ($relsVML->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") { $drawings[(string) $ele["Id"]] = self::dirAdd($vmlRelationship, $ele["Target"]); } } // Fetch VML document $vmlDrawing = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $vmlRelationship)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); $hfImages = array(); $shapes = $vmlDrawing->xpath('//v:shape'); foreach ($shapes as $idx => $shape) { $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); $imageData = $shape->xpath('//v:imagedata'); $imageData = $imageData[$idx]; $imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office'); $style = self::toCSSArray((string) $shape['style']); $hfImages[(string) $shape['id']] = new \PHPExcel\Worksheet\HeaderFooterDrawing(); if (isset($imageData['title'])) { $hfImages[(string) $shape['id']]->setName((string) $imageData['title']); } $hfImages[(string) $shape['id']]->setPath("zip://" . \PHPExcel\Shared_File::realpath($pFilename) . "#" . $drawings[(string) $imageData['relid']], false); $hfImages[(string) $shape['id']]->setResizeProportional(false); $hfImages[(string) $shape['id']]->setWidth($style['width']); $hfImages[(string) $shape['id']]->setHeight($style['height']); if (isset($style['margin-left'])) { $hfImages[(string) $shape['id']]->setOffsetX($style['margin-left']); } $hfImages[(string) $shape['id']]->setOffsetY($style['margin-top']); $hfImages[(string) $shape['id']]->setResizeProportional(true); } $docSheet->getHeaderFooter()->setImages($hfImages); } } } } // TODO: Autoshapes from twoCellAnchors! if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $drawings = array(); foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") { $drawings[(string) $ele["Id"]] = self::dirAdd("{$dir}/{$fileWorksheet}", $ele["Target"]); } } if ($xmlSheet->drawing && !$this->readDataOnly) { foreach ($xmlSheet->drawing as $drawing) { $fileDrawing = $drawings[(string) self::getArrayItem($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")]; $relsDrawing = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $images = array(); if ($relsDrawing && $relsDrawing->Relationship) { foreach ($relsDrawing->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") { $images[(string) $ele["Id"]] = self::dirAdd($fileDrawing, $ele["Target"]); } elseif ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart") { if ($this->includeCharts) { $charts[self::dirAdd($fileDrawing, $ele["Target"])] = array('id' => (string) $ele["Id"], 'sheet' => $docSheet->getTitle()); } } } } $xmlDrawing = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $fileDrawing)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions())->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"); if ($xmlDrawing->oneCellAnchor) { foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) { if ($oneCellAnchor->pic->blipFill) { $blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip; $xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm; $outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw; $objDrawing = new \PHPExcel\Worksheet\Drawing(); $objDrawing->setName((string) self::getArrayItem($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name")); $objDrawing->setDescription((string) self::getArrayItem($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr")); $objDrawing->setPath("zip://" . \PHPExcel\Shared\File::realpath($pFilename) . "#" . $images[(string) self::getArrayItem($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false); $objDrawing->setCoordinates(\PHPExcel\Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1)); $objDrawing->setOffsetX(\PHPExcel\Shared\Drawing::EMUToPixels($oneCellAnchor->from->colOff)); $objDrawing->setOffsetY(\PHPExcel\Shared\Drawing::EMUToPixels($oneCellAnchor->from->rowOff)); $objDrawing->setResizeProportional(false); $objDrawing->setWidth(\PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($oneCellAnchor->ext->attributes(), "cx"))); $objDrawing->setHeight(\PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($oneCellAnchor->ext->attributes(), "cy"))); if ($xfrm) { $objDrawing->setRotation(\PHPExcel\Shared\Drawing::angleToDegrees(self::getArrayItem($xfrm->attributes(), "rot"))); } if ($outerShdw) { $shadow = $objDrawing->getShadow(); $shadow->setVisible(true); $shadow->setBlurRadius(\PHPExcel\Shared\Drawing::EMUTopixels(self::getArrayItem($outerShdw->attributes(), "blurRad"))); $shadow->setDistance(\PHPExcel\Shared\Drawing::EMUTopixels(self::getArrayItem($outerShdw->attributes(), "dist"))); $shadow->setDirection(\PHPExcel\Shared\Drawing::angleToDegrees(self::getArrayItem($outerShdw->attributes(), "dir"))); $shadow->setAlignment((string) self::getArrayItem($outerShdw->attributes(), "algn")); $shadow->getColor()->setRGB(self::getArrayItem($outerShdw->srgbClr->attributes(), "val")); $shadow->setAlpha(self::getArrayItem($outerShdw->srgbClr->alpha->attributes(), "val") / 1000); } $objDrawing->setWorksheet($docSheet); } else { // ? Can charts be positioned with a oneCellAnchor ? $coordinates = \PHPExcel\Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1); $offsetX = \PHPExcel\Shared\Drawing::EMUToPixels($oneCellAnchor->from->colOff); $offsetY = \PHPExcel\Shared\Drawing::EMUToPixels($oneCellAnchor->from->rowOff); $width = \PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($oneCellAnchor->ext->attributes(), "cx")); $height = \PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($oneCellAnchor->ext->attributes(), "cy")); } } } if ($xmlDrawing->twoCellAnchor) { foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) { if ($twoCellAnchor->pic->blipFill) { $blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip; $xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm; $outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw; $objDrawing = new \PHPExcel\Worksheet\Drawing(); $objDrawing->setName((string) self::getArrayItem($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name")); $objDrawing->setDescription((string) self::getArrayItem($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr")); $objDrawing->setPath("zip://" . \PHPExcel\Shared\File::realpath($pFilename) . "#" . $images[(string) self::getArrayItem($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false); $objDrawing->setCoordinates(\PHPExcel\Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1)); $objDrawing->setOffsetX(\PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->from->colOff)); $objDrawing->setOffsetY(\PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->from->rowOff)); $objDrawing->setResizeProportional(false); if ($xfrm) { $objDrawing->setWidth(\PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($xfrm->ext->attributes(), "cx"))); $objDrawing->setHeight(\PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($xfrm->ext->attributes(), "cy"))); $objDrawing->setRotation(\PHPExcel\Shared\Drawing::angleToDegrees(self::getArrayItem($xfrm->attributes(), "rot"))); } if ($outerShdw) { $shadow = $objDrawing->getShadow(); $shadow->setVisible(true); $shadow->setBlurRadius(\PHPExcel\Shared\Drawing::EMUTopixels(self::getArrayItem($outerShdw->attributes(), "blurRad"))); $shadow->setDistance(\PHPExcel\Shared\Drawing::EMUTopixels(self::getArrayItem($outerShdw->attributes(), "dist"))); $shadow->setDirection(\PHPExcel\Shared\Drawing::angleToDegrees(self::getArrayItem($outerShdw->attributes(), "dir"))); $shadow->setAlignment((string) self::getArrayItem($outerShdw->attributes(), "algn")); $shadow->getColor()->setRGB(self::getArrayItem($outerShdw->srgbClr->attributes(), "val")); $shadow->setAlpha(self::getArrayItem($outerShdw->srgbClr->alpha->attributes(), "val") / 1000); } $objDrawing->setWorksheet($docSheet); } elseif ($this->includeCharts && $twoCellAnchor->graphicFrame) { $fromCoordinate = \PHPExcel\Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1); $fromOffsetX = \PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->from->colOff); $fromOffsetY = \PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->from->rowOff); $toCoordinate = \PHPExcel\Cell::stringFromColumnIndex((string) $twoCellAnchor->to->col) . ($twoCellAnchor->to->row + 1); $toOffsetX = \PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->to->colOff); $toOffsetY = \PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->to->rowOff); $graphic = $twoCellAnchor->graphicFrame->children("http://schemas.openxmlformats.org/drawingml/2006/main")->graphic; $chartRef = $graphic->graphicData->children("http://schemas.openxmlformats.org/drawingml/2006/chart")->chart; $thisChart = (string) $chartRef->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"); $chartDetails[$docSheet->getTitle() . '!' . $thisChart] = array('fromCoordinate' => $fromCoordinate, 'fromOffsetX' => $fromOffsetX, 'fromOffsetY' => $fromOffsetY, 'toCoordinate' => $toCoordinate, 'toOffsetX' => $toOffsetX, 'toOffsetY' => $toOffsetY, 'worksheetTitle' => $docSheet->getTitle()); } } } } } } // Loop through definedNames if ($xmlWorkbook->definedNames) { foreach ($xmlWorkbook->definedNames->definedName as $definedName) { // Extract range $extractedRange = (string) $definedName; $extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange); if (($spos = strpos($extractedRange, '!')) !== false) { $extractedRange = substr($extractedRange, 0, $spos) . str_replace('$', '', substr($extractedRange, $spos)); } else { $extractedRange = str_replace('$', '', $extractedRange); } // Valid range? if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') { continue; } // Some definedNames are only applicable if we are on the same sheet... if ((string) $definedName['localSheetId'] != '' && (string) $definedName['localSheetId'] == $sheetId) { // Switch on type switch ((string) $definedName['name']) { case '_xlnm._FilterDatabase': if ((string) $definedName['hidden'] !== '1') { $extractedRange = explode(',', $extractedRange); foreach ($extractedRange as $range) { $autoFilterRange = $range; if (strpos($autoFilterRange, ':') !== false) { $docSheet->getAutoFilter()->setRange($autoFilterRange); } } } break; case '_xlnm.Print_Titles': // Split $extractedRange $extractedRange = explode(',', $extractedRange); // Set print titles foreach ($extractedRange as $range) { $matches = array(); $range = str_replace('$', '', $range); // check for repeating columns, e g. 'A:A' or 'A:D' if (preg_match('/!?([A-Z]+)\\:([A-Z]+)$/', $range, $matches)) { $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($matches[1], $matches[2])); } elseif (preg_match('/!?(\\d+)\\:(\\d+)$/', $range, $matches)) { // check for repeating rows, e.g. '1:1' or '1:5' $docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2])); } } break; case '_xlnm.Print_Area': $rangeSets = explode(',', $extractedRange); // FIXME: what if sheetname contains comma? $newRangeSets = array(); foreach ($rangeSets as $rangeSet) { $range = explode('!', $rangeSet); // FIXME: what if sheetname contains exclamation mark? $rangeSet = isset($range[1]) ? $range[1] : $range[0]; if (strpos($rangeSet, ':') === false) { $rangeSet = $rangeSet . ':' . $rangeSet; } $newRangeSets[] = str_replace('$', '', $rangeSet); } $docSheet->getPageSetup()->setPrintArea(implode(',', $newRangeSets)); break; default: break; } } } } // Next sheet id ++$sheetId; } // Loop through definedNames if ($xmlWorkbook->definedNames) { foreach ($xmlWorkbook->definedNames->definedName as $definedName) { // Extract range $extractedRange = (string) $definedName; $extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange); if (($spos = strpos($extractedRange, '!')) !== false) { $extractedRange = substr($extractedRange, 0, $spos) . str_replace('$', '', substr($extractedRange, $spos)); } else { $extractedRange = str_replace('$', '', $extractedRange); } // Valid range? if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') { continue; } // Some definedNames are only applicable if we are on the same sheet... if ((string) $definedName['localSheetId'] != '') { // Local defined name // Switch on type switch ((string) $definedName['name']) { case '_xlnm._FilterDatabase': case '_xlnm.Print_Titles': case '_xlnm.Print_Area': break; default: if ($mapSheetId[(int) $definedName['localSheetId']] !== null) { $range = explode('!', (string) $definedName); if (count($range) == 2) { $range[0] = str_replace("''", "'", $range[0]); $range[0] = str_replace("'", "", $range[0]); if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) { $extractedRange = str_replace('$', '', $range[1]); $scope = $docSheet->getParent()->getSheet($mapSheetId[(int) $definedName['localSheetId']]); $excel->addNamedRange(new \PHPExcel\NamedRange((string) $definedName['name'], $worksheet, $extractedRange, true, $scope)); } } } break; } } elseif (!isset($definedName['localSheetId'])) { // "Global" definedNames $locatedSheet = null; $extractedSheetName = ''; if (strpos((string) $definedName, '!') !== false) { // Extract sheet name $extractedSheetName = \PHPExcel\Worksheet::extractSheetTitle((string) $definedName, true); $extractedSheetName = $extractedSheetName[0]; // Locate sheet $locatedSheet = $excel->getSheetByName($extractedSheetName); // Modify range $range = explode('!', $extractedRange); $extractedRange = isset($range[1]) ? $range[1] : $range[0]; } if ($locatedSheet !== null) { $excel->addNamedRange(new \PHPExcel\NamedRange((string) $definedName['name'], $locatedSheet, $extractedRange, false)); } } } } } if (!$this->readDataOnly || !empty($this->loadSheetsOnly)) { // active sheet index $activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]); // refers to old sheet index // keep active sheet index if sheet is still loaded, else first sheet is set as the active if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) { $excel->setActiveSheetIndex($mapSheetId[$activeTab]); } else { if ($excel->getSheetCount() == 0) { $excel->createSheet(); } $excel->setActiveSheetIndex(0); } } break; } } if (!$this->readDataOnly) { $contentTypes = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "[Content_Types].xml")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); foreach ($contentTypes->Override as $contentType) { switch ($contentType["ContentType"]) { case "application/vnd.openxmlformats-officedocument.drawingml.chart+xml": if ($this->includeCharts) { $chartEntryRef = ltrim($contentType['PartName'], '/'); $chartElements = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $chartEntryRef)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $objChart = \PHPExcel\Reader\Excel2007\Chart::readChart($chartElements, basename($chartEntryRef, '.xml')); // echo 'Chart ', $chartEntryRef, '<br />'; // var_dump($charts[$chartEntryRef]); // if (isset($charts[$chartEntryRef])) { $chartPositionRef = $charts[$chartEntryRef]['sheet'] . '!' . $charts[$chartEntryRef]['id']; // echo 'Position Ref ', $chartPositionRef, '<br />'; if (isset($chartDetails[$chartPositionRef])) { // var_dump($chartDetails[$chartPositionRef]); $excel->getSheetByName($charts[$chartEntryRef]['sheet'])->addChart($objChart); $objChart->setWorksheet($excel->getSheetByName($charts[$chartEntryRef]['sheet'])); $objChart->setTopLeftPosition($chartDetails[$chartPositionRef]['fromCoordinate'], $chartDetails[$chartPositionRef]['fromOffsetX'], $chartDetails[$chartPositionRef]['fromOffsetY']); $objChart->setBottomRightPosition($chartDetails[$chartPositionRef]['toCoordinate'], $chartDetails[$chartPositionRef]['toOffsetX'], $chartDetails[$chartPositionRef]['toOffsetY']); } } } } } } $zip->close(); return $excel; }
/** Error reporting */ error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // List functions echo date('H:i:s') . " List implemented functions\n"; $objCalc = \PHPExcel\Calculation::getInstance(); print_r($objCalc->listFunctionNames()); // Create new PHPExcel object echo date('H:i:s') . " Create new PHPExcel object\n"; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Add some data, we will use some formulas here echo date('H:i:s') . " Add some data\n"; $objPHPExcel->getActiveSheet()->setCellValue('A14', 'Count:'); $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Range 1'); $objPHPExcel->getActiveSheet()->setCellValue('B2', 2); $objPHPExcel->getActiveSheet()->setCellValue('B3', 8); $objPHPExcel->getActiveSheet()->setCellValue('B4', 10); $objPHPExcel->getActiveSheet()->setCellValue('B5', True); $objPHPExcel->getActiveSheet()->setCellValue('B6', False); $objPHPExcel->getActiveSheet()->setCellValue('B7', 'Text String'); $objPHPExcel->getActiveSheet()->setCellValue('B9', '22'); $objPHPExcel->getActiveSheet()->setCellValue('B10', 4); $objPHPExcel->getActiveSheet()->setCellValue('B11', 6); $objPHPExcel->getActiveSheet()->setCellValue('B12', 12); $objPHPExcel->getActiveSheet()->setCellValue('B14', '=COUNT(B2:B12)');
* @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); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("PHPExcel Test Document")->setSubject("PHPExcel Test Document")->setDescription("Test document for PHPExcel, generated using PHP classes.")->setKeywords("office PHPExcel php")->setCategory("Test result file"); // Add some data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Hello')->setCellValue('B2', 'world!')->setCellValue('C1', 'Hello')->setCellValue('D2', 'world!'); // Miscellaneous glyphs, UTF-8 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A4', 'Miscellaneous glyphs')->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç'); $objPHPExcel->getActiveSheet()->setCellValue('A8', "Hello\nWorld"); $objPHPExcel->getActiveSheet()->getRowDimension(8)->setRowHeight(-1); $objPHPExcel->getActiveSheet()->getStyle('A8')->getAlignment()->setWrapText(true); // Rename worksheet echo date('H:i:s'), " Rename worksheet", EOL; $objPHPExcel->getActiveSheet()->setTitle('Simple'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet
* @copyright Copyright (c) 2006 - 2014 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); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Add some data, we will use some formulas here echo date('H:i:s'), " Add some data and formulas", EOL; $objPHPExcel->getActiveSheet()->setCellValue('A1', '=B1')->setCellValue('A2', '=B2+1')->setCellValue('B1', '=A1+1')->setCellValue('B2', '=A2'); \PHPExcel\Calculation::getInstance($objPHPExcel)->cyclicFormulaCount = 100; // Calculated data echo date('H:i:s'), " Calculated data", EOL; for ($row = 1; $row <= 2; ++$row) { for ($col = 'A'; $col != 'C'; ++$col) { if (!is_null($formula = $objPHPExcel->getActiveSheet()->getCell($col . $row)->getValue()) && $formula[0] == '=') { echo 'Value of ', $col, $row, ' [', $formula, ']: ', $objPHPExcel->getActiveSheet()->getCell($col . $row)->getCalculatedValue() . EOL; } } } // Save Excel 2007 file echo date('H:i:s'), " Write to Excel2007 format", EOL;
* @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 PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // 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"); // Add some data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello'); $objPHPExcel->getActiveSheet()->setCellValue('B2', 'world!'); $objPHPExcel->getActiveSheet()->setCellValue('C1', 'Hello'); $objPHPExcel->getActiveSheet()->setCellValue('D2', 'world!'); // Rename worksheet echo date('H:i:s'), " Rename worksheet", EOL; $objPHPExcel->getActiveSheet()->setTitle('Simple'); // Set document security echo date('H:i:s'), " Set document security", EOL;
* @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); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s') . ' Create new PHPExcel object' . EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s') . ' Set document properties' . EOL; $objPHPExcel->getProperties()->setCreator('Maarten Balliauw')->setLastModifiedBy('Maarten Balliauw')->setTitle('PHPExcel Test Document')->setSubject('PHPExcel Test Document')->setDescription('Test document for PHPExcel, generated using PHP classes.')->setKeywords('office PHPExcel php')->setCategory('Test result file'); // Create the worksheet echo date('H:i:s') . ' Add data' . EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Financial Year')->setCellValue('B1', 'Financial Period')->setCellValue('C1', 'Country')->setCellValue('D1', 'Date')->setCellValue('E1', 'Sales Value')->setCellValue('F1', 'Expenditure'); $startYear = $endYear = $currentYear = date('Y'); $startYear--; $endYear++; $years = range($startYear, $endYear); $periods = range(1, 12); $countries = array('United States', 'UK', 'France', 'Germany', 'Italy', 'Spain', 'Portugal', 'Japan'); $row = 2; foreach ($years as $year) {
* @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 PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // 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"); // Generate an image echo date('H:i:s'), " Generate an image", EOL; $gdImage = @imagecreatetruecolor(120, 20) or die('Cannot Initialize new GD image stream'); $textColor = imagecolorallocate($gdImage, 255, 255, 255); imagestring($gdImage, 1, 5, 5, 'Created with PHPExcel', $textColor); // Add a drawing to the worksheet echo date('H:i:s'), " Add a drawing to the worksheet", EOL; $objDrawing = new \PHPExcel\Worksheet\MemoryDrawing(); $objDrawing->setName('Sample image'); $objDrawing->setDescription('Sample image'); $objDrawing->setImageResource($gdImage); $objDrawing->setRenderingFunction(\PHPExcel\Worksheet\MemoryDrawing::RENDERING_JPEG);
* 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## */ /** PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; $objPHPExcel = new \PHPExcel\Spreadsheet(); $objWorksheet = $objPHPExcel->getActiveSheet(); $objWorksheet->fromArray(array(array('', 2010, 2011, 2012), array('Q1', 12, 15, 21), array('Q2', 56, 73, 86), array('Q3', 52, 61, 69), array('Q4', 30, 32, 0))); // Set the Labels for each data series we want to plot // Datatype // Cell reference for data // Format Code // Number of datapoints in series // Data values // Data Marker $dataSeriesLabels1 = array(new \PHPExcel\Chart\DataSeriesValues('String', 'Worksheet!$C$1', NULL, 1)); // Set the X-Axis Labels // Datatype // Cell reference for data // Format Code // Number of datapoints in series
* @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 PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Mark Baker")->setLastModifiedBy("Mark Baker")->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"); // Add some data echo date('H:i:s'), " Add some data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Crouching'); $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Tiger'); $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Hidden'); $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Dragon'); // Rename worksheet echo date('H:i:s'), " Rename worksheet", EOL; $objPHPExcel->getActiveSheet()->setTitle('Simple'); // Set document security echo date('H:i:s'), " Set cell protection", EOL;
* @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 PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s') . ' Create new PHPExcel object' . EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s') . ' Set document properties' . EOL; $objPHPExcel->getProperties()->setCreator('Maarten Balliauw')->setLastModifiedBy('Maarten Balliauw')->setTitle('PHPExcel Test Document')->setSubject('PHPExcel Test Document')->setDescription('Test document for PHPExcel, generated using PHP classes.')->setKeywords('office PHPExcel php')->setCategory('Test result file'); // Create the worksheet echo date('H:i:s') . ' Add data' . EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Year')->setCellValue('B1', 'Quarter')->setCellValue('C1', 'Country')->setCellValue('D1', 'Sales'); $dataArray = array(array('2010', 'Q1', 'United States', 790), array('2010', 'Q2', 'United States', 730), array('2010', 'Q3', 'United States', 860), array('2010', 'Q4', 'United States', 850), array('2011', 'Q1', 'United States', 800), array('2011', 'Q2', 'United States', 700), array('2011', 'Q3', 'United States', 900), array('2011', 'Q4', 'United States', 950), array('2010', 'Q1', 'Belgium', 380), array('2010', 'Q2', 'Belgium', 390), array('2010', 'Q3', 'Belgium', 420), array('2010', 'Q4', 'Belgium', 460), array('2011', 'Q1', 'Belgium', 400), array('2011', 'Q2', 'Belgium', 350), array('2011', 'Q3', 'Belgium', 450), array('2011', 'Q4', 'Belgium', 500), array('2010', 'Q1', 'UK', 690), array('2010', 'Q2', 'UK', 610), array('2010', 'Q3', 'UK', 620), array('2010', 'Q4', 'UK', 600), array('2011', 'Q1', 'UK', 720), array('2011', 'Q2', 'UK', 650), array('2011', 'Q3', 'UK', 580), array('2011', 'Q4', 'UK', 510), array('2010', 'Q1', 'France', 510), array('2010', 'Q2', 'France', 490), array('2010', 'Q3', 'France', 460), array('2010', 'Q4', 'France', 590), array('2011', 'Q1', 'France', 620), array('2011', 'Q2', 'France', 650), array('2011', 'Q3', 'France', 415), array('2011', 'Q4', 'France', 570), array('2010', 'Q1', 'Germany', 720), array('2010', 'Q2', 'Germany', 680), array('2010', 'Q3', 'Germany', 640), array('2010', 'Q4', 'Germany', 660), array('2011', 'Q1', 'Germany', 680), array('2011', 'Q2', 'Germany', 620), array('2011', 'Q3', 'Germany', 710), array('2011', 'Q4', 'Germany', 690), array('2010', 'Q1', 'Spain', 510), array('2010', 'Q2', 'Spain', 490), array('2010', 'Q3', 'Spain', 470), array('2010', 'Q4', 'Spain', 420), array('2011', 'Q1', 'Spain', 460), array('2011', 'Q2', 'Spain', 390), array('2011', 'Q3', 'Spain', 430), array('2011', 'Q4', 'Spain', 415), array('2010', 'Q1', 'Italy', 440), array('2010', 'Q2', 'Italy', 410), array('2010', 'Q3', 'Italy', 420), array('2010', 'Q4', 'Italy', 450), array('2011', 'Q1', 'Italy', 430), array('2011', 'Q2', 'Italy', 370), array('2011', 'Q3', 'Italy', 350), array('2011', 'Q4', 'Italy', 335)); $objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A2'); // Set title row bold echo date('H:i:s') . ' Set title row bold' . EOL; $objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getFont()->setBold(true); // Set autofilter echo date('H:i:s') . ' Set autofilter' . EOL; // Always include the complete filter range!
* @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 PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // 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"); // Add some data, we will use printing features echo date('H:i:s'), " Add some data", EOL; for ($i = 1; $i < 200; $i++) { $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i); $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, 'Test value'); } // 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&G&C&HPlease treat this document as confidential!'); $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N'); // Add a drawing to the header echo date('H:i:s'), " Add a drawing to the header", EOL;