2. PHPRunner Tips and Tricks
  3. Create invoice Excel document using PHPOffice/PhpSpreadsheet
This topic is locked

Create invoice Excel document using PHPOffice/PhpSpreadsheet

 		9/30/2018 2:43:55 PM
PHPRunner Tips and Tricks
fhumanes author


In this example, we are going to use the PHP library PHPOffice/PhpSpreadsheet which is the evolution of PHPExcel, to obtain fully customized Excel files(xlsx).
This article is a continuation of the previous one where we explained how you can use PHPWord to use a template and create a Word document(docx) with the data of an invoice or delivery note.
Starting from the same data and in the same situation we will have 2 new buttons to obtain an Excel file (xlsx) or that same file passed to Acrobat PDF format(pdf).
The functions that the code does, triggered from a button of the VIEW page of invoices, is:

  • Read the template that is an Excel 2007 file (xlsx).
  • Recall the data of the Database.
  • Paste the customer identification data in the template.
  • For each one of the invoice lines, adding a new row and pasting the data of that line.
  • Create the Excel file or, if necessary, pass that excel file to a PDF file.
    To have the complete example, connect to My BLOG.
    Code:



<?php

require_once __DIR__ . '/../PhpSpreadsheet/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

use PhpOffice\PhpSpreadsheet\Shared\Date;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Template processor instance creation

$reader = IOFactory::createReader('Xlsx');

$spreadsheet = $reader->load(__DIR__ . '/PlantillaFactura.xlsx');

$idfactura= $_SESSION['idfactura'] ; // identificación de factura a obtener

// Recuperar datos de factura

$sql="SELECT Nif, NombreRazonSocial, Domicilio, RestoDomicilio, FechaFactura, TotalFactura FROM factura where idfactura = $idfactura";

$resql=db_query($sql,$conn);

$data=db_fetch_array($resql);

// Variables on different parts of document

$FechaFactura=$data['FechaFactura'];

$spreadsheet->getActiveSheet()->setCellValue('D1', Date::PHPToExcel($data['FechaFactura']));

$spreadsheet->getActiveSheet()->setCellValue('B2', $data['Nif']);

$spreadsheet->getActiveSheet()->setCellValue('B3', $data['NombreRazonSocial']);

$spreadsheet->getActiveSheet()->setCellValue('B5', $data['Domicilio']);

$spreadsheet->getActiveSheet()->setCellValue('B6', $data['RestoDomicilio']);

// read lines the invoice

$sql="SELECT Nombre, Precio, Cantidad, Valor FROM linea_factura where factura_idfactura= $idfactura ";

$rsSql=db_query($sql,$conn);

$baseRow = 10;

$r=0;

while ($data2 = db_fetch_array($rsSql)){

$row = $baseRow + $r;

$spreadsheet->getActiveSheet()->insertNewRowBefore($row, 1);

$spreadsheet->getActiveSheet()->setCellValue('A' . $row, $r + 1)

->setCellValue('B' . $row, $data2['Nombre'])

->setCellValue('C' . $row, $data2['Precio'])

->setCellValue('D' . $row, $data2['Cantidad'])

->setCellValue('E' . $row, $data2['Valor']);

$r= $r+1;

}

$spreadsheet->getActiveSheet()->removeRow($baseRow - 1, 1);

// -------------------- v pie para salvar el nuevo documento Word ------------------

$temp_file = tempnam(sys_get_temp_dir(), 'Excel');

// Save EXCEL

$writer = new Xlsx($spreadsheet);

$writer->save($temp_file);

// Save PDF

// $writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf($spreadsheet);

// $writer->save($temp_file);

// ------------------ Operation with file result -------------------------------------------

$documento = file_get_contents($temp_file);

unlink($temp_file); // delete file tmp

header("Content-Disposition: attachment; filename= factura.xlsx");

header('Content-Type: application/excel');

echo $documento;

?>
fhumanes author 10/1/2018

You can temporarily execute it in: Demo
lefty 10/4/2018



You can temporarily execute it in: Demo


Great Topic. I assume you just download the https://github.com/PHPOffice/PhpSpreadsheet plugin to the plugin area of PHPrunner and can you tell me where the write permissions should be changed?

Thanks.
G
 		gagsoft 10/6/2018



Great Topic. I assume you just download the https://github.com/PHPOffice/PhpSpreadsheet plugin to the plugin area of PHPrunner and can you tell me where the write permissions should be changed?

Thanks.


Is it possible to do this on Windows server?
Thanks

Peter G
fhumanes author 3/11/2019

This code is not dependent on the operating system.

Yes it is dependent on the PHP version. I have tested it with version 7.1 and 7.2
fhumanes author 3/11/2019



Great Topic. I assume you just download the https://github.com/PHPOffice/PhpSpreadsheet plugin to the plugin area of PHPrunner and can you tell me where the write permissions should be changed?

Thanks.



Hello:

I have not understood your question.

I leave this URL to a new article where I show other examples.

https://fhumanes.com/blog/otros-ejemplos/crear-informes-con-phpspreadsheet/

In this new one it is written to make reports with this library.

I hope it serves you.

Regards,
fhumanes author 6/29/2019

Convert MS Office document to PDF
Some of those who have tried the example of obtaining an invoice with PHPWord or phpSpreadsheet asked me if I had a solution to pass MS Office document to PDF.
I did not have it because all the ones I tried did not do it well. This one that I tried «OfficeToPDF», if you do it perfectly because it uses Microsoft software. That is the advantage and the disadvantage, since it only works in Windows.
I leave this article in case you want to try it on your computer.
A
 		acpan 7/7/2019

Thank you for sharing. Excellent tutorial!

ACP
fhumanes author 7/9/2019

Hello:
As I had committed, given the dependence of the executable Windows for the conversion of any MS Office document, I have separated this connection in a Webservices, in such a way that this functionality is installed in Windows and the rest of the application can continue running in Linux.

In the article in my blog, I left the code and explanation.
I recommend downloading the websevices code to anyone who wants to make a program of this type to separate or integrate different systems.
URL: https://fhumanes.com/blog/otros-ejemplos/convertir-documento-ms-office-a-pdf/
Regards,
A
 		asalbikin 12/20/2019

how to print with check box field selection
fhumanes author 12/20/2019

A more complete example where I use all types of data I have published here.
https://asprunner.com/forums/topic/26856-print-and-forms-management-in-pdf/
Cheers,
fernando
fhumanes author 1/13/2020

Hi:
For a while, the example has been giving this error:

Fatal error: Uncaught Error: Class 'IntlDateFormatter' not found in /home/u637977917/public_html/factura/ICM/FacturaWord.php:66 Stack trace:

#0 /home/u637977917/public_html/factura/factura_word.php(7): require()

#1 {main} thrown in /home/u637977917/public_html/factura/ICM/FacturaWord.php on line 66


That was motivated because on the server I changed to PHP 7.3 and I was missing the INTL option.
It is fixed.
Regards,
fernando
fhumanes author 8/26/2020



You can temporarily execute it in: Demo


Hello:
I have changed the application. Now the demo is at https://fhumanes.com/invoice
Regards,

fernando