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 finalize() { $writer = new \PHPExcel_Writer_Excel2007($this->excel); $writer->setPreCalculateFormulas(false); $writer->save($this->filename); return $this->filename; }
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(); }
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 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 } }
/** * */ 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'); }
/** * @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 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; }
/** * 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); }
/** * 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; }
$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; }
$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); } });
$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); }
/** * @author caochunhui@dachuwang.com * @description 用数组和地址直接生成excel文件 * 每一个数组占一个sheet */ private function _convert_array_to_excel($arr = array(), $sheet_titles = array(), $out_name = '', $barcode_arr = array()) { //下面的代码是抄的。 //set cache $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; PHPExcel_Settings::setCacheStorageMethod($cacheMethod); //open excel file $write_objPHPExcel = new PHPExcel(); $write_objPHPExcel->getDefaultStyle()->getFont()->setName('simsun')->setSize(10); //下面要循环了 $sheet_cnt = 0; foreach ($arr as $item) { //用订单id.csv来命名每一个sheet $out_sheet = new PHPExcel_Worksheet($write_objPHPExcel, $sheet_titles[$sheet_cnt]); //$out_sheet->setTitle($item); //row index start from 1 $row_index = 0; foreach ($item as $row) { $row_index++; //$cellIterator = $row->getCellIterator(); //$cellIterator->setIterateOnlyExistingCells(false); //column index start from 0 $column_index = -1; foreach ($row as $cell) { $column_index++; //var_dump($cell); $out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell, PHPExcel_Cell_DataType::TYPE_STRING); } } //如果条码数组不为空,那么说明需要在sheet里插入条码 if (!empty($barcode_arr) && isset($barcode_arr[$sheet_cnt])) { $barcode_download_res = $this->_download_barcode($barcode_arr[$sheet_cnt]); if ($barcode_download_res['code'] == 200) { //no pic you say a jb $pic_path = $barcode_download_res['file']; $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('barcode'); $objDrawing->setDescription(''); $objDrawing->setPath($pic_path); $objDrawing->setHeight(50); $objDrawing->setCoordinates('D26'); //$objDrawing->setOffsetX(10); //$objDrawing->getShadow()->setVisible(true); //$objDrawing->getShadow()->setDirection(36); $objDrawing->setWorksheet($out_sheet); //no pic you say a jb } } $write_objPHPExcel->addSheet($out_sheet); $sheet_cnt++; } $write_objPHPExcel->removeSheetByIndex(0); //删除第一个空sheet //上面要循环了 //上面的代码是抄的 //write excel file $objWriter = new PHPExcel_Writer_Excel2007($write_objPHPExcel); $dir_name = dirname($out_name); if (!is_dir($dir_name)) { $res = mkdir($dir_name, 0777, TRUE); } $objWriter->save($out_name); }
$activeSheet->getColumnDimension('B')->setWidth(50); $activeSheet->getColumnDimension('C')->setWidth(8); $activeSheet->getColumnDimension('D')->setWidth(8); $activeSheet->getColumnDimension('E')->setWidth(8); $activeSheet->getColumnDimension('F')->setWidth(8); $activeSheet->getColumnDimension('G')->setWidth(15); $objWorkSheet->setTitle("T{$key}"); $sheet++; } $objPHPExcel->removeSheetByIndex(0); /** * start export */ ob_end_clean(); $excelFileName = 'Làm thêm giờ - ' . $tennv; header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $excelFileName . '.xlsx"'); header('Cache-Control: max-age=0'); header('Cache-Control: max-age=1'); header('Expires: Mon, 20 Jan 2015 05:00:00 GMT'); // Date in the past header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header('Pragma: public'); // HTTP/1.0 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $objWriter->save('php://output'); exit;
/** * @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"; }