Example #1
3
 /**
  * @return \PHPExcel
  */
 protected function mockWorkbook()
 {
     $workbook = new \PHPExcel();
     $workbook->disconnectWorksheets();
     $workbook->getProperties()->setTitle('mocked');
     $sheet = new \PHPExcel_Worksheet($workbook);
     $sheet->fromArray([['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test']]);
     $workbook->addSheet($sheet);
     $sheet = new \PHPExcel_Worksheet($workbook);
     $sheet->fromArray([['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test']]);
     $workbook->addSheet($sheet);
     $workbook->setActiveSheetIndex(0);
     return $workbook;
 }
Example #2
1
 /**
  * @return \PHPExcel
  */
 protected function mockRow()
 {
     $workbook = new \PHPExcel();
     $workbook->disconnectWorksheets();
     $sheet = new \PHPExcel_Worksheet($workbook);
     $sheet->fromArray([['a1', 'b1', 'c1']]);
     $row = new \PHPExcel_Worksheet_Row($sheet, 1);
     return $row;
 }
Example #3
1
 /**
  * @return \PHPExcel_Worksheet
  */
 protected function mockSheet()
 {
     $workbook = new \PHPExcel();
     $workbook->disconnectWorksheets();
     $sheet = new \PHPExcel_Worksheet($workbook);
     $sheet->setTitle('mocked');
     $sheet->fromArray([['a1', 'b1'], ['a2', 'b2']]);
     return $sheet;
 }
Example #4
0
 /**
  * @param string $text
  *
  * @return Cell
  */
 protected function mockCell($text = 'text', $settings = false)
 {
     $workbook = new \PHPExcel();
     $workbook->disconnectWorksheets();
     $worksheet = new \PHPExcel_Worksheet($workbook);
     $workbook->addSheet($worksheet);
     $cell = $worksheet->setCellValue('A1', $pValue = $text, $returnCell = true);
     $settings = $settings ?: new ParserSettings();
     $cell = new Cell($cell, 1, $settings);
     return $cell;
 }
Example #5
0
 /**
  * Save file to disk.
  *
  * By default, it will not overwrite an existing file with the same name.
  *
  * @param string $filename Filename with path
  * @param string $format A value from \PHPExcel_IOFactory::$_autoResolveClasses
  * @param bool $canOverWrite Set to true if you want to let an existing file be overwritten upon saving.
  *
  * @return bool
  * @throws FileExistsException
  * @throws \PHPExcel_Reader_Exception
  */
 public function saveFile($filename, $format, $canOverWrite = false)
 {
     $objWriter = \PHPExcel_IOFactory::createWriter($this->objPHPExcel, $format);
     if (!$canOverWrite and file_exists($filename)) {
         throw new FileExistsException();
     }
     $objWriter->save($filename);
     $this->objPHPExcel->disconnectWorksheets();
     unset($this->objPHPExcel);
     return true;
 }
Example #6
0
 /**
  * Create excel file and store in tmp dir
  *
  * @param string $filename
  * @param string $format
  * @param bool   $disconnect
  * @return string
  */
 private function writeFile($filename, $format = 'Excel2007', $disconnect = true)
 {
     $path = sprintf('%s/%s', $this->path, $filename);
     $objWriter = \PHPExcel_IOFactory::createWriter($this->file, $format);
     $objWriter->save($path);
     if ($disconnect) {
         $this->file->disconnectWorksheets();
         unset($this->file);
     }
     $this->fileName = $filename;
     $this->filePath = $path;
     return $path;
 }
Example #7
0
 public function __destruct()
 {
     $this->_xls->disconnectWorksheets();
     unset($this->_xls);
 }
Example #8
0
 public function create_excel($excel_data = NUll)
 {
     //check if the excel data has been set if not exit the excel generation
     if (count($excel_data) > 0) {
         $objPHPExcel = new PHPExcel();
         $objPHPExcel->getProperties()->setCreator("HCMP");
         $objPHPExcel->getProperties()->setLastModifiedBy($excel_data['doc_creator']);
         $objPHPExcel->getProperties()->setTitle($excel_data['doc_title']);
         $objPHPExcel->getProperties()->setSubject($excel_data['doc_title']);
         $objPHPExcel->getProperties()->setDescription("");
         $objPHPExcel->setActiveSheetIndex(0);
         $rowExec = 1;
         //Looping through the cells
         $column = 0;
         foreach ($excel_data['column_data'] as $column_data) {
             $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, $rowExec, $column_data);
             $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($column))->setAutoSize(true);
             //$objPHPExcel->getActiveSheet()->getStyle($column, $rowExec)->getFont()->setBold(true);
             $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($column, $rowExec)->getFont()->setBold(true);
             $column++;
         }
         $rowExec = 2;
         foreach ($excel_data['row_data'] as $row_data) {
             $column = 0;
             foreach ($row_data as $cell) {
                 //Looping through the cells per facility
                 $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, $rowExec, $cell);
                 $column++;
             }
             $rowExec++;
         }
         $objPHPExcel->getActiveSheet()->setTitle('Simple');
         // Save Excel 2007 file
         //echo date('H:i:s') . " Write to Excel2007 format\n";
         $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
         // We'll be outputting an excel file
         header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
         header("Cache-Control: no-store, no-cache, must-revalidate");
         header("Cache-Control: post-check=0, pre-check=0", false);
         header("Pragma: no-cache");
         // It will be called file.xls
         header("Content-Disposition: attachment; filename=" . $excel_data['file_name'] . ".xlsx");
         // Write file to the browser
         $objWriter->save('php://output');
         $objPHPExcel->disconnectWorksheets();
         unset($objPHPExcel);
         // Echo done
     }
 }
 private function generate_excel($records = 'all')
 {
     require_once APPPATH . 'third_party/phpexcel/Classes/PHPExcel.php';
     require_once APPPATH . 'third_party/phpexcel/Classes/PHPExcel/Writer/Excel2007.php';
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->getProperties()->setCreator("MDB Order Management System");
     $objPHPExcel->getProperties()->setLastModifiedBy("MDB Order Management System");
     $objPHPExcel->getProperties()->setTitle("Order Exportation on 03-12-2014");
     $objPHPExcel->getProperties()->setSubject("Order Exportation on 03-12-2014");
     $objPHPExcel->getProperties()->setDescription("Order Exportation on 03-12-2014");
     $objPHPExcel->setActiveSheetIndex(0);
     $objPHPExcel->setActiveSheetIndex(0);
     // Column Header Setting
     $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Customer No.');
     $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Code');
     $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Name');
     $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Address');
     $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Address 2');
     $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'City');
     $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'State');
     $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Zip');
     $objPHPExcel->getActiveSheet()->SetCellValue('I1', 'Country/Region Code');
     $objPHPExcel->getActiveSheet()->SetCellValue('J1', 'Contact Name');
     $objPHPExcel->getActiveSheet()->SetCellValue('K1', 'Phone No.');
     $objPHPExcel->getActiveSheet()->SetCellValue('L1', 'Ship To Location');
     $objPHPExcel->getActiveSheet()->SetCellValue('M1', 'Store Location');
     $objPHPExcel->getActiveSheet()->SetCellValue('N1', 'Email');
     $row = 2;
     if ($records == 'all') {
         $records = $this->portal_model->get_all_records_for_export();
     }
     foreach ($records as $record) {
         $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, htmlspecialchars_decode($record['CustNo'], ENT_QUOTES));
         $objPHPExcel->getActiveSheet()->SetCellValue('B' . $row, $record['Code']);
         $objPHPExcel->getActiveSheet()->SetCellValue('C' . $row, htmlspecialchars_decode($record['Name'], ENT_QUOTES));
         $objPHPExcel->getActiveSheet()->SetCellValue('D' . $row, htmlspecialchars_decode($record['Address'], ENT_QUOTES));
         $objPHPExcel->getActiveSheet()->SetCellValue('E' . $row, htmlspecialchars_decode($record['Address2'], ENT_QUOTES));
         $objPHPExcel->getActiveSheet()->SetCellValue('F' . $row, htmlspecialchars_decode($record['City'], ENT_QUOTES));
         $objPHPExcel->getActiveSheet()->SetCellValue('G' . $row, htmlspecialchars_decode($record['State'], ENT_QUOTES));
         $objPHPExcel->getActiveSheet()->SetCellValue('H' . $row, htmlspecialchars_decode($record['Zip'], ENT_QUOTES));
         $objPHPExcel->getActiveSheet()->SetCellValue('I' . $row, htmlspecialchars_decode($record['Country'], ENT_QUOTES));
         //$objPHPExcel->getActiveSheet()->SetCellValue('J'.$row, $record['Contact']);
         $objPHPExcel->getActiveSheet()->SetCellValue('K' . $row, htmlspecialchars_decode($record['Phone'], ENT_QUOTES));
         $objPHPExcel->getActiveSheet()->SetCellValue('L' . $row, htmlspecialchars_decode($record['isShipTo'], ENT_QUOTES));
         $objPHPExcel->getActiveSheet()->SetCellValue('M' . $row, htmlspecialchars_decode($record['isStore'], ENT_QUOTES));
         $objPHPExcel->getActiveSheet()->SetCellValue('N' . $row, htmlspecialchars_decode($record['Email'], ENT_QUOTES));
         $row++;
     }
     $datestring = date('YmdHisu');
     $objPHPExcel->getActiveSheet()->setTitle('Simple');
     $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
     $objWriter->save('application/files/retailer-survey-' . $datestring . '.xlsx');
     //$objWriter->save(APPPATH . 'third_party/orders-' . date('YmdHisu') . '.csv');
     $objPHPExcel->disconnectWorksheets();
     unset($objPHPExcel);
     header('Pragma: public');
     header('Pragma: no-cache');
     header('Expires: 0');
     header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
     header('Content-Description: File Transfer');
     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8;');
     header('Content-Length: ' . filesize(APPPATH . 'third_party/orders-' . $datestring . '.xlsx'));
     header("Content-Disposition: attachment;filename=transaction-export-" . date('Y-m-d') . ".xlsx");
     header('Content-Transfer-Encoding: binary');
     ob_clean();
     flush();
     if (@readfile('application/files/retailer-survey-' . $datestring . '.xlsx')) {
         return true;
     } else {
         return false;
     }
 }
 public function patient_consumption($period_start = "", $period_end = "")
 {
     $patients = array();
     $oi_drugs = array();
     //get all regimen drugs from OI
     $sql = "SELECT IF(d.drug IS NULL,rd.drugcode,d.drug) as drugname,'' as drugqty\n              FROM regimen_drug rd \n              LEFT JOIN regimen r ON r.id=rd.regimen\n              LEFT JOIN regimen_service_type rst ON rst.id=r.type_of_service\n              LEFT JOIN drugcode d ON d.id=rd.drugcode\n              WHERE rst.name LIKE '%oi%'\n              AND d.drug NOT LIKE '%cot%'\n              GROUP BY drugname";
     $query = $this->db->query($sql);
     $drugs = $query->result_array();
     if ($drugs) {
         foreach ($drugs as $drug) {
             $oi_drugs[$drug['drugname']] = $drug['drugqty'];
         }
     }
     //get all patients dispensed,drug and in this period
     $sql = "SELECT pv.patient_id,CONCAT_WS( '/', MONTH( pv.dispensing_date ) , YEAR( pv.dispensing_date ) ) AS Month_Year, group_concat(d.drug) AS ARVDrug, group_concat(pv.quantity) AS ARVQTY\n\t\t\t  FROM v_patient_visits pv \n\t\t\t  LEFT JOIN drugcode d ON d.id = pv.drug_id\n\t\t\t  WHERE pv.dispensing_date\n\t\t\t  BETWEEN '" . $period_start . "'\n\t\t\t  AND '" . $period_end . "'\n\t\t\t  GROUP BY pv.patient_id, CONCAT_WS( '/', MONTH( pv.dispensing_date ) , YEAR( pv.dispensing_date ) )\n\t\t\t  ORDER BY pv.patient_id";
     $query = $this->db->query($sql);
     $transactions = $query->result_array();
     if ($transactions) {
         foreach ($transactions as $transaction) {
             $oi = $oi_drugs;
             $is_oi = FALSE;
             //split comma seperated drugs to array
             $drugs = $transaction['ARVDrug'];
             $drugs = explode(",", $drugs);
             //split comma seperated qtys to array
             $qtys = $transaction['ARVQTY'];
             $qtys = explode(",", $qtys);
             foreach ($drugs as $index => $drug) {
                 //add drug qtys to oi
                 if (array_key_exists($drug, $oi)) {
                     $is_oi = TRUE;
                     $oi[$drug] = $qtys[$index];
                 }
             }
             //add drug consumption to patient
             if ($is_oi == TRUE) {
                 $patients[$transaction['patient_id']] = $oi;
             }
         }
     }
     //export patient transactions
     $this->load->library('PHPExcel');
     $dir = "Export";
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->setActiveSheetIndex(0);
     /*Delete all files in export folder*/
     if (is_dir($dir)) {
         $files = scandir($dir);
         foreach ($files as $object) {
             if ($object != "." && $object != "..") {
                 unlink($dir . "/" . $object);
             }
         }
     } else {
         mkdir($dir);
     }
     //get columns
     $column = array();
     $letter = 'A';
     while ($letter !== 'AAA') {
         $column[] = $letter++;
     }
     //set col and row indices
     $col = 0;
     $row = 1;
     //wrap header text
     $objPHPExcel->getActiveSheet()->getStyle('A1:A' . $objPHPExcel->getActiveSheet()->getHighestRow())->getAlignment()->setWrapText(true);
     //autosize header
     $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(-1);
     //print
     $objPHPExcel->getActiveSheet()->SetCellValue($column[$col] . $row, "ARTID");
     $col++;
     $objPHPExcel->getActiveSheet()->SetCellValue($column[$col] . $row, "PERIOD");
     foreach ($oi_drugs as $drugname => $header) {
         $col++;
         $objPHPExcel->getActiveSheet()->SetCellValue($column[$col] . $row, $drugname);
     }
     //loop through patient transactions
     foreach ($patients as $art_id => $dispenses) {
         //reset col and row indices
         $col = 0;
         $row++;
         //write art_id and period reporting
         $objPHPExcel->getActiveSheet()->SetCellValue($column[$col] . $row, $art_id);
         $col++;
         $objPHPExcel->getActiveSheet()->SetCellValue($column[$col] . $row, date("m/Y", strtotime($period_start)));
         foreach ($dispenses as $drug_id => $drug_qty) {
             $col++;
             $objPHPExcel->getActiveSheet()->SetCellValue($column[$col] . $row, $drug_qty);
         }
     }
     //Generate file
     ob_start();
     $period_start = date("F-Y", strtotime($period_start));
     $original_filename = "PATIENT DRUG CONSUMPTION[" . $period_start . "].xls";
     $filename = $dir . "/" . urldecode($original_filename);
     $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
     $objWriter->save($filename);
     $objPHPExcel->disconnectWorksheets();
     unset($objPHPExcel);
     if (file_exists($filename)) {
         $filename = str_replace("#", "%23", $filename);
         redirect($filename);
     }
 }
 /**
  * Free memory
  *
  * @return void
  */
 public function freeMemory()
 {
     $this->_xls->disconnectWorksheets();
     unset($this->_xls);
 }
Example #12
0
        $params = $_REQUEST;
        $params['sort_by'] = $price_schema['fields'][Registry::get('addons.price_list.price_list_sorting')]['sort_by'];
        $params['page'] = $page;
        $params['skip_view'] = 'Y';
        fn_price_list_print_products($params, $worksheet, $counter, $row, $width, $selected_fields, $price_schema, $styles);
    }
    foreach ($width as $col => $size) {
        if ($size > MAX_SIZE) {
            $size = MAX_SIZE;
        }
        $worksheet->getColumnDimension($col)->setWidth($size);
    }
    $writer = new PHPExcel_Writer_Excel2007($pexcel);
    $imp_filename = fn_create_temp_file();
    $writer->save($imp_filename);
    $pexcel->disconnectWorksheets();
    unset($pexcel);
    Storage::instance('assets')->put($filename, array('file' => $imp_filename, 'caching' => true));
    fn_echo('<br />' . __('done'));
}
/**
 * Checks if server configuration supports xml creation
 *
 * @return bool False if some components are not installed, otherwise True
 */
function fn_price_list_is_xls_supported()
{
    $result = true;
    // check for ZipArchive class exists
    // phpexcel does not work without zip support
    if (!class_exists('ZipArchive')) {
Example #13
0
 /**
  * Create sheet from query and dump named file to browser.
  * @param $query the query.
  */
 function writeXlsx($query)
 {
     PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
     $objPHPExcel = new PHPExcel();
     if (!isset($this->rowParser)) {
         $this->rowParser = new DefaultRowParser();
     }
     $objPHPExcel->getProperties()->setCreator($this->creator);
     $objPHPExcel->getProperties()->setLastModifiedBy($this->author);
     $objPHPExcel->getProperties()->setTitle($this->title);
     $objPHPExcel->getProperties()->setSubject($this->subject);
     $objPHPExcel->getProperties()->setDescription($this->description);
     $objPHPExcel->getProperties()->setKeywords($this->keywords);
     $objPHPExcel->getProperties()->setCategory($this->catagory);
     global $ADODB_FETCH_MODE;
     $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
     $resultSet = $this->dbConn->Execute($query);
     if ($resultSet === false) {
         die("<br>Cannot get spreadsheet data with <pre>" . $query . "</pre> reason " . $this->dbConn->ErrorMsg() . "<br>");
     }
     //echo $query;
     //$colcount = $resultSet->FieldCount();
     // start writing in 3rd row, top isf for title and link.
     $row = 3;
     $this->tableHeader = $this->rowParser->parseToTableHeader($resultSet);
     $headCount = count($this->tableHeader);
     $headerStyles = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 0, 'color' => array('argb' => 'FFC0C0C0')));
     for ($i = 0; $i < $headCount; $i++) {
         $name = $this->tableHeader[$i];
         $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, $row, $name);
         $coor = XLSWriter::cellCoordinate($i, $row);
         $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles);
     }
     $row++;
     // get types
     $this->columnTypes = $this->rowParser->parseTypes($resultSet);
     $XlsTypes = array();
     //error_log('there are ' . count($this->columnTypes) . ' types from db =' . print_r($this->columnTypes, true), 0);
     for ($i = 0; $i < count($this->columnTypes); $i++) {
         $ftype = PHPExcel_Cell_DataType::TYPE_NUMERIC;
         //error_log("found  type = {$this->columnTypes[$i]} for column {$i}", 0);
         switch ($this->columnTypes[$i]) {
             case 'char':
             case 'bpchar':
             case 'varchar':
             case 'text':
             case 'date':
                 $ftype = PHPExcel_Cell_DataType::TYPE_STRING;
                 break;
             case 'int2':
             case 'int4':
             case 'int8':
             case '_numeric':
             case 'numeric':
             case 'float8':
                 $ftype = PHPExcel_Cell_DataType::TYPE_NUMERIC;
                 break;
             default:
                 $ftype = PHPExcel_Cell_DataType::TYPE_STRING;
                 break;
         }
         $XlsTypes[] = $ftype;
     }
     $cellStyleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 0, 'color' => array('argb' => 'FF0000')));
     $oldValue = '';
     if ($this->firstWeightColumn > 0) {
         // add weights row
         $this->weigthsRow = $row;
         $coor = XLSWriter::cellCoordinate($this->firstWeightColumn - 1, $row);
         $objPHPExcel->getActiveSheet()->setCellValue($coor, 'Weights', PHPExcel_Cell_DataType::TYPE_STRING);
         $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles);
         $weightSum = 0;
         $w = 0;
         ${$weightLast} = count($this->weights) - 1;
         for (; $w < count($this->weights); $w++) {
             $coor = XLSWriter::cellCoordinate($this->firstWeightColumn + $w, $row);
             $weightSum += $this->weights[$w];
             $objPHPExcel->getActiveSheet()->setCellValue($coor, $this->weights[$w], PHPExcel_Cell_DataType::TYPE_NUMERIC);
             $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles);
         }
         $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row);
         $wBegin = XLSWriter::cellCoordinate($this->firstWeightColumn, $row);
         $wEnd = XLSWriter::cellCoordinate($this->firstWeightColumn + ${$weightLast}, $row);
         $formula = "=SUM({$wBegin}:{$wEnd})";
         $objPHPExcel->getActiveSheet()->setCellValue($coor, $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
         $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles);
         $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row - 1);
         $objPHPExcel->getActiveSheet()->setCellValue($coor, 'Total WT', PHPExcel_Cell_DataType::TYPE_STRING);
         $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles);
         $row++;
     }
     while (!$resultSet->EOF) {
         $rowData = $this->rowParser->parse($resultSet);
         $headCount = count($this->tableHeader);
         //$resultSet->FieldCount();
         $changeColor = false;
         if ($this->colorChangerColumn >= 0) {
             if ($oldValue != $rowData[$this->colorChangerColumn]) {
                 $changeColor = true;
                 $oldValue = $rowData[$this->colorChangerColumn];
             }
         } else {
             if ($this->autoZebra) {
                 $changeColor = true;
             }
         }
         if ($changeColor) {
             $cellStyleArray['fill']['color']['argb'] = $this->rainBow->getCurrentAsARGBString();
             $this->rainBow->getNext();
         }
         $i = 0;
         for (; $i < $headCount; $i++) {
             $value = $rowData[$i];
             $coor = XLSWriter::cellCoordinate($i, $row);
             $xlstype = isset($XlsTypes[$i]) ? $XlsTypes[$i] : PHPExcel_Cell_DataType::TYPE_STRING;
             //error_log("writing cell type = {$xlstype} for column {$i}, value {$value}", 0);
             $objPHPExcel->getActiveSheet()->setCellValueExplicit($coor, $value, $xlstype);
             if ($this->columnTypes[$i] == 'date') {
                 $objPHPExcel->getActiveSheet()->getStyle($coor)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
             } else {
                 if ($this->columnTypes[$i] == 'time') {
                     $objPHPExcel->getActiveSheet()->getStyle($coor)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8);
                 }
             }
             $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($cellStyleArray);
         }
         if ($this->weightedSumsColumn >= 0) {
             $weightLast = count($this->weights) - 1;
             $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row);
             $wBegin = XLSWriter::cellCoordinateAbsoluteRow($this->firstWeightColumn, $this->weigthsRow);
             $wEnd = XLSWriter::cellCoordinateAbsoluteRow($this->firstWeightColumn + $weightLast, $this->weigthsRow);
             $rBegin = XLSWriter::cellCoordinate($this->firstWeightColumn, $row);
             $rEnd = XLSWriter::cellCoordinate($this->firstWeightColumn + $weightLast, $row);
             $wSumCoor = XLSWriter::cellCoordinateAbsolute($this->weightedSumsColumn, $this->weigthsRow);
             $formula = "=SUMPRODUCT({$wBegin}:{$wEnd},{$rBegin}:{$rEnd})/{$wSumCoor}";
             $objPHPExcel->getActiveSheet()->setCellValueExplicit($coor, $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
             $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($cellStyleArray);
         }
         $row++;
         $resultSet->moveNext();
     }
     $row = 1;
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $this->linkText);
     $objPHPExcel->getActiveSheet()->getCell('A' . $row)->getHyperlink()->setUrl($this->linkUrl);
     $row++;
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $this->title);
     $objPHPExcel->getActiveSheet()->getStyle('A' . $row)->applyFromArray($headerStyles);
     $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($headerStyles);
     $rightCell1 = XLSWriter::cellCoordinate(min($headCount - 1, 10), $row);
     $objPHPExcel->getActiveSheet()->mergeCells('A' . $row . ':' . $rightCell1);
     $rightCell2 = XLSWriter::cellCoordinate(min($headCount - 1, 10), 1);
     $objPHPExcel->getActiveSheet()->mergeCells('A1:' . $rightCell2);
     // set format
     $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
     $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
     $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
     $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);
     for ($i = 'A', $j = 0; $i <= 'Z' && $j < $headCount; $i++, $j++) {
         $objPHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(true);
         //            $objPHPExcel->getActiveSheet()->getStyle($i . '2')->applyFromArray($styleArray);
     }
     PHPExcel_Calculation::getInstance()->clearCalculationCache();
     PHPExcel_Calculation::getInstance()->disableCalculationCache();
     PHPExcel_Calculation::getInstance()->calculate();
     switch ($this->excelFormat) {
         case 'Excel2007':
             $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
             $this->mimeType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
             break;
         case 'Excel5':
             $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
             $this->mimeType = 'application/vnd.ms-excel';
             break;
         default:
             $objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
             $this->mimeType = 'text/comma-separated-values';
             break;
     }
     $tempFile = tempnam('/tmp/', 'PHPEXCEL');
     // '/tmp/'.$filename;
     $objWriter->setPreCalculateFormulas(true);
     $objWriter->save($tempFile);
     $fp = @fopen($tempFile, 'r');
     if ($fp != false) {
         header("Content-type: " . $this->mimeType);
         header("Pragma: public");
         header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
         header("Content-Length: " . filesize($tempFile));
         header("Content-Disposition: attachment; filename=\"{$this->filename}\"");
         fpassthru($fp);
         fclose($fp);
         $objPHPExcel->disconnectWorksheets();
         unset($objPHPExcel);
         unlink($tempFile);
         exit(0);
     } else {
         echo "cannot copy file {$tempFile} to out stream\n";
     }
 }
Example #14
0
 public function array2excel($data, $output = true)
 {
     //数组转换为excel文件
     $PHPExcel = new PHPExcel();
     $WorkSheet = $PHPExcel->getActiveSheet();
     set_time_limit(0);
     @ini_set('memory_limit', '256M');
     $row_index = 1;
     foreach ($data as $row) {
         //循环赋值
         $col_index = 0;
         foreach ($row as $col) {
             $WorkSheet->setCellValueByColumnAndRow($col_index, $row_index, $col);
             $col_index++;
         }
         $row_index++;
     }
     $Writer = new PHPExcel_Writer_Excel5($PHPExcel);
     $filename = time();
     if ($output) {
         header("Content-Type: application/force-download");
         header("Content-Type: application/octet-stream");
         header("Content-Type: application/download");
         header('Content-Disposition:inline;filename="' . $filename . '.xls"');
         header("Content-Transfer-Encoding: binary");
         header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
         header("Pragma: no-cache");
         $Writer->save('php://output');
         //直接输出
     } else {
         $Writer->save($filename . '.xls');
         //生成为文件
     }
     $PHPExcel->disconnectWorksheets();
     unset($PHPExcel);
 }
 /**
  * Save generated archive file or trigger a direct download if class var
  * $direct_download_archive is set to true
  * @param PHPExcel $obj_excel
  * @param String $period
  * @param String $table_name
  * @param Int $file_counter
  * @param Boolean $clear_excel_object_from_memory
  */
 private static function saveArchiveFile($obj_excel, $period, $table_name, $use_timestamp_as_prefix = true, $file_counter = null, $clear_excel_object_from_memory = true)
 {
     $save_path = self::generateArchiveFilepath($period, $table_name, $use_timestamp_as_prefix, $file_counter);
     $obj_excel_writer = new PHPExcel_Writer_Excel5($obj_excel);
     if (self::$direct_download_archive) {
         $filename = basename($save_path);
         header("Content-Type: application/vnd.ms-excel");
         header("Content-Disposition: attachment; filename={$filename}");
         $obj_excel_writer->save('php://output');
     } else {
         $obj_excel_writer->save($save_path);
         self::$archive_files[$period][$table_name] = str_replace(DIR_FS_ARCHIVES . self::ARCHIVE_PATH, '', $save_path);
     }
     if ($clear_excel_object_from_memory) {
         $obj_excel->disconnectWorksheets();
         unset($obj_excel_writer, $obj_excel);
     } else {
         unset($obj_excel_writer);
     }
 }
Example #16
0
 /**
  * Reset the writer
  *
  * @return void
  */
 public function _reset()
 {
     $this->excel->disconnectWorksheets();
     $this->resetValueBinder();
     unset($this->parsed);
 }
 /**
  * Reset the writer
  * @return void
  */
 protected function _reset()
 {
     $this->excel->disconnectWorksheets();
 }
Example #18
0
 /**
  * Creating a Excel
  * 
  * @Route /new/example/excel
  * 
  */
 public function exportingExcelAction()
 {
     /**
      * An example of excel exporting
      */
     $this->excel = $this->get('PHPExcel');
     $this->excel->createSheet(0);
     $this->excel->getActiveSheet()->setCellValue('A2', "No")->setCellValue('B2', "Name")->setCellValue('C2', "Last Name");
     $objWriter = \PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
     $objWriter->save('php://output');
     $this->excel->disconnectWorksheets();
     $this->app->response()->headers()->set('Content-Disposition', 'attachment; filename="example"');
     $this->app->response()->headers()->set('Cache-Control', 'max-age=0');
     $this->app->contentType(\Raptor\Raptor::EXCEL);
 }