PHPExcel -如何读取带有工作表id的excel工作表

Question

我使用PHPExcel从excel文件导入数据。

在我的当前代码中，我读取带有工作表名称的工作表数据，并在数组中获得结果。现在我想用下面的函数读取带有工作表id的工作表数据。

$objPHPExcel->setActiveSheetIndex(2)

但这不适用于我的代码。

这里是我的代码工作：

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

/** PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/../Classes/PHPExcel/IOFactory.php';

$objReader = PHPExcel_IOFactory::createReader('Excel2007');

$inputFileType = 'Excel2007';
$inputFileName = 'hello.xlsx';
$sheetname = 'Sample'; // I DON'T WANT TO USE SHEET NAME HERE

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadSheetsOnly($sheetname);
$objPHPExcel = $objReader->load($inputFileName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
echo ' Highest Column '. $getHighestColumn = $objPHPExcel->setActiveSheetIndex()->getHighestColumn(); // Get Highest Column
echo ' Get Highest Row '. $getHighestRow = $objPHPExcel->setActiveSheetIndex()->getHighestRow(); // Get Highest Row

echo "<pre>";
    print_r($sheetData);
echo "</pre>";

马克·贝克的：

当工作表名为示例(工作表ID: 2)时，我将在问题代码中得到此结果。

Array
(
    [1] => Array
        (
            [A] => OrderDate
            [B] => Region
            [C] => Rep
            [D] => Item
            [E] => Units
            [F] => Unit Cost
            [G] => Total
        )

    [2] => Array
        (
            [A] => 1/6/13
            [B] => East
            [C] => Jones
            [D] => Pencil
            [E] => 95
            [F] => 1.99
            [G] => 189.05
        )

    [3] => Array
        (
            [A] => 1/23/13
            [B] => Central
            [C] => Kivell
            [D] => Binder
            [E] => 50
            [F] => 19.99
            [G] => 999.5
        )

在改变之后，我得到了这样的结果：

PHPExcel Object
(
    [_uniqueID:PHPExcel:private] => 543b9dab3f6da
    [_properties:PHPExcel:private] => PHPExcel_DocumentProperties Object
        (
            [_creator:PHPExcel_DocumentProperties:private] => comp-30
            [_lastModifiedBy:PHPExcel_DocumentProperties:private] => 
            [_created:PHPExcel_DocumentProperties:private] => 1158364800
            [_modified:PHPExcel_DocumentProperties:private] => 1413191554
            [_title:PHPExcel_DocumentProperties:private] => 
            [_description:PHPExcel_DocumentProperties:private] => 
            [_subject:PHPExcel_DocumentProperties:private] => 
            [_keywords:PHPExcel_DocumentProperties:private] => 
            [_category:PHPExcel_DocumentProperties:private] => 
            [_manager:PHPExcel_DocumentProperties:private] => 
            [_company:PHPExcel_DocumentProperties:private] => Microsoft Corporation
            [_customProperties:PHPExcel_DocumentProperties:private] => Array
                (
                    [KSOProductBuildVer] => Array
                        (
                            [value] => 1033-9.1.0.4674
                            [type] => s
                        )

                )

        )

    [_security:PHPExcel:private] => PHPExcel_DocumentSecurity Object
        (
            [_lockRevision:PHPExcel_DocumentSecurity:private] => 
            [_lockStructure:PHPExcel_DocumentSecurity:private] => 
            [_lockWindows:PHPExcel_DocumentSecurity:private] => 
            [_revisionsPassword:PHPExcel_DocumentSecurity:private] => 
            [_workbookPassword:PHPExcel_DocumentSecurity:private] => 
        )

    [_workSheetCollection:PHPExcel:private] => Array
        (
            [0] => PHPExcel_Worksheet Object

知道吗？

谢谢。

Answer 1

在Mark Baker的帮助下，我现在能够获得带有工作表ID的工作表数据。

这里是完整的代码工作：

<?php
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

/** PHPExcel_IOFactory */
require_once dirname(__FILE__) . '../PHPExcel/IOFactory.php';

$objReader = PHPExcel_IOFactory::createReader('Excel2007');

$inputFileType = 'Excel2007';
$inputFileName = 'hello.xlsx';
$sheetIndex = 2;

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$sheetnames = $objReader->listWorksheetNames($inputFileName);
$objReader->setLoadSheetsOnly($sheetnames[$sheetIndex]);
$objPHPExcel = $objReader->load($inputFileName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

echo "<pre>";
    print_r($sheetData);
echo "</pre>";
?>

谢谢马克·贝克:)

Answer 2

如果要加载工作簿中的所有工作表，则更改

$inputFileType = 'Excel2007';
$inputFileName = 'hello.xlsx';
$sheetname = 'Sample'; // I DON'T WANT TO USE SHEET NAME HERE

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadSheetsOnly($sheetname);
$objPHPExcel = $objReader->load($inputFileName);

至

$inputFileType = 'Excel2007';
$inputFileName = 'hello.xlsx';

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

编辑

根据你的评论：

$inputFileType = 'Excel2007';
$inputFileName = 'hello.xlsx';
$sheetIndex = 2;

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$sheetnames = $objReader->listWorksheetNames($inputFileName);
$objReader->setLoadSheetsOnly($sheetname);
$objReader->setLoadSheetsOnly($sheetnames[$sheetIndex]);
$objPHPExcel = $objReader->load($inputFileName);

这将在sheetindex 2处获取用于工作表的表名，然后使用该表名仅加载该工作表。