/**
* Выводит отчет
*/
public function render()
{
$this->objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $this->getFileName() . '.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
}
public function output()
{
// Create new PHPExcel object
$objPHPExcel = new \PHPExcel();
$objSheet = $objPHPExcel->setActiveSheetIndex(0);
$col = 0;
$row = 1;
if (isset($this->header)) {
foreach ($this->header as $v) {
$cell = \PHPExcel_Cell::stringFromColumnIndex($col) . $row;
$objSheet->setCellValue($cell, $v);
$col++;
}
$row++;
$col = 0;
}
foreach ($this->content as $rowValue) {
foreach ($rowValue as $_v) {
$cell = \PHPExcel_Cell::stringFromColumnIndex($col) . $row;
$objSheet->setCellValue($cell, $_v);
$col++;
}
$row++;
$col = 0;
}
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle($this->title);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
$this->browserExport($this->type, $this->filename);
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, $this->type);
$objWriter->save('php://output');
}
/**
* Recorre los datos y llena el excel
* @param array $data
* @param array $json_fields
*/
public function setData($data, $json_fields = array())
{
if (count($data) > 0) {
$j = 2;
foreach ($data as $row) {
$json_data = array();
if (count($json_fields) > 0) {
foreach ($json_fields as $json) {
$json_data = array_merge(Zend_Json::decode($row[$json]), $json_data);
}
}
$i = 0;
foreach ($this->_columnas as $columna => $valores) {
$ok = true;
if (isset($valir["permiso"]) && !$valir["permiso"]) {
$ok = false;
}
if ($ok) {
$this->_excel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($i, $j, trim($this->_procesaValor($valores, $row, $json_data)));
$i++;
}
}
$j++;
}
}
}
/**
* 导出任务excel
*/
public function export_purchase()
{
vendor('PHPExcel.PHPExcel');
$obj = new PHPExcel();
$allBranch = M('Branch')->field(array('id', 'name'))->order('id ASC')->select();
$obj->setActiveSheetIndex(0)->setCellValue('A1', '商品名');
foreach ($allBranch as $k => $per) {
$cell = sprintf("%c1", $k + 66);
$obj->setActiveSheetIndex(0)->setCellValue($cell, $per['name']);
}
$data = D('Purchase')->export();
foreach ($data as $k_1 => $v) {
$obj->setActiveSheetIndex(0)->setCellValue('A' . ($k_1 + 2), $v['name']);
foreach ($allBranch as $k => $per) {
$cell = sprintf("%c%d", $k + 66, $k_1 + 2);
$obj->setActiveSheetIndex(0)->setCellValue($cell, D('Purchase')->getPerBranchAmount($per['id'], $v['goods_id']));
}
}
$obj->getActiveSheet()->setTitle('采购任务');
$obj->setActiveSheetIndex(0);
// 下载
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment; filename=\"采购任务.xlsx\"");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
$objWriter->save('php://output');
}
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 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;
}
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;
}
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 function actionExcel()
{
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("K'iin Balam")->setLastModifiedBy("K'iin Balam")->setTitle("YiiExcel Test Document")->setSubject("YiiExcel Test Document")->setDescription("Test document for YiiExcel, generated using PHP classes.")->setKeywords("office PHPExcel php YiiExcel UPNFM")->setCategory("Test result file");
// Add some data
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Hello')->setCellValue('B2', 'world!')->setCellValue('C1', 'Hello')->setCellValue('D2', 'world!');
// Miscellaneous glyphs, UTF-8
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A4', 'Miscellaneous glyphs')->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('YiiExcel');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Save a xls file
$filename = 'YiiExcel';
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');
unset($this->objWriter);
unset($this->objWorksheet);
unset($this->objReader);
unset($this->objPHPExcel);
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');
}
public function doXML($file)
{
$order = Mage::getModel('sales/order')->load($file);
#get all items
$items = $order->getAllItems();
$itemcount = count($items);
$shippingDetails = $this->getOrderShippingInfo($order);
$billingDetails = $this->getOrderBillingInfo($order);
$orderNumber = $order->getRealOrderId();
$orderDate = $order->getCreatedAtDate();
$orderCustomerEmail = $order->getCustomerEmail();
$lines = $this->getOrderLineDetails($order);
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("WEBSITE")->setLastModifiedBy("WEBSITE.com")->setTitle("Enquiry from WEBSITE.com")->setSubject("Enquiry from WEBSITE.com")->setDescription("Enquiry from WEBSITE.com")->setKeywords("office WEBSITE php")->setCategory("WEBSITE enquiry");
// Add some data
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Customer Information')->setCellValue('A3', 'Name')->setCellValue('B3', $billingDetails['billing_name'])->setCellValue('A4', 'Email')->setCellValue('B4', $orderCustomerEmail)->setCellValue('A5', 'Company Name')->setCellValue('B5', $billingDetails['billing_company'])->setCellValue('A6', 'Phone Num')->setCellValueExplicit('B6', $billingDetails['billing_telephone'], PHPExcel_Cell_DataType::TYPE_STRING)->setCellValue('A8', 'City')->setCellValue('B8', $billingDetails['billing_city'])->setCellValue('A9', 'County')->setCellValue('B9', $billingDetails['billing_state'])->setCellValue('A10', 'Postcode')->setCellValue('B10', $billingDetails['billing_zip'])->setCellValue('A13', 'Product Code')->setCellValue('B13', 'Description (Title)')->setCellValue('C13', 'Pack Size (Case Size)');
$cellNum = "14";
#loop for all order items
foreach ($items as $itemId => $item) {
$cell = "A" . $cellNum;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cell, $item->getSku());
$cell = "B" . $cellNum;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cell, $item->getName());
$cellNum++;
}
foreach (range('A', 'D') as $columnID) {
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}
$filename = "xls/WEBSITE.com_enquiry_" . $file . ".xlsx";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($filename);
//////////////////////////////////////////////////////////////
include 'Mailer.php';
// your email
$recipient = "*****@*****.**";
// person sending it
$from = "*****@*****.**";
// subject
$subject = "New enguiry from WEBSITE.com";
// email message
$message = "Please see the attached quote enquiry.";
// initialize email object ($to_address, $from_address, $subject, $reply_address=null, $mailer=null, $custom_header=null)
$myEmail = new CAQuote_Mailer($recipient, $from, $subject);
// Add the message to the email
$myEmail->addText($message);
// add the file to the email ($filename, $type=null, $filecontents=null)
// NOTE: If filecontents is left out, filename is assumed to be path to file.
// If filecontents is included, filename is only used as the name of file.
// and filecontents is used as the content of file.
$myEmail->addFile($filename, "application/vnd.ms-excel", $contents);
// actually send out the email
$myEmail->send();
//////////////////////////////////////////////////////////////
Mage::log('CAQuote module activated.', null, 'quote.log');
}
function exportExcel($data, $savefile = null, $title = null, $sheetname = 'sheet1')
{
import("Org.Util.PHPExcel");
//若没有指定文件名则为当前时间戳
if (is_null($savefile)) {
$savefile = time();
}
//若指字了excel表头，则把表单追加到正文内容前面去
if (is_array($title)) {
array_unshift($data, $title);
}
$objPHPExcel = new \PHPExcel();
//Excel内容
$head_num = count($data);
foreach ($data as $k => $v) {
$obj = $objPHPExcel->setActiveSheetIndex(0);
$row = $k + 1;
//行
$nn = 0;
foreach ($v as $vv) {
$col = chr(65 + $nn);
//列
$obj->setCellValue($col . $row, $vv);
//列,行,值
$nn++;
}
}
//设置列头标题
for ($i = 0; $i < $head_num - 1; $i++) {
$alpha = chr(65 + $i);
$objPHPExcel->getActiveSheet()->getColumnDimension($alpha)->setAutoSize(true);
//单元宽度自适应
$objPHPExcel->getActiveSheet()->getStyle($alpha . '1')->getFont()->setName("Candara");
//设置字体
$objPHPExcel->getActiveSheet()->getStyle($alpha . '1')->getFont()->setSize(12);
//设置大小
$objPHPExcel->getActiveSheet()->getStyle($alpha . '1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
//设置颜色
$objPHPExcel->getActiveSheet()->getStyle($alpha . '1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//水平居中
$objPHPExcel->getActiveSheet()->getStyle($alpha . '1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//垂直居中
$objPHPExcel->getActiveSheet()->getStyle($alpha . '1')->getFont()->setBold(true);
//加粗
}
$objPHPExcel->getActiveSheet()->setTitle($sheetname);
//题目
$objPHPExcel->setActiveSheetIndex(0);
//设置当前的sheet
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $savefile . '.xls"');
//文件名称
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//Excel5 Excel2007
$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;
}
/**
* Prepare the file
* @param Traversable
* @return bool
*/
protected function prepare(\Traversable $objReader)
{
if (!parent::prepare($objReader)) {
return false;
}
$this->currentRow = 0;
$this->objPHPExcel = new \PHPExcel();
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$this->objPHPExcel->setActiveSheetIndex(0);
return true;
}
function index()
{
$this->load->database();
$this->db->select('user_info.user_id,user_info.user_name,user_info.user_sex,hr_info.hr_center,hr_info.hr_department,user_info.user_college,user_info.user_major,user_info.user_phone,user_info.user_qq,user_info.user_remarks');
$this->db->from('hr_user');
$this->db->join('hr_info', 'hr_info.hr_id = hr_user.hr_id');
$this->db->join('user_info', 'user_info.user_id = hr_user.user_id');
$query = $this->db->get();
//$query = mb_convert_encoding("gb2312", "UTF-8", $query);
if (!$query) {
return false;
}
// Starting the PHPExcel library
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('报名表单');
$objPHPExcel->getActiveSheet()->setCellValue('A1', '报名编号');
$objPHPExcel->getActiveSheet()->setCellValue('B1', '姓名');
//$objPHPExcel->getActiveSheet()->setCellValue('A1', 'String');
$objPHPExcel->getActiveSheet()->setCellValue('C1', '性别');
$objPHPExcel->getActiveSheet()->setCellValue('D1', '中心');
$objPHPExcel->getActiveSheet()->setCellValue('E1', '部门');
$objPHPExcel->getActiveSheet()->setCellValue('F1', '学院');
$objPHPExcel->getActiveSheet()->setCellValue('G1', '专业');
$objPHPExcel->getActiveSheet()->setCellValue('H1', '电话');
$objPHPExcel->getActiveSheet()->setCellValue('I1', 'QQ');
$objPHPExcel->getActiveSheet()->setCellValue('J1', '自我介绍');
// Field names in the first row
$fields = $query->list_fields();
$col = 0;
foreach ($fields as $field) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 2, $field);
$col++;
}
// Fetching the table data
$row = 2;
foreach ($query->result() as $data) {
$col = 0;
foreach ($fields as $field) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->{$field});
$col++;
}
$row++;
}
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
//发送标题强制用户下载文件
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Products_' . date('dMy') . '.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
}
function procesarExcel($ruta, $archivo, $miConfigurador, $esteBloque, $esteRecursoDB, $sql)
{
/** Include PHPExcel */
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';
/** Include PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';
if (!file_exists($ruta . $archivo)) {
exit("Por favor ingrese un archivo valido." . EOL);
}
$objPHPExcelReader = PHPExcel_IOFactory::load($ruta . $archivo);
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$titulo = "EstudiantesActivos" . uniqid();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Universidad Distrtal Oficina Asesora de Sistemas")->setTitle($titulo)->setSubject("Estudiantes Activos")->setDescription("Archivo resultado de validar los estudiantes activos")->setKeywords("estudiantes activos cedulas OAS")->setCategory("Validaciòn");
$lastRow = $objPHPExcelReader->getActiveSheet()->getHighestRow();
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Cedulas');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', 'Activo');
for ($i = 2; $i <= $lastRow; $i++) {
$celda = $objPHPExcelReader->getActiveSheet()->getCell('A' . $i)->getValue();
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $i, $celda);
//ejecuta consulta
//
//
$cadena_sql = $sql->cadena_sql("consultarEstudianteActivo", $celda);
$registro = $esteRecursoDB->ejecutarAcceso($cadena_sql, "busqueda");
if ($registro == null) {
$strActivo = "NO";
} else {
$strActivo = $registro[0][0];
}
//Llena Campos
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $i, $strActivo);
//debe hacer consulta
}
//elimina todos los archivos excel de la carpeta
array_map('unlink', glob($ruta . "*.xls"));
array_map('unlink', glob($ruta . "*.xlsx"));
//escribe el nuevo archivo
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($ruta . $titulo . '.xlsx');
//URL de descarga
$url = $miConfigurador->getVariableConfiguracion("host");
$url .= $miConfigurador->getVariableConfiguracion("site");
$url .= "/blocks/" . $esteBloque["nombre"];
$url .= "/" . $esteBloque["grupo"];
$url .= "/uploads/";
$urlDescarga = $url . $titulo . '.xlsx';
//Salida HTML link descarga
echo '<p style="text-align:center;" ><a';
echo ' href="' . $urlDescarga . '">Descargar Listado de Estudiantes';
echo '</a></p>';
exit;
}
function export_excel($data, $bch, $zone, $qu, $pr, $st)
{
$objPHPExcel = new PHPExcel();
$collumn = array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O");
if (!empty($collumn)) {
foreach ($collumn as $each_collumn) {
$objPHPExcel->getActiveSheet()->getColumnDimension("{$each_collumn}")->setAutoSize(true);
}
}
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getProperties()->setCreator("NASFAT")->setLastModifiedBy("NASFAT")->setTitle("NASFAT Members")->setSubject("NASFAT Members")->setDescription("NASFAT Members")->setKeywords("NASFAT Members")->setCategory("NASFAT Members");
// Add some data
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A1", "Sn")->setCellValue("B1", "Name")->setCellValue("C1", "E-Mail")->setCellValue("D1", "Age")->setCellValue("E1", "Branch")->setCellValue("F1", "Zone")->setCellValue("G1", "Membership No")->setCellValue("H1", "Phone")->setCellValue("I1", "Occupation")->setCellValue("J1", "Qualification")->setCellValue("K1", "Gender")->setCellValue("L1", "Marital Status")->setCellValue("M1", "Membership Status")->setCellValue("N1", "Registration Date");
$i = 2;
$sn = 1;
foreach ($data as $each_data) {
$name = $each_data['PersonalProfile']['first_name'] . ', ' . $each_data['PersonalProfile']['last_name'] . ' ' . $each_data['PersonalProfile']['other_name'];
$email = $each_data['PersonalProfile']['email'];
$age = $each_data['PersonalProfile']['dob'];
$brnch = $each_data['PersonalProfile']['branch_id'];
$reg_no = $each_data['PersonalProfile']['reg_no'];
$phone = $each_data['PersonalProfile']['phone'];
if ($each_data['PersonalProfile']['qual_id'] == NULL) {
$qual = $each_data['PersonalProfile']['qualification'];
} else {
$qual = $qu[$each_data['PersonalProfile']['qual_id']];
}
if ($each_data['PersonalProfile']['prof_id'] == NULL) {
$prof = $pr[$each_data['PersonalProfile']['profession']];
// $prof = $each_data['PersonalProfile']['profession'];
} else {
$prof = $pr[$each_data['PersonalProfile']['prof_id']];
}
$mar_stat = $each_data['PersonalProfile']['mar_status'];
$mem_stat = $each_data['PersonalProfile']['membership_status_id'];
$reg_date = $each_data['PersonalProfile']['created'];
$sex = $each_data['PersonalProfile']['sex'];
$zn = $each_data['PersonalProfile']['zone_id'];
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$i}", "{$sn}")->setCellValue("B{$i}", "{$name}")->setCellValue("C{$i}", "{$email}")->setCellValue("D{$i}", "{$age}")->setCellValue("E{$i}", "{$bch[$brnch]}")->setCellValue("F{$i}", "{$zone[$zn]}")->setCellValue("G{$i}", "{$reg_no}")->setCellValue("H{$i}", "{$phone}")->setCellValue("I{$i}", "{$prof}")->setCellValue("J{$i}", "{$qual}")->setCellValue("K{$i}", "{$sex}")->setCellValue("L{$i}", "{$mar_stat}")->setCellValue("M{$i}", "{$st[$mem_stat]}")->setCellValue("N{$i}", "{$reg_date}");
$i++;
$sn++;
}
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle("NASFAT Members");
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Rirect output to a client"s web browser (Excel2007)
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment;filename=nasfat_members.xlsx");
header("Cache-Control: max-age=0");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
$objWriter->save("php://output");
exit;
}
function downExcel($map, $data)
{
include_once IA_ROOT . '/framework/class/PHPExcel.php';
include_once IA_ROOT . '/framework/class/PHPExcel/Writer/Excel5.php';
$excel = new PHPExcel();
//$map['title'] = iconv('utf-8", "gb2312", $map['title']);
$excel->getProperties()->setCreator("时代地产");
$excel->setActiveSheetIndex(0);
$excel->getActiveSheet()->setTitle($map['title']);
$excel->setActiveSheetIndex(0);
$sheet = $excel->getActiveSheet();
$c = range('A', 'Z');
$i = 1;
$cell = '';
foreach ($map['fields'] as $k => $f) {
$cell = $c[$k] . $i;
$sheet->setCellValue($cell, $f['title']);
}
foreach ($data as $item) {
$i++;
foreach ($map['fields'] as $k => $f) {
$cell = $c[$k] . $i;
$value = $item[$f['field']];
if ($f['type'] == 1) {
$value = date('Y-m-d H:i:s', $item[$f['field']]);
}
//数值类型
if ($f['type'] == 2) {
$sheet->setCellValueExplicit($cell, $value, PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$sheet->setCellValue($cell, $value);
}
}
}
$writer = new PHPExcel_Writer_Excel5($excel);
header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type: application/vnd.ms-excel;charset=UTF-8");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
//header("Content-Disposition:attachment;filename=" . $map['title'] . ".xls");
$ua = strtoupper($_SERVER["HTTP_USER_AGENT"]);
$filename = basename($map['title'] . ".xls");
if (preg_match("/IE/", $ua)) {
$encoded_filename = str_replace("+", "%20", urlencode($filename));
//header('Content-Disposition: attachment; filename=' . $filename);
header('Content-Disposition: attachment; filename="' . $encoded_filename . '"');
} else {
header('Content-Disposition: attachment; filename=' . $filename);
}
header("Content-Transfer-Encoding:binary");
$writer->save("php://output");
}
public function init()
{
parent::init();
foreach ($this->columns as $key => $column) {
if (is_array($column)) {
$this->columns[$key] = Yii::createObject(ArrayHelper::merge(['activeDataProvider' => $this->activeDataProvider], $column));
}
}
$this->activeDataProvider->pagination = ['defaultPageSize' => false, 'pageSizeLimit' => false];
$this->phpExcel = new \PHPExcel();
$this->phpExcel->setActiveSheetIndex(0);
}
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');
}
function exportarExcel($datos)
{
$configuracion = array("creador" => isset($datos['config']['creador']) ? $datos['config']['creador'] : 'SEDATU', "titulo" => isset($datos['config']['titulo']) ? $datos['config']['titulo'] : 'REPORTE', "asunto" => isset($datos['config']['asunto']) ? $datos['config']['asunto'] : 'GENERAL', "descripcion" => isset($datos['config']['descripcion']) ? $datos['config']['descripcion'] : 'REPORTE GENERADO POR EL SISTEMA', "categoria" => isset($datos['config']['categoria']) ? $datos['config']['categoria'] : 'REPORTES', "nombre" => isset($datos['config']['nombre']) ? $datos['config']['nombre'] : 'reporte_' . date('Y-m-d_h:i:s'));
//Control de errores
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');
//Se incluye la libreria PHPExcel
require_once dirname(__FILE__) . '/PHPEXCEL/PHPExcel.php';
//Objeto PHPExcel
$objPHPExcel = new PHPExcel();
//Propiedades del documento
$objPHPExcel->getProperties()->setCreator($configuracion['creador'])->setLastModifiedBy($configuracion['creador'])->setTitle($configuracion['titulo'])->setSubject($configuracion['asunto'])->setDescription("Este documento fue generado con el sistema de la SEDATU")->setKeywords("SEDATU REPORTES EXCEL ")->setCategory($configuracion['categoria']);
//Formateado de documento
//Encabezado
$columnas = 1;
if (sizeof($datos['informacion']) > 0) {
$columnas = sizeof($datos['informacion'][0]);
}
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '¡Hello world!' . $columnas);
//Informacion del documento
//$objPHPExcel->setActiveSheetIndex(0)
// ->setCellValue('A1', 'Hello world');
// Nombramiento de la hoja
$objPHPExcel->getActiveSheet()->setTitle('Reporte');
//Asignacion de hoja activa inicial
$objPHPExcel->setActiveSheetIndex(0);
//nombre del archivo
$archivo = "x" . date('simdh') * 2;
//Redireccionamiento como archivo excel
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $archivo . '"');
header('Cache-Control: max-age=0');
//header('Cache-Control: max-age=1'); //solo para internet explorer 9
// 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'); //salida al navegador
$objWriter->save('descargas/xls/' . $archivo);
//Guardado en el servidor
$_SESSION['USUARIO'][$archivo] = array("nombre" => $datos['nombre'], "carpeta" => "xls", "extension" => "xlsx");
return $archivo;
}
/**
* 生成EXCEL文件
* @param type $fileName 导出的文件名称
* @param array $title 内容的列标题 | 一维数组
* @param array $content 内容 | 必须为二维数组 |
* 函数补充说明 当参数$content二维数组下的键值为非数字时
* 程序会将内容值强制设置为文本类型。适用于：身份证，订单号码等纯数字内容
* 声明方法如下: array('0' => array('order' => '123456789', '0' => 'abc', '1' => 'test')
*
*/
public function export($fileName, array $title, array $content)
{
if (empty($title)) {
die('请填写填写导出的列标题');
}
$letters = range('A', 'Z');
$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");
$countTitle = count($title);
for ($i = 0; $i < $countTitle; $i++) {
$objPHPExcel->getActiveSheet()->getColumnDimension($letters[$i])->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("{$letters[$i]}1", $title[$i])->getStyle("{$letters[$i]}1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
$j = 2;
foreach ($content as $item) {
$i = 0;
if (!is_array($item)) {
die('内容数组非二维数组!');
}
foreach ($item as $key => $value) {
//如果内容为数组，表明该内容需要设置样式
if (is_array($value)) {
$value = $this->setStyle($value);
}
if (is_numeric($key)) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($letters[$i] . $j, $value)->getStyle($letters[$i] . $j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
} else {
$objPHPExcel->setActiveSheetIndex(0)->setCellValueExplicit($letters[$i] . $j, $value)->getStyle($letters[$i] . $j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
$i++;
}
$j++;
}
$objPHPExcel->getActiveSheet()->setTitle($fileName);
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
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;
}
public function export($data, $savefile = null, $title = null, $sheetname = '')
{
import('Common.Tools.PHPExcel.PHPExcel');
//若没有指定文件名则为当前时间戳
if (empty($savefile)) {
$savefile = time();
}
//若指字了excel表头，则把表单追加到正文内容前面去
if (is_array($title)) {
array_unshift($data, $title);
}
$objPHPExcel = new \PHPExcel();
//Excel内容
$head_num = count($data);
foreach ($data as $k => $v) {
$obj = $objPHPExcel->setActiveSheetIndex(0);
$row = $k + 1;
//行
$nn = 0;
foreach ($v as $vv) {
$col = chr(65 + $nn);
//列
$obj->setCellValue($col . $row, $vv);
//列,行,值
$nn++;
}
}
//设置列头标题
for ($i = 0; $i < $head_num - 1; $i++) {
$alpha = chr(65 + $i);
$objPHPExcel->getActiveSheet()->getColumnDimension($alpha)->setAutoSize(true);
//单元宽度自适应
$objPHPExcel->getActiveSheet()->getStyle($alpha . '1')->getFont()->setName("Candara");
//设置字体
$objPHPExcel->getActiveSheet()->getStyle($alpha . '1')->getFont()->setSize(12);
//设置大小
}
$objPHPExcel->getActiveSheet()->setTitle($sheetname);
//题目
$objPHPExcel->setActiveSheetIndex(0);
//设置当前的sheet
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $savefile . '.xls"');
//文件名称
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//Excel5 Excel2007
$objWriter->save('php://output');
exit;
}
public function download()
{
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
$data = $this->mode->show();
if ($data) {
if (!class_exists('PHPExcel')) {
include_once CUR_CONF_PATH . 'lib/PHPExcel/PHPExcel.php';
}
$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");
//设置标题
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '姓名')->setCellValue('B1', '单位')->setCellValue('C1', '职务')->setCellValue('D1', '手机号')->setCellValue('E1', '邮箱')->setCellValue('F1', '嘉宾身份');
for ($i = 0, $n = 2; $i < count($data); $i++, $n++) {
//加入数据
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $n, $data[$i]['name'])->setCellValue('B' . $n, $data[$i]['company'])->setCellValue('C' . $n, $data[$i]['job'])->setCellValue('D' . $n, $data[$i]['telephone'])->setCellValue('E' . $n, $data[$i]['email'])->setCellValue('F' . $n, $data[$i]['guest_type_text']);
}
// 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 (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="会议嘉宾.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
} else {
$this->errorOutput('NO_DATA');
}
}
function catalog_to_xlsx()
{
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Boy Gruv")->setLastModifiedBy("Boy Gruv")->setTitle("PHPExcel Document")->setSubject("PHPExcel Document")->setDescription("Document for PHPExcel, generated using PHP classes.")->setKeywords("office PHPExcel php")->setCategory("Result file");
// Заголовок страницы Categories
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'category_id')->setCellValue('B1', 'parent_id')->setCellValue('C1', 'name(en)')->setCellValue('D1', 'name(ru)')->setCellValue('E1', 'top')->setCellValue('F1', 'columns')->setCellValue('G1', 'sort_order')->setCellValue('H1', 'image_name')->setCellValue('I1', 'date_added')->setCellValue('J1', 'date_modified')->setCellValue('K1', 'seo_keyword')->setCellValue('L1', 'description(en)')->setCellValue('M1', 'description(ru)')->setCellValue('N1', 'meta_title(en)')->setCellValue('O1', 'meta_title(ru)')->setCellValue('P1', 'meta_description(en)')->setCellValue('Q1', 'meta_description(ru)')->setCellValue('R1', 'meta_keywords(en)')->setCellValue('S1', 'meta_keywords(ru)')->setCellValue('T1', 'store_ids')->setCellValue('U1', 'layout')->setCellValue('V1', 'status');
// Заполняем данные
$i = 2;
$arr = SQL_get_catalog();
for ($j = 0; $j < count($arr); $j++) {
$i = $j + 2;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $i, $arr[$j]["id_cat"] + 100)->setCellValue('B' . $i, $arr[$j]["parent_id"] + 100)->setCellValue('D' . $i, $arr[$j]["name"])->setCellValue('E' . $i, 'true')->setCellValue('F' . $i, '1')->setCellValue('G' . $i, '0')->setCellValue('I' . $i, date('Y-m-d H:i:s'))->setCellValue('J' . $i, date('Y-m-d H:i:s'))->setCellValue('K' . $i, '')->setCellValue('M' . $i, '')->setCellValue('T' . $i, '0')->setCellValue('V' . $i, 'true');
}
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Categories');
//Добавляем новую страницу
$MyWorkSheet = new PHPExcel_Worksheet($objPHPExcel, 'CategoryFilters');
$objPHPExcel->addSheet($MyWorkSheet, 1);
// Заголовок страницы CategoryFilters
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A1', 'category_id')->setCellValue('B1', 'filter_group')->setCellValue('C1', 'filter');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Save Excel 2007 file
$callStartTime = microtime(true);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
//echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
$ret_str = date('H:i:s') . " File written to " . str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME));
return $ret_str;
}
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');
}
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');
}
