- 4 years ago
- Zaid Bin Khalid
- 72020 Views
-
12
PHPExcel is a very powerful library to read and write data into excel. Now this project is archived by author visit to see detail. 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 can read XLSX file with PHPExcel or PhpSpreadsheet.
Installation: The PHPSpreadsheet can be installed with the help of Composer
On Terminal: The following command runs on the terminal to install PHPSpreadsheet:
composer require phpoffice/phpspreadsheet
Download PhpSpreadsheet and add it to your project like below.
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
How to write data and save the XLSX file.
The below example is the complete working example that you can use to write the XLSX file with the help of the PhpSpreadsheet.
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// Set the value of cell A1
$sheet->setCellValue('A1', 'A1 Cell Data Here');
$sheet->setCellValue('B1', 'B1 Cell Data Here');
// Write an .xlsx file
$writer = new Xlsx($spreadsheet);
// Save .xlsx file to the current directory
$writer->save('lcw.xlsx');
How to read the XLSX file in PhpSpreadsheet?
You can use PhpSpreadsheet to read the XLSX file with the help of the below code.
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('lcw.xlsx');
$sheet = $spreadsheet->getActiveSheet();
// Store data from the activeSheet to the varibale in the form of Array
$data = array(1,$sheet->toArray(null,true,true,true));
// Display the sheet content
var_dump($data);
In the PHPExcel
If you are using the older version of PHPExcel then consider below code 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);
}catch(Exception $e){
die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}
// Get worksheet dimensions
$sheet = $objPHPExcel->getActiveSheet();
$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);
// Use foreach loop and insert data into Query
}
Note: In the above example, you need to understand the highlighted area.
$inputFileName = 'sample.xlsx'; This line indicates the excel file that you want to read. You can read any sheet with the help of getSheet method. You can also use getActiveSheet() method if you are using PHPExcel Archive.
$sheet = $objPHPExcel->getSheet(1); // Change sheet number
Now you just need to read the rows and columns of the excel sheet.
In the FOR LOOP, all data stored in $rowData. Now you just need to dump data into your DB.
For that, use a foreach loop and within loop use your insert query to store data. Below snippet is the example of a foreach loop.
foreach($rowData as $val){
mysqli_query('YOUR-QUERY',$connection)
}
You can also use the SimpleXLSX class to read the excel file. To download the simpleXLSX class click here. The below snippet is the simple example code to read XLSX file.
require_once 'SimpleXLSX.php';
if ( $xlsx = SimpleXLSX::parse('pricelist.xlsx') ) {
print_r( $xlsx->rows() );
} else {
echo SimpleXLSX::parse_error();
}
- 4 years ago
- Zaid Bin Khalid
- 72020 Views
-
12