Excel Calculation using PHPSpreadsheet in Codeigniter

Показать описание

In this video, you will be able to do basic excel calculation using phpspreadsheet in codeigniter.

In this tutorial, while exporting mysql data to excel file , you will be setting formula for the excel column that will be calculated in excel.

In my excel file, I have some products with quantity and price. So, we will be doing calculation of amount of each product and then total of the amount of the products.

For amount calculation for each product
Amount= quantity * price
in phpspreadsheet, we can achieve this using :
$sheet- setCellValue('E8','=C8 * D8');
E8 - column index, you can your you own column
C8,B8 - column and row for which multiplication should be done

For addition or total of product amount
$sheet- setCellValue('E8','=SUM(E2:E5')');
SUM is excel formula which takes parameter for calculation

For download excel file, you can watch this video :

In controller ,
after that use
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

Spreadsheet - for creating a worksheet in excel
xlsx -for creating excel file

we will be passing worksheet (spreadsheet) to xlsx - this will create a excel file that contain a worksheet.
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet-getActiveSheet();
$sheet- setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);

For download of excel file, you have to add headers

save will save that in a particular folder
For download you need to pass php://output to download in excel
$writer- save("php://output");

Documentation:

Starting hello world:

Code Link:

Playlist:

#codeigniter #knowledgethrusters #phppsreadsheet