/** * 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 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; }
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."); } }