PHP PHPExcel::getActiveSheet方法代码示例

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

 function exportExcel($expTitle, $expCellName, $expTableData)
 {
     $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);
     //文件名称
     $fileName = $_SESSION['account'] . date('_YmdHis');
     //or $xlsTitle 文件名称可根据自己情况设定
     $cellNum = count($expCellName);
     $dataNum = count($expTableData);
     vendor("PHPExcel.PHPExcel");
     $objPHPExcel = new \PHPExcel();
     $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
     $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');
     //合并单元格
     for ($i = 0; $i < $cellNum; $i++) {
         $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
     }
     for ($i = 0; $i < $dataNum; $i++) {
         for ($j = 0; $j < $cellNum; $j++) {
             $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), $expTableData[$i][$expCellName[$j][0]]);
         }
     }
     header('pragma:public');
     header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
     header("Content-Disposition:attachment;filename={$fileName}.xls");
     //attachment新窗口打印inline本窗口打印
     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
     //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
     $objWriter->save('php://output');
     exit;
 }

 function generate($generator)
 {
     require_once "PHPExcel.php";
     $data = $generator->generateExportData();
     $this->chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
     $this->charArray = str_split($this->chars, 1);
     $objPHPExcel = new PHPExcel();
     // set document properties
     $objPHPExcel->getProperties()->setTitle("Test Data");
     // create a first sheet and populate the headings
     $objPHPExcel->setActiveSheetIndex(0);
     // hardcoded limitation of 26 x 27 columns (right now)
     $numCols = count($data["colData"]);
     for ($i = 0; $i < $numCols; $i++) {
         $col = $this->getExcelCol($i, 1);
         $objPHPExcel->getActiveSheet()->setCellValue($col, $data["colData"][$i]);
     }
     for ($i = 0; $i < count($data["rowData"]); $i++) {
         for ($j = 0; $j < $numCols; $j++) {
             $col = $this->getExcelCol($j, $i + 2);
             $objPHPExcel->getActiveSheet()->setCellValue($col, $data["rowData"][$i][$j]);
         }
     }
     // redirect output to a client’s web browser (Excel5)
     header('Content-Type: application/vnd.ms-excel');
     header('Content-Disposition: attachment;filename="01simple.xls"');
     header('Cache-Control: max-age=0');
     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
     $objWriter->save('php://output');
     exit;
 }

 public static function exportXlsx($data, $keys)
 {
     // Create new PHPExcel object
     $objPHPExcel = new \PHPExcel();
     // Set document properties
     $objPHPExcel->getProperties()->setCreator("Roadiz CMS")->setLastModifiedBy("Roadiz CMS")->setCategory("");
     $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
     $cacheSettings = ['memoryCacheSize' => '8MB'];
     \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
     $objPHPExcel->setActiveSheetIndex(0);
     foreach ($keys as $key => $value) {
         $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($key, 1, $value);
     }
     foreach ($data as $key => $answer) {
         foreach ($answer as $k => $value) {
             $columnAlpha = \PHPExcel_Cell::stringFromColumnIndex($k);
             if ($value instanceof \DateTime) {
                 $value = \PHPExcel_Shared_Date::PHPToExcel($value);
                 $objPHPExcel->getActiveSheet()->getStyle($columnAlpha . (2 + $key))->getNumberFormat()->setFormatCode('dd.mm.yyyy hh:MM:ss');
             }
             $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($k, 2 + $key, $value);
         }
     }
     // Set active sheet index to the first sheet, so Excel opens this as the first sheet
     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
     ob_start();
     $objWriter->save('php://output');
     $return = ob_get_clean();
     return $return;
 }

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

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 exportDaytime()
 {
     $app = JFactory::getApplication();
     $modelCalendar = new EstivoleModelCalendar();
     $modelDaytime = new EstivoleModelDaytime();
     $modelServices = new EstivoleModelServices();
     $daytimeid = $app->input->get('daytime_id');
     $daytime = $modelDaytime->getDaytime($daytimeid);
     $calendar = $modelCalendar->getItem($daytime->calendar_id);
     $this->services = $modelServices->getServicesByDaytime($daytime->daytime_day);
     // Create new PHPExcel object
     $objPHPExcel = new PHPExcel();
     // Set document properties
     $objPHPExcel->getProperties()->setCreator("Estivale Open Air")->setLastModifiedBy("Estivole")->setTitle("Export Estivole")->setSubject("Export des tranches horaires Estivole");
     // Add some data
     $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A1", "Plan de travail Estivale Open Air - Calendrier " . $calendar->name);
     // // Miscellaneous glyphs, UTF-8
     $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A2", date('d-m-Y', strtotime($daytime->daytime_day)));
     // Rename worksheet
     $objPHPExcel->getActiveSheet()->setTitle("Export");
     // Set active sheet index to the first sheet, so Excel opens this as the first sheet
     $objPHPExcel->setActiveSheetIndex(0);
     $cellCounter = 4;
     // Add data
     for ($i = 0; $i < count($this->services); $i++) {
         $objPHPExcel->getActiveSheet()->setCellValue("A" . $cellCounter, $this->services[$i]->service_name);
         $this->daytimes = $modelDaytime->listItemsForExport($this->services[$i]->service_id);
         foreach ($this->daytimes as $daytime) {
             $userId = $daytime->user_id;
             $userProfileEstivole = EstivoleHelpersUser::getProfileEstivole($userId);
             $userProfile = JUserHelper::getProfile($userId);
             $user = JFactory::getUser($userId);
             $objPHPExcel->getActiveSheet()->setCellValue("A" . ($cellCounter + 1), $userProfileEstivole->profilestivole['lastname'] . ' ' . $userProfileEstivole->profilestivole['firstname'])->setCellValueExplicit("B" . ($cellCounter + 1), $userProfile->profile['phone'], PHPExcel_Cell_DataType::TYPE_STRING)->setCellValue("C" . ($cellCounter + 1), $user->email)->setCellValue("D" . ($cellCounter + 1), date('H:i', strtotime($daytime->daytime_hour_start)))->setCellValue("E" . ($cellCounter + 1), date('H:i', strtotime($daytime->daytime_hour_end)));
             $cellCounter++;
         }
         $cellCounter = $cellCounter + 2;
     }
     // Redirect output to a client’s web browser (Excel2007)
     header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
     header("Content-Disposition: attachment;filename=\"01simple.xlsx\"");
     header("Cache-Control: max-age=0");
     // If you"re serving to IE 9, then the following may be needed
     header("Cache-Control: max-age=1");
     // If you"re serving to IE over SSL, then the following may be needed
     header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
     // Date in the past
     header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
     // always modified
     header("Cache-Control: cache, must-revalidate");
     // HTTP/1.1
     header("Pragma: public");
     // HTTP/1.0
     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
     $objWriter->save("php://output");
     exit;
 }

 public static function usersReport()
 {
     $conn = new Connect();
     $query = 'SELECT * FROM ' . self::DB_TBL_USUARIOS;
     $consult = $conn->prepare($query);
     $consult->execute();
     if ($consult->rowCount() > 0) {
         header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
         header('Content-Disposition: attachment;filename="Reporte de usuarios.xlsx"');
         header('Cache-Control: max-age=0');
         $reportName = "Reporte de usuarios";
         $reportNameTitles = array("Id", "Cédula", "Nombres", "Apellidos", "Email", "Teléfono", "Extensión", "Usuario", "Contrasena", "Rol");
         $styleColumnsTitle = array('font' => array('name' => 'Arial', 'bold' => true));
         $generarReporteXLSX = new PHPExcel();
         $generarReporteXLSX->getProperties()->setCreator("VideoConferencias UTPL")->setLastModifiedBy("VideoConferencias UTPL")->setTitle("Reporte de usuarios")->setSubject("Reporte de usuarios")->setDescription("Reporte de usuarios")->setKeywords("Reporte de usuarios")->setCategory("Reportes");
         $generarReporteXLSX->setActiveSheetIndex(0)->mergeCells('A1:J1');
         $generarReporteXLSX->setActiveSheetIndex(0)->setCellValue('A1', $reportName)->setCellValue('A3', $reportNameTitles[0])->setCellValue('B3', $reportNameTitles[1])->setCellValue('C3', $reportNameTitles[2])->setCellValue('D3', $reportNameTitles[3])->setCellValue('E3', $reportNameTitles[4])->setCellValue('F3', $reportNameTitles[5])->setCellValue('G3', $reportNameTitles[6])->setCellValue('H3', $reportNameTitles[7])->setCellValue('I3', $reportNameTitles[8])->setCellValue('J3', $reportNameTitles[9]);
         $i = 4;
         while ($row = $consult->fetch()) {
             $generarReporteXLSX->setActiveSheetIndex(0)->setCellValue('A' . $i, $row['id'])->setCellValue('B' . $i, $row['cedula'])->setCellValue('C' . $i, $row['nombres'])->setCellValue('D' . $i, $row['apellidos'])->setCellValue('E' . $i, $row['email'])->setCellValue('F' . $i, $row['telefono'])->setCellValue('G' . $i, $row['telefono_ext'])->setCellValue('H' . $i, $row['usuario'])->setCellValue('I' . $i, $row['contrasena'])->setCellValue('J' . $i, $row['id_rol']);
             $i++;
         }
         $generarReporteXLSX->getActiveSheet()->getStyle('A3:J3')->applyFromArray($styleColumnsTitle);
         $generarReporteXLSX->getActiveSheet()->setTitle('Usuarios');
         $generarReporteXLSX->setActiveSheetIndex(0);
         $generarReporteXLSX->getActiveSheet(0)->freezePaneByColumnAndRow(0, 4);
         $objWriter = PHPExcel_IOFactory::createWriter($generarReporteXLSX, 'Excel2007');
         $objWriter->save('php://output');
         exit;
     }
 }

 /**
  * 导出EXCEL表格
  */
 public function exportExcel()
 {
     /** Include PHPExcel */
     require_once './core/PHPExcel/PHPExcel.php';
     // Create new PHPExcel object
     $objPHPExcel = new PHPExcel();
     // Set document properties
     $objPHPExcel->getProperties()->setCreator("chen")->setLastModifiedBy("chen")->setTitle("会员信息列表")->setSubject("会员信息列表")->setDescription("会员信息列表");
     $memberList = D('Member')->where('user_id=' . $_SESSION['uid'])->field('id,wechat_id,name,mobile,email,date_reg,date_login')->order('date_login desc')->select();
     $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '会员ID')->setCellValue('B1', '微信号')->setCellValue('C1', '昵称')->setCellValue('D1', '手机号码')->setCellValue('E1', '邮箱')->setCellValue('F1', '注册时间')->setCellValue('G1', '上次访问时间');
     foreach ($memberList as $k => $v) {
         $objPHPExcel->getActiveSheet()->setCellValue('A' . ($k + 2), $v['id'])->setCellValue('B' . ($k + 2), $v['wechat_id'])->setCellValue('C' . ($k + 2), $v['name'])->setCellValue('D' . ($k + 2), $v['mobile'])->setCellValue('E' . ($k + 2), $v['email'])->setCellValue('F' . ($k + 2), date('Y-m-d H:i', $v['date_reg']))->setCellValue('G' . ($k + 2), date('Y-m-d H:i', $v['date_login']));
     }
     // Rename worksheet
     $objPHPExcel->getActiveSheet()->setTitle('会员信息列表');
     // Set active sheet index to the first sheet, so Excel opens this as the first sheet
     $objPHPExcel->setActiveSheetIndex(0);
     // Redirect output to a client’s web browser (Excel5)
     header('Content-Type: application/vnd.ms-excel');
     header('Content-Disposition: attachment;filename="会员列表' . date('md') . '.xls"');
     header('Cache-Control: max-age=0');
     // If you're serving to IE 9, then the following may be needed
     header('Cache-Control: max-age=1');
     // If you're serving to IE over SSL, then the following may be needed
     header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
     // Date in the past
     header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
     // always modified
     header('Cache-Control: cache, must-revalidate');
     // HTTP/1.1
     header('Pragma: public');
     // HTTP/1.0
     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
     $objWriter->save('php://output');
 }

 /**
  * @return \PHPExcel
  */
 protected function getExcel()
 {
     if ($this->excel === null) {
         $this->excel = new \PHPExcel();
         $this->excel->getProperties()->setCreator('dasred/translation');
         $this->excel->setActiveSheetIndex(0);
         $sheet = $this->excel->getActiveSheet()->setTitle(basename($this->getArguments()[0]));
     }
     return $this->excel;
 }

function generateXLS($data)
{
    global $mysqli, $ano;
    //include "connect.php";
    require_once dirname(__FILE__) . '/PHPExcel/Classes/PHPExcel/IOFactory.php';
    $filename = 'fluxocaixa_' . $ano . '.xlsx';
    //$filename = md5(time()). '.xlsx';
    header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    header("Content-Disposition: attachment; filename=\"{$filename}\"");
    header("Cache-Control: max-age=0");
    $excelObj = new PHPExcel();
    $excelObj->getProperties()->setCreator("Gestor Financeiro Web")->setLastModifiedBy("Vinícius Hacebe")->setTitle("Fluxo de Caixa")->setSubject("Fluxo de Caixa")->setDescription("Dados exportados do fluxo de caixa")->setKeywords("fluxo de caixa")->setCategory("fluxo de caixa");
    $sheet = $excelObj->setActiveSheetIndex(0);
    $sheet->setCellValue('A1', 'Classificação')->setCellValue('B1', 'Descrição')->setCellValue('C1', 'Saldo')->setCellValue('D1', 'Jan/' . $ano)->setCellValue('E1', 'Fev/' . $ano)->setCellValue('F1', 'Mar/' . $ano)->setCellValue('G1', 'Abr/' . $ano)->setCellValue('H1', 'Mai/' . $ano)->setCellValue('I1', 'Jun/' . $ano)->setCellValue('J1', 'Jul/' . $ano)->setCellValue('K1', 'Ago/' . $ano)->setCellValue('L1', 'Set/' . $ano)->setCellValue('M1', 'Out/' . $ano)->setCellValue('N1', 'Nov/' . $ano)->setCellValue('O1', 'Dez/' . $ano);
    $recordIndex = 2;
    for ($i = 0; $i < sizeof($data); $i++) {
        writeToSheet($recordIndex, $data, $i, $sheet);
    }
    foreach (range('A', 'O') as $columnID) {
        $excelObj->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
    }
    $excelObj->getActiveSheet()->getStyle('A1:A2560')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $objWriter = PHPExcel_IOFactory::createWriter($excelObj, 'Excel2007');
    //$objWriter->save($filename);
    $objWriter->save('php://output');
    //print_r($data);
}

 public static function renderData(array $itemsIterator, array $fields, $filename)
 {
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->getProperties()->setCreator("PrintWeek");
     $objPHPExcel->getProperties()->setTitle("Report");
     $objPHPExcel->getProperties()->setSubject("Report");
     $objPHPExcel->getProperties()->setDescription("Report");
     $objPHPExcel->setActiveSheetIndex(0);
     /**
      * Выводим строку названий столбцов
      */
     $col = 0;
     foreach ($fields as $name) {
         $objPHPExcel->getActiveSheet()->SetCellValue(self::getCellCoordinate($col++, 1), $name);
     }
     /**
      * Основной вывод информации
      */
     $row = 2;
     foreach ($itemsIterator as $item) {
         $col = 0;
         foreach ($fields as $name => $title) {
             $objPHPExcel->getActiveSheet()->SetCellValue(self::getCellCoordinate($col++, $row), $item->{$name});
         }
         $row++;
     }
     $objPHPExcel->getActiveSheet()->setTitle('Report');
     $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
     $objWriter->save($filename);
 }

function getExcel($fileName)
{
    $date = date('Y-m-d h:i:s', time());
    $fileName .= "_{$date}.xls";
    $filename1 = "data.json";
    //bug : if you use "/data.json", you cannnot open this file . you need to use "data.json"
    $json_string = file_get_contents($filename1);
    //$json2_string = icon_to_utf8($json_string);
    $obj = json_decode($json_string, true);
    $objPHPExcel = new PHPExcel();
    $objProps = $objPHPExcel->getProperties();
    $baseRow = 1;
    foreach ($obj as $r => $dataRow) {
        $row = $baseRow + $r;
        $objPHPExcel->getActiveSheet()->insertNewRowBefore($row, 1);
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $dataRow['city']);
        $objPHPExcel->getActiveSheet()->setCellValue('B' . $row, $dataRow['api']);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $row, $dataRow['state']);
        $objPHPExcel->getActiveSheet()->setCellValue('D' . $row, $dataRow['pm25']);
        $objPHPExcel->getActiveSheet()->setCellValue('E' . $row, $dataRow['pm10']);
        $objPHPExcel->getActiveSheet()->setCellValue('F' . $row, $dataRow['co']);
        $objPHPExcel->getActiveSheet()->setCellValue('G' . $row, $dataRow['no2']);
        $objPHPExcel->getActiveSheet()->setCellValue('H' . $row, $dataRow['o3']);
    }
    $objPHPExcel->getActiveSheet()->setTitle('Simple');
    $objPHPExcel->setActiveSheetIndex(0);
    //将输出重定向到一个客户端web浏览器(Excel2007)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header("Content-Disposition: attachment; filename=\"{$fileName}\"");
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
    //文件通过浏览器下载
    exit;
}