PHP PHPExcel::getProperties方法代码示例

 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 toExcel($SiteName, $StrarTime, $FinshTime)
 {
     /** Error reporting */
     error_reporting(E_ALL);
     /** Include path **/
     ini_set('include_path', ini_get('include_path') . ';../Classes/');
     /** PHPExcel */
     include 'PHPExcel.php';
     /** PHPExcel_Writer_Excel2007 */
     include 'PHPExcel/Writer/Excel2007.php';
     // Create new PHPExcel object
     // echo date('H:i:s') . " Create new PHPExcel object\n";
     $objPHPExcel = new PHPExcel();
     // Set properties
     // echo date('H:i:s') . " Set properties\n";
     $objPHPExcel->getProperties()->setCreator("Chen Po Hsun");
     $objPHPExcel->getProperties()->setLastModifiedBy("Po Hsun");
     $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
     $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
     $objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
     // Add some data
     // echo date('H:i:s') . " Add some data\n";
     $time_diff = (strtotime($time1) - strtotime($time2)) / (60 * 60) + 1;
     $begin = 2;
     $objPHPExcel->setActiveSheetIndex(0);
     $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'SiteName');
     $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Country!');
     $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'PM2.5');
     $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'PM10');
     $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'CO');
     $data['query'] = $this->sql->select_site($SiteName, $StrarTime);
     while ($begin < $time_diff + 2) {
         $objPHPExcel->getActiveSheet()->SetCellValue('B1', $data['SiteName']);
         $objPHPExcel->getActiveSheet()->SetCellValue('C1', $data['Country']);
         $objPHPExcel->getActiveSheet()->SetCellValue('D1', $data['PM2.5']);
         $objPHPExcel->getActiveSheet()->SetCellValue('E1', $data['PM10']);
         $objPHPExcel->getActiveSheet()->SetCellValue('F1', $data['CO']);
         $objPHPExcel->getActiveSheet()->SetCellValue('A$begin', $data['PublishTime']);
         $begin++;
         $data['query'] = $this->sql->select_site($SiteName, $FinshTime);
     }
     // Rename sheet
     // echo date('H:i:s') . " Rename sheet\n";
     $objPHPExcel->getActiveSheet()->setTitle('Test');
     // Save Excel 2007 file
     // echo date('H:i:s') . " Write to Excel2007 format\n";
     $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
     $objWriter->save(str_replace('.php', '.xlsx', __FILE__));
     // Echo done
     // echo date('H:i:s') . " Done writing file.\r\n";
 }

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

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

 /**
  * @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 display()
 {
     global $current_user;
     $c = range('A', 'Z');
     // Create new PHPExcel object
     $objPHPExcel = new PHPExcel();
     // set column size to auto
     foreach ($c as $columnID) {
         $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
     }
     // Set properties
     $objPHPExcel->getProperties()->setCreator($current_user->display_name);
     $objPHPExcel->getProperties()->setLastModifiedBy($current_user->display_name);
     $objPHPExcel->getProperties()->setTitle($this->title);
     $objPHPExcel->getProperties()->setSubject($this->subject);
     $objPHPExcel->getProperties()->setDescription($this->description);
     // Add some data
     $objPHPExcel->setActiveSheetIndex(0);
     // table header
     $x = 0;
     foreach ($this->cols as $v) {
         $objPHPExcel->getActiveSheet()->SetCellValue($c[$x] . '1', $v)->getStyle($c[$x] . '1')->getFont()->setBold(true);
         // A1, B1, etc
         $x++;
     }
     // table row
     foreach ($this->data as $i => $item) {
         $j = $i + 2;
         // A2, B2, etc
         $x = 0;
         $index = $i + 1;
         $item->index = 0;
         foreach ($this->cols as $column_name => $v) {
             if ($column_name == 'index') {
                 $item->index = 10;
             }
             #$val = $item->$column_name;
             $method = 'column_' . $column_name;
             if (method_exists($this->table_obj, $method)) {
                 $val = $this->table_obj->{$method}($item, $column_name);
             } else {
                 $val = $this->table_obj->column_default($item, $column_name);
             }
             $objPHPExcel->getActiveSheet()->SetCellValue($c[$x] . $j, $val, $format);
             $x++;
         }
     }
     $objPHPExcel->getActiveSheet()->setTitle('Sheet 1');
     $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
     header('Content-type: application/vnd.ms-excel');
     header('Content-Disposition: attachment; filename="' . $this->filename . '"');
     $objWriter->save('php://output');
     exit;
 }

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

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

 /**
  * Genera excel con los datos de la tabla
  */
 public function excel()
 {
     $this->_setDataTable();
     $this->_addPlugin();
     $letras = array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z");
     $this->load->lib("Vendor/PHPExcel", false);
     $this->_objPHPExcel = new PHPExcel();
     $this->_objPHPExcel->getProperties()->setCreator("Sumanet 3.0")->setLastModifiedBy("Sumanet 3.0")->setTitle("Exportación de expedientes")->setSubject("Sumanet")->setDescription($this->_title)->setKeywords("office 2007 openxml php sumanet")->setCategory("Sumanet");
     $i = 0;
     foreach ($this->_data["columns"] as $columna) {
         if (count($columna) > 0) {
             $this->_objPHPExcel->setActiveSheetIndex(0)->setCellValue($letras[$i] . '1', strip_tags($columna["column_name"]));
             $i++;
         }
     }
     $this->_filasExcel($letras);
     $this->_objPHPExcel->getActiveSheet()->setTitle($this->_title);
     $this->_objPHPExcel->setActiveSheetIndex(0);
     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
     header('Content-Disposition: attachment;filename="' . $this->_title . '.xlsx"');
     header('Cache-Control: max-age=0');
     $objWriter = PHPExcel_IOFactory::createWriter($this->_objPHPExcel, 'Excel2007');
     $objWriter->save('php://output');
     exit;
 }

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

 public function download()
 {
     if ($this->session->userdata('logged_in')) {
         //Cek User Branch
         $user_branch = $this->session->userdata('user_branch');
         $branch_name = str_replace(' ', '', $this->session->userdata('user_branch_name'));
         if ($user_branch == 0) {
             $branch_name = "Pusat";
         }
         //load our new PHPExcel library
         $this->load->library('excel');
         $objPHPExcel = new PHPExcel();
         $objPHPExcel->getProperties()->setCreator("Amartha MIS");
         $objPHPExcel->getProperties()->setLastModifiedBy("Amartha MIS");
         $objPHPExcel->getProperties()->setTitle("REGPYD");
         $objPHPExcel->getProperties()->setSubject("REGPYD");
         $objPHPExcel->getProperties()->setDescription("REGPYD");
         $objPHPExcel->setActiveSheetIndex(0);
         $objPHPExcel->getActiveSheet()->setTitle('REGPYD');
         //TITLE
         $objPHPExcel->getActiveSheet()->setCellValue("A1", "Amartha Microfinance");
         $objPHPExcel->getActiveSheet()->setCellValue("A2", "REGPYD Cabang {$branch_name}");
         $objPHPExcel->getActiveSheet()->mergeCells("A1:D1");
         $objPHPExcel->getActiveSheet()->getStyle("A1")->applyFromArray(array("font" => array("bold" => true, 'size' => 16)));
         $objPHPExcel->getActiveSheet()->getStyle("A2")->applyFromArray(array("font" => array("bold" => true)));
         //TOP ROW
         $objPHPExcel->getActiveSheet()->getStyle("A4:O4")->applyFromArray(array("font" => array("bold" => true)));
         $objPHPExcel->getActiveSheet()->setCellValue("A4", "NO");
         $objPHPExcel->getActiveSheet()->setCellValue("B4", "NOMOR REKENING");
         $objPHPExcel->getActiveSheet()->setCellValue("C4", "NAMA");
         $objPHPExcel->getActiveSheet()->setCellValue("D4", "MAJELIS");
         $objPHPExcel->getActiveSheet()->setCellValue("E4", "CABANG");
         $objPHPExcel->getActiveSheet()->setCellValue("F4", "PLAFOND");
         $objPHPExcel->getActiveSheet()->setCellValue("G4", "PROFIT");
         $objPHPExcel->getActiveSheet()->setCellValue("H4", "TGL PENCAIRAN");
         $objPHPExcel->getActiveSheet()->setCellValue("I4", "TGL JATUH TEMPO");
         $objPHPExcel->getActiveSheet()->setCellValue("J4", "ANGSURAN KE");
         $objPHPExcel->getActiveSheet()->setCellValue("K4", "AKAD");
         $objPHPExcel->getActiveSheet()->setCellValue("L4", "TAB WAJIB");
         $objPHPExcel->getActiveSheet()->setCellValue("M4", "TAB SUKARELA");
         $objPHPExcel->getActiveSheet()->setCellValue("N4", "SEKTOR PEMBIAYAAN");
         $objPHPExcel->getActiveSheet()->setCellValue("O4", "TUJUAN PEMBIAYAAN");
         $no = 5;
         //EXPORT
         $filename = "REGPYD_" . $branch_name . "_" . time() . '.xls';
         //save our workbook as this file name
         header('Content-Type: application/vnd.ms-excel');
         //mime type
         header('Content-Disposition: attachment;filename="' . $filename . '"');
         //tell browser what's the file name
         header('Cache-Control: max-age=0');
         //no cache
         $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
         $objWriter->save('php://output');
         //redirect('accounting/jurnal', 'refresh');
     } else {
         //If no session, redirect to login page
         redirect('login', 'refresh');
     }
 }

 function arrayToExcel($objPHPExcel = null, $rows, $writeArrayKeysAsHeader = false, $rowStartWrite = 1, $setActiveSheetTo = 0, $sheetName = null)
 {
     include_once 'sites/all/libraries/PHPExcel/PHPExcel/Writer/Excel2007.php';
     // Create an object/instance for the output spreadsheet
     if (is_null($objPHPExcel)) {
         $objPHPExcel = new PHPExcel();
     }
     myExcel_setActiveRow($rowStartWrite);
     // Create new sheet is needed
     $sheetOk = true;
     do {
         try {
             $objPHPExcel->setActiveSheetIndex($setActiveSheetTo);
             $sheetOk = true;
         } catch (Exception $e) {
             $sheetOk = false;
             $objPHPExcel->createSheet();
         }
     } while ($sheetOk === false);
     if (is_null($sheetName)) {
         $objPHPExcel->getActiveSheet()->setTitle("Sheet {$setActiveSheetTo}");
     } else {
         $objPHPExcel->getActiveSheet()->setTitle($sheetName);
     }
     // Set basic properties to output spreadsheet
     $objPHPExcel->getProperties()->setCreator("Business USA");
     $objPHPExcel->getProperties()->setLastModifiedBy("Business USA");
     $objPHPExcel->getProperties()->setTitle("Business USA");
     $objPHPExcel->getProperties()->setSubject("Business USA");
     $objPHPExcel->getProperties()->setDescription("Business USA");
     // Debug
     if (strpos(request_uri(), '-DEBUG-NOEXCELWRITE-REPORTWRITE-') !== false) {
         ob_end_clean();
     }
     // Write headders
     if ($writeArrayKeysAsHeader === true) {
         $headders = array();
         foreach ($rows[0] as $key => $cell) {
             $headders[] = $key;
         }
         myExcel_WriteValuesToActiveRow($objPHPExcel, $headders, true);
         // Set the next row as "active" so the next time myExcel_WriteValuesToActiveRow() is called it will write to the next
         myExcel_setActiveRow(myExcel_getActiveRow() + 1);
     }
     // Write rows
     foreach ($rows as $row) {
         // Add a row into the spreadsheet
         myExcel_WriteValuesToActiveRow($objPHPExcel, $row);
         // Set the next row as "active" so the next time myExcel_WriteValuesToActiveRow() is called it will write to the next
         myExcel_setActiveRow(myExcel_getActiveRow() + 1);
     }
     // Debug
     if (strpos(request_uri(), '-DEBUG-NOEXCELWRITE-REPORTWRITE-') !== false) {
         flush();
         exit;
     }
     myExcel_decideColumnWidths($objPHPExcel, $rows);
     return $objPHPExcel;
 }

/**
 * The MysqlExportXls function is used to export mysql query result into an .xls file.
 * @param MysqlExportXlsConnectOptions $connectOptions
 * @param MysqlExportXlsFileOptions $fileOptions
 * @return error message. Return empty string on success.
 */
function MysqlExportXls($connectOptions, $fileOptions, $query)
{
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->getProperties()->setCreator($fileOptions->creator);
    $objPHPExcel->getProperties()->setLastModifiedBy($fileOptions->lastModifiedBy);
    $objPHPExcel->getProperties()->setTitle($fileOptions->title);
    $objPHPExcel->getProperties()->setSubject($fileOptions->subject);
    $objPHPExcel->getProperties()->setDescription($fileOptions->description);
    $objPHPExcel->setActiveSheetIndex(0);
    $activeSheet = $objPHPExcel->getActiveSheet();
    $activeSheet->setTitle($fileOptions->title);
    // connect to mysql
    $link = mysql_connect($connectOptions->host, $connectOptions->userName, $connectOptions->password);
    if (!$link) {
        return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not connect: ' . mysql_error($link);
    }
    // use database
    $selectDb = mysql_select_db($connectOptions->useDatabase, $link);
    if (!$selectDb) {
        return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not select database' . mysql_error($link);
    }
    // PHPExcel use utf-8 encoding to save file only !!!
    $setCharset = mysql_set_charset("utf8", $link);
    if (!$setCharset) {
        return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not set charset' . mysql_error($link);
    }
    // execute sql
    $result = mysql_query($query, $link);
    if (!$result) {
        return __FILE__ . ":" . __FUNCTION__ . ':' . 'Query failed: ' . mysql_error($link);
    }
    // field names
    $columnIndex = 0;
    while ($field = mysql_fetch_field($result)) {
        $activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . '1', $field->name);
        ++$columnIndex;
    }
    $rowIndex = 2;
    // 1 based, the firset row is for field names.
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $columnIndex = 0;
        foreach ($line as $key => $col_value) {
            $activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . $rowIndex, $col_value === null ? "" : $col_value, PHPExcel_Cell_DataType::TYPE_STRING2);
            ++$columnIndex;
        }
        ++$rowIndex;
    }
    // free mysql resource
    mysql_free_result($result);
    mysql_close($link);
    // write data into file
    $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
    $objWriter->setPreCalculateFormulas(FALSE);
    // Why true by default ? oh god damn it!
    $objWriter->save($fileOptions->name);
    return "";
}