1,105,427 Community Members

import an excel file(xls,xlsx) to mysql via php

Member Avatar
djasy
Newbie Poster
5 posts since Jul 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

good evening people,i'm creating an application where i'd like to import and save a list of people in an excel sheet to my database in mysql, using php, And i'd like to modify it through a form using php!
Can anyone have an idea or something which will help me to solve my problem?
thank you!

Member Avatar
chrishea
Posting Virtuoso
1,524 posts since Sep 2008
Reputation Points: 182 [?]
Q&As Helped to Solve: 256 [?]
Skill Endorsements: 6 [?]
 
0
 

I use this for importing. With respect to modifying it in PHP, that's just some standard code that you'll have to write. If the modifications are on a record-by-record basis, then you just populate the form with values from the DB and save the result. If you want to display multiple records in a spreadsheet-like grid, then you should look into JQuery code that can do that.

Member Avatar
djasy
Newbie Poster
5 posts since Jul 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

thanks, but this one uses only xls, is there another one which uses xlsx(2010)?

Member Avatar
chrishea
Posting Virtuoso
1,524 posts since Sep 2008
Reputation Points: 182 [?]
Q&As Helped to Solve: 256 [?]
Skill Endorsements: 6 [?]
 
0
 
Member Avatar
djasy
Newbie Poster
5 posts since Jul 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

@chrishea: thanks for the link

Question Answered as of 1 Year Ago by chrishea
Member Avatar
djasy
Newbie Poster
5 posts since Jul 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
1
 

hello, i found out a way to import, using the PHPExcel classes, and i think it may be helpful for other who would like to use it and to modify it in order to make it perfect , here is the code!

    // here is the function which will help us to call the PHPExcel classe in our pages, you can put it in a file apart.
        $chemin = '/opt/lampp/htdocs/www/Classes/';
        set_include_path(get_include_path() . PATH_SEPARATOR . $chemin);//we specify the path" using linux"

        function __autoload($classe)
        {
            $fichier = str_replace
            (
                '_', 
                DIRECTORY_SEPARATOR,
                $classe
            ) . '.php' ;
            require_once($fichier);
        }   

        $fichierACharger = 'listeglsi.xlsx';//the worksheets to use

                $fichierType = PHPExcel_IOFactory::identify($fichierACharger);

                $objetALire = PHPExcel_IOFactory::createReader($fichierType);
                $objetALire->setReadDataOnly(true);

                $objPHPExcel = $objetALire->load($fichierACharger);
                //echo '<script>alert("le fichier a été chargé avec succes !");</script>';
                $feuille = $objPHPExcel->getSheet(0);//we specify the sheet to use

                $highestRow = $feuille->getHighestRow();//we select all the rows used in the sheet 

                $highestCol = $feuille->getHighestColumn();// we select all the columns used in the sheet

                $indexCol = PHPExcel_Cell::columnIndexFromString($highestCol);we set an index on the columns
                //we show the data through a table
                echo'<table>'."\n";
                for($row = 9;$row <= $highestRow;$row++)
                {
                    echo'<tr>'."\n";
                    for($col = 1;$col <= $indexCol ;$col++)
                    {
                        $classeur = $feuille->getCellByColumnAndRow($col,$row)->getValue();
                        echo'<td>'.$classeur.'</td>'."\n";
                    }
                    echo'</tr>'."\n";
                }
                echo'</table>'."\n";

                //connection to the database
                include("cnx_import_excel.php.inc");

                //importing files to the database
                for($row = 9;$row <= $highestRow;$row++)
                {
                    $mat = $feuille->getCellByColumnAndRow(1,$row)->getValue();
                    $noms = $feuille->getCellByColumnAndRow(2,$row)->getValue();
                    $postPren = $feuille->getCellByColumnAndRow(3,$row)->getValue();
                    $sexe = $feuille->getCellByColumnAndRow(4,$row)->getValue();
                    //example of my data base
                    $rqt = "INSERT INTO T_ETUDIANTS (E_ID,E_NOMS,E_PREPOST,E_SEXE)VALUES('$mat','$noms','$postPren','$sexe')";
                    $accuse = mysql_query($rqt)or die (mysql_error());
                }
                //connection closing
                mysql_close();
Member Avatar
miatiza
Newbie Poster
2 posts since Feb 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

i am a noob, pls help me...
i have downloaded the phpexcel but i dont know where to start with? where should i put this php excel file? and wht should i do next? =x

Member Avatar
miatiza
Newbie Poster
2 posts since Feb 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

actually i want to import Data from excel into my tABLE.

Member Avatar
vikaspa
Newbie Poster
12 posts since Jun 2005
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

refer tests directory ...lot of examples are there

Member Avatar
naidu65
Newbie Poster
1 post since Apr 2014
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

can complete code for excel file uload data into DB using php

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: