PHPExcel在每个表中迭代获取特定的单元格 - PHPExcel Iterate through sheets getting specific cells in each

我试图通过Excel工作表迭代以获得每个工作表上的特定单元格。

它很好地遍历工作表，打印索引。但是对活动表的引用不会改变。我从每次迭代的第一个工作表中获得相同的信息。

这是我的…

<?php
echo "<!DOCTYPE html>
        <head>
            <style>
                table th {
                    text-align: right;
                    padding: 0.5em;
                }
                table td {
                    background-color: rgba(0,0,3, 0.1);
                    padding: 0.5em;
                }
            </style>
        </head>
        <body>";
/** Include PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/../Classes/PHPExcel/IOFactory.php';
$inputFileName = '01hospital.xlsx';
//  Read your Excel workbook
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objReader->setReadDataOnly(true);
    $objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    echo 'Worksheet number - ', $objPHPExcel->getIndex($worksheet) , PHP_EOL;
    $division = $objPHPExcel->getActiveSheet()->getCell('C3');
    $hospital = $objPHPExcel->getActiveSheet()->getCell('B5');
    $site_id = $objPHPExcel->getActiveSheet()->getCell('C6');
    $address = $objPHPExcel->getActiveSheet()->getCell('C7');
    $phone = $objPHPExcel->getActiveSheet()->getCell('C8');
    $time_zone = $objPHPExcel->getActiveSheet()->getCell('C9');
    $mhbs = $objPHPExcel->getActiveSheet()->getCell('F12'); // Default 0
    $mh_inc = $objPHPExcel->getActiveSheet()->getCell('F13'); // Default $0
    $ms_inc = $objPHPExcel->getActiveSheet()->getCell('F14'); // Default $0
    $mnwdo = $objPHPExcel->getActiveSheet()->getCell('I15');
    $facility_rate = $objPHPExcel->getActiveSheet()->getCell('D16');
    $mndo = $objPHPExcel->getActiveSheet()->getCell('H17'); // Default 10
    $mnpo = $objPHPExcel->getActiveSheet()->getCell('H18'); // Default 2
    $dosd = $objPHPExcel->getActiveSheet()->getCell('J21'); // Default 60
    $reseot = $objPHPExcel->getActiveSheet()->getCell('J22'); // Default 15
    $odnas = $objPHPExcel->getActiveSheet()->getCell('J23'); // Default 'No'
    $ssnas = $objPHPExcel->getActiveSheet()->getCell('J24'); // Default 'No'

    echo "<table>
            <tr><th>Division:</th><td>$division</td></tr>
            <tr><th>Hospital</th><td>$hospital</td></tr>
            <tr><th>Site ID</th><td>$site_id</td></tr>
            <tr><th>Address</th><td>$address</td></tr>
            <tr><th>Phone</th><td>$phone</td></tr>
            <tr><th>Time Zone</th><td>$time_zone</td></tr>
            <tr><th>Minimum Hours Between Shifts</th><td>$mhbs</td></tr>
            <tr><th>Max Hourly INC</th><td>$mh_inc</td></tr>
            <tr><th>Max Shift INC</th><td>$ms_inc</td></tr>
            <tr><th>Max Number of Weekend Days Off</th><td>$mnwdo</td></tr>
            <tr><th>Facility Rate</th><td>'$$facility_rate</td></tr>
            <tr><th>Max Number of Days Off</th><td>$mndo</td></tr>
            <tr><th>Max Number of Providers Off per Day</th><td>$mnpo</td></tr>
            <tr><th>Day Off Submission Deadline</th><td>$dosd</td></tr>
            <tr><th>Reminder Email Sent to Enter Off Time</th><td>$reseot</td></tr>
            <tr><th>Do Off Days Need Approval by Scheduler?</th><td>$odnas</td></tr>
            <tr><th>Does Swapping Shifts Need Approval by Scheduler?</th><td>$ssnas</td></tr>
        </table>
        <hr />";
}
    echo "Success: ".date('H:i:s');
?>

您是对的，当您遍历工作表时，活动工作表不会更改。它不需要改变，实际上也不应该改变....但是你也不需要关心。

整个要点是，您将迭代中的当前工作表分配给$worksheet，因此您可以使用它访问单元格。

foreach ($objPHPExcel->getWorksheetIterator() as $worksheetNbr => $worksheet) {
    echo 'Worksheet number - ', $worksheetNbr, PHP_EOL;
    $division = $worksheet->getCell('C3');
    ....
}

并且(如上所示)您也可以通过foreach获取工作表索引，而无需调用$objPHPExcel->getIndex($worksheet)

一旦包含了PHPExcel_IOFactory，请使用静态方法'load'

$objPHPExcel = PHPExcel_IOFactory::load($filePatch);

，然后得到一个数组的表名

$sheetNames = $objPHPExcel->getSheetNames();

那么你有这个:

foreach ($sheetNames as $sheetNameIndex => $sheetName)
    {
        $sheet =   $objPHPExcel->setActiveSheetIndexByName($sheetName)->toArray(null,true,true,true); //sets and returns active sheet
        //parse each array and insert them into database
    }

在$sheet变量你有excel表格作为数组

在发布我的问题后不到五分钟，我发现了以下解决方法:

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    echo 'Worksheet number - ', $objPHPExcel->getIndex($worksheet) , PHP_EOL;
    $index =  $objPHPExcel->getIndex($worksheet);
    $division = $objPHPExcel->setActiveSheetIndex($index)->getCell('C3');
    //  Rest of code here ...
}