Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
780 views
in Technique[技术] by (71.8m points)

php - How to find out how many rows and columns to read from an Excel file with PHPExcel?

With the following code, I am able to read the cells out of an Excel file with PHPExcel.

I currently manually define how many rows and columns to read.

Is there a way that PHPExcel can tell me how many rows and columns I have to read to get all the data out of the worksheet, e.g. even if some rows and columns are left blank?

$file_name = htmlentities($_POST['file_name']);
$sheet_name = htmlentities($_POST['sheet_name']);
$number_of_columns = htmlentities($_POST['number_of_columns']);
$number_of_rows = htmlentities($_POST['number_of_rows']);

$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>';

Solution:

Thanks, Mark, here's the full solution with those functions:

$file_name = htmlentities($_POST['file_name']);
$sheet_name = htmlentities($_POST['sheet_name']);
$number_of_columns = htmlentities($_POST['number_of_columns']);
$number_of_rows = htmlentities($_POST['number_of_rows']);

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

$objPHPExcel = $objReader->load("data/" . $file_name);

$highestColumm = $objPHPExcel->setActiveSheetIndex(0)->getHighestColumn();
$highestRow = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();

echo 'getHighestColumn() =  [' . $highestColumm . ']<br/>';
echo 'getHighestRow() =  [' . $highestRow . ']<br/>';

echo '<table border="1">';
foreach ($objPHPExcel->setActiveSheetIndex(0)->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    echo '<tr>';
    foreach ($cellIterator as $cell) {
        if (!is_null($cell)) {
            $value = $cell->getCalculatedValue();
            echo '<td>';
            echo $value . '&nbsp;';
            echo '</td>';
        }
    }
    echo '</tr>';
}
echo '</table>';

alt text

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
$objPHPExcel->setActiveSheetIndex(0)->getHighestColumn();

and

$objPHPExcel->setActiveSheetIndex(0)->getHighestRow();

or

$objPHPExcel->setActiveSheetIndex(0)->calculateWorksheetDimension();

which returns a range as a string like A1:AC2048

although trailing blank rows and columns are included in these.

EDIT

or you can use the iterators to loop through the existing rows and columns to get each cell within the worksheets used range. See /Tests/28iterator.php in the production distribution for an example. The iterators can be set to ignore blanks.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...