При разработке системы электронного документооборота потребовалось реализовать функции для экспорта данных в популярных форматах. В частности, в формате Microsoft Excel. Требования к экспорту были довольно простые – экспортировать данные с минимумом форматирования, т.е. никаких объединенных ячеек, игр со шрифтами и т.п. Форматы экспорта XLSX и Excel XML.

В данном случае расскажу про Excel XML.

Итак, в любой системе оперирующей табличными данными раной или поздно возникает потребность экспорта данных. Цели экспорта разные:

передача данных в табличном виде
анализ данных в соотв. системах
построение диаграмм
построение сводных таблиц и т.п.

Исходя из целей, напросился разумный вывод, что при экспорте данных надо постараться сохранить или указать тип данных в соответствующих колонках, чтобы приложение, которое открывает файл не пыталось применить форматирование по своему усмотрению. Говоря иначе, даты должны быть датами, числа – числами, строки – строками.

Задача

Технические требования сформулировал тривиально:

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

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

Реализовать в классе набор функций для записи значений ячеек и ряда – основное требование, подразумевающее создание функций для записи значений ячеек указанных типов и возможность записи готового ряда в файл.

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

Помимо описанных требований, понадобилось добавить сервисные функции:

Включение автофильтра
Сжатия файла в zip.

Реализация

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

public function writeDocumentProperties($organization = null, $user = null)
{
	fwrite($this->file, '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">');
	if (!is_null($user))
	{
		fwrite($this->file, '<Author>'.$user->description.'</Author>');
		fwrite($this->file, '<LastAuthor>'.$user->description.'</LastAuthor>');
	}

	$dt = new Datetime();
	$dt_string = $dt->format('Y-m-d\TH:i:s\Z');
	fwrite($this->file, '<Created>'.$dt_string.'</Created>');
	fwrite($this->file, '<LastSaved>'.$dt_string.'</LastSaved>');

	if (!is_null($organization))
		fwrite($this->file, '<Company>'.$organization->name.'</Company>');

	fwrite($this->file, '<Version>12.00</Version>');
	fwrite($this->file, '</DocumentProperties>');
}

Правда, именно в этой функции используются сущности системы документооборота — organization (организация) и user (пользователь). Заменить эти сущности на, скажем, строковые значения, не проблема.

Наиболее интересной в заголовке является информация о стилях. В формате Excel XML они реализованы очень удобно, поэтому просто создаю таблицу со стилями для строк, даты/времени и гиперссылки.

public function writeStyles()
{
	fwrite($this->file, '<Styles>');
	//default style
	fwrite($this->file, '<Style ss:ID="Default" ss:Name="Normal"><Font ss:Color="#000000"/></Style>');
	//Datetime style
	fwrite($this->file, '<Style ss:ID="DateTime"><NumberFormat ss:Format="General Date"/></Style>');
	fwrite($this->file, '<Style ss:ID="Date"><NumberFormat ss:Format="Short Date"/></Style>');
	fwrite($this->file, '<Style ss:ID="Time"><NumberFormat ss:Format="h:mm:ss"/></Style>');
	//Hyperlink style
	fwrite($this->file, '<Style ss:ID="Hyperlink" ss:Name="Hyperlink"><Font ss:Color="#0000FF" ss:Underline="Single"/></Style>');
	//Bold
	fwrite($this->file, '<Style ss:ID="Bold"><Font ss:Bold="1"/></Style>');
	fwrite($this->file, '</Styles>');
}

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

public function openWorksheet()
{
	fwrite($this->file, '<Worksheet ss:Name="Export">');
	fwrite($this->file, strtr('<Table ss:ExpandedColumnCount="{col_count}" ss:ExpandedRowCount="{row_count}" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">', array('{col_count}'=>$this->colCount, '{row_count}'=>$this->rowCount)));
}

Но вот запись рядов – процесс поинтереснее. Класс должен работать быстро и обрабатывать неограниченный объем данных, ведь записей может быть тысяч сто или даже миллион! Хочешь скорости – работай с памятью, хочешь неограниченный объем данных – работай с диском. Чтобы помирить требования, реализовал функции resetRow и flushRow.
Первая – очищает текущий ряд, после чего его снова можно наполнять данными, а вторая – записывает текущий ряд в открытый файл на диск. Их совместное использование позволяет выдерживать баланс между скоростью и объемом используемой памяти.

public function resetRow()
{
	$this->currentRow = array();
}

public function flushRow()
{
	fwrite($this->file, implode('', $this->currentRow));
	unset($this->currentRow);
}

Каждая ячейка записывается функцией соответствующей типу данных, а именно appendCellxxx, где xxx – тип данных. Допустимые типы данных: Num, String, Real, DateTime, Date, Time, Link. Пример функции для записи числового значения:

public function appendCellNum($value)
{
	$this->currentRow[] = '<Cell><Data ss:Type="Number">'.$value.'</Data></Cell>';
}

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

Применение

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

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

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

	$export->openWriter();
	$export->openWorkbook();

	$export->writeDocumentProperties($organization, $user);
	$export->writeStyles();
	$export->openWorksheet();

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

	//data rows
	$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();
	}

	//close all
	$export->closeWorksheet();
	$export->closeWorkbook();
	$export->closeWriter();
	
	//zip file
	$export->zip();

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

В моем случае, каждый ряд записывается на диск, что, пока, является вполне приемлемым, но в будущем, возможно, потребует изменений. Например, будет разумно сохранять не каждый ряд, а каждый десяток или даже сотню рядов за раз. Тогда скорость экспорта увеличится.

Скорость

Кстати, на собственном опыте убедился, как важно предполагать возможность существования больших объемов данных при пакетной операции, такой как экспорт.
Изначально, пытался осуществлять экспорт данных используя CActiveDataProvider, что требовало при экспорте 1000 записей порядка 240 секунд! Изменив запрос так, чтобы использовать CArrayDataProvider сократил время экспорта 1000 записей до 0.5 секунды!
Специально для этой публикации замерил показатели экспорта.
Экспортировал 1626 записей с 9 атрибутами, представляющих собой информацию о закрытых инцидентах (см. ITSM).

Исходный вид экспортируемой таблицы

(извините, картинка исчезает после публикации)

Результат

(извините, картинка исчезает после публикации)

Показатели экспорта

Объем конечного файла: 1 312 269
Объем сжатого файла: 141 762
Затраченное время: примерно 0.5 сек

Кому интересно, может получить исходный код моего класса AlxdExportExcelXML безвозмездно. Только надо не забыть поправить функцию writeDocumentProperties, чтобы отвязаться от сущностей системы документооборота organization и user, или использовать свои аналогичные сущности с соответствующими свойствами.

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