writeSheet() public method

public writeSheet ( array $data, $sheet_name = '', array $header_types = [] )
$data array
$header_types array
Example #1
3
 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");
     };
 }
Example #2
2
<?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');
<?php

include_once "../../xlsxwriter.class.php";
$writer = new XLSXWriter();
$writer->setAuthor('Some Author');
$header = array('General' => 'string', 'Simple Integer' => '0', '2 Decimal Places Integer' => '0.00', 'Integer 1000s Group' => '#,##0', '1000s,Decimal,Leading Zero' => '#,##0.00', '1000s,Decimal,No Leading Zero' => '#,###.00', 'Negative In Parentheses' => '#,##0_);(#,##0)', 'Negative In Parentheses With Decimal' => '#,##0.00_);(#,##0.00)');
$row = array('1000', '2000', '3000', '4000', '0.50', '0.50', '-50', '-50');
$writer->writeSheet(array($row), 'Number', $header);
$header = array('Whole Percent' => '0%', 'Decimal Percent' => '0.00%');
$row = array('1', '1');
$writer->writeSheet(array($row), 'Percent', $header);
$header = array('USD' => '[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00', 'CAD' => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00', 'Euro' => '#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]', 'JPY' => '[$¥-411]#,##0;[RED]-[$¥-411]#,##0', 'CNY' => '[$¥-804]#,##0.00;[RED]-[$¥-804]#,##0.00');
$row = array('1000', '2000', '3000', '4000', '5000');
$writer->writeSheet(array($row), 'Currency', $header);
$header = array('M/D/YY' => 'M/D/YY', 'MM/DD/YYYY' => 'MM/DD/YYYY', 'YYYY-MM-DD' => 'YYYY-MM-DD', 'YYYY-MM-DD HH:MM:SS' => 'YYYY-MM-DD HH:MM:SS', 'NN' => 'NN', 'NNN' => 'NNN', 'NNNN' => 'NNNN', 'D' => 'D', 'DD' => 'DD', 'M' => 'M', 'MM' => 'MM', 'MMM' => 'MMM', 'MMMM' => 'MMMM', 'YY' => 'YY', 'YYYY' => 'YYYY', 'Q YY' => 'Q YY', 'Q YYYY' => 'Q YYYY');
$row = array('1999-01-01', '1999-01-01', '1999-12-31', '1999-12-31 00:00:00', '1999-12-31', '1999-12-31', '1999-12-31', '1999-12-31', '1999-12-31', '1999-12-31', '1999-12-31', '1999-12-31', '1999-12-31', '1999-12-31', '1999-12-31', '1999-12-31', '1999-12-31');
$writer->writeSheet(array($row), 'Date', $header);
$header = array('HH:MM' => 'HH:MM', 'HH:MM:SS' => 'HH:MM:SS', 'HH:MM AM/PM' => 'HH:MM AM/PM', 'HH:MM:SS AM/PM' => 'HH:MM:SS AM/PM');
$row = array('12-31-1999 01:23:00', '12-31-1999 01:23:00', '12-31-1999 01:23:00', '12-31-1999 01:23:00');
$writer->writeSheet(array($row), 'Time', $header);
$writer->writeToFile('formats.xlsx');
Example #4
0
 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");
     };
 }
Example #5
0
function render($data)
{
    $tournament = $data['tournament'];
    $disciplines = $data['disciplines'];
    $header = ['Event' => 'string', 'SpielerID' => 'string', 'Name' => 'string', 'Vorname' => 'string', 'Verein' => 'string', 'Geschlecht' => 'string', 'Email' => 'string', 'Partner ID' => 'string', 'Setzplatz' => 'string'];
    $output = [];
    foreach ($disciplines as $d) {
        $dname = $d->name;
        // Fix up discipline names
        if (\preg_match('/^H([DE]) U0?([0-9]+)$/', $dname, $m)) {
            $dname = 'J' . $m[1] . ' U' . $m[2];
        } elseif (\preg_match('/^D([DE]) U0?([0-9]+)$/', $dname, $m)) {
            $dname = 'M' . $m[1] . ' U' . $m[2];
        } elseif (\preg_match('/^(DD|DE|GD|HD|HE|MX)-?\\s*([A-Z])$/', $dname, $m)) {
            $dname = $m[1] . $m[2];
        }
        $is_doubles = $d->with_partner();
        foreach ($d->entries as $er) {
            if ($er['on_waiting_list']) {
                continue;
            }
            if ($is_doubles && $er['partner'] === NULL) {
                continue;
            }
            \array_push($output, [$dname, $er['player']->textid, $er['player']->get_lastname(), $er['player']->get_firstname(), $er['player_club']->name, $er['player']->gender, $er['player']->email, $is_doubles ? $er['partner']->textid : '', $er['seeding']]);
            if ($is_doubles) {
                \array_push($output, [$dname, $er['partner']->textid, $er['partner']->get_lastname(), $er['partner']->get_firstname(), $er['partner_club']->name, $er['partner']->gender, $er['partner']->email, $is_doubles ? $er['player']->textid : '', $er['seeding']]);
            }
        }
    }
    $writer = new \XLSXWriter();
    $writer->writeSheet($output, 'Meldungen', $header);
    $safe_filename = \bmtmgr\utils\sanitize_filename($tournament->name);
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . $safe_filename . '.xlsx"');
    echo $writer->writeToString();
}
Example #6
0
 $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("");
     $oPage->no_cache();
     $oPage->add('<p style="text-align:center">');
     $oPage->add('<div style="display:inline-block;margin:0.5em;"><a style="text-decoration:none" href="' . utils::GetAbsoluteUrlAppRoot() . 'pages/ajax.csvimport.php?operation=get_csv_template&disposition=attachment&class_name=' . $sClassName . '"><img border="0" src="../images/csv.png"><br/>' . $sClassDisplayName . '.csv</a></div>');
 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;
 }
Example #8
0
						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";
		$to=0;
Example #9
0
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');
$header = array('Hechos' => 'string', 'Observaciones' => 'string', 'fecha' => 'date', 'Comunidad' => 'string', 'Direccion' => 'string');
include 'spoon/spoon.php';
$objDB = new DBConexion();
$fechaInicial = $_POST['fechainicial'];
$fechaFinal = $_POST['fechafinal'];
$fecha = date_create_from_format("d/m/Y", $fechaInicial);
$fechaInicial = date_format($fecha, "Y-m-d");
$fecha = date_create_from_format("d/m/Y", $fechaFinal);
$fechaFinal = date_format($fecha, "Y-m-d");
$query = "SELECT narracion_hechos, observaciones, atenciones.fecha_registro as fechareg, comunidades.comunidad as community, ciudadanos.direccion as address\nFROM atenciones\nINNER JOIN comunidades ON comunidades.id_comunidad = atenciones.comunidad\nINNER JOIN ciudadanos ON ciudadanos.id_ciudadano = atenciones.id_ciudadano\nWHERE atenciones.fecha_registro >  '{$fechaInicial}'\nAND atenciones.fecha_registro <  '{$fechaFinal}'";
$atencionesArray = $objDB->getRecords($query);
//$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($atencionesArray, 'Sheet1', $header);
//$writer->writeSheet($data2,'Sheet2');
$writer->writeToStdOut();
//$writer->writeToFile('example.xlsx');
//echo $writer->writeToString();
exit(0);
 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);
 }
<?php

include_once "xlsxwriter.class.php";
$header = array('c1' => 'dollar', 'c2' => 'euro', 'c3' => '#,##0.00', 'c4' => '#,##0.00 [$€-407]', 'c5' => '[$¥-411]#,##0;[RED]-[$¥-411]#,##0');
$row = array(100, 200, 300, 400, 500);
$writer = new XLSXWriter();
$writer->writeSheet(array($row), 'Sheet1', $header);
$writer->writeToFile('example.xlsx');
Example #12
0
 public function crudListExcel($model, $scope)
 {
     $obj = CrudModel::createInstance($model, $scope);
     $params = $this->request->all();
     $cols = $obj->getList()->getParam('columns');
     $xls = [];
     $row = [];
     foreach ($cols as $col) {
         if ((empty($col['ctype']) || $col['ctype'] != 'checkbox') && $col['data'] != 'actions' && empty($col['invisible'])) {
             $row[] = $col['title'];
         }
     }
     $xls[] = $row;
     $query = $this->app['session']->get('current_query_info');
     if (empty($query) || !isset($query['sql']) || !isset($query['bind'])) {
         $q = CrudModelCollectionBuilder::createDataTables($obj, $params)->applyContextFilter()->getCollectionQuery()->getQuery();
         $query = ['sql' => $q->toSQL(), 'bind' => $q->getBindings()];
     }
     $rs = \DB::select($query['sql'], $query['bind']);
     foreach ($rs as $r) {
         $row = [];
         foreach ($cols as $col) {
             if ((empty($col['ctype']) || $col['ctype'] != 'checkbox') && $col['data'] != 'actions' && empty($col['invisible'])) {
                 $row[] = $r[$col['data']] ?? '';
             }
         }
         $xls[] = $row;
     }
     $writer = new \XLSXWriter();
     $writer->writeSheet($xls, 'Sheet1');
     $data = $writer->writeToString();
     header('Cache-Control: no-cache, must-revalidate');
     header('Pragma: no-cache');
     //keeps ie happy
     header('Content-Disposition: attachment; filename=xls.xlsx');
     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
     header('Content-Length: ' . strlen($data));
     header('Content-Transfer-Encoding: binary');
     echo $data;
     exit;
 }
 /**
  * @covers XLSXWriter::writeToFile
  */
 public function testWriteToFile()
 {
     $filename = tempnam("/tmp", "xlsx_writer");
     $header = array('0' => 'string', '1' => 'string', '2' => 'string', '3' => 'string');
     $sheet = array(array('55', '66', '77', '88'), array('10', '11', '12', '13'));
     $xlsx_writer = new XLSXWriter();
     $xlsx_writer->writeSheet($sheet, 'mysheet', $header);
     $xlsx_writer->writeToFile($filename);
     $zip = new ZipArchive();
     $r = $zip->open($filename);
     $this->assertTrue($r);
     $r = $zip->numFiles > 0 ? true : false;
     $this->assertTrue($r);
     $out_sheet = array();
     for ($z = 0; $z < $zip->numFiles; $z++) {
         $inside_zip_filename = $zip->getNameIndex($z);
         if (preg_match("/sheet(\\d+).xml/", basename($inside_zip_filename))) {
             $out_sheet = $this->stripCellsFromSheetXML($zip->getFromName($inside_zip_filename));
             array_shift($out_sheet);
             $out_sheet = array_values($out_sheet);
         }
     }
     $zip->close();
     @unlink($filename);
     $r1 = self::array_diff_assoc_recursive($out_sheet, $sheet);
     $r2 = self::array_diff_assoc_recursive($sheet, $out_sheet);
     $this->assertEmpty($r1);
     $this->assertEmpty($r2);
 }