PHP Fill with values an existing .xlsb file with data from Oracle

Question

I want to write into my .xlsb excel file which name is the number of the day.
For exemple the name of the file for yesterday is 23.xlsb.

Initial the name of the file is "Template.xlsb" and the location is different. As you can see this file it's copied and renamed in a new location. In this file I have a lot of macros and vba codes and that's why I don't want to create a new Excel file.

At the end, the link for this Excel file is in this variable $renamed_link.
$renamed_link = C:\Documents and Settings\Administrator\Desktop\Rezultate DIDU\2017\Apr\23.xlsb.

I want to fill the first worksheet, named "Parametri" with data from my sql query($parametri).
The range from worksheet wich will be filled with data is from A2 until T and the total number of the rows. The table have 20 columns.

<?php
require(realpath(dirname(__FILE__)."/PHPExcel-1.8/Classes/PHPExcel.php"));
$sql_data = date('d.m.Y', strtotime('-1days'));

    $conn = oci_connect('USER', 'PASS', 'dark:1521/DAR');
    if (!$conn) {
        $e = oci_error();
        trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
    }
    Else {echo 'Connection successfully !';
    }
$parametri = oci_parse($conn,"SELECT * FROM R0508UNITP WHERE TO_DATE('${sql_data}','DD.MM.YYYY') BETWEEN R0508UNITP.R0508VFROM AND R0508UNITP.R0508VTILL");

oci_execute($parametri);

echo "<table border='1'>\n";
while ($row = oci_fetch_array($parametri, OCI_ASSOC+OCI_RETURN_NULLS)) {


echo "<tr>\n";
foreach ($row as $item) {
    echo "    <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;") . "</td>\n";
}
    echo "</tr>\n";
}
echo "</table>\n";

$day = date('j', strtotime('-1 days'));
$month = date('M', strtotime('-1 days'));
$year = date('Y', strtotime('-1 days'));


$link = "C:\Documents and Settings\Administrator\Desktop\Rezultate DIDU\\${year}\\${month}";
$template_link = 'C:\Documents and Settings\Administrator\Desktop\Rezultate DIDU\Template.xlsb';
$destination_link = "C:\Documents and Settings\Administrator\Desktop\Rezultate DIDU\\${year}\\${month}".'\Template.xlsb';
$renamed_link = "C:\Documents and Settings\Administrator\Desktop\Rezultate DIDU\\${year}\\${month}\\${day}".'.xlsb';


if(!is_dir($link)) 
{

mkdir($link, 0777,true);

}

if(!file_exists($destination_link))
{
    copy($template_link,$destination_link);

} else {
}

rename($destination_link,$renamed_link);
?>

Print screen of my php table(some of it).
This table I want to be in my excel file.

Answer 1

Parsing and (especially) editing and xlsb (miscrosoft excel binary file) is not trivial at all.

At the time of writing you can find the ~1000 pages format specification pdf at this link: http://interoperability.blob.core.windows.net/files/MS-XLSB/[MS-XLSB].pdf

To avoid xlsb complexity there are PHP libraries that let you manipulate those files:

EasyXLS is a commercial solution:

https://www.easyxls.com/

Another option is PHPExcel

https://github.com/PHPOffice/PHPExcel

it's licensed under LGPL. This library let you write (among the others) BIFF 8 (.xls) Excel 95 and above files. You should check if your xlsb files fall under this category.

---

As you've fetched the data from the db (you already succeeded in doing this) then inserting that data into the xlsb template file and saving as a new file is trivial. Both libraries offer examples and tutorials.

Just one of them: https://www.easyxls.com/manual/tutorials/php/read-xlsb-file.html

---

UPDATE

As written above EasyXLS can read xlsb files whose internal format is BIFF12.

This is stated on their website, I didn't tested actually their software but there is a "trial version" available for download so you may give it a try.

EasyXLS is not free and is around $295 at the time of writing so may be out of budget depending on your project.

EasyXLS is not a pure PHP library: the .NET version comes with a COM+ Library with support for PHP (among other languages). For this reason it's not suitable *nix systems but as you're on Windows it could be a viable solution.

---

Let's see PHPExcel

Development of this library has been abandoned in favour of the the new PhpSpreadsheet

The latter is still in a development stage. The current release however is almost complete and usable and I would expect a production / stable release to be available soon.

PhpSpreadsheet supports BIFF5 and BIFF8 formats that are the files with extension .xls.

It does not support BIFF12 - .xlsb files.

---

Testing PHPSpreadsheet

The documentation is very clear and thorough.

I downloaded and installed the current release with Composer following the instructions available in the documentation.

I "downgraded" the Template.xlsb file you provided to .xls format (using Excel). Apparently I didn't lose any of the functionality of the original file.

Then I was able to edit it with the following code...

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

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
$spreadsheet = $reader->load( 'Template.xls' );

$worksheet = $spreadsheet->getSheetByName( 'PARAMETRI' );

$worksheet->setCellValue( 'A2', 123 );

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xls( $spreadsheet );
$writer->save( "Template-Modified.xls" );

...but the saved file Template-Modified.xls has lost the macros.

However running the same code on another .xls file I made with macros succeeded in the process without loosing anything.

Probably the macros on your file get lost as they are password protected in the source file.

---

I think with minor compromises you may succeed in your project with PHPSpreadsheet.

I don't know if you can downgrade to .xls and / or remove password protection from macros in your template files.

Anyway PHPSpreadsheet development is very active, the documentation and code is good.

You may get further informations or support directly from the development team via GitHub