The USE statement is not recognized by scriptcase. (PHPSpreadsheet)

Scriptcase 9 General
#1

Hi, I am trying to use PHPSpreadsheet with Scriptcase, but when I run the application I get this error message. “Parse error: syntax error, unexpected ‘use’ (T_USE) in C:\Program Files\NetMake\v9\wwwroot\scriptcase\app\ClaroRM\Test\index.php on line 1629”

Does anyone know why this error is generated in scriptcase ?.

The line that generates the error is this: “use PhpOffice\PhpSpreadsheet\Spreadsheet;” It is the first line of the code and there are no missing periods and commas or other syntax errors that could generate the error.

Can someone give me an idea of how to solve this problem? I need to generate excel files from my application and PHPExcel does not work correctly since all the files it generates are not recognized by Excel or -Excel indicates that the files are corrupt.

Thank you

Aldo,

#2

Hi

I still do not know why the USE statement in scriptcase does not work or if there is a way to use it and unfortunately I do not know it, but looking for a solution and spending many hours in Google, I found the way to make phpspreadsheet work for my purpose.

I leave the code in case anyone can use it as an idea. I am still modifying it so that it does everything I need, but just as it is, it creates and stores the xlsx file without problem.

I apologize for the Spanish texts in the code, but I hope this code is useful to someone.

$contenido="<?php
require ('../../../vendor/autoload.php');

use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;".'

require_once "../../../vendor/phpoffice/phpSpreadsheet/src/Bootstrap.php";

$helper = new Sample();
if ($helper->isCli()) {
    $helper->log("C?digo para ejecutarse s?lo en un navegador Web" . PHP_EOL);
    return;
}
// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Establecer propiedades del documento
$spreadsheet->getProperties()->setCreator("RM")
    ->setLastModifiedBy("RM")
    ->setTitle("Registro masivo")
    ->setSubject("Office 2007 XLSX")
    ->setDescription("Documento Office 2007 XLSX, generado usando RM.")
    ->setKeywords("office 2007 openxml php")
    ->setCategory("Test result file");

// Definir formatos

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

$styleArray = [
    "font" => [
        "bold" => true,
        "color" => ["argb" => "FFFFFF"],
        "size" => 16,

    ],
    "alignment" => [
        "horizontal" => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
    ],
    "borders" => [
        "top" => [
            "borderStyle" => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        ],
    ],
    "fill" => [
        "fillType" => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
        "rotation" => 90,
        "startColor" => [
            "argb" => "D52B1E",
        ],
        "endColor" => [
            "argb" => "D52B1E",
        ],
    ],
];

$styleArray2 = [
    "borders" => [
        "allBorders" => [
            "borderStyle" => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            "color" => ["argb" => "000000"],
        ],
    ],
    "fill" => [
        "fillType" => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
        "rotation" => 90,
        "startColor" => [
            "argb" => "ADAFAF",
        ],
        "endColor" => [
            "argb" => "ADAFAF",
        ],
    ],
];

$styleArray3 = [
    "font" => [
        "bold" => true,
        "color" => ["argb" => "000000"],
        "size" => 10,
    ],
    "alignment" => [
        "horizontal" => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
    ],
    "borders" => [
        "top" => [
            "borderStyle" => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        ],
    ],
    "fill" => [
        "fillType" => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
        "rotation" => 90,
        "startColor" => [
            "argb" => "ADAFAF",
        ],
        "endColor" => [
            "argb" => "ADAFAF",
        ],
    ],
];

$spreadsheet->getActiveSheet()->getStyle("A1:G1")->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle("A2:G2")->applyFromArray($styleArray3);
$spreadsheet->getActiveSheet()->getStyle("A5:G5")->applyFromArray($styleArray2);

$spreadsheet->getActiveSheet()->getColumnDimension("B")->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension("C")->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension("D")->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension("E")->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension("F")->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension("G")->setAutoSize(true);

// Insertar fecha en formato Excel
$spreadsheet->getActiveSheet()->getStyle("A2") 
    ->getNumberFormat() 
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DDMMYYYY); 

// Obtener fecha actual y hora 
// Convertir a Excel date/time 
$dateTime = time();  
$excelDateValue = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($dateTime );  

// Fijar la celda con el valor en formato date/time 
$spreadsheet->setActiveSheetIndex(0)->setCellValue("A2",$excelDateValue); 

// Crear encabezado de columnas
$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue("A1", "Usuarios Registrados por proceso masivo")
    ->setCellValue("A5", "Pais")
    ->setCellValue("B5", "Gerencia")
    ->setCellValue("C5", "Nombre")
    ->setCellValue("D5", "Area")
    ->setCellValue("E5", "Nombre")
    ->setCellValue("F5", "Usuario")
    ->setCellValue("G5", "Nombre");

// Ejecutar consulta para leer todos los registros que cumplan con la condici?n buscada.

$serverName = "Your server"; //serverName\instanceName;
$connectionInfo = array( "Database"=>"Your database", "UID"=>"user", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
    //echo "Conexi?n establecida.<br />";
}else{
    //echo "Conexi?n no se pudo establecer.<br />";
     die( print_r( sqlsrv_errors(), true));
}

$sql_string    = "SELECT UMD.Pais, UMD.Gerencia, G.Nombre AS NGerencia, UMD.Area, A.Nombre, Usuario, lcname collate SQL_Latin1_General_CP1_CI_AS, UMD.Correlativo  FROM UsuarioMasivoDetalle AS UMD INNER JOIN     Gerencia AS G ON UMD.Gerencia  = G.Gerencia AND UMD.Pais collate SQL_Latin1_General_CP1_CI_AS = G.Pais INNER JOIN AREA AS A ON UMD.Area = A.Area AND UMD.Gerencia  = A.Gerencia AND UMD.Pais collate SQL_Latin1_General_CP1_CI_AS = A.Pais WHERE Procesado = 1 AND Rechazado = 0 AND Detalle IN '. $to_copy. '"'. ';  

// Ejecutar la consulta 
$stmt = sqlsrv_query( $conn, $sql_string);  
if ( $stmt )  
{  
    //echo "Comando ejecutado.

";
}
else
{
//echo "Error en la ejecuci?n.
";
die( print_r( sqlsrv_errors(), true));
}

$cell = 6;

// Iterar atrav?s del resultado para mover cada campo a la celda correspondiente  

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC))  
{  
     // Se utiliza mb_convert_encoding para convertir de UTF-8 a ISO-8859-1 a fin de que se muestren las tildes en el campo lcnombre
    $spreadsheet->setActiveSheetIndex(0)
        ->setCellValue("A".$cell, $row[0])
        ->setCellValue("B".$cell, $row[1])
        ->setCellValue("C".$cell, mb_convert_encoding($row[2], "UTF-8", "ISO-8859-1"))
        ->setCellValue("D".$cell, $row[3])
        ->setCellValue("E".$cell, mb_convert_encoding($row[4], "UTF-8", "ISO-8859-1"))
        ->setCellValue("F".$cell, $row[5])
        ->setCellValue("G".$cell, mb_convert_encoding($row[6], "UTF-8", "ISO-8859-1"));
    $cell++;
    $filename= "Correlativo ".$row[7]. ".xlsx";
    $path = $_SERVER["DOCUMENT_ROOT"]."/scriptcase/file/docCargaMasiva/".$filename;

} 

sqlsrv_close( $conn);

// Renombrar hoja

$spreadsheet->getActiveSheet()->setTitle("Registro masivo");

// Fijar el ?ndice de la hoja a la primera hoja, as? Excel abre esta hoja al abrir el archivo
$spreadsheet->setActiveSheetIndex(0);

// Redireccionar la salia al cliente web browser (Xlsx).  Estas lineas har?an que el archivo se descargue a la computadora del usuario.
//header("Content-Encoding: ISO-8859-1");
//header("Content-type: application/vnd.ms-excel; charset=ISO-8859-1");
//header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");'.'
//header("Content-Disposition: attachment; filename=$path");
//header("Cache-Control: max-age=0");

// Si se utiliza IE 9 esta linea es necesaria

header("Cache-Control: max-age=1");

// Si se usa IE sobre SSL, estas lineas son necesarias

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); // always modified
header("Cache-Control: cache, must-revalidate"); // HTTP/1.1
header("Pragma: public"); // HTTP/1.0

$writer = IOFactory::createWriter($spreadsheet, "Xlsx");
//$writer->save("php://output"); // Usar esta linea si se desea que el usuario descargue a su computadora, pero recuerde quitar de comentario las lineas header de arriba.
$writer->save($path);

// Liberar memoria

$spreadsheet->disconnectWorksheets();
unset($spreadsheet);?>';

//$ejecutar = "/usr/local/bin/php -f" . $_SERVER["DOCUMENT_ROOT"]."/scriptcase/file/docCargaMasiva/archivoxlsx.php";


$file = fopen("archivoxlsx.php", "a");
fwrite($file, $contenido);
fclose($file);
header ("Location: archivoxlsx.php");
#3

This is a very clever solution and can used to get around the fact that you cannot use “USE” in SC because USE needs to be the first line of the code, and SC puts it somewhere in the middle, even on a blank form. I just used it in a Wordpress API library that uses USE.

Just to add to this, be aware that everything in the main script stops after header("Location… as the script in the quotes takes over. Therefore if you want to capture any results from the script in the quotes you need to add a second header ("Location:… and do something like this inside the script in quotes and at the end of it…

header (“Location: …/blank_wp_update/index.php?ID=$id&ProbotID=’.$probotID.’&price=’.$p rice.’”);

So there are two header ("Location:… lines of code, one in the quotes and the other outside the quotes. This then passes the returned data, assuming you have put them into variables to second SC app, which can also be a blank app, and from there you can carry on as normal, you have got around the issue that USE does not work in SC. ,

Here is my entire code for using a Woocommerce API inside SC. EDIT I needed to add an unlink command to delete the file created each time, otherwise it keeps appending the script to the prod.php file

$wp_code = [wp_code];
$name = [name];
$price = [price];
$description = [description];
$shortdescription = [shortdescription];
// $id = [ID];
$probotID = [probotID];

if ($wp_code > 0){
//update price only here
sc_redir(‘form_probot_edit_scripts_user’, id = $id);// temp until coded to stop duplicate items
}
else
{

$code = '<?php
require (“C:/zampp/htdocs/stripe/woocommerce/vendor/autoload.php”);// needs changing in prod to the libraries path
use Automattic\WooCommerce\Client;
$woocommerce = new Client(
“https://yourWordPress_site.com/”, // Your store URL
“ck_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”, // Your consumer key
“cs_xxxxxxxxxxxxxxxxxxxxxxxxxx”, // Your consumer secret
[
“wp_api” => true, // Enable the WP REST API integration
“version” => “wc/v3” // WooCommerce WP REST API version
]
);

$data = [
“name” => “’.$name.’”,
“type” => “simple”,
“regular_price” => “’.$price.’”,
“sku” => “’. $probotID .’”,
“description” => “’. $description .’”,
“short_description” => “’. $shortdescription .’”,
“categories” => [
[
“id” => 27
],
[
“id” => 15
]
],
“images” => [
[
“src” => “http://demo.woothemes.com/woocommerce/wp-content/uploads/sites/56/2013/06/T_2_front.jpg”
],
[
“src” => “http://demo.woothemes.com/woocommerce/wp-content/uploads/sites/56/2013/06/T_2_back.jpg”
]
]
];

$result = $woocommerce->post(“products”, $data);
$id = $result->id;
header (“Location: …/blank_wp_update/index.php?ID=$id&ProbotID=’.$probotID.’&price=’.$p rice.’”);

?>
';
$file = “prod.php”;

// Use unlink() function to delete the file each time
if (!unlink($file)) {
// echo ("$file_pointer cannot be deleted due to an error");
}
else {
// echo ("$file has been deleted");
}

$file = fopen(“prod.php”, “a”);
fwrite($file, $code);
fclose($file);
$response = header (“Location: prod.php”);
}

#4

try this:
// remark use statement
//use PhpOffice\PhpSpreadsheet\Spreadsheet;
$spreadsheet = new PhpOffice\PhpSpreadsheet\Spreadsheet();