Read multiple sets xlsx file with PHP - Phpspreadsheet

Question

Read multiple sets xlsx file with PHP - Phpspreadsheet

Asked 5 years, 10 months ago

Viewed 3,335 times

1

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’m using the Phpspreadsheet library to read the files. I can do when you only have one spreadsheet in the document, but when there are others it doesn’t work.

Test document.xlsx

Planilla1

Planilla2

Code to submit file: 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 data and display in an HTML table: parses.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";
}


?>

Recalling q the code works to read a file with only one spreadsheet. What I need is for him to read the other spreadsheets present in the same file (below the editor Planilla1 and Planilla2

1 answer

Browser other questions tagged php excel phpexcel

You are not signed in. Login or sign up in order to post.

by fernandosavio • **9,013** points · Answer 1 · 2018-09-03T12:45:26+00:00

The problem is on the line:

$sheet = $spreadsheet->getActiveSheet();

That way you are only taking the active spreadsheet and processing the data, without taking the other spreadsheets from the document.

Us Docs talk about the functions $spreadsheet.getSheetCount() and $spreadsheet.getSheet().

It seems to me that the solution to your problem is to take the amount of spreadsheets from the document using $spreadsheet.getSheetCount() and access them within a for with the $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
}