public function toExcel($SiteName, $StrarTime, $FinshTime)
{
/** Error reporting */
error_reporting(E_ALL);
/** Include path **/
ini_set('include_path', ini_get('include_path') . ';../Classes/');
/** PHPExcel */
include 'PHPExcel.php';
/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';
// Create new PHPExcel object
// echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();
// Set properties
// echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Chen Po Hsun");
$objPHPExcel->getProperties()->setLastModifiedBy("Po Hsun");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
// Add some data
// echo date('H:i:s') . " Add some data\n";
$time_diff = (strtotime($time1) - strtotime($time2)) / (60 * 60) + 1;
$begin = 2;
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'SiteName');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Country!');
$objPHPExcel->getActiveSheet()->SetCellValue('D1', 'PM2.5');
$objPHPExcel->getActiveSheet()->SetCellValue('E1', 'PM10');
$objPHPExcel->getActiveSheet()->SetCellValue('F1', 'CO');
$data['query'] = $this->sql->select_site($SiteName, $StrarTime);
while ($begin < $time_diff + 2) {
$objPHPExcel->getActiveSheet()->SetCellValue('B1', $data['SiteName']);
$objPHPExcel->getActiveSheet()->SetCellValue('C1', $data['Country']);
$objPHPExcel->getActiveSheet()->SetCellValue('D1', $data['PM2.5']);
$objPHPExcel->getActiveSheet()->SetCellValue('E1', $data['PM10']);
$objPHPExcel->getActiveSheet()->SetCellValue('F1', $data['CO']);
$objPHPExcel->getActiveSheet()->SetCellValue('A$begin', $data['PublishTime']);
$begin++;
$data['query'] = $this->sql->select_site($SiteName, $FinshTime);
}
// Rename sheet
// echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('Test');
// Save Excel 2007 file
// echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
// Echo done
// echo date('H:i:s') . " Done writing file.\r\n";
}
public function flush()
{
if (!$this->active) {
return;
}
$this->outputResponse->setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$writer = new \PHPExcel_Writer_Excel2007($this->excelDoc);
$tempFile = tempnam(sys_get_temp_dir(), 'NiysuExcel');
$writer->save($tempFile);
$this->outputResponse->appendData(file_get_contents($tempFile));
unlink($tempFile);
}
/**
* Outputs export footer
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportFooter()
{
global $workbook;
global $tmp_filename;
$tmp_filename = tempnam(realpath($GLOBALS['cfg']['TempDir']), 'pma_xlsx_');
$workbookWriter = new PHPExcel_Writer_Excel2007($workbook);
$workbookWriter->save($tmp_filename);
if (!PMA_exportOutputHandler(file_get_contents($tmp_filename))) {
return FALSE;
}
unlink($tmp_filename);
unset($GLOBALS['workbook']);
unset($GLOBALS['sheet_index']);
return TRUE;
}
function modifyExcelFile($aMatrix, $sFileLink, $sSheetname, $bIsDownload = false)
{
$this->load->library('excel');
$inputFileType = PHPExcel_IOFactory::identify($sFileLink);
$objPHPExcel = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objPHPExcel->load($sFileLink);
// Add data
$objPHPExcel->disconnectWorksheets();
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
foreach ($aMatrix as $sCol => $sValue) {
$aCols = explode('-', $sCol);
if (count($aCols) > 1) {
$concat = $aCols[0] . ":" . $aCols[1];
$activeSheet->mergeCells($concat);
$sCol = $aCols[0];
}
if (is_array($sValue)) {
$sColor = $sValue['color'];
$sValue = $sValue['value'];
$activeSheet->getStyle($sCol)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => $sColor))));
}
$activeSheet->SetCellValue($sCol, $sValue);
unset($aCols);
}
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle($sSheetname);
// Save Excel 2007 file
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel, 'Excel2007');
$objWriter->save($sFileLink);
// Redirect output to a client’s web browser (Excel2007)
if ($bIsDownload) {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . basename($sFileLink) . '"');
header('Cache-Control: max-age=0');
readfile($sFileLink);
}
}
public function output()
{
$writer = new PHPExcel_Writer_Excel2007($this->spreadsheet);
$file = "app/temp/" . uniqid() . "_report.xlsx";
$writer->save($file);
Application::redirect("/{$file}");
}
/**
* Dumps XLS file to memory & retrieves content
* @param \PHPExcel $xls
* @return string
*/
private function getBuffer(\PHPExcel $xls)
{
ob_start();
$writer = new \PHPExcel_Writer_Excel2007($xls);
$writer->save('php://output');
return ob_get_clean();
}
public function finalize()
{
$writer = new \PHPExcel_Writer_Excel2007($this->excel);
$writer->setPreCalculateFormulas(false);
$writer->save($this->filename);
return $this->filename;
}
function _output($title)
{
header('Content-Type: application/vnd.openXMLformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = new PHPExcel_Writer_Excel2007($this->xls);
$objWriter->save('php://output');
exit;
}
function display()
{
global $current_user;
$c = range('A', 'Z');
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// set column size to auto
foreach ($c as $columnID) {
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}
// Set properties
$objPHPExcel->getProperties()->setCreator($current_user->display_name);
$objPHPExcel->getProperties()->setLastModifiedBy($current_user->display_name);
$objPHPExcel->getProperties()->setTitle($this->title);
$objPHPExcel->getProperties()->setSubject($this->subject);
$objPHPExcel->getProperties()->setDescription($this->description);
// Add some data
$objPHPExcel->setActiveSheetIndex(0);
// table header
$x = 0;
foreach ($this->cols as $v) {
$objPHPExcel->getActiveSheet()->SetCellValue($c[$x] . '1', $v)->getStyle($c[$x] . '1')->getFont()->setBold(true);
// A1, B1, etc
$x++;
}
// table row
foreach ($this->data as $i => $item) {
$j = $i + 2;
// A2, B2, etc
$x = 0;
$index = $i + 1;
$item->index = 0;
foreach ($this->cols as $column_name => $v) {
if ($column_name == 'index') {
$item->index = 10;
}
#$val = $item->$column_name;
$method = 'column_' . $column_name;
if (method_exists($this->table_obj, $method)) {
$val = $this->table_obj->{$method}($item, $column_name);
} else {
$val = $this->table_obj->column_default($item, $column_name);
}
$objPHPExcel->getActiveSheet()->SetCellValue($c[$x] . $j, $val, $format);
$x++;
}
}
$objPHPExcel->getActiveSheet()->setTitle('Sheet 1');
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $this->filename . '"');
$objWriter->save('php://output');
exit;
}
function xlsToXslx($file_name_input, $file_name_output)
{
try {
$objPHPexcel = PHPExcel_IOFactory::load($file_name_input);
$objWriter = new PHPExcel_Writer_Excel2007($objPHPexcel);
$objWriter->setOffice2003Compatibility(true);
$objWriter->save($file_name_output);
return true;
} catch (Exception $e) {
return false;
}
}
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 create_worksheet($excel_data = NUll)
{
//check if the excel data has been set if not exit the excel generation
if (count($excel_data) > 0) {
//echo "<pre/>";
//print_r($excel_data);
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '2MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
ini_set('max_execution_time', 123456);
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("CD4");
$objPHPExcel->getProperties()->setLastModifiedBy($excel_data['doc_creator']);
$objPHPExcel->getProperties()->setTitle($excel_data['doc_title']);
$objPHPExcel->getProperties()->setSubject($excel_data['doc_title']);
$objPHPExcel->getProperties()->setDescription("");
// Add some data
// echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0);
$rowExec = 1;
//Looping through the cells
$column = 0;
// foreach ($excel_data['column_data'] as $cell) {
// $objPHPExcel -> getActiveSheet() -> setCellValueByColumnAndRow($column, $rowExec, $cell);
// $objPHPExcel -> getActiveSheet() -> getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($column)) -> setAutoSize(true);
// $column++;
// }
// $rowExec = 2;
// $column = 0;
// foreach ($excel_data['row_data'] as $cell) {
// //Looping through the cells per facility
// $objPHPExcel -> getActiveSheet() -> setCellValueByColumnAndRow($column, $rowExec, $cell);
// $rowExec++;
// $column++;
// }
$objPHPExcel->getActiveSheet()->fromArray($excel_data['row_data'], NULL, 'A1');
// Rename sheet
// echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Save Excel 2007 file
//echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
// We'll be outputting an excel file
header('Content-type: application/vnd.ms-excel');
// It will be called file.xls
header("Content-Disposition: attachment; filename=" . $excel_data['file_name']);
// Write file to the browser
$objWriter->save('php://output');
// Echo done
}
}
/**
* @return array
*/
public function downloadAction()
{
if (!$this->isGranted('event.entries')) {
return $this->redirect()->toRoute('home');
}
$id = (int) $this->params()->fromRoute('id');
$trial = $this->getRepository()->find($id);
if (!$trial || !$trial->getEvent()->isApproved()) {
return $this->notFoundAction();
}
$scoreSheet = $this->getEventService()->createScoreSheet($trial);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename=\'USCSS Trial Score Sheet - ' . $trial . '.xlsx\'');
header('Cache-Control: max-age=0');
$output = new \PHPExcel_Writer_Excel2007($scoreSheet);
$output->save('php://output');
return $this->getResponse();
}
/**
*
*/
public function render()
{
$excel = new \PHPExcel();
$excel->getDefaultStyle()->getFont()->setName('Arial');
$excel->getDefaultStyle()->getFont()->setSize(10);
$this->_sheet = $excel->getActiveSheet();
$this->_sheet->setTitle($this->_tabs_title);
$this->_sheet->getTabColor()->setARGB('FFc3e59e');
$this->_sheet->fromArray($this->makeExportData());
//resize
$this->excelWidth();
//height header
header('Content-Type: application/excel');
header('Content-Disposition: attachment; filename="' . $this->getFile() . '"');
header('Cache-Control: max-age=0');
$writer = new \PHPExcel_Writer_Excel2007($excel);
$writer->save('php://output');
}
public function summaryReportAction($year, $quarter)
{
$periodMananger = $this->get('wealthbot_ria.period.manager');
$reportMananger = $this->get('wealthbot_ria.billing_report.manager');
// Generate report
$periods = $periodMananger->getQuarterPeriod($year, $quarter);
$excel = $reportMananger->generateSummary($periods);
// Generate filename
$periods = array_keys($periods);
$quarter = empty($quarter) ? min($periods) . '-' . max($periods) : $quarter;
$filename = "billing_summary_q{$quarter}_{$year}";
$response = new Response();
$response->headers->add(array('Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'Content-Disposition' => 'attachment;filename="' . $filename . '.xlsx"', 'Cache-Control' => 'max-age=0'));
ob_start();
$writer = new \PHPExcel_Writer_Excel2007($excel);
$writer->save('php://output');
$response->setContent(ob_get_clean());
return $response;
}
/**
* Для записи в файл таблиц использует библиотеку PHPExcell. Также копирует картинки в папку out/images/ с изменением имени.
* @param array $products
* @param string $imagesInDir
* @param string $imagesOutDir
*/
public function write(array $products, $imagesInDir, $imagesOutDir)
{
$excelDoc = new \PHPExcel();
$excelDoc->setActiveSheetIndex();
$activeSheet = $excelDoc->getActiveSheet();
$activeSheet->setTitle("Таблица товаров");
//Записываем заголовок
$col = 'A';
foreach (self::header as $headerElem => $elemWidth) {
$activeSheet->setCellValue($col . 1, $headerElem);
$activeSheet->getColumnDimension($col++)->setWidth($elemWidth * WIDTH_MULTIPLIER);
}
$activeSheet->freezePane('A2');
//записываем информацию о товарах
foreach ($products as $index => $product) {
$activeSheet->setCellValue('A' . ($index + 2), $index + 1);
//Номер товара
$activeSheet->setCellValue('B' . ($index + 2), 1);
// Тип товара
$activeSheet->setCellValue('C' . ($index + 2), $product->category->name);
// Категория
$activeSheet->setCellValue('D' . ($index + 2), sprintf("%s;%s;%s", $product->manufacturer->name, $product->manufacturer->country, $product->manufacturer->url));
$activeSheet->setCellValue('E' . ($index + 2), $product->name);
// Название
$activeSheet->setCellValue('F' . ($index + 2), $product->ingredients);
// Состав
$activeSheet->setCellValue('G' . ($index + 2), $product->shortDescr);
// Краткое описание
$activeSheet->setCellValue('H' . ($index + 2), '');
// Полное описание
$activeSheet->setCellValue('I' . ($index + 2), $product->keywords);
// Ключевые слова
$activeSheet->setCellValue('J' . ($index + 2), $product->price);
// Цена
$activeSheet->setCellValue('K' . ($index + 2), $product->sale);
// Скидка
$this->writeImages($product, $index + 1, $imagesInDir, $imagesOutDir);
}
//Сохраняем файл таблиц на диске
$docWriter = new \PHPExcel_Writer_Excel2007($excelDoc);
$docWriter->save($this->fileName);
}
/**
* Dumps the stats table
* @param StatsTable $statsTable
* @return string
*/
public function dump(StatsTable $statsTable)
{
$excel = new \PHPExcel();
$excel->getDefaultStyle()->applyFromArray($this->getDefaultStyleArray());
$sheet = $excel->getSheet();
$row = 1;
$data = $statsTable->getData();
$width = count(reset($data));
// HEADERS //
if ($this->enableHeaders) {
$headerStyle = new \PHPExcel_Style();
$headerStyle->applyFromArray($this->getHeadersStyleArray());
$col = 0;
foreach ($statsTable->getHeaders() as $header) {
$sheet->setCellValueByColumnAndRow($col, $row, $header);
$col++;
}
$sheet->duplicateStyle($headerStyle, 'A1:' . \PHPExcel_Cell::stringFromColumnIndex($width - 1) . '1');
$row++;
}
// DATA //
foreach ($statsTable->getData() as $data) {
$this->applyValues($sheet, $row, $data, $statsTable->getDataFormats());
$row++;
}
// AGGREGATIONS //
if ($this->enableAggregation) {
$this->applyValues($sheet, $row, $statsTable->getAggregations(), $statsTable->getAggregationsFormats(), $this->getAggregationsStyleArray());
}
// FINAL FORMATTING //
for ($col = 0; $col < $width; $col++) {
$sheet->getColumnDimension(\PHPExcel_Cell::stringFromColumnIndex($col))->setAutoSize(true);
}
$xlsDumper = new \PHPExcel_Writer_Excel2007($excel);
$pFilename = @tempnam(\PHPExcel_Shared_File::sys_get_temp_dir(), 'phpxltmp');
$xlsDumper->save($pFilename);
$contents = file_get_contents($pFilename);
@unlink($pFilename);
unset($excel);
unset($xlsDumper);
return $contents;
}
/**
* Write excel function
* @param array $titles
* @param array $values
*/
public function write($titles, $values, $filename = 'Negocios')
{
App::import('Vendor', 'PHPExcel');
App::import('Vendor', 'PHPExcel/Writer/Excel2007');
$objPHPExcel = new PHPExcel();
//Set properties
$objPHPExcel->getProperties()->setCreator("Sheep Head Apps");
$objPHPExcel->getProperties()->setLastModifiedBy("Sheep Head Apps");
$objPHPExcel->getProperties()->setTitle("Negocios");
$objPHPExcel->getProperties()->setSubject("Negocios Infobot");
$objPHPExcel->getProperties()->setDescription("Documento para la base de datos de infobot.");
//Set active sheet
$objPHPExcel->setActiveSheetIndex(0);
$colums = array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z");
$index_x = 0;
$index_y = 1;
//Set titles
foreach ($titles as $title) {
$objPHPExcel->getActiveSheet()->SetCellValue($colums[$index_x] . $index_y, $title);
$index_x++;
}
$index_x = 0;
$index_y = 2;
//Set info
foreach ($values as $value) {
foreach ($value as $val) {
$objPHPExcel->getActiveSheet()->SetCellValue($colums[$index_x] . $index_y, $val);
$index_x++;
}
$index_y++;
$index_x = 0;
}
$objPHPExcel->getActiveSheet()->setTitle('Negocios');
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
// Redirect output to a clientÕs web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit;
}
/**
* Read given csv file and write all rows to given xls file
*
* @param string $csv_file Resource path of the csv file
* @param string $xls_file Resource path of the excel file
* @param string $csv_enc Encoding of the csv file, use utf8 if null
* @throws Exception
*/
public static function convert($csv_file, $xls_file, $csv_enc = null)
{
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
$cacheSettings = array('dir' => 'E:/PHPExcel_cache');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
} else {
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
}
//open csv file
$objReader = new PHPExcel_Reader_CSV();
$objReader->setDelimiter(CSVToExcelConverter::guessDelimiter($csv_file));
if ($csv_enc != null) {
$objReader->setInputEncoding($csv_enc);
}
$objPHPExcel_CSV = $objReader->load($csv_file);
$in_sheet = $objPHPExcel_CSV->getActiveSheet();
//open excel file
$objPHPExcel_XLSX = new PHPExcel();
$out_sheet = $objPHPExcel_XLSX->getActiveSheet();
$out_sheet->setTitle('File Content');
//row index start from 1
$row_index = 0;
foreach ($in_sheet->getRowIterator() as $row) {
//if($row_index==20)
// break;
$row_index++;
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
//column index start from 0
$column_index = -1;
foreach ($cellIterator as $cell) {
$column_index++;
$out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell->getValue());
}
}
//write excel file
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel_XLSX);
$objWriter->save($xls_file);
}
/**
* Close Excel file
*
* @return int <0 if KO, >0 if OK
*/
function close_file()
{
global $conf;
if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
$this->workbook->close();
} else {
require_once PHPEXCEL_PATH . 'PHPExcel/Writer/Excel5.php';
$objWriter = new PHPExcel_Writer_Excel2007($this->workbook);
$objWriter->save($this->file);
$this->workbook->disconnectWorksheets();
unset($this->workbook);
}
return 0;
}
$objPHPExcel->getDefaultStyle()->getFont()->setName('Helvetica')->setSize(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(60);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
//title
$objPHPExcel->getActiveSheet()->getStyle('A1:G3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1:A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//header row
$objPHPExcel->getActiveSheet()->getStyle('A5:G5')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('A5:G5')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A5:G5')->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => '000000')));
$objPHPExcel->getActiveSheet()->getStyle('A5:G5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
for ($x = 1; $x <= $rowxls; $x++) {
$objPHPExcel->getActiveSheet()->getRowDimension($x)->setRowHeight(22);
}
$objPHPExcel->getActiveSheet()->getStyle('A1:G' . $rowxls)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A6:B' . $rowxls)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('C6:G' . $rowxls)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
// Rename sheet
//echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('Categories');
// Save Excel 2007 file
//echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save('custom/reports/exports/' . '[' . $_global_general->website_title . ']-' . cleanurl($date_title . ' Sales By Items (Categories)') . '.xlsx');
// Echo done
//echo date('H:i:s') . " Done writing file.\r\n";
}
$date = strtotime($rec['ShipmentDate']);
$newdate = date('d/m/Y', $date);
$objPHPExcel->getActiveSheet()->SetCellValue('A' . $j, $i)->SetCellValue('B' . $j, $rec['GroupName'])->SetCellValue('C' . $j, $rec['ItemName'])->SetCellValue('D' . $j, $rec['ShipmentStatusDesc'])->SetCellValue('E' . $j, $newdate)->SetCellValue('F' . $j, $rec['OwnerTypeName'])->SetCellValue('G' . $j, $rec['Qty'] == '' ? '' : number_format($rec['Qty']));
$objPHPExcel->getActiveSheet()->getStyle('G' . $j . ':G' . $j)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('A' . $j . ':A' . $j)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000'))));
$objPHPExcel->getActiveSheet()->getStyle('A' . $j . ':A' . $j)->applyFromArray($styleThinBlackBorderOutline);
$objPHPExcel->getActiveSheet()->getStyle('B' . $j . ':B' . $j)->applyFromArray($styleThinBlackBorderOutline);
$objPHPExcel->getActiveSheet()->getStyle('C' . $j . ':C' . $j)->applyFromArray($styleThinBlackBorderOutline);
$objPHPExcel->getActiveSheet()->getStyle('D' . $j . ':D' . $j)->applyFromArray($styleThinBlackBorderOutline);
$objPHPExcel->getActiveSheet()->getStyle('E' . $j . ':E' . $j)->applyFromArray($styleThinBlackBorderOutline);
$objPHPExcel->getActiveSheet()->getStyle('F' . $j . ':F' . $j)->applyFromArray($styleThinBlackBorderOutline);
$objPHPExcel->getActiveSheet()->getStyle('G' . $j . ':G' . $j)->applyFromArray($styleThinBlackBorderOutline);
$i++;
$j++;
}
if (function_exists('date_default_timezone_set')) {
date_default_timezone_set('UTC');
} else {
putenv("TZ=UTC");
}
$exportTime = date("Y-m-d_His", time());
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$file = 'Shipment_List' . $exportTime . '.xlsx';
$objWriter->save(str_replace('.php', '.xlsx', 'media/' . $file));
header('Location:media/' . $file);
} else {
$error = "No record found";
echo $error;
}
$sheet->fromArray(
$grid, // The data to set
NULL, // Array values with this value will not be set
'A4' // Top left coordinate of the worksheet range where we want to set these values (default is A1)
);
$xls->addSheet($sheet);
$nbre_commandes = $nbre_commandes+1;
}
$recapsheet->SetCellValue('F1', $nbre_commandes);
$recapsheet->fromArray(
$recap, // The data to set
NULL, // Array values with this value will not be set
'A1' // Top left coordinate of the worksheet range where we want to set these values (default is A1)
);
$xlsWriter = new PHPExcel_Writer_Excel2007($xls);
$file = tempnam(sys_get_temp_dir(), 'Tux');
$xlsWriter->save($file);
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename=Preparations.xlsx');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . filesize($file));
ob_clean();
flush();
readfile($file);
unlink($file);
}
} elseif ($vs_display_text = $t_display->getDisplayValue($vo_result, $vn_placement_id, array_merge(array('request' => $this->request, 'purify' => true), is_array($va_info['settings']) ? $va_info['settings'] : array()))) {
$o_sheet->setCellValue($vs_supercol . $vs_column . $vn_line, html_entity_decode(strip_tags(br2nl($vs_display_text)), ENT_QUOTES | ENT_HTML5));
// We trust the autosizing up to a certain point, but
// we want column widths to be finite :-).
// Since Arial is not fixed-with and font rendering
// is different from system to system, this can get a
// little dicey. The values come from experimentation.
if ($o_sheet->getColumnDimension($vs_supercol . $vs_column)->getWidth() == -1) {
// don't overwrite existing settings
if (strlen($vs_display_text) > 55) {
$o_sheet->getColumnDimension($vs_supercol . $vs_column)->setWidth(50);
}
}
}
if (!($vs_column = next($va_a_to_z))) {
$vs_supercol = array_shift($va_supercol_a_to_z);
$vs_column = reset($va_a_to_z);
}
}
$vn_line++;
}
// set column width to auto for all columns where we haven't set width manually yet
foreach (range('A', 'Z') as $vs_chr) {
if ($o_sheet->getColumnDimension($vs_chr)->getWidth() == -1) {
$o_sheet->getColumnDimension($vs_chr)->setAutoSize(true);
}
}
$o_writer = new PHPExcel_Writer_Excel2007($workbook);
@header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
@header('Content-Disposition:inline;filename=Export.xlsx ');
$o_writer->save('php://output');
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');
}
public function render()
{
$this->setFileName();
$this->getData();
$writer = new \PHPExcel_Writer_Excel2007($this->_excel);
$writer->save($this->getFileAbsolute());
/* header('Content-Type: application/excel');
header('Content-Disposition: attachment; filename="' . $this->getFileNameExt() . '"');
header('Cache-Control: max-age=0');
$writer->save('php://output'); */
unset($this->_excel);
}
/**
* @return string filename
*/
public function export()
{
global $CFG;
/** PHPExcel */
include $CFG->dirroot . '/blocks/evalcomix/classes/excel/PHPExcel.php';
/** PHPExcel_Writer_Excel2007 */
include $CFG->dirroot . '/blocks/evalcomix/classes/excel/PHPExcel/Writer/Excel2007.php';
// Create new PHPExcel object
//echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();
// Set properties
//echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("EvalCOMIX");
$objPHPExcel->getProperties()->setLastModifiedBy("Moodle");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Report for EvalCOMIX_MD.");
$row = 1;
// 1-based index
foreach ($this->header as $col => $value) {
$objPHPExcel->getActiveSheet()->getStyle($row)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
}
$row = 2;
foreach ($this->names as $key => $left_value) {
$col = 0;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $left_value);
$col = 1;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $this->surnames[$key]);
$index = $row - 2;
if (isset($this->right_rows[$index])) {
foreach ($this->right_rows[$index] as $right_value) {
$col++;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $right_value);
}
}
$row++;
}
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Report EvalCOMIX');
// Save Excel 2007 file
//echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
//$dirname = dirname(__FILE__);
//$filename = $dirname.'/tmp/report_evalcomix'.date('Ymd_H-i-s').'.xlsx';
$filename = '/blocks/evalcomix/reports/tmp/report_evalcomix' . date('Ymd_H-i-s') . '.xlsx';
$objWriter->save($CFG->dirroot . $filename);
return $filename;
}
/**
* @Route("/export/{id}", name="dsp_query_query_export")
*/
public function exportAction(Query $query)
{
$em = $this->getDoctrine()->getManager();
/** @var \PDO $pdo */
$pdo = $em->getConnection();
$req = $pdo->prepare($query->getQuery());
$req->execute();
$objPHPExcel = new \PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$numRow = 0;
while ($row = $req->fetch()) {
$numRow++;
if ($numRow == 1) {
$numCol = 0;
foreach (array_keys($row) as $val) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($numCol, $numRow, $val);
$numCol++;
}
$numRow++;
}
$numCol = 0;
foreach ($row as $val) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($numCol, $numRow, $val);
$numCol++;
}
}
$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
$file = "/tmp/test.xlsx";
$objWriter->save($file);
header('Content-Disposition: attachment; filename=' . basename($file));
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Length: ' . filesize($file));
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');
readfile($file);
}
/**
* Export
*
* @access public
*/
public function export()
{
$excel = new \PHPExcel();
$worksheet = $excel->getActiveSheet();
$row = 1;
$worksheet->setCellValueByColumnAndRow(0, $row, 'Product');
$worksheet->setCellValueByColumnAndRow(1, $row, 'price');
$worksheet->setCellValueByColumnAndRow(2, $row, 'amount');
$worksheet->setCellValueByColumnAndRow(3, $row, 'Total price');
$excel->getActiveSheet()->getStyle('A1:D1')->getFont()->setBold(true);
foreach ($this->get_purchase_order_items() as $purchase_order_item) {
$row++;
$worksheet->setCellValueByColumnAndRow(0, $row, $purchase_order_item->get_stock_object()->get_name());
$worksheet->setCellValueByColumnAndRow(1, $row, $purchase_order_item->price);
$worksheet->setCellValueByColumnAndRow(2, $row, $purchase_order_item->amount);
$worksheet->setCellValueByColumnAndRow(3, $row, $purchase_order_item->price * $purchase_order_item->amount);
}
$row++;
$worksheet->setCellValueByColumnAndRow(3, $row, $this->get_price());
$excel->getActiveSheet()->getStyle($row . ':' . $row)->getFont()->setBold(true);
$cellIterator = $worksheet->getRowIterator()->current()->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(true);
/** @var PHPExcel_Cell $cell */
foreach ($cellIterator as $cell) {
$worksheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
}
$writer = new \PHPExcel_Writer_Excel2007($excel);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="purchase_order_' . $this->id . '.xls"');
header('Cache-Control: max-age=0');
$writer->save('php://output');
exit;
}
public function actionIndex()
{
Yii::import('ext.yiiexcel.YiiExcel', true);
Yii::registerAutoloader(array('YiiExcel', 'autoload'), true);
echo "run dump\n";
$root = dirname(dirname(dirname(__FILE__)));
$ts = (int) (time() / 1000);
$filePath = $root . "/download/{$ts}.xlsx";
if (file_exists($filePath)) {
echo "Location: /download/{$ts}.xlsx";
echo "Cache: hit";
exit;
}
$model = new Jmuser('search');
$model->unsetAttributes();
// clear any default values
$model->attributes = array();
$data = $model->search();
$data->setPagination(false);
$objPHPExcel = new PHPExcel();
$row = 1;
foreach (Jmuser::fields() as $col => $field) {
$labels = $model->attributeLabels();
$name = isset($labels[$field]) ? $labels[$field] : $field;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $name);
}
//var_dump($cnt, $data->getItemCount(), $data->getTotalItemCount());
$users = $data->getData(array("order" => "sysID"));
foreach ($users as $key => $user) {
echo "{$row}/" . count($users) . " " . $user->oa . "\n";
$row += 1;
foreach (Jmuser::fields() as $col => $field) {
$val = $user->{$field};
$cell = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($col, $row);
$cell->setValueExplicit($val, PHPExcel_Cell_DataType::TYPE_STRING);
}
}
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save($filePath);
echo "Location: /downloads/{$ts}.xlsx";
}
