Use Php to Read an Excel File and Store in Database
PHPExcel is a very powerful library to read and write information into excel. Now this project is archived by writer visit to run into particular. PHPExcel is officially known as PhpSpreadsheet. I am going to tell you how you can create an excel file with XLSX extension. And how you tin read XLSX file with PHPExcel or PhpSpreadsheet.
Installation: The PHPSpreadsheet can be installed with the aid of Composer
On Terminal: The following control runs on the last to install PHPSpreadsheet:
composer require phpoffice/phpspreadsheet Download PhpSpreadsheet and add it to your project like below.
<?php crave 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Spreadsheet; apply PhpOffice\PhpSpreadsheet\Writer\Xlsx; $spreadsheet = new Spreadsheet(); How to write data and save the XLSX file.
The beneath instance is the consummate working case that y'all can use to write the XLSX file with the assistance of the PhpSpreadsheet.
<?php require 'vendor/autoload.php'; apply PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); // Set up the value of cell A1 $sheet->setCellValue('A1', 'A1 Cell Data Here'); $sheet->setCellValue('B1', 'B1 Cell Information Here'); // Write an .xlsx file $author = new Xlsx($spreadsheet); // Salve .xlsx file to the electric current directory $writer->relieve('lcw.xlsx'); How to read the XLSX file in PhpSpreadsheet?
Yous tin use PhpSpreadsheet to read the XLSX file with the assistance of the below lawmaking.
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('lcw.xlsx'); $canvass = $spreadsheet->getActiveSheet(); // Store data from the activeSheet to the varibale in the course of Array $data = array(ane,$sheet->toArray(naught,true,true,true)); // Brandish the sheet content var_dump($information); In the PHPExcel
If yous are using the older version of PHPExcel then consider below lawmaking to read XLSX file.
Example code.
include_once('Excel/Classes/PHPExcel.php'); $inputFileName = 'sample.xlsx'; //Read your Excel workbook try{ $inputFileType = PHPExcel_IOFactory::identify($inputFileName); $objReader = PHPExcel_IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($inputFileName); }take hold of(Exception $e){ die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$due east->getMessage()); } // Get worksheet dimensions $sheet = $objPHPExcel->getActiveSheet(); $highestRow = $sheet->getHighestRow(); $highestColumn = $canvass->getHighestColumn(); // Loop through each row of the worksheet in plough for ($row = 1; $row <= $highestRow; $row++){ // Read a row of data into an array $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, Zilch, True, Fake); // Use foreach loop and insert information into Query } Notation: In the above instance, you need to understand the highlighted area.
$inputFileName = 'sample.xlsx'; This line indicates the excel file that you want to read. You tin read whatsoever sheet with the help of getSheet method. You lot can too apply getActiveSheet() method if y'all are using PHPExcel Annal.
$sheet = $objPHPExcel->getSheet(1); // Change sheet number At present you just demand to read the rows and columns of the excel sail.
In the FOR LOOP , all data stored in $rowData . Now you lot simply demand to dump data into your DB .
For that, employ a foreach loop and inside loop apply your insert query to shop data. Below snippet is the example of a
foreach($rowData as $val){ mysqli_query('YOUR-QUERY',$connectedness) } You can also use the SimpleXLSX course to read the excel file. To download the simpleXLSX class click here. The beneath snippet is the simple example lawmaking to read XLSX file .
require_once 'SimpleXLSX.php'; if ( $xlsx = SimpleXLSX::parse('pricelist.xlsx') ) { print_r( $xlsx->rows() ); } else { echo SimpleXLSX::parse_error(); } Source: https://learncodeweb.com/php/phpexcel-use-to-read-excel-file-and-insert-into-database/
Post a Comment for "Use Php to Read an Excel File and Store in Database"