PHP PHPExcel::setActiveSheetIndex方法代码示例

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

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

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

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

 /**
  * 导出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 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;
     }
 }

 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 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 = "user@email.com";
     // person sending it
     $from = "noreply@email.com";
     // 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');
}