Anybody made PHPSpreadsheet work

Scriptcase 9 General
#1

Hi

Been looking at migrating existing PHPExcel Spreadsheet (blank) apps to PHPSpreadsheet.

I can’t even get the basics to work… create an empty spreadsheet as per the PHPSpreadsheet help page.

Elsewhere in the forum it says the way that SC implements the tool, the USE functions cant be used (They have to be at the start of a script I think), but the workaround seems confused (at least I couldn’t understand it :dizzy_face:

The HELP? item in the knowledge base is worse than useless… It has

  1. A link to an OLD version of PHPSpreadsheet
  2. A macro call (includesc_url_library) that as far as I know doesn’t exist
  3. All of the pictures are missing (presumably because of the dumb reorganisation of the forum a few months ago!)

I can get the lib to load, (I tried several different ways) but as soon as I try to use the first command:
$documento= new \PhpOffice\PhpSpreadsheet\Spreadsheet();

I get a syntax error within the Spreadsheet class. (I presume because the ‘use’ command in PHPSpreadsheet help, had to be deleted)

Code is:

require_once(’…/…/…/…’.$this->Ini->path_prod. ‘/third/phpspreadsheet/vendor/autoload.php’);

echo(“Hello
”);
//sc_include_library(“sys”,“PHPSpreadsheet”,“vendor/autoload.php”);

//includesc_url_library(“sys”,“PHPSpreadsheet”,“vendor/autoload.php”);

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue(‘A1’, ‘Hello World !’);

$writer = new Xlsx($spreadsheet);
$writer->save(‘hello world.xlsx’);

Error is:

Parse error: syntax error, unexpected ‘=’ in C:\Program Files\NetMake\v9Jun18\wwwroot\scriptcase\prod\third\phpspreadsheet\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php on line 2460

Can’t get much simpler than this… which begs the question why a similar simple example is not provided either in the knowledgebase or samples.

2 Likes
#2

Hi,
This works for me (library version and date [1.11.0] - 2020-03-02)
sc_include_library(“prj”, “Spreadsheet”, “vendor/autoload.php”,true,true);
$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($rutaXLS);
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
$objPHPExcel = $reader->load($rutaXLS);
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->setActiveSheetIndex(1)

#3

Thanks for the help, but thats the wrong use-case. I’m trying to simply WRITE a spreadsheet, not READ it.
I notice some you’ve used PHPExcel variable names so I assume youve migrated this successfully.

My basic problem in the WRITE scenario is that the first line (Instantiate a new blank spreadsheet) fails with a syntax error (Within the PHPSpreadsheet class itself, not the calling code) and I can’t find any sensible help to track down whats going on, or what I need to do.

EDIT:
For anyone else struggling… I think the basic problem is that it requires PHP version 7.2 or newer to develop using PhpSpreadsheet. I have a standard 9.4 Scriptcase with PHP 7.0

I do intend to upgrade to 7.3, but the process with Scriptase seems inordinately complex and I don’t have time to do a complete upgrade and test all of my apps right now. Even when I do that on the IDE, I then have to test all of the other things on my server for compatibility with 7.3, so not a trivial task.

#4

Hello, how are you?., i have same problem., please you can confirm if you have solved this issue.?

#5

My EDIT, above indicates the likely cause (needs PHP 7.2), but I have not followed this through to a full solution as the upgrade would be too big a job for me right now.

#6

this works great in SC , great library , auto detects csv, excel , many formats.

sc_include_library(“prj”, “Spreadsheet”, “vendor/autoload.php”,true,true);

notice that you upload and unzip the library as a project library and not as a public library (otherwise you need to use sys and not prj as the first parameters
also the reference to all functions works only via the full path
\PhpOffice\PhpSpreadsheet\IOFactory::createR… bla bla

for some reason USE in SC does not work

#7

Hi @maximnl
I am sorry for asking this question after more than an year.
I am getting the following error and I am not able to locate the autoload.php

Failed opening required ‘…/_lib/libraries/grp/PHPSSheet/vendor/autoload.php’ (include_path=’.;C:\php\pear’) in C:\Program Files\NetMake\v9-php81\wwwroot\scriptcase\app\Logistics_Tracking_lib\lib\php\nm_functions.php:200 Stack trace: #0 C:\Program Files\NetMake\v9-php81\wwwroot\scriptcase\app\Logistics_Tracking\blank\index.php(1993): sc_include_library(‘prj’, ‘PHPSSheet’, ‘vendor/autoload…’)

I am using Scriptcase 9.9. It comes with the PHP Library bunded as external. I just saved that library as a project library.
NOTE I Also tried directly using the external library (as sys) . Still the same issue.
The vendor folder or the autoload.php folder is not available in the library folder

Code Segment

// includesc_url_library(“prj”,“PHP”,“vendor/autoload.php”);

sc_include_library(“prj”,“PHPSSheet”,“vendor/autoload.php”);
//sc_include_library(“sys”,“PhpSpreadsheet”,“vendor/autoload.php”);

$document= new \PhpOffice\PhpSpreadsheet\Spreadsheet(); //$documento changed to $document

$document

->getProperties()

->setCreator(“Prueba”)

->setLastModifiedBy(‘JuanAquino’) // última vez modificado por

->setTitle('Miprimer documento creado con PhpSpreadSheet')

->setSubject('Elasunto')

->setDescription(‘Estedocumento fue generado para prueba’)

->setKeywords(‘etiquetas opalabras clave separadas por espacios’)

->setCategory(‘Lacategoría’);

$documentName = “Mi primer archivo.xlsx”; //$nombreDelDocumento changed to $documentName

header(‘Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);

header(‘Content-Disposition:attachment;filename="’ . $documentName . ‘"’);

header(‘Cache-Control:max-age=0’);

#Como ya hay una hoja por defecto, la obtenemos, no la creamos

$productData = $document->getActiveSheet(); //$hojaDeProductos

$productData->setTitle(“Productos”);

#Escribir encabezado de los productos

//$encabezado=[“id”,“nombre”,“apellido”,“edad”,“nacionalidad”,“profesion”,“sexo”,“nacimiento”];

$header =[“id”,“nombre”,“apellido”,“edad”,“sexo”,“fechaNacimiento0”,“nacionalidad”]; //$encabezado changed to $header

#El último argumento es por defecto A1 pero lo pongo para que seexplique mejor → The last argument is A1 by default but I put it to explain it better

$productData->fromArray($header,null, “A1”);

//$consulta= “SELECT id, nombre, apellido, edad, nacionalidad, profesion,sexo, nacimiento FROM dbo.tabla2”;

$query= “SELECT id, nombre, apellido, edad, sexo, fechaNacimiento0,nacionalidad FROM dbo.prueba”; //$consulta

sc_select(ds,$query);
echo “dumping data”;
var_dump({ds});
echo "End Dump ";

/* For debug purpose

#Comenzamos en la 2 porque la 1 es del encabezado We start at 2 because 1 is from the header

$rowNumber= 2; //$numeroDeFila changed to $rowNumber

$product= {ds}; //$producto

foreach($products $key => $value) //$valor changed to value

{

$id= $value[‘id’];

$nombre = $value[‘nombre’];

$apellido = $value[‘apellido’];

$edad = $value[‘edad’];

$nacionalidad= $value[‘nacionalidad’];

$profesion= $value[‘profesion’];

$sexo = $value[‘sexo’];

$fechaNacimiento = $value[‘nacimiento’];

#Escribirlos en el documento

$productData->setCellValueByColumnAndRow(1,$rowNumber, $id);

$productData->setCellValueByColumnAndRow(2,$rowNumber, $nombre);

$productData->setCellValueByColumnAndRow(3,$rowNumber, $apellido);

$productData->setCellValueByColumnAndRow(4,$rowNumber, $edad);

$productData->setCellValueByColumnAndRow(5,$rowNumber, $nacionalidad);

$productData->setCellValueByColumnAndRow(6,$rowNumber, $profesion);

$productData->setCellValueByColumnAndRow(7,$rowNumber, $sexo);

$productData->setCellValueByColumnAndRow(8,$rowNumber, $fechaNacimiento);

$rowNumber++;

}
End for Debug Purpose */

$writer= PhpOffice\PhpSpreadsheet\IOFactory::createWriter($document,‘Xlsx’);

$writer->save(‘php://output’);

exit;

Thanks in advance

#8

I was also having problems and then I downloaded the latest version myself https://github.com/PHPOffice/PhpSpreadsheet and created a new library and imported it myself.

image
image1793×380 24.6 KB

My include statement links to the php code that builds the sheet
sc_include_library(“prj”, “excel_lib”, “horizontal_time_sheet.php”, “include_once”, “true”);
Inside the php file:

<?php require "vendor/autoload.php"; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; // create new sheet $spreadsheet = new Spreadsheet(); $spreadsheet->getProperties() ->setCreator($GLOBALS['excel_parms']['Creator']) ->setLastModifiedBy($GLOBALS['excel_parms']['ModifiedBy']) ->setTitle($GLOBALS['excel_parms']['Title']) ->setSubject($GLOBALS['excel_parms']['Subject']) ->setDescription($GLOBALS['excel_parms']['Description']) ->setKeywords($GLOBALS['excel_parms']['Keywords']) ->setCategory($GLOBALS['excel_parms']['Category']); // set values of cells
#9

Thanks for the response. But sorry I have lost you.
Can you please guide me as to how the new library was created ? That is if it is not a problem or too much ?
Thanks

#10

I suggest looking at this Scriptcase Macros: Control Module ( sc_include_lib ) - YouTube

#11

Hi Nico
Got it working
I posted the working code in this forum as a new Topic. Just so others can refer / add improvements with Topic title PHPSpreadSheet in Scriptcase
Thanks to you and others for the help

2 Likes