只读取特定的工作表

Question

我正在尝试从 xls 文件中读取一个工作表，我有以下代码：

$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);        
} 

问题在于它会读取文件中的所有工作表。

有任何想法吗？

- ardb

---

你有一个针对每个工作表的迭代循环。显然它将读取所有工作表！你需要调用PHPExcel类中的getActiveSheet()方法。 - Dennis Braga

getHighestRow() 应该替换为 getHighestDataRow()；列函数同理。 - Raptor

Answer 1

根据位于/Documentation文件夹中的PHPExcel用户文档-读取电子表格文件中所述（第5.2节名为“仅从文件中读取命名工作表”）： 如果您知道要读取的工作表的名称。

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

如果您事先不知道工作表的名称，可以在加载文件之前获取所有工作表的列表。

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

- Mark Baker

---

在加载主表之前能够获取名称列表 - 真是太棒了。我有一个为工作表命名的过程，但它在某个时候发生了变化，所以它与需要重新访问的旧工作簿不匹配。顺序不会改变，所以我知道它是第二张工作表，但正如指出的那样，你需要知道名称。 - jpmcc

Answer 2

您可以更轻松地完成它，而不必获取工作表名称列表：

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

加载第三个工作表（第二个）。

- Mycelin

---

但是如果用户更改了工作表索引会怎么样？ - Muhammad Tarique

Answer 3

一种最简单的方法适用于那些仍在苦苦挣扎的人 -

//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...

- Muhammad Tarique

Answer 4

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

可以通过名称或工作簿中的索引位置访问单个工作表。索引位置表示打开MS Excel（或其他适当的电子表格程序）中每个工作表“选项卡”显示的顺序。

要按名称访问工作表，请使用getSheetByName()方法，指定要访问的工作表的名称。

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

通过索引值访问工作表，使用getSheet()方法。请注意，工作表的索引值从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);

这一切都可以通过@Mark Baker的PHPExcel库来实现。谢谢。

- G_real

Answer 5

// 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'));
            }
        }
    }

- Sonu Chohan