如何获取工作表名称（bcoz in
one

pre> $ sheetName = $ objPHPExcel-> getActiveSheet（） - > getTitle（）;


时间更改为某个整数值，
与excel工作表相同

查看PHPExcel_Shared_Date :: ExcelToPHP（$ excelDate）或PHPExcel_Shared_Date :: ExcelToPHPObject（$ excelDate）日期/时间值到PHP时间戳或DateTime对象

查看$ objPHPExcel-> getActiveSheet（） - > toArray（）方法，而不是循环遍历所有rwos和列自己。如果你想使用toArray与格式化的参数虽然，不要使用$ objReader-> setReadDataOnly（true）;否则PHPExcel不能区分数字和日期/时间。最新的SVN代码为工作表对象添加了一个rangeToArray（）方法，它允许您一次读取一行（或单元格块），例如。 $ objPHPExcel-> getActiveSheet（） - > rangeToArray（'A1：A4'）

其余的问题基本上都是PHP数组操作

EDIT

PS。

编辑2

p>

使用最新的SVN代码来利用rangeToArray（）方法：

  $ objWorksheet = $ objPHPExcel-> setActiveSheetIndex（0）; 
 $ highestRow = $ objWorksheet-> getHighestRow（）; 
 $ highestColumn = $ objWorksheet-> getHighestColumn（）; 

 $ headingsArray = $ objWorksheet-> rangeToArray（'A1：'。$ highestColumn.'1'，null，true，true，true）; 
 $ headingsArray = $ headingsArray [1]; 

 $ r = -1; 
 $ namedDataArray = array（）; 
 for（$ row = 2; $ row< = $ highestRow; ++ $ row）{
 $ dataRow = $ objWorksheet-> rangeToArray（'A'。$ row。'：'。 $ highestColumn。$ row，null，true，true，true）; 
 if（（isset（$ dataRow [$ row] ['A']））&&（$ dataRow [$ row] ['A']>））{
 + + $ r; 
 foreach（$ headingsArray as $ columnKey => $ columnHeading）{
 $ namedDataArray [$ r] [$ columnHeading] = $ dataRow [$ row] [$ columnKey]; 
} 
} 
} 

 echo'< pre>'; 
 var_dump（$ namedDataArray）; 
 echo'< / pre>< hr />'; 
 

I m using PHPExcel to read data from Excel sheet and store in mysql table, till now i m able to upload .xls as well as .xlsx file and after uploading the xls i got below table structure of data

name    start_date              end_date               city
one 11/25/2011 3:30:00 PM   11/29/2011 4:40:00 AM   Jaipur
two 10/22/2011 5:30:00 PM   10/25/2011 6:30:00 AM   Kota
three  3/10/2011 2:30:00 PM 3/11/2011 12:30:00 AM   Bikaner
                                                    chandigarh

now i have some problems, please suggest me the optimized method

1. how do we get the sheet name ( bcoz in one excel there are 7 sheets )

2. for now what i do to store these data into db, below is the code snippet

   $inputFileName = "test.xls";  
   $inputFileType = PHPExcel_IOFactory::identify($inputFileName);  
   $objReader = PHPExcel_IOFactory::createReader($inputFileType);  
   $objReader->setReadDataOnly(true);  
   /**  Load $inputFileName to a PHPExcel Object  **/  
   $objPHPExcel = $objReader->load($inputFileName);  
   $total_sheets=$objPHPExcel->getSheetCount(); // here 4  
   $allSheetName=$objPHPExcel->getSheetNames(); // array ([0]=>'student',[1]=>'teacher',[2]=>'school',[3]=>'college')  
   $objWorksheet = $objPHPExcel->setActiveSheetIndex(0); // first sheet  
   $highestRow = $objWorksheet->getHighestRow(); // here 5  
   $highestColumn = $objWorksheet->getHighestColumn(); // here 'E'  
   $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);  // here 5  
   for ($row = 1; $row <= $highestRow; ++$row) {  
       for ($col = 0; $col <= $highestColumnIndex; ++$col) {  
       $value=$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();  
           if(is_array($arr_data) ) { $arr_data[$row-1][$col]=$value; }  
       }  
   }
   print_r($arr_data);
   

and this returns

Array
(
[0] => Array
    (
        [0] => name
        [1] => start_date
        [2] => end_date
        [3] => city
        [4] =>         
    )

[1] => Array
    (
        [0] => one  
        [1] => 40568.645833333
        [2] => 40570.5
        [3] => Jaipur
        [4] => 
    )
[2] => Array
    (
        [0] => two 
        [1] => 40568.645833333
        [2] => 40570.5
        [3] => Kota
        [4] => 
    )
[3] => Array
    (
        [0] => three
        [1] => 40568.645833333
        [2] => 40570.5
        [3] => Bikaner
        [4] => 
     )
  [4] => Array
    (
        [0] => 
        [1] => 
        [2] => 
        [3] => Chandigarh
        [4] => 
    )

)

i need that

• header of each excel sheet (i.e. first row) become key of array($arr_data) and
• rest become the value of array.
• time changed into some integer value, that shoud be same as in excel sheet
• blank field ( i.e.blank header column ) of array in all row should be truncated (here [4] )
• if first field of an excel sheet (or combined condition on some fields) is not fulfilled then that row should not be added into array

i.e. desired array should look like

Array  
    (  
    [0] => Array  
    (  
      [name] => one  
      [start_date] => 11/25/2011 3:30:00 PM  
      [end_date] => 11/29/2011 4:40:00 AM  
      [city] => Jaipur  
    )  
    [1] => Array  
    (  
      [name] => two  
      [start_date] => 10/22/2011 5:30:00 PM  
      [end_date] => 10/25/2011 6:30:00 AM  
      [city] => Kota  
    )  
    [2] => Array  
    (  
      [name] => three  
      [start_date] => 3/10/2011 2:30:00 PM  
      [end_date] => 3/11/2011 12:30:00 AM  
      [city] => Bikaner  
    )  
)

and after that i store data into my db using mysql action on desired array.

• is there any other short method to store above data in db

Note: Please do not refer manual( it is really really very bad )... just tell me the methods name..

update

@Mark Thanks for your solution, it helps me a lot, but still some problems are there

• how to handle empty/blank cell in excel sheet..bcoz when any cell are empty then it display a notice

Notice: Undefined index: C in C:\xampp\htdocs\xls_reader\Tests\excel2007.php on line 60
Notice: Undefined index: D in C:\xampp\htdocs\xls_reader\Tests\excel2007.php on line 60

whereas line 60 is

foreach($headingsArray as $columnKey => $columnHeading) { 
    $namedDataArray[$r][$columnHeading] = $dataRow[$row]$columnKey];
}

• how to set conditions before retrieving array of complete data i.e. if i want that if first and second column are empty/blank in any row then that row should not be added in our desired array

thanks

how do we get the sheet name ( bcoz in one excel there are 7 sheets )?

To get the current active sheet:

$sheetName = $objPHPExcel->getActiveSheet()->getTitle();

time changed into some integer value, that shoud be same as in excel sheet

Look at PHPExcel_Shared_Date::ExcelToPHP($excelDate) or PHPExcel_Shared_Date::ExcelToPHPObject($excelDate) to convert the date/time values to a PHP timestamp or DateTime object

Have a look at the $objPHPExcel->getActiveSheet()->toArray() method rather than looping through all the rwos and columns yourself. If you want to use toArray with the formatted argument though, don't use $objReader->setReadDataOnly(true); otherwise PHPExcel can't distinguish between a number and a date/time. The latest SVN code has added a rangeToArray() method to the worksheet object, which allows you to read a row (or block of cells) at a time, e.g. $objPHPExcel->getActiveSheet()->rangeToArray('A1:A4')

The rest of your questions are basically PHP array manipulation

EDIT

PS. Instead of just telling us that the manual is really really very bad... tell us how we can improve it.

EDIT 2

Using the latest SVN code to take advantage of the rangeToArray() method:

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

$headingsArray = $objWorksheet->rangeToArray('A1:'.$highestColumn.'1',null, true, true, true);
$headingsArray = $headingsArray[1];

$r = -1;
$namedDataArray = array();
for ($row = 2; $row <= $highestRow; ++$row) {
    $dataRow = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row,null, true, true, true);
    if ((isset($dataRow[$row]['A'])) && ($dataRow[$row]['A'] > '')) {
        ++$r;
        foreach($headingsArray as $columnKey => $columnHeading) {
            $namedDataArray[$r][$columnHeading] = $dataRow[$row][$columnKey];
        }
    }
}

echo '<pre>';
var_dump($namedDataArray);
echo '</pre><hr />';