How to use PhpSpreadsheet with App Engine and Google Cloud Storage-CodePudding

I've been banging my head on this one for a few days and thought it was time to ask. The task seems simple - Use PhpSpreadsheet to write an Excel sheet to a Google Cloud Storage bucket.

Currently my thought is that the file will need to streamed to the bucket. The other option it seems would be generating it the file in memory and then writing it to the bucket which I feel like could be resource intensive.

I'm using App Engine with a Standard environment and PHP 7.4.

To test it out out I've been using the most basic code to make the sheet -

require 'vendor/autoload.php';    
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;    
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');    
$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

Then I'm tying to combine with a basic write to a storage bucket -

require 'vendor/autoload.php';
use Google\Cloud\Storage\StorageClient;

 /**
 * @param string $bucketName The name of your Cloud Storage bucket.
 * @param string $objectName The name of your Cloud Storage object.
 * @param string $source The path to the file to upload.
 */
function upload_object($bucketName, $objectName, $source)
{
    // $bucketName = 'my-bucket';
    // $objectName = 'my-object';
    // $source = '/path/to/your/file';

    $storage = new StorageClient();
    $file = fopen($source, 'r');
    $bucket = $storage->bucket($bucketName);
    $object = $bucket->upload($file, [
        'name' => $objectName
    ]);
}

These are basically the code samples from PhpSpreadsheet and Google. I've gotten both to work on thier own. Does anyone have any ideas about getting the generated Excel sheet to end up on the Cloud Storage bucket?

// Added 2022-01-13 //////////////////////////////

After a few comments I've tried different options and have listed the results below. I think it is getting close, but not quite there.

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Google\Cloud\Storage\StorageClient;

$bucketName = 'BUCKETNAME'; // Name of the GCS bucket
$objectName = 'new_file_name.xlsx'; // Name of the new file we are creating
$objectLocation = 'gs://'.$bucketName.'/'.$objectName; // Path of the new file

try {

    $storage = new StorageClient();
    $storage->registerStreamWrapper();

    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setCellValue('A1', 'Hello World !');
    $sheet->setCellValue('B1', 'Hello You !');

    $writer = new Xlsx($spreadsheet);
    
    // Trial 1
    // This displays "Could not open file "gs://BUCKETNAME/new_file_name.xlsx" for writing." in the browser window.
    $writer->save($objectLocation);

    // Trial 2
    // This creates an empty file in the GCS bucket and displays the XLSX file code on the screen
    //$file = fopen($writer->save('php://output'), 'r');
    //$bucket = $storage->bucket($bucketName);
    //$object = $bucket->upload($file, [
    //    'name' => $objectName
    //]);

    // Trial 3
    // This displays "Could not open file "new_file_name.xlsx" for writing." in the browser window.
    //$file = fopen($writer->save($objectName), 'r');
    //$bucket = $storage->bucket($bucketName);
    //$object = $bucket->upload($file, [
    //    'name' => $objectName
    //]);
}
catch (Exception $e) {
    echo $e->getMessage();
}

CodePudding user response：

Here's a working sample code:

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Google\Cloud\Storage\StorageClient;

$bucketName = 'BUCKET_NAME'; // Name of the GCS bucket
$objectName = 'FILENAME.xlsx'; // Name of the new file we are creating

try {
    $storage = new StorageClient();
    $storage->registerStreamWrapper();

    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setCellValue('A1', 'Hello World !');
    $sheet->setCellValue('B1', 'Hello You !');

    $writer = new Xlsx($spreadsheet);

    ob_start();
    $writer->save('php://output');
    $content = ob_get_contents();
    ob_end_clean();

    $bucket = $storage->bucket($bucketName);
    $object = $bucket->upload($content, [
        'name' => $objectName
    ]);
}
catch (Exception $e) {
    echo $e->getMessage();
}
?>

Below are screenshots of the spreadsheets with content in the Google Cloud Storage:

In PHP, there is a reference called PHP Output Control.

I used ob_start() to create an output buffer and ob_end_clean() to delete the topmost output buffer and all of its contents without sending anything to the browser.

I also used php://output to store the file temporarily in the script's working directory according to the PHPSpreadsheet Documentation.