Read multiple sheets xlsx file with PHP - PhpSpreadsheet

Question

Read multiple sheets xlsx file with PHP - PhpSpreadsheet

Navigation

#1 by (1 votes)

0

I have a file in xlsx format with two spreadsheets and I need to read the data in PHP to create an import script in the database.

I am using the PhpSpreadsheet library to read the files. I can do it when I only have one worksheet in the document, but when there are others it does not work.

Test document.xlsx

Worksheet1

Worksheet2

Codetosubmitfile:index.php

<!DOCTYPE html>
<html>
<head>
	<title>Importacao</title>
</head>
<body>
	<h1>Upload do arquivo</h1>
	<form method="POST" action="processa.php" enctype="multipart/form-data">
		<label>Arquivo</label>
		<input type="file" name="arquivo"><br><br>
		<input type="submit" value="enviar">
	</form>
</body>
</html>

Code to read the data and display it in an HTML table: process.php

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet; //classe responsável pela manipulação da planilha


function readData($arquivo){

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx");

$spreadsheet = $reader->load($arquivo);

$sheet = $spreadsheet->getActiveSheet();

//Coluna - Retira o título(2)

echo '<table border="1" cellpadding="8" style="margin-left:100px;">';
foreach ($sheet->getRowIterator(2) as $row) {
    $cellInterator = $row->getCellIterator();
    $cellInterator->setIterateOnlyExistingCells(false);

    echo '<tr>';
    //Linha
    foreach ($cellInterator as $cell) {
        if(!is_null($cell)){
            $value = $cell->getCalculatedValue();
            echo "<td> $value </td>";
        }
    }
    echo '</tr>';
}
echo "</table>";
}

$dados = $_FILES['arquivo'];

var_dump($dados);


$route = $_FILES['arquivo']['tmp_name'];
readData($route);
//$route = 'spreadsheet1.xlsx';
if(!empty($route)){


}else{
    echo "null";
}


?>

Remembering that the code works to read a file with only one worksheet. What I need is for it to read the other sheets present in the same file (below the calc editor with the names Sheet1 and Sheet2

php excel phpexcel

asked by anonymous 03.09.2018 / 04:30

1 answer

Apply setItemOnClickListener to Button GridView. Kotlin or Java Loading script.js (external) using DOMContentLoaded?

score 1 · Accepted Answer

The problem is on the line:

$sheet = $spreadsheet->getActiveSheet();

This way you are only taking the active worksheet and processing the data, without taking the other worksheets in the document.

In docs talk about the functions $spreadsheet.getSheetCount() and $spreadsheet.getSheet() .

I think the solution to your problem is to get the amount of spreadsheets in the document using $spreadsheet.getSheetCount() and access them within for with $spreadsheet.getSheet() .

Example:

<?php

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx");

$spreadsheet = $reader->load($arquivo);
$sheet_count = $spreadsheet->getSheetCount();

for ($i=0 ; $i < $sheet_count ; $i++) {
    $sheet = $spreadsheet->getSheet($i);

    // processa os dados da planilh
}