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

在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 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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 关于#r语言#的问题:(svydesign)为什么在一个大的数据集中抽取了一个小数据集
  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格