Read only specific sheet

Question

I am trying to read just one sheet from an xls document and I have this:

$objPHPExcel = $objReader->load('daily/' . $fisierInbound);
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    $worksheetTitle     = $worksheet->getTitle();
    $highestRow         = $worksheet->getHighestRow(); // e.g. 10
    $highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    $dataCalls          = $worksheet->getCellByColumnAndRow(2, 2)->getValue();
    $dataSubstr         = substr($dataCalls, 53);        
}

The problem is that it reads all the sheets of the file.

Any ideas?

You have a iteraration `for each` worksheet. It's some kind of obvious that his going to read all of them! You need to call the `getActiveSheet()` method from PHPExcel's class. — Dennis Braga, Apr 15 '14 at 11:04
`getHighestRow()` should be replaced with `getHighestDataRow()`; same for Column function... — Raptor, May 27 '16 at 02:37

score 23 · Answer 1 · edited Jul 14 '20 at 21:15

As described in the PHPExcel User Documentation - Reading Spreadsheet Files document in the /Documentation folder (section 5.2. entitled"Reading Only Named WorkSheets from a File"):

If you know the name of the sheet that you want to read.

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example1.xls'; 
$sheetname = 'Data Sheet #2'; 

/**  Create a new Reader of the type defined in $inputFileType  **/ 
$objReader = PHPExcel_IOFactory::createReader($inputFileType); 
/**  Advise the Reader of which WorkSheets we want to load  **/ 
$objReader->setLoadSheetsOnly($sheetname); 
/**  Load $inputFileName to a PHPExcel Object  **/ 
$objPHPExcel = $objReader->load($inputFileName);

If you don't know the name of the worksheet in advance, you can get a list of all worksheets before loading the file

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example1.xls'; 

/**  Create a new Reader of the type defined in $inputFileType  **/ 
$objReader = PHPExcel_IOFactory::createReader($inputFileType); 
/**  Read the list of worksheet names and select the one that we want to load  **/
$worksheetList = $objReader->listWorksheetNames($inputFileName)
$sheetname = $worksheetList[0]; 

/**  Advise the Reader of which WorkSheets we want to load  **/ 
$objReader->setLoadSheetsOnly($sheetname); 
/**  Load $inputFileName to a PHPExcel Object  **/ 
$objPHPExcel = $objReader->load($inputFileName);

Being able to obtain the list of names before loading the main sheet - fantastic. I had a process for naming sheets, but it changed at some point, so it doesn't match up with older workbooks that need to be re-visited. The order doesn't change so I know it is the second sheet, but as pointed out you need the name. — jpmcc, Sep 14 '22 at 10:41

score 15 · Answer 2 · answered Jun 04 '15 at 17:15

15

You can do it easier than getting list of worksheet names:

$objPHPExcel->setActiveSheetIndex(2);
$worksheet = $objPHPExcel->getActiveSheet();

Loads #2 (third) worksheet.

answered Jun 04 '15 at 17:15

Mycelin

607
8
6

1

But what if user change sheet indexes? – Muhammad Tarique Jul 17 '17 at 06:54

Muhammad Tarique · Answer 3 · 2017-11-09T06:53:03.473

9

An easiest way for those who is still struggling with this -

//include library
include('path/to/PHPExcel/IOFactory.php');

//load the file
$objPHPExcel = PHPExcel_IOFactory::load('your/path/for/excel/file');

//get the worksheet of your choice by its name
$worksheet = $objPHPExcel->getSheetByName('Name of sheet');

#and your work goes here...

edited Nov 09 '17 at 06:53

answered Jul 17 '17 at 07:20

Muhammad Tarique

1,407
1
13
17

G_real · Answer 4 · 2019-01-24T15:47:11.693

//load library - EXCEL
$this->load->library('excel');
$objPHPExcel = PHPExcel_IOFactory::load('./folder/exceldata.xls');

Individual worksheets can be accessed by name, or by their index position in the workbook. The index position represents the order that each worksheet "tab" is shown when the workbook is opened in MS Excel (or other appropriate Spreadsheet program).

To access a sheet by name, use the getSheetByName() method, specifying the name of the worksheet that you want to access.

//Retrieve the worksheet called 'Worksheet 1'
$objPHPExcel->getSheetByName('Worksheet 1');

To access a sheet by its index, use the getSheet() method. Note that sheets are indexed from 0.

//Retrieve the **1st 'tab' worksheet** e.g. called 'Sheet 1'
$worksheet = $objPHPExcel->getSheet(0);
//Retrieve the **2nd 'tab' worksheet** e.g. called 'Sheet 2'
$worksheet = $objPHPExcel->getSheet(1);

This all can be achieved by help of @Mark Baker 's PHPExcel Library. Thanks.

score 0 · Answer 5 · answered Jan 02 '23 at 09:44

// In CodeIgniter Version - 3.1.11

public function upload(){
        
        ini_set('MAX_EXECUTION_TIME', -1);
        ini_set('memory_limit', '-1');          
        
        if(isset($_FILES["file_upload"]["name"]))
        {
            require 'vendor/autoload.php';
            $path = $_FILES["file_upload"]["tmp_name"];
            $object = PHPExcel_IOFactory::load($path);

            $db_flag = true;
            foreach($object->getWorksheetIterator() as $worksheet)
            {         

                $highestRow = $worksheet->getHighestRow();
                $highestColumn = $worksheet->getHighestColumn();

                $sheetname = $worksheet->getTitle();
                $sheetname = trim($sheetname);
                if ($sheetname == "Asset Wise & Location Wise") 
                {
                    $db_flag = false;
                    $db_count = 0;
                    $this->db->truncate("fi_asset_wise");
                    $this->load->helper('dt_helper');
                    
                    $this->db->trans_start();                           

                    // get data from excel
                    for($row=5; $row<=$highestRow; $row++)
                    {
                        $asst_categ     = trim($worksheet->getCellByColumnAndRow(0, $row)->getCalculatedValue());
                        if(empty($asst_categ) || strpos(strtoupper($asst_categ), "TOTAL") !== FALSE )
                        {
                            continue;
                        }
                        $descrip        = trim($worksheet->getCellByColumnAndRow(1, $row)->getCalculatedValue());
                        $rate           = trim($worksheet->getCellByColumnAndRow(2, $row)->getCalculatedValue());
                        $main_head      = trim($worksheet->getCellByColumnAndRow(3, $row)->getCalculatedValue());
                        $sub_head       = trim($worksheet->getCellByColumnAndRow(4, $row)->getCalculatedValue());
                        $gl_code        = trim($worksheet->getCellByColumnAndRow(5, $row)->getCalculatedValue());
                        $asset_code     = trim($worksheet->getCellByColumnAndRow(6, $row)->getCalculatedValue());
                        
                        if(empty($dt_of_sale)) {
                            $dt_of_sale = null;
                        }else{    
                            
                            $db_udt = ($dt_of_sale- 25569) * 86400;
                            $date=date("d-m-Y", $db_udt);
                            $dt_of_sale = check_format_method($date);                                          
                        }
                        
                        $no_of_days         = trim($worksheet->getCellByColumnAndRow(26, $row)->getCalculatedValue());

                        $insert_arr = array(
                            'asst_categ'  => $asst_categ,
                            'descrip'  => $descrip,
                            'rate'  => $rate,
                            'main_head'  => $main_head,
                            'sub_head'  => $sub_head,
                            'gl_code'  => $gl_code,
                            'asset_code'  => $asset_code,
                            'dt_of_sale'  => $dt_of_sale,
                            'no_of_days'  => ($no_of_days >= 0.00 )?$no_of_days:0.00,   
                            'status' => 1,
                            'created_at' => date('Y-m-d H:i:s'),
                            'updated_at' => date('Y-m-d H:i:s')
                        );

                        if(!empty($insert_arr)) 
                        {
                            $this->ass_model->insertData($insert_arr);
                            ++$db_count;
                        }
                    }
                    $this->db->trans_complete(); 

                    // Running Transactions Manually
                    if ($this->db->trans_status() === FALSE){
                        $this->db->trans_rollback();
                    } else {
                        $this->db->trans_commit();
                    }
                }
            }
            
            if($db_flag === true)
            {
                $this->session->set_flashdata('db_error', 'Error !! Invalid Sheet Name. Please enter correct sheetname.');
                redirect(site_url('fixed-asset/fixed-asset-register'));
            }

            if($db_count > 0)
            {
                $this->session->set_flashdata('db_success', 'Success - Data Inserted Successfully');
                redirect(site_url('fixed-asset/fixed-asset-register'));
            }
        }
    }

Read only specific sheet

5 Answers5

Linked