/**
* 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;
