public function finalize() { $writer = new \PHPExcel_Writer_Excel2007($this->excel); $writer->setPreCalculateFormulas(false); $writer->save($this->filename); return $this->filename; }
public function generate($format = "Excel5", $docName = "Tabelle") { switch ($format) { case 'Excel2007': include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel2007.php'; $writer = new PHPExcel_Writer_Excel2007($this); $ext = 'xlsx'; $header = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'; //supprime le pre-calcul $writer->setPreCalculateFormulas(false); break; case 'Excel2003': include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel2007.php'; $writer = new PHPExcel_Writer_Excel2007($this); $writer->setOffice2003Compatibility(true); $ext = 'xlsx'; //supprime le pre-calcul $writer->setPreCalculateFormulas(false); break; case 'Excel5': include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel5.php'; $writer = new PHPExcel_Writer_Excel5($this); $ext = 'xls'; break; case 'CSV': include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/CSV.php'; $writer = new PHPExcel_Writer_CSV($this); $writer->setDelimiter(","); //l'op�rateur de s�paration est la virgule $writer->setSheetIndex(0); //Une seule feuille possible $ext = 'csv'; break; case 'PDF': include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/PDF.php'; $writer = new PHPExcel_Writer_PDF($this); $writer->setSheetIndex(0); //Une seule feuille possible $ext = 'pdf'; break; case 'HTML': include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/HTML.php'; $writer = new PHPExcel_Writer_HTML($this); $writer->setSheetIndex(0); //Une seule feuille possible $ext = 'html'; break; } header('Content-type:' . $header); header('Content-Disposition:inline;filename=' . $docName . '.' . $ext); $writer->save('php://output'); }
public function execute() { $options = $this->getOptions(); $optionsExport = $options['settings']['export']; $optionsRenderer = $this->getOptionsRenderer(); $phpExcel = new PHPExcel(); // Sheet 1 $phpExcel->setActiveSheetIndex(0); $sheet = $phpExcel->getActiveSheet(); $sheet->setTitle($this->getTranslator()->translate($optionsRenderer['sheetName'])); if (true === $optionsRenderer['displayTitle']) { $sheet->setCellValue('A' . $optionsRenderer['rowTitle'], $this->getTitle()); $sheet->getStyle('A' . $optionsRenderer['rowTitle'])->getFont()->setSize(15); } /* * Print settings */ $this->setPrinting($phpExcel); /* * Calculate column width */ $this->calculateColumnWidth($sheet, $this->getColumnsToExport()); /* * Header */ $xColumn = 0; $yRow = $optionsRenderer['startRowData']; foreach ($this->getColumnsToExport() as $col) { /* @var $column \ZfcDatagrid\Column\AbstractColumn */ $label = $this->getTranslator()->translate($col->getLabel()); $sheet->setCellValueByColumnAndRow($xColumn, $yRow, $label); $sheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($xColumn))->setWidth($col->getWidth()); $xColumn++; } /* * Data */ $yRow = $optionsRenderer['startRowData'] + 1; foreach ($this->getData() as $row) { $xColumn = 0; foreach ($this->getColumnsToExport() as $col) { $value = $row[$col->getUniqueId()]; if (is_array($value)) { $value = implode(PHP_EOL, $value); } /* @var $column \ZfcDatagrid\Column\AbstractColumn */ $currentColumn = PHPExcel_Cell::stringFromColumnIndex($xColumn); $sheet->getCell($currentColumn . $yRow)->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING); $columnStyle = $sheet->getStyle($currentColumn . $yRow); $columnStyle->getAlignment()->setWrapText(true); /* * Styles */ $styles = array_merge($this->getRowStyles(), $col->getStyles()); foreach ($styles as $style) { /* @var $style \ZfcDatagrid\Column\Style\AbstractStyle */ if ($style->isApply($row) === true) { switch (get_class($style)) { case 'ZfcDatagrid\\Column\\Style\\Bold': $columnStyle->getFont()->setBold(true); break; case 'ZfcDatagrid\\Column\\Style\\Italic': $columnStyle->getFont()->setItalic(true); break; case 'ZfcDatagrid\\Column\\Style\\Color': $columnStyle->getFont()->getColor()->setRGB($style->getRgbHexString()); break; case 'ZfcDatagrid\\Column\\Style\\BackgroundColor': $columnStyle->getFill()->applyFromArray(['type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => ['rgb' => $style->getRgbHexString()]]); break; case 'ZfcDatagrid\\Column\\Style\\Align': switch ($style->getAlignment()) { case \ZfcDatagrid\Column\Style\Align::$RIGHT: $columnStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); break; case \ZfcDatagrid\Column\Style\Align::$LEFT: $columnStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); break; case \ZfcDatagrid\Column\Style\Align::$CENTER: $columnStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); break; case \ZfcDatagrid\Column\Style\Align::$JUSTIFY: $columnStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY); break; default: //throw new \Exception('Not defined yet: "'.get_class($style->getAlignment()).'"'); break; } break; case 'ZfcDatagrid\\Column\\Style\\Strikethrough': $columnStyle->getFont()->setStrikethrough(true); break; case 'ZfcDatagrid\\Column\\Style\\Html': // @todo strip the html? break; default: throw new \Exception('Not defined yet: "' . get_class($style) . '"'); break; } } } $xColumn++; } $yRow++; } /* * Autofilter, freezing, ... */ $highest = $sheet->getHighestRowAndColumn(); // Letzte Zeile merken // Autofilter + Freeze $sheet->setAutoFilter('A' . $optionsRenderer['startRowData'] . ':' . $highest['column'] . $highest['row']); $freezeRow = $optionsRenderer['startRowData'] + 1; $sheet->freezePane('A' . $freezeRow); // repeat the data header for each page! $sheet->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd($optionsRenderer['startRowData'], $optionsRenderer['startRowData']); // highlight header line $style = ['font' => ['bold' => true], 'borders' => ['allborders' => ['style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => ['argb' => PHPExcel_Style_Color::COLOR_BLACK]]], 'fill' => ['type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => ['argb' => PHPExcel_Style_Color::COLOR_YELLOW]]]; $range = 'A' . $optionsRenderer['startRowData'] . ':' . $highest['column'] . $optionsRenderer['startRowData']; $sheet->getStyle($range)->applyFromArray($style); // print borders $range = 'A' . $freezeRow . ':' . $highest['column'] . $highest['row']; $sheet->getStyle($range)->applyFromArray(['borders' => ['allborders' => ['style' => PHPExcel_Style_Border::BORDER_THIN]]]); /* * Save the file */ $path = $optionsExport['path']; $saveFilename = date('Y-m-d_H-i-s') . $this->getCacheId() . '.xlsx'; $excelWriter = new \PHPExcel_Writer_Excel2007($phpExcel); $excelWriter->setPreCalculateFormulas(false); $excelWriter->save($path . '/' . $saveFilename); /* * Send the response stream */ $response = new ResponseStream(); $response->setStream(fopen($path . '/' . $saveFilename, 'r')); $headers = new Headers(); $headers->addHeaders(['Content-Type' => ['application/force-download', 'application/octet-stream', 'application/download'], 'Content-Length' => filesize($path . '/' . $saveFilename), 'Content-Disposition' => 'attachment;filename=' . $this->getFilename() . '.xlsx', 'Cache-Control' => 'must-revalidate', 'Pragma' => 'no-cache', 'Expires' => 'Thu, 1 Jan 1970 00:00:00 GMT']); $response->setHeaders($headers); return $response; }
protected function getWriter() { $writer = new \PHPExcel_Writer_Excel2007($this->objPHPExcel); $writer->setPreCalculateFormulas(false); return $writer; }
/** * Export the actual selected translations as Excel file * uses stripped version of PHPExcel located in system/library * * @return void */ public function exportExcel() { // Include PHPExcel found in library folder include_once DIR_SYSTEM . 'library' . DIRECTORY_SEPARATOR . 'Classes' . DIRECTORY_SEPARATOR . 'PHPExcel.php'; // Start computing data $this->index(); // Create phpExcel object $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("Andreas Tangemann")->setLastModifiedBy("Andreas Tangemann")->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 header $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', $this->data['column_module']); $objPHPExcel->getActiveSheet()->setCellValue('B1', $this->data['column_folder']); $objPHPExcel->getActiveSheet()->setCellValue('C1', $this->data['column_file']); $objPHPExcel->getActiveSheet()->setCellValue('D1', $this->data['column_variable']); $objPHPExcel->getActiveSheet()->setCellValue('E1', $this->data['column_translation_source']); $objPHPExcel->getActiveSheet()->setCellValue('F1', $this->data['column_translation_destination']); $objPHPExcel->getActiveSheet()->setCellValue('G1', 'Hyperlink'); // Set column width $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20); // Same style for all cells $objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('A1'), 'B1:G1'); // Set page orientation and size $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); // Rename sheet $objPHPExcel->getActiveSheet()->setTitle('Translation'); // Get the directory $directory = $this->_getDirectory(); // Get all selected translations $results = $this->model_localisation_translator->getTranslations($this->data); if ($results) { foreach ($results as $key => $result) { // Iterate through all translations $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, (int) $key + 2, $result['module']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, (int) $key + 2, $result['folder']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, (int) $key + 2, $result['file']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, (int) $key + 2, $result['variable']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, (int) $key + 2, $result['translation_source']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, (int) $key + 2, $result['translation_destination']); // Create link to file and insert in Excel file $fileref = $this->_createFileref($directory, $result); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, (int) $key + 2, str_replace('/', '\\', $fileref)); $objPHPExcel->getActiveSheet()->getCellByColumnAndRow(6, (int) $key + 2)->getHyperlink()->setUrl($fileref); } } // Insert Autofilter $objPHPExcel->getActiveSheet()->setAutoFilter('A1:G' . count($results)); // Save Excel 2007 file try { $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $objWriter->setPreCalculateFormulas(false); ob_start(); $objWriter->save("php://output"); $excel = ob_get_contents(); ob_end_clean(); } catch (Exception $e) { die('Error saving Excel file: ' . $e->getMessage()); } // Add custom header and print file header('Content-Description: File Transfer'); header('Content-Type: application/vnd.openxmlformats-officedocument' . '.spreadsheetml.sheet'); header('Content-Disposition: attachment; filename=translations' . date('c') . '.xlsx'); header('Content-Transfer-Encoding: binary'); header('Expires: 0'); header('Cache-Control: must-re_validate, post-check=0, pre-check=0'); header('Pragma: public'); header('Content-Length: ' . strlen($excel)); echo $excel; // so no other output is added to the Excel file die; }
public static function export2Excel($objPHPExcel, $versionExcel = 'Excel2007', $fileName = '') { switch ($versionExcel) { case 'Excel2007': // redirect output to client browser with Excel2007 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel); $objWriter->setOffice2003Compatibility(true); $objWriter->setPreCalculateFormulas(false); $objWriter->save('php://output'); break; case 'Excel5': // redirect output to client browser with Excel 2005 header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="' . $fileName . '.xls"'); header('Cache-Control: max-age=0'); $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel); $objWriter->setPreCalculateFormulas(false); $objWriter->save('php://output'); break; default: break; } }
private function exportExcelCustomerList($statistics) { $statuses = $this->getDoctrine()->getManager()->getRepository('CoreBundle:ProductStatus')->findAll(); $excel = new \PHPExcel(); $worksheet = $excel->getActiveSheet(); $worksheet->setCellValueByColumnAndRow(0, 1, 'Customer'); $column = 1; foreach ($statuses as $status) { $worksheet->setCellValueByColumnAndRow($column++, 1, $status->getName()); } $row = 2; foreach ($statistics as $item) { $worksheet->setCellValueByColumnAndRow(0, $row, $item['customer']->getName()); $column = 1; foreach ($statuses as $status) { $worksheet->setCellValueByColumnAndRow($column++, $row, empty($item['changes'][$status->getId()]) ? '0' : $item['changes'][$status->getId()]); } $row++; } header('Content-type: application/ms-excel'); header('Content-Disposition: attachment; filename=report.xlsx'); $objWriter = new \PHPExcel_Writer_Excel2007($excel); $objWriter->setPreCalculateFormulas(false); $objWriter->setOffice2003Compatibility(true); $objWriter->save('php://output'); die; }
public function action_index() { $start = Arr::get($_GET, 'start') ? strtotime($_GET['start']) : strtotime('first day of this month'); $end = Arr::get($_GET, 'end') ? strtotime($_GET['end']) + 86399 : time(); $query = array('financial_time' => isset($_GET['excel']) ? array('$gt' => 0) : array('$exists' => 1), 'update_time' => array('$gt' => intval($start), '$lt' => intval($end))); if (Arr::get($_GET, 'app-start')) { $query['process_time']['$gt'] = strtotime($_GET['app-start']); } if (Arr::get($_GET, 'app-end')) { $query['process_time']['$lt'] = strtotime($_GET['app-end']) + 86399; } if (Arr::get($_GET, 'fin-start')) { $query['financial_time']['$gt'] = strtotime($_GET['fin-start']); } if (Arr::get($_GET, 'fin-end')) { $query['financial_time']['$lt'] = strtotime($_GET['fin-end']) + 86399; } if (Group::current('allow_assign')) { $companies = DB::select('id', 'name')->from('companies')->order_by('name', 'asc')->execute()->as_array('id', 'name'); } if (!Group::current('allow_assign') || Arr::get($_GET, 'company')) { if (Group::current('allow_assign')) { $company = $_GET['company']; if (!is_array($company)) { $company = explode(',', $company); } $company = array_map('intval', $company); } else { $company = array(User::current('company_id')); } $query['user_id'] = array('$in' => DB::select('id')->from('users')->where('company_id', 'IN', $company)->execute()->as_array(NULL, 'id')); } $jobs = array(); if (Arr::get($_GET, 'ticket')) { $tickets = explode(',', $_GET['ticket']); $q = array(); foreach ($tickets as $ticket) { $ticket = preg_replace('/[^a-z0-9]/i', '', strval($ticket)); if (!$ticket) { continue; } if (preg_match('/^T1W[0-9]{12}$/', $ticket)) { $q[] = $ticket; } else { $q[] = new MongoRegex('/.*' . $ticket . '.*/i'); } } if (count($q) > 1) { $jobs['_id'] = array('$in' => $q); } elseif ($q) { $jobs['_id'] = $q[0]; } } if (Arr::get($_GET, 'fsa')) { $values = is_array($_GET['fsa']) ? $_GET['fsa'] : explode(',', $_GET['fsa']); $jobs['data.12'] = count($values) > 1 ? array('$in' => array_values($values)) : current($values); } if (Arr::get($_GET, 'fsam')) { $values = is_array($_GET['fsam']) ? $_GET['fsam'] : explode(',', $_GET['fsam']); $jobs['data.13'] = count($values) > 1 ? array('$in' => array_values($values)) : current($values); } if (Arr::get($_GET, 'fda')) { $values = is_array($_GET['fda']) ? $_GET['fda'] : explode(',', $_GET['fda']); $jobs['data.14'] = count($values) > 1 ? array('$in' => array_values($values)) : current($values); } if (Arr::get($_GET, 'address')) { $jobs['data.8'] = new MongoRegex('/.*' . strval($_GET['address']) . '.*/mi'); } if ($jobs) { if (count($jobs) == 1 && isset($jobs['_id'])) { $query['job_key'] = $jobs['_id']; } else { $query['job_key'] = array('$in' => Database_Mongo::collection('jobs')->distinct('_id', $jobs)); } } $sort = array('job_key' => 1); if (!Arr::get($_GET, 'sort')) { $_GET['sort'] = array('-submission'); } foreach ($_GET['sort'] as $s) { $dir = substr($s, 0, 1) == '-' ? -1 : 1; $s = substr($s, 1); switch ($s) { case 'submission': $sort['update_time'] = $dir; break; case 'approval': $sort['process_time'] = $dir; break; case 'financial': $sort['financial_time'] = $dir; break; } } $result = Database_Mongo::collection('submissions')->find($query)->sort($sort); $submissions = array(); $users = array(); $jobs = array(); $keys = array('region' => 1, 'data.8' => 1, 'data.9' => 1, 'data.14' => 1); foreach ($result as $submission) { $jobs[$submission['job_key']] = 1; $keys[$submission['key']] = 1; $submissions[$submission['job_key']][] = $submission; $users[$submission['user_id']] = 1; } $result = Database_Mongo::collection('jobs')->find(array('_id' => array('$in' => array_keys($jobs))), $keys); $jobs = array(); foreach ($result as $job) { $jobs[$job['_id']] = $job; } if ($users) { User::get(array_keys($users)); } $rates = array(); $result = DB::select()->from('rates')->execute(); foreach ($result as $row) { $rates[$row['company_id']][$row['region_id']][$row['column_id']] = $row['rate']; } $columns = Columns::get_financial(); $approved = array(); $duplicates = array(); $discr = array(); $partial = array(); $full = array(); $skip = array(); if (Group::current('allow_assign') && isset($_GET['approve']) && Arr::get($_GET, 'company') && count($_GET['company']) == 1) { $rates = Arr::get($rates, $_GET['company'][0], array()); foreach ($submissions as $job => $list) { $region = $jobs[$job]['region']; $data = array(); $partial_fl = false; $full_fl = true; $dup_fl = false; $discr_fl = false; $skip_fl = false; foreach ($list as $submission) { $data[$submission['key']][] = $submission; } foreach ($data as $key => $values) { $key = substr($key, 5); $rate = isset($rates[$region][$key]) ? $rates[$region][$key] : (isset($rates[0][$key]) ? $rates[0][$key] : 0); $value = array_shift($values); if (count($values)) { $dup_fl = true; $full_fl = false; } elseif ($value['value'] != Arr::path($jobs, $job . '.data.' . $key)) { $discr_fl = true; $full_fl = false; } elseif (!$rate) { $skip_fl = true; $full_fl = false; } elseif (!$value['financial_time']) { $approved[] = array('id' => $value['_id'], 'rate' => $rate, 'paid' => min(floatval($value['value']), Arr::get($columns, $key))); $partial_fl = true; } } if ($partial_fl) { if ($full_fl) { $full[$job] = 1; } else { $partial[$job] = 1; } } if ($skip_fl) { $skip[$job] = 1; } if ($dup_fl) { $duplicates[$job] = 1; } if ($discr_fl) { $discr[$job] = 1; } $submissions[$job] = $data; } $time = time(); foreach ($approved as $value) { Database_Mongo::collection('submissions')->update(array('_id' => $value['id']), array('$set' => array('paid' => $value['paid'], 'rate' => $value['rate'], 'financial_time' => $time))); } $count = count($jobs); $jobs = Database_Mongo::collection('jobs')->find(array('_id' => array('$in' => array_keys($jobs)))); foreach ($jobs as $job) { Utils::calculate_financial($job); } Messages::save(sprintf('%d/%d tickets were successfully approved.', count($full), $count), 'success'); if ($partial) { Messages::save(sprintf('%d tickets were partially approved.', count($partial)), 'warning'); } if ($discr) { Messages::save(sprintf('%d tickets contain discrepancies.', count($discr)), 'danger'); } if ($duplicates) { Messages::save(sprintf('%d tickets contain duplicates.', count($duplicates)), 'danger'); } if ($skip) { Messages::save(sprintf('%d tickets contain submissions with unknown rates.', count($skip)), 'danger'); } $this->redirect($this->request->uri() . URL::query(array('approve' => NULL))); } elseif (isset($_GET['export'])) { $discr = isset($_GET['discrepancy']); header('Content-type: text/csv'); header('Content-disposition: filename="Submissions.' . date('Ymd', $start) . '-' . date('Ymd', $end) . '.' . date('YmdHi', time()) . '.csv"'); $file = tmpfile(); $headers = array('Tickets ID', 'Submission Date', 'Approval Date', 'User'); if (Group::current('allow_assign')) { $headers[] = 'Company'; } $headers[] = 'Column'; $headers[] = 'Value'; $keys = array(); fputcsv($file, $headers); foreach ($submissions as $job => $list) { foreach ($list as $submission) { if (!$discr || Arr::path($jobs, $job . '.' . $submission['key']) != $submission['value']) { $keys[$submission['key']] = 1; $key = substr($submission['key'], 5); $data = array($job, date('d-m-Y H:i', $submission['update_time']), Arr::get($submission, 'process_time') ? date('d-m-Y H:i', $submission['process_time']) : '', User::get($submission['user_id'], 'login')); if (Group::current('allow_assign')) { $data[] = Arr::get($companies, User::get($submission['user_id'], 'company_id'), 'Unknown'); } $data[] = Columns::get_name($key); $data[] = Columns::output($submission['value'], Columns::get_type($key), true); fputcsv($file, $data); } } } fseek($file, 0); fpassthru($file); fclose($file); die; } elseif (isset($_GET['excel'])) { $doc = PHPExcel_IOFactory::load(DOCROOT . 'financial.template.xlsx'); $sheet = $doc->getSheet(); $sheet->setTitle(date('d-m-Y', $start) . ' - ' . date('d-m-Y', $end)); $s = date('M Y', $start); $e = date('M Y', $end); if ($s == $e) { $month = $s; } else { $month = $s . ' - ' . $e; } $map = array('G' => '190', 'H' => '191', 'I' => '192', 'J' => '193', 'K' => '194', 'L' => '195', 'M' => '196', 'N' => '197', 'O' => '198', 'P' => '199', 'Q' => '200', 'R' => '201', 'S' => '202', 'T' => '203', 'U' => '204', 'V' => '205', 'W' => '206', 'X' => '207', 'Y' => '208', 'Z' => '43', 'AA' => '257'); $i = 10; $total = array(); $amount = array(); $users = array(); if (count($submissions)) { foreach (array_merge($map, array('AC' => 0, 'AD' => 0)) as $column => $key) { $sheet->setCellValue($column . '7', '=SUM(' . $column . '11:' . $column . (count($submissions) + 10) . ')'); } $styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $sheet->getStyle('B11:AD' . (count($submissions) + 10))->applyFromArray($styleArray); } foreach ($submissions as $job => $list) { $job = Database_Mongo::collection('jobs')->findOne(array('_id' => $job)); $data = Arr::get($job, 'data'); $inst_min = false; $inst_max = false; $app_min = false; $app_max = false; $result = array(); $rate = array(); foreach ($list as $submission) { $users[$submission['user_id']] = 1; $inst = $submission['update_time']; if ($inst_min === false || $inst_min > $inst) { $inst_min = $inst; } if ($inst_max === false || $inst_max < $inst) { $inst_max = $inst; } $app = Arr::get($submission, 'process_time', false); if ($app_min === false && $app !== false || $app_min > $app) { $app_min = $app; } if ($app_max === false && $app !== false || $app_max < $app) { $app_max = $app; } $key = substr($submission['key'], 5); $result[$key] = $submission['paid']; $rate[$key] = $submission['rate']; } $sum = 0; foreach ($result as $key => $value) { $sum += $value * $rate[$key]; $total[$key] = Arr::get($total, $key, 0) + $value * $rate[$key]; $amount[$key] = Arr::get($amount, $key, 0) + $value; } $i++; $inst_min = date('d-m-Y', $inst_min); $inst_max = date('d-m-Y', $inst_max); $app_min = date('d-m-Y', $app_min); $app_max = date('d-m-Y', $app_max); $sheet->setCellValue('B' . $i, $inst_min == $inst_max ? $inst_min : $inst_min . ' - ' . $inst_max)->setCellValue('C' . $i, $app_min == $app_max ? $app_min : $app_min . ' - ' . $app_max)->setCellValue('D' . $i, $job['_id'])->setCellValue('E' . $i, Arr::get($data, 8))->setCellValue('F' . $i, Arr::get($data, 14))->setCellValue('AC' . $i, $sum); foreach ($map as $key => $value) { $sheet->setCellValue($key . $i, Arr::get($result, $value) ? $result[$value] : ''); } } foreach ($map as $key => $value) { $sheet->setCellValue($key . '10', Arr::get($amount, $value) ? $total[$value] / $amount[$value] : ''); } if ($users) { $companies = DB::select('name')->from('companies')->where('id', 'IN', DB::select('company_id')->distinct(true)->from('users')->where('id', 'IN', array_keys($users)))->execute()->as_array(NULL, 'name'); } else { $companies = array('None'); } $sheet->setCellValue('D2', implode(', ', $companies))->setCellValue('D3', $month); header('Content-type: application/xlsx'); header('Content-disposition: filename="Report.xlsx"'); $name = tempnam(sys_get_temp_dir(), 'excel'); $writer = new PHPExcel_Writer_Excel2007($doc); $writer->setPreCalculateFormulas(true); $writer->save($name); readfile($name); unlink($name); die; } $discrepancies = array(); foreach ($submissions as $job => $list) { $fl = false; foreach ($list as $submission) { if (Arr::path($jobs, $job . '.' . $submission['key']) != $submission['value']) { $fl = true; break; } } if (!$fl) { $discrepancies[$job] = 1; } } $view = View::factory("Reports/Financial")->set('approve_all', $start > 0 && date('m', $start) == date('m', $end) && Arr::get($_GET, 'company') && count($_GET['company']) == 1 && !Arr::get($_GET, 'fin-start'))->bind('companies', $companies)->bind('submissions', $submissions)->bind('discrepancies', $discrepancies)->bind('columns', $columns)->bind('jobs', $jobs)->bind('rates', $rates); $this->response->body($view); }
public function execute() { if ($this->getFindException()) { foreach ($this->getReasonsException() as $key => $reason) { $msg[$key] = $reason; } if ($reason !== null) { return $this->raiseError($reason, $key); } } $options = $this->getOptions(); $optionsExport = $options['settings']['export']; $optionsRenderer = $this->getOptionsRenderer(); $phpExcel = new PHPExcel(); // Sheet 1 $phpExcel->setActiveSheetIndex(0); $sheet = $phpExcel->getActiveSheet(); $sheet->setTitle($this->getTranslator()->translate($optionsRenderer['sheetName'])); if ($optionsRenderer['displayTitle'] === true) { $sheet->setCellValue('A' . $optionsRenderer['rowTitle'], $this->getTitle()); $sheet->getStyle('A' . $optionsRenderer['rowTitle'])->getFont()->setSize(15); } $this->calculateColumnWidth($this->getColumnsToExport()); $xColumn = 0; $yRow = $optionsRenderer['startRowData']; foreach ($this->getColumnsToExport() as $column) { /* @var $column \Zf2datatable\Column\AbstractColumn */ //$label = $this->getTranslator()->translate($column->getLabel()); $label = $column->getLabel(); $sheet->setCellValueByColumnAndRow($xColumn, $yRow, $label); // $sheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($xColumn))->setCollapsed(true); $sheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($xColumn))->setWidth($column->getWidth()); $xColumn++; } /* * Data */ $yRow = $optionsRenderer['startRowData'] + 1; foreach ($this->getData() as $row) { $xColumn = 0; foreach ($this->getColumnsToExport() as $column) { /* @var $column \Zf2datatable\Column\AbstractColumn */ $currentColumn = PHPExcel_Cell::stringFromColumnIndex($xColumn); $sheet->getCell($currentColumn . $yRow)->setValueExplicit($row[$column->getUniqueId()], PHPExcel_Cell_DataType::TYPE_STRING); $columnStyle = $sheet->getStyle($currentColumn . $yRow); $columnStyle->getAlignment()->setWrapText(true); /* * Styles */ $styles = array_merge($this->getRowStyles(), $column->getStyles()); foreach ($styles as $style) { /* @var $style \Zf2datatable\Column\Style\AbstractStyle */ if ($style->isApply($row) === true) { switch (get_class($style)) { case 'Zf2datatable\\Column\\Style\\Bold': $columnStyle->getFont()->setBold(true); break; case 'Zf2datatable\\Column\\Style\\Italic': $columnStyle->getFont()->setItalic(true); break; case 'Zf2datatable\\Column\\Style\\Color': $columnStyle->getFont()->getColor()->setRGB($style->getRgbHexString()); break; case 'Zf2datatable\\Column\\Style\\BackgroundColor': $columnStyle->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => $style->getRgbHexString()))); break; default: throw new \Exception('Not defined yet: "' . get_class($style) . '"'); break; } } } $xColumn++; } $yRow++; } /* * Autofilter, freezing, ... */ // Letzte Zeile merken $endRow = $yRow - 1; $endColumn = count($this->getColumnsToExport()) - 1; // Autofilter + Freeze $sheet->setAutoFilter('A' . $optionsRenderer['startRowData'] . ':' . PHPExcel_Cell::stringFromColumnIndex($endColumn) . $endRow); $freezeRow = $optionsRenderer['startRowData'] + 1; $sheet->freezePane('A' . $freezeRow); /* * Print settings */ $this->setPrinting($phpExcel); /* * Save the file */ $path = $optionsExport['path']; $saveFilename = $this->getCacheId() . '.xlsx'; $excelWriter = new \PHPExcel_Writer_Excel2007($phpExcel); $excelWriter->setPreCalculateFormulas(false); $excelWriter->save($path . '/' . $saveFilename); /* * Send the response stream */ $response = new ResponseStream(); $response->setStream(fopen($path . '/' . $saveFilename, 'r')); $headers = new Headers(); $headers->addHeaders(array('Content-Type' => array('application/force-download', 'application/octet-stream', 'application/download'), 'Content-Length' => filesize($path . '/' . $saveFilename), 'Content-Disposition' => 'attachment;filename=' . $this->getFilename() . '.xlsx', 'Cache-Control' => 'must-revalidate', 'Pragma' => 'no-cache', 'Expires' => 'Thu, 1 Jan 1970 00:00:00 GMT')); $response->setHeaders($headers); return $response; }