Using Template and Conditional Formatting in PHPSpreadsheet | PHPSpreadsheet Tutorial  #4

Using Template and Conditional Formatting in PHPSpreadsheet | PHPSpreadsheet Tutorial #4

Hi everyone. Welcome to my php tutorial video. in this video, i’m about to explain how to use template. which means we’re gonna make and style a spreadsheet in microsoft excel. and then we’re gonna use that file and fill our data in PHP. this way, we don’t need to make the excel file from scratch like in previous chapters. and we will also learn how to make and implement conditional formatting. let’s begin here i have prepared some data in database. the database name is phpexceltest and the table students. and we have id, first name, last name, email, gender, class and score. now let’s make the template. i’m gonna use this guide from previous chapter. remove unnecessary part. and add one column for score. and for the content, i’m gonna add a border. just vertical border. and add top border below the bottom row to close the table. now for how the template works. in template, we only need to change the content. and to insert the content, we insert a new row. and add the data to the previous row and so on. and last we remove this unnecessary rows. now let’s do it in the code. i’m gonna copy the script from previous chapter. and remove these unnecessary codes. in previous chapters i made some errors by forgetting to call the class i use in script. but now that’s not going to happen. because i’m now using visual studio code with a plugin called PHP intellephense. what does php intellephense do? let’s see. php intellephense is a high performance php intellisense basically this plugin will greatly help us in developing less-faulty code. let’s go to the script. i’m gonna give an example. when i’m using something like IOFactory. when i type iofactory. there will be a suggestion like this. and when i click it or press enter, this line will be automatically added. so with this we are less likely to forget. and then, it could also detect what is inside this class. like this. iofactory have create reader and some method. and it will also tell what parameter this method expect. this is just convenient. and what’s more, we can peek at the source code of these classes. just in case we want to know what is inside it. of course we’re not gonna make any change here. alright, let’s start coding. now what we gonna do is load the template add the content remove last empty rows then we make a conditional formatting now we load the xlsx template to do it, we need to use reader from IOFactory. so let’s call iofactory, create reader. and the reader type should be xlsx. in previous chapters, we begin with creating new spreadsheet object like this. but not this time, we are not making a new spreadsheet. instead, we just make it from one that already exist. so we just need to load that file using reader we have just made like this. reader. load. and the parameter should be our template file. let’s try to execute the code. if nothing goes wrong, we should get our template back. now lets add the content we are getting our data from database so make a new connection. and add some logic to stop the code if the connection fail. now fetch the data. in my database i have a thousand data, for just an example i think that is too much, i’m just gonna limit it to 100. before the loop, i need to store the start of the data rows. it should be 3. as well as a pointer for current rows, which will be incremented later. now let’s do the loop. in every loop, we need to insert a new row. let’s try it out to make sure it is working. looks good. now set the cell values with our data. i’m gonna speed it up. and lastly, increment the current row. but there are extra empty rows at the bottom, which is our original rows in template. we have to remove these 2 rows. using removeRow(). the first parameter is the current row number. the second is how much row we need to remove. in this case, 2. it’s perfect. now we gonna add a conditional formatting in the score columns. the rule is if the cell value less than, let’s say… 70. the fill color should be changed. now how we do it in the script. first, i make a new conditional object. as i have explained before, the conditional class is automatically added as i type. now making the rule, which is less than 70. to do that we call condition. set condition type. set it to Conditional::CONDITION_CELLIS. then set operator type, to OPERATOR_LESSTHAN. and add condition 70. i will explain those later in the video. and then set the condition style. this is the same as when we setting the cell style. i’m just gonna change the fill and font color here. let’s do the fill color. seems like i use the wrong color class here. this is not the class i want. what i want is the one from style class. let’s go back a bit. when i type Color, there are 2 suggestion. one is from the reader xls class. and the other is from style. the intellephense plugin can help us code easily. but you need to be careful with the suggestion because it could inject the wrong class and you won’t realize it. so double check the class and the class namespace before hitting enter on the suggestion. next let’s do the font color. now we need to apply the conditional into cells range. let’s define the range first. to apply the conditional, we need to get the current conditional from the range. the getConditionalStyles should return an array. then we push our conditional into this conditional array. and finally, reapply the conditional array using setConditionalStyles. so basically we make a conditional. then get the current conditional from the range. add our new conditional to that array. and then apply this new conditional array to the range. if nothing goes wrong, it should looks like this. if we open conditional formatting, then manage rules. we can see the rule we just made. when we edit this rule. we can see the rule type is “format only cells that contain” which is equal to CONDITION_CELLIS constant. and then the operator less than is equal to OPERATOR_LESSTHAN constant. and the addCondition parameter goes to the value. and in the format, we just change the fill color to red. and font color to yellow. and leave everything to default. this conclude this chapter of tutorial. thank you for watching. i hope this tutorial is helpful. i put the link to the source codes in the description. feel free to ask anything in the comment section. please kindly like my video and subscribe to my channel. and see you in the next video.

14 thoughts on “Using Template and Conditional Formatting in PHPSpreadsheet | PHPSpreadsheet Tutorial #4”

  • I have an issue: PHP Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, bool given in C:xampphtdocsphpdemosexcel3.php on line 15
    Can you giveme an idea of what us happening? because I'm following all the tutorial steps.

  • Hello from Russia!))) when you make a video in which you tell how to get data from a file excel and processing it in php, next load it into the database?

  • hi my case excel is downloading but in corrupted format. and in notepad++ the downloaded excel showing following problem
    "<br />
    <b>Fatal error</b>: Maximum execution time of 30 seconds exceeded in <b>C:xampphtdocssdrfexcelvendorphpofficephpspreadsheetsrcPhpSpreadsheetCellCell.php</b> on line <b>329</b><br />
    kindly help me.i have not found any maximum execution parameter in cell.php. also change php.ini file max_execution_time to 3000 second. But not worked.

  • Update of my status. My system is now working fully. Thank you very much for this tutorial. My previous problem was due to i was using a excel templates which had vba scripts on it which i did not created. But after i creating a fresh excel templates the codes starts working 100% in first chance. Again thank you.

  • Nice tutorial so helpful i wish you culd make a how to insert xlsx data into database video, thanks in advance!

  • Data base data fatch and horizontal total print after conversation to excel and excel in print horizontal total

  • I have problem. Everything is working on the local server but when i upload it to the live server i get a syntax error on one of the phpspreadsheet files. i have confirmed that i am using php version 7.0 on my cpanel

    Parse error: syntax error, unexpected '=' in /home/kyucuco1/*************/user/includes/phpspreadsheet/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php on line 2460

  • I keep getting an error when trying to open the file :Excel cannot open the file 'template.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the file format of the file. Any help here? I was able to successfully work through the first 3 Tutorials without any issues, it's only now that I am trying to use the template created.

  • Hola, gracias por tus vídeos, me ayudaron mucho, yo estaba usando phpexcel y tuve que mudarme a phpspredsheet por falta de herramientas.
    Un favor, podrías mostrar un ejemplo donde pueda clonar celdas, agregar saltos de pagina, y leer el nombre de las hojas dentro de una plantilla.

Leave a Reply

Your email address will not be published. Required fields are marked *