How to Create File Excel in Php

This time i want to share excel tutorial in php. for those of you who want to know how to create an excel file with php and use it according to your needs. you should follow this tutorial step by step. we just start the tutorial this time.

What Will I Learn?

• Make Object Excel
• Using array data in excel
• Download File Excel

Requirements

• Basic Php
• Localhost (xampp,wampp, or etc)
• Internet Connection

Difficulty

• Basic

Make Object Excel

Install PhpSpreadsheet

The first step we should do is to install the package from PhpSpreadsheet. You can open your project folder and run a command prompt there. and if the instalations prosecc finished you can see the folder vendor have been created as automatically.

composer require phpoffice/phpspreadsheet

Created Object Excel

The next step I created a file with the name index.php. and We have to load all the files in the vendor and import the PhpSpreadsheet dependencies.

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$phpExcel = new Spreadsheet();

require 'vendor/autoload.php'; : To load all file in vendor.

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

To import all dependencies of PhpSpreadsheet . So we can use the functions of Php Spreadsheet.

$phpExcel = new Spreadsheet(); : We create a variable to be initialized as a new Excel object.

Set Properties

To set properties in the excel object we can chain them.

$phpExcel->getProperties()->setCreator('Jhon Doe')
         ->setTitle('Tutorial Excel With Php')
         ->setSubject('This Subject of Tutorial')
         ->setDescription('This Description of Tutorial');

getProperties() : This is the main function of the process of chain functioning.

setCreator() : This function is used to write sapa creator from the file.

setTitle() : This function is used to set Title of File excel.

setSubject() : This function is used to set Subject of File excel.

setDescription() : This function is used to set Description of File excel.

Set Data Sheet

Then we will put data in the sheet. Its main function is getActiveSheet ()

$phpExcel->getActiveSheet()->setTitle("This Part 1 of tutorial excel");
$phpExcel->setActiveSheetIndex(0)
         ->setCellValue('A1','Utopian.io Rewarding Open Source')
         ->setCellValue('A2','The First in the world');

getActiveSheet(): The main function is used to select which page we will do.

setTitle(): Set title of page excel.

setActiveSheetIndex(0) : We will set the active sheet we will do is the first page, so in the form of the array so we start from 0.

setCellValue() : In the excel field we know that excel has colom like 'A1', 'A2', 'A3' ...
We can set by using this function.

setCellValue('A1','Utopian.io Rewarding Open Source') : its means the colomn A1 we write data 'Utopian.io Rewarding Open Source'

setCellValue('A2','The First in the world') : its means the colomn A1 we write data 'The First in the world'

Out File Excel

We can output the excel file with this code.

$writer = new Xlsx($phpExcel);
$writer->save('hello world.xlsx');

$writer = new Xlsx($phpExcel); : We create an output variable($write) with and excel object passing $phpExcel.

$writer->save('hello world.xlsx'); : To save the output with name 'hello world.xlsx. Important to know the extension of file must same of .xlsx.

and now if all we have done we try to know its results.
index.php

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$phpExcel = new Spreadsheet();
$phpExcel->getProperties()->setCreator('Jhon Doe')
         ->setTitle('Tutorial Excel With Php')
         ->setSubject('This Subject of Tutorial')
         ->setDescription('This Description of Tutorial');
$phpExcel->getActiveSheet()->setTitle("This Part 1 of tutorial excel");
$phpExcel->setActiveSheetIndex(0)
         ->setCellValue('A1','Utopian.io Rewarding Open Source')
         ->setCellValue('A2','The First in the world');
$writer = new Xlsx($phpExcel);
$writer->save('hello world.xlsx');

Run localhost in your project folder and go to index.php then see it in your folder. there is a new excel file.

Hello world.xlsx

Using array data in excel

Certainly in practice in the actual case we may not enter data one by one, column by column. the data that we have must be an array. therefore here I will explain how if we enter the array data into Excel.
I will create a multidimensional array.

Set Data

$player = [
    ['Dybala', 21,'Juventus'],
    ['Ronaldo',9,'Realmadrid'],
    ['Messi',10,'Barcelona'],
    ['Alexis Sanchez',9,'Manchester United'],
    ['Sergio Aguero',9,'Manchester City']
]

Set Header Column

We will make head titles, for the columns we create.

$phpExcel->setActiveSheetIndex(0)
         ->setCellValue('A1','Players')
         ->setCellValue('B1','Numbers')
         ->setCellValue('C1','Clubs');

We will put the header in each column horizontally.

Players ('A1') | Numbers('B1') | Clubs('C1')

Foearch Data

$col = 2;
foreach ($players as $player) {
    $phpExcel->setActiveSheetIndex(0)
            ->setCellValueByColumnAndRow( 1 , $col , $player[0])
             ->setCellValueByColumnAndRow( 2 , $col , $player[1])
             ->setCellValueByColumnAndRow( 3 , $col , $player[2]);
$col++;
}

$col = 2;
We create a variable with the number 2, serves as an iterator. his idea we will start from column 2 because column 1 already in the content with header title that we have created before.

foreach ($players as $player) {} : We do foreach to the data $players.

setCellValueByColumnAndRow( 1 , $col , $player[0])

@params 1

: The position of the column we will input the data, 1 for Colom A1.

@params $col

: The column in the setting becomes dynamic and it becomes an iterator.

$player[0]

: The data content of the array and [0] is the index of the array

$col++;

: Because it is looping then we must give $col++; so that all data can be removed from array. And then run your localhost go to the index.php . If there is no error , you can see the Excel File hello world.xlsx

Download File Excel

Before we can save the Excel file but directly inside our project folder. in fact we definitely want to make the user can download the file and save it in their download folder. for that we need to add some functions.

header('Content-Type: Application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="downloadExcel.xlsx"');
$writer->save('php://output');

to download Excel file we need to add the header
header('Content-Type: Application/vnd.ms-excel'); : Format Content type for Excel vnd.ms-excel.

header('Content-Disposition: attachment; filename="downloadExcel.xlsx"'); :
it is useful to specify this file as what, here I am asking this file as attachment. the filename we put here as downloadExcel.xlsx .

$writer->save('php://output'); : To save as Output Of PHP.

Now we run our localhost again in the same project and see the result.

Finally we managed to create an excel file with the help of PhpSpreadsheet in Php. hopefully this tutorial can help you. and thank you for following this tutorial. see you later in the tutorial

---

Posted on Utopian.io - Rewarding Open Source Contributors

---