В моих проектах часто нужно было собирать данные из разных источников в CSV формат, и пока не нужно было получать данные из нескольких страниц таблиц xls, мне хватало простого fgetcsv() / fputcsv(). Но вот наступил всё-таки тот день, когда передо мной была поставленна задача «получать данные со всех страниц документа». И, как водится, я начал искать готовое решение, чтобы не строить свой «велосипед». Но, к сожалению, именно того, что мне нужно было, я не нашёл: было похожее решение, которое выводило многостраничный документ на экран, но использовалась другая библиотека, которая, как я понял, не поддерживала формат xslx (Excel 2007 +). Ещё немного поискав другие варианты, я понял, что дело гиблое и решил разобраться с библиотекой самостоятельно. Совместив некоторые подсказки по работе с библиотекой PHPExcel в одно целое, я получил следующий скрипт. Итак, приступим.
Для начала нам понадобится сам PHPExcel. Сразу отмечу, что библиотека отлично ставится через composer, однако нигде не указана явно полная версия библиотеки. Методом подбора я указал версию 1.8 с добавлением признака «неточности».
В моём composer.json, которую я добавил в блок «require-dev»:{}, получилась вот такая запись:
На данный момент установилась версия 1.8.1. Так как библиотека PHPExcel наследует SPL, который есть в PHP, начиная с версии 5.3, то вместо стандартных обходов масива строк и ячеек документа при помощи foreach() я решил использовать Итераторы.
Подключаем библиотеку, загружаем документ и определяем некоторые первоначальные данные:
Далее получаем Итератор страниц, для обхода которых нам ненужно знать их количество:
Обход итератора производится при помощи вот такой несложной конструкции:
Аналогичным образом были получены и обходятся строки и ячейки документа. Как красиво получить данные из ячейки, я нашёл здесь же, на Хабре, в статье Универсальное чтение ячеек в PHPExcel. Я не буду подробно расписывать все проверки, у кого есть желание — можете прочитать в указанной статье.
Запись в CSV файл, я произвожу то же через эту библиотеку вот таким образом.
Наполнение объекта для записи в файл я покажу ниже в полном варианте скрипта. Единственное, что могу добавить: если вам нужно будет записывать даты заново в документы xls/xlsx и указать явное форматирование, то при подготовке объекта PHPExcel можно воспользоваться следующей конструкцией:
Где формат задаётся при помощи константы PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2. В данном случае это формат yyyy-mm-dd, который можно сразу использовать в запросе MySql.
Кстати, вот все доступные константы библиотеки:
В итоге у меня получился скрипт который пишет каждую страницу документа в отдельный csv-файл, при этом получает правильные значения ячеек независимо от наличия внешних данных и форматирует дату в подходящем формате.
Вот он полностью:
На этапе тестирования все значения выводятся на экран, далее кому не нужно естественно вывод можно убрать.
Критика, дополнения и исправления приветствуются. Всем спасибо, и буду очень рад если моя статья кому-то поможет и сократит несколько часов работы.
Для начала нам понадобится сам 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;
На этапе тестирования все значения выводятся на экран, далее кому не нужно естественно вывод можно убрать.
Критика, дополнения и исправления приветствуются. Всем спасибо, и буду очень рад если моя статья кому-то поможет и сократит несколько часов работы.