function exportExcel($expTitle, $expCellName, $expTableData)
{
$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);
//文件名称
$fileName = $_SESSION['account'] . date('_YmdHis');
//or $xlsTitle 文件名称可根据自己情况设定
$cellNum = count($expCellName);
$dataNum = count($expTableData);
vendor("PHPExcel.PHPExcel");
$objPHPExcel = new \PHPExcel();
$cellName = 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', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
$objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');
//合并单元格
for ($i = 0; $i < $cellNum; $i++) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
}
for ($i = 0; $i < $dataNum; $i++) {
for ($j = 0; $j < $cellNum; $j++) {
$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), $expTableData[$i][$expCellName[$j][0]]);
}
}
header('pragma:public');
header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
header("Content-Disposition:attachment;filename={$fileName}.xls");
//attachment新窗口打印inline本窗口打印
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
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 actionExportExcel()
{
$model = new Reporte();
$evento_id = $_POST['Report']['evento_id'];
$sector_id = $_POST['Report']['sector_id'];
$subsector_id = $_POST['Report']['subsector_id'];
$rama_actividad_id = $_POST['Report']['rama_actividad_id'];
$actividad_id = $_POST['Report']['actividad_id'];
$dataReporte = $model->search($evento_id, $sector_id, $subsector_id, $rama_actividad_id, $actividad_id);
$objExcel = new PHPExcel();
$objExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Nombre')->setCellValue('B1', 'Cédula')->setCellValue('C1', 'Celular')->setCellValue('D1', 'Teléfono')->setCellValue('E1', 'E-mail')->setCellValue('F1', 'Dirección')->setCellValue('G1', 'Sector')->setCellValue('H1', 'Subsector')->setCellValue('I1', 'Rama de Actividad')->setCellValue('J1', 'Actividad');
$id = 2;
foreach ($dataReporte as $Reporte) {
$objExcel->setActiveSheetIndex(0)->setCellValue('A' . $id, $Reporte['nombre_completo'])->setCellValue('B' . $id, $Reporte['cedula'])->setCellValue('C' . $id, $Reporte['celular'])->setCellValue('D' . $id, $Reporte['telefono'])->setCellValue('E' . $id, $Reporte['email'])->setCellValue('F' . $id, $Reporte['direccion'])->setCellValue('G' . $id, $Reporte['sector'])->setCellValue('H' . $id, $Reporte['subsector'])->setCellValue('I' . $id, $Reporte['rama_actividad'])->setCellValue('J' . $id, $Reporte['actividad']);
$id++;
}
for ($i = 'A'; $i <= 'O'; $i++) {
$objExcel->setActiveSheetIndex(0)->getColumnDimension($i)->setAutoSize(TRUE);
}
$objExcel->getActiveSheet()->setTitle('ReporteParticipantes');
//// Se activa la hoja para que sea la que se muestre cuando el archivo se abre
$objExcel->setActiveSheetIndex(0);
//
//// Inmovilizar paneles
$objExcel->getActiveSheet(0)->freezePane('A4');
$objExcel->getActiveSheet(0)->freezePaneByColumnAndRow(1, 2);
// Se manda el archivo al navegador web, con el nombre que se indica, en formato 2007
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="ReporteParticipantes.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
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;
}
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 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;
}
public static function exportXlsx($data, $keys)
{
// Create new PHPExcel object
$objPHPExcel = new \PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Roadiz CMS")->setLastModifiedBy("Roadiz CMS")->setCategory("");
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = ['memoryCacheSize' => '8MB'];
\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objPHPExcel->setActiveSheetIndex(0);
foreach ($keys as $key => $value) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($key, 1, $value);
}
foreach ($data as $key => $answer) {
foreach ($answer as $k => $value) {
$columnAlpha = \PHPExcel_Cell::stringFromColumnIndex($k);
if ($value instanceof \DateTime) {
$value = \PHPExcel_Shared_Date::PHPToExcel($value);
$objPHPExcel->getActiveSheet()->getStyle($columnAlpha . (2 + $key))->getNumberFormat()->setFormatCode('dd.mm.yyyy hh:MM:ss');
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($k, 2 + $key, $value);
}
}
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_start();
$objWriter->save('php://output');
$return = ob_get_clean();
return $return;
}
public function exportDaytime()
{
$app = JFactory::getApplication();
$modelCalendar = new EstivoleModelCalendar();
$modelDaytime = new EstivoleModelDaytime();
$modelServices = new EstivoleModelServices();
$daytimeid = $app->input->get('daytime_id');
$daytime = $modelDaytime->getDaytime($daytimeid);
$calendar = $modelCalendar->getItem($daytime->calendar_id);
$this->services = $modelServices->getServicesByDaytime($daytime->daytime_day);
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Estivale Open Air")->setLastModifiedBy("Estivole")->setTitle("Export Estivole")->setSubject("Export des tranches horaires Estivole");
// Add some data
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A1", "Plan de travail Estivale Open Air - Calendrier " . $calendar->name);
// // Miscellaneous glyphs, UTF-8
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A2", date('d-m-Y', strtotime($daytime->daytime_day)));
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle("Export");
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
$cellCounter = 4;
// Add data
for ($i = 0; $i < count($this->services); $i++) {
$objPHPExcel->getActiveSheet()->setCellValue("A" . $cellCounter, $this->services[$i]->service_name);
$this->daytimes = $modelDaytime->listItemsForExport($this->services[$i]->service_id);
foreach ($this->daytimes as $daytime) {
$userId = $daytime->user_id;
$userProfileEstivole = EstivoleHelpersUser::getProfileEstivole($userId);
$userProfile = JUserHelper::getProfile($userId);
$user = JFactory::getUser($userId);
$objPHPExcel->getActiveSheet()->setCellValue("A" . ($cellCounter + 1), $userProfileEstivole->profilestivole['lastname'] . ' ' . $userProfileEstivole->profilestivole['firstname'])->setCellValueExplicit("B" . ($cellCounter + 1), $userProfile->profile['phone'], PHPExcel_Cell_DataType::TYPE_STRING)->setCellValue("C" . ($cellCounter + 1), $user->email)->setCellValue("D" . ($cellCounter + 1), date('H:i', strtotime($daytime->daytime_hour_start)))->setCellValue("E" . ($cellCounter + 1), date('H:i', strtotime($daytime->daytime_hour_end)));
$cellCounter++;
}
$cellCounter = $cellCounter + 2;
}
// Redirect output to a client’s web browser (Excel2007)
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment;filename=\"01simple.xlsx\"");
header("Cache-Control: max-age=0");
// If you"re serving to IE 9, then the following may be needed
header("Cache-Control: max-age=1");
// If you"re serving to IE over SSL, then the following may be needed
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
// Date in the past
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
// always modified
header("Cache-Control: cache, must-revalidate");
// HTTP/1.1
header("Pragma: public");
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
$objWriter->save("php://output");
exit;
}
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;
}
}
/**
* 导出EXCEL表格
*/
public function exportExcel()
{
/** Include PHPExcel */
require_once './core/PHPExcel/PHPExcel.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("chen")->setLastModifiedBy("chen")->setTitle("会员信息列表")->setSubject("会员信息列表")->setDescription("会员信息列表");
$memberList = D('Member')->where('user_id=' . $_SESSION['uid'])->field('id,wechat_id,name,mobile,email,date_reg,date_login')->order('date_login desc')->select();
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '会员ID')->setCellValue('B1', '微信号')->setCellValue('C1', '昵称')->setCellValue('D1', '手机号码')->setCellValue('E1', '邮箱')->setCellValue('F1', '注册时间')->setCellValue('G1', '上次访问时间');
foreach ($memberList as $k => $v) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . ($k + 2), $v['id'])->setCellValue('B' . ($k + 2), $v['wechat_id'])->setCellValue('C' . ($k + 2), $v['name'])->setCellValue('D' . ($k + 2), $v['mobile'])->setCellValue('E' . ($k + 2), $v['email'])->setCellValue('F' . ($k + 2), date('Y-m-d H:i', $v['date_reg']))->setCellValue('G' . ($k + 2), date('Y-m-d H:i', $v['date_login']));
}
// Rename worksheet
$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="会员列表' . date('md') . '.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
header('Pragma: public');
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
/**
* @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 generateXLS($data)
{
global $mysqli, $ano;
//include "connect.php";
require_once dirname(__FILE__) . '/PHPExcel/Classes/PHPExcel/IOFactory.php';
$filename = 'fluxocaixa_' . $ano . '.xlsx';
//$filename = md5(time()). '.xlsx';
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment; filename=\"{$filename}\"");
header("Cache-Control: max-age=0");
$excelObj = new PHPExcel();
$excelObj->getProperties()->setCreator("Gestor Financeiro Web")->setLastModifiedBy("Vinícius Hacebe")->setTitle("Fluxo de Caixa")->setSubject("Fluxo de Caixa")->setDescription("Dados exportados do fluxo de caixa")->setKeywords("fluxo de caixa")->setCategory("fluxo de caixa");
$sheet = $excelObj->setActiveSheetIndex(0);
$sheet->setCellValue('A1', 'Classificação')->setCellValue('B1', 'Descrição')->setCellValue('C1', 'Saldo')->setCellValue('D1', 'Jan/' . $ano)->setCellValue('E1', 'Fev/' . $ano)->setCellValue('F1', 'Mar/' . $ano)->setCellValue('G1', 'Abr/' . $ano)->setCellValue('H1', 'Mai/' . $ano)->setCellValue('I1', 'Jun/' . $ano)->setCellValue('J1', 'Jul/' . $ano)->setCellValue('K1', 'Ago/' . $ano)->setCellValue('L1', 'Set/' . $ano)->setCellValue('M1', 'Out/' . $ano)->setCellValue('N1', 'Nov/' . $ano)->setCellValue('O1', 'Dez/' . $ano);
$recordIndex = 2;
for ($i = 0; $i < sizeof($data); $i++) {
writeToSheet($recordIndex, $data, $i, $sheet);
}
foreach (range('A', 'O') as $columnID) {
$excelObj->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}
$excelObj->getActiveSheet()->getStyle('A1:A2560')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objWriter = PHPExcel_IOFactory::createWriter($excelObj, 'Excel2007');
//$objWriter->save($filename);
$objWriter->save('php://output');
//print_r($data);
}
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);
}
public function __construct()
{
parent::__construct();
$this->objPHPExcel = new PHPExcel();
$this->workSheet = $this->objPHPExcel->getActiveSheet();
$this->debug();
}
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");
}
/**
* @return ExportAbstract|\Symfony\Component\HttpFoundation\Response
*/
public function setData()
{
$PHPExcel = new \PHPExcel();
$dataGrid = $this->getDataGrid();
$rowNum = 1;
$colNum = 0;
foreach ($dataGrid->getColumns() as $header) {
$label = isset($this->translator) ? $this->translator->trans($header->getLabel(), array(), $header->getAttribute('translation_domain')) : $header->getLabel();
$PHPExcel->getActiveSheet()->setCellValueByColumnAndRow($colNum, $rowNum, $label);
$colNum++;
}
$rowNum++;
foreach ($dataGrid as $row) {
$colNum = 0;
foreach ($row as $cell) {
$PHPExcel->getActiveSheet()->setCellValueByColumnAndRow($colNum, $rowNum, (string) $cell->getValue());
$colNum++;
}
$rowNum++;
}
$writer = $this->getWriter($PHPExcel);
ob_start();
$writer->save("php://output");
$this->data = ob_get_clean();
return $this->update();
}
public static function downloadExcelFileByArray($data, $fileName = '')
{
self::prepare();
if (!$fileName) {
$fileName = 'xls-download-' . date('Y-m-d-H-i-s') . '.xls';
}
$objPHPExcel = new \PHPExcel();
$objPHPExcel->getActiveSheet()->fromArray($data);
$objPHPExcel->getActiveSheet()->freezePane('A2');
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $fileName . '"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
header('Pragma: public');
// HTTP/1.0
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
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;
}
/**
* 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 exportOrderAction()
{
$orderIds = $this->getRequest()->getPost('orderIds');
$orderIds = $this->_genOrdersIdList($orderIds);
$orders = Mage::getModel('sales/order')->getCollection()->addAttributeToFilter('increment_id', array('in' => $orderIds))->getItems();
// export Excel xml
// Include PHPExcel
require_once Mage::getBaseDir('lib') . "/PHPExcel/Classes/PHPExcel.php";
//Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$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");
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
// Add some data
$delta = 6;
$start = 1;
foreach ($orders as $_order) {
$address = $_order->getShippingAddress();
$items = $_order->getAllItems();
//var_dump($items[0]->getName());
//var_dump($items[0]->getSku());
//var_dump($items[0]->getQtyOrdered());
foreach ($items as $_item) {
$end = $start + $delta - 1;
$objPHPExcel->getActiveSheet()->mergeCells('A' . $start . ':A' . $end . '')->setCellValue('A' . $start . '', $_order->getIncrementId())->setCellValue('B' . $start . '', 'size: ' . $_item->getProductOptions()['options'][0]['value'])->mergeCells('B' . (string) ($start + 1) . ':B' . $end . '')->setCellValue('C' . $start . '', $address->getData('firstname') . ' ' . $address->getData('lastname'))->setCellValue('C' . (string) ($start + 1) . '', split(PHP_EOL, $address->getData('street'))[0])->setCellValue('C' . (string) ($start + 2) . '', split(PHP_EOL, $address->getData('street'))[1])->setCellValue('C' . (string) ($start + 3) . '', $address->getData('city') . ', ' . $address->getData('region') . ' ' . $address->getData('postcode'))->setCellValue('C' . (string) ($start + 4) . '', Mage::app()->getLocale()->getCountryTranslation($address->getData('country_id')))->setCellValue('C' . (string) ($start + 5) . '', $address->getData('telephone'))->setCellValue('D' . $start . '', $_item->getName())->setCellValue('D' . (string) ($start + 1) . '', 'Qty: ' . $_item->getQtyOrdered())->setCellValue('D' . (string) ($start + 2) . '', 'SKU: ' . $_item->getSku())->getStyle('C' . (string) ($start + 5))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
// add picture
$objDrawing = new PHPExcel_Worksheet_Drawing();
$imageUrl = $_item->getProduct()->getImageUrl();
$imageUrl = str_replace(Mage::getBaseUrl('media'), Mage::getBaseDir('media') . '/', $imageUrl);
$objDrawing->setPath($imageUrl);
$objDrawing->setCoordinates('B' . (string) ($start + 1));
$objDrawing->setHeight(80);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$start = $start + $delta;
}
}
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Orders');
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="orders-' . date('Y_m_d_H_i_s') . '.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
header('Pragma: public');
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
}
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;
}
/**
* @inheritdoc
*/
public function stream_open($path, $mode, $options, &$opened_path)
{
\PHPExcel_Settings::setCacheStorageMethod(\PHPExcel_CachedObjectStorageFactory::cache_to_sqlite3);
$this->objPHPExcel = new \PHPExcel();
$this->sheet = $this->objPHPExcel->getActiveSheet();
$this->offset = 1;
return parent::stream_open($path, $mode, $options, $opened_path);
}
function EWD_UFAQ_Export_To_Excel()
{
include_once '../wp-content/plugins/ultimate-faqs/PHPExcel/Classes/PHPExcel.php';
// Instantiate a new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set the active Excel worksheet to sheet 0
$objPHPExcel->setActiveSheetIndex(0);
// Print out the regular order field labels
$objPHPExcel->getActiveSheet()->setCellValue("A1", "Question");
$objPHPExcel->getActiveSheet()->setCellValue("B1", "Answer");
$objPHPExcel->getActiveSheet()->setCellValue("C1", "Categories");
$objPHPExcel->getActiveSheet()->setCellValue("D1", "Tags");
//start while loop to get data
$rowCount = 2;
$params = array('posts_per_page' => -1, 'post_type' => 'ufaq');
$Posts = get_posts($params);
foreach ($Posts as $Post) {
$Categories = get_the_terms($Post->ID, "ufaq-category");
if (is_array($Categories)) {
foreach ($Categories as $Category) {
$Category_String .= $Category->name . ",";
}
$Category_String = substr($Category_String, 0, -1);
} else {
$Category_String = "";
}
$Tags = get_the_terms($Post->ID, "ufaq-tag");
if (is_array($Tags)) {
foreach ($Tags as $Tag) {
$Tag_String .= $Tag->name . ",";
}
$Tag_String = substr($Tag_String, 0, -1);
} else {
$Tag_String = "";
}
$objPHPExcel->getActiveSheet()->setCellValue("A" . $rowCount, $Post->post_title);
$objPHPExcel->getActiveSheet()->setCellValue("B" . $rowCount, $Post->post_content);
$objPHPExcel->getActiveSheet()->setCellValue("C" . $rowCount, $Category_String);
$objPHPExcel->getActiveSheet()->setCellValue("D" . $rowCount, $Tag_String);
$rowCount++;
unset($Category_String);
unset($Tag_String);
}
// Redirect output to a client’s web browser (Excel5)
if ($Format_Type == "CSV") {
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="FAQ_Export.csv"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$objWriter->save('php://output');
} else {
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="FAQ_Export.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
}
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]);
}
}
// we'll need to check if the compression option is turned on. And then execute this code - unullmass
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//get the name of the save file
$filename = $this->getDownloadFilename($generator);
$filepath = "./cache/" . $filename;
//save the excel data to that file
$objWriter->save($filepath);
if (!$generator->isPromptDownloadZipped()) {
// redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
readfile($filepath);
@unlink($filepath);
} else {
// create archive and send back
$zippath = $filepath . ".zip";
$zip = new ZipArchive();
$zipfile = $zip->open($zippath, ZipArchive::CREATE);
if ($zipfile) {
if ($zip->addFile($filepath, $filename)) {
//we've got our zip file now we may set the response header
$zip->close();
header("Cache-Control: private, no-cache, must-revalidate");
header("Content-type: application/zip");
header('Content-Disposition: attachment; filename="' . $filename . '.zip"');
readfile($zippath);
unlink($zippath);
unlink($filename);
exit;
}
}
}
}
/**
* @return string
*/
public function compile()
{
$this->writeHeaders($this->phpExcel->getActiveSheet());
$this->writeData($this->phpExcel->getActiveSheet());
$writer = \PHPExcel_IOFactory::createWriter($this->phpExcel, 'Excel2007');
@FileHelper::createDirectory(dirname($this->filename));
$writer->save($this->filename);
return $this->filename;
}
protected function prepare(\PHPExcel_Worksheet $templateSheet)
{
$this->output = new \PHPExcel();
$outputSheet = $this->output->getActiveSheet();
$outputSheet->setTitle('Report');
$this->templateSheet = $this->output->addExternalSheet($templateSheet);
foreach ($this->templateSheet->getColumnDimensions() as $col => $columnDimension) {
$outputSheet->getColumnDimension($col)->setWidth($columnDimension->getWidth());
}
}
public function exportMembersShirts()
{
$app = JFactory::getApplication();
$service_id = $app->input->get('service_id', null);
$model = new EstivoleModelMembers();
$modelDaytime = new EstivoleModelDaytime();
$modelService = new EstivoleModelService();
$service = $modelService->getItem($service_id);
$service_name = $service->service_name != '' ? $service->service_name : 'Tous les membres';
$this->members = $model->getTotalItemsForExport();
for ($i = 0; $i < count($this->members); $i++) {
$this->members[$i]->member_daytimes = $modelDaytime->getMemberDaytimes($this->members[$i]->member_id, $this->calendars[0]->calendar_id);
}
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Estivale Open Air")->setLastModifiedBy("Estivole")->setTitle("Export Estivole")->setSubject("Export des bénévoles Estivole");
// Add some data
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A1", "Plan de travail Estivale Open Air - Bénévoles");
// $this->state = $this->get('State');
// $this->filter_services = $this->state->get('filter.services_members');
$objPHPExcel->getActiveSheet()->setCellValue("A2", "Export membres \"" . $service_name . "\"");
// // Miscellaneous glyphs, UTF-8
$objPHPExcel->getActiveSheet()->setCellValue("A4", "Nom")->setCellValue("B4", "Email")->setCellValue("C4", "Téléphone")->setCellValue("D4", "Nbre t-shirts")->setCellValue("E4", "Taille t-shirts");
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle("Export");
$cellCounter = 5;
// Add data
for ($i = 0; $i < count($this->members); $i++) {
$userId = $this->members[$i]->user_id;
$userProfileEstivole = EstivoleHelpersUser::getProfileEstivole($userId);
$userProfile = JUserHelper::getProfile($userId);
$user = JFactory::getUser($userId);
$objPHPExcel->getActiveSheet()->setCellValue("A" . ($cellCounter + 1), $userProfileEstivole->profilestivole['lastname'] . ' ' . $userProfileEstivole->profilestivole['firstname'])->setCellValue("B" . ($cellCounter + 1), $user->email)->setCellValueExplicit("C" . ($cellCounter + 1), $userProfile->profile['phone'], PHPExcel_Cell_DataType::TYPE_STRING)->setCellValue("D" . ($cellCounter + 1), round(count($this->members[$i]->member_daytimes) / 2))->setCellValue("E" . ($cellCounter + 1), $userProfileEstivole->profilestivole['tshirtsize']);
$cellCounter++;
}
// Redirect output to a client’s web browser (Excel2007)
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment;filename=\"01simple.xlsx\"");
header("Cache-Control: max-age=0");
// If you"re serving to IE 9, then the following may be needed
header("Cache-Control: max-age=1");
// If you"re serving to IE over SSL, then the following may be needed
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
// Date in the past
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
// always modified
header("Cache-Control: cache, must-revalidate");
// HTTP/1.1
header("Pragma: public");
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
$objWriter->save("php://output");
exit;
}
public function actionTemplate()
{
Yii::import('ext.heart.excel.EHeartExcel', true);
EHeartExcel::init();
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'No')->setCellValue('B1', 'ModelType')->setCellValue('C1', 'Model')->setCellValue('D1', 'Controller')->setCellValue('E1', 'ModelParent')->setCellValue('F1', 'ControllerParent');
$modelPath = Yii::getPathOfAlias('application.models');
$files = scandir($modelPath);
$row = 2;
foreach ($files as $file) {
if (is_file($modelPath . '/' . $file) && CFileHelper::getExtension($file) === 'php' && !in_array($file, array('ContactForm.php', 'LoginForm.php', 'Admin.php', 'User.php'))) {
$file_arr = explode(".", $file);
$filename = $file_arr[0];
$objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $row - 1)->setCellValue('B' . $row, "1")->setCellValue('C' . $row, $filename)->setCellValue('D' . $row, 'test/' . $filename)->setCellValue('E' . $row, '-')->setCellValue('F' . $row, '-');
$row++;
}
}
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="models.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
/**
* Export
*
* @access public
*/
public function export()
{
$excel = new \PHPExcel();
$worksheet = $excel->getActiveSheet();
$row = 1;
$worksheet->setCellValueByColumnAndRow(0, $row, 'Product');
$worksheet->setCellValueByColumnAndRow(1, $row, 'price');
$worksheet->setCellValueByColumnAndRow(2, $row, 'amount');
$worksheet->setCellValueByColumnAndRow(3, $row, 'Total price');
$excel->getActiveSheet()->getStyle('A1:D1')->getFont()->setBold(true);
foreach ($this->get_purchase_order_items() as $purchase_order_item) {
$row++;
$worksheet->setCellValueByColumnAndRow(0, $row, $purchase_order_item->get_stock_object()->get_name());
$worksheet->setCellValueByColumnAndRow(1, $row, $purchase_order_item->price);
$worksheet->setCellValueByColumnAndRow(2, $row, $purchase_order_item->amount);
$worksheet->setCellValueByColumnAndRow(3, $row, $purchase_order_item->price * $purchase_order_item->amount);
}
$row++;
$worksheet->setCellValueByColumnAndRow(3, $row, $this->get_price());
$excel->getActiveSheet()->getStyle($row . ':' . $row)->getFont()->setBold(true);
$cellIterator = $worksheet->getRowIterator()->current()->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(true);
/** @var PHPExcel_Cell $cell */
foreach ($cellIterator as $cell) {
$worksheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
}
$writer = new \PHPExcel_Writer_Excel2007($excel);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="purchase_order_' . $this->id . '.xls"');
header('Cache-Control: max-age=0');
$writer->save('php://output');
exit;
}
public function 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');
}
