PHP Excel - php import excel data into database & export data from database in excel sheet in php

hey guys welcome to my channel so guys in this video we are going to start with the import excel data into our database in php so guys we are going to use a package named as spreadsheet so where we are going to use this and import the excel data into our database so as we have already one table named students so where we have few fields full name email phone and course where i want to insert the excel data into this table okay so guys now let's get started so first step let's move to this our editor and here a small setup guys with i'm using a bootstrap five to design this user interface and now let's begin with the coding so first up guys let's start with the form and the method is equal to post and here your input which will be file name is equal to import file okay let's use a class to design the user interface one submit button to submit the data duty on button import and let's design the button primary and i want to give a space and now let's see the output for this cool so now when we click on this it should insert the data so for that guys you need to when you're using this input type file you have to call enc type multi multipart form data enc type is equal to and now guys we need to uh install the package now so we need to start our terminal okay let's get back and here let's click on welcome to spreadsheet documentation and below you can find the installation process so we are going to use composer and install this so if you don't have composer just download composer and install in your system and then check over there do you have composer or not so for that composer hyphen capital v and hit enter here you will find your composer version guys so now i have a composer let's copy this and paste so it will take some time to install this php spreadsheet package so let's just wait guys over here okay so our application successfully installed guys done and let me just close this terminal and now that let's begin with the coding part let's go to conform action code.php so let's create a file with the name code.php and when you click on the submit button let's make this button type as submit and this button name will be excel data okay so now let's go to code.php with the method post with this button name now let's begin with php open and php close where you are going to check with if condition a set function dollar underscore post method of your cv excel data so now guys let's take the file name so file file name is equal to dollar underscore files of where we will tell this one will be the file name so let's copy the file name import file okay so the input type file name and what we want to get from that i want to get the name of it done so we have got the name over here not the file so yep now let's begin with the validation part where i want to validate only excel file should be accepted so let's tell allowed extensions allowed extensions is equal to where inside array i want to keep xls done now let's filter this so let's get our file extension also path info open and close bracket your file name so from this file name i want to get the extension okay so let's type path info extension done so this will be your file extension now you can keep this at top because you are just taking from that and now let's write an if condition to check whether in this array we have that file extension or not so if the file extension matches with your given allowed extensions done then only you're going to come in else you can be redirected with the message let's make a session over here and tell message where you will mention invalid file and let's redirect with the header location where location call it i want to go back to index.php exit all done so what in case if it is success then you are going to check over here so you need to move to the php documentation and let's go over here and you can see that the simplest way to write a spreadsheet where we want to include this also so let's copy this to lines and import at the top so make sure you are matching the path so my code dot php is over here itself so where it's going to inside the vendor and checking for the auto load so i have directly included if your path is different so you can just add on like this okay so yep now let's continue over here so let's get back and let's move for creating the spreadsheet so let me click over here and here i want to load the workbook from a file okay so when we upload something over here right so i want to load those data so you can just copy this code and get back and paste here so yep over here this is your file so what is the file we want to get from the input let's paste here and this is not the name we don't want we want the temp name so temp name means that it will go and take the file from it done where this is the path of input field input file name we can mention one more field called path so which means we are going to take from that path data and load that and then guys over here in the inside the spreadsheet you are going to check the get activities sheet so let me just show you that part okay let's go at beginning here you'll find it get activity sheet so whatever it is done and i want to get that data in to arrive function like get all the data in array format and store in a variable called data so done and after this guys let's create the excel and insert the records so let's create one excel sheet okay done and here my field names are full name email phone and course so let me just type let me type it full name email phone and course done so let's make it here let me type varun overnight g.c phone number over here some random digit phone and the course so and let's go to become all set i have saved this file guys with the name php excel on the desktop okay so let's get back and now let's begin the code over here so once you upload this file so i want to check that so for that let's for each use the forage loop where we will tell dollar data so this dollar data you're taking as dollar row and with this row you're going to loop and get the output dollar row of zero and this will be the one two now let's check the output for this zero okay let's refresh guys and now let's upload the file okay i'm going to click import here you can find that full name varun and then mohan and the final one over here okay so all the record we have got this full name so if you want to get that second record just echo this and refresh so here we get all the emails also so same way we are going to divide this and insert into database so now let's get back refresh the page and start begin with the code part so now let's create a variable so this will be the full name is equal to dollar 0 will be full name then dollar email is equal to row 1 and dollar phone is equal to row 2 and row 3 will be to make that course over here and now guys let's begin with the query dollar student query is equal to insert into students table the column field and the values where you're going to tell in that single quotes i want the full name variable so first we are going to store full name so name make sure all the fields which you are placing here it should be matching from your database table columns so let's get that email phone course so same way all the variables according to it red dollar email then next is your phone and then the course all done so now guys let's execute this above query where i want to keep our result masculine underscore query function where inside this give your database connection first and then your student query so now guys let's give this database connection let's move at the top of the page and start with mysqli underscore connect function localhost the username password and the database name its admin panel asset done guys and now once this executes let's turn dollar msg is equal to one or make it to true so if this is like if it is set then you are going to show the message dollar msg or else you can check in a set function also so if it is true you will show the message successfully inserted let's copy and paste here successfully imported okay let's copy the else part done not imported and if you're using a session you make sure you're starting the session at top of your page so session underscore start function and same process we want to show on the index page so you just need to start that okay open php close php now to show the message you can just use a simple code f set under concession of your message echo h4 tag we will mention session of message and unset after showing the message done guys so now let's get back let's see the output for this let's get back refresh so first we will try to import some text file so i'm going to input this dot txt file import here invalid file you're getting now let's try to import the excel file okay and here we have four records i mean four rows so total rows we have four let's import that successfully imported and now let's get back to our database here we have two records let's refresh and you'll find six records perfect so here we get all the data but one thing you are seeing that full name email phone and course is not required over here right so we just want to skip this so there is one method like dollar count is equal to i am setting it to zero and after this inside the forage loop let's use if condition where you are going to tell the count is greater than zero like if it is more than zero only you should enter else you can mention that dollar count is equal to where i want to set it to 1 also done so let me just cut this and paste after the your data insert query all set so here it will skip for the one and when it comes to next loop it will set it to 1 and then time this count will be greater than zero so now let's get back and let's delete all the records so i don't need so now table is empty refresh the page and now let's enter once again done import imported successfully and let's get back browse cool so you can see guys that three records has been entered which over here you can find the three records done guys perfect so guys now we have successfully done with how to import the excel data into our database and now let's let's see and now we are going to export the data from the database in excel sheet in php so guys now let's get started so let's move to this file where this is our index page okay so let me just copy this full thing and paste it once again margin top five how to export excel export data from database in excel sheet using php okay so now let's change this over here i need one so let's check with the select option data over here because i want to download that files in multiple so that is your excel extensions like xlsx and then one be this and then one will be csv file okay csv xls done guys so now let's design this form control and the class name i want to give this as export file type and then guys one button so which we already have and instead of save excel data we are going to tell export excel data okay or export excel button we can tell and here let's change this button name to export all set guys and now let's see the output for this refresh perfect okay so here we can select the required and click on export to export the data so now let's let's begin with the coding part so now guys let's click on this button and go to the form action code dot php with the method post by taking this input select option values okay so now let's copy this button name and go to code.php file and let's begin with coding part so guys first step we are going to use if condition to check the button is clicked or not with the help of a set function where the button is coming with the help of post method done and now guys let's get the file type that is your file type over here and store in a variable called file extension name so file extension name is equal to dollar underscore post method of your input field type done and now guys let's get the data from our database so which data i mean this data which we have imported the excel values so yep let's type the student is equal to where we'll tell select all the data from students table okay so the table name is students oh yeah let's copy that and paste here and now let's learn this query so you can just type query one is equal to with the help of mysqli query function so first database connection and then your query that is the student's query done once this is executed now you need to check in if condition if record exist or not so insert this with the help of mysqli number of rows function you can check check over here your executed query so you can just take this query run and paste here when this is success you're going to show else you're going to tell that no record font so for that case guys you're going to let me just copy from below and paste here until no record found that's it so you are using session make sure station is started and now guys what if we get the data so over here the main role comes to export our excel data so yep let's go to our documentation and at this welcome to spreadsheet we have got this okay so in this hello world here is the code so let's copy this okay and let's paste here all set and the importing part of this we have already copied it so in previous video just we just pasted that done and now guys let's begin over here so yep over here first row we want to set the excel data so as i can show you an example over here so this row okay i want to set the default values and then loop all the data over here okay so first row we will set that so in the a column one row i want to set the id id and then according to your requirement full name email phone and course so this will be the e1 d1 c1 and b1 done so these fields are nothing but my just column names what i have in my db done and now guys we are going to start with the for loop i mean for each loop where we will take the query executed query run as dollar row so with this help of dollar row we are going to print the values so yep so first step so now guys let's copy one example from here and now inside this a1 column i want and this at this one we have to paste two right for entering to the second row okay so enter that second row we will concatenate it let's use dot and dollar row count and this row count we are going to start from 2 because first row it will take this then we are going to start with the row 2 and after that it will just count and let's increment this using plus plus okay so e to it is starting and then it will continue on and at this place you are going to take your dollar row data let's make this as data because we are getting confused with this row count and this data so let's keep as data only data of the first row will be id so let's copy and paste next will be full name so these fields are nothing but the table column field name f name full name email phone and course so same spelling we have to give okay email phone and course and now guys over here change the b c d and e all set so now let's give it the if condition if the extension is double equal to xlsx so which extension is this your file extension so which means this file name file extension name is double equal to this exemption then you are going to write the code over here so let's take this and paste here done and from here i want to give the file name so let's tell final underscore file name is equal to dollar file name i want to concatenate and insert single quotes let's give the file extension done so this was for one xlsx okay i want the csv also and the excel s value also so you can just copy and paste remove this and paste for the c sv done so if you want to get from the documentation you can just go to this read and write file to option and from there you can find out okay so you can just move on below and see all that here this is for reader and here is for right okay so that option we are getting it so let me just copy this and use else if okay xls and here we remove this also so this is imported at the top done and next was for csv so let's copy this and paste here for csv and paste that file name all set so this file name from where are you getting dollar file name let's create it at the top itself dollar file name is equal to student hyphen sheet that's it if you want to concatenate timing and all you can just concatenate and get the data so i just want that file name and then after this guys let's see what happens over here so we just need to call the dollar final file name so why final file name we are taking all the concatenating and storing over here right and that final file name we are going to save it so now let me just open this and here if you directly move on with this you are going to download the data in your folder itself let me just show you refresh and here i move with that csv export okay it's loaded and it went stopped there yep you can see here csv file has come right but concatenation has been missed why let us check out okay we did not add over here so let's add that dot so whatever the file comes with the dot csv extension done so let me just delete this delete this and let's get back and once again we will try to export so export done and here you find that xls file all set guys now let's delete and now we don't want to die here save the file inside the folder i want to download this file okay so for that case guys you are just going to type few code in a header function so let's type header function and the content hyphen type colon here your code comes and the second one also we are going to take content called this position so insert this what code comes let me just paste that so here is the code guys and i have pasted here one line and in second content disposition where attachment and the file name we have to call so inside this file name will be calling the code for url encode and get the file name okay so inside single quotes let's with double quotes i want to get the url and code function and inside this url and code you're going to take the file name let's take that here you paste your file name so let me just cover up the spaces not required let's save and now guys let's try to execute this once again so yep refresh now let's open with csv export and it's got downloaded perfect so i'm going to open this file and here we see okay so there is no data let's get back oops we have saving this so it should not save right let me just keep for the reference this one we have to get this output so we have to download right so for that case guys you are going to remove this and save in a php colon forward slash output that's it so let me just delete this we don't require over here done oops we have missed with this so let's type csv so make sure you're typing this done and now let's try to export again here we go let's open this yes perfect so you can see guys with the id we have got all the data over here so as we have successfully done with the export excel data in php so you can download with your xls done so here is the extension and if you want to for this also you can just download and here you find that extension so guys in this video that's it and thank you for watching this video let's continue next video with the new topic of this excel data where we want to update these records okay so if already exist this record please don't insert while if it is existing or if you want to update so in that case we are going to check so guys in this video that's it thank you once again please subscribe like and share

Channel: Funda Coder

Views: 53

Keywords: fundaofwebit, funda coder, php excel import, php excel export, import excel data into database in php, php excel import data to database in php, import and export excel data in php, php excel import and export data in php, php import and export data, how to import excel data into mysql database in php. php excel import., how to export data from mysql database to excel sheet in php using phpspreadsheet package., php excel tutorial

Id: ELSFN-t1hqc

Length: 32min 35sec (1955 seconds)

Published: Tue Dec 14 2021