How to read data from Excel using PHPExcel

Question

I am trying to import data from an excel sheet (.xlsx). I found PHPExcel to import data, but after loading the document and source code, I was confused which file is important to me. I also tried to find a document on this site, but could not find a way.

About my task: Read the Excel data from the selected sheet and paste the data into my database table.

So, I am very grateful if you would advise me how to use it.

Thanks.

Answer 1

You can use the PHPExcel library to read an Excel file and insert data into a database.

Sample code below.

// Include PHPExcel_IOFactory include 'PHPExcel/IOFactory.php'; $inputFileName = 'sample.xls'; // Read your Excel workbook try { $inputFileType = PHPExcel_IOFactory::identify($inputFileName); $objReader = PHPExcel_IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($inputFileName); } catch(Exception $e) { die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage()); } // Get worksheet dimensions $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); // Loop through each row of the worksheet in turn for ($row = 1; $row <= $highestRow; $row++){ // Read a row of data into an array $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE); // Insert row data array into database here using your own structure 

Answer 2

With a quick look at the documentation, use IOFactory to automatically determine the file format.

 include 'path/to/PHPExcel/IOFactory.php'; // Let IOFactory determine the spreadsheet format $document = PHPExcel_IOFactory::load('path/to/spreadsheet.xls'); // Get the active sheet as an array $activeSheetData = $document->getActiveSheet()->toArray(null, true, true, true); var_dump($activeSheetData); 

Answer 3

Try this to start the development process:

 include '.... /PHPexcel/Classes/PHPExcel.php'; $dataFfile = "C:/tmp/test_data.xls"; $objPHPExcel = PHPExcel_IOFactory::load($dataFfile); $sheet = $objPHPExcel->getActiveSheet(); $data = $sheet->rangeToArray('A2:AB5523'); echo "Rows available: " . count($data) . "\n"; foreach ($data as $row) { print_r($row); } 

Replace include path with PHPExcel.php path Replace $dataFile your excel file
Also adjust the range of cells you want to import