SimpleXLSX class (Official)

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:
SimpleXLS old format MS Excel 97 php reader.
SimpleXLSXGen xlsx php writer.

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

Sergey Shuchkin [email protected]

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();

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() );
}
// Sheet numeration started 0

Array
(
    [0] => Sheet1
    [1] => Sheet2
    [2] => Sheet3
)

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()

code	message	comment
1	File not found	Where file? UFO?
2	Unknown archive format	ZIP?
3	XML-entry parser error	bad XML
4	XML-entry not found	bad ZIP archive
5	Entry not found	File not found in ZIP archive
6	Worksheet not found	Not exists

zeu5oul / simplexlsx Goto Github PK

simplexlsx's Introduction