static function xls()
{
return function ($req, $res, $svc, $app) {
$time = date('H:i:s', time());
$date = date('d-m-Y');
$exportFile = __DIR__ . "/../xls/relatorio-tickets-{$time}.xlsx";
$headers = [['Protocolo', 'Operadora', 'Status', 'Cidade', 'Problema', 'Designação', 'Cliente Final', 'Data']];
$rows = array_map(function ($ticket) {
return [$ticket->protocolo, $ticket->operadora, $ticket->status, $ticket->cidade, $ticket->tipo_problema, $ticket->designacao, $ticket->cliente_final, date('d/m/Y', strtotime($ticket->criado_em))];
}, Tickets::filter($req, $app));
$writer = new \XLSXWriter();
$writer->writeSheet(array_merge($headers, $rows), 'Relatório ' . $date);
$writer->setAuthor('Sim TV - Trouble Ticket');
$writer->writeToFile($exportFile);
$res->file($exportFile, "Relatório Tickets {$time}.xlsx");
};
}
<?php
include_once "../xlsxwriter.class.php";
$header = array('year' => 'string', 'month' => 'string', 'amount' => 'money', 'first_event' => 'datetime', 'second_event' => 'date');
$data1 = array(array('2003', '1', '-50.5', '2010-01-01 23:00:00', '2012-12-31 23:00:00'), array('2003', '=B2', '23.5', '2010-01-01 00:00:00', '2012-12-31 00:00:00'));
$data2 = array(array('2003', '01', '343.12'), array('2003', '02', '345.12'));
$writer = new XLSXWriter();
$writer->setAuthor('Some Author');
$writer->writeSheet($data1, 'Sheet1', $header);
$writer->writeSheet($data2, 'Sheet2');
$writer->writeToFile('test.xlsx');
static function xls()
{
return function ($req, $res, $svc, $app) {
$time = date('H:i:s', time());
$date = date('d-m-Y');
$exportFile = __DIR__ . "/../xls/relatorio-clientes-{$time}.xlsx";
$headers = [['Cidade', 'Contrato', 'Designação', 'Cliente', 'Velocidade', 'Operadora', 'Equipamento', 'Endereço', 'Criado em']];
$clientes = $app->db->cadcliente;
if ($req->cidade) {
$clientes->where_equal('cidade', $req->cidade);
}
$rows = array_map(function ($cliente) {
return [$cliente->cidade, $cliente->contrato, $cliente->designacao, $cliente->cliente, $cliente->velocidade, $cliente->operadora, $cliente->equipamento, $cliente->endereco, date('d/m/Y', strtotime($cliente->data))];
}, $clientes->find_many());
$writer = new \XLSXWriter();
$writer->writeSheet(array_merge($headers, $rows), 'Relatório ' . $date);
$writer->setAuthor('Sim TV - Trouble Ticket');
$writer->writeToFile($exportFile);
$res->file($exportFile, "Relatório Cliente {$time}.xlsx");
};
}
$oSearch = new DBObjectSearch($sClassName);
$oSearch->AddCondition('id', 0, '=');
// Make sure we create an empty set
$oSet = new CMDBObjectSet($oSearch);
$sResult = cmdbAbstractObject::GetSetAsCSV($oSet, array('showMandatoryFields' => true));
$sClassDisplayName = MetaModel::GetName($sClassName);
$sDisposition = utils::ReadParam('disposition', 'inline');
if ($sDisposition == 'attachment') {
switch ($sFormat) {
case 'xlsx':
$oPage = new ajax_page("");
$oPage->SetContentType('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$oPage->SetContentDisposition('attachment', $sClassDisplayName . '.xlsx');
require_once APPROOT . '/application/excelexporter.class.inc.php';
$writer = new XLSXWriter();
$writer->setAuthor(UserRights::GetUserFriendlyName());
$aHeaders = array(0 => explode(',', $sResult));
// comma is the default separator
$writer->writeSheet($aHeaders, $sClassDisplayName, array());
$oPage->add($writer->writeToString());
break;
case 'csv':
default:
$oPage = new CSVPage("");
$oPage->add_header("Content-type: text/csv; charset=utf-8");
$oPage->add_header("Content-disposition: attachment; filename=\"{$sClassDisplayName}.csv\"");
$oPage->no_cache();
$oPage->add($sResult);
}
} else {
$oPage = new ajax_page("");
public function GetFooter()
{
$hFile = @fopen($this->aStatusInfo['tmp_file'], 'rb');
if ($hFile === false) {
throw new Exception('ExcelBulkExport: Failed to open temporary data file: "' . $this->aStatusInfo['tmp_file'] . '" for reading.');
}
$sHeaders = fgets($hFile);
$aHeaders = json_decode($sHeaders, true);
$aData = array();
while ($sLine = fgets($hFile)) {
$aRow = json_decode($sLine);
$aData[] = $aRow;
}
fclose($hFile);
$fStartExcel = microtime(true);
$writer = new XLSXWriter();
$writer->setAuthor(UserRights::GetUserFriendlyName());
$aHeaderTypes = array();
$aHeaderNames = array();
foreach ($aHeaders as $Header) {
$aHeaderNames[] = $Header['label'];
$aHeaderTypes[] = $Header['type'];
}
$writer->writeSheet($aData, 'Sheet1', $aHeaderTypes, $aHeaderNames);
$fExcelTime = microtime(true) - $fStartExcel;
//$this->aStatistics['excel_build_duration'] = $fExcelTime;
$fTime = microtime(true);
$data = $writer->writeToString();
$fExcelSaveTime = microtime(true) - $fTime;
//$this->aStatistics['excel_write_duration'] = $fExcelSaveTime;
@unlink($this->aStatusInfo['tmp_file']);
return $data;
}
$factor=getDetailedTableInfo2("vl_results_multiplicationfactor","worksheetID='$worksheetID' limit 1","factor");
if(!$factor) {
$factor=1;
}
//results
$results.=getVLResult("roche",$worksheetID,$rq["vlSampleID"],$factor).($rcount<$rnum?", ":"");
}
}
//xls
$dataRocheResults[]=array($patientART,$patientOtherID,$facility,$numberResults,$results);
}
}
//output to xlsx
$writer = new XLSXWriter();
$writer->setAuthor($default_institutionName);
$writer->writeSheet($dataAbbottResults,"abbott",$headerAbbottResults);
$writer->writeSheet($dataRocheResults,"roche",$headerRocheResults);
$writer->writeToStdOut();
break;
case "clinicalrequestformsexcel":
//filename of the excel file to be downloaded
$filename=0;
$filename="excel.clinical.request.forms.".getFormattedDateCRB($datetime).".xls";
//create an instance of the class
$xls = new ExportXLS($filename);
//period
$from=0;
$from="$fromYear-$fromMonth-$fromDay";
public function Run()
{
$sCode = 'error';
$iPercentage = 100;
$sMessage = Dict::Format('ExcelExporter:ErrorUnexpected_State', $this->sState);
$fTime = microtime(true);
try {
switch ($this->sState) {
case 'new':
$oIDSet = new DBObjectSet($this->oSearch);
$oIDSet->OptimizeColumnLoad(array('id'));
$this->aObjectsIDs = array();
while ($oObj = $oIDSet->Fetch()) {
$this->aObjectsIDs[] = $oObj->GetKey();
}
$sCode = 'retrieving-data';
$iPercentage = 5;
$sMessage = Dict::S('ExcelExporter:RetrievingData');
$this->iPosition = 0;
$this->aStatistics['objects_count'] = count($this->aObjectsIDs);
$this->aStatistics['data_retrieval_duration'] += microtime(true) - $fTime;
// The first line of the file is the "headers" specifying the label and the type of each column
$this->GetFieldsList($oIDSet, $this->bAdvancedMode);
$sRow = json_encode($this->aTableHeaders);
$hFile = @fopen($this->GetDataFile(), 'ab');
if ($hFile === false) {
throw new Exception('ExcelExporter: Failed to open temporary data file: "' . $this->GetDataFile() . '" for writing.');
}
fwrite($hFile, $sRow . "\n");
fclose($hFile);
// Next state
$this->sState = 'retrieving-data';
break;
case 'retrieving-data':
$oCurrentSearch = clone $this->oSearch;
$aIDs = array_slice($this->aObjectsIDs, $this->iPosition, $this->iChunkSize);
$oCurrentSearch->AddCondition('id', $aIDs, 'IN');
$hFile = @fopen($this->GetDataFile(), 'ab');
if ($hFile === false) {
throw new Exception('ExcelExporter: Failed to open temporary data file: "' . $this->GetDataFile() . '" for writing.');
}
$oSet = new DBObjectSet($oCurrentSearch);
$this->GetFieldsList($oSet, $this->bAdvancedMode);
while ($aObjects = $oSet->FetchAssoc()) {
$aRow = array();
foreach ($this->aAuthorizedClasses as $sAlias => $sClassName) {
$oObj = $aObjects[$sAlias];
if ($this->bAdvancedMode) {
$aRow[] = $oObj->GetKey();
}
foreach ($this->aFieldsList[$sAlias] as $sAttCodeEx => $oAttDef) {
$value = $oObj->Get($sAttCodeEx);
if ($value instanceof ormCaseLog) {
// Extract the case log as text and remove the "===" which make Excel think that the cell contains a formula the next time you edit it!
$sExcelVal = trim(preg_replace('/========== ([^=]+) ============/', '********** $1 ************', $value->GetText()));
} else {
$sExcelVal = $oAttDef->GetEditValue($value, $oObj);
}
$aRow[] = $sExcelVal;
}
}
$sRow = json_encode($aRow);
fwrite($hFile, $sRow . "\n");
}
fclose($hFile);
if ($this->iPosition + $this->iChunkSize > count($this->aObjectsIDs)) {
// Next state
$this->sState = 'building-excel';
$sCode = 'building-excel';
$iPercentage = 80;
$sMessage = Dict::S('ExcelExporter:BuildingExcelFile');
} else {
$sCode = 'retrieving-data';
$this->iPosition += $this->iChunkSize;
$iPercentage = 5 + round(75 * ($this->iPosition / count($this->aObjectsIDs)));
$sMessage = Dict::S('ExcelExporter:RetrievingData');
}
break;
case 'building-excel':
$hFile = @fopen($this->GetDataFile(), 'rb');
if ($hFile === false) {
throw new Exception('ExcelExporter: Failed to open temporary data file: "' . $this->GetDataFile() . '" for reading.');
}
$sHeaders = fgets($hFile);
$aHeaders = json_decode($sHeaders, true);
$aData = array();
while ($sLine = fgets($hFile)) {
$aRow = json_decode($sLine);
$aData[] = $aRow;
}
fclose($hFile);
@unlink($this->GetDataFile());
$fStartExcel = microtime(true);
$writer = new XLSXWriter();
$writer->setAuthor(UserRights::GetUserFriendlyName());
$writer->writeSheet($aData, 'Sheet1', $aHeaders);
$fExcelTime = microtime(true) - $fStartExcel;
$this->aStatistics['excel_build_duration'] = $fExcelTime;
$fTime = microtime(true);
$writer->writeToFile($this->GetExcelFilePath());
$fExcelSaveTime = microtime(true) - $fTime;
$this->aStatistics['excel_write_duration'] = $fExcelSaveTime;
// Next state
$this->sState = 'done';
$sCode = 'done';
$iPercentage = 100;
$sMessage = Dict::S('ExcelExporter:Done');
break;
case 'done':
$this->sState = 'done';
$sCode = 'done';
$iPercentage = 100;
$sMessage = Dict::S('ExcelExporter:Done');
break;
}
} catch (Exception $e) {
$sCode = 'error';
$sMessage = $e->getMessage();
}
$this->aStatistics['total_duration'] += microtime(true) - $fTime;
$peak_memory = memory_get_peak_usage(true);
if ($peak_memory > $this->aStatistics['peak_memory_usage']) {
$this->aStatistics['peak_memory_usage'] = $peak_memory;
}
return array('code' => $sCode, 'message' => $sMessage, 'percentage' => $iPercentage);
}
