Простий експорт в Excel XML

    При розробці системи електронного документообігу було потрібно реалізувати функції для експорту даних в популярних форматах. Зокрема, в форматі Microsoft Excel. Вимоги до експорту були досить прості — експортувати дані з мінімумом форматування, тобто ніяких об'єднаних осередків, ігор зі шрифтами і т.п. Формати експорту XLSX і Excel XML.
 
 
 
В даному випадку розповім про Excel XML .
 
Отже, в будь-якій системі що оперує табличними даними раною чи пізно виникає потреба експорту даних. Цілі експорту різні:
 
     
  • передача даних в табличному вигляді
  •  
  • аналіз даних в соотв. системах
  •  
  • побудова діаграм
  •  
  • побудова зведених таблиць і т.п.
  •  
Виходячи з цілей, напросився розумний висновок, що при експорті даних треба постаратися зберегти або вказати тип даних у відповідних колонках, щоб програма, яка відкриває файл не намагалося застосувати форматування за своїм розсудом. Говорячи інакше, дати повинні бути датами, числа — числами, рядка — рядками.
 
 

Задача

Технічні вимоги сформулював тривіально:
 
     
  • Оформити експортуючий механізм у вигляді автономного класу
  •  
  • Реалізувати в класі набір функцій для запису значень осередків і ряду
  •  
  • Можливість роботи з необмеженим обсягом даних.
  •  
Оформити експортуючий механізм у вигляді автономного класу — очевидна вимога, виконання якого дозволить в майбутньому поділитися класом з іншими розробниками і використовувати його в нових проектах.
 
Реалізувати в класі набір функцій для запису значень осередків і ряду — основна вимога, що припускає створення функцій для запису значень осередків зазначених типів і можливість запису готового ряду в файл.
 
Можливість роботи з необмеженим обсягом даних — зрозуміло, сам клас експорту відповідати за записуваний обсяг не зможе, але він повинен надати функції для запису даних на диск та звільнення оперативної пам'яті для наступної порції даних.
 
Крім описаних вимог, знадобилося додати сервісні функції:
  
 

Реалізація

Перш за все, при створенні класу здійснюю перевірку кінцевого імені файлу і запитує кількість колонок і рядів. Файл повинен мати коректне ім'я, а папка, в яку він буде збережений, повинна існувати. Все як завжди.
Формат 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 ).
 
 
Вихідний вид експортованої таблиці
Як виглядає вихідна таблиця:
 
 
 
Результат
Як виглядає результат експорту:
 
 
 
Показники експорту
Обсяг кінцевого файлу: 1312269
Обсяг стисненого файлу: 141762
Витрачений час: приблизно 0.5 сек
 
Кому цікаво, може отримати вихідний код мого класу AlxdExportExcelXML безоплатно. Тільки треба не забути поправити функцію writeDocumentProperties , щоб відв'язатися від сутностей системи документообігу organization і user, або використовувати свої аналогічні сутності з відповідними властивостями.
    
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.