PhpSpreadsheet run as a Procedure

ernesttan1976 · Thu Aug 27, 2020 10:57 am

Dear all,

I am trying to run this code as a php procedure by clicking on an input:button, onclick method.
Procedure is run as a new window.
But the window is blank. No error message whatsoever is shown.

Before this, I had run 'composer require PhpOffice\PhpSpreadsheet and now the autoloader is working well. In fact I ran a plain php file with this library and it works well.
But I couldn't get it to work as a php procedure in Nubuilder.

Any advice is greatly appreciated. Thanks!

Code: Select all

require_once 'nudb/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

//Open excel sheet
$inputFileName = "http://58.185.199.182:8000/197201333K-PTE-01-workerList.xlsx";

try {
$spreadsheet = new \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);

//Read range
$highestRow = $spreadsheet->getHighestRow;
$dataArray = $spreadsheet->getSheet(0)->rangeToArray(
    'B16:J'.$highestRow, //The worksheet range
    NULL,  // values for empty cells
    TRUE, // should formulas be calculated
    TRUE, // should values be formatted for each cell
    TRUE  // should the array be indexed by cell row and cell column
);

} catch (Exception $e){
    
    die('Error loading file: '.$e->getMessage());
}


header('Content-Type: text/html; charset=utf-8');

//echo '<!DOCTYPE html>';
echo '<html>';
echo '<body>';

//For each row, display
echo '<table>'."\n";
for ($row = 1; $row <= $highestrow-15; ++$row){
    echo '<tr>'.PHP_EOL;
        for ($col = 1; $col <=11; ++ $col){
            //$value = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
            $value = $dataArray[$row-1,$col-1];
            echo '<td>'.$value.'</td>'.PHP_EOL;
        }
    echo '</tr>'.PHP_EOL;
}


echo '</table>'.PHP_EOL;

echo '</body>';
echo '</html>';

//$writer = new Xlsx($spreadsheet);
//header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//header('Content-Disposition: attachment;filename="demo.xlsx"');
//header('Cache-Control: max-age=0');
//header('Expires: Fri, 11 Nov 2011 11:11:11 GMT');
//header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
//header('Cache-Control: cache, must-revalidate');
//header('Pragma: public');
//$writer->save('php://output');
// ob_end_flush();

//$sql = "UPDATE users SET name=?, surname=?, sex=? WHERE id=?";
//$pdo->prepare($sql)->execute([$name, $surname, $sex, $id]);
//Find the FIN
//Update the other columns

//If cannot find, then give a message.
//If can find then mention "record XX updated"

Unread post by **kev1n** » Fri Aug 28, 2020 5:33 pm

Hi,

I have no experience with composer but you could add some nuDebug('1');

Code: Select all

 nuDebug('2');  etc. in the code to see where it gets stuck. 
Also check nuDebug Results and the server log for errors.

Unread post by **kev1n** » Mon Sep 14, 2020 6:53 am

Hi,

Were you able to resolve your issue?

ernesttan1976 · Thu Oct 01, 2020 12:00 pm

Oh yes thank you!

ernesttan1976 · Thu Oct 01, 2020 12:05 pm

Screenshot

Unread post by **kev1n** » Fri Oct 02, 2020 9:12 am

More interesting than the screenshot would be how you did it...

ernesttan1976 · Mon Oct 05, 2020 10:57 am

//START OF FIT TRACE

//Connect to the DB using PDO.
try {

//print_r(PDO::getAvailableDrivers());
//print_r("<br/>");

/*
Connect to the local server using Windows Authentication and specify
the AdventureWorks database as the database in use. To connect using
SQL Server Authentication, set values for the "UID" and "PWD"
attributes in the $connectionInfo parameter. For example:
$connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database"=>"AdventureWorks");
*/

$uid="XXX";
$pwd="XXX";
$serverName = "localhost\MSSQL2014,1433";
$database="XXX";
$conn = new PDO( "sqlsrv:server=$serverName ; Database = $database", $uid, $pwd);

} catch (PDOException $e) {
print_r("Error: ".$e->getMessage());
die();
}

if('#DateFrom#'=== Undefined || '#DateTo#'=== Undefined || '#Project#'=== Undefined){

$sql = "SELECT Personnel.Name as PersonnelName, Trade.Trade, round(Trace.Duration/60.0-1,2) as Hours, CONVERT(varchar,Trace.Timestamp,20) as TimestampEntry, CONVERT(varchar,Trace.TimestampExit,20) as TimestampExit, Company.SiteName as Project, Location.Name as LocationName, CONVERT(varchar,Trace.Timestamp,11) as DateSort
FROM PTS.dbo.Trace
INNER JOIN PTS.dbo.Personnel ON Personnel.PID = Trace.PID
INNER JOIN PTS.dbo.Location ON Location.LocID = Trace.LocID
INNER JOIN PTS.dbo.Company ON Company.CompanySiteID = Location.CompanySiteID
INNER JOIN PTS.dbo.Trade ON Trade.TradeID = Personnel.TradeID
WHERE ((((Trace.Timestamp BETWEEN '2020-09-01' AND '2020-09-29') AND round(Trace.Duration/60.0-1,2) > 0)) OR (Trace.Timestamp BETWEEN '2020-09-29' AND DATEADD(day, 1, '2020-09-29'))) AND Company.SiteName LIKE '%P4601%'
ORDER BY DateSort, Personnel.Name
";

} else {

$sql = "SELECT Personnel.Name as PersonnelName, Trade.Trade, round(Trace.Duration/60.0-1,2) as Hours, CONVERT(varchar,Trace.Timestamp,20) as TimestampEntry, CONVERT(varchar,Trace.TimestampExit,20) as TimestampExit, Company.SiteName as Project, Location.Name as LocationName, CONVERT(varchar,Trace.Timestamp,11) as DateSort
FROM PTS.dbo.Trace
INNER JOIN PTS.dbo.Personnel ON Personnel.PID = Trace.PID
INNER JOIN PTS.dbo.Location ON Location.LocID = Trace.LocID
INNER JOIN PTS.dbo.Company ON Company.CompanySiteID = Location.CompanySiteID
INNER JOIN PTS.dbo.Trade ON Trade.TradeID = Personnel.TradeID
WHERE ((((Trace.Timestamp BETWEEN '#DateFrom#' AND '#DateTo#') AND round(Trace.Duration/60.0-1,2) > 0)) OR (Trace.Timestamp BETWEEN '#DateTo#' AND DATEADD(day, 1, '#DateTo#'))) AND Company.SiteName LIKE '%#Project#%'
ORDER BY DateSort, Personnel.Name";

};

$stmt = $conn->query($sql);
$rows = $stmt->fetchall(PDO::FETCH_ASSOC);

//Get the column names.
$columnNames = array();
if(!empty($rows)){
//We only need to loop through the first row of our result
//in order to collate the column names.
$firstRow = $rows[0];
foreach($firstRow as $colName => $val){
$columnNames[] = $colName;
}
}

//Load PHP Spreadsheet Library
require_once('phpspreadsheet/vendor/autoload.php');

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;

//styling arrays
//table head style
$tableHead = [
'font'=>[
'color'=>[
'rgb'=>'FFFFFF'
],
'bold'=>true,
'size'=>11
],
'fill'=>[
'fillType' => Fill::FILL_SOLID,
'startColor' => [
'rgb' => 'BCCDFF'
]
],
];
//even row
$evenRow = [
'fill'=>[
'fillType' => Fill::FILL_SOLID,
'startColor' => [
'rgb' => 'FFFFFF'
]
]
];
//odd row
$oddRow = [
'fill'=>[
'fillType' => Fill::FILL_SOLID,
'startColor' => [
'rgb' => 'C8FFF5'
]
]
];

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$spreadsheet->getDefaultStyle()
->getFont()
->setName('Arial')
->setSize(10);

//heading
$spreadsheet->getActiveSheet()
->setCellValue('A1',"Timesheet for ".'#Project#'.' from '.'#DateFrom#'.' to '.'#DateTo#');

//merge heading
$spreadsheet->getActiveSheet()->mergeCells("A1:H1");

// set font style
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);

// set cell alignment
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

//setting column width
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(8.5);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(16);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(16);

$sheet->fromArray(
$columnNames, // The data to set
NULL, // Array values with this value will not be set
'A2' // Top left coordinate of the worksheet range where
// we want to set these values (default is A1)
);

$sheet->fromArray(
$rows, // The data to set
NULL, // Array values with this value will not be set
'A3' // Top left coordinate of the worksheet range where
// we want to set these values (default is A1)
);

$spreadsheet->getActiveSheet()->getStyle('A2:H2')->applyFromArray($tableHead);

for($i=3;$i<=count($rows)+2;$i++) {
//set row style
if( $i % 2 == 0 ){
//even row
$spreadsheet->getActiveSheet()->getStyle('A'.$i.':H'.$i)->applyFromArray($evenRow);
}else{
//odd row
$spreadsheet->getActiveSheet()->getStyle('A'.$i.':H'.$i)->applyFromArray($oddRow);
}

}
// (3) AUTO FILTER
$sheet->setAutoFilter('A2:'.chr(64+count($columnNames)).(count($rows)+2));
//nuDebug('A2:'.chr(64+count($columnNames)).(count($rows)+2));

// (4) FREEZE PANE
//$sheet->freezePane('C2');

// (5) SAVE TO FILE
$writer = new Xlsx($spreadsheet);
$filename = 'FIT_TraceXLS.xlsx';
$filepath = './libs/upload/public/fit_trace/';

$writer->save($filepath.$filename);
//$writer->save('php://output');

if(!$fdl=@fopen($filepath.$filename,'r')){
die("Cannot Open File!");
} else {
header("Cache-Control: ");// leave blank to avoid IE errors
header("Pragma: ");// leave blank to avoid IE errors
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"".$filename."\"");
header("Content-length:".(string)(filesize($filename)));
sleep(1);
fpassthru($fdl);
}

exit;

// Set the content-type:
//header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//header('Content-Length: ' . filesize($filename));
//readfile($filename); // send file
//unlink($filename); // delete file
//exit;

//header('Content-Type: application/vnd.ms-excel');
//header('Content-Disposition: attachment; filename="phpspreadsheet/FIT_TraceXLS.xlsx"');
//$writer->save("php://output");

unset($writer);
unset($sheet);
unset($spreadsheet);
echo 'FIT_TraceXLS.xlsx saved';

ernesttan1976 · Mon Oct 05, 2020 11:09 am

Hi Kevin,

Above is the source code I put into a nuProcedure.
I copied the phpspreadsheet library to the vendors folder under Nubuilder.
The database I'm extracting from is MSSQL, need to change to others as needed.

Unread post by **kev1n** » Wed Oct 07, 2020 6:12 pm

Thanks!

treed · Unread post by **treed** » Mon Apr 03, 2023 5:51 pm

ernesttan1976 wrote: ↑Mon Oct 05, 2020 11:09 am Hi Kevin,

Above is the source code I put into a nuProcedure.
I copied the phpspreadsheet library to the vendors folder under Nubuilder.
The database I'm extracting from is MSSQL, need to change to others as needed.

I am also trying to use PhpSpreadsheet. Is there a way to make this work without putting the library within the nuBuilder directory structure? My goal is to keep the nuBuilder update process as simple as possible.

nuBuilder Forum

PhpSpreadsheet run as a Procedure

PhpSpreadsheet run as a Procedure

Re: PhpSpreadsheet run as a Procedure

Re: PhpSpreadsheet run as a Procedure

Re: PhpSpreadsheet run as a Procedure

Re: PhpSpreadsheet run as a Procedure

Re: PhpSpreadsheet run as a Procedure

Re: PhpSpreadsheet run as a Procedure

Re: PhpSpreadsheet run as a Procedure

Re: PhpSpreadsheet run as a Procedure

Re: PhpSpreadsheet run as a Procedure