PHP Classes

How can PHP Read Excel File xlsx in 2022 using SimpleXLSX: Parse and retrieve data from Excel XLS files

Recommend this page to a friend!
     
  Info   Example   View files Files   Install with Composer Install with Composer   Download Download   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
StarStarStarStar 73%Total: 23,514 This week: 3All time: 24 This week: 14Up
Version License PHP version Categories
simple-xlsx 0.8.31MIT/X Consortium ...5.0PHP 5, Files and Folders
Collaborate with this project 

Author

simplexlsx - github.com

Description

This class can be used to parse and retrieve data from Excel XLS spreadsheet files.

It can parse a given Excel XLS file by extracting its contents files and parsing the contained XML spreadsheet file.

The class provides functions to retrieve data for the spreadsheet worksheets, rows and cells.

Picture of Sergey Shuchkin
  Performance   Level  
Name: Sergey Shuchkin <contact>
Classes: 4 packages by
Country: Russian Federation Russian Federation
Age: 43
All time rank: 513 in Russian Federation Russian Federation
Week rank: 15 Up1 in Russian Federation Russian Federation Up
Innovation award
Innovation award
Nominee: 1x

Example

<?php
ini_set
('error_reporting', E_ALL);
ini_set('display_errors', true);

require_once
__DIR__.'/../src/SimpleXLSX.php';

echo
'<h1>Parse books.xslx</h1><pre>';
if (
$xlsx = SimpleXLSX::parse('books.xlsx') ) {
   
print_r( $xlsx->rows() );
} else {
    echo
SimpleXLSX::parseError();
}
echo
'<pre>';


Details

SimpleXLSX class (Official)

<img src="https://img.shields.io/packagist/dt/shuchkin/simplexlsx" /> <img src="https://img.shields.io/github/license/shuchkin/simplexlsx" /> <img src="https://img.shields.io/github/stars/shuchkin/simplexlsx" /> <img src="https://img.shields.io/github/forks/shuchkin/simplexlsx" /> <img src="https://img.shields.io/github/issues/shuchkin/simplexlsx" /> <img src="https://img.shields.io/opencollective/all/simplexlsx" /> <img src="https://img.shields.io/badge/patreon-_-_" />

Parse and retrieve data from Excel XLSx files. MS Excel 2007 workbooks PHP reader. No addiditional extensions need (internal unzip + standart SimpleXML parser).

See also:<br/> SimpleXLS old format MS Excel 97 php reader.<br/> SimpleXLSXGen xlsx php writer.

Hey, bro, please ? the package for my motivation :) and donate for more motivation!

Sergey Shuchkin <sergey.shuchkin@gmail.com>

Basic Usage

if ( $xlsx = SimpleXLSX::parse('book.xlsx') ) {
	print_r( $xlsx->rows() );
} else {
	echo SimpleXLSX::parseError();
}
Array
(
    [0] => Array
        (
            [0] => ISBN
            [1] => title
            [2] => author
            [3] => publisher
            [4] => ctry
        )

    [1] => Array
        (
            [0] => 618260307
            [1] => The Hobbit
            [2] => J. R. R. Tolkien
            [3] => Houghton Mifflin
            [4] => USA
        )

)
// SimpleXLSX::parse( $filename, $is_data = false, $debug = false ): SimpleXLSX (or false)
// SimpleXLSX::parseFile( $filename, $debug = false ): SimpleXLSX (or false)
// SimpleXLSX::parseData( $data, $debug = false ): SimpleXLSX (or false)

Installation

The recommended way to install this library is through Composer. New to Composer?

This will install the latest supported version:

$ composer require shuchkin/simplexlsx

or download class here

Examples

XLSX to html table

echo SimpleXLSX::parse('book.xlsx')->toHTML();

or

if ( $xlsx = SimpleXLSX::parse('book.xlsx') ) {
	echo '<table border="1" cellpadding="3" style="border-collapse: collapse">';
	foreach( $xlsx->rows() as $r ) {
		echo '<tr><td>'.implode('</td><td>', $r ).'</td></tr>';
	}
	echo '</table>';
} else {
	echo SimpleXLSX::parseError();
}

XLSX read cells, out commas and bold headers

echo '<pre>';
if ( $xlsx = SimpleXLSX::parse( 'xlsx/books.xlsx' ) ) {
	foreach ( $xlsx->rows() as $r => $row ) {
		foreach ( $row as $c => $cell ) {
			echo ($c > 0) ? ', ' : '';
			echo ( $r === 0 ) ? '<b>'.$cell.'</b>' : $cell;
		}
		echo '<br/>';
	}
} else {
	echo SimpleXLSX::parseError();
}
echo '</pre>';

XLSX get sheet names and sheet indexes

if ( $xlsx = SimpleXLSX::parse( 'xlsx/books.xlsx' ) ) {
	print_r( $xlsx->sheetNames() );
	print_r( $xlsx->sheetName( $xlsx->activeSheet ) );
}
// Sheet numeration started 0
Array
(
    [0] => Sheet1
    [1] => Sheet2
    [2] => Sheet3
)
Sheet2

Gets extend cell info by ->rowsEx()

print_r( SimpleXLSX::parse('book.xlsx')->rowsEx() );
Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [type] => s
                    [name] => A1
                    [value] => ISBN
                    [href] => 
                    [f] => 
                    [format] => 
                    [r] => 1
                    [hidden] =>
                )

            [1] => Array
                (
                    [type] => 
                    [name] => B1
                    [value] => 2016-04-12 13:41:00
                    [href] => 
                    [f] => 
                    [format] => m/d/yy h:mm
                    [r] => 2
                    [hidden] => 1
                )

Select Sheet

$xlsx = SimpleXLSX::parse('book.xlsx');
print_r( $xlsx->rows(1) ); // Sheet numeration started 0, we select second worksheet

Get sheet by index

$xlsx = SimpleXLSX::parse('book.xlsx');	
echo 'Sheet Name 2 = '.$xlsx->sheetName(1);

XLSX::parse remote data

if ( $xlsx = SimpleXLSX::parse('http://www.example.com/example.xlsx' ) ) {
	$dim = $xlsx->dimension(1); // don't trust dimension extracted from xml
	$num_cols = $dim[0];
	$num_rows = $dim[1];
	echo $xlsx->sheetName(1).':'.$num_cols.'x'.$num_rows;
} else {
	echo SimpleXLSX::parseError();
}

XLSX::parse memory data

// For instance $data is a data from database or cache    
if ( $xlsx = SimpleXLSX::parseData( $data ) ) {
	print_r( $xlsx->rows() );
} else {
	echo SimpleXLSX::parseError();
}

Get Cell (slow)

echo $xlsx->getCell(0, 'B2'); // The Hobbit

DateTime helpers

// default SimpleXLSX datetime format YYYY-MM-DD HH:MM:SS (MySQL)
echo $xlsx->getCell(0,'C2'); // 2016-04-12 13:41:00

// custom datetime format
$xlsx->setDateTimeFormat('d.m.Y H:i');
echo $xlsx->getCell(0,'C2'); // 12.04.2016 13:41

// unixstamp
$xlsx->setDateTimeFormat('U');
$ts = $xlsx->getCell(0,'C2'); // 1460468460
echo gmdate('Y-m-d', $ts); // 2016-04-12
echo gmdate('H:i:s', $ts); // 13:41:00

// raw excel value
$xlsx->setDateTimeFormat( NULL ); // returns as excel datetime
$xd = $xlsx->getCell(0,'C2'); // 42472.570138889
echo gmdate('m/d/Y', $xlsx->unixstamp( $xd )); // 04/12/2016
echo gmdate('H:i:s', $xlsx->unixstamp( $xd )); // 13:41:00 

Rows with header values as keys

if ( $xlsx = SimpleXLSX::parse('books.xlsx')) {
	// Produce array keys from the array values of 1st array element
	$header_values = $rows = [];
	foreach ( $xlsx->rows() as $k => $r ) {
		if ( $k === 0 ) {
			$header_values = $r;
			continue;
		}
		$rows[] = array_combine( $header_values, $r );
	}
	print_r( $rows );
}
Array
(
    [0] => Array
        (
            [ISBN] => 618260307
            [title] => The Hobbit
            [author] => J. R. R. Tolkien
            [publisher] => Houghton Mifflin
            [ctry] => USA
        )
    [1] => Array
        (
            [ISBN] => 908606664
            [title] => Slinky Malinki
            [author] => Lynley Dodd
            [publisher] => Mallinson Rendel
            [ctry] => NZ
        )
)

Debug

ini_set('error_reporting', E_ALL );
ini_set('display_errors', 1 );

if ( $xlsx = SimpleXLSX::parseFile('books.xlsx', true ) ) {
	echo $xlsx->toHTML();
} else {
	echo SimpleXLSX::parseError();
}

Classic OOP style

$xlsx = new SimpleXLSX('books.xlsx'); // try...catch
if ( $xlsx->success() ) {
	print_r( $xlsx->rows() );
} else {
	echo 'xlsx error: '.$xlsx->error();
}

More examples here

Error Codes

SimpleXLSX::ParseErrno(), $xlsx->errno()<br/> <table> <tr><th>code</th><th>message</th><th>comment</th></tr> <tr><td>1</td><td>File not found</td><td>Where file? UFO?</td></tr> <tr><td>2</td><td>Unknown archive format</td><td>ZIP?</td></tr> <tr><td>3</td><td>XML-entry parser error</td><td>bad XML</td></tr> <tr><td>4</td><td>XML-entry not found</td><td>bad ZIP archive</td></tr> <tr><td>5</td><td>Entry not found</td><td>File not found in ZIP archive</td></tr> <tr><td>6</td><td>Worksheet not found</td><td>Not exists</td></tr> </table>


  Files folder image Files (9)  
File Role Description
Files folder imageexamples (5 files)
Files folder imagesrc (1 file)
Accessible without login Plain text file CHANGELOG.md Data Auxiliary data
Accessible without login Plain text file license.md Lic. License text
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files (9)  /  examples  
File Role Description
  Accessible without login Plain text file 01-basic_usage.php Example Example script
  Accessible without login Plain text file 02-rows_and_rowsEx.php Example Example script
  Accessible without login Plain text file 03-sheets.php Example Example script
  Accessible without login Plain text file 04-upload_and_convert_to_html.php Example Example script
  Accessible without login Plain text file 05-rows_with_header_values_as_keys.php Example Example script

  Files folder image Files (9)  /  src  
File Role Description
  Plain text file SimpleXLSX.php Class Class source

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
 Version Control Unique User Downloads Download Rankings  
 100%
Total:23,514
This week:3
All time:24
This week:14Up
User Ratings User Comments (17)
 All time
Utility:95%StarStarStarStarStar
Consistency:88%StarStarStarStarStar
Documentation:79%StarStarStarStar
Examples:86%StarStarStarStarStar
Tests:-
Videos:-
Overall:73%StarStarStarStar
Rank:135
 
Merci ! &#1093;&#1086;&#1088;&#1086;&#1096;&#1086; &#1089;&#1...
2 years ago (PHX)
80%StarStarStarStarStar
verygood
5 years ago (Qu?nh Châm C?u)
80%StarStarStarStarStar
Very pleased - good job!
5 years ago (Laurence Jones)
80%StarStarStarStarStar
Thank you, Sergey.
6 years ago (John R Monteith)
80%StarStarStarStarStar
Why? const SCHEMA_REL_OFFICEDOCUMENT = 'http://schemas.
8 years ago (zafado)
80%StarStarStarStarStar
Absolutely simple to use
9 years ago (eduardo de souza)
80%StarStarStarStarStar
Good solution for XLSX file parsing.
9 years ago (Janko Hrasko)
70%StarStarStarStar
Many thanks Sergey!
9 years ago (Arman)
80%StarStarStarStarStar
Great, no memory troubles on big sheets.
10 years ago (J.Daam)
72%StarStarStarStar
Good! A tough problem and a great start
11 years ago (sigmond axel)
60%StarStarStarStar
Great job! Very usful class.
12 years ago (AL)
75%StarStarStarStar
Exactly what I was looking for.
13 years ago (Graham Zell)
77%StarStarStarStar
Good solution for parsing XLSX file.
13 years ago (keunhee Han)
72%StarStarStarStar
The script work easely and excelent.
14 years ago (Sommy)
60%StarStarStarStar
Brilliant solution.
14 years ago (irek zuchowski)
60%StarStarStarStar
The only thing missing is the handling of date types during t...
14 years ago (frank bradley)
60%StarStarStarStar
perfect.
14 years ago (Higor Camara Vaz da Costa)
60%StarStarStarStar