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;
}
/**
* @param string $dirName Target directory for xls files
*/
public function __construct($dirName)
{
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '512MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$this->filename = realpath($dirName . '/') . date("dmY-His") . "_" . uniqid() . ".xlsx";
$this->objPHPExcel = new PHPExcel();
}
public function __construct()
{
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array(' memoryCacheSize ' => '8MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$this->phpExcel = new PHPExcel();
}
/**
*
* @do 构造函数
*
* @access public
* @author Nick
* @copyright rockhippo
* @param -
* @return -
*
*/
public function __construct()
{
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
//创建一个处理对象实例
$this->objPHPExcel = new PHPExcel();
}
private function configExcel($pParamHash)
{
// config PHPExcel
// cache method
switch ($this->getConfig('cache_method')) {
case 'cache_to_discISAM':
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
break;
case 'cache_in_memory_serialized':
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
break;
case 'cache_in_memory':
default:
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
break;
}
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
// create excel object
if (!isset($this->mPHPExcel)) {
$this->mPHPExcel = new PHPExcel();
}
// set doc properties
$this->mPHPExcel->getProperties()->setTitle($pParamHash['workbook']['title']);
// $this->mPHPExcel->getProperties()->setCreator("Maarten Balliauw");
// $this->mPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
// $this->mPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
// $this->mPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
}
/**
* @inheritdoc
*/
public function stream_open($path, $mode, $options, &$opened_path)
{
\PHPExcel_Settings::setCacheStorageMethod(\PHPExcel_CachedObjectStorageFactory::cache_to_sqlite3);
$this->objPHPExcel = new \PHPExcel();
$this->sheet = $this->objPHPExcel->getActiveSheet();
$this->offset = 1;
return parent::stream_open($path, $mode, $options, $opened_path);
}
public function __construct($file_path)
{
require_once "../app/classes/PHPExcel.php";
#将单元格序列化后再进行Gzip压缩，然后保存在内存中
PHPExcel_Settings::setCacheStorageMethod(PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip);
$file_type = PHPExcel_IOFactory::identify($file_path);
$objReader = PHPExcel_IOFactory::createReader($file_type);
self::$objPHPExcel = $objReader->load($file_path);
}
public function exportXLSX(Repository $repository, $contentTypeName, $workspace = 'default', $language = 'default', $viewName = 'exchange')
{
$repository->selectContentType($contentTypeName);
// Select view and fallback if necessary
$contentTypeDefinition = $repository->getContentTypeDefinition();
$viewDefinition = $contentTypeDefinition->getExchangeViewDefinition($viewName);
$viewName = $viewDefinition->getName();
$this->writeln('Connecting repository');
$this->writeln('');
$repository->selectWorkspace($workspace);
$repository->selectLanguage($language);
$repository->selectView($viewName);
/** @var Record[] $records */
$records = $repository->getRecords('', '.id', 1);
if ($records !== false) {
// Create new PHPExcel object
$objPHPExcel = new \PHPExcel();
// use temp folder for processing of large files
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '12MB');
\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
// Set document properties
$objPHPExcel->getProperties()->setCreator("AnyContent CMCK")->setLastModifiedBy("AnyContent CMCK")->setTitle("Full Export from content type " . $contentTypeDefinition->getTitle())->setSubject("AnyContent Export")->setDescription("");
$worksheet = $objPHPExcel->setActiveSheetIndex(0);
$worksheet->setTitle('Export');
$worksheet->setCellValueByColumnAndRow(0, 1, '.id');
$worksheet->getStyleByColumnAndRow(0, 1)->getFont()->setBold(false)->setItalic(true);
$worksheet->setCellValueByColumnAndRow(1, 1, '.revision');
$worksheet->getStyleByColumnAndRow(1, 1)->getFont()->setBold(false)->setItalic(true);
$row = 1;
$column = 2;
foreach ($contentTypeDefinition->getProperties($viewName) as $property) {
$worksheet->setCellValueByColumnAndRow($column, $row, $property);
$worksheet->getStyleByColumnAndRow($column, $row)->getFont()->setBold(true);
$worksheet->getColumnDimensionByColumn($column)->setWidth(20);
$column++;
}
$row++;
foreach ($records as $record) {
$this->writeln('Processing record ' . $record->getID() . ' - ' . $record->getName());
$worksheet->setCellValueByColumnAndRow(0, $row, $record->getID());
$worksheet->setCellValueByColumnAndRow(1, $row, $record->getRevision());
$column = 2;
foreach ($contentTypeDefinition->getProperties($viewName) as $property) {
$worksheet->setCellValueByColumnAndRow($column, $row, $record->getProperty($property));
$column++;
}
$row++;
}
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_start();
$objWriter->save('php://output');
$excelOutput = ob_get_clean();
return $excelOutput;
}
return false;
}
public function setData()
{
$excel_cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$excel_cacheSettings = ['memoryCacheSize ' => '128MB'];
\PHPExcel_Settings::setCacheStorageMethod($excel_cacheMethod, $excel_cacheSettings);
$excel = \PHPExcel_IOFactory::load(\Yii::$app->getModule('data')->importDir . '/' . $this->filename);
$data = $excel->getActiveSheet()->toArray();
unset($excel);
return $data;
}
public function create_worksheet($excel_data = NUll)
{
//check if the excel data has been set if not exit the excel generation
if (count($excel_data) > 0) {
//echo "<pre/>";
//print_r($excel_data);
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '2MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
ini_set('max_execution_time', 123456);
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("CD4");
$objPHPExcel->getProperties()->setLastModifiedBy($excel_data['doc_creator']);
$objPHPExcel->getProperties()->setTitle($excel_data['doc_title']);
$objPHPExcel->getProperties()->setSubject($excel_data['doc_title']);
$objPHPExcel->getProperties()->setDescription("");
// Add some data
// echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0);
$rowExec = 1;
//Looping through the cells
$column = 0;
// foreach ($excel_data['column_data'] as $cell) {
// $objPHPExcel -> getActiveSheet() -> setCellValueByColumnAndRow($column, $rowExec, $cell);
// $objPHPExcel -> getActiveSheet() -> getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($column)) -> setAutoSize(true);
// $column++;
// }
// $rowExec = 2;
// $column = 0;
// foreach ($excel_data['row_data'] as $cell) {
// //Looping through the cells per facility
// $objPHPExcel -> getActiveSheet() -> setCellValueByColumnAndRow($column, $rowExec, $cell);
// $rowExec++;
// $column++;
// }
$objPHPExcel->getActiveSheet()->fromArray($excel_data['row_data'], NULL, 'A1');
// Rename sheet
// echo date('H:i:s') . " Rename sheet\n";
$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('Content-type: application/vnd.ms-excel');
// It will be called file.xls
header("Content-Disposition: attachment; filename=" . $excel_data['file_name']);
// Write file to the browser
$objWriter->save('php://output');
// Echo done
}
}
private function load_resource_sheets()
{
$this->write_log(' Starting reading excel file, this may take minutes, please wait....');
$file_resource_path = $this->file_directory . $this->file_resource_name;
if (file_exists($file_resource_path)) {
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '2GB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$reader = PHPExcel_IOFactory::createReader('Excel2007');
$reader->setReadDataOnly(true);
$this->objPHPExcel_resource = $reader->load($file_resource_path);
}
$this->write_log(' File read. starting convert:');
}
/**
* MySqlExcelBuilder::__construct()
*
* @param mixed $db - Database
* @param mixed $un - User name
* @param mixed $pw - Password
*
*/
public function __construct($db, $un, $pw)
{
$host = 'localhost';
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
$this->phpExcel = new PHPExcel();
$dsn = "mysql:host={$host};port=3306;dbname={$db}";
try {
$this->pdo = new PDO($dsn, $un, $pw);
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
} catch (PDOException $e) {
$this->{$pdo} = null;
error_log("{$dsn}\n" . 'Connection failed: ' . $e->getMessage());
}
}
/**
* Formats the specified response.
* @param \yii\web\Response $response the response to be formatted.
*/
public function format($response)
{
//$response->getHeaders()->set('Content-Type', 'application/vnd.ms-excel');
$response->setDownloadHeaders(basename(\Yii::$app->request->pathInfo) . '.xls', 'application/vnd.ms-excel');
if ($response->data === null) {
return;
}
\PHPExcel_Settings::setCacheStorageMethod(\PHPExcel_CachedObjectStorageFactory::cache_to_sqlite3);
$styles = $this->getStyles();
$objPHPExcel = new \PHPExcel();
$sheet = $objPHPExcel->getActiveSheet();
$offset = 1;
/*
* serialize filter
$sheet->setCellValue('A1', $opcje['nazwaAnaliza']);
$sheet->duplicateStyle($styles['default'], 'A1:C4');
$sheet->getRowDimension(1)->setRowHeight(18);
$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(15);
$sheet->getStyle('C3:C4')->getFont()->setBold(true);
$offset = 6;
*/
$data = $response->data;
if (!isset($data['items'])) {
// single model
$this->addLine($sheet, $offset, array_keys($data));
$this->addLine($sheet, $offset + 1, array_values($data));
for ($i = 1, $lastColumn = 'A'; $i < count($data); $i++, $lastColumn++) {
}
$sheet->duplicateStyle($styles['header'], 'A' . $offset . ':' . $lastColumn . $offset);
} else {
// a collection of models
if (($firstRow = reset($data['items'])) !== false) {
$this->addLine($sheet, $offset, array_keys($firstRow));
}
$startOffset = ++$offset;
$item = [];
foreach ($data['items'] as $item) {
$this->addLine($sheet, $offset++, $item);
}
$this->addSummaryRow($sheet, $startOffset, $offset, $item);
}
$filename = tempnam(\Yii::getAlias('@runtime'), 'xls');
$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save($filename);
$response->content = file_get_contents($filename);
unlink($filename);
}
function __construct($nom_archivo, $titulo)
{
//ini_set('memory_limit','512M');
set_time_limit(400);
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '10MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$this->docexcel = new PHPExcel();
/*"../../reportes_generados/$nom_archivo");*/
$this->docexcel->getProperties()->setCreator("XPHS")->setLastModifiedBy("XPHS")->setTitle($titulo)->setSubject($titulo)->setDescription('Reporte "' . $titulo . '", generado por el framework XPHS')->setKeywords("office 2007 openxml php")->setCategory("Report File");
$this->docexcel->setActiveSheetIndex(0);
$this->docexcel->getActiveSheet()->setTitle($titulo);
$this->nombre_archivo = $nom_archivo;
$this->titulo = $titulo;
$this->fila = 1;
$this->equivalencias = array(0 => 'A', 1 => 'B', 2 => 'C', 3 => 'D', 4 => 'E', 5 => 'F', 6 => 'G', 7 => 'H', 8 => 'I', 9 => 'J', 10 => 'K', 11 => 'L', 12 => 'M', 13 => 'N', 14 => 'O', 15 => 'P', 16 => 'Q', 17 => 'R', 18 => 'S', 19 => 'T', 20 => 'U', 21 => 'V', 22 => 'W', 23 => 'X', 24 => 'Y', 25 => 'Z', 26 => 'AA', 27 => 'AB', 28 => 'AC', 29 => 'AD', 30 => 'AE', 31 => 'AF', 32 => 'AG', 33 => 'AH', 34 => 'AI', 35 => 'AJ', 36 => 'AK', 37 => 'AL', 38 => 'AM', 39 => 'AN', 40 => 'AO', 41 => 'AP', 42 => 'AQ', 43 => 'AR', 44 => 'AS', 45 => 'AT', 46 => 'AU', 47 => 'AV', 48 => 'AW', 49 => 'AX', 50 => 'AY', 51 => 'AZ', 52 => 'BA', 53 => 'BB', 54 => 'BC', 55 => 'BD', 56 => 'BE', 57 => 'BF', 58 => 'BG', 59 => 'BH', 60 => 'BI', 61 => 'BJ', 62 => 'BK', 63 => 'BL', 64 => 'BM', 65 => 'BN', 66 => 'BO', 67 => 'BP', 68 => 'BQ', 69 => 'BR', 70 => 'BS', 71 => 'BT', 72 => 'BU', 73 => 'BV', 74 => 'BW', 75 => 'BX', 76 => 'BY', 77 => 'BZ');
}
public function read($filePath, $type = 'xls')
{
$readerObj = null;
$type = strtolower($type);
\PHPExcel_Settings::setCacheStorageMethod(\PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip, array());
if ($type == 'xls') {
$readerObj = $this->_getImportExtendInstanceForXls();
} else {
if ($type == 'xlsx') {
$readerObj = $this->_getImportExtendInstanceForXlsx();
}
}
if (empty($readerObj)) {
return false;
}
$excelExtendObj = $readerObj->load($filePath);
if ($excelExtendObj->getSheetCount() < 1) {
return false;
}
$allData = array();
foreach ($excelExtendObj->getAllSheets() as $sheet) {
$sheetData = $sheet->toArray();
$dataCount = count($sheetData);
if ($dataCount <= 1) {
continue;
}
$fields = array();
$dataSet = array();
foreach ($sheetData as $rowIndex => $rowData) {
foreach ($rowData as $cellIndex => $cellValue) {
if ($rowIndex == 0) {
$fields[$cellIndex] = $cellValue;
} else {
if (!isset($dataSet[$rowIndex - 1])) {
$dataSet[$rowIndex - 1] = array();
}
$dataSet[$rowIndex - 1][$fields[$cellIndex]] = $rowData[$cellIndex];
}
}
}
$title = trim($sheet->getTitle());
$allData[$title] = $dataSet;
}
return $allData;
}
/**
* Read given csv file and write all rows to given xls file
*
* @param string $csv_file Resource path of the csv file
* @param string $xls_file Resource path of the excel file
* @param string $csv_enc Encoding of the csv file, use utf8 if null
* @throws Exception
*/
public static function convert($csv_file, $xls_file, $csv_enc = null)
{
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
$cacheSettings = array('dir' => 'E:/PHPExcel_cache');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
} else {
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
}
//open csv file
$objReader = new PHPExcel_Reader_CSV();
$objReader->setDelimiter(CSVToExcelConverter::guessDelimiter($csv_file));
if ($csv_enc != null) {
$objReader->setInputEncoding($csv_enc);
}
$objPHPExcel_CSV = $objReader->load($csv_file);
$in_sheet = $objPHPExcel_CSV->getActiveSheet();
//open excel file
$objPHPExcel_XLSX = new PHPExcel();
$out_sheet = $objPHPExcel_XLSX->getActiveSheet();
$out_sheet->setTitle('File Content');
//row index start from 1
$row_index = 0;
foreach ($in_sheet->getRowIterator() as $row) {
//if($row_index==20)
// break;
$row_index++;
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
//column index start from 0
$column_index = -1;
foreach ($cellIterator as $cell) {
$column_index++;
$out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell->getValue());
}
}
//write excel file
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel_XLSX);
$objWriter->save($xls_file);
}
/**
*
* set mode driver
*/
public function setMode($driver)
{
$this->driver = $driver;
$path = dirname($this->getPath()) . '/';
$host = $this->setup->getAddress() . '/';
switch ($driver) {
case 'excel2003':
$phpexcel = BASEPATH . 'protected/lib/excel/';
define('PHPEXCEL_ROOT', $phpexcel);
set_include_path(get_include_path() . PATH_SEPARATOR . $phpexcel);
require_once 'PHPExcel.php';
$this->rpt = new PHPExcel();
$this->exportedDir['excel_path'] = $host . 'exported/excel/';
$this->exportedDir['full_path'] = $path . 'exported/excel/';
break;
case 'excel2007':
//phpexcel
$phpexcel = BASEPATH . 'protected/lib/excel/';
define('PHPEXCEL_ROOT', $phpexcel);
set_include_path(get_include_path() . PATH_SEPARATOR . $phpexcel);
require_once 'PHPExcel.php';
$this->rpt = new PHPExcel();
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_sqlite3;
$cacheSettings = array('cacheTime' => 600);
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$this->exportedDir['excel_path'] = $host . 'exported/excel/';
$this->exportedDir['full_path'] = $path . 'exported/excel/';
break;
case 'pdf':
require_once BASEPATH . 'protected/lib/tcpdf/tcpdf.php';
$this->rpt = new TCPDF();
$this->rpt->setCreator($this->Application->getID());
$this->rpt->setAuthor($this->setup->getSettingValue('config_name'));
$this->rpt->setPrintHeader(false);
$this->rpt->setPrintFooter(false);
$this->exportedDir['pdf_path'] = $host . 'exported/pdf/';
$this->exportedDir['full_path'] = $path . 'exported/pdf/';
break;
}
}
/**
* Constructor function
*
* @param String File path
* @param Boolean Do we read columns from first row
*/
public function __construct($file, $readColumns = true)
{
// Check if we have PHPExcel
if (!class_exists('PHPExcel')) {
throw new PHPExcelFormatterException('PHPExcel class not found. Please include it.');
}
// Set file
$this->_file = $file;
// Initiate PHPExcel cache
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '32MB');
\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
// Create PHPExcel object
$excelObj = new \PHPExcel();
$inputFileType = \PHPExcel_IOFactory::identify($this->_file);
$readerObj = \PHPExcel_IOFactory::createReader($inputFileType);
$readerObj->setReadDataOnly(true);
// Load file to a PHPExcel Object
$excelObj = $readerObj->load($this->_file);
// Set worksheet
$this->_worksheetObj = $excelObj->setActiveSheetIndex(0);
$this->_highestRow = $this->_worksheetObj->getHighestRow();
$this->_highestColumn = $this->_worksheetObj->getHighestColumn();
$this->_highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($this->_highestColumn);
// If we need to read columns from first row
if ($readColumns) {
// If first row is columns, don't add it to formatted data
$this->_startingRow = 2;
$row = 1;
for ($col = 0; $col < $this->_highestColumnIndex; ++$col) {
$value = $this->_worksheetObj->getCellByColumnAndRow($col, $row)->getValue();
$columns[$col] = $value;
$this->_columnNumbers[$value] = $col;
}
// Set columns
$this->setColumns($columns);
}
}
* @category PHPExcel
* @package PHPExcel
* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
* @version ##VERSION##, ##DATE##
*/
/** Error reporting */
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');
/** Include PHPExcel */
require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
if (!PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
die($cacheMethod . " caching method is not available" . EOL);
}
echo date('H:i:s'), " Enable Cell Caching using ", $cacheMethod, " method", EOL;
// Create new PHPExcel object
echo date('H:i:s'), " Create new PHPExcel object", EOL;
$objPHPExcel = new PHPExcel();
// Set document properties
echo date('H:i:s'), " Set properties", EOL;
$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");
// Create a first sheet
echo date('H:i:s'), " Add data", EOL;
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', "Firstname");
$objPHPExcel->getActiveSheet()->setCellValue('B1', "Lastname");
$objPHPExcel->getActiveSheet()->setCellValue('C1', "Phone");
function setGet()
{
if (isset($_GET["get"])) {
$this->cp->cp->showLayout = false;
$this->get = mysql_escape_string($_GET["get"]);
$attr = array("accountID");
switch ($this->get) {
case 'mapa':
include 'vista/ralarma_mapa.phtml';
$info = array("par" => $_GET["par"], "lat" => $_GET["lat"], "lon" => $_GET["lon"]);
echo "<div id='info' style='display:none;'>" . json_encode($info) . "</div>";
switch ($_GET["par"]) {
case "1":
//vel
break;
case "2":
//time
break;
case "3":
//geoz
$this->poligono = $this->poMP->find($_GET["pol"]);
$this->puntos = $this->poMP->fetchPuntos($this->poligono->ID_POLIGONO);
echo "<div id='pol' style='display:none;'>";
echo json_encode($this->puntos);
echo "</div>";
break;
case "4":
//geof
break;
case "5":
//pint
$this->obj = $this->piMP->find($_GET["pol"]);
echo "<div id='pint' style='display:none;'>";
echo json_encode($this->obj);
echo "</div>";
break;
}
break;
case 'descargar':
$ini = strtotime($_GET["fecha_ini"] . " " . $_GET["hrs_ini"] . ":" . $_GET["min_ini"] . ":00");
$fin = strtotime($_GET["fecha_fin"] . " " . $_GET["hrs_fin"] . ":" . $_GET["min_fin"] . ":00");
$fini = $_GET["fecha_ini"] . " " . $_GET["hrs_ini"] . ":" . $_GET["min_ini"] . ":00";
$ffin = $_GET["fecha_fin"] . " " . $_GET["hrs_fin"] . ":" . $_GET["min_fin"] . ":00";
$rep = null;
if ($_GET["id_device"] == "0") {
$gr = $this->dgMP->find($_GET["id_grupo"], $attr);
if ($gr->accountID == $this->cp->getSession()->get("accountID")) {
$de = $this->deMP->fetchByGrupo($_GET["id_grupo"]);
$dev = array();
$license = array();
foreach ($de as $d) {
$dev[] = $d->deviceID;
$license[$d->deviceID] = $d->licensePlate;
$nombre[$d->deviceID] = $d->displayName;
}
$rep = $this->alMP->reporte($ini, $fin, $dev);
}
} else {
$dev = $this->deMP->find($_GET["id_device"], array("accountID", "licensePlate", "displayName"));
$license[$_GET["id_device"]] = $dev->licensePlate;
$nombre[$_GET["id_device"]] = $dev->displayName;
if ($dev->accountID == $this->cp->getSession()->get("accountID")) {
$rep = $this->alMP->reporte($ini, $fin, array($_GET["id_device"]));
}
}
if ($rep != null) {
require_once 'modelo/DireccionMP.php';
require_once 'Classes/PHPExcel.php';
$this->diMP = new DireccionMP();
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array(' memoryCacheSize ' => '8MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("ViaGPS")->setTitle("Reporte de Alarmas " . $ini . " - " . $fin)->setSubject("Reporte de Alarmas " . $ini . " - " . $fin)->setDescription("Reporte de Alarmas " . $ini . " - " . $fin);
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Alarmas');
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load("plantilla.xls");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, 2, 'Reporte de Alarmas')->setCellValueByColumnAndRow(5, 3, utf8_encode('Periodo de tiempo: ') . $fini . " / " . $ffin);
$columnas = array("Fecha", "Vehiculo", "Patente", "Direccion", "Comuna", "Region", "Velocidad", "Encendido", "Alarma", "Regla");
$nCol = count($columnas);
$rowIni = 7;
for ($i = 0; $i < $nCol; $i++) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i + 1, $rowIni - 1, utf8_encode($columnas[$i]));
}
$km = 0;
$i = 0;
foreach ($rep as $r) {
$dir = $this->getDireccion($r->latitude, $r->longitude);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $rowIni + $i, $r->fecha)->setCellValueByColumnAndRow(2, $rowIni + $i, $nombre[$r->deviceID])->setCellValueByColumnAndRow(3, $rowIni + $i, $license[$r->deviceID])->setCellValueByColumnAndRow(4, $rowIni + $i, $dir->DIRECCION)->setCellValueByColumnAndRow(5, $rowIni + $i, $dir->COMUNA)->setCellValueByColumnAndRow(6, $rowIni + $i, $dir->REGION)->setCellValueByColumnAndRow(7, $rowIni + $i, round($r->speedKPH))->setCellValueByColumnAndRow(8, $rowIni + $i, $r->encendido == "1" ? "Si" : "No")->setCellValueByColumnAndRow(9, $rowIni + $i, $r->NOM_ALERTA)->setCellValueByColumnAndRow(10, $rowIni + $i, utf8_encode(strip_tags($this->traduceRegla($r))));
$i++;
}
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="reporte_alarma_' . $ini . '_' . $fin . '.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
break;
case 'reporte':
$ini = strtotime($_POST["fecha_ini"] . " " . $_POST["hrs_ini"] . ":" . $_POST["min_ini"] . ":00");
$fin = strtotime($_POST["fecha_fin"] . " " . $_POST["hrs_fin"] . ":" . $_POST["min_fin"] . ":00");
$rep = null;
if ($_POST["id_device"] == "0") {
$gr = $this->dgMP->find($_POST["id_grupo"], $attr);
if ($gr->accountID == $this->cp->getSession()->get("accountID")) {
$de = $this->deMP->fetchByGrupo($_POST["id_grupo"]);
$dev = array();
$license = array();
foreach ($de as $d) {
$dev[] = $d->deviceID;
$license[$d->deviceID] = $d->licensePlate;
$nombre[$d->deviceID] = $d->displayName;
$vehicle[$d->deviceID] = $d->vehicleID;
}
$rep = $this->alMP->reporte($ini, $fin, $dev);
$this->sensor = $this->sdMP->fetchByDevices($dev);
}
} else {
$dev = $this->deMP->find($_POST["id_device"], array("accountID", "licensePlate", "vehicleID", "displayName"));
$this->sensor = $this->sdMP->fetchByDevice($_POST["id_device"], $dev->accountID);
$license[$_POST["id_device"]] = $dev->licensePlate;
$nombre[$_POST["id_device"]] = $dev->displayName;
$vehicle[$_POST["id_device"]] = $dev->vehicleID;
if ($dev->accountID == $this->cp->getSession()->get("accountID")) {
$rep = $this->alMP->reporte($ini, $fin, array($_POST["id_device"]));
}
}
// print_r($this->sensor);
if ($rep != null) {
foreach ($rep as $r) {
$out[] = array("licensePlate" => $license[$r->deviceID], "vehicleID" => $vehicle[$r->deviceID], "displayName" => $nombre[$r->deviceID], "fecha" => $r->fecha, "latitude" => $r->latitude, "longitude" => $r->longitude, "encendido" => $r->encendido, "alarma" => $r->NOM_ALERTA, "regla" => utf8_encode($this->traduceRegla($r)), "velocidad" => round($r->speedKPH), "heading" => $r->heading);
}
echo json_encode($out);
}
break;
}
}
}
function index()
{
$this->load->library('excel2');
$path = "D:/xampp/htdocs/simkeu-bpihv2/[upload]/2015pendaftarnJanMar_copy.xlsx";
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '8MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objPHPExcel = PHPExcel_IOFactory::load($path);
$worksheet = $objPHPExcel->setActiveSheetIndex(0);
$maxCell = $worksheet->getHighestRowAndColumn();
$excel = $worksheet->rangeToArray('A1:' . $maxCell['column'] . $maxCell['row']);
$excel = array_map('array_filter', $excel);
$excel = array_filter($excel);
/**/
$max_kolom = 0;
$max_baris = 0;
$max_baris = $this->excel2->endKey($excel);
echo "<br>";
for ($i = 0; $i <= $max_baris; $i++) {
$baris = $this->excel2->endKey($excel[$i]);
if ($baris > $max_kolom) {
$max_kolom = $baris;
}
}
echo "max baris : " . $max_baris;
echo "<br>";
echo "max kolom : " . $max_kolom;
echo "<br>";
echo '<br><table border="1"><tr>';
/*
foreach ($excel as $key => $values)
{
echo '<tr>';
foreach ($values as $anotherkey => $val)
{
echo 'key:'.$key. ' AnotherKey: '.$anotherkey.' value:'.$val.'<br>';
echo '<td>' . $val . '</td>';
}
echo '</tr>';
}
*/
//$stop = false;
//while ($stop = true) {
for ($i = 2; $i <= $max_baris; $i++) {
$data3['kode_unit'] = ' ';
$data3['kd_porsi'] = ' ';
$data3['no_porsi'] = ' ';
$data3['bps_lama'] = ' ';
$data3['bps_baru'] = ' ';
$data3['bps'] = ' ';
$data3['kd_calhaj'] = ' ';
$data3['cbg_calhaj'] = ' ';
$data3['na_calhaj'] = ' ';
$data3['nayah_calhaj'] = ' ';
$data3['norek'] = ' ';
$data3['nil_setor'] = ' ';
$data3['j_aktif'] = ' ';
$data3['j_jamaah'] = ' ';
$data3['kd_pihk'] = ' ';
$data3['pihk'] = ' ';
$data3['j_bayar'] = ' ';
$data3['thn_daftar'] = ' ';
$data3['tgl_batal'] = ' ';
$data3['psddka8'] = ' ';
$data3['psdskdp'] = ' ';
$data3['kd_spph'] = ' ';
$data3['jmhdat8_spph'] = ' ';
$data3['tgl_plnsn'] = ' ';
$data3['kd_user'] = '******';
$data3['kd_user_baru'] = ' ';
$data3['alt_calhaj'] = ' ';
$data3['tgl_setor'] = ' ';
$data3['cuid'] = ' ';
$data3['cdate'] = ' ';
$data3['muid'] = ' ';
$data3['mdate'] = ' ';
$data3['status'] = ' ';
echo '<tr>';
$data3['kode_unit'] = "010101";
$data3['kd_porsi'] = $excel[$i]['0'];
$data3['no_porsi'] = $excel[$i]['19'];
$data3['bps_lama'] = $excel[$i]['1'];
$data3['bps_baru'] = $excel[$i]['24'];
$data3['bps'] = $excel[$i]['2'];
$data3['kd_calhaj'] = $excel[$i]['3'];
$data3['cbg_calhaj'] = $excel[$i]['4'];
$data3['na_calhaj'] = $excel[$i]['5'];
$data3['nayah_calhaj'] = $excel[$i]['6'];
$data3['norek'] = $excel[$i]['7'];
$data3['nil_setor'] = $excel[$i]['8'];
$data3['j_aktif'] = $excel[$i]['9'];
$data3['j_jamaah'] = $excel[$i]['10'];
$data3['kd_pihk'] = $excel[$i]['11'];
$data3['pihk'] = $excel[$i]['12'];
$data3['j_bayar'] = $excel[$i]['13'];
$data3['thn_daftar'] = $excel[$i]['14'];
$data3['tgl_batal'] = $excel[$i]['15'];
$data3['kanwil_no_srt'] = $excel[$i]['16'];
$data3['psddka8'] = $excel[$i]['17'];
$data3['psdskdp'] = $excel[$i]['18'];
$data3['kd_spph'] = $excel[$i]['19'];
$data3['jmhdat8_spph'] = date('Y-m-d', strtotime($excel[$i]['20']));
$data3['tgl_plnsn'] = $excel[$i]['21'];
$data3['kd_user'] = $excel[$i]['22'];
$data3['kd_user_baru'] = $excel[$i]['25'];
$data3['no_surat'] = $excel[$i]['23'];
$data3['alt_calhaj'] = "";
$data3['tgl_setor'] = date('Y-m-d', strtotime($excel[$i]['20']));
$data3['cuid'] = "";
$data3['cdate'] = date("Y-m-d");
$data3['muid'] = "";
$data3['mdate'] = date("Y-m-d");
$data3['status'] = "";
echo '<td>';
echo $i;
echo '</td>';
echo '<td>';
echo $data3['kode_unit'];
echo '</td>';
echo '<td>';
echo $data3['no_porsi'];
echo '</td>';
echo '<td>';
echo $data3['bps_lama'];
echo '</td>';
echo '<td>';
echo $data3['bps_baru'];
echo '</td>';
echo '<td>';
echo $data3['bps'];
echo '</td>';
echo '<td>';
echo $data3['kd_calhaj'];
echo '</td>';
echo '<td>';
echo $data3['cbg_calhaj'];
echo '</td>';
echo '<td>';
echo $data3['na_calhaj'];
echo '</td>';
echo '<td>';
echo $data3['nayah_calhaj'];
echo '</td>';
echo '<td>';
echo $data3['norek'];
echo '</td>';
echo '<td>';
echo $data3['nil_setor'];
echo '</td>';
echo '<td>';
echo $data3['j_aktif'];
echo '</td>';
echo '<td>';
echo $data3['j_jamaah'];
echo '</td>';
echo '<td>';
echo $data3['kd_pihk'];
echo '</td>';
echo '<td>';
echo $data3['pihk'];
echo '</td>';
echo '<td>';
echo $data3['j_bayar'];
echo '</td>';
echo '<td>';
echo $data3['thn_daftar'];
echo '</td>';
echo '<td>';
echo $data3['tgl_batal'];
echo '</td>';
echo '<td>';
echo $data3['kanwil_no_srt'];
echo '</td>';
echo '<td>';
echo $data3['psddka8'];
echo '</td>';
echo '<td>';
echo $data3['psdskdp'];
echo '</td>';
echo '<td>';
echo $data3['kd_spph'];
echo '</td>';
echo '<td>';
echo $data3['jmhdat8_spph'];
echo '</td>';
echo '<td>';
echo $data3['tgl_plnsn'];
echo '</td>';
echo '<td>';
echo $data3['kd_user'];
echo '</td>';
echo '<td>';
echo $data3['kd_user_baru'];
echo '</td>';
echo '<td>';
echo $data3['no_surat'];
echo '</td>';
echo '<td>';
echo $data3['alt_calhaj'];
echo '</td>';
echo '<td>';
echo $data3['tgl_setor'];
echo '</td>';
echo '<td>';
echo $data3['cuid'];
echo '</td>';
echo '<td>';
echo $data3['cdate'];
echo '</td>';
echo '<td>';
echo $data3['muid'];
echo '</td>';
echo '<td>';
echo $data3['mdate'];
echo '</td>';
echo '<td>';
echo $data3['status'];
/**
* @author caochunhui@dachuwang.com
* @description 用数组和地址直接生成excel文件
* 每一个数组占一个sheet
*/
private function _convert_array_to_excel($arr = array(), $sheet_titles = array(), $out_name = '', $barcode_arr = array())
{
//下面的代码是抄的。
//set cache
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
//open excel file
$write_objPHPExcel = new PHPExcel();
$write_objPHPExcel->getDefaultStyle()->getFont()->setName('simsun')->setSize(10);
//下面要循环了
$sheet_cnt = 0;
foreach ($arr as $item) {
//用订单id.csv来命名每一个sheet
$out_sheet = new PHPExcel_Worksheet($write_objPHPExcel, $sheet_titles[$sheet_cnt]);
//$out_sheet->setTitle($item);
//row index start from 1
$row_index = 0;
foreach ($item as $row) {
$row_index++;
//$cellIterator = $row->getCellIterator();
//$cellIterator->setIterateOnlyExistingCells(false);
//column index start from 0
$column_index = -1;
foreach ($row as $cell) {
$column_index++;
//var_dump($cell);
$out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell, PHPExcel_Cell_DataType::TYPE_STRING);
}
}
//如果条码数组不为空，那么说明需要在sheet里插入条码
if (!empty($barcode_arr) && isset($barcode_arr[$sheet_cnt])) {
$barcode_download_res = $this->_download_barcode($barcode_arr[$sheet_cnt]);
if ($barcode_download_res['code'] == 200) {
//no pic you say a jb
$pic_path = $barcode_download_res['file'];
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('barcode');
$objDrawing->setDescription('');
$objDrawing->setPath($pic_path);
$objDrawing->setHeight(50);
$objDrawing->setCoordinates('D26');
//$objDrawing->setOffsetX(10);
//$objDrawing->getShadow()->setVisible(true);
//$objDrawing->getShadow()->setDirection(36);
$objDrawing->setWorksheet($out_sheet);
//no pic you say a jb
}
}
$write_objPHPExcel->addSheet($out_sheet);
$sheet_cnt++;
}
$write_objPHPExcel->removeSheetByIndex(0);
//删除第一个空sheet
//上面要循环了
//上面的代码是抄的
//write excel file
$objWriter = new PHPExcel_Writer_Excel2007($write_objPHPExcel);
$dir_name = dirname($out_name);
if (!is_dir($dir_name)) {
$res = mkdir($dir_name, 0777, TRUE);
}
$objWriter->save($out_name);
}
public function download($export_type, $offset = null, $rows = null, $min_id = null, $max_id = null)
{
// we use our own error handler
global $registry;
$registry = $this->registry;
set_error_handler('error_handler_for_export_import', E_ALL);
register_shutdown_function('fatal_error_shutdown_handler_for_export_import');
// Use the PHPExcel package from http://phpexcel.codeplex.com/
$cwd = getcwd();
chdir(DIR_SYSTEM . 'PHPExcel');
require_once 'Classes/PHPExcel.php';
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_ExportImportValueBinder());
chdir($cwd);
// find out whether all data is to be downloaded
$all = !isset($offset) && !isset($rows) && !isset($min_id) && !isset($max_id);
// Memory Optimization
if ($this->config->get('export_import_settings_use_export_cache')) {
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '16MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
}
try {
// set appropriate timeout limit
set_time_limit(1800);
$languages = $this->getLanguages();
$default_language_id = $this->getDefaultLanguageId();
// create a new workbook
$workbook = new PHPExcel();
// set some default styles
$workbook->getDefaultStyle()->getFont()->setName('Arial');
$workbook->getDefaultStyle()->getFont()->setSize(10);
//$workbook->getDefaultStyle()->getAlignment()->setIndent(0.5);
$workbook->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$workbook->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$workbook->getDefaultStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
// pre-define some commonly used styles
$box_format = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'F0F0F0')));
$text_format = array('numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_TEXT));
$price_format = array('numberformat' => array('code' => '######0.00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT));
$weight_format = array('numberformat' => array('code' => '##0.00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT));
// create the worksheets
$worksheet_index = 0;
switch ($export_type) {
case 'c':
// creating the Categories worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Categories');
$this->populateCategoriesWorksheet($worksheet, $languages, $box_format, $text_format, $offset, $rows, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the CategoryFilters worksheet
if ($this->existFilter()) {
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('CategoryFilters');
$this->populateCategoryFiltersWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
}
break;
case 'p':
// creating the Products worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Products');
$this->populateProductsWorksheet($worksheet, $languages, $default_language_id, $price_format, $box_format, $weight_format, $text_format, $offset, $rows, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the AdditionalImages worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('AdditionalImages');
$this->populateAdditionalImagesWorksheet($worksheet, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the Specials worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Specials');
$this->populateSpecialsWorksheet($worksheet, $default_language_id, $price_format, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the Discounts worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Discounts');
$this->populateDiscountsWorksheet($worksheet, $default_language_id, $price_format, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the Rewards worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Rewards');
$this->populateRewardsWorksheet($worksheet, $default_language_id, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the ProductOptions worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('ProductOptions');
$this->populateProductOptionsWorksheet($worksheet, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the ProductOptionValues worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('ProductOptionValues');
$this->populateProductOptionValuesWorksheet($worksheet, $price_format, $box_format, $weight_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the ProductAttributes worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('ProductAttributes');
$this->populateProductAttributesWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the ProductFilters worksheet
if ($this->existFilter()) {
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('ProductFilters');
$this->populateProductFiltersWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
}
break;
case 'o':
// creating the Options worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Options');
$this->populateOptionsWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the OptionValues worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('OptionValues');
$this->populateOptionValuesWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
break;
case 'a':
// creating the AttributeGroups worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('AttributeGroups');
$this->populateAttributeGroupsWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the Attributes worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Attributes');
$this->populateAttributesWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
break;
case 'f':
if (!$this->existFilter()) {
throw new Exception($this->language->get('error_filter_not_supported'));
break;
}
// creating the FilterGroups worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('FilterGroups');
$this->populateFilterGroupsWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the Filters worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Filters');
$this->populateFiltersWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
break;
default:
break;
}
$workbook->setActiveSheetIndex(0);
// redirect output to client browser
$datetime = date('Y-m-d');
switch ($export_type) {
case 'c':
$filename = 'categories-' . $datetime;
if (!$all) {
if (isset($offset)) {
$filename .= "-offset-{$offset}";
} else {
if (isset($min_id)) {
$filename .= "-start-{$min_id}";
}
}
if (isset($rows)) {
$filename .= "-rows-{$rows}";
} else {
if (isset($max_id)) {
$filename .= "-end-{$max_id}";
}
}
}
$filename .= '.xlsx';
break;
case 'p':
$filename = 'products-' . $datetime;
if (!$all) {
if (isset($offset)) {
$filename .= "-offset-{$offset}";
} else {
if (isset($min_id)) {
$filename .= "-start-{$min_id}";
}
}
if (isset($rows)) {
$filename .= "-rows-{$rows}";
} else {
if (isset($max_id)) {
$filename .= "-end-{$max_id}";
}
}
}
$filename .= '.xlsx';
break;
case 'o':
$filename = 'options-' . $datetime . '.xlsx';
break;
case 'a':
$filename = 'attributes-' . $datetime . '.xlsx';
break;
case 'f':
if (!$this->existFilter()) {
throw new Exception($this->language->get('error_filter_not_supported'));
break;
}
$filename = 'filters-' . $datetime . '.xlsx';
break;
default:
$filename = $datetime . '.xlsx';
break;
}
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($workbook, 'Excel2007');
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('php://output');
// Clear the spreadsheet caches
$this->clearSpreadsheetCache();
exit;
} catch (Exception $e) {
$errstr = $e->getMessage();
$errline = $e->getLine();
$errfile = $e->getFile();
$errno = $e->getCode();
$this->session->data['export_import_error'] = array('errstr' => $errstr, 'errno' => $errno, 'errfile' => $errfile, 'errline' => $errline);
if ($this->config->get('config_error_log')) {
$this->log->write('PHP ' . get_class($e) . ': ' . $errstr . ' in ' . $errfile . ' on line ' . $errline);
}
return;
}
}
function read_excel($out, $in_file, $options = array())
{
if (!isset($options["calculated"])) {
$options["calculated"] = false;
}
$calculated = $options["calculated"];
// キャッシュを/tmpに変更する。
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
$cacheSettings = array("dir" => "/tmp");
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
// EXCELオブジェクトを読む
$xls_reader = PHPExcel_IOFactory::createReader("Excel2007");
$xls_reader->setReadDataOnly(true);
$xls_object = $xls_reader->load($in_file);
$num_sheets = $xls_object->getSheetCount();
// シートを読む
for ($sheet = 0; $sheet < $num_sheets; $sheet++) {
$xls_object->setActiveSheetIndex($sheet);
$xls_sheet = $xls_object->getActiveSheet();
$worksheet_name = $xls_sheet->getTitle();
$headers = explode(":", "^:" . $options["inheader"] . ":\$");
// ワークシートの先頭行に "# ファイル名" を出力する。
fputs($out, "# {$worksheet_name}\n");
// 行番号は1から始まるのでHighestRowは総行数と同じ。
$num_rows = $xls_sheet->getHighestRow();
// columnIndexFromStringは1から始まる（columnIndexFromString('0')==1）のでHighestColumnは総列数と同じ。
$num_cols = PHPExcel_Cell::columnIndexFromString($xls_sheet->getHighestColumn());
// 行を読む
for ($row = 0; $row < $num_rows; $row++) {
// 列を読む
$csv_record = array();
for ($col = 0; $col < $num_cols; $col++) {
// getCellByColumnAndRowの列番号は0から始まり、行番号は1から始まる。
if ($calculated) {
$value = (string) $xls_sheet->getCellByColumnAndRow($col, $row + 1)->getCalculatedValue();
} else {
$value = (string) $xls_sheet->getCellByColumnAndRow($col, $row + 1)->getValue();
// if (substr($value, 0, 1) == "=") $value = "E"; // buggy for expression?
}
$csv_record[$col] = trim(normalize_string($value));
}
// 行を出力する。
if (($header = array_shift($headers)) !== null) {
// ヘッダを出力する。
if ($header == "^") {
$num_cols = count($csv_record);
$fields = array_fill(0, $num_cols, "");
$types = array_fill(0, $num_cols, "text");
$params = array_fill(0, $num_cols, "");
$titles = array_fill(0, $num_cols, "");
$header = array_shift($headers);
}
if ($header == "F") {
$fields = array_replace($fields, $csv_record);
} else {
if ($header == "C") {
$types = array_replace($types, $csv_record);
} else {
if ($header == "P") {
$params = array_replace($params, $csv_record);
} else {
if ($header == "T") {
$titles = array_replace($titles, $csv_record);
} else {
if ($header == "N") {
} else {
if ($header == "\$") {
for ($i = 0; $i < count($fields); $i++) {
if ($fields[$i] == "") {
$fields[$i] = $titles[$i];
}
if ($fields[$i] == "") {
$fields[$i] = "f{$i}";
$titles[$i] = "f{$i}";
}
}
array_unshift($fields, "fields");
fputcsv($out, $fields, ",", '"');
array_unshift($types, "types");
fputcsv($out, $types, ",", '"');
array_unshift($params, "params");
fputcsv($out, $params, ",", '"');
array_unshift($titles, "titles");
fputcsv($out, $titles, ",", '"');
$headers = array();
// （最初の）データを出力する。
array_unshift($csv_record, "record");
fputcsv($out, $csv_record, ",", '"');
}
}
}
}
}
}
} else {
// データを出力する。
array_unshift($csv_record, "record");
fputcsv($out, $csv_record, ",", '"');
}
}
// ワークシートの最終行に空行を出力する。
fputs($out, "\n");
}
$xls_object->disconnectWorksheets();
unset($xls_object);
return;
}
/**
* 活动信息导出
*/
public function activity_export()
{
$user_activity = M('user_activity');
import('@.ORG.Page');
$arr = "";
$true_name = trim($this->_get('true_name'));
#宝宝名称
if (!empty($true_name)) {
$arr['cms_user.true_name'] = array('LIKE', "%" . $true_name . "%");
$this->assign("true_name", $this->_get('true_name'));
}
$region_id = intval($this->_get('region_id'));
#城市
if ($region_id > 0) {
$region_list = $user_activity->query("SELECT id FROM `cms_region` where pid={$region_id} and status=1");
foreach ($region_list as $rlist) {
$arr_c1[] = $rlist['id'];
}
$str_c1 = implode(',', $arr_c1);
$region_list = $user_activity->query("SELECT id FROM `cms_region` where pid in({$str_c1}) and status=1");
foreach ($region_list as $rlist2) {
$arr_c1[] = $rlist2['id'];
}
$str_c2 = implode(',', $arr_c1);
$arr['cms_user.region_id'] = array('in', $str_c2);
$this->assign("region_id", $this->_get('region_id'));
}
$admin_id = intval($this->_get('admin_id'));
#所属客服
if ($admin_id > 0) {
$arr['cms_user.admin_id'] = array('eq', $admin_id);
$this->assign("admin_id", $this->_get('admin_id'));
}
$pre_status = intval($this->_get('pre_status'));
#预约状态
if ($pre_status != 3) {
$arr['cms_user_activity.pre_status'] = array('eq', $pre_status);
$this->assign("pre_status", $this->_get('pre_status'));
} else {
$this->assign("pre_status", 3);
}
$to_status = intval($this->_get('to_status'));
#到店状态
if ($to_status != 3) {
$arr['cms_user_activity.to_status'] = array('eq', $to_status);
$this->assign("to_status", $this->_get('to_status'));
} else {
$this->assign("to_status", 3);
}
$source_id = intval($this->_get('source_id'));
#到店状态
if ($source_id != 100) {
$arr['cms_user.source_id'] = array('eq', $source_id);
$this->assign("source_id", $this->_get('source_id'));
} else {
$this->assign("source_id", 100);
}
//$arr['cms_user.status']=array('eq',1);
$arr['cms_user_activity.type'] = array('eq', 2);
$user_arr = $user_activity->field('cms_user_activity.*,cms_user.sex,cms_user.birthday,cms_user.phone,cms_user.true_name')->join('LEFT JOIN cms_user ON cms_user.user_id=cms_user_activity.user_id')->order('cms_user_activity.create_time DESC')->select();
vendor('PHPExcel_1_7_8.Classes.PHPExcel');
vendor('PHPExcel_1_7_8.Classes.PHPExcel.IOFactory');
vendor('PHPExcel_1_7_8.Classes.PHPExcel.Worksheet');
//创建Excel对象
$objPHPExcel = new PHPExcel();
//设置Excel数据缓存方式为磁盘文件缓存（适用于大数据量处理，以减少对PHP自身内存的占用）
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
$cacheSettings = array('dir' => C('LEG_EXCEL_DATA_CACHE_DIR'));
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
//设置Excel元数据
$objPHPExcel->getProperties()->setCreator("客服管理系统");
$objPHPExcel->getProperties()->setLastModifiedBy("客服管理系统后台程序");
$objPHPExcel->getProperties()->setTitle("客服管理系统后台导出客服活动列表");
$objPHPExcel->getProperties()->setSubject("客服活动列表");
$objPHPExcel->getProperties()->setDescription("Exported document for Office 2007 XLSX, generated using PHP classes.");
$objPHPExcel->getProperties()->setKeywords("office 2007 php");
$objPHPExcel->getProperties()->setCategory("Export result file");
//填充数据到活动的电子表格中
$objPHPExcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPExcel->getActiveSheet();
$objWorksheet->setCellValueByColumnAndRow(0, 1, 'ID');
$objWorksheet->setCellValueByColumnAndRow(1, 1, '宝宝名称');
$objWorksheet->setCellValueByColumnAndRow(2, 1, '性别');
$objWorksheet->setCellValueByColumnAndRow(3, 1, '宝宝年龄');
$objWorksheet->setCellValueByColumnAndRow(4, 1, '课程名称');
$objWorksheet->setCellValueByColumnAndRow(5, 1, '预约时间');
$objWorksheet->setCellValueByColumnAndRow(6, 1, '电话号码');
$objWorksheet->setCellValueByColumnAndRow(7, 1, '预约状态');
$objWorksheet->setCellValueByColumnAndRow(8, 1, '到店状态');
foreach ($user_arr as $key => $rs) {
$key += 2;
$objWorksheet->setCellValueByColumnAndRow(0, $key, $rs['id']);
$objWorksheet->setCellValueByColumnAndRow(1, $key, $rs['true_name']);
$objWorksheet->setCellValueByColumnAndRow(2, $key, $rs['sex'] == 1 ? '女' : '男');
$objWorksheet->setCellValueByColumnAndRow(3, $key, get_age($rs['birthday']));
$objWorksheet->setCellValueByColumnAndRow(4, $key, $rs['course']);
$objWorksheet->setCellValueByColumnAndRow(5, $key, date("Y-m-d H:i:s", $rs['create_time']));
$objWorksheet->setCellValueByColumnAndRow(6, $key, $rs['phone']);
$objWorksheet->setCellValueByColumnAndRow(7, $key, $rs['pre_status'] == 1 ? '已预约' : '未预约');
$objWorksheet->setCellValueByColumnAndRow(8, $key, $rs['to_status'] == 1 ? "已到店" : '未到店');
}
//导出到文件中
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//直接输出到浏览器
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="ActivityUserList.xlsx"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
}
/**
* Create the PHPExcel instance to work in
*
* @return \PHPExcel
*/
private function createFile()
{
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '32MB');
\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$file = new \PHPExcel();
$this->createWorksheets($file);
return $file;
}
require_once '../../librerias/PHPExcel.php';
class MyReadFilter implements PHPExcel_Reader_IReadFilter
{
public function readCell($column, $row, $worksheetName = '')
{
// Read title row and rows 20 - 30
if ($row == 1) {
return true;
}
return false;
}
}
$nombreArchivo = $_POST['nombreArchivo'];
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array(' memoryCacheSize ' => '10MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$inputFileName = '../../uploads/' . $nombreArchivo;
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
//$sheetname = 'Data Sheet #2';
//$objReader->setLoadSheetsOnly($sheetname);
$objReader->setReadFilter(new MyReadFilter());
$objPHPExcel = $objReader->load($inputFileName);
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
//******************************************
//OBTENER COLUMNAS *************************
//******************************************
function outputExcel($data, $header, $fileName = 'now', $title = 'Sheet1', $type = 'xlsx')
{
if ($fileName == 'now') {
$fileName = date('YmdHis');
}
/** Include PHPExcel */
require_once APPPATH . 'libraries/PHPExcel.php';
// 设置缓存方式，减少对内存的占用
// $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
// $cacheSettings = array ( 'cacheTime' => 300 );
// PHPExcel_Settings::setCacheStorageMethod ( $cacheMethod, $cacheSettings );
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
// $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
// $cacheSettings = array( 'memoryCacheSize' => '8MB');
// PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file");
// 只获取指定数组
$data = getArrayByKey($data, array_keys($header));
$header_length = count($header);
$rows_length = count($data);
//输出头部
$i = 0;
foreach ($header as $k => $v) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($i + 65) . '1', $v);
$i++;
}
//输出内容
for ($row = 0; $row < $rows_length; $row++) {
$i = 0;
foreach ($header as $k => $v) {
if ($k == 'tracking_number') {
//强制为字符串显示
// $objPHPExcel->getActiveSheet()->setCellValueExplicit(chr($i + 65).($row + 2), $data[$row][$k],PHPExcel_Cell_DataType::TYPE_STRING);
// $objPHPExcel->getActiveSheet()->getStyle(chr($i + 65).($row + 2))->getNumberFormat()->setFormatCode("@");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($i + 65) . ($row + 2), " " . $data[$row][$k]);
} else {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($i + 65) . ($row + 2), $data[$row][$k]);
}
$i++;
}
}
//Set column widths 设置列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
// $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle($title);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $fileName . '.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;
}
echo '</td>';
echo '<td>';
echo $data3['kd_kabkota'];
echo '</td>';
echo '<td>';
echo $data3['nama_kabkota'];
echo '</td>';
// for($j = 0; $j <= $max_kolom; $j++)
// {
// echo '<td>' . $data[$i][$j] . '</td>';
// }
$this->insert_kota($data3);
echo '</tr>';
}
//}
echo '</table>';
}
function insert_kota($data)
{
$this->db->flush_cache();
$this->db->insert('reff_kabkota', $data);
}
function provinsi()
{
$this->load->library('excel2');
$path = "D:/xampp/htdocs/simkeu-bpihv2/[upload]/RPROPINSI.xlsx";
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '8MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objPHPExcel = PHPExcel_IOFactory::load($path);
$worksheet = $objPHPExcel->setActiveSheetIndex(0);
$maxCell = $worksheet->getHighestRowAndColumn();
$excel = $worksheet->rangeToArray('A1:' . $maxCell['column'] . $maxCell['row']);
$excel = array_map('array_filter', $excel);
$excel = array_filter($excel);
/**/
$max_kolom = 0;
$max_baris = 0;
$max_baris = $this->excel2->endKey($excel);
echo "<br>";
for ($i = 0; $i <= $max_baris; $i++) {
$baris = $this->excel2->endKey($excel[$i]);
if ($baris > $max_kolom) {
$max_kolom = $baris;
}
}
echo "max baris : " . $max_baris;
echo "<br>";
echo "max kolom : " . $max_kolom;
echo "<br>";
echo '<br><table border="1"><tr>';
/*
foreach ($excel as $key => $values)
{
echo '<tr>';
foreach ($values as $anotherkey => $val)
{
public static function exportXLS($items)
{
include 'lib/core/lhform/PHPExcel.php';
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize ' => '64MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getStyle('A1:AW1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setTitle('Report');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, erTranslationClassLhTranslation::getInstance()->getTranslation('survey/collected', 'Chats'));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1, erTranslationClassLhTranslation::getInstance()->getTranslation('survey/collected', 'Department name'));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 1, erTranslationClassLhTranslation::getInstance()->getTranslation('survey/collected', 'Operator'));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, 1, erTranslationClassLhTranslation::getInstance()->getTranslation('survey/collected', 'Stars'));
$attributes = array('virtual_chats_number', 'department_name', 'user', 'average_stars');
$i = 2;
foreach ($items as $item) {
foreach ($attributes as $key => $attr) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($key, $i, (string) $item->{$attr});
}
$i++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
// 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="report.xlsx"');
// Write file to the browser
$objWriter->save('php://output');
}