Is there a way to get worksheet names from 20 MB + Excel files using PHPExcel?

Question

I am using PHPExcel to read data from Excel files.

In the following code, I can read one specific worksheet from an Excel 3 MB file in just a couple of seconds. It works well.

However, now I have 27 MB and 88 MB Excel files from which I need to get the data. They are so large that even OpenOffice cannot open them.

I found that when loading a sheet, I can use the index number instead of the name, but this seems inconsistent, for example. in one Excel file setLoadSheetsOnly(0)gave me the third sheet, and setLoadSheetsOnly(1)gave me an error , although the file had four sheets. So for some reason it seems unreliable .

Is there a way to read worksheet names from a large file so that I can access one of my worksheets at the same time?

        $objReader = PHPExcel_IOFactory::createReaderForFile("data/" . $file_name);
        $objReader->setLoadSheetsOnly(array($sheet_name));
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load("data/" . $file_name);

        echo '<table border="1">';
        for ($row = 1; $row < $number_of_rows; $row++) {
            echo '<tr>';
            for ($column = 0; $column < $number_of_columns; $column++) {
                $value = $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($column, $row)->getValue();
                echo '<td>';
                echo $value . '&nbsp;';
                echo '</td>';
            }
            echo '</tr>';
        }
        echo '</table>';
        die;

ADDITION:

I found some code that comes close, but it seems it is not always accurate, for example. here he skipped the second worksheet in a 27 MB file:

and here he received only the third sheet and skipped 3 more:

$objReader = PHPExcel_IOFactory::createReaderForFile("data/" . $file_name);
$objReader->setLoadSheetsOnly(0);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("data/" . $file_name);

echo $objPHPExcel->getSheetCount(), ' worksheets<hr/>';
$loadedSheetNames = $objPHPExcel->getSheetNames();
foreach ($loadedSheetNames as $sheetIndex => $loadedSheetName) {
    echo $sheetIndex, ' -> ', $loadedSheetName, '<br />';
}
die;

Answer 1

, .

, setLoadSheetsOnly() : , , in_array() , , , . .

// check if sheet should be skipped
if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) {
    continue;
}

, 0 == "mySheetName" ( PHP ).

, Reader, , .

Classes/PHPExcel/Reader/Excel2007.php

/**
 * Reads names of the worksheets from a file, without loading the whole file to a PHPExcel object
 *
 * @param   string      $pFilename
 * @throws  Exception
 */
public function listWorksheetNames($pFilename)
{
    // Check if file exists
    if (!file_exists($pFilename)) {
        throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
    }

    $worksheetNames = array();

    $zip = new ZipArchive;
    $zip->open($pFilename);

    $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
    foreach ($rels->Relationship as $rel) {
        switch ($rel["Type"]) {
            case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
                $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));  //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");

                if ($xmlWorkbook->sheets) {
                    foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
                        // Check if sheet should be skipped
                        $worksheetNames[] = (string) $eleSheet["name"];
                    }
                }
        }
    }

    $zip->close();

    return $worksheetNames;
}

, :

$inputFileType = 'Excel2007';
$inputFileName = 'biostat-behfisk-2005.xlsx';
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$worksheetNames = $objReader->listWorksheetNames($inputFileName);

foreach ($worksheetNames as $sheetName) {
    echo $sheetName, '<br />';
}

$worksheetNames UTF-8. .xlsx , . , PHPExcel SVN, () , , , .

EDIT2

Excel5 Reader

/**
 * Reads names of the worksheets from a file, without loading the whole file to a PHPExcel object
 *
 * @param   string      $pFilename
 * @throws  Exception
 */
public function listWorksheetNames($pFilename)
{
    // Check if file exists
    if (!file_exists($pFilename)) {
        throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
    }

    $worksheetNames = array();

    // Read the OLE file
    $this->_loadOLE($pFilename);

    // total byte size of Excel data (workbook global substream + sheet substreams)
    $this->_dataSize = strlen($this->_data);

    $this->_pos     = 0;
    $this->_sheets  = array();

    // Parse Workbook Global Substream
    while ($this->_pos < $this->_dataSize) {
        $code = self::_GetInt2d($this->_data, $this->_pos);

        switch ($code) {
            case self::XLS_Type_BOF:    $this->_readBof();      break;
            case self::XLS_Type_SHEET:  $this->_readSheet();    break;
            case self::XLS_Type_EOF:    $this->_readDefault();  break 2;
            default:                    $this->_readDefault();  break;
        }
    }

    foreach ($this->_sheets as $sheet) {
        if ($sheet['sheetType'] != 0x00) {
            // 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
            continue;
        }

        $worksheetNames[] = $sheet['name'];
    }

    return $worksheetNames;
}

, Excel2007 Reader, , .xls , .

Answer 2

phpexcel, :

public function getWorksheetNames($pFilename) {

    $worksheetNames = array ();

    $zip = zip_open ( $pFilename );
    while ( $entry = zip_read ( $zip ) ) {

        $entry_name = zip_entry_name ( $entry );
        if ($entry_name == 'xl/workbook.xml') {
            if (zip_entry_open ( $zip, $entry, "r" )) {
                $buf = zip_entry_read ( $entry, zip_entry_filesize ( $entry ) );
                $workbook = simplexml_load_string ( $buf );
                foreach ( $workbook->sheets as $sheets ) {
                    foreach( $sheets as $sheet) {
                        $attributes=$sheet->attributes();
                        $worksheetNames[]=$attributes['name'];
                    }
                }
                zip_entry_close ( $entry );
            }
            break;
        }

    }
    zip_close ( $zip );
    return $worksheetNames;
}

excel 2007 , ,