php PHPExcel 阅读器——需要帮助

PHPExcel reader -- help required

提问by diEcho

I m using PHPExcelto 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

我正在使用PHPExcel从 Excel 工作表中读取数据并存储在 mysql 表中，直到现在我能够上传 .xls 和 .xlsx 文件，在上传 xls 后我得到了下面的数据表结构

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

1. 我们如何获得工作表名称（bcoz 在一个 excel 中有 7 个工作表）

2. 现在我如何将这些数据存储到数据库中，下面是代码片段

   $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

• 每个excel表的标题（即第一行）成为数组的键（$arr_data）和
• 其余成为数组的值。
• 时间变成了一些整数值，应该和excel表中的一样
• 应截断所有行中数组的空白字段（ieblank 标题列）（此处 [4]）
• 如果不满足 Excel 工作表的第一个字段（或某些字段的组合条件），则不应将该行添加到数组中

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.

之后，我使用所需数组上的 mysql 操作将数据存储到我的数据库中。

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

• 有没有其他简短的方法可以将上述数据存储在 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

@Mark 感谢您的解决方案，它对我有很大帮助，但仍然存在一些问题

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

• 如何处理Excel工作表中的空/空白单元格..bcoz当任何单元格为空时，它会显示一条通知

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

注意：未定义索引：C in C:\xampp\htdocs\xls_reader\Tests\excel2007.php on line 60
注意：Undefined index：D in C:\xampp\htdocs\xls_reader\Tests\excel2007.php on line 60

whereas line 60 is

而第 60 行是

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

谢谢

回答by Mark Baker

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

我们如何获得工作表名称（bcoz 在一个 excel 中有 7 张工作表）？

To get the current active sheet:

要获取当前活动工作表：

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

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

时间变成了一些整数值，应该和excel表中的一样

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

查看 PHPExcel_Shared_Date::ExcelToPHP($excelDate) 或 PHPExcel_Shared_Date::ExcelToPHPObject($excelDate) 将日期/时间值转换为 PHP 时间戳或 DateTime 对象

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')

看看 $objPHPExcel->getActiveSheet()->toArray() 方法，而不是自己遍历所有 rwos 和列。如果你想使用带有格式化参数的 toArray，不要使用 $objReader->setReadDataOnly(true); 否则 PHPExcel 无法区分数字和日期/时间。最新的SVN代码在worksheet对象中增加了rangeToArray()方法，可以一次读取一行（或一块单元格），例如$objPHPExcel->getActiveSheet()->rangeToArray('A1:A4' )

The rest of your questions are basically PHP array manipulation

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

EDIT

编辑

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

附注。而不是仅仅告诉我们手册真的非常糟糕......告诉我们我们如何改进它。

EDIT 2

编辑 2

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

使用最新的 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 />';