duancui19840401
duancui19840401
2015-01-10 13:28
浏览 122
已采纳

在Symfony2中使用PHPExcel读取Excel文件,编辑一个单元格的内容,然后保存到数据库

I've built a system where the client uploads data to it using a spreadsheet. It was using CSV's but the data contained within them mean that the upload wasn't too reliable. So instead I'm going to use PHPExcel and the PHPExcel bundle for Symfony2 to read the uploaded Excel spreadsheet and insert this data to the database.

I have two problems with using PHPExcel. The first is that there will be a column in the uploaded file that contains a date in a UK format (DD/MM/YYYY). However, obviously, MySQL uses an American format (YYYY-MM-DD). So what I'd like to do is format that one particular cell to use the YYYY-MM-DD format instead of DD/MM/YYYY.

The second issue I have is that I'd like to then insert all of this data in to the MySQL database in the most efficient way possible. I'm not sure exactly how best to way to do this. I know I will have memory issues with PHPExcel when using huge spreadsheets, so I want to make sure that saving to the database is the most efficient as possible.

So far, as I've been concentrating on reading the Excel file, this is my code:

        $objPHPExcel = $this->get('phpexcel')->createPHPExcelObject($dir.$fileName);

        $objPHPExcel->setActiveSheetIndex(0);
        $row = $objPHPExcel->getActiveSheet()->getHighestRow()+1;

        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
                echo 'Worksheet - ' , $worksheet->getTitle();
                echo '<table>';
                foreach ($worksheet->getRowIterator() as $row) {
                        //echo '<trRow number - ' , $row->getRowIndex() , EOL;
                        echo '<tr>';

                        $cellIterator = $row->getCellIterator();
                        $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
                        foreach ($cellIterator as $cell) {
                                if (!is_null($cell)) {
                                        echo '<td>'.$cell->getFormattedValue().'</td>';
                                }
                        }
                        echo '<tr>';
                }
                echo '</table>';
        }

When trying to format the individual cell, I then changed my code to this:

        $objPHPExcel = $this->get('phpexcel')->createPHPExcelObject($dir.$fileName);
        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
                echo 'Worksheet - ' , $worksheet->getTitle();
                echo '<table>';
                foreach ($worksheet->getRowIterator() as $row) {
                        //echo '<trRow number - ' , $row->getRowIndex() , EOL;
                        echo '<tr>';

                        $cellIterator = $row->getCellIterator();
                        $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
                        foreach ($cellIterator as $cell) {
                                if (!is_null($cell)) {
                                        echo '<td>'.$cell[0]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[1]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[2]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[3]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[4]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[5]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[6]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[7]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[8]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[9]->getFormattedValue().'</td>';
                                        echo '<td>'.date('Y-m-d', strtotime($cell[10]->getFormattedValue())).'</td>';
                                        echo '<td>'.$cell[11]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[12]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[13]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[14]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[15]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[16]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[17]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[18]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[19]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[20]->getFormattedValue().'</td>';
                                        echo '<td>'.$cell[21]->getFormattedValue().'</td>';
                                }
                        }
                        echo '<tr>';
                }
                echo '</table>';
        }

But this gave the following error in the Apache2 log:

PHP Fatal error: Cannot use object of type PHPExcel_Cell as array

Any help would be appreciated.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douzachan4578
    douzachan4578 2015-01-10 13:48
    已采纳

    Yes, $cell is not an array, it's an object, so using $cell[0] is invalid

    Replace the whole block of code:

    foreach ($cellIterator as $cell) {
        if (!is_null($cell)) {
            echo '<td>'.$cell[0]->getFormattedValue().'</td>';
            echo '<td>'.$cell[1]->getFormattedValue().'</td>';
            echo '<td>'.$cell[2]->getFormattedValue().'</td>';
            echo '<td>'.$cell[3]->getFormattedValue().'</td>';
            echo '<td>'.$cell[4]->getFormattedValue().'</td>';
            echo '<td>'.$cell[5]->getFormattedValue().'</td>';
            echo '<td>'.$cell[6]->getFormattedValue().'</td>';
            echo '<td>'.$cell[7]->getFormattedValue().'</td>';
            echo '<td>'.$cell[8]->getFormattedValue().'</td>';
            echo '<td>'.$cell[9]->getFormattedValue().'</td>';
            echo '<td>'.date('Y-m-d', strtotime($cell[10]->getFormattedValue())).'</td>';
            echo '<td>'.$cell[11]->getFormattedValue().'</td>';
            echo '<td>'.$cell[12]->getFormattedValue().'</td>';
            echo '<td>'.$cell[13]->getFormattedValue().'</td>';
            echo '<td>'.$cell[14]->getFormattedValue().'</td>';
            echo '<td>'.$cell[15]->getFormattedValue().'</td>';
            echo '<td>'.$cell[16]->getFormattedValue().'</td>';
            echo '<td>'.$cell[17]->getFormattedValue().'</td>';
            echo '<td>'.$cell[18]->getFormattedValue().'</td>';
            echo '<td>'.$cell[19]->getFormattedValue().'</td>';
            echo '<td>'.$cell[20]->getFormattedValue().'</td>';
            echo '<td>'.$cell[21]->getFormattedValue().'</td>';
        }
    }
    

    with

    foreach ($cellIterator as $cell) {
        if (!is_null($cell)) {
            if ($cell->getColumn() == 'K') {
                // The 10th column (column K) is a date that needs reformatting
                $cellValue = date('Y-m-d', strtotime($cell->getFormattedValue()));
            } else {
                 $cellValue = $cell->getFormattedValue();
            }
            echo '<td>'.$cellValue.'</td>';
        }
    }
    

    It would also be better to use PHPExcel's own date handling methods rather than relying on the formatted value of a date cell, so

    $cellValue = PHPExcel_Shared_Date::ExcelToPHPObject($cell->getValue())
        ->format('Y-m-d');
    

    would be cleaner and safer than

    $cellValue = date('Y-m-d', strtotime($cell->getFormattedValue()));
    

    Also, if it's likely that other columns might contain a date value, rather than checking for specific columns, replace

    if ($cell->getColumn() == 'K') {
        // The 10th column (column K) is a date that needs reformatting
    

    with

    if (PHPExcel_Shared_Date::isDateTime($cell)) {
        // Cell contains a date value that needs reformatting
    

    So the final result should look something like:

    foreach ($cellIterator as $cell) {
        if (!is_null($cell)) {
            if (PHPExcel_Shared_Date::isDateTime($cell)) {
                // Cell contains a date value that needs reformatting
                $cellValue = PHPExcel_Shared_Date::ExcelToPHPObject($cell->getValue())
                    ->format('Y-m-d');
            } else {
                 $cellValue = $cell->getFormattedValue();
            }
            echo '<td>'.$cellValue.'</td>';
        }
    }
    

    You might also want to consider eliminating the if (!is_null($cell)) test by setting

    $cellIterator->setIterateOnlyExistingCells(true); 
    

    As a final note, as you plan to insert all this row data into a MySQL database, build an array of each cell in the row as you iterate, and do your insert at the end of each row. However, as the formatted value of a numeric value might include thousands separators, currency codes, etc (e.g. $ 123,456.789), it'd probably a better idea to get the raw value (123456.789) using getValue() rather than getFormattedValue() for all non-date values, otherwise you may get errors with your SQL when trying to insert a formatted value with thousands separators into a FLOAT or INTEGER column in a database table

    点赞 评论

相关推荐