public static function exportXlsx($data, $keys)
{
// Create new PHPExcel object
$objPHPExcel = new \PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Roadiz CMS")->setLastModifiedBy("Roadiz CMS")->setCategory("");
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = ['memoryCacheSize' => '8MB'];
\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objPHPExcel->setActiveSheetIndex(0);
foreach ($keys as $key => $value) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($key, 1, $value);
}
foreach ($data as $key => $answer) {
foreach ($answer as $k => $value) {
$columnAlpha = \PHPExcel_Cell::stringFromColumnIndex($k);
if ($value instanceof \DateTime) {
$value = \PHPExcel_Shared_Date::PHPToExcel($value);
$objPHPExcel->getActiveSheet()->getStyle($columnAlpha . (2 + $key))->getNumberFormat()->setFormatCode('dd.mm.yyyy hh:MM:ss');
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($k, 2 + $key, $value);
}
}
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_start();
$objWriter->save('php://output');
$return = ob_get_clean();
return $return;
}
/**
* Output record line into file
*
* @param array $array_selected_sorted Array with list of field to export
* @param resource $objp A record from a fetch with all fields from select
* @param Translate $outputlangs Object lang to translate values
* @param array $array_types Array with types of fields
* @return int <0 if KO, >0 if OK
*/
function write_record($array_selected_sorted, $objp, $outputlangs, $array_types)
{
global $conf;
// Create a format for the column headings
if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
$outputlangs->charset_output = 'ISO-8859-1';
// Because Excel 5 format is ISO
}
// Define first row
$this->col = 0;
foreach ($array_selected_sorted as $code => $value) {
if (strpos($code, ' as ') == 0) {
$alias = str_replace(array('.', '-'), '_', $code);
} else {
$alias = substr($code, strpos($code, ' as ') + 4);
}
if (empty($alias)) {
dol_print_error('', 'Bad value for field with code=' . $code . '. Try to redefine export.');
}
$newvalue = $objp->{$alias};
$newvalue = $this->excel_clean($newvalue);
$typefield = isset($array_types[$code]) ? $array_types[$code] : '';
// Traduction newvalue
if (preg_match('/^\\((.*)\\)$/i', $newvalue, $reg)) {
$newvalue = $outputlangs->transnoentities($reg[1]);
} else {
$newvalue = $outputlangs->convToOutputCharset($newvalue);
}
if (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/i', $newvalue)) {
if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
$formatdate = $this->workbook->addformat();
$formatdate->set_num_format('yyyy-mm-dd');
//$formatdate->set_num_format(0x0f);
$arrayvalue = preg_split('/[.,]/', xl_parse_date($newvalue));
//print "x".$arrayvalue[0].'.'.strval($arrayvalue[1]).'<br>';
$newvalue = strval($arrayvalue[0]) . '.' . strval($arrayvalue[1]);
// $newvalue=strval(36892.521); directly does not work because . will be convert into , later
$this->worksheet->write($this->row, $this->col, $newvalue, PHPExcel_Shared_Date::PHPToExcel($formatdate));
} else {
$newvalue = dol_stringtotime($newvalue);
$this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, PHPExcel_Shared_Date::PHPToExcel($newvalue));
$coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
$this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd');
}
} elseif (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]$/i', $newvalue)) {
if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
$formatdatehour = $this->workbook->addformat();
$formatdatehour->set_num_format('yyyy-mm-dd hh:mm:ss');
//$formatdatehour->set_num_format(0x0f);
$arrayvalue = preg_split('/[.,]/', xl_parse_date($newvalue));
//print "x".$arrayvalue[0].'.'.strval($arrayvalue[1]).'<br>';
$newvalue = strval($arrayvalue[0]) . '.' . strval($arrayvalue[1]);
// $newvalue=strval(36892.521); directly does not work because . will be convert into , later
$this->worksheet->write($this->row, $this->col, $newvalue, $formatdatehour);
} else {
$newvalue = dol_stringtotime($newvalue);
$this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, PHPExcel_Shared_Date::PHPToExcel($newvalue));
$coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
$this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd h:mm:ss');
}
} else {
if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
$this->worksheet->write($this->row, $this->col, $newvalue);
} else {
if ($typefield == 'Text' || $typefield == 'TextAuto') {
//$this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row+1)->setValueExplicit($newvalue, PHPExcel_Cell_DataType::TYPE_STRING);
$this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, (string) $newvalue);
$coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
$this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('@');
$this->workbook->getActiveSheet()->getStyle($coord)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
} else {
$this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
}
}
}
$this->col++;
}
$this->row++;
return 0;
}
public function execute()
{
$this->form_validation->set_rules('event', 'Event', 'trim');
$this->form_validation->set_rules('date_from', 'Date From', 'trim');
$this->form_validation->set_rules('date_to', 'Date To', 'trim');
if ($this->form_validation->run() === false) {
$this->session->set_flashdata('alert', '<div class="alert alert-danger">' . validation_errors() . '</div>');
$data['content'] = $this->load->view('export', '', true);
$this->load->view('template', $data);
} else {
ini_set('memory_limit', '-1');
require_once "../assets/phpexcel/PHPExcel.php";
$excel = new PHPExcel();
$excel->setActiveSheetIndex(0);
$active_sheet = $excel->getActiveSheet();
$active_sheet->setTitle('Candidate');
//style
$active_sheet->getStyle("A1:AC1")->getFont()->setBold(true);
//header
$active_sheet->setCellValue('A1', 'No');
$active_sheet->setCellValue('B1', 'Event');
$active_sheet->setCellValue('C1', 'Serial Number');
$active_sheet->setCellValue('D1', 'Name of Contacts');
$active_sheet->setCellValue('E1', 'Job Title');
$active_sheet->setCellValue('F1', 'Departement');
$active_sheet->setCellValue('G1', 'Company');
$active_sheet->setCellValue('H1', 'Telephone');
$active_sheet->setCellValue('I1', 'Mobile');
$active_sheet->setCellValue('J1', 'Actcode');
$active_sheet->setCellValue('K1', 'New Name');
$active_sheet->setCellValue('L1', 'New Title');
$active_sheet->setCellValue('M1', 'New Telephone');
$active_sheet->setCellValue('N1', 'New Mobile');
$active_sheet->setCellValue('O1', 'Email');
$active_sheet->setCellValue('P1', 'Mobile Sms');
$active_sheet->setCellValue('Q1', 'Distribution Date');
$active_sheet->setCellValue('R1', 'Status');
$active_sheet->setCellValue('S1', 'Call History');
$event = $this->input->post('event');
$date_from = format_ymd($this->input->post('date_from'));
$date_to = format_ymd($this->input->post('date_to'));
$result = $this->export_model->export($event, $date_from, $date_to)->result();
$i = 2;
foreach ($result as $r) {
$active_sheet->setCellValue('A' . $i, $i - 1);
$active_sheet->setCellValue('B' . $i, $r->event_name);
$active_sheet->setCellValueExplicit('C' . $i, $r->sn);
$active_sheet->setCellValue('D' . $i, $r->name);
$active_sheet->setCellValue('E' . $i, $r->title);
$active_sheet->setCellValue('F' . $i, $r->dept);
$active_sheet->setCellValue('G' . $i, $r->company);
$active_sheet->setCellValueExplicit('H' . $i, $r->tlp);
$active_sheet->setCellValueExplicit('I' . $i, $r->mobile);
$active_sheet->setCellValue('J' . $i, $r->actcode);
$active_sheet->setCellValue('K' . $i, $r->name_new);
$active_sheet->setCellValue('L' . $i, $r->title_new);
$active_sheet->setCellValueExplicit('M' . $i, $r->tlp_new);
$active_sheet->setCellValueExplicit('N' . $i, $r->mobile_new);
$active_sheet->setCellValue('O' . $i, $r->email);
$active_sheet->setCellValueExplicit('P' . $i, $r->mobile_sms);
$active_sheet->setCellValue('Q' . $i, PHPExcel_Shared_Date::PHPToExcel(date_to_excel($r->dist_date)));
$active_sheet->getStyle('Q' . $i)->getNumberFormat()->setFormatCode('dd/mm/yyyy');
$active_sheet->setCellValue('R' . $i, $r->status_name);
$active_sheet->setCellValue('S' . $i, $this->callhis_model->get_note($r->id));
$i++;
}
$filename = 'Candidate_' . date('Ymd_His') . '.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$objWriter->save('php://output');
}
}
protected function writeCell($value, $column, $row, $config)
{
// auto type
if (!isset($config['type']) || $config['type'] === null) {
$this->sheet->setCellValueByColumnAndRow($column, $row, $value);
} elseif ($config['type'] === 'date') {
if (!is_int($value)) {
$timestamp = strtotime($value);
}
$this->sheet->SetCellValueByColumnAndRow($column, $row, \PHPExcel_Shared_Date::PHPToExcel($timestamp));
if (!isset($config['styles']['numberformat']['code'])) {
$config['styles']['numberformat']['code'] = $this->defaultDateFormat;
}
} elseif ($config['type'] === 'url') {
if (isset($config['label'])) {
if ($config['label'] instanceof \Closure) {
// NOTE: calculate label on top level
$label = call_user_func($config['label']);
} else {
$label = $config['label'];
}
} else {
$label = $value;
}
$urlValid = filter_var($value, FILTER_VALIDATE_URL) !== false;
if (!$urlValid) {
$label = '';
}
$this->sheet->setCellValueByColumnAndRow($column, $row, $label);
if ($urlValid) {
$this->sheet->getCellByColumnAndRow($column, $row)->getHyperlink()->setUrl($value);
}
} else {
$this->sheet->setCellValueExplicitByColumnAndRow($column, $row, $value, $config['type']);
}
if (isset($config['styles'])) {
$this->sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($config['styles']);
}
}
/**
* Bind value to a cell
*
* @param PHPExcel_Cell $cell Cell to bind value to
* @param mixed $value Value to bind in cell
* @return boolean
*/
public function bindValue(PHPExcel_Cell $cell, $value = null)
{
// Find out data type
$dataType = parent::dataTypeForValue($value);
// Style logic - strings
if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) {
// Check for percentage
if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) {
// Convert value to number
$cell->setValueExplicit((double) str_replace('%', '', $value) / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
return true;
}
// Check for time e.g. '9:45', '09:45'
if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d$/', $value)) {
list($h, $m) = explode(':', $value);
$days = $h / 24 + $m / 1440;
// Convert value to number
$cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);
return true;
}
// Check for date
if (strtotime($value) !== false) {
// make sure we have UTC for the sake of strtotime
$saveTimeZone = date_default_timezone_get();
date_default_timezone_set('UTC');
// Convert value to Excel date
$cell->setValueExplicit(PHPExcel_Shared_Date::PHPToExcel(strtotime($value)), PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
// restore original value for timezone
date_default_timezone_set($saveTimeZone);
return true;
}
}
// Style logic - Numbers
if ($dataType === PHPExcel_Cell_DataType::TYPE_NUMERIC) {
// Leading zeroes?
if (preg_match('/^\\-?[0]+[0-9]*\\.?[0-9]*$/', $value)) {
// Convert value to string
$cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
// Set style
$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
return true;
}
}
// Not bound yet? Use parent...
return parent::bindValue($cell, $value);
}
private static function couponFirstPeriodDate($settlement, $maturity, $frequency, $next)
{
$months = 12 / $frequency;
$result = PHPExcel_Shared_Date::ExcelToPHPObject($maturity);
$eom = self::isLastDayOfMonth($result);
while ($settlement < PHPExcel_Shared_Date::PHPToExcel($result)) {
$result->modify('-' . $months . ' months');
}
if ($next) {
$result->modify('+' . $months . ' months');
}
if ($eom) {
$result->modify('-1 day');
}
return PHPExcel_Shared_Date::PHPToExcel($result);
}
/**
* Fill worksheet from values in array
*
* @param array $source Source array
* @param mixed $nullValue Value in source array that stands for blank cell
* @param string $startCell Insert array starting from this cell address as the top left coordinate
* @param bool $strictNullComparison Apply strict comparison when testing for null values in the array
* @throws PhpExcelException
* @return Worksheet
*/
public function fromArray(array $source = null, $nullValue = null, $startCell = 'A1', $strictNullComparison = false)
{
foreach ($source as &$row) {
if (is_array($row)) {
foreach ($row as $key => $value) {
if ($value instanceof DateTime) {
$row[$key] = PhpOffice_PHPExcel_Shared_Date::PHPToExcel($value);
}
}
}
}
try {
$this->sheet->fromArray($source, $nullValue, $startCell, $strictNullComparison);
} catch (PhpOffice_PHPExcel_Exception $ex) {
throw new PhpExcelException('Unable to paste the array to worksheet', $ex->getCode(), $ex);
}
return $this;
}
protected function renderFields($key, $value)
{
$renderTimeArray = array('p_loadTimeSlot', 'p_unloadTimeSlot', 'p_shipperEta', 'p_consigneeEta', 'p_shipperAta', 'p_shipperAtd', 'p_consigneeAta', 'p_consigneeAtd', 'to_shipperEta');
$securityArray = array('p_securityFlag', 'p_stackFlag');
if (in_array($key, $renderTimeArray)) {
if (is_object($value)) {
return \PHPExcel_Shared_Date::PHPToExcel($value);
}
} else {
if (in_array($key, $securityArray)) {
if ($value == true) {
return 'ДА';
} else {
return 'НЕТ';
}
}
}
return $value;
}
/**
* @return array
*/
protected function makeExportData()
{
$rows = array();
$rows[] = $this->excelHeader();
/** @var $em \Doctrine\ORM\EntityManager */
$em = \AppKernel::getStaticContainer()->get('doctrine');
/** @var $query \Doctrine\ORM\QueryBuilder */
$object = $em->getRepository('ApplicationSonataClientBundle:Garantie')->createQueryBuilder('g')->orderBy('g.date_decheance', 'ASC')->getQuery()->execute();
$nom_de_la_banques = Garantie::getNomDeLaBanques();
foreach ($object as $key => $row) {
$date_decheance = $row->getDateDecheance() ? \PHPExcel_Shared_Date::PHPToExcel($row->getDateDecheance()) : '';
/** @var $row Garantie */
$cell = array('client' => (string) $row->getClient(), 'date_decheance' => $date_decheance, 'montant' => $row->getMontant(), 'nom_de_la_banques_id' => isset($nom_de_la_banques[$row->getNomDeLaBanquesId()]) ? $nom_de_la_banques[$row->getNomDeLaBanquesId()] : '', 'num_de_ganrantie' => $row->getNumDeGanrantie(), 'note' => $row->getNote(), 'client_date_fin_mission' => $row->getClient()->getDateFinMission() ? \PHPExcel_Shared_Date::PHPToExcel($row->getClient()->getDateFinMission()) : '', 'calc_day_date_decheance' => $date_decheance ? '=B' . ($key + 2) . '-TODAY()' : '');
//format
$this->excelCellFormat($key + 2, $row);
$rows[] = $cell;
}
return $rows;
}
/**
* Bind value to a cell
*
* @param PHPExcel_Cell $cell Cell to bind value to
* @param mixed $value Value to bind in cell
* @return boolean
*/
public function bindValue(PHPExcel_Cell $cell, $value = null)
{
// Find out data type
$dataType = parent::dataTypeForValue($value);
// Style logic - strings
if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) {
// Check for percentage
if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) {
// Convert value to number
$cell->setValueExplicit((double) str_replace('%', '', $value) / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
return true;
}
// Check for date
if (strtotime($value) !== false) {
// Convert value to Excel date
$cell->setValueExplicit(PHPExcel_Shared_Date::PHPToExcel(strtotime($value)), PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
return true;
}
}
// Style logic - Numbers
if ($dataType === PHPExcel_Cell_DataType::TYPE_NUMERIC) {
// Leading zeroes?
if (preg_match('/^\\-?[0]+[0-9]*\\.?[0-9]*$/', $value)) {
// Convert value to string
$cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
// Set style
$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
return true;
}
}
// Not bound yet? Use parent...
return parent::bindValue($cell, $value);
}
/**
* Convert a dynamic rule daterange to a custom filter range expression for ease of calculation
*
* @param string $dynamicRuleType
* @param PHPExcel_Worksheet_AutoFilter_Column &$filterColumn
* @return mixed[]
*/
private function _dynamicFilterDateRange($dynamicRuleType, &$filterColumn)
{
$rDateType = PHPExcel_Calculation_Functions::getReturnDateType();
PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC);
$val = $maxVal = NULL;
$ruleValues = array();
$baseDate = PHPExcel_Calculation_DateTime::DATENOW();
// Calculate start/end dates for the required date range based on current date
switch ($dynamicRuleType) {
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK:
$baseDate = strtotime('-7 days', $baseDate);
break;
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK:
$baseDate = strtotime('-7 days', $baseDate);
break;
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH:
$baseDate = strtotime('-1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH:
$baseDate = strtotime('+1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER:
$baseDate = strtotime('-3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER:
$baseDate = strtotime('+3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR:
$baseDate = strtotime('-1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR:
$baseDate = strtotime('+1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
}
switch ($dynamicRuleType) {
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TODAY:
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY:
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW:
$maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(strtotime('+1 day', $baseDate));
$val = (int) PHPExcel_Shared_Date::PHPToExcel($baseDate);
break;
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE:
$maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(strtotime('+1 day', $baseDate));
$val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate)));
break;
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR:
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR:
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR:
$maxVal = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 31, 12, date('Y', $baseDate)));
++$maxVal;
$val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate)));
break;
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER:
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER:
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER:
$thisMonth = date('m', $baseDate);
$thisQuarter = floor(--$thisMonth / 3);
$maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), (1 + $thisQuarter) * 3, date('Y', $baseDate)));
++$maxVal;
$val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1 + $thisQuarter * 3, date('Y', $baseDate)));
break;
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH:
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH:
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH:
$maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), date('m', $baseDate), date('Y', $baseDate)));
++$maxVal;
$val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK:
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK:
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK:
$dayOfWeek = date('w', $baseDate);
$val = (int) PHPExcel_Shared_Date::PHPToExcel($baseDate) - $dayOfWeek;
$maxVal = $val + 7;
break;
}
switch ($dynamicRuleType) {
// Adjust Today dates for Yesterday and Tomorrow
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY:
--$maxVal;
--$val;
break;
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW:
++$maxVal;
++$val;
break;
}
// Set the filter column rule attributes ready for writing
$filterColumn->setAttributes(array('val' => $val, 'maxVal' => $maxVal));
// Set the rules for identifying rows for hide/show
$ruleValues[] = array('operator' => PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL, 'value' => $val);
$ruleValues[] = array('operator' => PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN, 'value' => $maxVal);
PHPExcel_Calculation_Functions::setReturnDateType($rDateType);
return array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND));
}
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Antrag');
$objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Absatz / Zeilen');
$objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Kommentar');
if ($this->veranstaltung->getEinstellungen()->kommentare_unterstuetzbar) {
$objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Bewertung: Positiv');
$objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Bewertung: Negativ');
$objPHPExcel->getActiveSheet()->getStyle("A1:G1")->applyFromArray(array("font" => array("bold" => true)));
} else {
$objPHPExcel->getActiveSheet()->getStyle("A1:E1")->applyFromArray(array("font" => array("bold" => true)));
}
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
$row = 1;
foreach ($kommentare as $kommentar) {
$row++;
$timestamp = strtotime($kommentar->datum);
$objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, PHPExcel_Shared_Date::PHPToExcel($timestamp));
$objPHPExcel->getActiveSheet()->getStyle('A' . $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
$verfasserIn = $kommentar->verfasserIn->name;
if ($kommentar->verfasserIn->email != "") {
$verfasserIn .= " (" . $kommentar->verfasserIn->email . ")";
}
$objPHPExcel->getActiveSheet()->SetCellValue('B' . $row, $verfasserIn);
$absatz_zeilen = "";
$objPHPExcel->getActiveSheet()->getStyle('C' . $row)->getAlignment()->setWrapText(true);
if (is_a($kommentar, "AntragKommentar")) {
/** @var AntragKommentar $kommentar */
$antrag = Antrag::model()->findByPk($kommentar->antrag_id);
if ($this->veranstaltung->getEinstellungen()->revision_name_verstecken) {
$objPHPExcel->getActiveSheet()->SetCellValue('C' . $row, $kommentar->antrag->name);
} else {
$objPHPExcel->getActiveSheet()->SetCellValue('C' . $row, $kommentar->antrag->revision_name . " " . $kommentar->antrag->name);
public function process(Vtiger_Request $request)
{
require_once "libraries/PHPExcel/PHPExcel.php";
$currentUser = Users_Record_Model::getCurrentUserModel();
$module = $request->getModule(false);
//this is the type of things in the current view
$filter = $request->get('viewname');
//this is the cvid of the current custom filter
$recordIds = $this->getRecordsListFromRequest($request);
//this handles the 'all' situation.
//we now know what we want to render and can get a handle to the respective view, probably should use a listviewcontroller to get the data
$selectedModule = $request->get("targetmodule");
//set up our spreadsheet to write out to
$workbook = new PHPExcel();
$worksheet = $workbook->setActiveSheetIndex(0);
$header_styles = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'E1E0F7')), 'font' => array('bold' => true));
$row = 1;
$col = 0;
$generator = new QueryGenerator($selectedModule, $currentUser);
$generator->initForCustomViewById($filter);
$customView = CustomView_Record_Model::getInstanceById($filter);
$listviewController = new ListViewController($db, $currentUser, $generator);
$headers = $listviewController->getListViewHeaderFields();
//get the column headers, they go in row 0 of the spreadsheet
foreach ($headers as $column => $webserviceField) {
$fieldObj = Vtiger_Field::getInstance($webserviceField->getFieldId());
//echo $fieldObj->label;
$fields[] = $fieldObj;
$worksheet->setCellValueExplicitByColumnAndRow($col, $row, decode_html(vtranslate($fieldObj->label, $selectedModule)), PHPExcel_Cell_DataType::TYPE_STRING);
$col++;
}
$row++;
$targetModuleFocus = CRMEntity::getInstance($selectedModule);
//ListViewController has lots of paging stuff and things we don't want
//so lets just itterate across the list of IDs we have and get the field values
foreach ($recordIds as $id) {
$col = 0;
$record = Vtiger_Record_Model::getInstanceById($id, $selectedModule);
foreach ($fields as $field) {
//depending on the uitype we might want the raw value, the display value or something else.
//we might also want the display value sans-links so we can use strip_tags for that
//phone numbers need to be explicit strings
$value = $record->getDisplayValue($field->name);
$uitype = $field->uitype;
switch ($uitype) {
case 4:
//numbers
//numbers
case 25:
case 7:
case 71:
$worksheet->setCellvalueExplicitByColumnAndRow($col, $row, strip_tags($value), PHPExcel_Cell_DataType::TYPE_NUMERIC);
break;
case 6:
//datetimes
//datetimes
case 23:
case 70:
$worksheet->setCellvalueExplicitByColumnAndRow($col, $row, PHPExcel_Shared_Date::PHPToExcel(strtotime($value)), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$worksheet->getStyleByColumnAndRow($col, $row)->getNumberFormat()->setFormatCode('DD/MM/YYYY HH:MM:SS');
//format the date to the users preference
break;
default:
$worksheet->setCellValueExplicitByColumnAndRow($col, $row, decode_html(strip_tags($value)), PHPExcel_Cell_DataType::TYPE_STRING);
}
//echo strip_tags($value);
$col++;
}
//echo "<br>";
$row++;
}
//having written out all the data lets have a go at getting the columns to auto-size
$col = 0;
$row = 1;
foreach ($headers as $column => $webserviceField) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$worksheet->getStyleByColumnAndRow($col, $row)->applyFromArray($header_styles);
$worksheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
$col++;
}
$rootDirectory = vglobal('root_directory');
$tmpDir = vglobal('tmp_dir');
$tempFileName = tempnam($rootDirectory . $tmpDir, 'xls');
$workbookWriter = PHPExcel_IOFactory::createWriter($workbook, 'Excel5');
$workbookWriter->save($tempFileName);
if (isset($_SERVER['HTTP_USER_AGENT']) && strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE')) {
header('Pragma: public');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
}
header('Content-Type: application/x-msexcel');
header('Content-Length: ' . @filesize($tempFileName));
$filename = decode_html($customView->get('viewname')) . ".xls";
header('Content-disposition: attachment; filename="' . $filename . '"');
$fp = fopen($tempFileName, 'rb');
fpassthru($fp);
}
/**
* Map to the appropriate write method acording to the token recieved.
*
* @access public
* @param integer $row The row of the cell we are writing to
* @param integer $col The column of the cell we are writing to
* @param mixed $token What we are writing
* @param mixed $format The optional format to apply to the cell
*/
function write($row, $col, $token, $format = null, $numberFormat = null)
{
if ($numberFormat != 'General' && PHPExcel_Shared_Date::isDateTimeFormatCode($numberFormat)) {
if (is_string($token)) {
// Error string
return $this->writeString($row, $col, $token, $format);
} elseif (!is_float($token)) {
// PHP serialized date/time or date/time object
return $this->writeNumber($row, $col, PHPExcel_Shared_Date::PHPToExcel($token), $format);
} else {
// Excel serialized date/time
return $this->writeNumber($row, $col, $token, $format);
}
} elseif (preg_match("/^([+-]?)(?=\\d|\\.\\d)\\d*(\\.\\d*)?([Ee]([+-]?\\d+))?\$/", $token)) {
// Match number
return $this->writeNumber($row, $col, $token, $format);
} elseif ($token == '') {
// Match blank
return $this->writeBlank($row, $col, $format);
} else {
// Default: match string
return $this->writeString($row, $col, $token, $format);
}
}
function writeReportToExcelFile($fileName, $filterlist = '')
{
global $currentModule, $current_language, $current_user;
$mod_strings = return_module_language($current_language, $currentModule);
require_once 'include/PHPExcel/PHPExcel.php';
$xlsrowheight = GlobalVariable::getVariable('Report.Excel.Export.RowHeight', 20);
$workbook = new PHPExcel();
$worksheet = $workbook->setActiveSheetIndex(0);
$fieldinfo = array();
$arr_val = $this->GenerateReport('PDF', $filterlist, false, $fieldinfo);
$totalxls = $this->GenerateReport('TOTALXLS', $filterlist);
$header_styles = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'E1E0F7')), 'font' => array('bold' => true));
if (isset($arr_val)) {
$FieldDataTypes = array();
foreach ($arr_val[0] as $hdr => $value) {
$FieldDataTypes[$hdr] = $fieldinfo[$hdr]->getFieldDataType();
if ($fieldinfo[$hdr]->getColumnName() == 'totaltime') {
$FieldDataTypes[$hdr] = 'time';
}
if ($fieldinfo[$hdr]->getColumnName() == 'totaldaytime') {
$FieldDataTypes[$hdr] = 'time';
}
}
$BoolTrue = getTranslatedString('LBL_YES');
//$BoolFalse = getTranslatedString('LBL_NO');
$count = 0;
$rowcount = 1;
$workbook->getActiveSheet()->getRowDimension($rowcount)->setRowHeight($xlsrowheight);
//copy the first value details
$arrayFirstRowValues = $arr_val[0];
foreach ($arrayFirstRowValues as $key => $value) {
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, $key, true);
$worksheet->getStyleByColumnAndRow($count, $rowcount)->applyFromArray($header_styles);
// NOTE Performance overhead: http://stackoverflow.com/questions/9965476/phpexcel-column-size-issues
$worksheet->getColumnDimensionByColumn($count)->setAutoSize(true);
$count = $count + 1;
if ($FieldDataTypes[$key] == 'currency') {
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, getTranslatedString('LBL_CURRENCY'), true);
$worksheet->getStyleByColumnAndRow($count, $rowcount)->applyFromArray($header_styles);
$worksheet->getColumnDimensionByColumn($count)->setAutoSize(true);
$count = $count + 1;
}
}
$rowcount++;
$workbook->getActiveSheet()->getRowDimension($rowcount)->setRowHeight($xlsrowheight);
foreach ($arr_val as $key => $array_value) {
$count = 0;
foreach ($array_value as $hdr => $value) {
$value = decode_html($value);
switch ($FieldDataTypes[$hdr]) {
case 'boolean':
$celltype = PHPExcel_Cell_DataType::TYPE_BOOL;
$value = $value == $BoolTrue ? 1 : 0;
break;
case 'integer':
case 'double':
case 'currency':
$celltype = PHPExcel_Cell_DataType::TYPE_NUMERIC;
break;
case 'date':
$value = DateTimeField::__convertToDBFormat($value, $current_user->date_format);
$dt = new DateTime($value);
$value = PHPExcel_Shared_Date::PHPToExcel($dt);
$celltype = PHPExcel_Cell_DataType::TYPE_NUMERIC;
break;
case 'time':
$dt = new DateTime("1970/01/01 {$value}");
$value = PHPExcel_Shared_Date::PHPToExcel($dt);
$celltype = PHPExcel_Cell_DataType::TYPE_NUMERIC;
break;
default:
$celltype = PHPExcel_Cell_DataType::TYPE_STRING;
break;
}
if ($FieldDataTypes[$hdr] == 'currency') {
$csym = preg_replace('/[0-9,.-]/', '', $value);
$value = preg_replace('/[^0-9,.-]/', '', $value);
$value = str_replace($current_user->currency_grouping_separator, '', $value);
if ($current_user->currency_decimal_separator != '.') {
$value = str_replace($current_user->currency_decimal_separator, '.', $value);
}
}
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, $value, $celltype);
if ($FieldDataTypes[$hdr] == 'date') {
$worksheet->getStyleByColumnAndRow($count, $rowcount)->getNumberFormat()->setFormatCode($current_user->date_format);
} elseif ($FieldDataTypes[$hdr] == 'time') {
$worksheet->getStyleByColumnAndRow($count, $rowcount)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
}
if ($FieldDataTypes[$hdr] == 'currency') {
$count = $count + 1;
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, $csym, PHPExcel_Cell_DataType::TYPE_STRING);
}
$count = $count + 1;
}
$rowcount++;
$workbook->getActiveSheet()->getRowDimension($rowcount)->setRowHeight($xlsrowheight);
}
// Summary Total
$rowcount++;
$workbook->getActiveSheet()->getRowDimension($rowcount)->setRowHeight($xlsrowheight);
$count = 0;
if (isset($totalxls) and is_array($totalxls) and count($totalxls) > 0) {
if (is_array($totalxls[0])) {
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, getTranslatedString('Totals', 'Reports'), PHPExcel_Cell_DataType::TYPE_STRING);
$worksheet->getStyleByColumnAndRow($count, $rowcount)->applyFromArray($header_styles);
$count = $count + 1;
foreach ($totalxls[0] as $key => $value) {
$chdr = substr($key, -3, 3);
$translated_str = in_array($chdr, array_keys($mod_strings)) ? $mod_strings[$chdr] : $key;
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, decode_html($translated_str), PHPExcel_Cell_DataType::TYPE_STRING);
$worksheet->getStyleByColumnAndRow($count, $rowcount)->applyFromArray($header_styles);
$count = $count + 1;
}
}
$rowcount++;
$workbook->getActiveSheet()->getRowDimension($rowcount)->setRowHeight($xlsrowheight);
foreach ($totalxls as $key => $array_value) {
$count = 0;
foreach ($array_value as $hdr => $value) {
if ($count == 0) {
$lbl = substr($hdr, 0, strrpos($hdr, '_'));
$mname = substr($lbl, 0, strpos($lbl, '_'));
$lbl = substr($lbl, strpos($lbl, '_') + 1);
$lbl = str_replace('_', ' ', $lbl);
$lbl = getTranslatedString($lbl, $mname);
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, decode_html($lbl), PHPExcel_Cell_DataType::TYPE_STRING);
$worksheet->getStyleByColumnAndRow($count, $rowcount)->applyFromArray($header_styles);
$workbook->getActiveSheet()->getRowDimension($rowcount)->setRowHeight($xlsrowheight);
$count = $count + 1;
}
$value = str_replace($current_user->currency_grouping_separator, '', $value);
if ($current_user->currency_decimal_separator != '.') {
$value = str_replace($current_user->currency_decimal_separator, '.', $value);
}
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
$count = $count + 1;
}
$rowcount++;
}
}
}
$workbookWriter = PHPExcel_IOFactory::createWriter($workbook, 'Excel5');
$workbookWriter->save($fileName);
}
/**
*
* @param integer $year
* @param string $uid
*
* @return unknown|\Symfony\Component\HttpFoundation\RedirectResponse
*/
public function excelYearAction($year, $uid)
{
$urlFrom = $this->getReferer();
if (null == $urlFrom || trim($urlFrom) == '') {
$urlFrom = $this->generateUrl('_admin_company_list');
}
$em = $this->getEntityManager();
try {
$company = $em->getRepository('AcfDataBundle:Company')->find($uid);
if (null == $company) {
$this->flashMsgSession('warning', $this->translate('Company.edit.notfound'));
} else {
$mbpurchases = $em->getRepository('AcfDataBundle:MBPurchase')->getAllByYearCompany($year, $company);
$buys = array();
foreach ($mbpurchases as $mbpurchase) {
$buys = array_merge($buys, $mbpurchase->getTransactions()->toArray());
}
$phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();
$phpExcelObject->getProperties()->setCreator('Salah Abdelkader Seif Eddine')->setLastModifiedBy($this->getSecurityTokenStorage()->getToken()->getUser()->getFullname())->setTitle($this->translate('pagetitle.buy.list'))->setSubject($this->translate('pagetitle.buy.list'))->setDescription($this->translate('pagetitle.buy.list'))->setKeywords($this->translate('pagetitle.buy.list'))->setCategory('ACEF buy');
$phpExcelObject->setActiveSheetIndex(0);
$workSheet = $phpExcelObject->getActiveSheet();
$workSheet->setTitle($this->translate('pagetitle.buy.listExcel', array('%mbpurchase%' => $year)));
$workSheet->setCellValue('A1', $this->translate('Buy.number.label'));
$workSheet->getStyle('A1')->getFont()->setBold(true);
$workSheet->setCellValue('B1', $this->translate('Buy.dtActivation.label'));
$workSheet->getStyle('B1')->getFont()->setBold(true);
$workSheet->setCellValue('C1', $this->translate('Buy.bill.label'));
$workSheet->getStyle('C1')->getFont()->setBold(true);
$workSheet->setCellValue('D1', $this->translate('Buy.relation.label'));
$workSheet->getStyle('D1')->getFont()->setBold(true);
$workSheet->setCellValue('E1', $this->translate('Buy.relation.number'));
$workSheet->getStyle('E1')->getFont()->setBold(true);
$workSheet->setCellValue('F1', $this->translate('Buy.label.label'));
$workSheet->getStyle('F1')->getFont()->setBold(true);
$workSheet->setCellValue('G1', $this->translate('Buy.balanceHt.label'));
$workSheet->getStyle('G1')->getFont()->setBold(true);
$workSheet->setCellValue('H1', $this->translate('Buy.vat.label'));
$workSheet->getStyle('H1')->getFont()->setBold(true);
$workSheet->setCellValue('I1', $this->translate('Buy.stamp.label'));
$workSheet->getStyle('I1')->getFont()->setBold(true);
$workSheet->setCellValue('J1', $this->translate('Buy.balanceTtc.label'));
$workSheet->getStyle('J1')->getFont()->setBold(true);
$workSheet->setCellValue('K1', $this->translate('Buy.regime.label'));
$workSheet->getStyle('K1')->getFont()->setBold(true);
$workSheet->setCellValue('L1', $this->translate('Buy.withholding.label'));
$workSheet->getStyle('L1')->getFont()->setBold(true);
$workSheet->setCellValue('M1', $this->translate('Buy.withholding.value.label'));
$workSheet->getStyle('M1')->getFont()->setBold(true);
$workSheet->setCellValue('N1', $this->translate('Buy.balanceNet.label'));
$workSheet->getStyle('N1')->getFont()->setBold(true);
$workSheet->setCellValue('O1', $this->translate('Buy.paymentType.label'));
$workSheet->getStyle('O1')->getFont()->setBold(true);
$workSheet->setCellValue('P1', $this->translate('Buy.dtPayment.label'));
$workSheet->getStyle('P1')->getFont()->setBold(true);
$workSheet->setCellValue('Q1', $this->translate('Buy.account.label'));
$workSheet->getStyle('Q1')->getFont()->setBold(true);
$workSheet->setCellValue('R1', $this->translate('Buy.nature.label'));
$workSheet->getStyle('R1')->getFont()->setBold(true);
$workSheet->setCellValue('S1', $this->translate('Buy.transactionStatus.label'));
$workSheet->getStyle('S1')->getFont()->setBold(true);
$workSheet->setCellValue('T1', $this->translate('Buy.otherInfos.label'));
$workSheet->getStyle('T1')->getFont()->setBold(true);
$workSheet->getStyle('A1:T1')->applyFromArray(array('fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '94ccdf'))));
$suppliersConstStr = $em->getRepository('AcfDataBundle:ConstantStr')->findOneBy(array('name' => 'suppliersPrefix'));
if (null == $suppliersConstStr) {
$suppliersConstStr = new ConstantStr();
$suppliersConstStr->setName('suppliersPrefix');
$suppliersConstStr->setValue('401');
$em->persist($suppliersConstStr);
$em->flush();
}
$suppliersPrefix = $suppliersConstStr->getValue();
$this->gvars['suppliersPrefix'] = $suppliersPrefix;
$i = 1;
// $currencyFormatter = new \NumberFormatter($this->getRequest()->getLocale(), \NumberFormatter::CURRENCY);
// $balance = $currencyFormatter->formatCurrency($balance, 'TND');
foreach ($buys as $buy) {
$i++;
$workSheet->setCellValue('A' . $i, $buy->getNumber(), \PHPExcel_Cell_DataType::TYPE_STRING2);
$workSheet->setCellValue('B' . $i, \PHPExcel_Shared_Date::PHPToExcel($buy->getDtActivation()), \PHPExcel_Cell_DataType::TYPE_NUMERIC);
$workSheet->getStyle('B' . $i)->getNumberFormat()->setFormatCode('dd/mm/yyyy');
$workSheet->setCellValue('C' . $i, $buy->getBill(), \PHPExcel_Cell_DataType::TYPE_STRING2);
$workSheet->setCellValue('D' . $i, $buy->getRelation()->getLabel(), \PHPExcel_Cell_DataType::TYPE_STRING2);
$numb = $suppliersPrefix . $buy->getRelation()->getNumberFormated();
$workSheet->setCellValueExplicit('E' . $i, $numb, \PHPExcel_Cell_DataType::TYPE_STRING2);
$workSheet->setCellValue('F' . $i, $buy->getLabel(), \PHPExcel_Cell_DataType::TYPE_STRING2);
$balanceHt = $buy->getBalanceTtc() - $buy->getStamp() - $buy->getVat();
// $balanceHt = $currencyFormatter->formatCurrency($balanceHt, 'TND');
$workSheet->setCellValue('G' . $i, $balanceHt);
$workSheet->getStyle('G' . $i)->getNumberFormat()->setFormatCode('#,##0.000');
$workSheet->setCellValue('H' . $i, $buy->getVat());
$workSheet->getStyle('H' . $i)->getNumberFormat()->setFormatCode('#,##0.000');
$workSheet->setCellValue('I' . $i, $buy->getStamp());
$workSheet->getStyle('I' . $i)->getNumberFormat()->setFormatCode('#,##0.000');
$workSheet->setCellValue('J' . $i, $buy->getBalanceTtc());
$workSheet->getStyle('J' . $i)->getNumberFormat()->setFormatCode('#,##0.000');
$workSheet->setCellValue('K' . $i, $this->translate('Buy.regime.' . $buy->getRegime()), \PHPExcel_Cell_DataType::TYPE_STRING2);
$withholding = $buy->getBalanceTtc() - $buy->getBalanceNet();
$workSheet->setCellValue('L' . $i, $withholding);
$workSheet->getStyle('L' . $i)->getNumberFormat()->setFormatCode('#,##0.000');
$workSheet->setCellValue('M' . $i, $buy->getWithholding()->getValue() / 100);
$workSheet->getStyle('M' . $i)->getNumberFormat()->setFormatCode('#,##0.00%');
$workSheet->setCellValue('N' . $i, $buy->getBalanceNet());
$workSheet->getStyle('N' . $i)->getNumberFormat()->setFormatCode('#,##0.000');
$workSheet->setCellValue('O' . $i, $this->translate('Transaction.paymentType.' . $buy->getPaymentType()), \PHPExcel_Cell_DataType::TYPE_STRING2);
$workSheet->setCellValue('P' . $i, \PHPExcel_Shared_Date::PHPToExcel($buy->getDtPayment()), \PHPExcel_Cell_DataType::TYPE_NUMERIC);
$workSheet->getStyle('P' . $i)->getNumberFormat()->setFormatCode('dd/mm/yyyy');
$workSheet->setCellValue('Q' . $i, $buy->getAccount()->getLabel(), \PHPExcel_Cell_DataType::TYPE_STRING2);
if (null == $buy->getNature()) {
$workSheet->setCellValue('R' . $i, 'ACHATS DE MARCHANDISES', \PHPExcel_Cell_DataType::TYPE_STRING2);
} else {
$workSheet->setCellValue('R' . $i, $buy->getNature()->getLabel(), \PHPExcel_Cell_DataType::TYPE_STRING2);
}
$workSheet->setCellValue('S' . $i, $this->translate('Transaction.transactionStatus.' . $buy->getTransactionStatus()), \PHPExcel_Cell_DataType::TYPE_STRING2);
$workSheet->setCellValue('T' . $i, $buy->getOtherInfos(), \PHPExcel_Cell_DataType::TYPE_STRING2);
if ($i % 2 == 1) {
$workSheet->getStyle('A' . $i . ':T' . $i)->applyFromArray(array('fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'd8f1f5'))));
} else {
$workSheet->getStyle('A' . $i . ':T' . $i)->applyFromArray(array('fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'bfbfbf'))));
}
}
$workSheet->getColumnDimension('A')->setAutoSize(true);
$workSheet->getColumnDimension('B')->setAutoSize(true);
$workSheet->getColumnDimension('C')->setAutoSize(true);
$workSheet->getColumnDimension('D')->setAutoSize(true);
$workSheet->getColumnDimension('E')->setAutoSize(true);
$workSheet->getColumnDimension('F')->setAutoSize(true);
$workSheet->getColumnDimension('G')->setAutoSize(true);
$workSheet->getColumnDimension('H')->setAutoSize(true);
$workSheet->getColumnDimension('I')->setAutoSize(true);
$workSheet->getColumnDimension('J')->setAutoSize(true);
$workSheet->getColumnDimension('K')->setAutoSize(true);
$workSheet->getColumnDimension('L')->setAutoSize(true);
$workSheet->getColumnDimension('M')->setAutoSize(true);
$workSheet->getColumnDimension('N')->setAutoSize(true);
$workSheet->getColumnDimension('O')->setAutoSize(true);
$workSheet->getColumnDimension('P')->setAutoSize(true);
$workSheet->getColumnDimension('Q')->setAutoSize(true);
$workSheet->getColumnDimension('R')->setAutoSize(true);
$workSheet->getColumnDimension('S')->setAutoSize(true);
$workSheet->getColumnDimension('T')->setAutoSize(true);
$writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel2007');
$response = $this->get('phpexcel')->createStreamedResponse($writer);
$response->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8');
$filename = $this->normalize($this->translate('pagetitle.buy.listByYear', array('%year%' => $year, '%company%' => $mbpurchase->getCompany()->getCorporateName())));
$filename = str_ireplace('"', '|', $filename);
$filename = str_ireplace(' ', '_', $filename);
$response->headers->set('Content-Disposition', 'attachment;filename=' . $filename . '.xlsx');
$response->headers->set('Pragma', 'public');
$response->headers->set('Cache-Control', 'maxage=1');
return $response;
}
} catch (\Exception $e) {
$logger = $this->getLogger();
$logger->addCritical($e->getLine() . ' ' . $e->getMessage() . ' ' . $e->getTraceAsString());
}
return $this->redirect($urlFrom);
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param PHPExcel $objPHPExcel
* @return PHPExcel
* @throws Exception
*/
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
$timezoneObj = new DateTimeZone('Europe/London');
$GMT = new DateTimeZone('UTC');
$zip = new ZipArchive();
if ($zip->open($pFilename) === true) {
// echo '<h1>Meta Information</h1>';
$xml = simplexml_load_string($zip->getFromName("meta.xml"));
$namespacesMeta = $xml->getNamespaces(true);
// echo '<pre>';
// print_r($namespacesMeta);
// echo '</pre><hr />';
$docProps = $objPHPExcel->getProperties();
$officeProperty = $xml->children($namespacesMeta['office']);
foreach ($officeProperty as $officePropertyData) {
$officePropertyDC = array();
if (isset($namespacesMeta['dc'])) {
$officePropertyDC = $officePropertyData->children($namespacesMeta['dc']);
}
foreach ($officePropertyDC as $propertyName => $propertyValue) {
switch ($propertyName) {
case 'title':
$docProps->setTitle($propertyValue);
break;
case 'subject':
$docProps->setSubject($propertyValue);
break;
case 'creator':
$docProps->setCreator($propertyValue);
$docProps->setLastModifiedBy($propertyValue);
break;
case 'date':
$creationDate = strtotime($propertyValue);
$docProps->setCreated($creationDate);
$docProps->setModified($creationDate);
break;
case 'description':
$docProps->setDescription($propertyValue);
break;
}
}
$officePropertyMeta = array();
if (isset($namespacesMeta['dc'])) {
$officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']);
}
foreach ($officePropertyMeta as $propertyName => $propertyValue) {
$propertyValueAttributes = $propertyValue->attributes($namespacesMeta['meta']);
switch ($propertyName) {
case 'initial-creator':
$docProps->setCreator($propertyValue);
break;
case 'keyword':
$docProps->setKeywords($propertyValue);
break;
case 'creation-date':
$creationDate = strtotime($propertyValue);
$docProps->setCreated($creationDate);
break;
case 'user-defined':
$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING;
foreach ($propertyValueAttributes as $key => $value) {
if ($key == 'name') {
$propertyValueName = (string) $value;
} elseif ($key == 'value-type') {
switch ($value) {
case 'date':
$propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'date');
$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_DATE;
break;
case 'boolean':
$propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'bool');
$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_BOOLEAN;
break;
case 'float':
$propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'r4');
$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_FLOAT;
break;
default:
$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING;
}
}
}
$docProps->setCustomProperty($propertyValueName, $propertyValue, $propertyValueType);
break;
}
}
}
// echo '<h1>Workbook Content</h1>';
$xml = simplexml_load_string($zip->getFromName("content.xml"));
$namespacesContent = $xml->getNamespaces(true);
// echo '<pre>';
// print_r($namespacesContent);
// echo '</pre><hr />';
$workbook = $xml->children($namespacesContent['office']);
foreach ($workbook->body->spreadsheet as $workbookData) {
$workbookData = $workbookData->children($namespacesContent['table']);
$worksheetID = 0;
foreach ($workbookData->table as $worksheetDataSet) {
$worksheetData = $worksheetDataSet->children($namespacesContent['table']);
// print_r($worksheetData);
// echo '<br />';
$worksheetDataAttributes = $worksheetDataSet->attributes($namespacesContent['table']);
// print_r($worksheetDataAttributes);
// echo '<br />';
if (isset($this->_loadSheetsOnly) && isset($worksheetDataAttributes['name']) && !in_array($worksheetDataAttributes['name'], $this->_loadSheetsOnly)) {
continue;
}
// echo '<h2>Worksheet '.$worksheetDataAttributes['name'].'</h2>';
// Create new Worksheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($worksheetID);
if (isset($worksheetDataAttributes['name'])) {
$worksheetName = (string) $worksheetDataAttributes['name'];
$objPHPExcel->getActiveSheet()->setTitle($worksheetName);
}
$rowID = 1;
foreach ($worksheetData as $key => $rowData) {
// echo '<b>'.$key.'</b><br />';
switch ($key) {
case 'table-header-rows':
foreach ($rowData as $key => $cellData) {
$rowData = $cellData;
break;
}
case 'table-row':
$columnID = 'A';
foreach ($rowData as $key => $cellData) {
if (!is_null($this->getReadFilter())) {
if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
continue;
}
}
// echo '<b>'.$columnID.$rowID.'</b><br />';
$cellDataText = $cellData->children($namespacesContent['text']);
$cellDataOfficeAttributes = $cellData->attributes($namespacesContent['office']);
$cellDataTableAttributes = $cellData->attributes($namespacesContent['table']);
// echo 'Office Attributes: ';
// print_r($cellDataOfficeAttributes);
// echo '<br />Table Attributes: ';
// print_r($cellDataTableAttributes);
// echo '<br />Cell Data Text';
// print_r($cellDataText);
// echo '<br />';
//
$type = $formatting = $hyperlink = null;
$hasCalculatedValue = false;
$cellDataFormula = '';
if (isset($cellDataTableAttributes['formula'])) {
$cellDataFormula = $cellDataTableAttributes['formula'];
$hasCalculatedValue = true;
}
if (isset($cellDataText->p)) {
// echo 'Value Type is '.$cellDataOfficeAttributes['value-type'].'<br />';
switch ($cellDataOfficeAttributes['value-type']) {
case 'string':
$type = PHPExcel_Cell_DataType::TYPE_STRING;
$dataValue = $cellDataText->p;
if (isset($dataValue->a)) {
$dataValue = $dataValue->a;
$cellXLinkAttributes = $dataValue->attributes($namespacesContent['xlink']);
$hyperlink = $cellXLinkAttributes['href'];
}
break;
case 'boolean':
$type = PHPExcel_Cell_DataType::TYPE_BOOL;
$dataValue = $cellDataText->p == 'TRUE' ? True : False;
break;
case 'float':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dataValue = (double) $cellDataOfficeAttributes['value'];
if (floor($dataValue) == $dataValue) {
$dataValue = (int) $dataValue;
}
break;
case 'date':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dateObj = new DateTime($cellDataOfficeAttributes['date-value'], $GMT);
$dateObj->setTimeZone($timezoneObj);
list($year, $month, $day, $hour, $minute, $second) = explode(' ', $dateObj->format('Y m d H i s'));
$dataValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hour, $minute, $second);
if ($dataValue != floor($dataValue)) {
$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15 . ' ' . PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
} else {
$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15;
}
break;
case 'time':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dataValue = PHPExcel_Shared_Date::PHPToExcel(strtotime('01-01-1970 ' . implode(':', sscanf($cellDataOfficeAttributes['time-value'], 'PT%dH%dM%dS'))));
$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
break;
}
// echo 'Data value is '.$dataValue.'<br />';
// if (!is_null($hyperlink)) {
// echo 'Hyperlink is '.$hyperlink.'<br />';
// }
}
if ($hasCalculatedValue) {
$type = PHPExcel_Cell_DataType::TYPE_FORMULA;
// echo 'Formula: '.$cellDataFormula.'<br />';
$cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=') + 1);
$temp = explode('"', $cellDataFormula);
foreach ($temp as $key => &$value) {
// Only replace in alternate array entries (i.e. non-quoted blocks)
if ($key % 2 == 0) {
$value = preg_replace('/\\[\\.(.*):\\.(.*)\\]/Ui', '$1:$2', $value);
$value = preg_replace('/\\[\\.(.*)\\]/Ui', '$1', $value);
$value = PHPExcel_Calculation::_translateSeparator(';', ',', $value, $inBraces);
}
}
unset($value);
// Then rebuild the formula string
$cellDataFormula = implode('"', $temp);
// echo 'Adjusted Formula: '.$cellDataFormula.'<br />';
}
if (!is_null($type)) {
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $dataValue, $type);
if ($hasCalculatedValue) {
// echo 'Forumla result is '.$dataValue.'<br />';
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($dataValue);
}
if ($cellDataOfficeAttributes['value-type'] == 'date' || $cellDataOfficeAttributes['value-type'] == 'time') {
$objPHPExcel->getActiveSheet()->getStyle($columnID . $rowID)->getNumberFormat()->setFormatCode($formatting);
}
if (!is_null($hyperlink)) {
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->getHyperlink()->setUrl($hyperlink);
}
}
// Merged cells
if (isset($cellDataTableAttributes['number-columns-spanned']) || isset($cellDataTableAttributes['number-rows-spanned'])) {
$columnTo = $columnID;
if (isset($cellDataTableAttributes['number-columns-spanned'])) {
$columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cellDataTableAttributes['number-columns-spanned'] - 2);
}
$rowTo = $rowID;
if (isset($cellDataTableAttributes['number-rows-spanned'])) {
$rowTo = $rowTo + $cellDataTableAttributes['number-rows-spanned'] - 1;
}
$cellRange = $columnID . $rowID . ':' . $columnTo . $rowTo;
$objPHPExcel->getActiveSheet()->mergeCells($cellRange);
}
if (isset($cellDataTableAttributes['number-columns-repeated'])) {
// echo 'Repeated '.$cellDataTableAttributes['number-columns-repeated'].' times<br />';
$columnID = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cellDataTableAttributes['number-columns-repeated'] - 2);
}
++$columnID;
}
++$rowID;
break;
}
}
++$worksheetID;
}
}
}
// Return
return $objPHPExcel;
}
/**
* Load workbook
*
* Loads the specified Excel2003XML file
*
* @param string $filename The file that should be loaded
* @return QExcel_Workbook The loaded workbook
* @throws Exception Invalid file
*/
public function load($filename)
{
if (!$this->canRead($filename)) {
throw new Exception($filename . " is an Invalid Spreadsheet file.");
}
$xml = simplexml_load_file($filename);
$namespaces = $xml->getNamespaces(true);
$sheetId = 0;
$xml_ss = $xml->children($namespaces['ss']);
foreach ($xml_ss->Worksheet as $worksheet) {
// Initialize worksheet
$sheet = $this->_workbook->addSheet();
$worksheet_ss = $worksheet->attributes($namespaces['ss']);
// Set worksheet name
if (isset($worksheet_ss['Name'])) {
$sheet->name = self::_convertStringEncoding((string) $worksheet_ss['Name'], $this->_charSet);
}
// Skip worksheet when not in loadSheetsOnly
if ($this->getLoadSheetsOnly() && isset($worksheet_ss['Name']) && !in_array($worksheet_ss['Name'], $this->getLoadSheetsOnly())) {
continue;
}
$row = 0;
if (isset($worksheet->Table->Row)) {
foreach ($worksheet->Table->Row as $rowData) {
$row_ss = $rowData->attributes($namespaces['ss']);
if (isset($row_ss['Index'])) {
$row = (int) $row_ss['Index'] - 1;
}
$column = 0;
foreach ($rowData->Cell as $cell) {
$cell_ss = $cell->attributes($namespaces['ss']);
if (isset($cell_ss['Index'])) {
$column = $cell_ss['Index'] - 1;
}
if (isset($cell->Data)) {
$cellValue = $cellData = $cell->Data;
$type = PHPExcel_Cell_DataType::TYPE_NULL;
$cellData_ss = $cellData->attributes($namespaces['ss']);
if (isset($cellData_ss['Type'])) {
$cellDataType = $cellData_ss['Type'];
switch ($cellDataType) {
case 'String':
$cellValue = self::_convertStringEncoding($cellValue, $this->_charSet);
$type = PHPExcel_Cell_DataType::TYPE_STRING;
break;
case 'Number':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$cellValue = (double) $cellValue;
if (floor($cellValue) == $cellValue) {
$cellValue = (int) $cellValue;
}
break;
case 'Boolean':
$type = PHPExcel_Cell_DataType::TYPE_BOOL;
$cellValue = $cellValue != 0;
break;
case 'DateTime':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$cellValue = PHPExcel_Shared_Date::PHPToExcel(strtotime($cellValue));
break;
case 'Error':
$type = PHPExcel_Cell_DataType::TYPE_ERROR;
break;
}
}
$sheet->setCell($row, $column, $this->getCellValue($cellValue, $type));
}
$column++;
}
$row++;
}
}
$sheetId++;
}
// Return
return $this->_workbook;
}
public function generateByOfficeByScore()
{
$sheet = $this->workbook->createSheet();
$sheet->setTitle("PA Score (by Office_by score)");
$listOfDepartment = $this->arrayGetColumn($this->summary, 'staff_department');
foreach ($listOfDepartment as $department) {
$sortedArray[$this->getFullOfficeNameByDepartment($department)] = array();
}
unset($sortedArray['Hong Kong Office']);
$sortedArray['HK Senior'] = array();
$sortedArray['HK Junior'] = array();
foreach ($this->summary as $detail) {
if ($this->getFullOfficeNameByDepartment($detail['staff_department']) == 'Hong Kong Office') {
if ($detail['is_senior'] == 1) {
array_push($sortedArray['HK Senior'], $detail);
} else {
array_push($sortedArray['HK Junior'], $detail);
}
} else {
array_push($sortedArray[$this->getFullOfficeNameByDepartment($detail['staff_department'])], $detail);
}
}
$rowOffSet = 1;
uksort($sortedArray, array($this, 'officeNameOrdering'));
foreach ($sortedArray as $officeName => $officeData) {
$avgSum = 0;
$avgCount = 0;
if ($officeName == 'HK Senior' || $officeName == 'HK Junior') {
$sheet->setCellValueByColumnAndRow(0, $rowOffSet, "Hong Kong Office");
} else {
$sheet->setCellValueByColumnAndRow(0, $rowOffSet, $officeName);
}
$rowOffSet += 1;
$colOffSet = 0;
$sheet->setCellValueByColumnAndRow($colOffSet++, $rowOffSet, "Full Name");
$sheet->setCellValueByColumnAndRow($colOffSet++, $rowOffSet, "Position");
$sheet->setCellValueByColumnAndRow($colOffSet++, $rowOffSet, "Join Date");
$sheet->setCellValueByColumnAndRow($colOffSet++, $rowOffSet, "Years of Service");
$sheet->setCellValueByColumnAndRow($colOffSet, $rowOffSet, "Part A score\nbefore countersigning");
$sheet->getStyleByColumnAndRow($colOffSet++, $rowOffSet)->getAlignment()->setWrapText(true);
$sheet->setCellValueByColumnAndRow($colOffSet, $rowOffSet, "Part B1 score\nbefore countersigning");
$sheet->getStyleByColumnAndRow($colOffSet++, $rowOffSet)->getAlignment()->setWrapText(true);
$sheet->setCellValueByColumnAndRow($colOffSet, $rowOffSet, "Part B2 score\nbefore countersigning");
$sheet->getStyleByColumnAndRow($colOffSet++, $rowOffSet)->getAlignment()->setWrapText(true);
$sheet->setCellValueByColumnAndRow($colOffSet, $rowOffSet, "Part A score\nafter countersigning");
$sheet->getStyleByColumnAndRow($colOffSet++, $rowOffSet)->getAlignment()->setWrapText(true);
$sheet->setCellValueByColumnAndRow($colOffSet, $rowOffSet, "Part B after\ncountersigning");
$sheet->getStyleByColumnAndRow($colOffSet++, $rowOffSet)->getAlignment()->setWrapText(true);
$sheet->setCellValueByColumnAndRow($colOffSet, $rowOffSet, "Final Score");
for ($i = 0; $i <= $colOffSet; $i++) {
$sheet->getStyleByColumnAndRow($i, $rowOffSet)->getBorders()->getBottom()->setBorderStyle(TRUE);
}
$rowOffSet += 1;
$arrayTotalScore = $this->arrayGetColumn($officeData, 'part_a_b_total');
array_multisort($arrayTotalScore, SORT_DESC, $officeData);
foreach ($officeData as $detail) {
$colOffSet = 0;
$sheet->setCellValueByColumnAndRow($colOffSet++, $rowOffSet, $detail['staff_name']);
$sheet->setCellValueByColumnAndRow($colOffSet++, $rowOffSet, $detail['staff_position']);
$timestamp = strtotime($detail['survey_commencement_date']);
$sheet->setCellValueByColumnAndRow($colOffSet, $rowOffSet, ceil(PHPExcel_Shared_Date::PHPToExcel($timestamp)));
$sheet->getStyleByColumnAndRow($colOffSet++, $rowOffSet)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
$todayTimeStamp = time();
$dateDiff = floor(($todayTimeStamp - $timestamp) / (365 * 60 * 60 * 24));
$sheet->setCellValueByColumnAndRow($colOffSet++, $rowOffSet, "{$dateDiff} Years");
$sheet->setCellValueByColumnAndRow($colOffSet++, $rowOffSet, $detail['part_a_overall_score']);
$sheet->setCellValueByColumnAndRow($colOffSet++, $rowOffSet, $detail['part_b1_overall_score']);
$sheet->setCellValueByColumnAndRow($colOffSet++, $rowOffSet, $detail['part_b2_overall_score']);
$sheet->setCellValueByColumnAndRow($colOffSet++, $rowOffSet, $detail['part_a_total']);
$sheet->setCellValueByColumnAndRow($colOffSet++, $rowOffSet, $detail['part_b_total']);
$sheet->setCellValueByColumnAndRow($colOffSet, $rowOffSet, $detail['part_a_b_total']);
if (!empty($detail['part_a_b_total'])) {
$avgSum += $detail['part_a_b_total'];
$avgCount += 1;
}
$rowOffSet += 1;
}
if ($avgCount) {
$sheet->setCellValueByColumnAndRow(--$colOffSet, $rowOffSet, "Average: ");
$sheet->setCellValueByColumnAndRow(++$colOffSet, $rowOffSet, round($avgSum / $avgCount, 2));
$rowOffSet += 1;
}
$rowOffSet += 1;
}
for ($i = 0; $i <= 13; $i++) {
$sheet->getColumnDimensionByColumn($i)->setAutoSize(TRUE);
}
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param PHPExcel $objPHPExcel
* @return PHPExcel
* @throws PHPExcel_Reader_Exception
*/
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
{
$fromFormats = array('\\-', '\\ ');
$toFormats = array('-', ' ');
$underlineStyles = array(PHPExcel_Style_Font::UNDERLINE_NONE, PHPExcel_Style_Font::UNDERLINE_DOUBLE, PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING, PHPExcel_Style_Font::UNDERLINE_SINGLE, PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING);
$verticalAlignmentStyles = array(PHPExcel_Style_Alignment::VERTICAL_BOTTOM, PHPExcel_Style_Alignment::VERTICAL_TOP, PHPExcel_Style_Alignment::VERTICAL_CENTER, PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
$horizontalAlignmentStyles = array(PHPExcel_Style_Alignment::HORIZONTAL_GENERAL, PHPExcel_Style_Alignment::HORIZONTAL_LEFT, PHPExcel_Style_Alignment::HORIZONTAL_RIGHT, PHPExcel_Style_Alignment::HORIZONTAL_CENTER, PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS, PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$timezoneObj = new DateTimeZone('Europe/London');
$GMT = new DateTimeZone('UTC');
// Check if file exists
if (!file_exists($pFilename)) {
throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
if (!$this->canRead($pFilename)) {
throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file.");
}
$xml = simplexml_load_file($pFilename, 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
$namespaces = $xml->getNamespaces(true);
$docProps = $objPHPExcel->getProperties();
if (isset($xml->DocumentProperties[0])) {
foreach ($xml->DocumentProperties[0] as $propertyName => $propertyValue) {
switch ($propertyName) {
case 'Title':
$docProps->setTitle(self::_convertStringEncoding($propertyValue, $this->_charSet));
break;
case 'Subject':
$docProps->setSubject(self::_convertStringEncoding($propertyValue, $this->_charSet));
break;
case 'Author':
$docProps->setCreator(self::_convertStringEncoding($propertyValue, $this->_charSet));
break;
case 'Created':
$creationDate = strtotime($propertyValue);
$docProps->setCreated($creationDate);
break;
case 'LastAuthor':
$docProps->setLastModifiedBy(self::_convertStringEncoding($propertyValue, $this->_charSet));
break;
case 'LastSaved':
$lastSaveDate = strtotime($propertyValue);
$docProps->setModified($lastSaveDate);
break;
case 'Company':
$docProps->setCompany(self::_convertStringEncoding($propertyValue, $this->_charSet));
break;
case 'Category':
$docProps->setCategory(self::_convertStringEncoding($propertyValue, $this->_charSet));
break;
case 'Manager':
$docProps->setManager(self::_convertStringEncoding($propertyValue, $this->_charSet));
break;
case 'Keywords':
$docProps->setKeywords(self::_convertStringEncoding($propertyValue, $this->_charSet));
break;
case 'Description':
$docProps->setDescription(self::_convertStringEncoding($propertyValue, $this->_charSet));
break;
}
}
}
if (isset($xml->CustomDocumentProperties)) {
foreach ($xml->CustomDocumentProperties[0] as $propertyName => $propertyValue) {
$propertyAttributes = $propertyValue->attributes($namespaces['dt']);
$propertyName = preg_replace_callback('/_x([0-9a-z]{4})_/', 'PHPExcel_Reader_Excel2003XML::_hex2str', $propertyName);
$propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_UNKNOWN;
switch ((string) $propertyAttributes) {
case 'string':
$propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING;
$propertyValue = trim($propertyValue);
break;
case 'boolean':
$propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_BOOLEAN;
$propertyValue = (bool) $propertyValue;
break;
case 'integer':
$propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_INTEGER;
$propertyValue = intval($propertyValue);
break;
case 'float':
$propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_FLOAT;
$propertyValue = floatval($propertyValue);
break;
case 'dateTime.tz':
$propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_DATE;
$propertyValue = strtotime(trim($propertyValue));
break;
}
$docProps->setCustomProperty($propertyName, $propertyValue, $propertyType);
}
}
foreach ($xml->Styles[0] as $style) {
$style_ss = $style->attributes($namespaces['ss']);
$styleID = (string) $style_ss['ID'];
// echo 'Style ID = '.$styleID.'<br />';
if ($styleID == 'Default') {
$this->_styles['Default'] = array();
} else {
$this->_styles[$styleID] = $this->_styles['Default'];
}
foreach ($style as $styleType => $styleData) {
$styleAttributes = $styleData->attributes($namespaces['ss']);
// echo $styleType.'<br />';
switch ($styleType) {
case 'Alignment':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = (string) $styleAttributeValue;
switch ($styleAttributeKey) {
case 'Vertical':
if (self::identifyFixedStyleValue($verticalAlignmentStyles, $styleAttributeValue)) {
$this->_styles[$styleID]['alignment']['vertical'] = $styleAttributeValue;
}
break;
case 'Horizontal':
if (self::identifyFixedStyleValue($horizontalAlignmentStyles, $styleAttributeValue)) {
$this->_styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue;
}
break;
case 'WrapText':
$this->_styles[$styleID]['alignment']['wrap'] = true;
break;
}
}
break;
case 'Borders':
foreach ($styleData->Border as $borderStyle) {
$borderAttributes = $borderStyle->attributes($namespaces['ss']);
$thisBorder = array();
foreach ($borderAttributes as $borderStyleKey => $borderStyleValue) {
// echo $borderStyleKey.' = '.$borderStyleValue.'<br />';
switch ($borderStyleKey) {
case 'LineStyle':
$thisBorder['style'] = PHPExcel_Style_Border::BORDER_MEDIUM;
// $thisBorder['style'] = $borderStyleValue;
break;
case 'Weight':
// $thisBorder['style'] = $borderStyleValue;
break;
case 'Position':
$borderPosition = strtolower($borderStyleValue);
break;
case 'Color':
$borderColour = substr($borderStyleValue, 1);
$thisBorder['color']['rgb'] = $borderColour;
break;
}
}
if (!empty($thisBorder)) {
if ($borderPosition == 'left' || $borderPosition == 'right' || $borderPosition == 'top' || $borderPosition == 'bottom') {
$this->_styles[$styleID]['borders'][$borderPosition] = $thisBorder;
}
}
}
break;
case 'Font':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = (string) $styleAttributeValue;
switch ($styleAttributeKey) {
case 'FontName':
$this->_styles[$styleID]['font']['name'] = $styleAttributeValue;
break;
case 'Size':
$this->_styles[$styleID]['font']['size'] = $styleAttributeValue;
break;
case 'Color':
$this->_styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue, 1);
break;
case 'Bold':
$this->_styles[$styleID]['font']['bold'] = true;
break;
case 'Italic':
$this->_styles[$styleID]['font']['italic'] = true;
break;
case 'Underline':
if (self::identifyFixedStyleValue($underlineStyles, $styleAttributeValue)) {
$this->_styles[$styleID]['font']['underline'] = $styleAttributeValue;
}
break;
}
}
break;
case 'Interior':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
switch ($styleAttributeKey) {
case 'Color':
$this->_styles[$styleID]['fill']['color']['rgb'] = substr($styleAttributeValue, 1);
break;
}
}
break;
case 'NumberFormat':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = str_replace($fromFormats, $toFormats, $styleAttributeValue);
switch ($styleAttributeValue) {
case 'Short Date':
$styleAttributeValue = 'dd/mm/yyyy';
break;
}
if ($styleAttributeValue > '') {
$this->_styles[$styleID]['numberformat']['code'] = $styleAttributeValue;
}
}
break;
case 'Protection':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
}
break;
}
}
// print_r($this->_styles[$styleID]);
// echo '<hr />';
}
// echo '<hr />';
$worksheetID = 0;
$xml_ss = $xml->children($namespaces['ss']);
foreach ($xml_ss->Worksheet as $worksheet) {
$worksheet_ss = $worksheet->attributes($namespaces['ss']);
if (isset($this->_loadSheetsOnly) && isset($worksheet_ss['Name']) && !in_array($worksheet_ss['Name'], $this->_loadSheetsOnly)) {
continue;
}
// echo '<h3>Worksheet: ',$worksheet_ss['Name'],'<h3>';
//
// Create new Worksheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($worksheetID);
if (isset($worksheet_ss['Name'])) {
$worksheetName = self::_convertStringEncoding((string) $worksheet_ss['Name'], $this->_charSet);
// 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
$objPHPExcel->getActiveSheet()->setTitle($worksheetName, false);
}
$columnID = 'A';
if (isset($worksheet->Table->Column)) {
foreach ($worksheet->Table->Column as $columnData) {
$columnData_ss = $columnData->attributes($namespaces['ss']);
if (isset($columnData_ss['Index'])) {
$columnID = PHPExcel_Cell::stringFromColumnIndex($columnData_ss['Index'] - 1);
}
if (isset($columnData_ss['Width'])) {
$columnWidth = $columnData_ss['Width'];
// echo '<b>Setting column width for '.$columnID.' to '.$columnWidth.'</b><br />';
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
}
++$columnID;
}
}
$rowID = 1;
if (isset($worksheet->Table->Row)) {
foreach ($worksheet->Table->Row as $rowData) {
$rowHasData = false;
$row_ss = $rowData->attributes($namespaces['ss']);
if (isset($row_ss['Index'])) {
$rowID = (int) $row_ss['Index'];
}
// echo '<b>Row '.$rowID.'</b><br />';
$columnID = 'A';
foreach ($rowData->Cell as $cell) {
$cell_ss = $cell->attributes($namespaces['ss']);
if (isset($cell_ss['Index'])) {
$columnID = PHPExcel_Cell::stringFromColumnIndex($cell_ss['Index'] - 1);
}
$cellRange = $columnID . $rowID;
if ($this->getReadFilter() !== NULL) {
if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
continue;
}
}
if (isset($cell_ss['MergeAcross']) || isset($cell_ss['MergeDown'])) {
$columnTo = $columnID;
if (isset($cell_ss['MergeAcross'])) {
$columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cell_ss['MergeAcross'] - 1);
}
$rowTo = $rowID;
if (isset($cell_ss['MergeDown'])) {
$rowTo = $rowTo + $cell_ss['MergeDown'];
}
$cellRange .= ':' . $columnTo . $rowTo;
$objPHPExcel->getActiveSheet()->mergeCells($cellRange);
}
$cellIsSet = $hasCalculatedValue = false;
$cellDataFormula = '';
if (isset($cell_ss['Formula'])) {
$cellDataFormula = $cell_ss['Formula'];
// added this as a check for array formulas
if (isset($cell_ss['ArrayRange'])) {
$cellDataCSEFormula = $cell_ss['ArrayRange'];
// echo "found an array formula at ".$columnID.$rowID."<br />";
}
$hasCalculatedValue = true;
}
if (isset($cell->Data)) {
$cellValue = $cellData = $cell->Data;
$type = PHPExcel_Cell_DataType::TYPE_NULL;
$cellData_ss = $cellData->attributes($namespaces['ss']);
if (isset($cellData_ss['Type'])) {
$cellDataType = $cellData_ss['Type'];
switch ($cellDataType) {
/*
const TYPE_STRING = 's';
const TYPE_FORMULA = 'f';
const TYPE_NUMERIC = 'n';
const TYPE_BOOL = 'b';
const TYPE_NULL = 'null';
const TYPE_INLINE = 'inlineStr';
const TYPE_ERROR = 'e';
*/
case 'String':
$cellValue = self::_convertStringEncoding($cellValue, $this->_charSet);
$type = PHPExcel_Cell_DataType::TYPE_STRING;
break;
case 'Number':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$cellValue = (double) $cellValue;
if (floor($cellValue) == $cellValue) {
$cellValue = (int) $cellValue;
}
break;
case 'Boolean':
$type = PHPExcel_Cell_DataType::TYPE_BOOL;
$cellValue = $cellValue != 0;
break;
case 'DateTime':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$cellValue = PHPExcel_Shared_Date::PHPToExcel(strtotime($cellValue));
break;
case 'Error':
$type = PHPExcel_Cell_DataType::TYPE_ERROR;
break;
}
}
if ($hasCalculatedValue) {
// echo 'FORMULA<br />';
$type = PHPExcel_Cell_DataType::TYPE_FORMULA;
$columnNumber = PHPExcel_Cell::columnIndexFromString($columnID);
if (substr($cellDataFormula, 0, 3) == 'of:') {
$cellDataFormula = substr($cellDataFormula, 3);
// echo 'Before: ',$cellDataFormula,'<br />';
$temp = explode('"', $cellDataFormula);
$key = false;
foreach ($temp as &$value) {
// Only replace in alternate array entries (i.e. non-quoted blocks)
if ($key = !$key) {
$value = str_replace(array('[.', '.', ']'), '', $value);
}
}
} else {
// Convert R1C1 style references to A1 style references (but only when not quoted)
// echo 'Before: ',$cellDataFormula,'<br />';
$temp = explode('"', $cellDataFormula);
$key = false;
foreach ($temp as &$value) {
// Only replace in alternate array entries (i.e. non-quoted blocks)
if ($key = !$key) {
preg_match_all('/(R(\\[?-?\\d*\\]?))(C(\\[?-?\\d*\\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
// Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
// through the formula from left to right. Reversing means that we work right to left.through
// the formula
$cellReferences = array_reverse($cellReferences);
// Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
// then modify the formula to use that new reference
foreach ($cellReferences as $cellReference) {
$rowReference = $cellReference[2][0];
// Empty R reference is the current row
if ($rowReference == '') {
$rowReference = $rowID;
}
// Bracketed R references are relative to the current row
if ($rowReference[0] == '[') {
$rowReference = $rowID + trim($rowReference, '[]');
}
$columnReference = $cellReference[4][0];
// Empty C reference is the current column
if ($columnReference == '') {
$columnReference = $columnNumber;
}
// Bracketed C references are relative to the current column
if ($columnReference[0] == '[') {
$columnReference = $columnNumber + trim($columnReference, '[]');
}
$A1CellReference = PHPExcel_Cell::stringFromColumnIndex($columnReference - 1) . $rowReference;
$value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
}
}
}
}
unset($value);
// Then rebuild the formula string
$cellDataFormula = implode('"', $temp);
// echo 'After: ',$cellDataFormula,'<br />';
}
// echo 'Cell '.$columnID.$rowID.' is a '.$type.' with a value of '.(($hasCalculatedValue) ? $cellDataFormula : $cellValue).'<br />';
//
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $cellValue, $type);
if ($hasCalculatedValue) {
// echo 'Formula result is '.$cellValue.'<br />';
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue);
}
$cellIsSet = $rowHasData = true;
}
if (isset($cell->Comment)) {
// echo '<b>comment found</b><br />';
$commentAttributes = $cell->Comment->attributes($namespaces['ss']);
$author = 'unknown';
if (isset($commentAttributes->Author)) {
$author = (string) $commentAttributes->Author;
// echo 'Author: ',$author,'<br />';
}
$node = $cell->Comment->Data->asXML();
// $annotation = str_replace('html:','',substr($node,49,-10));
// echo $annotation,'<br />';
$annotation = strip_tags($node);
// echo 'Annotation: ',$annotation,'<br />';
$objPHPExcel->getActiveSheet()->getComment($columnID . $rowID)->setAuthor(self::_convertStringEncoding($author, $this->_charSet))->setText($this->_parseRichText($annotation));
}
if ($cellIsSet && isset($cell_ss['StyleID'])) {
$style = (string) $cell_ss['StyleID'];
// echo 'Cell style for '.$columnID.$rowID.' is '.$style.'<br />';
if (isset($this->_styles[$style]) && !empty($this->_styles[$style])) {
// echo 'Cell '.$columnID.$rowID.'<br />';
// print_r($this->_styles[$style]);
// echo '<br />';
if (!$objPHPExcel->getActiveSheet()->cellExists($columnID . $rowID)) {
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValue(NULL);
}
$objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->_styles[$style]);
}
}
++$columnID;
}
if ($rowHasData) {
if (isset($row_ss['StyleID'])) {
$rowStyle = $row_ss['StyleID'];
}
if (isset($row_ss['Height'])) {
$rowHeight = $row_ss['Height'];
// echo '<b>Setting row height to '.$rowHeight.'</b><br />';
$objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight);
}
}
++$rowID;
}
}
++$worksheetID;
}
// Return
return $objPHPExcel;
}
* @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');
/** PHPExcel_IOFactory */
require_once '../Classes/PHPExcel/IOFactory.php';
echo date('H:i:s'), " Load from Excel5 template", EOL;
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load("templates/30template.xls");
echo date('H:i:s'), " Add new data to the template", EOL;
$data = array(array('title' => 'Excel for dummies', 'price' => 17.99, 'quantity' => 2), array('title' => 'PHP for dummies', 'price' => 15.99, 'quantity' => 1), array('title' => 'Inside OOP', 'price' => 12.95, 'quantity' => 1));
$objPHPExcel->getActiveSheet()->setCellValue('D1', PHPExcel_Shared_Date::PHPToExcel(time()));
$baseRow = 5;
foreach ($data as $r => $dataRow) {
$row = $baseRow + $r;
$objPHPExcel->getActiveSheet()->insertNewRowBefore($row, 1);
$objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $r + 1)->setCellValue('B' . $row, $dataRow['title'])->setCellValue('C' . $row, $dataRow['price'])->setCellValue('D' . $row, $dataRow['quantity'])->setCellValue('E' . $row, '=C' . $row . '*D' . $row);
}
$objPHPExcel->getActiveSheet()->removeRow($baseRow - 1, 1);
echo date('H:i:s'), " Write to Excel5 format", EOL;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace('.php', '.xls', __FILE__));
echo date('H:i:s'), " File written to ", str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;
// Echo memory peak usage
echo date('H:i:s'), " Peak memory usage: ", memory_get_peak_usage(true) / 1024 / 1024, " MB", EOL;
// Echo done
echo date('H:i:s'), " Done writing file", EOL;
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param PHPExcel $objPHPExcel
* @return PHPExcel
* @throws Exception
*/
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
{
$fromFormats = array('\\-', '\\ ');
$toFormats = array('-', ' ');
$underlineStyles = array(PHPExcel_Style_Font::UNDERLINE_NONE, PHPExcel_Style_Font::UNDERLINE_DOUBLE, PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING, PHPExcel_Style_Font::UNDERLINE_SINGLE, PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING);
$verticalAlignmentStyles = array(PHPExcel_Style_Alignment::VERTICAL_BOTTOM, PHPExcel_Style_Alignment::VERTICAL_TOP, PHPExcel_Style_Alignment::VERTICAL_CENTER, PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
$horizontalAlignmentStyles = array(PHPExcel_Style_Alignment::HORIZONTAL_GENERAL, PHPExcel_Style_Alignment::HORIZONTAL_LEFT, PHPExcel_Style_Alignment::HORIZONTAL_RIGHT, PHPExcel_Style_Alignment::HORIZONTAL_CENTER, PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS, PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
$xml = simplexml_load_file($pFilename);
$namespaces = $xml->getNamespaces(true);
// echo '<pre>';
// print_r($namespaces);
// echo '</pre><hr />';
//
// echo '<pre>';
// print_r($xml);
// echo '</pre><hr />';
//
$docProps = $objPHPExcel->getProperties();
foreach ($xml->DocumentProperties[0] as $propertyName => $propertyValue) {
switch ($propertyName) {
case 'Title':
$docProps->setTitle($propertyValue);
break;
case 'Subject':
$docProps->setSubject($propertyValue);
break;
case 'Author':
$docProps->setCreator($propertyValue);
break;
case 'Created':
$creationDate = strtotime($propertyValue);
$docProps->setCreated($creationDate);
break;
case 'LastAuthor':
$docProps->setLastModifiedBy($propertyValue);
break;
case 'Company':
$docProps->setCompany($propertyValue);
break;
case 'Category':
$docProps->setCategory($propertyValue);
break;
case 'Keywords':
$docProps->setKeywords($propertyValue);
break;
case 'Description':
$docProps->setDescription($propertyValue);
break;
}
}
foreach ($xml->Styles[0] as $style) {
$style_ss = $style->attributes($namespaces['ss']);
$styleID = (string) $style_ss['ID'];
// echo 'Style ID = '.$styleID.'<br />';
if ($styleID == 'Default') {
$this->_styles['Default'] = array();
} else {
$this->_styles[$styleID] = $this->_styles['Default'];
}
foreach ($style as $styleType => $styleData) {
$styleAttributes = $styleData->attributes($namespaces['ss']);
// echo $styleType.'<br />';
switch ($styleType) {
case 'Alignment':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = (string) $styleAttributeValue;
switch ($styleAttributeKey) {
case 'Vertical':
if (self::identifyFixedStyleValue($verticalAlignmentStyles, $styleAttributeValue)) {
$this->_styles[$styleID]['alignment']['vertical'] = $styleAttributeValue;
}
break;
case 'Horizontal':
if (self::identifyFixedStyleValue($horizontalAlignmentStyles, $styleAttributeValue)) {
$this->_styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue;
}
break;
case 'WrapText':
$this->_styles[$styleID]['alignment']['wrap'] = true;
break;
}
}
break;
case 'Borders':
foreach ($styleData->Border as $borderStyle) {
$borderAttributes = $borderStyle->attributes($namespaces['ss']);
$thisBorder = array();
foreach ($borderAttributes as $borderStyleKey => $borderStyleValue) {
// echo $borderStyleKey.' = '.$borderStyleValue.'<br />';
switch ($borderStyleKey) {
case 'LineStyle':
$thisBorder['style'] = PHPExcel_Style_Border::BORDER_MEDIUM;
// $thisBorder['style'] = $borderStyleValue;
break;
case 'Weight':
// $thisBorder['style'] = $borderStyleValue;
break;
case 'Position':
$borderPosition = strtolower($borderStyleValue);
break;
case 'Color':
$borderColour = substr($borderStyleValue, 1);
$thisBorder['color']['rgb'] = $borderColour;
break;
}
}
if (count($thisBorder) > 0) {
if ($borderPosition == 'left' || $borderPosition == 'right' || $borderPosition == 'top' || $borderPosition == 'bottom') {
$this->_styles[$styleID]['borders'][$borderPosition] = $thisBorder;
}
}
}
break;
case 'Font':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = (string) $styleAttributeValue;
switch ($styleAttributeKey) {
case 'FontName':
$this->_styles[$styleID]['font']['name'] = $styleAttributeValue;
break;
case 'Size':
$this->_styles[$styleID]['font']['size'] = $styleAttributeValue;
break;
case 'Color':
$this->_styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue, 1);
break;
case 'Bold':
$this->_styles[$styleID]['font']['bold'] = true;
break;
case 'Italic':
$this->_styles[$styleID]['font']['italic'] = true;
break;
case 'Underline':
if (self::identifyFixedStyleValue($underlineStyles, $styleAttributeValue)) {
$this->_styles[$styleID]['font']['underline'] = $styleAttributeValue;
}
break;
}
}
break;
case 'Interior':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
switch ($styleAttributeKey) {
case 'Color':
$this->_styles[$styleID]['fill']['color']['rgb'] = substr($styleAttributeValue, 1);
break;
}
}
break;
case 'NumberFormat':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = str_replace($fromFormats, $toFormats, $styleAttributeValue);
switch ($styleAttributeValue) {
case 'Short Date':
$styleAttributeValue = 'dd/mm/yyyy';
break;
}
if ($styleAttributeValue > '') {
$this->_styles[$styleID]['numberformat']['code'] = $styleAttributeValue;
}
}
break;
case 'Protection':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
}
break;
}
}
// print_r($this->_styles[$styleID]);
// echo '<hr />';
}
// echo '<hr />';
$worksheetID = 0;
foreach ($xml->Worksheet as $worksheet) {
$worksheet_ss = $worksheet->attributes($namespaces['ss']);
if (isset($this->_loadSheetsOnly) && isset($worksheet_ss['Name']) && !in_array($worksheet_ss['Name'], $this->_loadSheetsOnly)) {
continue;
}
// Create new Worksheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($worksheetID);
if (isset($worksheet_ss['Name'])) {
$worksheetName = (string) $worksheet_ss['Name'];
$objPHPExcel->getActiveSheet()->setTitle($worksheetName);
}
$columnID = 'A';
foreach ($worksheet->Table->Column as $columnData) {
$columnData_ss = $columnData->attributes($namespaces['ss']);
if (isset($columnData_ss['Index'])) {
$columnID = PHPExcel_Cell::stringFromColumnIndex($columnData_ss['Index'] - 1);
}
if (isset($columnData_ss['Width'])) {
$columnWidth = $columnData_ss['Width'];
// echo '<b>Setting column width for '.$columnID.' to '.$columnWidth.'</b><br />';
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
}
++$columnID;
}
$rowID = 1;
foreach ($worksheet->Table->Row as $rowData) {
$row_ss = $rowData->attributes($namespaces['ss']);
if (isset($row_ss['Index'])) {
$rowID = (int) $row_ss['Index'];
}
// echo '<b>Row '.$rowID.'</b><br />';
if (isset($row_ss['StyleID'])) {
$rowStyle = $row_ss['StyleID'];
}
if (isset($row_ss['Height'])) {
$rowHeight = $row_ss['Height'];
// echo '<b>Setting row height to '.$rowHeight.'</b><br />';
$objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight);
}
$columnID = 'A';
foreach ($rowData->Cell as $cell) {
$cell_ss = $cell->attributes($namespaces['ss']);
if (isset($cell_ss['Index'])) {
$columnID = PHPExcel_Cell::stringFromColumnIndex($cell_ss['Index'] - 1);
}
$cellRange = $columnID . $rowID;
if (isset($cell_ss['MergeAcross']) || isset($cell_ss['MergeDown'])) {
$columnTo = $columnID;
if (isset($cell_ss['MergeAcross'])) {
$columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cell_ss['MergeAcross'] - 1);
}
$rowTo = $rowID;
if (isset($cell_ss['MergeDown'])) {
$rowTo = $rowTo + $cell_ss['MergeDown'];
}
$cellRange .= ':' . $columnTo . $rowTo;
$objPHPExcel->getActiveSheet()->mergeCells($cellRange);
}
$hasCalculatedValue = false;
$cellDataFormula = '';
if (isset($cell_ss['Formula'])) {
$cellDataFormula = $cell_ss['Formula'];
$hasCalculatedValue = true;
}
if (isset($cell->Data)) {
$cellValue = $cellData = $cell->Data;
$type = PHPExcel_Cell_DataType::TYPE_NULL;
$cellData_ss = $cellData->attributes($namespaces['ss']);
if (isset($cellData_ss['Type'])) {
$cellDataType = $cellData_ss['Type'];
switch ($cellDataType) {
/*
const TYPE_STRING = 's';
const TYPE_FORMULA = 'f';
const TYPE_NUMERIC = 'n';
const TYPE_BOOL = 'b';
const TYPE_NULL = 's';
const TYPE_INLINE = 'inlineStr';
const TYPE_ERROR = 'e';
*/
case 'String':
$type = PHPExcel_Cell_DataType::TYPE_STRING;
break;
case 'Number':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$cellValue = (double) $cellValue;
if (floor($cellValue) == $cellValue) {
$cellValue = (int) $cellValue;
}
break;
case 'Boolean':
$type = PHPExcel_Cell_DataType::TYPE_BOOL;
$cellValue = $cellValue != 0;
break;
case 'DateTime':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$cellValue = PHPExcel_Shared_Date::PHPToExcel(strtotime($cellValue));
break;
case 'Error':
$type = PHPExcel_Cell_DataType::TYPE_ERROR;
break;
}
}
if ($hasCalculatedValue) {
$type = PHPExcel_Cell_DataType::TYPE_FORMULA;
$columnNumber = PHPExcel_Cell::columnIndexFromString($columnID);
// Convert R1C1 style references to A1 style references (but only when not quoted)
$temp = explode('"', $cellDataFormula);
foreach ($temp as $key => &$value) {
// Only replace in alternate array entries (i.e. non-quoted blocks)
if ($key % 2 == 0) {
preg_match_all('/(R(\\[?-?\\d*\\]?))(C(\\[?-?\\d*\\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
// Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
// through the formula from left to right. Reversing means that we work right to left.through
// the formula
$cellReferences = array_reverse($cellReferences);
// Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
// then modify the formula to use that new reference
foreach ($cellReferences as $cellReference) {
$rowReference = $cellReference[2][0];
// Empty R reference is the current row
if ($rowReference == '') {
$rowReference = $rowID;
}
// Bracketed R references are relative to the current row
if ($rowReference[0] == '[') {
$rowReference = $rowID + trim($rowReference, '[]');
}
$columnReference = $cellReference[4][0];
// Empty C reference is the current column
if ($columnReference == '') {
$columnReference = $columnNumber;
}
// Bracketed C references are relative to the current column
if ($columnReference[0] == '[') {
$columnReference = $columnNumber + trim($columnReference, '[]');
}
$A1CellReference = PHPExcel_Cell::stringFromColumnIndex($columnReference - 1) . $rowReference;
$value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
}
}
}
unset($value);
// Then rebuild the formula string
$cellDataFormula = implode('"', $temp);
}
// echo 'Cell '.$columnID.$rowID.' is a '.$type.' with a value of '.(($hasCalculatedValue) ? $cellDataFormula : $cellValue).'<br />';
//
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $cellValue, $type);
if ($hasCalculatedValue) {
// echo 'Forumla result is '.$cellValue.'<br />';
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue);
}
}
if (isset($cell_ss['StyleID'])) {
$style = (string) $cell_ss['StyleID'];
// echo 'Cell style for '.$columnID.$rowID.' is '.$style.'<br />';
if (isset($this->_styles[$style]) && count($this->_styles[$style]) > 0) {
// echo 'Cell '.$columnID.$rowID.'<br />';
// print_r($this->_styles[$style]);
// echo '<br />';
if (!$objPHPExcel->getActiveSheet()->cellExists($columnID . $rowID)) {
$objPHPExcel->getActiveSheet()->setCellValue($columnID . $rowID, NULL);
}
$objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->_styles[$style]);
}
}
++$columnID;
}
++$rowID;
}
++$worksheetID;
}
// Return
return $objPHPExcel;
}
public function create_excel($cart, $data)
{
/** Error reporting */
error_reporting(E_ALL);
//define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
date_default_timezone_set('Europe/London');
/** Include PHPExcel */
require_once dirname(__FILE__) . '/excel/PHPExcel.php';
// Create new PHPExcel object
//echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();
// Set document properties
//echo date('H:i:s') , " Set document properties" , EOL;
$objPHPExcel->getProperties()->setCreator("Margina Radu")->setLastModifiedBy("Margina Radu")->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', 'Corden: Invoice');
$objPHPExcel->getActiveSheet()->setCellValue('F1', PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, date('m'), date('d'), date('Y'))));
$objPHPExcel->getActiveSheet()->getStyle('F1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);
//$objPHPExcel->getActiveSheet()->setCellValue('E1', '#12566');
$objPHPExcel->getActiveSheet()->setCellValue('A12', 'ID');
$objPHPExcel->getActiveSheet()->setCellValue('B12', 'Simbol');
$objPHPExcel->getActiveSheet()->setCellValue('C12', 'Denumire');
$objPHPExcel->getActiveSheet()->setCellValue('D12', 'Cantitatea');
$objPHPExcel->getActiveSheet()->setCellValue('E12', 'Pret');
$objPHPExcel->getActiveSheet()->setCellValue('F12', 'Subtotal');
$index = 13;
$start_index = 13;
foreach ($cart as $item) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . $index, $item['id']);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $index, $item['simbol']);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $index, $item['name']);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $index, $item['qty']);
$objPHPExcel->getActiveSheet()->setCellValue('E' . $index, $item['price']);
$objPHPExcel->getActiveSheet()->setCellValue('F' . $index, $item['subtotal']);
$index++;
}
$end_index = $index - 1;
$total_index = $index + 1;
$objPHPExcel->getActiveSheet()->setCellValue('E' . $total_index, 'Pret Total:');
$objPHPExcel->getActiveSheet()->setCellValue('F' . $total_index, '=SUM(F13:F' . $end_index . ')');
// Add comment
//echo date('H:i:s') , " Add comments" , EOL;
$objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->getText()->createTextRun('Total amount on the current invoice, including VAT.');
$objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->setWidth('100pt');
$objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->setHeight('100pt');
$objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->setMarginLeft('150pt');
$objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->getFillColor()->setRGB('EEEEEE');
// Add rich-text string
//echo date('H:i:s') , " Add rich-text string" , EOL;
$objRichText = new PHPExcel_RichText();
$objRichText->createText("Nume: ");
$objPayable = $objRichText->createTextRun($data['nume'] . " " . $data['prenume']);
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText("\nEmail : ");
$objPayable = $objRichText->createTextRun($data['email']);
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText("\nTelefon : ");
$objPayable = $objRichText->createTextRun($data['telefon']);
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText("\nAdresa : ");
$objPayable = $objRichText->createTextRun($data['adresa']['orasul'] . "," . $data['adresa']['localitatea'] . ", str. " . $data['adresa']['strada'] . " " . $data['adresa']['numarul'] . ", ap. " . $data['adresa']['apartament']);
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText("\nMetoda de plata : ");
$objPayable = $objRichText->createTextRun($data['payment']);
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText("\nSunet de confirmare : ");
$objPayable = $objRichText->createTextRun(isset($data['call_after']) ? $data['call_after'] : 'nu');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objPHPExcel->getActiveSheet()->getCell('A3')->setValue($objRichText);
// Merge cells
//echo date('H:i:s') , " Merge cells" , EOL;
$objPHPExcel->getActiveSheet()->mergeCells('A3:F10');
// Just to test...
// Protect cells
//echo date('H:i:s') , " Protect cells" , EOL;
// $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
//$objPHPExcel->getActiveSheet()->protectCells('A3:F13', 'PHPExcel');
// Set cell number formats
//echo date('H:i:s') , " Set cell number formats" , EOL;
//$objPHPExcel->getActiveSheet()->getStyle('E4:F13')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
// Set column widths
//echo date('H:i:s') , " Set column widths" , EOL;
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
// Set fonts
//echo date('H:i:s') , " Set fonts" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('F1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('E' . $total_index)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('F' . $total_index)->getFont()->setBold(true);
// Set alignments
//echo date('H:i:s') , " Set alignments" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('F' . $total_index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B13')->getAlignment()->setShrinkToFit(true);
// Set thin black border outline around column
//echo date('H:i:s') , " Set thin black border outline around column" , EOL;
$styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000'))));
$objPHPExcel->getActiveSheet()->getStyle('A13:F' . $end_index)->applyFromArray($styleThinBlackBorderOutline);
// Set thick brown border outline around "Total"
//echo date('H:i:s') , " Set thick brown border outline around Total" , EOL;
$styleThickBrownBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THICK, 'color' => array('argb' => 'FF993300'))));
$objPHPExcel->getActiveSheet()->getStyle('E' . $total_index . ':F' . $total_index . '')->applyFromArray($styleThickBrownBorderOutline);
// Set fills
//echo date('H:i:s') , " Set fills" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getFill()->getStartColor()->setARGB('FF808080');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('80FFFFFF');
// Set style for header row using alternative method
//echo date('H:i:s') , " Set style for header row using alternative method" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('A12:F12')->applyFromArray(array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => 'FFA0A0A0'), 'endcolor' => array('argb' => 'FFFFFFFF'))));
$objPHPExcel->getActiveSheet()->getStyle('A12')->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('left' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$objPHPExcel->getActiveSheet()->getStyle('B12')->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)));
$objPHPExcel->getActiveSheet()->getStyle('F12')->applyFromArray(array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
// Unprotect a cell
//echo date('H:i:s') , " Unprotect a cell" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
// Add a drawing to the worksheet
//echo date('H:i:s') , " Add a drawing to the worksheet" , EOL;
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath($_SERVER['DOCUMENT_ROOT'] . '/assets/img/images/logo.jpg');
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
// Set header and footer. When no different headers for odd/even are used, odd header is assumed.
//echo date('H:i:s') , " Set header/footer" , EOL;
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BInvoice&RPrinted on &D');
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');
// Set page orientation and size
//echo date('H:i:s') , " Set page orientation and size" , EOL;
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
// Rename first worksheet
//echo date('H:i:s') , " Rename first worksheet" , EOL;
$objPHPExcel->getActiveSheet()->setTitle('Invoice');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
/** Include PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/excel/PHPExcel/IOFactory.php';
// Save Excel 2007 file
//echo date('H:i:s') , " Write to Excel2007 format" , EOL;
$callStartTime = microtime(true);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', 'invoice.xlsx'));
//echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
//echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
// Echo memory usage
//echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
// Echo done
//echo date('H:i:s') , " Done writing files" , EOL;
//echo 'Files have been created in ' , getcwd() , EOL;
}
/**
* EOMONTH
*
* Returns the serial number for the last day of the month that is the indicated number of months before or after start_date.
* Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
*
* @param long $dateValue Excel date serial value or a standard date string
* @param int $adjustmentMonths Number of months to adjust by
* @return long Excel date serial value
*/
public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0)
{
$dateValue = self::flattenSingleValue($dateValue);
$adjustmentMonths = floor(self::flattenSingleValue($adjustmentMonths));
if (!is_numeric($adjustmentMonths)) {
return self::$_errorCodes['value'];
}
if (is_string($dateValue = self::_getDateValue($dateValue))) {
return self::$_errorCodes['value'];
}
// Execute function
$PHPDateObject = self::_adjustDateByMonths($dateValue, $adjustmentMonths + 1);
$adjustDays = (int) $PHPDateObject->format('d');
$adjustDaysString = '-' . $adjustDays . ' days';
$PHPDateObject->modify($adjustDaysString);
switch (self::getReturnDateType()) {
case self::RETURNDATE_EXCEL:
return (double) PHPExcel_Shared_Date::PHPToExcel($PHPDateObject);
break;
case self::RETURNDATE_PHP_NUMERIC:
return (int) PHPExcel_Shared_Date::ExcelToPHP(PHPExcel_Shared_Date::PHPToExcel($PHPDateObject));
break;
case self::RETURNDATE_PHP_OBJECT:
return $PHPDateObject;
break;
}
}
// 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);
$objPHPExcel->getActiveSheet()->setCellValue('A8', 'Boolean')->setCellValue('B8', 'False')->setCellValue('C8', false);
$dateTimeNow = time();
$objPHPExcel->getActiveSheet()->setCellValue('A9', 'Date/Time')->setCellValue('B9', 'Date')->setCellValue('C9', PHPExcel_Shared_Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C9')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
$objPHPExcel->getActiveSheet()->setCellValue('A10', 'Date/Time')->setCellValue('B10', 'Time')->setCellValue('C10', PHPExcel_Shared_Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C10')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
$objPHPExcel->getActiveSheet()->setCellValue('A11', 'Date/Time')->setCellValue('B11', 'Date and Time')->setCellValue('C11', PHPExcel_Shared_Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C11')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME);
$objPHPExcel->getActiveSheet()->setCellValue('A12', 'NULL')->setCellValue('C12', NULL);
$objRichText = new PHPExcel_RichText();
$objRichText->createText('你好 ');
$objPayable = $objRichText->createTextRun('你 好 吗？');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText(', unless specified otherwise on the invoice.');
$objPHPExcel->getActiveSheet()->setCellValue('A13', 'Rich Text')->setCellValue('C13', $objRichText);
$objRichText2 = new PHPExcel_RichText();
$objRichText2->createText("black text\n");
$objRed = $objRichText2->createTextRun("red text");
$objRed->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED));
$objPHPExcel->getActiveSheet()->getCell("C14")->setValue($objRichText2);
public function outputEntrylist()
{
$event_id = $this->Session->read('event_info_id');
$entryInfo = $this->EntryInfo->find('all', array('conditions' => array('EntryInfo.event_info_id' => $event_id)));
if (count($entryInfo) == 0) {
$this->Session->setFlash(__('参加者情報がありません'));
return;
}
$book = new PHPExcel();
$book->setActiveSheetIndex(0);
$sheet = $book->getActiveSheet();
$sheet->setTitle('参加者一覧');
$sheet->setCellValue('A1', '開催情報マスタNo');
$sheet->setCellValue('B1', '状態');
$sheet->setCellValue('C1', '開催日');
$sheet->setCellValue('D1', '医療機関No.');
$sheet->setCellValue('E1', '医療機関名');
$sheet->setCellValue('F1', '参加者No.');
$sheet->setCellValue('G1', '所属');
$sheet->setCellValue('H1', '役職');
$sheet->setCellValue('I1', '氏名');
$sheet->setCellValue('J1', '電話番号1');
$sheet->setCellValue('K1', '電話番号2');
$sheet->setCellValue('L1', 'FAX');
$sheet->setCellValue('M1', 'メールアドレス');
$sheet->setCellValue('N1', '郵便番号');
$sheet->setCellValue('O1', '住所');
$sheet->setCellValue('P1', '備考');
$status = array('未入場', '受付済', '代理受付');
$cnt_c = 2;
foreach ($entryInfo as $k => $v) {
$read_date = date('Y-M-d', strtotime($v['EntryInfo']['event_date']));
$display_date = PHPExcel_Shared_Date::PHPToExcel(new DateTime($read_date));
$sheet->getStyle('C' . $cnt_c)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
$sheet->setCellValue('A' . $cnt_c, $v['EntryInfo']['event_info_id']);
$sheet->setCellValue('B' . $cnt_c, $status[intval($v['EntryInfo']['status_id'])]);
$sheet->setCellValue('C' . $cnt_c, $display_date);
$sheet->setCellValue('D' . $cnt_c, $v['EntryInfo']['medical_instition_no']);
$sheet->setCellValue('E' . $cnt_c, $v['EntryInfo']['medical_instition']);
$sheet->setCellValue('F' . $cnt_c, $v['EntryInfo']['participant_no']);
$sheet->setCellValue('G' . $cnt_c, $v['EntryInfo']['department']);
$sheet->setCellValue('H' . $cnt_c, $v['EntryInfo']['post']);
$sheet->setCellValue('I' . $cnt_c, $v['EntryInfo']['name']);
$sheet->setCellValue('J' . $cnt_c, $v['EntryInfo']['tel_no1']);
$sheet->setCellValue('K' . $cnt_c, $v['EntryInfo']['tel_no2']);
$sheet->setCellValue('L' . $cnt_c, $v['EntryInfo']['fax']);
$sheet->setCellValue('M' . $cnt_c, $v['EntryInfo']['mail_address']);
$sheet->setCellValue('N' . $cnt_c, $v['EntryInfo']['postal_code']);
$sheet->setCellValue('O' . $cnt_c, $v['EntryInfo']['address']);
$sheet->setCellValue('P' . $cnt_c, $v['EntryInfo']['remarks']);
$cnt_c++;
}
$objWriter = new PHPExcel_Writer_Excel2007($book);
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename=entry_export.xlsx");
header("Content-Transfer-Encoding: binary ");
$objWriter->save('php://output');
}
/**
* Функция проверяет корректность данных в конкретной строке импорта
* @param integer $index числовой индекс проверяемой строки
* @return array массив ошибок, или массив корректных данных
*/
public function isImportRowCorrect($index)
{
$error = [];
$result = [];
//если у нас есть назаполненые поля, то прерываем загрузку
if ($this->isNotEmptyFields($index) !== true) {
return false;
}
$result['CUSTOMER'] = $this->getCustomerById($this->getCellValue('A', $index));
if (!is_object($result['CUSTOMER'])) {
$error[] = 'This customer not found in DB';
}
$result['LOADING_ITEM_TYPE'] = trim($this->getCellValue('B', $index));
if (!$this->isFieldHasCorrectValue($result['LOADING_ITEM_TYPE'], ['Pallet', 'Container', 'Carton box', 'Crate'])) {
$error[] = 'Field "Loading item type" has incorrect value';
} else {
$result['PACKAGE_TYPE'] = $this->getPackageTypeByName($result['LOADING_ITEM_TYPE']);
if ($result['PACKAGE_TYPE'] == false) {
$error[] = 'This "Loading item type" not found in DB';
}
}
$result['CARGO_TYPE'] = trim($this->getCellValue('C', $index));
if (!$this->isFieldHasCorrectValue($result['CARGO_TYPE'], ['General', 'HVC', 'ADR', 'Temperature controlled', 'Oversized', 'Other', 'LTL'])) {
$error[] = 'Field "Cargo type" has incorrect value';
} else {
$result['TRUCKING_CARGO_TYPE'] = $this->getCargoTypeByName($result['CARGO_TYPE']);
if ($result['TRUCKING_CARGO_TYPE'] == false || !is_object($result['TRUCKING_CARGO_TYPE'])) {
$error[] = 'This "Cargo type" not found in DB';
}
}
$result['SHIPPER'] = $this->getOrganizationById($this->getCellValue('L', $index));
if (false == $result['SHIPPER']) {
$error[] = 'This "Shipper" not found in DB';
}
$result['SHIPPER_ADDRESS'] = $this->getOrganizationLocationById($this->getCellValue('M', $index));
if (false == $result['SHIPPER_ADDRESS']) {
$error[] = 'This "Shipper address" not found in DB';
}
$result['CONSIGNEE'] = $this->getOrganizationById($this->getCellValue('O', $index));
if (false == $result['CONSIGNEE']) {
$error[] = 'This "Consignee" not found in DB';
}
$result['CONSIGNEE_ADDRESS'] = $this->getOrganizationLocationById($this->getCellValue('P', $index));
if (false == $result['CONSIGNEE_ADDRESS']) {
$error[] = 'This "Consignee address" not found in DB';
}
$result['LOADING_TIME_SLOT'] = $this->getCellValue('N', $index);
$result['UNLOADING_TIME_SLOT'] = $this->getCellValue('Q', $index);
if (empty($result['LOADING_TIME_SLOT']) && empty($result['UNLOADING_TIME_SLOT'])) {
$error[] = 'One fields of "Loading time slot" or "Unload time slot" must not be empty';
}
//заполним Loading Time Slot
if (empty($result['LOADING_TIME_SLOT'])) {
$result['LOADING_TIME_SLOT'] = \PHPExcel_Shared_Date::PHPToExcel(new \DateTime());
}
if (is_object($result['TRUCKING_CARGO_TYPE']) && $result['TRUCKING_CARGO_TYPE']->getName() == 'LTL') {
$result['TOTAL_WEIGHT'] = $this->getCellValue('J', $index);
$result['TOTAL_VOLUME'] = $this->getCellValue('H', $index);
if (empty($result['TOTAL_WEIGHT']) && empty($result['TOTAL_VOLUME'])) {
$error[] = 'One fields of "Total weight" or "Total value" must not be empty';
}
} else {
$result['TOTAL_WEIGHT'] = $this->getCellValue('J', $index);
$result['ITEM_QUANTITY'] = $this->getCellValue('I', $index);
if (empty($result['TOTAL_WEIGHT']) || empty($result['ITEM_QUANTITY'])) {
$error[] = 'Fields "Total weight" and "Item quantity" must not be empty';
}
}
$result['STACKABILITY'] = $this->getCellValue('K', $index);
if (strlen($result['STACKABILITY']) && !$this->isFieldHasCorrectValue($result['STACKABILITY'], ['YES', 'NO'])) {
$error[] = 'Field "Stackability" has incorrect value';
} else {
$result['STACKABILITY'] = strtoupper($result['STACKABILITY']) == 'YES' ? 1 : 0;
}
$result['SECURITY'] = $this->getCellValue('R', $index);
if (strlen($result['SECURITY']) && !$this->isFieldHasCorrectValue($result['SECURITY'], ['YES', 'NO'])) {
$error[] = 'Field "Security" has incorrect value';
} else {
$result['SECURITY'] = strtoupper($result['SECURITY']) == 'YES' ? 1 : 0;
}
if ($result['SECURITY'] == 1) {
$result['SECURITY_TYPE'] = trim($this->getCellValue('S', $index));
if (!$this->isFieldHasCorrectValue($result['SECURITY_TYPE'], ['guard', 'convoy'])) {
$error[] = 'Field "Security type" has incorrect value';
} else {
$result['SECURITY_TYPE'] = $this->getSecurityTypeByName($result['SECURITY_TYPE']);
if ($result['SECURITY_TYPE'] == false) {
$error[] = 'This "Security type" not found in DB';
}
}
} else {
$result['SECURITY_TYPE'] = null;
}
if (!count($error)) {
return ['SUCCESS' => true, 'RESULT' => $result];
} else {
return ['SUCCESS' => false, 'RESULT' => $error];
}
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param PHPExcel $objPHPExcel
* @return PHPExcel
* @throws PHPExcel_Reader_Exception
*/
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
$timezoneObj = new DateTimeZone('Europe/London');
$GMT = new DateTimeZone('UTC');
$zipClass = PHPExcel_Settings::getZipClass();
$zip = new $zipClass();
if (!$zip->open($pFilename)) {
throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! Error opening file.");
}
// echo '<h1>Meta Information</h1>';
$xml = simplexml_load_string($zip->getFromName("meta.xml"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
$namespacesMeta = $xml->getNamespaces(true);
// echo '<pre>';
// print_r($namespacesMeta);
// echo '</pre><hr />';
$docProps = $objPHPExcel->getProperties();
$officeProperty = $xml->children($namespacesMeta['office']);
foreach ($officeProperty as $officePropertyData) {
$officePropertyDC = array();
if (isset($namespacesMeta['dc'])) {
$officePropertyDC = $officePropertyData->children($namespacesMeta['dc']);
}
foreach ($officePropertyDC as $propertyName => $propertyValue) {
$propertyValue = (string) $propertyValue;
switch ($propertyName) {
case 'title':
$docProps->setTitle($propertyValue);
break;
case 'subject':
$docProps->setSubject($propertyValue);
break;
case 'creator':
$docProps->setCreator($propertyValue);
$docProps->setLastModifiedBy($propertyValue);
break;
case 'date':
$creationDate = strtotime($propertyValue);
$docProps->setCreated($creationDate);
$docProps->setModified($creationDate);
break;
case 'description':
$docProps->setDescription($propertyValue);
break;
}
}
$officePropertyMeta = array();
if (isset($namespacesMeta['dc'])) {
$officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']);
}
foreach ($officePropertyMeta as $propertyName => $propertyValue) {
$propertyValueAttributes = $propertyValue->attributes($namespacesMeta['meta']);
$propertyValue = (string) $propertyValue;
switch ($propertyName) {
case 'initial-creator':
$docProps->setCreator($propertyValue);
break;
case 'keyword':
$docProps->setKeywords($propertyValue);
break;
case 'creation-date':
$creationDate = strtotime($propertyValue);
$docProps->setCreated($creationDate);
break;
case 'user-defined':
$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING;
foreach ($propertyValueAttributes as $key => $value) {
if ($key == 'name') {
$propertyValueName = (string) $value;
} elseif ($key == 'value-type') {
switch ($value) {
case 'date':
$propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'date');
$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_DATE;
break;
case 'boolean':
$propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'bool');
$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_BOOLEAN;
break;
case 'float':
$propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'r4');
$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_FLOAT;
break;
default:
$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING;
}
}
}
$docProps->setCustomProperty($propertyValueName, $propertyValue, $propertyValueType);
break;
}
}
}
// echo '<h1>Workbook Content</h1>';
$xml = simplexml_load_string($zip->getFromName("content.xml"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
$namespacesContent = $xml->getNamespaces(true);
// echo '<pre>';
// print_r($namespacesContent);
// echo '</pre><hr />';
$workbook = $xml->children($namespacesContent['office']);
foreach ($workbook->body->spreadsheet as $workbookData) {
$workbookData = $workbookData->children($namespacesContent['table']);
$worksheetID = 0;
foreach ($workbookData->table as $worksheetDataSet) {
$worksheetData = $worksheetDataSet->children($namespacesContent['table']);
// print_r($worksheetData);
// echo '<br />';
$worksheetDataAttributes = $worksheetDataSet->attributes($namespacesContent['table']);
// print_r($worksheetDataAttributes);
// echo '<br />';
if (isset($this->_loadSheetsOnly) && isset($worksheetDataAttributes['name']) && !in_array($worksheetDataAttributes['name'], $this->_loadSheetsOnly)) {
continue;
}
// echo '<h2>Worksheet '.$worksheetDataAttributes['name'].'</h2>';
// Create new Worksheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($worksheetID);
if (isset($worksheetDataAttributes['name'])) {
$worksheetName = (string) $worksheetDataAttributes['name'];
// 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
$objPHPExcel->getActiveSheet()->setTitle($worksheetName, false);
}
$rowID = 1;
foreach ($worksheetData as $key => $rowData) {
// echo '<b>'.$key.'</b><br />';
switch ($key) {
case 'table-header-rows':
foreach ($rowData as $key => $cellData) {
$rowData = $cellData;
break;
}
case 'table-row':
$rowDataTableAttributes = $rowData->attributes($namespacesContent['table']);
$rowRepeats = isset($rowDataTableAttributes['number-rows-repeated']) ? $rowDataTableAttributes['number-rows-repeated'] : 1;
$columnID = 'A';
foreach ($rowData as $key => $cellData) {
if ($this->getReadFilter() !== NULL) {
if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
continue;
}
}
// echo '<b>'.$columnID.$rowID.'</b><br />';
$cellDataText = isset($namespacesContent['text']) ? $cellData->children($namespacesContent['text']) : '';
$cellDataOffice = $cellData->children($namespacesContent['office']);
$cellDataOfficeAttributes = $cellData->attributes($namespacesContent['office']);
$cellDataTableAttributes = $cellData->attributes($namespacesContent['table']);
// echo 'Office Attributes: ';
// print_r($cellDataOfficeAttributes);
// echo '<br />Table Attributes: ';
// print_r($cellDataTableAttributes);
// echo '<br />Cell Data Text';
// print_r($cellDataText);
// echo '<br />';
//
$type = $formatting = $hyperlink = null;
$hasCalculatedValue = false;
$cellDataFormula = '';
if (isset($cellDataTableAttributes['formula'])) {
$cellDataFormula = $cellDataTableAttributes['formula'];
$hasCalculatedValue = true;
}
if (isset($cellDataOffice->annotation)) {
// echo 'Cell has comment<br />';
$annotationText = $cellDataOffice->annotation->children($namespacesContent['text']);
$textArray = array();
foreach ($annotationText as $t) {
foreach ($t->span as $text) {
$textArray[] = (string) $text;
}
}
$text = implode("\n", $textArray);
// echo $text,'<br />';
$objPHPExcel->getActiveSheet()->getComment($columnID . $rowID)->setText($this->_parseRichText($text));
}
if (isset($cellDataText->p)) {
// Consolidate if there are multiple p records (maybe with spans as well)
$dataArray = array();
// Text can have multiple text:p and within those, multiple text:span.
// text:p newlines, but text:span does not.
// Also, here we assume there is no text data is span fields are specified, since
// we have no way of knowing proper positioning anyway.
foreach ($cellDataText->p as $pData) {
if (isset($pData->span)) {
// span sections do not newline, so we just create one large string here
$spanSection = "";
foreach ($pData->span as $spanData) {
$spanSection .= $spanData;
}
array_push($dataArray, $spanSection);
} else {
array_push($dataArray, $pData);
}
}
$allCellDataText = implode($dataArray, "\n");
// echo 'Value Type is '.$cellDataOfficeAttributes['value-type'].'<br />';
switch ($cellDataOfficeAttributes['value-type']) {
case 'string':
$type = PHPExcel_Cell_DataType::TYPE_STRING;
$dataValue = $allCellDataText;
if (isset($dataValue->a)) {
$dataValue = $dataValue->a;
$cellXLinkAttributes = $dataValue->attributes($namespacesContent['xlink']);
$hyperlink = $cellXLinkAttributes['href'];
}
break;
case 'boolean':
$type = PHPExcel_Cell_DataType::TYPE_BOOL;
$dataValue = $allCellDataText == 'TRUE' ? True : False;
break;
case 'percentage':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dataValue = (double) $cellDataOfficeAttributes['value'];
if (floor($dataValue) == $dataValue) {
$dataValue = (int) $dataValue;
}
$formatting = PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00;
break;
case 'currency':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dataValue = (double) $cellDataOfficeAttributes['value'];
if (floor($dataValue) == $dataValue) {
$dataValue = (int) $dataValue;
}
$formatting = PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE;
break;
case 'float':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dataValue = (double) $cellDataOfficeAttributes['value'];
if (floor($dataValue) == $dataValue) {
if ($dataValue == (int) $dataValue) {
$dataValue = (int) $dataValue;
} else {
$dataValue = (double) $dataValue;
}
}
break;
case 'date':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dateObj = new DateTime($cellDataOfficeAttributes['date-value'], $GMT);
$dateObj->setTimeZone($timezoneObj);
list($year, $month, $day, $hour, $minute, $second) = explode(' ', $dateObj->format('Y m d H i s'));
$dataValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hour, $minute, $second);
if ($dataValue != floor($dataValue)) {
$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15 . ' ' . PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
} else {
$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15;
}
break;
case 'time':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dataValue = PHPExcel_Shared_Date::PHPToExcel(strtotime('01-01-1970 ' . implode(':', sscanf($cellDataOfficeAttributes['time-value'], 'PT%dH%dM%dS'))));
$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
break;
}
// echo 'Data value is '.$dataValue.'<br />';
// if ($hyperlink !== NULL) {
// echo 'Hyperlink is '.$hyperlink.'<br />';
// }
} else {
$type = PHPExcel_Cell_DataType::TYPE_NULL;
$dataValue = NULL;
}
if ($hasCalculatedValue) {
$type = PHPExcel_Cell_DataType::TYPE_FORMULA;
// echo 'Formula: ', $cellDataFormula, PHP_EOL;
$cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=') + 1);
$temp = explode('"', $cellDataFormula);
$tKey = false;
foreach ($temp as &$value) {
// Only replace in alternate array entries (i.e. non-quoted blocks)
if ($tKey = !$tKey) {
$value = preg_replace('/\\[([^\\.]+)\\.([^\\.]+):\\.([^\\.]+)\\]/Ui', '$1!$2:$3', $value);
// Cell range reference in another sheet
$value = preg_replace('/\\[([^\\.]+)\\.([^\\.]+)\\]/Ui', '$1!$2', $value);
// Cell reference in another sheet
$value = preg_replace('/\\[\\.([^\\.]+):\\.([^\\.]+)\\]/Ui', '$1:$2', $value);
// Cell range reference
$value = preg_replace('/\\[\\.([^\\.]+)\\]/Ui', '$1', $value);
// Simple cell reference
$value = PHPExcel_Calculation::_translateSeparator(';', ',', $value, $inBraces);
}
}
unset($value);
// Then rebuild the formula string
$cellDataFormula = implode('"', $temp);
// echo 'Adjusted Formula: ', $cellDataFormula, PHP_EOL;
}
$colRepeats = isset($cellDataTableAttributes['number-columns-repeated']) ? $cellDataTableAttributes['number-columns-repeated'] : 1;
if ($type !== NULL) {
for ($i = 0; $i < $colRepeats; ++$i) {
if ($i > 0) {
++$columnID;
}
if ($type !== PHPExcel_Cell_DataType::TYPE_NULL) {
for ($rowAdjust = 0; $rowAdjust < $rowRepeats; ++$rowAdjust) {
$rID = $rowID + $rowAdjust;
$objPHPExcel->getActiveSheet()->getCell($columnID . $rID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $dataValue, $type);
if ($hasCalculatedValue) {
// echo 'Forumla result is '.$dataValue.'<br />';
$objPHPExcel->getActiveSheet()->getCell($columnID . $rID)->setCalculatedValue($dataValue);
}
if ($formatting !== NULL) {
$objPHPExcel->getActiveSheet()->getStyle($columnID . $rID)->getNumberFormat()->setFormatCode($formatting);
} else {
$objPHPExcel->getActiveSheet()->getStyle($columnID . $rID)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
}
if ($hyperlink !== NULL) {
$objPHPExcel->getActiveSheet()->getCell($columnID . $rID)->getHyperlink()->setUrl($hyperlink);
}
}
}
}
}
// Merged cells
if (isset($cellDataTableAttributes['number-columns-spanned']) || isset($cellDataTableAttributes['number-rows-spanned'])) {
if ($type !== PHPExcel_Cell_DataType::TYPE_NULL || !$this->_readDataOnly) {
$columnTo = $columnID;
if (isset($cellDataTableAttributes['number-columns-spanned'])) {
$columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cellDataTableAttributes['number-columns-spanned'] - 2);
}
$rowTo = $rowID;
if (isset($cellDataTableAttributes['number-rows-spanned'])) {
$rowTo = $rowTo + $cellDataTableAttributes['number-rows-spanned'] - 1;
}
$cellRange = $columnID . $rowID . ':' . $columnTo . $rowTo;
$objPHPExcel->getActiveSheet()->mergeCells($cellRange);
}
}
++$columnID;
}
$rowID += $rowRepeats;
break;
}
}
++$worksheetID;
}
}
// Return
return $objPHPExcel;
}
*/
/** Error reporting */
error_reporting(E_ALL);
/** Include PHPExcel */
require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
// Create new PHPExcel object
echo date('H:i:s'), " Create new PHPExcel object", EOL;
$objPHPExcel = new PHPExcel();
// Set document properties
echo date('H:i:s'), " Set document properties", EOL;
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file");
// Create a first sheet, representing sales 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');
$objPHPExcel->getActiveSheet()->setCellValue('A4', '1001');
$objPHPExcel->getActiveSheet()->setCellValue('B4', 'PHP for dummies');
$objPHPExcel->getActiveSheet()->setCellValue('C4', '20');
$objPHPExcel->getActiveSheet()->setCellValue('D4', '1');
$objPHPExcel->getActiveSheet()->setCellValue('E4', '=IF(D4<>"",C4*D4,"")');
$objPHPExcel->getActiveSheet()->setCellValue('A5', '1012');
$objPHPExcel->getActiveSheet()->setCellValue('B5', 'OpenXML for dummies');
$objPHPExcel->getActiveSheet()->setCellValue('C5', '22');
/**
* Write Cell
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Worksheet $pSheet Worksheet
* @param PHPExcel_Cell $pCell Cell
* @param string[] $pStringTable String table
* @param string[] $pFlippedStringTable String table (flipped), for faster index searching
* @throws Exception
*/
private function _writeCell(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, PHPExcel_Cell $pCell = null, $pStringTable = null, $pFlippedStringTable = null)
{
if (is_array($pStringTable) && is_array($pFlippedStringTable)) {
// Cell
$objWriter->startElement('c');
$objWriter->writeAttribute('r', $pCell->getCoordinate());
// Sheet styles
$aStyles = $pSheet->getStyles();
if (isset($aStyles[$pCell->getCoordinate()])) {
$styleIndex = $this->getParentWriter()->getStylesHashTable()->getIndexForHashCode($aStyles[$pCell->getCoordinate()]->getHashCode());
if ($styleIndex != '') {
$objWriter->writeAttribute('s', $styleIndex);
}
}
// If cell value is supplied, write cell value
if (is_object($pCell->getValue()) || $pCell->getValue() !== '') {
// Map type
$mappedType = $pCell->getDataType();
// Write data type depending on its type
switch (strtolower($mappedType)) {
case 'inlinestr':
// Inline string
$objWriter->writeAttribute('t', $mappedType);
break;
case 's':
// String
$objWriter->writeAttribute('t', $mappedType);
break;
case 'b':
// Boolean
$objWriter->writeAttribute('t', $mappedType);
break;
case 'f':
// Formula
$calculatedValue = null;
if ($this->getParentWriter()->getPreCalculateFormulas()) {
$calculatedValue = $pCell->getCalculatedValue();
} else {
$calculatedValue = $pCell->getValue();
}
if (is_string($calculatedValue)) {
$objWriter->writeAttribute('t', 'str');
}
break;
}
// Write data depending on its type
switch (strtolower($mappedType)) {
case 'inlinestr':
// Inline string
if (!$pCell->getValue() instanceof PHPExcel_RichText) {
$objWriter->writeElement('t', PHPExcel_Shared_String::ControlCharacterPHP2OOXML($pCell->getValue()));
} else {
if ($pCell->getValue() instanceof PHPExcel_RichText) {
$objWriter->startElement('is');
$this->getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $pCell->getValue());
$objWriter->endElement();
}
}
break;
case 's':
// String
if (!$pCell->getValue() instanceof PHPExcel_RichText) {
if (isset($pFlippedStringTable[$pCell->getValue()])) {
$objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()]);
}
} else {
if ($pCell->getValue() instanceof PHPExcel_RichText) {
$objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()->getHashCode()]);
}
}
break;
case 'f':
// Formula
$objWriter->writeElement('f', substr($pCell->getValue(), 1));
if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
if ($this->getParentWriter()->getPreCalculateFormulas()) {
$calculatedValue = $pCell->getCalculatedValue();
if (substr($calculatedValue, 0, 1) != '#') {
$objWriter->writeElement('v', $calculatedValue);
} else {
$objWriter->writeElement('v', '0');
}
} else {
$objWriter->writeElement('v', '0');
}
}
break;
case 'n':
// Numeric
if (PHPExcel_Shared_Date::isDateTime($pCell)) {
$dateValue = $pCell->getValue();
if (is_string($dateValue)) {
// Error string
$objWriter->writeElement('v', $pFlippedStringTable[$dateValue]);
} elseif (!is_float($dateValue)) {
// PHP serialized date/time or date/time object
$objWriter->writeElement('v', PHPExcel_Shared_Date::PHPToExcel($dateValue));
} else {
// Excel serialized date/time
$objWriter->writeElement('v', $dateValue);
}
} else {
$objWriter->writeElement('v', $pCell->getValue());
}
break;
case 'b':
// Boolean
$objWriter->writeElement('v', $pCell->getValue() ? '1' : '0');
break;
}
}
$objWriter->endElement();
} else {
throw new Exception("Invalid parameters passed.");
}
}
