From excel (.xls/.xlsx) file to Access/mySQL using phpExcel

Topics: Developer Forum, Project Management Forum, User Forum
Jun 24, 2011 at 8:27 AM

Hi All!!!
I'm new to phpExcel. (IT'S URGENT!!!)
Suggest me how to read, dump data from excel file to Access/mySQL using phpExcel...

I need to query the data & report it.

 

Please reply asap!!!

Jun 24, 2011 at 9:32 AM

It is an example how to read data from xls file and output it as html table. You can edit dumpPage  function to insert data to mysql instead of print it 

        PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
        $objReader = PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel = $objReader->load("somefile.xls");
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX  Document")->setSubject("Office 2007 XLSX  Document")->setDescription(" Document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("php result file");
dump($objPHPExcel);
   function dumpPage($objWorksheet){
        echo '<table border=1>' . "\n";
        foreach ($objWorksheet->getRowIterator() as $row) {
            echo '<tr>' . "\n";
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false); 
            foreach ($cellIterator as $cell) {
                echo '<td> (' .$cell->getCoordinate() . ") ". $cell->getValue() . '</td>' . "\n";
            }
            echo '</tr>' . "\n";
        }
        echo '</table>' . "\n";        
    }

   function dump($objPHPExcel,$start_page = 0, $count = -1){
        $i = 0;
        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
            if ($i < $start_page) {
                continue;
            }
            if ($count != -1 && $start_page + $count < ++$i) {
                break;
            }
            echo '- ' . $worksheet->getTitle() . "<br/>\n";
	        $dumpPage($worksheet);
        }
    }
With regards Igor 
Jun 24, 2011 at 9:40 AM

@Igor:
Thanks a ton for such a swift reply!!!

I'll verify it.

Also, can u guide me with the related test/examples in the package.

Regards,
√iral

Jun 24, 2011 at 10:07 AM

Could you clarify, what you want to know about test examples.  One of the most usefull for me was 05featuredemo.php. Just  read file name, exec it and check code to get working examples.

 

Regards, Igor 

Jun 24, 2011 at 10:50 AM

Thanks again...
It was just that their description is not clear from their name.

Regards,
Viral

Jun 24, 2011 at 11:54 AM

Igor Sir,
Now some assistance about date, time formats while reading from excel file.

Regards,
Viral

Jun 24, 2011 at 12:11 PM

I haven't worked with this part of library, usually I generate xls from mysql data.  So i'm afraid I can help you with this.

 

Regards,  Igor 

Jun 24, 2011 at 12:20 PM

No problem Sir.
Thanks a lot for what you taught.

That was hightly appreciable...
:)
Regards,
Viral

Dec 19, 2012 at 8:22 AM
Edited Dec 19, 2012 at 8:23 AM

hello,

i have used the same set of code but it does not read excel2007 format,can any one guide me on this plz

Dec 19, 2012 at 8:31 AM

sorry it  was a little edit

$objReader = PHPExcel_IOFactory::createReader('Excel2007');

i am very new