Оптимизация setCellValueExplicit() в PHPExcel

Я имею дело с 700 строками данных в своем excel.

И я добавляю в столбец эту запись:

foreach($data as $k => $v){
   $users ->getCell('A'.$k)->setValue($v['Username']);
   $users->setCellValueExplicit('B'.$k, 
      '=INDEX(\'Feed\'!H2:H'.$lastRow.',MATCH(A'.$k.',\'Feed\'!G2:G'.$lastRow.',0))',
      PHPExcel_Cell_DataType::TYPE_FORMULA);
}

$users обозначает электронную таблицу.

Я вижу, что запись 700 ячеек с указанным выше setCellValueExplicit() занимает более 2 минут для обработки. Если я опускаю эту строку, то той же машине требуется 4 секунды для ее обработки.

2 минуты могут быть в порядке, но что, если у меня 2000 ячеек. Есть ли способ оптимизировать скорость?

ps: =VLOOKUP такая же медленная, как и вышеприведенная функция.


Обновить

Вся идея скрипта: прочитать файл CSV (13 столбцов и не менее 100 строк), записать его в электронную таблицу, создать новую таблицу ($users), прочитать два столбца, отсортировать их по одному столбцу и записать в $users таблица.

Прочитайте столбцы:

$data = array();
for ($i = 1; $i <= $lastRow; $i++) {
    $user = $Feed ->getCell('G'.$i)->getValue();
    $number = $Feed ->getCell('H'.$i)->getValue();
    $row = array('User' => $user, 'Number' => $number);
    array_push($data, $row);
}

Сортировать данные

function cmpb($a,$b){
    //get which string is less or 0 if both are the same
    if($a['Number']>$b['Number']){
        $cmpb = -1;
    }elseif($a['Number']<$b['Number']){
        $cmpb = 1;
    }else{
        $cmpb = 0;
    }
    //if the strings are the same, check name
    if($cmpb == 0){
        //compare the name
        $cmpb = strcasecmp($a['User'], $b['User']);
    }
    return $cmpb;
}
usort($data, 'cmpb');

Запись данных

foreach($data as $k => $v){
   $users ->getCell('A'.$k)->setValue($v['Username']);
   $users ->getCell("B{$k}")->setValueExplicit("=INDEX('Feed'!H2:H{$lastRow},MATCH(A{$k},'Feed'!G2:G{$lastRow},0))",
 PHPExcel_Cell_DataType::TYPE_FORMULA);
}

а также сброс данных для памяти:

unset($data);

Так что, если закомментировать строку с setValueExplicit, все станет ровнее.


person Diolor    schedule 09.05.2013    source источник
comment
Значение, которое вы устанавливаете для setCellValueExplicit, немного нечитаемо из-за всех этих конкатенаций и экранирования в одинарных кавычках. Вы можете заменить его на "=INDEX('Feed'!H2:H{$lastRow},MATCH(A{$k},'Feed'!G2:G{$lastRow},0))".   -  person Rolando Isidoro    schedule 09.05.2013
comment
@RolandoIsidoro К сожалению, я не могу сказать, что заметил улучшение производительности.   -  person Diolor    schedule 09.05.2013
comment
Вот почему я добавил как комментарий, а не как ответ. Я просто просматривал исходный код PHPExcel и не могу найти причину описанного вами поведения. Я добавлю ответ с некоторыми соображениями, потому что он не подходит для комментариев. Дай мне знать, что ты думаешь об этом.   -  person Rolando Isidoro    schedule 09.05.2013
comment
Нет никакой логической причины, по которой setCellValueExplicit() должен быть значительно медленнее, чем getCell()->setValue(), но вы можете попробовать getCell()->setValueExplicit(), как предложил Роландо.   -  person Mark Baker    schedule 10.05.2013
comment
P.S. - Прошу прощения за написание необоснованного кода, не стесняйтесь смотреть альтернативы. Я не буду заставлять вас использовать PHPExcel   -  person Mark Baker    schedule 10.05.2013
comment
@MarkBaker PHPExcel великолепен и удобен в использовании. Для меня это хорошая головоломка, чтобы научиться оптимизировать мой код. Я просто пытаюсь понять, почему это происходит. Я запущу тот же скрипт на лучшем сервере   -  person Diolor    schedule 10.05.2013


Ответы (1)


Глядя на исходный код PHPExcel, это функция PHPExcel_Worksheet::setCellValueExplicit:

public function setCellValueExplicitByColumnAndRow($pColumn = 0, $pRow = 1, $pValue = null, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING)
{
    return $this->getCell(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow)->setValueExplicit($pValue, $pDataType);
}

Для используемого вами типа данных, PHPExcel_Cell_DataType::TYPE_FORMULA, функция PHPExcel_Cell::setValueExplicit просто выполняется:

case PHPExcel_Cell_DataType::TYPE_FORMULA:
    $this->_value = (string)$pValue;
    break;

Я не могу найти логического объяснения старому при выполнении этой конкретной инструкции. Попробуйте заменить его следующим и дайте мне знать, есть ли какие-либо улучшения:

$users ->getCell("B{$k}")->setValueExplicit("=INDEX('Feed'!H2:H{$lastRow},MATCH(A{$k},'Feed'!G2:G{$lastRow},0))", PHPExcel_Cell_DataType::TYPE_FORMULA);

В качестве последнего ресурса я бы посоветовал отслеживать время выполнения инструкции, чтобы найти узкое место.

person Rolando Isidoro    schedule 09.05.2013
comment
Спасибо за ваш ответ. Я попробовал, и время сократилось с 2 минут до одной минуты. Я все же боюсь, что это много, хотя и совершенно неразумно. Я тоже запускаю скрипт 2-3 раза и получаю Allowed memory size of 134217728 bytes exhausted. 128 мегабайт памяти... Приветствуются любые идеи, спасибо - person Diolor; 10.05.2013
comment
Если вы можете вставить набор $data, с которым вы работаете, где-нибудь, может быть, я мог бы попытаться ускорить его. - person Rolando Isidoro; 10.05.2013
comment
Если у вас также возникают проблемы с памятью, вам нужно либо увеличить объем памяти, либо использовать кэширование ячеек; но последнее повлияет на скорость - person Mark Baker; 10.05.2013
comment
Обновлено. $data — это многомерный массив 2x700. - person Diolor; 10.05.2013