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

    В продолжение темы, начатой в предыдущей статье, хочу поделиться своим опытом экспорта данных, в частности, в формате 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 совершенно безвозмездно.
    Поделиться публикацией

    Похожие публикации

    Комментарии 15

      +1
      1. К сожалению, код протестировать без артефактов на диске не получится.
      2. У Excel немного больше форматов, чем «строка», «число», «дата». Например, форматированные проценты, дроби с конфигурируемым количеством знаков после запятой.
      3. Форматирование ячейки (не путать с форматом!) тут не учитывается тоже.

      П.С. Я так понимаю, автор делал сей код для простенького упаковщика, но с таким же успехом можно было использовать fputcsv(...)

      П.П.С. Я нисколько не умаляю заслуг ТС! Написал и поделился — молодец! Даёшь качественные пулл-реквесты!
        0
        1. Разместил файл template.xlsx на Gitub. Можете им воспользоваться.
        2. Верно-верно. Но публикация ведь про мой опыт, а мне нужны были только указанные мной форматы.

        Однако, нет ничего проще использовать и другие форматы. Попробую описать по пунктам:
        a. Открыть template.xlsx
        b. Ввести в какую-нибудь ячейку тестовое значение и отформатировать его так, как вам нужно
        c. Стереть введенное значение (можно не стирать, но ...)
        d. Сохранить template.xlsx
        e. Переименовать xlsx в xip.
        f. Распаковать.
        g. Найти файл /xl/styles.xml.
        h. Открыть его в каком-нибудь приложении для просмотра, например, в Internet Explorer.
        i. Найти тэг cellXfs, в нем и будут содержаться все стили, включая новый.
        Рекомендую проделать эти операции на копии template.xlsx, чтобы в п.i сравнить исходный styles.xml и конечный styles.xml. Обращаю внимание еще и на тот факт, что ссылка на стиль осуществляется по порядковому номеру стиля.

        3. Именно так, ведь простой экспорт подразумевает экспорт именно значений, а не форматов. Хотя, справедливости ради, укажу на тот факт, что в функции appendCellString учитывается установка флага isBold, который позволяет сделать строковые значения жирным шрифтом.

        Про fputcsv знаю, но значения сохраненные в csv формате при открытии в Excel рискуют быть искаженными. Уж очень любит Microsoft Excel «догадываться» какой формат надо установить для значения и превращает все подряд в даты. Кроме этого, обычному современному пользователю csv формат неизвестен, ну или вызывает замешательство и депрессию. В общем, такой простенький экспорт в формате xlsx оправдан тем, что сокращает количество обращений пользователей с вопросом: «Что делать с CSV?»
        Кстати, про команду «Данные» — «Из текста» в Excel знает тоже не каждый пользователь.

        Смысл публикации именно в том, чтобы поделиться опытом и позволить другим программистам, коллегам, не ломать голову при решении подобных задач. Написание класса для экспорта в XLSX у меня отняло примерно пару дней. Сперва разбирался с шаблоном. Потом со строками и датами. Потом оформлял в класс. Если кто-то другой прочитав эту публикацию сэкономит пару дней я буду только рад!
          0
          Спасибо за статью. Как раз в ближайшую неделю-две придется ковыряться с XLS на тему подмены значений.
            0
            C xls нужно будет по-другому как-то работать.
              0
              сорри, опечатался, имел в виду как раз XLSX
            0
            Это круто. Действительно, библиотеки, которые умеют все, очень толстые и неповоротливые. Простое решение, которое включает в себя только то что надо, мне кажется, в этом случае просто идеально подходи.
              0
              Deaddy указал на то, что в предыдущем сообщений Вы написали XLS, а не XLSX. Если это не опечатка, то надо понимать, что это разные форматы. То, что подходит для XLSX не подойдет для XLS.
                0
                Вы, видимо, что-то напутали =). Я не автор предыдущего сообщения про XLS.
                  0
                  Ой, простите. Моя вина.
              0
              В одном из проектов пользовался github.com/mk-j/PHP_XLSXWriter
                0
                Вариант интересный конечно, но есть же PHPExcel — phpexcel.codeplex.com/
                Удобно, красиво и без придумывания велосипеда
                  0
                  На больших файлах он съест всю память.
                  0
                  Если в таблице больше 25 столбцов, то формируется не валидный файл.
                  public function appendCellNum($value)
                  {
                      $this->curCel++;
                      $this->currentRow[] = '<c r="'.chr(64+$this->curCel).$this->numRows.'"><v>'.$value.'</v></c>';
                  }
                  

                  chr(64+$this->curCel)
                  вернет символы, которые не входят в алфавит и их уже нельзя использовать в нумерации ячеек. Например, если столбцов 26, то будет использован символ [.
                  Ну и в заголовке sheet1.xml это тоже используется:
                  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>');
                  
                    0
                    Верно подмечено!
                    Решаемо:

                    function numToLetter($num)
                    {
                    	$f = '';
                    	do 
                    	{
                    		$f = chr(64 + $num % 26) . $f;
                    		$num = floor($num / 26);
                    	}
                    	while ($num > 0);
                    	return $f;
                    }
                    
                    echo '<br/>letters: ',numToLetter(1905); //return BUG
                    


                    Спасибо за замечание.
                    0
                    Жаль, что решение не автономно и есть связь с Yii.

                    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.