/** * Выводит отчет */ 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'); }