PHPExcel 阅读器——需要帮助

2021-12-24 00:00:00 file-upload database php phpexcel

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

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. 我们如何获得工作表名称(bcoz 在一个 excel 中有 7 个工作表)

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

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

然后返回

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] => 
    )

)

我需要那个

  • 每个excel表的标题(即第一行)成为数组的键($arr_data)和
  • rest 成为数组的值.
  • 时间变成了一些整数值，应该和excel表中的一样
  • 应截断所有行中数组的空白字段(即空白标题列)(此处 [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  
    )  
)

然后我在所需数组上使用 mysql 操作将数据存储到我的数据库中.

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

  • 是否还有其他简短的方法可以将上述数据存储在 db 中

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

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

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

注意:未定义索引:C in C:xampphtdocsxls_readerTestsexcel2007.php在线 60
注意:未定义索引:D in C:xampphtdocsxls_readerTestsexcel2007.php在线60

Notice: Undefined index: C in C:xampphtdocsxls_readerTestsexcel2007.php on line 60
Notice: Undefined index: D in C:xampphtdocsxls_readerTestsexcel2007.php on line 60

而第 60 行是 

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

  • 如何在检索完整数据数组之前设置条件，即如果我希望第一列和第二列在任何行中为空/空白，则不应将该行添加到我们想要的数组中

    • 谢谢

    推荐答案

    我们如何获得工作表名称( bcoz in一个excel有7张)?

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

    <块引用>

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

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

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

    查看 $objPHPExcel->getActiveSheet()->toArray() 方法，而不是自己遍历所有 rwos 和列.但是，如果您想将 toArray 与格式化参数一起使用，请不要使用 $objReader->setReadDataOnly(true);否则 PHPExcel 无法区分数字和日期/时间.最新的 SVN 代码在工作表对象中添加了 rangeToArray() 方法，该方法允许您一次读取一行(或单元格块)，例如$objPHPExcel->getActiveSheet()->rangeToArray('A1:A4')

    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

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

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

    编辑

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

    EDIT

    编辑 2

    使用最新的SVN代码利用rangeToArray()方法:

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

相关文章