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();
}
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.");
}
public function __construct()
{
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array(' memoryCacheSize ' => '8MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$this->phpExcel = new PHPExcel();
}
/**
* Set the Zip Class to use (PCLZip or ZipArchive)
*
* @param string $zipClass
* PHPExcel_Settings::ZipArchive
* @return boolean or failure
*/
public static function setZipClass($zipClass) {
if (($zipClass == self::PCLZIP) || ($zipClass == self::ZIPARCHIVE)) {
self::$_zipClass = $zipClass;
return True;
}
return False;
} // function setZipClass()
function read_xlsfile($filename)
{
/** PHPExcel_IOFactory */
include_once 'Classes/PHPExcel/IOFactory.php';
$inputFileName = $filename;
try {
PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$row = 0;
foreach ($sheetData as $v => $i) {
if ($row == 0) {
$row = 1;
continue;
}
if (count($i) == 3 && filter_var($i['C'], FILTER_VALIDATE_EMAIL)) {
$query[] = "('" . implode("','", $i) . "')";
}
}
if (count($query) == 1000) {
bulk_insert($query);
$query = '';
}
return $query;
}
/**
*
* @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();
}
/**
* @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 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 __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 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 static function pdfExport($data, $headerTitle = [], $headerRGBColor = 'FCFCFC')
{
$rendererName = \PHPExcel_Settings::PDF_RENDERER_DOMPDF;
$rendererLibraryPath = dirname(dirname(__DIR__)) . DIRECTORY_SEPARATOR . 'vendor' . DIRECTORY_SEPARATOR . 'dompdf' . DIRECTORY_SEPARATOR . 'dompdf';
if (!\PHPExcel_Settings::setPdfRenderer($rendererName, $rendererLibraryPath)) {
return false;
}
$phpExcel = self::init($data, $headerTitle, $headerRGBColor);
$objWriter = \PHPExcel_IOFactory::createWriter($phpExcel, 'PDF');
$objWriter->save('php://output');
return true;
}
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());
}
}
public static function getBlock()
{
session::start();
$block_path = __DIR__ . '/block/';
$block_name = str_replace('_class', '', __CLASS__) . '.php';
self::$block_name = $block_name;
self::$blocks[$block_name] = ['name' => $block_name, 'path' => $block_path];
// Create new PHPExcel object
$xls = new PHPExcel();
// Устанавливаем индекс активного листа
$xls->setActiveSheetIndex(0);
// Получаем активный лист
$sheet = $xls->getActiveSheet();
// Подписываем лист
$sheet->setTitle('Таблица умножения');
// Вставляем текст в ячейку A1
$sheet->setCellValue("A1", 'Таблица умножения');
$sheet->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$sheet->getStyle('A1')->getFill()->getStartColor()->setRGB('EEEEEE');
// Объединяем ячейки
$sheet->mergeCells('A1:H1');
// Выравнивание текста
$sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
for ($i = 2; $i < 10; $i++) {
for ($j = 2; $j < 10; $j++) {
// Выводим таблицу умножения
$sheet->setCellValueByColumnAndRow($i - 2, $j, $i . "x" . $j . "=" . $i * $j);
// Применяем выравнивание
$sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
}
//Сохранение листа excel в PDF рабочий вариант
//1. скачать tcpdf, распаковать в classes
//2. в PHPExcel/Settings строка 49 заменить tcPDF на tcpdf
//3. далее работающий код
$rendererLibrary = 'tcpdf';
$rendererLibraryPath = 'app/classes/' . $rendererLibrary;
PHPExcel_Settings::setPdfRenderer('tcpdf', $rendererLibraryPath);
$path = 'files/asdfg1_' . date('i') . '_' . date('s') . '.pdf';
$objWriter = new PHPExcel_Writer_PDF($xls);
$objWriter = PHPExcel_IOFactory::createWriter($xls, 'PDF');
$objWriter->setSheetIndex(0);
$objWriter->save($path);
self::$values['path'] = $path;
//echo
//controller::call('/main/get_pdf', ['params' => ['path' => 'files/asdfg1.pdf']]);
return self::show();
}
/**
* 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;
}
/**
* Instantiate a new renderer of the configured type within this container class
*
* @param PHPExcel $phpExcel PHPExcel object
* @throws PHPExcel_Writer_Exception when PDF library is not configured
*/
public function __construct(PHPExcel $phpExcel)
{
$pdfLibraryName = PHPExcel_Settings::getPdfRendererName();
if (is_null($pdfLibraryName)) {
throw new PHPExcel_Writer_Exception("PDF Rendering library has not been defined.");
}
$pdfLibraryPath = PHPExcel_Settings::getPdfRendererPath();
if (is_null($pdfLibraryName)) {
throw new PHPExcel_Writer_Exception("PDF Rendering library path has not been defined.");
}
$includePath = str_replace('\\', '/', get_include_path());
$rendererPath = str_replace('\\', '/', $pdfLibraryPath);
if (strpos($rendererPath, $includePath) === false) {
set_include_path(get_include_path() . PATH_SEPARATOR . $pdfLibraryPath);
}
$rendererName = 'PHPExcel_Writer_PDF_' . $pdfLibraryName;
$this->_renderer = new $rendererName($phpExcel);
}
public function getThumbnail($path, $maxX, $maxY, $scalingup, $fileview)
{
require_once 'PHPExcel/Classes/PHPExcel.php';
require_once 'PHPExcel/Classes/PHPExcel/IOFactory.php';
$absPath = $fileview->toTmpFile($path);
$tmpPath = \OC_Helper::tmpFile();
$rendererName = \PHPExcel_Settings::PDF_RENDERER_DOMPDF;
$rendererLibraryPath = \OC::$THIRDPARTYROOT . '/3rdparty/dompdf';
\PHPExcel_Settings::setPdfRenderer($rendererName, $rendererLibraryPath);
$phpexcel = new \PHPExcel($absPath);
$excel = \PHPExcel_IOFactory::createWriter($phpexcel, 'PDF');
$excel->save($tmpPath);
$pdf = new \imagick($tmpPath . '[0]');
$pdf->setImageFormat('jpg');
unlink($absPath);
unlink($tmpPath);
$image = new \OC_Image();
$image->loadFromData($pdf);
return $image->valid() ? $image : false;
}
/**
* 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");
/**
* Set the pdf renderer
* @throws \Exception
*/
protected function setPdfRenderer()
{
// Get the driver name
$driver = Config::get('excel.export.pdf.driver');
$path = Config::get('excel.export.pdf.drivers.' . $driver . '.path');
// Disable autoloading for dompdf
if (!defined("DOMPDF_ENABLE_AUTOLOAD")) {
define("DOMPDF_ENABLE_AUTOLOAD", false);
}
// Set the pdf renderer
if (!\PHPExcel_Settings::setPdfRenderer($driver, $path)) {
throw new \Exception("{$driver} could not be found. Make sure you've included it in your composer.json");
}
}
// and its directory location on your server
//$rendererName = PHPExcel_Settings::PDF_RENDERER_TCPDF;
$rendererName = PHPExcel_Settings::PDF_RENDERER_MPDF;
//$rendererName = PHPExcel_Settings::PDF_RENDERER_DOMPDF;
//$rendererLibrary = 'tcPDF5.9';
$rendererLibrary = 'mPDF5.4';
//$rendererLibrary = 'domPDF0.6.0beta3';
$rendererLibraryPath = dirname(__FILE__) . '/../../../libraries/PDF/' . $rendererLibrary;
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("PDF Test Document")->setSubject("PDF Test Document")->setDescription("Test document for PDF, generated using PHP classes.")->setKeywords("pdf php")->setCategory("Test result file");
// Add some data
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Hello')->setCellValue('B2', 'world!')->setCellValue('C1', 'Hello')->setCellValue('D2', 'world!');
// Miscellaneous glyphs, UTF-8
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A4', 'Miscellaneous glyphs')->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');
$objPHPExcel->getActiveSheet()->setShowGridLines(false);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
if (!PHPExcel_Settings::setPdfRenderer($rendererName, $rendererLibraryPath)) {
die('NOTICE: Please set the $rendererName and $rendererLibraryPath values' . '<br />' . 'at the top of this script as appropriate for your directory structure');
}
// Redirect output to a client’s web browser (PDF)
header('Content-Type: application/pdf');
header('Content-Disposition: attachment;filename="01simple.pdf"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->save('php://output');
exit;
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;
}
}
}
/**
* Set default options for libxml loader
*
* @param int $options Default options for libxml loader
*/
public static function setLibXmlLoaderOptions($options = null)
{
if (is_null($options)) {
$options = LIBXML_DTDLOAD | LIBXML_DTDATTR;
}
@libxml_disable_entity_loader($options == (LIBXML_DTDLOAD | LIBXML_DTDATTR));
self::$_libXmlLoaderOptions = $options;
}
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');
}
