nuBuilder Forum

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!

Hi,

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

Hi,

Were you able to resolve your issue?

Oh yes thank you!

Screenshot

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

//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';

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.

Thanks!

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.