Конвертация многостраничного 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;

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

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