Pull to refresh

Простой экспорт в Excel XLSX

Reading time 5 min
Views 44K
В продолжение темы, начатой в предыдущей статье, хочу поделиться своим опытом экспорта данных, в частности, в формате XLSX.



Итак, кому интересно, как заполнить XLSX без больших и сложных библиотек, прошу под кат.

Недавно передо мной возникла задача экспортировать непредсказуемый по размеру объем табличных данных в формате XLSX. Как любой здравомыслящий программист, первым делом полез искать готовые решения.
Почти сразу наткнулся на библиотеку PHPExcel. Мощное решение, с кучей разных функций и возможностей. Порывшись еще немного нашел отзывы программистов о ней. В частности, на форумах встречаются жалобы на скорость работы и отказ работать с большим объемом данных. Отметил библиотеку как один из вариантов решения и начал искать дальше.
Находил еще несколько библиотек для работы с XLSX, но все они были или забытыми, т.к. не обновлялись по 2-3 года, или обязательно тянули за собой сторонние библиотеки, или использовали DOM для работы с файлами, что мне не очень нравилось. Каждый раз, натыкаясь на очередную библиотеку и изучая механизмы ее работы, ловил себя на мысли, что все это «из пушки по воробьям». Не нужно мне такое сложное решение!
Признаюсь честно, изучив поверхностно каждое из найденных решений, не стал ставить и тестировать ни одного. Мне нужно было более простое и надежное, как танк, решение.

Задача


В общем, раз не нашел ничего подходящего, значит надо сформулировать технические требования к тому, что нужно. Требования, как и следовало ожидать, оказались тривиальными:
  • Оформить экспортирующий механизм в виде автономного класса
  • Реализовать в классе набор функций для записи значений ячеек и ряда
  • Возможность работы с неограниченным объемом данных
  • Распаковка и упаковка XLSX.

Отдельно остановлюсь только на последнем пункте. Как известно, XLSX представляет собой обычный zip-архив, который можно распаковать и увидеть, что он состоит из нескольких файлов и каталогов. Обратным образом его можно упаковать и переименовать в XLSX. Если все изменения правильные, то Microsoft Excel откроет файл без проблем.

Реализация


Изначально очень хотел создавать все файлы, из которых состоит XLSX, кодом, но, к счастью, быстро понял бессмысленность своей идеи. И родилось иное, более правильно и простое решение. Надо с помощью Microsoft Excel создать файл XLSX в таком виде, в каком он нужен в итоге, но без данных, иными словами — шаблон, а потом, с помощью кода, только добавить данные!
В таком случае, класс должен будет распаковывать шаблон в отдельный каталог, вносить изменения в /xl/worksheets/sheet1.xml и упаковывать содержимое каталога обратно в XLSX.

В объявлении класса присутствуют публичные переменные:
$templateFile – имя файла шаблона
$exportDir – папка, в которую будет распакован шаблон, разумеется с необходимыми правами доступа.

Конструктор класса принимает имя будущего файла, количество колонок и рядов. Потом проверяет, что имя файла корректно, папка для распаковки шаблона существует и формирует полное имя конечной папки для распаковки шаблона.
После создания класса можно распаковать шаблон и открыть на запись sheet1.xml. На самом деле я не просто дописываю в файл, а полностью его перезаписываю. Однажды взяв из него начальную строку, вношу в нее изменение в тэге dimension, который отражает размер экспортируемого диапазона, и записываю в файл.

public function openWriter()
{
	if (is_dir($this->baseDir))
		CFileHelper::removeDirectory($this->baseDir);
	mkdir($this->baseDir);

	exec("unzip $this->templateFullFilename -d \"$this->baseDir\"");

	$this->workSheetHandler = fopen($this->baseDir.'/xl/worksheets/sheet1.xml', 'w+');

	fwrite($this->workSheetHandler, '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><dimension ref="A1:'.chr(64+$this->colCount).$this->rowCount.'"/><sheetData>');
}


Обеспечить скорость работы и возможность работы с большим объемом данных позволяют функции resetRow и flushRow. Они отвечают за очистку текущего ряда в памяти и запись текущего ряда на диск.
А вот сохранение значений ячеек с разными типами оказалось не такой простой задачей.

Запись строки

Казалось бы, что сложного записать строковое значение в файл. Однако, в XLSX все не так просто. Все строки внутри XLSX хранятся в отдельном файле /xl/sharedStrings.xml. В ячейки записываются не строковые значения, а их порядковые номера — индексы. Разумное решение с точки зрения сокращения размера файла.

Но такое решение неудобно с точки зрения программного заполнения шаблона. Если выполнять указанное требование, то мне бы пришлось выполнять отдельный проход по всем строковым значениям в массиве данных, исключать повторяющиеся, сохранять их в sharedStrings.xml, проиндексировать и вместо значений в исходном массиве вписать их индексы. Медленно и неудобно.

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

public function appendCellString($value)
{
	$this->curCel++;
	if (!empty($value)) {
		$value = htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
		$value = preg_replace( '/[\x00-\x13]/', '', $value );
		$this->currentRow[] = '<c r="'.chr(64+$this->curCel).$this->numRows.'" t="inlineStr"'.($this->isBold ? ' s="7"' : '').'><is><t>'.$value.'</t></is></c>';
		$this->numStrings++;
	}
}


Запись числа

Никаких сложностей с записью целых или дробных чисел не возникло. Все просто:

public function appendCellNum($value)
{
	$this->curCel++;
	$this->currentRow[] = '<c r="'.chr(64+$this->curCel).$this->numRows.'"><v>'.$value.'</v></c>';
}


Запись даты и времени

Дата и время хранятся в виде количества секунд прошедших с 01.01.1970 поделенных на количество секунд в сутках. Причем, в вычислении допущена ошибка с определением високосного года. В общем, не вдаваясь в подробности, которые несложно найти в сети, чтобы корректно вычислять дату пришлось объявить в классе две константы:
ZERO_TIMESTAMP – смещение даты в формате Excel от UNIX_TIMESTAMP
SEC_IN_DAY – секунд в сутках.
После вычисления значения даты и времени, целая часть дроби – это дата, дробная часть – время:

const ZERO_TIMESTAMP = 2209161600;
const SEC_IN_DAY = 86400;

public function appendCellDateTime($value)
{
	$this->curCel++;

	if (empty($value))
		$this->appendCellString('');
	else
	{
		$dt = new DateTime($value);
		$ts = $dt->getTimestamp() + self::ZERO_TIMESTAMP;
		$this->currentRow[] = '<c r="'.chr(64+$this->curCel).$this->numRows.'" s="1"><v>'.$ts/self::SEC_IN_DAY.'</v></c>';
	}
}

После записи всех данных остается закрыть рабочий лист и рабочую книгу.

Применение


Как и раньше, использование описанного класса основано на экспорте данных с помощью провайдера CArrayDataProvider. Предполагая, что объем экспортируемых данных может оказаться очень большим, применен специальный итератор CDataProviderIterator, который перебирает возвращаемые данные по 100 записей (можно указать иное число записей).

public function exportXLSX($organization, $user, &$filename)
{
	$this->_provider = new CArrayDataProvider(/*query*/);

	Yii::import('ext.AlxdExportXLSX.AlxdExportXLSX');
	$export = new AlxdExportXLSX($filename, count($this->_attributes), $this->_provider->getTotalItemCount() + 1);

	$export->openWriter();
	$export->resetRow();
	$export->openRow(true);
	foreach ($this->_attributes as $code => $format)
		$export->appendCellString($this->_objectref->getAttributeLabel($code));
	$export->closeRow();
	$export->flushRow();

	$rows = new CDataProviderIterator($this->_provider, 100);
	foreach ($rows as $row)
	{
		$export->resetRow();
		$export->openRow();

		foreach ($this->_attributes as $code => $format)
		{
			switch ($format->type)
            {
                case 'Num':
                    $export->appendCellNum($row[$code]);
                /*other types*/
                default:
                    $export->appendCellString('');					
            }
		}

		$export->closeRow();
		$export->flushRow();
	}
	$export->closeWriter();
	$export->zip();

	$filename = $export->getZipFullFileName();
}

Кому интересно, может получить исходный код моего класса AlxdExportXLSX совершенно безвозмездно.
Tags:
Hubs:
+11
Comments 17
Comments Comments 17

Articles