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"; }
function generate($generator) { require_once "PHPExcel.php"; $data = $generator->generateExportData(); $this->chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; $this->charArray = str_split($this->chars, 1); $objPHPExcel = new PHPExcel(); // set document properties $objPHPExcel->getProperties()->setTitle("Test Data"); // create a first sheet and populate the headings $objPHPExcel->setActiveSheetIndex(0); // hardcoded limitation of 26 x 27 columns (right now) $numCols = count($data["colData"]); for ($i = 0; $i < $numCols; $i++) { $col = $this->getExcelCol($i, 1); $objPHPExcel->getActiveSheet()->setCellValue($col, $data["colData"][$i]); } for ($i = 0; $i < count($data["rowData"]); $i++) { for ($j = 0; $j < $numCols; $j++) { $col = $this->getExcelCol($j, $i + 2); $objPHPExcel->getActiveSheet()->setCellValue($col, $data["rowData"][$i][$j]); } } // redirect output to a client’s web browser (Excel5) header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="01simple.xls"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; }
public function payQuickerReport() { $market = array(1 => 'US', 2 => 'CA', 3 => 'AU', 4 => 'NZ', 5 => 'GB'); // error_reporting(E_ALL); $data = $this->RoyaltiesEarned->getRoyaltiesReport(3, ['method' => 'all']); // echo '<pre>'; // print_r($data); // echo '</pre>'; // die; // for ($i = 0; $i <= $queryCount; $i = $i + $this->payQuickerReportLimit) { // // } $objPHPExcel = new PHPExcel(); // Set properties $objPHPExcel->getProperties()->setCreator(""); $objPHPExcel->getProperties()->setLastModifiedBy(""); $objPHPExcel->getProperties()->setTitle(""); $objPHPExcel->getProperties()->setSubject(""); $objPHPExcel->getProperties()->setDescription(""); $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setTitle('Instructions'); // Add some data $objPHPExcel->createSheet(1); $objPHPExcel->setActiveSheetIndex(1); $objPHPExcel->getActiveSheet()->setTitle('Instant Payments'); $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'INSTANT PAYMENTS'); $objPHPExcel->getActiveSheet()->SetCellValue('A2', "RECIPIENT'S EMAIL ADDRESS\n * Required!"); $objPHPExcel->getActiveSheet()->SetCellValue('B2', "PAYMENT AMOUNT\n * Required"); $objPHPExcel->getActiveSheet()->SetCellValue('C2', "COUNTRY CODE\n * Required"); $objPHPExcel->getActiveSheet()->SetCellValue('D2', "STATE CODE\n * Required"); $objPHPExcel->getActiveSheet()->SetCellValue('E2', "COMMENT\n Optional"); $objPHPExcel->getActiveSheet()->SetCellValue('F2', "SECURITY ID\n Optional"); $objPHPExcel->getActiveSheet()->SetCellValue('G2', "SECURITY ID HINT\n Optional"); $objPHPExcel->getActiveSheet()->SetCellValue('H2', "ACCOUNTING ID\n Optional"); $objPHPExcel->getActiveSheet()->SetCellValue('I2', "EXPIRATION DATE\n Optional"); $objPHPExcel->getActiveSheet()->SetCellValue('J2', "UDF1\n Optional"); $objPHPExcel->getActiveSheet()->SetCellValue('K2', "UDF2\n Optional"); $objPHPExcel->getActiveSheet()->SetCellValue('L2', "UDF3\n Optional"); $objPHPExcel->getActiveSheet()->SetCellValue('M2', "AUTO ISSUE DEBIT CARDS\n Optional"); $objPHPExcel->getActiveSheet()->SetCellValue('N2', "EXCLUDE FROM 1099 RECONCILIATION\n Optional"); // Add data for ($i = 0; $i < count($data); $i++) { $cell = $i + 3; $objPHPExcel->getActiveSheet()->setCellValue('A' . $cell, $data[$i]['Email']['email'])->setCellValue('B' . $cell, $data[$i]['RoyaltiesEarned']['amount'])->setCellValue('C' . $cell, $market[$data[$i]['RoyaltiesEarned']['market_id']])->setCellValue('D' . $cell, $data[$i]['State']['abbrev'])->setCellValue('D' . $cell, $data[$i]['State']['abbrev'])->setCellValue('E' . $cell, $data[$i]['User']['first_name'])->setCellValue('M' . $cell, 'YES'); } $objPHPExcel->createSheet(2); $objPHPExcel->setActiveSheetIndex(2); $objPHPExcel->getActiveSheet()->setTitle('Version'); $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Version'); $objPHPExcel->getActiveSheet()->SetCellValue('A2', '816eda7a-db7a-4c83-adeb-206bdfad2bb0'); // Save Excel 2007 file $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="file.xls"'); // Write file to the browser // $objWriter->save('php://output'); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); }
function createExcel($no_telp, $npwd) { ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/Classes/PHPExcel.php'; // Create new PHPExcel object $objPHPExcel = new PHPExcel(); // Set document properties $objPHPExcel->getProperties()->setCreator("Disyanjak Bandung")->setLastModifiedBy("Disyanjak Bandung")->setTitle("Daftar SMS")->setSubject("Daftar SMS")->setDescription("Daftar SMS untuk ke WP")->setKeywords("office PHPExcel php")->setCategory("Test result file"); $objPHPExcel->getActiveSheet()->getStyle('A2')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); // Add some data $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Phone No.')->setCellValue('A2', $no_telp); //$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); //$objPHPExcel->getActiveSheet()->setCellValue('A8',"Hello\nWorld"); //$objPHPExcel->getActiveSheet()->getRowDimension(8)->setRowHeight(-1); //$objPHPExcel->getActiveSheet()->getStyle('A8')->getAlignment()->setWrapText(true); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Daftar SMS'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Save Excel 2007 file $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $fileName = 'send_sms_' . $npwd; $objWriter->save($fileName . '.xlsx'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save($fileName . '.xls'); return $fileName; }
function get_comments_xls($filename, $cons) { require_once TEMPLATEPATH . '/app/PHPExcel.php'; global $wpdb; $sql = "SELECT post_title,comment_ID,comment_author, comment_date_gmt, comment_content\n\t\t\tFROM {$wpdb->comments}\n\t\t\t\tLEFT OUTER JOIN {$wpdb->posts} ON ({$wpdb->comments}.comment_post_ID = {$wpdb->posts}.ID)\n\t\t\t\tINNER JOIN {$wpdb->term_relationships} as r1 ON ({$wpdb->posts}.ID = r1.object_id)\n\t\t\t\tINNER JOIN {$wpdb->term_taxonomy} as t1 ON (r1.term_taxonomy_id = t1.term_taxonomy_id)\n\t\t\tWHERE comment_approved = '1'\n\t\t\t\tAND comment_type = ''\n\t\t\t\tAND post_password = ''\n\t\t\t\tAND t1.taxonomy = 'category'\n\t\t\t\tAND t1.term_id = " . $cons . "\n\t\t\torder by comment_date_gmt"; $qr = $wpdb->get_results($sql, ARRAY_N); // Create new PHPExcel object $objPHPExcel = new PHPExcel(); // Set properties $objPHPExcel->getProperties()->setCreator("Consultator")->setLastModifiedBy("Consultator")->setTitle("Consultator")->setSubject("Consultator")->setDescription("Αρχείο Εξαγωγής Σχολίων")->setKeywords("Σχόλια")->setCategory("Αρχείο Σχολίων"); // Add some data // Headers $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Άρθρο')->setCellValue('B1', 'Κωδικός Σχολίου')->setCellValue('C1', 'Σχολιαστής')->setCellValue('D1', 'Ημερομηνία Υποβολής')->setCellValue('E1', 'Σχόλιο'); $objPHPExcel->getActiveSheet()->fromArray($qr, NULL, 'A2'); // Rename sheet $objPHPExcel->getActiveSheet()->setTitle('Σχόλια Διαβούλευσης'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client’s web browser (Excel5) header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="' . $filename . '.xls"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); $objPHPExcel->disconnectWorksheets(); unset($objPHPExcel); exit; }
/** * @return \PHPExcel */ protected function getExcel() { if ($this->excel === null) { $this->excel = new \PHPExcel(); $this->excel->getProperties()->setCreator('dasred/translation'); $this->excel->setActiveSheetIndex(0); $sheet = $this->excel->getActiveSheet()->setTitle(basename($this->getArguments()[0])); } return $this->excel; }
function create_timesheet($data) { error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); require_once dirname(__FILE__) . '/Classes/PHPExcel.php'; $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'TIME-SHEET ' . date('d-m-Y') . ' (' . date('l') . ')'); $objPHPExcel->getActiveSheet()->setCellValue('A2', 'No'); $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Employ Id'); $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Name'); $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Attendence'); $objPHPExcel->getActiveSheet()->setCellValue('E2', 'Extra Hours'); $objPHPExcel->getActiveSheet()->setCellValue('F2', 'Reason For Leave'); $i = 3; foreach ($data as $employ) { $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i - 2); $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $employ['emp_id']); $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $employ['emp_f_name'] . ' ' . $employ['emp_l_name']); $i++; } $heading = array('font' => array('bold' => true, 'color' => array('rgb' => 'FFFFFF'), 'size' => 16, 'name' => 'Calibri'), 'alignment' => array('wrap' => true, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)); $columnheaderstyle = array('font' => array('bold' => true, 'color' => array('rgb' => '000000'), 'size' => 12, 'name' => 'Calibri'), 'alignment' => array('wrap' => true, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)); $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:F1'); $objPHPExcel->getActiveSheet()->getStyle('A1:F1')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '1F497D')))); $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($heading); $objPHPExcel->getActiveSheet()->getStyle('A2:F2')->applyFromArray($columnheaderstyle); $objPHPExcel->getActiveSheet()->getStyle('A3:A500')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D3:D500')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E3:E500')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B3:B500')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(6.85); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(17.5); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(17.5); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(19); $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&C&HPlease treat this document as confidential!'); $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N'); $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $objPHPExcel->getActiveSheet()->setTitle('Printing'); $objPHPExcel->setActiveSheetIndex(0); $callStartTime = microtime(true); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('timesheet/timesheet' . date('dmY') . '.xlsx'); }
public static function usersReport() { $conn = new Connect(); $query = 'SELECT * FROM ' . self::DB_TBL_USUARIOS; $consult = $conn->prepare($query); $consult->execute(); if ($consult->rowCount() > 0) { header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="Reporte de usuarios.xlsx"'); header('Cache-Control: max-age=0'); $reportName = "Reporte de usuarios"; $reportNameTitles = array("Id", "Cédula", "Nombres", "Apellidos", "Email", "Teléfono", "Extensión", "Usuario", "Contrasena", "Rol"); $styleColumnsTitle = array('font' => array('name' => 'Arial', 'bold' => true)); $generarReporteXLSX = new PHPExcel(); $generarReporteXLSX->getProperties()->setCreator("VideoConferencias UTPL")->setLastModifiedBy("VideoConferencias UTPL")->setTitle("Reporte de usuarios")->setSubject("Reporte de usuarios")->setDescription("Reporte de usuarios")->setKeywords("Reporte de usuarios")->setCategory("Reportes"); $generarReporteXLSX->setActiveSheetIndex(0)->mergeCells('A1:J1'); $generarReporteXLSX->setActiveSheetIndex(0)->setCellValue('A1', $reportName)->setCellValue('A3', $reportNameTitles[0])->setCellValue('B3', $reportNameTitles[1])->setCellValue('C3', $reportNameTitles[2])->setCellValue('D3', $reportNameTitles[3])->setCellValue('E3', $reportNameTitles[4])->setCellValue('F3', $reportNameTitles[5])->setCellValue('G3', $reportNameTitles[6])->setCellValue('H3', $reportNameTitles[7])->setCellValue('I3', $reportNameTitles[8])->setCellValue('J3', $reportNameTitles[9]); $i = 4; while ($row = $consult->fetch()) { $generarReporteXLSX->setActiveSheetIndex(0)->setCellValue('A' . $i, $row['id'])->setCellValue('B' . $i, $row['cedula'])->setCellValue('C' . $i, $row['nombres'])->setCellValue('D' . $i, $row['apellidos'])->setCellValue('E' . $i, $row['email'])->setCellValue('F' . $i, $row['telefono'])->setCellValue('G' . $i, $row['telefono_ext'])->setCellValue('H' . $i, $row['usuario'])->setCellValue('I' . $i, $row['contrasena'])->setCellValue('J' . $i, $row['id_rol']); $i++; } $generarReporteXLSX->getActiveSheet()->getStyle('A3:J3')->applyFromArray($styleColumnsTitle); $generarReporteXLSX->getActiveSheet()->setTitle('Usuarios'); $generarReporteXLSX->setActiveSheetIndex(0); $generarReporteXLSX->getActiveSheet(0)->freezePaneByColumnAndRow(0, 4); $objWriter = PHPExcel_IOFactory::createWriter($generarReporteXLSX, 'Excel2007'); $objWriter->save('php://output'); exit; } }
/** * Genera excel con los datos de la tabla */ public function excel() { $this->_setDataTable(); $this->_addPlugin(); $letras = 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"); $this->load->lib("Vendor/PHPExcel", false); $this->_objPHPExcel = new PHPExcel(); $this->_objPHPExcel->getProperties()->setCreator("Sumanet 3.0")->setLastModifiedBy("Sumanet 3.0")->setTitle("Exportación de expedientes")->setSubject("Sumanet")->setDescription($this->_title)->setKeywords("office 2007 openxml php sumanet")->setCategory("Sumanet"); $i = 0; foreach ($this->_data["columns"] as $columna) { if (count($columna) > 0) { $this->_objPHPExcel->setActiveSheetIndex(0)->setCellValue($letras[$i] . '1', strip_tags($columna["column_name"])); $i++; } } $this->_filasExcel($letras); $this->_objPHPExcel->getActiveSheet()->setTitle($this->_title); $this->_objPHPExcel->setActiveSheetIndex(0); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $this->_title . '.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($this->_objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; }
public function download() { if ($this->session->userdata('logged_in')) { //Cek User Branch $user_branch = $this->session->userdata('user_branch'); $branch_name = str_replace(' ', '', $this->session->userdata('user_branch_name')); if ($user_branch == 0) { $branch_name = "Pusat"; } //load our new PHPExcel library $this->load->library('excel'); $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("Amartha MIS"); $objPHPExcel->getProperties()->setLastModifiedBy("Amartha MIS"); $objPHPExcel->getProperties()->setTitle("REGPYD"); $objPHPExcel->getProperties()->setSubject("REGPYD"); $objPHPExcel->getProperties()->setDescription("REGPYD"); $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setTitle('REGPYD'); //TITLE $objPHPExcel->getActiveSheet()->setCellValue("A1", "Amartha Microfinance"); $objPHPExcel->getActiveSheet()->setCellValue("A2", "REGPYD Cabang {$branch_name}"); $objPHPExcel->getActiveSheet()->mergeCells("A1:D1"); $objPHPExcel->getActiveSheet()->getStyle("A1")->applyFromArray(array("font" => array("bold" => true, 'size' => 16))); $objPHPExcel->getActiveSheet()->getStyle("A2")->applyFromArray(array("font" => array("bold" => true))); //TOP ROW $objPHPExcel->getActiveSheet()->getStyle("A4:O4")->applyFromArray(array("font" => array("bold" => true))); $objPHPExcel->getActiveSheet()->setCellValue("A4", "NO"); $objPHPExcel->getActiveSheet()->setCellValue("B4", "NOMOR REKENING"); $objPHPExcel->getActiveSheet()->setCellValue("C4", "NAMA"); $objPHPExcel->getActiveSheet()->setCellValue("D4", "MAJELIS"); $objPHPExcel->getActiveSheet()->setCellValue("E4", "CABANG"); $objPHPExcel->getActiveSheet()->setCellValue("F4", "PLAFOND"); $objPHPExcel->getActiveSheet()->setCellValue("G4", "PROFIT"); $objPHPExcel->getActiveSheet()->setCellValue("H4", "TGL PENCAIRAN"); $objPHPExcel->getActiveSheet()->setCellValue("I4", "TGL JATUH TEMPO"); $objPHPExcel->getActiveSheet()->setCellValue("J4", "ANGSURAN KE"); $objPHPExcel->getActiveSheet()->setCellValue("K4", "AKAD"); $objPHPExcel->getActiveSheet()->setCellValue("L4", "TAB WAJIB"); $objPHPExcel->getActiveSheet()->setCellValue("M4", "TAB SUKARELA"); $objPHPExcel->getActiveSheet()->setCellValue("N4", "SEKTOR PEMBIAYAAN"); $objPHPExcel->getActiveSheet()->setCellValue("O4", "TUJUAN PEMBIAYAAN"); $no = 5; //EXPORT $filename = "REGPYD_" . $branch_name . "_" . time() . '.xls'; //save our workbook as this file name header('Content-Type: application/vnd.ms-excel'); //mime type header('Content-Disposition: attachment;filename="' . $filename . '"'); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); //redirect('accounting/jurnal', 'refresh'); } else { //If no session, redirect to login page redirect('login', 'refresh'); } }
public static function renderData(array $itemsIterator, array $fields, $filename) { $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("PrintWeek"); $objPHPExcel->getProperties()->setTitle("Report"); $objPHPExcel->getProperties()->setSubject("Report"); $objPHPExcel->getProperties()->setDescription("Report"); $objPHPExcel->setActiveSheetIndex(0); /** * Выводим строку названий столбцов */ $col = 0; foreach ($fields as $name) { $objPHPExcel->getActiveSheet()->SetCellValue(self::getCellCoordinate($col++, 1), $name); } /** * Основной вывод информации */ $row = 2; foreach ($itemsIterator as $item) { $col = 0; foreach ($fields as $name => $title) { $objPHPExcel->getActiveSheet()->SetCellValue(self::getCellCoordinate($col++, $row), $item->{$name}); } $row++; } $objPHPExcel->getActiveSheet()->setTitle('Report'); $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $objWriter->save($filename); }
function arrayToExcel($objPHPExcel = null, $rows, $writeArrayKeysAsHeader = false, $rowStartWrite = 1, $setActiveSheetTo = 0, $sheetName = null) { include_once 'sites/all/libraries/PHPExcel/PHPExcel/Writer/Excel2007.php'; // Create an object/instance for the output spreadsheet if (is_null($objPHPExcel)) { $objPHPExcel = new PHPExcel(); } myExcel_setActiveRow($rowStartWrite); // Create new sheet is needed $sheetOk = true; do { try { $objPHPExcel->setActiveSheetIndex($setActiveSheetTo); $sheetOk = true; } catch (Exception $e) { $sheetOk = false; $objPHPExcel->createSheet(); } } while ($sheetOk === false); if (is_null($sheetName)) { $objPHPExcel->getActiveSheet()->setTitle("Sheet {$setActiveSheetTo}"); } else { $objPHPExcel->getActiveSheet()->setTitle($sheetName); } // Set basic properties to output spreadsheet $objPHPExcel->getProperties()->setCreator("Business USA"); $objPHPExcel->getProperties()->setLastModifiedBy("Business USA"); $objPHPExcel->getProperties()->setTitle("Business USA"); $objPHPExcel->getProperties()->setSubject("Business USA"); $objPHPExcel->getProperties()->setDescription("Business USA"); // Debug if (strpos(request_uri(), '-DEBUG-NOEXCELWRITE-REPORTWRITE-') !== false) { ob_end_clean(); } // Write headders if ($writeArrayKeysAsHeader === true) { $headders = array(); foreach ($rows[0] as $key => $cell) { $headders[] = $key; } myExcel_WriteValuesToActiveRow($objPHPExcel, $headders, true); // Set the next row as "active" so the next time myExcel_WriteValuesToActiveRow() is called it will write to the next myExcel_setActiveRow(myExcel_getActiveRow() + 1); } // Write rows foreach ($rows as $row) { // Add a row into the spreadsheet myExcel_WriteValuesToActiveRow($objPHPExcel, $row); // Set the next row as "active" so the next time myExcel_WriteValuesToActiveRow() is called it will write to the next myExcel_setActiveRow(myExcel_getActiveRow() + 1); } // Debug if (strpos(request_uri(), '-DEBUG-NOEXCELWRITE-REPORTWRITE-') !== false) { flush(); exit; } myExcel_decideColumnWidths($objPHPExcel, $rows); return $objPHPExcel; }
/** * The MysqlExportXls function is used to export mysql query result into an .xls file. * @param MysqlExportXlsConnectOptions $connectOptions * @param MysqlExportXlsFileOptions $fileOptions * @return error message. Return empty string on success. */ function MysqlExportXls($connectOptions, $fileOptions, $query) { $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator($fileOptions->creator); $objPHPExcel->getProperties()->setLastModifiedBy($fileOptions->lastModifiedBy); $objPHPExcel->getProperties()->setTitle($fileOptions->title); $objPHPExcel->getProperties()->setSubject($fileOptions->subject); $objPHPExcel->getProperties()->setDescription($fileOptions->description); $objPHPExcel->setActiveSheetIndex(0); $activeSheet = $objPHPExcel->getActiveSheet(); $activeSheet->setTitle($fileOptions->title); // connect to mysql $link = mysql_connect($connectOptions->host, $connectOptions->userName, $connectOptions->password); if (!$link) { return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not connect: ' . mysql_error($link); } // use database $selectDb = mysql_select_db($connectOptions->useDatabase, $link); if (!$selectDb) { return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not select database' . mysql_error($link); } // PHPExcel use utf-8 encoding to save file only !!! $setCharset = mysql_set_charset("utf8", $link); if (!$setCharset) { return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not set charset' . mysql_error($link); } // execute sql $result = mysql_query($query, $link); if (!$result) { return __FILE__ . ":" . __FUNCTION__ . ':' . 'Query failed: ' . mysql_error($link); } // field names $columnIndex = 0; while ($field = mysql_fetch_field($result)) { $activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . '1', $field->name); ++$columnIndex; } $rowIndex = 2; // 1 based, the firset row is for field names. while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { $columnIndex = 0; foreach ($line as $key => $col_value) { $activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . $rowIndex, $col_value === null ? "" : $col_value, PHPExcel_Cell_DataType::TYPE_STRING2); ++$columnIndex; } ++$rowIndex; } // free mysql resource mysql_free_result($result); mysql_close($link); // write data into file $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $objWriter->setPreCalculateFormulas(FALSE); // Why true by default ? oh god damn it! $objWriter->save($fileOptions->name); return ""; }
public function ExcelOutputFile(DataToExcel $dataToExcel, $spreadsheetName) { $this->dataToExcel = $dataToExcel; $this->writer = new PHPExcel(); $name = explode("/", $spreadsheetName); $name = str_replace(".xls", "", $name[1]); $this->writer->getProperties()->setTitle($name); $this->spreadPath = $spreadsheetName; }
/** * 设置部分属性 * @param $objPHPExcel * * @return */ private function properties(\PHPExcel &$objPHPExcel, $title = '') { $objPHPExcel->getProperties()->setCreator("Joe Enozoomstudio"); // $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw"); $objPHPExcel->getProperties()->setTitle($title); // $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document"); // $objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes."); // $objPHPExcel->getProperties()->setKeywords("office 2007 openxml php"); // $objPHPExcel->getProperties()->setCategory("Test result file"); }
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; }
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 } }
/** * (non-PHPdoc) * @see \scipper\Datatransfer\TransferService::generateEmptyDocument() */ public function generateDocument(Map $map) { if (!class_exists("PHPExcel")) { throw new GenerationException("dependency 'PHPExcel' not found"); } $excel = new \PHPExcel(); $excel->removeSheetByIndex(0); $excel->getProperties()->setCreator($map->getCreator()); $excel->getProperties()->setTitle($map->getTitle()); $protectedStyle = new \PHPExcel_Style(); $protectedStyle->applyFromArray(array("fill" => array("type" => \PHPExcel_Style_Fill::FILL_SOLID, "color" => array("argb" => "55CCCCCC")), "borders" => array("bottom" => array("style" => \PHPExcel_Style_Border::BORDER_THIN), "right" => array("style" => \PHPExcel_Style_Border::BORDER_MEDIUM)))); $i = 0; foreach ($map->getSheets() as $sheet) { $active = $excel->addSheet(new \PHPExcel_Worksheet(NULL, $sheet->getTitle()), $i); $active->getProtection()->setSheet(true); $active->getStyle("A1:Z30")->getProtection()->setLocked(\PHPExcel_Style_Protection::PROTECTION_UNPROTECTED); foreach ($sheet->getCells() as $cell) { //Convert content to list format ist necessary if ($cell->getType() == "select") { $dataValidation = $active->getCell($cell->getCoord())->getDataValidation(); $dataValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST); $dataValidation->setAllowBlank(false); $dataValidation->setShowInputMessage(true); $dataValidation->setShowDropDown(true); $dataValidation->setFormula1($cell->getContent()); } else { $active->setCellValue($cell->getCoord(), $cell->getValue()); } //Add protection is necessary if ($cell->isProtected()) { $active->protectCells($cell->getCoord(), "123"); $active->setSharedStyle($protectedStyle, $cell->getCoord()); // } elseif(!$cell->isProtected() && $active->getProtection()->isProtectionEnabled()) { // $active->unprotectCells($cell->getCoord()); } $active->getColumnDimension($cell->getX())->setAutoSize(true); if (!$cell->isVisible()) { $active->getColumnDimension($cell->getX())->setVisible(false); } } $i++; } $excel->setActiveSheetIndex(0); $writer = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007'); $filename = $this->documentRoot . $excel->getProperties()->getTitle() . ".xlsx"; $writer->save($filename); return $filename; }
public function initialize($details) { $this->log("Loading PHPExcel"); /** PHPExcel_Writer_Excel2007 */ include 'PHPExcel/PHPExcel/Writer/Excel2007.php'; /** PHPExcel_Writer_Excel5 - for 2003 and below */ include 'PHPExcel/PHPExcel/Writer/Excel5.php'; /** PHPExcel_HTML Writer*/ include 'PHPExcel/PHPExcel/Writer/HTML.php'; /** PHPExcel_PDF Writer */ include 'PHPExcel/PHPExcel/IOFactory.php'; /*Initialize format array*/ $this->formats['xls'] = "Excel 2003"; //$this->formats['xlsx'] = "Excel 2007"; $this->formats['pdf'] = "Adobe PDF"; $this->details = $details; $this->log("Loading details:" . "<ul>" . "<li>Creator: " . $details['creator'] . "</li>" . "<li>Title: " . $details['title'] . "</li>" . "<li>Format: " . $details['format'] . "</li>" . "<li>Filename: " . $details['fileName'] . "</li>" . "</ul>"); // Create new PHPExcel object $objPHPExcel = new PHPExcel(); $this->log("Creating report - " . $details['title']); // Set properties $this->log("Setting properties of report"); $objPHPExcel->getProperties()->setCreator($details['creator'])->setLastModifiedBy($details['creator'])->setTitle($details['title'])->setSubject($details['title'] . " report")->setDescription($details['title'] . " report generated using PHPExcel and ADODB"); $this->fileName = $details['fileName']; $this->format = $details['format']; $this->setPHPExcelObj($objPHPExcel); }
public function createXls() { $this->load->database(); $query = $this->db->query("select * from T_USER"); if (!$query) { return false; } $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setTitle("title")->setDescription("description"); $objPHPExcel->setActiveSheetIndex(0); // Field names in the first row $fields = $query->list_fields(); $col = 0; foreach ($fields as $field) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field); $col++; } $row = 2; foreach ($query->result() as $data) { $col = 0; foreach ($fields as $field) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->{$field}); $col++; } $row++; } // Assign cell values // $objPHPExcel->setActiveSheetIndex(0); // $objPHPExcel->getActiveSheet()->setCellValue('A1', 'cell value here'); //$objPHPExcel->getActiveSheet()->setCellValue('A2', 'cell value here'); // Save it as an excel 2003 file $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save("nameoffile_2.xls"); }
function getExcel($fileName) { $date = date('Y-m-d h:i:s', time()); $fileName .= "_{$date}.xls"; $filename1 = "data.json"; //bug : if you use "/data.json", you cannnot open this file . you need to use "data.json" $json_string = file_get_contents($filename1); //$json2_string = icon_to_utf8($json_string); $obj = json_decode($json_string, true); $objPHPExcel = new PHPExcel(); $objProps = $objPHPExcel->getProperties(); $baseRow = 1; foreach ($obj as $r => $dataRow) { $row = $baseRow + $r; $objPHPExcel->getActiveSheet()->insertNewRowBefore($row, 1); $objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $dataRow['city']); $objPHPExcel->getActiveSheet()->setCellValue('B' . $row, $dataRow['api']); $objPHPExcel->getActiveSheet()->setCellValue('C' . $row, $dataRow['state']); $objPHPExcel->getActiveSheet()->setCellValue('D' . $row, $dataRow['pm25']); $objPHPExcel->getActiveSheet()->setCellValue('E' . $row, $dataRow['pm10']); $objPHPExcel->getActiveSheet()->setCellValue('F' . $row, $dataRow['co']); $objPHPExcel->getActiveSheet()->setCellValue('G' . $row, $dataRow['no2']); $objPHPExcel->getActiveSheet()->setCellValue('H' . $row, $dataRow['o3']); } $objPHPExcel->getActiveSheet()->setTitle('Simple'); $objPHPExcel->setActiveSheetIndex(0); //将输出重定向到一个客户端web浏览器(Excel2007) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header("Content-Disposition: attachment; filename=\"{$fileName}\""); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); //文件通过浏览器下载 exit; }
public function actionCsv($pars = 0) { if ($pars) { $model = Operation::find()->where("pars = :pars", [':pars' => (int) $pars])->orderBy("url asc")->all(); } else { $model = Operation::find()->orderBy("url asc")->all(); } include Yii::getAlias('@vendor/phpoffice/phpexcel/Classes/PHPExcel.php'); include Yii::getAlias('@vendor/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php'); $objPHPExcel = new \PHPExcel(); $objPHPExcel->getProperties()->setCreator("Php Shaman")->setLastModifiedBy("Php Shaman")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Data for yelp parsing")->setKeywords("office 2007 openxml php")->setCategory("Data for yelp parsing"); // Add some data $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'name')->setCellValue('B1', 'categories')->setCellValue('C1', 'phone')->setCellValue('D1', 'state')->setCellValue('E1', 'city')->setCellValue('F1', 'address')->setCellValue('G1', 'postal')->setCellValue('H1', 'site')->setCellValue('I1', 'description'); foreach ($model as $k => $o) { $cat = []; foreach ($o->categories as $c) { $cat[] = $c->name; } $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . ($k + 2), $o->name)->setCellValue('B' . ($k + 2), join(' | ', $cat))->setCellValue('C' . ($k + 2), $o->phone)->setCellValue('D' . ($k + 2), ParsSettings::getState($o->state))->setCellValue('E' . ($k + 2), $o->city)->setCellValue('F' . ($k + 2), $o->address)->setCellValue('G' . ($k + 2), $o->postal)->setCellValue('H' . ($k + 2), $o->site)->setCellValue('I' . ($k + 2), $o->description); } header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="pars.xls"'); header('Cache-Control: max-age=0'); header('Cache-Control: max-age=1'); header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); header('Cache-Control: cache, must-revalidate'); header('Pragma: public'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); }
/** * Format a recordset * * @param Garp_Model $model * @param array $rowset * @return string */ public function format(Garp_Model $model, array $rowset) { $phpexcel = new PHPExcel(); PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); // set metadata $props = $phpexcel->getProperties(); if (Garp_Auth::getInstance()->isLoggedIn()) { $userData = Garp_Auth::getInstance()->getUserData(); $bootstrap = Zend_Controller_Front::getInstance()->getParam('bootstrap'); if ($bootstrap) { $view = $bootstrap->getResource('view'); $userName = $view->fullName($userData); $props->setCreator($userName)->setLastModifiedBy($userName); } } $props->setTitle('Garp content export – ' . $model->getName()); if (count($rowset)) { $this->_addContent($phpexcel, $model, $rowset); } /** * Hm, PHPExcel seems to only be able to write to a file (instead of returning * an XLS binary string). Therefore, we save a temporary file, read its contents * and return those, after which we unlink the temp file. */ $tmpFileName = APPLICATION_PATH . '/data/logs/tmp.xls'; $writer = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5'); $writer->save($tmpFileName); $contents = file_get_contents($tmpFileName); unlink($tmpFileName); return $contents; }
/** * @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; }
public function __construct() { require_once './include/phpexcel/PHPExcel.php'; $objPHPExcel = new PHPExcel(); $this->ins = $objPHPExcel; $objPHPExcel->getProperties()->setCreator('Eddy')->setLastModifiedBy("Eddy"); }
function export($list, $params = array()) { if (PHP_SAPI == 'cli') { die('This example should only be run from a Web Browser'); } require_once IA_ROOT . '/framework/library/phpexcel/PHPExcel.php'; $excel = new PHPExcel(); $excel->getProperties()->setCreator("人人商城")->setLastModifiedBy("人人商城")->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("report file"); $sheet = $excel->setActiveSheetIndex(0); $rownum = 1; foreach ($params['columns'] as $key => $column) { $sheet->setCellValue($this->column($key, $rownum), $column['title']); if (!empty($column['width'])) { $sheet->getColumnDimension($this->column_str($key))->setWidth($column['width']); } } $rownum++; foreach ($list as $row) { $len = count($row); for ($i = 0; $i < $len; $i++) { $value = $row[$params['columns'][$i]['field']]; $sheet->setCellValue($this->column($i, $rownum), $value); } $rownum++; } $excel->getActiveSheet()->setTitle($params['title']); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $params['title'] . '-' . date('Y-m-d H:i', time()) . '.xlsx"'); header('Cache-Control: max-age=0'); $writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007'); $writer->save('php://output'); exit; }
/** * Write relationships to XML format * * @param PHPExcel $pPHPExcel * @return string XML Output * @throws PHPExcel_Writer_Exception */ public function writeRelationships(PHPExcel $pPHPExcel = null) { // Create XML writer $objWriter = null; if ($this->getParentWriter()->getUseDiskCaching()) { $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory()); } else { $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY); } // XML header $objWriter->startDocument('1.0', 'UTF-8', 'yes'); // Relationships $objWriter->startElement('Relationships'); $objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/package/2006/relationships'); $customPropertyList = $pPHPExcel->getProperties()->getCustomProperties(); if (!empty($customPropertyList)) { // Relationship docProps/app.xml $this->_writeRelationship($objWriter, 4, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties', 'docProps/custom.xml'); } // Relationship docProps/app.xml $this->_writeRelationship($objWriter, 3, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties', 'docProps/app.xml'); // Relationship docProps/core.xml $this->_writeRelationship($objWriter, 2, 'http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties', 'docProps/core.xml'); // Relationship xl/workbook.xml $this->_writeRelationship($objWriter, 1, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument', 'xl/workbook.xml'); // a custom UI in workbook ? if ($pPHPExcel->hasRibbon()) { $this->_writeRelationShip($objWriter, 5, 'http://schemas.microsoft.com/office/2006/relationships/ui/extensibility', $pPHPExcel->getRibbonXMLData('target')); } $objWriter->endElement(); // Return return $objWriter->getData(); }
public static function export($author) { $sql = 'SELECT * FROM {{iusers}} WHERE author=' . $author . ' ORDER BY code'; $list = DB::getAll($sql); header('Content-Type: text/html; charset=utf-8'); header('P3P: CP="NOI ADM DEV PSAi COM NAV OUR OTRo STP IND DEM"'); header('Cache-Control: no-store, no-cache, must-revalidate'); header('Cache-Control: post-check=0, pre-check=0', FALSE); header('Pragma: no-cache'); header('Content-transfer-encoding: binary'); header('Content-Disposition: attachment; filename=export.xls'); header('Content-Type: application/octet-stream'); $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("")->setLastModifiedBy("")->setTitle("Office 2007 XLSX")->setSubject("Office 2007 XLSX")->setDescription("Document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("GBROS file"); $sheet = $objPHPExcel->setActiveSheetIndex(0); $sheet->setCellValue('A1', 'ID'); $sheet->setCellValue('B1', 'Имя'); $sheet->setCellValue('C1', 'Баланс, руб.'); $sheet->getColumnDimension('A')->setAutoSize(true); $sheet->getColumnDimension('B')->setAutoSize(true); $sheet->getColumnDimension('C')->setAutoSize(true); foreach ($list as $i => $item) { $sheet->setCellValue('A' . ($i + 2), $item['code']); $sheet->setCellValue('B' . ($i + 2), $item['name']); $sheet->setCellValue('C' . ($i + 2), $item['balance']); $objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 1))->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getStyle('B' . ($i + 1))->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getStyle('C' . ($i + 1))->getAlignment()->setWrapText(true); } $objPHPExcel->getActiveSheet()->setTitle('Лист 1'); $objPHPExcel->setActiveSheetIndex(0); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); }
function exportToExcel($itemlessRecords) { //PHPEXCEL // Create new PHPExcel object $objPHPExcel = new PHPExcel(); // Set properties $objPHPExcel->getProperties()->setCreator("DCL")->setLastModifiedBy("DCL")->setTitle("Office 2007 XLSX Document")->setSubject("Office 2007 XLSX Document")->setDescription("Office 2007 XLSX, generated using PHP.")->setKeywords("office 2007 openxml php")->setCategory("eContent Wish List Report"); // Add some data $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'eContent Wish List Report')->setCellValue('A3', 'ID')->setCellValue('B3', 'Title')->setCellValue('C3', 'Author')->setCellValue('D3', 'ISBN')->setCellValue('E3', 'ILS Id')->setCellValue('F3', 'Source')->setCellValue('G3', 'Wishlist Size'); $a = 4; //Loop Through The Report Data foreach ($itemlessRecords as $itemlessRecord) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $a, $itemlessRecord->id)->setCellValue('B' . $a, $itemlessRecord->title)->setCellValue('C' . $a, $itemlessRecord->author)->setCellValue('D' . $a, $itemlessRecord->isbn)->setCellValue('E' . $a, $itemlessRecord->ilsId)->setCellValue('F' . $a, $itemlessRecord->source)->setCellValue('G' . $a, $itemlessRecord->numWishList); $a++; } $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')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true); // Rename sheet $objPHPExcel->getActiveSheet()->setTitle('Wish List'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client's web browser (Excel5) header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename=EContentWishListReport.xls'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; }
public function generate(array $fields, array $data, $fileName = 'excelDbDump') { $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setTitle("export")->setDescription("none"); $objPHPExcel->setActiveSheetIndex(0); // Field names in the first row $col = 0; foreach ($fields as $field) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field); $col++; } // Fetching the table data $row = 2; foreach ($data as $data) { $col = 0; foreach ($fields as $field) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->{$field}); $col++; } $row++; } $objPHPExcel->setActiveSheetIndex(0); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); // Sending headers to force the user to download the file header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="' . $fileName . '_' . date('d-m-y') . '.xls"'); header('Cache-Control: max-age=0'); $objWriter->save('php://output'); }