Php read excel file to array

Clients often prefer to send me Excel .xlsx files that are basically just CSV files. E.g. there is just one sheet and it's simply a table of data starting from A0. In such circumstances, one can manually convert them to CSV format, or one can have PhpSpreadsheet read them directly as-is. This tutorial will show you how to do the latter.

Steps

Below is an commented example of how to read a .xlsx spreadsheet that does better to explain than I could do with words.

setReadDataOnly(true);

// Read the spreadsheet file.
$spreadsheet = $reader->load(__DIR__ . '/path/to/file.xlsx');

$sheet = $spreadsheet->getSheet($spreadsheet->getFirstSheetIndex());
$data = $sheet->toArray();

// output the data to the console, so you can see what there is.
die(print_r($data, true)); 

With the following example file, you get the following output:

Array
(
    [0] => Array
        (
            [0] => ID
            [1] => Circumstance
        )

    [1] => Array
        (
            [0] => 1
            [1] => Metal-Detecting
        )

    [2] => Array
        (
            [0] => 2
            [1] => Chance find
        )

    [3] => Array
        (
            [0] => 3
            [1] => Fieldwalking
        )

    [4] => Array
        (
            [0] => 4
            [1] => Mudlarking
        )

    [5] => Array
        (
            [0] => 5
            [1] => Gardening
        )

    [6] => Array
        (
            [0] => 6
            [1] => Other chance find
        )

    [7] => Array
        (
            [0] => 7
            [1] => Archaeological investigation
        )

    [8] => Array
        (
            [0] => 8
            [1] => Construction
        )

    [9] => Array
        (
            [0] => 9
            [1] => Agriculture or drainage work
        )

    [10] => Array
        (
            [0] => 10
            [1] => Investigation of shipwreck
        )

    [11] => Array
        (
            [0] => 11
            [1] => Unknown
        )

)

However, I found that if I opened the spreadsheet (in LibreOffice), and pressed Ctrl + A to select all cells, and then manually set the font to Arial, I generated the following bad example that would consider every cell to having an empty value. This suggests to me that the $reader->setReadDataOnly(true); doesn't quite cover me. If you know of a change that will resolve this, then please paste a solution in the comments.

Last updated: 23rd February 2022
First published: 23rd February 2022

I have a big excel file that looks like this:

Php read excel file to array

I would like to put each row into an array.

Is this possible to access the first row's order id like this?

$result[0][2] // returns 7432

Assuming the actual first row that gives prefix for the columns' name is not present.

How could I do that?

Php read excel file to array

asked Nov 17, 2011 at 16:17

May be my answer is too simple (for one time work only), but I use the CONCATENATE "Function" in excell. The last cell on each row will have concatenation function, like this:

=CONCATENATE("['";A2;"'=>['data1' => '";B2;"', 'data2' => '";C2;"'],")

where:

column "A" is ID of something;
column "B" is first characteristic;
column "C" is second characteristic;
etc.

Then just copy and paste function results to Your script or config file, and do not forget the first and the last bracket.

answered Apr 17, 2015 at 19:34

1

This works for me:

$content = file_get_contents($your_file_path); 
$lines = array_map("rtrim", explode("\n", $content));

Lars Ebert

3,4272 gold badges22 silver badges44 bronze badges

answered Jul 10, 2015 at 6:19

Php read excel file to array

paulalexandrupaulalexandru

9,0336 gold badges62 silver badges92 bronze badges

Since the PHPExcel library deprecated they've released "PhpSpreadsheet"

This will help PhpSpreadsheet

answered Oct 28, 2018 at 10:07

Php read excel file to array

1

Can we read Excel file in PHP?

PhpSpreadsheet is a library written in pure PHP and offers a set of classes that allow you to read and write various spreadsheet file formats such as Excel and LibreOffice Calc. In this tutorial, we are going learn how to read and write the xlsx file. You can integrate it with your database if you need.

How do I convert Excel data to array?

Upload or paste your Excel. Just paste (copy tables from Microsoft Excel, Google Sheets, Mac Numbers or a web page) or drag-and-drop your Excel into the textarea of Data Source, and it will immediately perform the magic of the conversion..
Edit your Excel online, if needed. ... .
Copy the converted JSON Array..

How do you read and write Excel file in PHP?

Loading a Spreadsheet File $inputFileName = './sampleData/example1. xls'; /** Load $inputFileName to a Spreadsheet Object **/ $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName); See samples/Reader/01_Simple_file_reader_using_IOFactory. php for a working example of this code.

How can I open XLSX file in PHP?

EasyXLS on Linux, Mac, Windows using Java with PHP.
Step 1: Download and install EasyXLS Excel Library for Java. To download the trial version of EasyXLS Excel Library, press the below button: ... .
Step 2: Install PHP/Java Bridge. ... .
Step 3: Setup EasyXLS library in Tomcat. ... .
Step 4: Run PHP code that reads XLSX file..