Конвертация многостраничного xls/xslx в csv при помощи PHPExcel

В моих проектах часто нужно было собирать данные из разных источников в CSV формат, и пока не нужно было получать данные из нескольких страниц таблиц xls, мне хватало простого fgetcsv() / fputcsv(). Но вот наступил всё-таки тот день, когда передо мной была поставленна задача «получать данные со всех страниц документа». И, как водится, я начал искать готовое решение, чтобы не строить свой «велосипед». Но, к сожалению, именно того, что мне нужно было, я не нашёл: было похожее решение, которое выводило многостраничный документ на экран, но использовалась другая библиотека, которая, как я понял, не поддерживала формат xslx (Excel 2007 +). Ещё немного поискав другие варианты, я понял, что дело гиблое и решил разобраться с библиотекой самостоятельно. Совместив некоторые подсказки по работе с библиотекой PHPExcel в одно целое, я получил следующий скрипт. Итак, приступим.

Для начала нам понадобится сам PHPExcel. Сразу отмечу, что библиотека отлично ставится через composer, однако нигде не указана явно полная версия библиотеки. Методом подбора я указал версию 1.8 с добавлением признака «неточности».

В моём composer.json, которую я добавил в блок «require-dev»:{}, получилась вот такая запись:

"require-dev": {
"phpoffice/phpexcel": "~1.8"
},


На данный момент установилась версия 1.8.1. Так как библиотека PHPExcel наследует SPL, который есть в PHP, начиная с версии 5.3, то вместо стандартных обходов масива строк и ячеек документа при помощи foreach() я решил использовать Итераторы.

Подключаем библиотеку, загружаем документ и определяем некоторые первоначальные данные:

/** Include PHPExcel */
include_once '../Classes/PHPExcel.php';

$callStartTime = microtime(true);
$tmpFileName = microtime(true);
$format = 'Y-m-d';
// Load PHPExcel object
$objPHPExcel =  PHPExcel_IOFactory::load('multipage.xls');

Далее получаем Итератор страниц, для обхода которых нам ненужно знать их количество:

$sheetsIterator = $objPHPExcel->getWorksheetIterator();

Обход итератора производится при помощи вот такой несложной конструкции:

while( $sheetsIterator->valid()) {
    $pageNumber = $sheetsIterator->key();
    $pageContent = $sheetsIterator->current();
    
    $sheetsIterator->next();

Аналогичным образом были получены и обходятся строки и ячейки документа. Как красиво получить данные из ячейки, я нашёл здесь же, на Хабре, в статье Универсальное чтение ячеек в PHPExcel. Я не буду подробно расписывать все проверки, у кого есть желание — можете прочитать в указанной статье.

Запись в CSV файл, я произвожу то же через эту библиотеку вот таким образом.

// Create new object to write converted data and separate documents sheets
    $csvPagePhpExcel = new PHPExcel();

    // HERE Add Data to Object 

    // Creating CSV writer Object and save data to file
    $objWriter = PHPExcel_IOFactory::createWriter($csvPagePhpExcel, 'CSV');
    $objWriter->save($currentTmpFileName);

Наполнение объекта для записи в файл я покажу ниже в полном варианте скрипта. Единственное, что могу добавить: если вам нужно будет записывать даты заново в документы xls/xlsx и указать явное форматирование, то при подготовке объекта PHPExcel можно воспользоваться следующей конструкцией:

            if ($isDate) {
                $csvPagePhpExcel->getActiveSheet()->getStyle($cellIterator->key().$rowIterator->key())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
                $csvPagePhpExcel->getActiveSheet()->setCellValue($cellIterator->key().$rowIterator->key(), $cellValue);
            } else {
                $csvPagePhpExcel->getActiveSheet()->setCellValue($cellIterator->key().$rowIterator->key(), $cellValue);
            }

Где формат задаётся при помощи константы PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2. В данном случае это формат yyyy-mm-dd, который можно сразу использовать в запросе MySql.

Кстати, вот все доступные константы библиотеки:

/* Pre-defined formats */
    const FORMAT_GENERAL                 = 'General';
 
    const FORMAT_TEXT                    = '@';
 
    const FORMAT_NUMBER                  = '0';
    const FORMAT_NUMBER_00               = '0.00';
    const FORMAT_NUMBER_COMMA_SEPARATED1 = '#,##0.00';
    const FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-';
 
    const FORMAT_PERCENTAGE              = '0%';
    const FORMAT_PERCENTAGE_00           = '0.00%';
 
    const FORMAT_DATE_YYYYMMDD2          = 'yyyy-mm-dd';
    const FORMAT_DATE_YYYYMMDD           = 'yy-mm-dd';
    const FORMAT_DATE_DDMMYYYY           = 'dd/mm/yy';
    const FORMAT_DATE_DMYSLASH           = 'd/m/y';
    const FORMAT_DATE_DMYMINUS           = 'd-m-y';
    const FORMAT_DATE_DMMINUS            = 'd-m';
    const FORMAT_DATE_MYMINUS            = 'm-y';
    const FORMAT_DATE_XLSX14             = 'mm-dd-yy';
    const FORMAT_DATE_XLSX15             = 'd-mmm-yy';
    const FORMAT_DATE_XLSX16             = 'd-mmm';
    const FORMAT_DATE_XLSX17             = 'mmm-yy';
    const FORMAT_DATE_XLSX22             = 'm/d/yy h:mm';
    const FORMAT_DATE_DATETIME           = 'd/m/y h:mm';
    const FORMAT_DATE_TIME1              = 'h:mm AM/PM';
    const FORMAT_DATE_TIME2              = 'h:mm:ss AM/PM';
    const FORMAT_DATE_TIME3              = 'h:mm';
    const FORMAT_DATE_TIME4              = 'h:mm:ss';
    const FORMAT_DATE_TIME5              = 'mm:ss';
    const FORMAT_DATE_TIME6              = 'h:mm:ss';
    const FORMAT_DATE_TIME7              = 'i:s.S';
    const FORMAT_DATE_TIME8              = 'h:mm:ss;@';
    const FORMAT_DATE_YYYYMMDDSLASH      = 'yy/mm/dd;@';
 
    const FORMAT_CURRENCY_USD_SIMPLE     = '"$"#,##0.00_-';
    const FORMAT_CURRENCY_USD            = '$#,##0_-';
    const FORMAT_CURRENCY_EUR_SIMPLE     = '[$EUR ]#,##0.00_-';

В итоге у меня получился скрипт который пишет каждую страницу документа в отдельный csv-файл, при этом получает правильные значения ячеек независимо от наличия внешних данных и форматирует дату в подходящем формате.

Вот он полностью:

<?php
/** Include PHPExcel */
include_once '../Classes/PHPExcel.php';
 
$callStartTime = microtime(true);
// Load PHPExcel object
$objPHPExcel =  PHPExcel_IOFactory::load('multipage.xls');
 
// Get all document sheets
$sheetsIterator = $objPHPExcel->getWorksheetIterator();
 
$tmpFileName = microtime(true);
// Date format ready to import in SQL database
$format = 'Y-m-d';
 
while( $sheetsIterator->valid()) {
    $currentTmpFileName = "/tmp/{$tmpFileName}_sheet_{$sheetsIterator->key()}.csv";
    echo $sheetsIterator->key() . '<hr />';
// Get current sheet rows
    $rowIterator = $sheetsIterator->current()->getRowIterator();
 
    // Create new object to write converted data and separate documents sheets
    $csvPagePhpExcel = new PHPExcel();
 
    while ($rowIterator->valid()) {
        // Get Cells from current Rows
        $cellIterator = $rowIterator->current()->getCellIterator();
        echo '<br />' . $rowIterator->key() .'-';
        while ($cellIterator->valid()) {
 
            $cellValue = $cellIterator->current()->getCalculatedValue();
            //check is date
            if(PHPExcel_Shared_Date::isDateTime($cellIterator->current())) {
                $cellValue = date($format, PHPExcel_Shared_Date::ExcelToPHP($cellValue));
            }
 
            //for incorrect formulas take old value
            if((substr($cellValue,0,1) === '=' ) && (strlen($cellValue) > 1)){
                $cellValue = $cellIterator->current()->getOldCalculatedValue();
            }
 
            $currentCellNum = PHPExcel_Cell::columnIndexFromString($cellIterator->key());
            echo $cellIterator->key() . '(' . $currentCellNum . ') => ' . $cellValue;
 
            $csvPagePhpExcel->getActiveSheet()->setCellValue($cellIterator->key().$rowIterator->key(), $cellValue);
            $cellIterator->next();
        }
        $rowIterator->next();
    }
    // Creating CSV writer Object and save data to file
    $objWriter = PHPExcel_IOFactory::createWriter($csvPagePhpExcel, 'CSV');
    $objWriter->save($currentTmpFileName);
 
    // clearing trash
    $csvPagePhpExcel->__destruct();
    unset($csvPagePhpExcel);
    $objWriter = '';
    unset($objWriter);
 
    $sheetsIterator->next();
}
 
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
echo $callTime;

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

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

Похожие публикации

Комментарии 11

    0
    Вы копируете значения ячейка-за-ячейкой, но можно проще — копировать лист целиком: gist.github.com/pqr/ad12493947e7b1e2910f/82db011d811606bdf109b4d6addeb25517ca5342
    //Берём лист из оригинального файла
    $sheet = $sheetsIterator->current();
    //"Перекладываем" его в PhpExcel объект, который потом будет сохранён в CSV
    $csvPagePhpExcel->addSheet($sheet, 0);
    

    В примечание к своему примеру скажу, что помимо метода addSheet() есть ещё addExternalSheet() — в общем случае более верным будет использовать именно addExternalSheet(), т.к. он копирует ещё и стили, которые хранятся не в объекте $sheet, а в самом workbook. Но для данной задачи вывода в CSV стили не важны, поэтому использование addSheet() будет быстрее и проще.

    Разница с вашим кодом есть: копируя листы целиком, я никак не могу задать форма вывода дат. Будет использован некий формат «по умолчанию», который может отличаться от файла к файлу. Я попробовал два файла из своих реальных таблиц, в одном из них был FORMAT_DATE_XLSX14, в другом FORMAT_DATE_YYYYMMDD2.
    Второе отличие: в вашем коде происходит хитрая проверка на ошибки в формулах (которые могут ссылаться на внешние книги), в моём варианте этих проверок нет.

    Провёл тест вашего кода и своего кода на большом файле из 170 листов и 9.5 Мб результирующего CSV текста:
    ваш вариант: 3221 сек
    мой вариант: 347 сек.

    p.s. пока писал комментарий, подумал, что перекладывать лист в промежуточный PhpExcel объект для записи в CSV вообще не обзательно, можно писать в CSV на лету из исходного PhpExcel объекта, двигая указатель текущего листа ($objWriter->setSheetIndex($sheetsIterator->key())): gist.github.com/pqr/ad12493947e7b1e2910f/1698b405fb511b4fc625a95139d3c23c3a21bd5d
    отработал за 321 сек.

    Наконец, не плохо было бы установить флаг $objReader->setReadDataOnly(true): gist.github.com/pqr/ad12493947e7b1e2910f/dd57ff58a2f7952e5beae491d95b3c8bc378dd66
    отработал за 152 сек.

      0
      Согласен, можно получить итератор страницы и сразу сохранить его в нужном формате, но в данном случае мне критически важно было форматирование дат, и передача всех данных в новый документ. При чём шаблоны входящих документов могут быть разными и при этом мне не обязательно знать заранее в какой колонке будут находиться даты, а в какой простой текст.

      К чему приводит установка флага setReadDataOnly(true), то есть какую ещё пользу он несёт кроме запрета изменения данных?
        0
        setReadDataOnly(true) — это не запрет на изменение, это команда для ридера «прочитай из исходного файла только данные без стилей» — ускоряет чтение, уменьшает потребление памяти. О нём, кстати, написано в той статье Универсальное чтение ячеек в PHPExcel
          0
          Понятно, спасибо, но в данном случае этот режим мне тоже не подходит так-как мы не сможем получить правильно дату.
      +1
      А какой смысл кусков кода из манула, если Вы принципиально нового ничего не придумали и не показали? Т.е. это все есть в мануале и никаких фич здесь нет.
        +2
        Смысл в том что это уже готовое решение, для експорта в Excel есть куча мануалов — каких только хочешь, а для данного процесса нигде ничего нет. Я сам неоднократно был близок к переходу на PHPExcel но из-за необязательности получения данных НЕ из первой страницы документа просто нехотел переходить на эту библиотеку только из-за того что она была для меня новая и я не знал на сколько просто с ней работать пока сам не разобрался. И из-за этого лепил велосипеды через fputcsv которые иногда были не очень то «ровными». А сколько людей до сих пор не могут начать делать то же самое пока не увидят подходящий вариант.
        Ещё про смысл
        А какой смысл кусков кода из манула, если Вы принципиально нового ничего не придумали и не показали? Т.е. это все есть в мануале и никаких фич здесь нет.

        А какой смысл выкладывать здесь какие-то решения если всё можно выучить самостоятельно по мануалам, давайте всех новичков тыкать в мануалы как это делают на огромном количестве форумов. Вместо того что бы ответить на конкретно поставленный вопрос или хотя бы указать направление в котором нужно «копать» — каждый считает своим долгом сначала «оскорбить» вопрошающего, а через нескользо страниц гадостей возможно дать ответ. Я считаю что не нужно уподобаться им и судя по количеству людей которые добавили мою публикацию в избранное — всё таки смысл в ней есть.
        0
        Методом подбора я указал версию 1.8 с добавлением признака «неточности».

        Элементарно, Ватсон:
        https://packagist.org/packages/phpoffice/phpexcel

        Кстати, замеры производительности этой библиотеки делали?
          0
          Спасибо за ссылку на packagist
          По поводу замеров — кое что можно прочитать здесь Реализация быстрого импорта из Excel на PHP как я понимаю это не саммая быстрая либа, но из бесплатных только она может работать с xlsx форматом, поправьте меня если я не прав.
            0
            Это хорошая либа, но она достаточно быстро деградирует по производительности при росте количества элементов на листе / количества листов.

            Быстро и полностью совместимо с Excel — только так:

            new COM("Excel.Application", NULL, CP_UTF8);
            


            На Windows-сервере, конечно же.
          0
          Есть неплохая библиотека, которая может составить конкуренцию PHPExcel в разборе файлов: github.com/box/spout
          На простых (и больших) файлах работает отлично. На чем-то сложном проверить не пришлось. Если у кого-нибудь появится опыт — прошу поделиться.
            0
            Спасибо, По возможности протестирую, синтаксис очень похож на PHPExcel правда смущает что в инструкции к либе есть итераторы но они обходятся через foreach (правда это не запрещено, но всё же)

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

          Самое читаемое