Tags
PHP , xlsx , parse
Asked 7 years ago
21 Feb 2017
Views 19912
lain

lain posted

how to read xls file in PHP?

read excel file using PHP , and than break it into chunks and want to do some calculation in some column
and save it in fromat of .xlsx file

so any suggestion , dealing with large .xlsx file ?
shyam

shyam
answered Nov 30 '-1 00:00

PHPExcelReader
:: https://github.com/Janson-Leung/PHPExcelReader
:: lightweight Excel file reading XLS, XLSX file


<?php
    require 'PHPExcelReader/PHPExcelReader.php';

    try{
        $Reader = new PHPExcelReader('test.xls');
        $current = $Reader->current();    // get the current row data
     } catch (Exception $e) {
        die($e->getMessage());
    }
PHPExcelReader can't read long file because memory issue , PHPExcelReader read file and keep it in memory in once. - Rasi  
Apr 24 '17 05:48
Mitul Dabhi

Mitul Dabhi
answered Nov 30 '-1 00:00

PHPExcel library will good for Reading , Writing and Creating spreadsheet documents in PHP

How to read Xlsx in php ?

download PhpExcel and put in project .
and enable PHP extension php_zip , php_xml , php_gd2


include 'PHPExcel.php';
$objPHPExcel = PHPExcel_IOFactory::load("text.xlsx");

$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
var_dump($sheetData);



more example

get the active sheet



include 'PHPExcel.php';
 
$objPHPExcel = PHPExcel_IOFactory::load("text.xlsx");
$worksheet = $objPHPExcel->getActiveSheet();




read cell of xlsx in php

$worksheet->getCell("A12")->getValue() ;
//do calculation here


or set new cell for calculation result in xlsx at php

$worksheet->setCellValue('B12', '=DPRODUCT(A4:E10,"Yield",A1:B2)');


write xlsx file in php


$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('test.xlsx');




if you are using PHP version 5.6 or higher than its good to use PhpSpreadsheet

PhpSpreadsheet is the next version of PHPExcel.

download PhpSpreadsheet and put in your project

include PhpSpreadsheet and use it

$filename="read.xlsx";
include 'PHPExcel/IOFactory.php';
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filename);


or


include 'PHPExcel/IOFactory.php';
$inputFileType = 'Xls';
//$inputFileType = 'Xlsx';

$filename='example1.xls';
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($inputFileName);

$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
var_dump($sheetData);

Rasi

Rasi
answered Nov 30 '-1 00:00

use spreadsheet-reader is PHP spreadsheet reader library in php which handle large (as in really large) spreadsheet files ,
XLS file parsing is done with php-excel-reader
 
<?php
 
	require('php-excel-reader/excel_reader2.php');

	require('SpreadsheetReader.php');

	$Reader = new SpreadsheetReader('example.xlsx');
	foreach ($Reader as $Row)
	{
		print_r($Row);
	}
?>
Post Answer