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