How to Import Excel File in Codeigniter

hi friends in this video tutorial we are going to discuss how to import excel sheet data into my SQL database encode ignitor framework by using PHP excel library we have already discussed how to export my SQL data to excel sheet encode ignitor by using PHP excel library but now we have discussed how to import data from excel sheet my SQL database by using PHP excel librarian codeigniter framework phpexcel library PHP class library is a very easy to understand library for get data from excel file and export data to excel file by using PHP script now let's start discussing this topic so this is our testing data basin in this we have our customer table with table column like customer ID customer name/address city postalcode and country we will import data into this table from excel sheet first we have to make database connections so we have to go to application slash config folder slash database dot PHP file under this file we have set hostname set to localhost username set to root password set to blank and database name set to testing after this we have go to config dot PHP file and under this file we have said base URL of our code igniter application now we have go to Auto a load dot PHP file and in dollar otto load libraries variable we have read database in session so when we have execute code ignitor framework then it will load database in session library after this we have go $2 out a load helper variable and here we have read URL inform this code will load URL inform helper here we have already download phpexcel library input into this application slash libraries folder under this libraries folder we have created one Excel Doc PHP file and under this file we have read class extends phpexcel library class here we have create Excel class which extends property of phpexcel library class so by using this excel class we will import excel sheet data under this we have right public function with construct magic function when object of this class has been created then it will execute this block of code under this also we have right parent class with construct magic function here we have extend parent class construct function so when object of this excel has been created then it will execute phpexcel construct magic function code now we have go-to controller folder and under this we have already created cell import controller we will this controller for handle HTTP request in modules folder we have also already created Excel import modal for handled database operation an in views folder also we have already created Excel import dot PHP this is browser output file first we have go-to Excel import controller and under this we have right public function construct magic function when we have called any method of this controller then first it will execute this block of code under this we have right parent can magic function here it will execute constructor method of parent class below this we have red dollar this with Lauder class subject load with model method and under this we have right excelling port model this code will load Excel import model after this we have read dollar this with load object with library method and under this we have write Excel this code will load Excel library below this we have write function index method this is the main method of this controller so when we have called this controller then this method has been executed under this method we have read dollar this with load with view method and under this import Excel view file name this code will load this view file when we have called index method now we have go-to Excel import view file and on this file we have already included bootstrap library stylesheet link and JavaScript library link under this page first we define form which method is equal to post ID is equal to import form an encryption type is equal to multi-part form data for upload file you under this form we have defined input type is equal to file name and ID is equal to file and accept attribute as equal to X LS and xlsx extension by this tag we can only select only this both extension file from our local computer first admit form data so we have read input type is equal to submit with name and value is equal to import by click on this button we will import excel file data below this form we have defined one division tag with the attribute ID is equal to customer data under this tag we will load customer table data an HTML table format now we have to move to jQuery code in first we have make load data function this function will fetch latest customer data and display on webpage under this we have right start right Ajax request and under this first we have right URL option set to base URL function with Excel import controller fetch method we have send request to this method in second option we have right method and under this we have set post method so we have used post method for send form data to server lastly we have write success callback function this function has been called if request completed successfully under this function we have right division tag ID customer data with HTML method and under this we have read data this will display customer data on web page now we have go-to Excel import controller and under this we have make fetch method this method will receive Ajax requests for fetch data from customer table under this method we have right dollar data variable is equal to dollar this with Excel import model with select method for this we have go-to Excel import model and here we have make select method under this method we have read dollar this with database class object with order by method with table column customer ID in descending order this code will make sort data in descending order according value of customer ID below this we have red dollar query variable is equal to dollar this with database class object with get method and under this we have write customer table this get method will execute select query and store result in dollar query variable lastly we have rightt return statement with dollar query variable this is the output of this function now we have go-to Excel import controller and here we have red dollar output variable is equal to total data with value get from dollar data with number rows method it will return number of rows after this we have defined table with table column like customer name address city postal code and country below this we have right for each loot under this we have red dollar data with result method as dollar row variable this method will return result in array of the object under this loop we have read dollar output variable in this variable we have append HTML code and in this in first column we have print customer name data get from dollar row customer name object in second column we have read dollar row address object to which print address data in third column we have read dollar rows to the object to which print City data you in fourth column we have read dollar row postal code object to which print postal code data and in last column we have read dollar row country object to which print country column data so this way we have store all data and dollar output variable now we want to send this data to Ajax requests so we have right echo statement with dollar output variable now we have go-to Excel import view file in here we have called load data function which display latest customer data on webpage here friends when we page has been loaded into browser then we can see total 10 customer records on webpage an HTML table format in above data we can see one file tag with submit button so for import Excel file data go to view file in here we have red dollar for my D import form with on method and under this we have write submit event so when form has been submitted then this code has been executed under this first we have red event got preventdefault method this method will stop to submit form data to server now we have start right Ajax request and under this we have read URL option set to base URL function with Excel import controller import method we have send request to this method in second option we have right method option on this option has been said to post so here we have used post method for send data to server in third option we have reg data option and under this option we have write new form data object with this argument this option will send data to server and key in value pair of form fields in fourth option we have right content type set to false here content type used when sending data to the server and fifth option we have right cache options set to false this option for unable request pages to be cached in sixth option we have right process data option set to false this for send eom documents were not in processed data file so it is set to false lastly we have write success callback function this function has been called if request completed successfully and it will receive data from server under this function first we have read dollar with file tag ID file with value method with blank value this code will clear final selection tag below this we have called load data function this function will display fresh data on webpage from customer table now we have if go to Excel import controller and under this we have created import method this method will receive Ajax request under this method first we have writen if statement in condition we have read as said function with dollar files filename variable this condition will check if this variable value is set then it will execute if block of code under this block we have right dollar path variable is equal to dollar files file temporary name variable here we have store selected file temporary path in dollar path variable below this we have red dollar object variable is equal to phpexcel IO factory class with load method and under this method we have red dollar path variable this method will loads phpexcel from file get from dollar path variable using automatic phpexcel reader eye reader resolution after this we have right for each loop and under this we have red dollar object variable with get worksheet an iterator method as dollar worksheet variable by using this method we can get worksheet iterator which can be fetched by using dollar worksheet variable under this loop we have read dollar highest row variable is equal to dollar worksheet variable with get highest row method this method will return highest worksheet row after this we have read dollar highest column variable is equal to dollar worksheet variable with get highest column method this method will return highest worksheet column now we have right for loot under this we have red dollar row variable as equal to $2 row variable value less than equal to dollar highest row variable in dollar row variable value increment by one under this loop we have read dollar customer name variable is equal to dollar worksheet variable which gets L by column and row method with two argument and first argument we have red zero column number and second argument we have red dollar row variable with get value method here this gets held by column and row method gets Ellen a specific coordinate by using numeric cell coordinates and by using get value method we can get value of cell and store in dollar customer name variable below this we have red dollar address variable is equal to dollar worksheet variable with get sell by column and row method with two argument in first argument we have write one column number and second argument we have read dollar row variable with get value method here this gets held by column and row method gets sell at a specific coordinate by using numeric cell coordinates and by using get value method we can get value of cell and store in dollar address variable below this we have red dollar city variable is equal to dollar worksheet variable with get cell by column and row method with two argument in first argument we have right to Colin number and second argument we have red dollar a variable with get value method here this get celled by column and row method get cell at a specific coordinate by using numeric cell coordinates and by using get value method we can get value of cell and store in dollar city variable we have rightt dollar postal code variable is equal to dollar worksheet variable with get cell by column and row method with two argument and first argument we have right three-column number and second argument we have red dollar a variable with get value method here this gets held by column and row method gets sell at a specific coordinate by using numeric cell coordinates and by using get value method we can get value of cell and store and dollar postal code variable after this we have read dollar country variable is equal to dollar worksheet variable with get cell by column and row method with two argument and first argument we have right for column number and second argument we have red dollar a variable which get value method here this gets held by column in row method gets L in a specific coordinate by using numeric cell coordinates and by using get value method we can get value of cell and store in dollar country variable below this we have red dollar data variable is equal to array with key customer name with value get from dollar customer name variable address key-value get from duller address variable city key value get from dollar city variable postal code key value get from dollar postal code variable and country key value get from dollar country variable this way we have store who'll excel sheet data into this dollar data variable in our a format now we have go-to Excel import model and under this we have red insert method with dollar data argument under this we have read dollar this with database class object with insert vac method with two argument in first argument we have read customer table name and in second argument we have read dollar data variable by using this insert batch method we can insert multiple data and single query execution now we have go-to Excel import controller and here we have red dollar this with Excel import model with insert method and under this we have red dollar data variable this code will insert multiple data which we have store into dollar data variable in array format to my SQL customer table lastly we have around echo statement with data imported successfully this message will display to user by using Ajax request now we have check output in browser friends first we have refreshed browser and on web page we can see total ten customer data and table format in above table we can see file tag with import button so when we have click on file tag then in our local computer we can only see excel file only we cannot see other file because we have used HTML except attribute so we have select excel sheet and click on import button after click on import we can see data imported successfully on webpage in that selected excel sheet there is file customer data has been store and after importing of excel sheet data we can see total 15 data on webpage in table so this way we can import Excel sheet data into my askew L table and CodeIgniter framework by using PHP Excel library if you have working on any enterprise level application and in that you have to enter a large amount of data into my Eskew l table then you have to make you excel sheet and follow this code for importing large amount of data into my SQL table if you have any query regarding this video-tutorial part please comment your query in comment box and if you liked this video tutorial please share with your friends or even you can also share on social media also if you want to get more update regarding our video tutorial please subscribe our YouTube channel forget more update regarding release of future video lastly keep watching our YouTube channel thanks for watching this video tutorial

Channel: Webslesson

Views: 49,826

Keywords: excel import, import with codeigniter, excel to mysql, codeigniter upload excel to database, read excel file in codeigniter, codeigniter excel library, php excel import, codeigniter excel to database, phpexcel tutorial, codeigniter, excel mysql, import excel, import excel file data in mysql, insert batch, upload excel file in mysql, code to import excel data in mysql database, upload excel file

Id: ZLuiaIPx8l0

Length: 19min 28sec (1168 seconds)

Published: Fri Apr 06 2018