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');
}
