Простой способ передачи табличных данных из PHP в Excel

К написанию данного топика меня подтолкнула одна заметка о генерации xls в PHP.
Способ, представленный в той заметке действительно очень прост, но не всегда может быть удобен.
Есть множество других способов передать табличные данные из PHP в Excel, я опишу тот, который показался мне наиболее простым и функциональным. Нужно особенно отметить, что что я не говорю о генерации xls файла, а лишь предлагаю пользователю открыть полученные данные при помощи Excel так, что пользователи не искушённые в программировании не заметят подлога.

Итак, первое что необходимо сделать — разместить на нашей страничке ссылку на скрипт генерирующий следующие заголовки:
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);
header("Content-Type: application/x-msexcel");
header("Content-Disposition: attachment; filename=\"" . iconv('UTF-8', 'CP1251', $object->getFileName()) . "\";");
header("Content-Transfer-Encoding:­ binary");
header("Content-Length: " . $object->getFileSize());

$object — сферический объект в вакууме, который каждый читатель реализует так, как ему больше нравится. Назначение геттеров getFileName() и getFileSize() понятно из названий. Здесь стоит выделить один неочевидный нюанс (спасибо savostin за то что напомнил об этом) — getFileName() конечно может возвращать любое имя файла, но если вы хотите что бы браузер предложил открыть полученный контент в Excel, то расширение файла должно быть xls.
Ничего нового я пока не рассказал, все это придумано до меня, впрочем как и то что будет описано ниже.
Как верно было отмечено в комментариях к заметке о генерации xls в PHP, Excel гораздо быстрее работает с XML. Но самое главное преимущество, пожалуй, все же не в скорости, а в гораздо более широких возможностях. Углубляться в дебри я не стану, а лишь приведу простой пример, и ссылку на подробное описание всех тегов.

Итак, после того как заголовки сгенерированны, нам нужно отдать пользователю собственно данные. Я обычно заворачиваю генерацию таблицы в отдельный метод:
echo $object->getContent();

А таблицу генерирую при помощи Smarty:
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
	<Styles>
  		<Style ss:ID="bold">
			<Font ss:Bold="1"/>
		</Style>
 	</Styles> 
	<Worksheet ss:Name="WorksheetName">
		<Table>
			<Row>
				{foreach from=$data.header item=caption}
					<Cell ss:StyleID="bold"><Data ss:Type="String">{$caption.columnName}</Data></Cell>
				{/foreach}
			</Row> 
			{foreach from=$data.content item=row}	
				<Row>
					{foreach from=$row item=col}
						<Cell><Data ss:Type="String">{$col}</Data></Cell>
					{/foreach}
				</Row>
			{/foreach}
		</Table>
	</Worksheet>
</Workbook>

Как видно из кода, в шаблон передается массив $data содержащий два массива — строку заголовка таблицы и собственно данные.
Стоит отметить что использовать шаблонизатор только для генерации XML несколько накладно, и получить XML можно множеством других способов. В моем конкретном случае генерация XML это лишь маленькая плюшка в большом проекте, где без шаблонизатора не обойтись.

В качестве примера я привел простейшую таблицу, при желании можно манипулировать гораздо большим числом атрибутов. Это особенно приятно, если учесть, что для реализации не требуются никакие сторонние библиотеки.
Описанный метод несколько лет работает на одном проекте и ни один пользователь до сих пор не заподозрил, что открываемые им данные не являются документом MS Office.

Подробнее о структуре XML используемой в MS Excel можно почитать в MSDN XML Spreadsheet Reference
Поделиться публикацией

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

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

    +2
    Думаю, уместно было бы выложить пример сгенерированного таким образом файла. Я лично сейчас использую PHPExcel, хоть он и прожорливый.
      0
      К сожалению прямо сейчас примера под рукой нет. Завтра сделаю обязательно.
      PHPExcel не пробовал, т.к. с лихвой хватает описанного выше метода. Сейчас обдумывается обратное направление движения информации — из Excel в браузер.
        0
        Я правильно понял, что в «обратном направлении» речь идёт о подключении библиотек для разбора бинарника xls?

        Если так — было бы очень интересно, т.к. скоро будет стоять похожая задача, которую пока не читал как решать, а простым решением на ум приходит экспорт из Excel в XML, что не красиво.
          0
          Этот вопрос тоже достаточно интересен, но сейчас меня больше интересует непосредственно копирование/вставка диапазона ячеек из листа Excel на страницу браузера (то есть некоторый аналог Google docs). Правда это имеет мало отношения к PHP.
            0
            Понятно.
            Я кстати посмотрел описание указанного выше PHPExcel.
            Похоже он умеет разбирать бинарники.
              0
              умеет и неплохо.
                0
                В виду его прожорливости есть ограничение на размер файла который ему можно скормить… Даже на 10 метровом эксель файле при разборе потребляет почти 500 метров памяти.
                А ведь эксельки бываю куда больше.
                Лучшее что я нашел — это перловый xlscat. Но понятное его можно использовать только на собственном сервер.
                  0
                  На собственном сервере прекрасно работает xls2csv из пакета catdoc
                  www.wagner.pp.ru/~vitus/software/catdoc/

                  Пользую его уже не один год для автоматизированной обработки поступающих прайсов в xls. Для xlsx и odt парсеры пишутся за день на любом языке по выбору — там простой разбор XML
        0
        прожорливый — мягко сказано.
        0
        Если я не ошибаюсь, расширение у этого файла должно быть xml, верно?
        Как бы теперь заставить на компьютере непосвященного открывать этот файл в Excel?
          0
          Этот нюанс я упустил. Поправлю топик вечером. Достаточно в заголовках указать расширение xls. Excel отлично работает с такими файлами.
            0
            У меня почему-то выдает предупреждение, что файл поврежден (хоть и открывает после этого).
            Пример был бы полезен.
              0
              Недавно тоже экспериментировал с таким способом. Файл с расширением xml в некоторых версиях офиса открывается с предупреждением, а OpenOffice вообще не воспринимает его, как свой (по умолчанию открывается в ИЕ, но через «Открыть с помощью» нормально. Изменение расширения не спасает — OO ругается).
                0
                xml как раз открывается нормально, без предупреждений. Вот только настраивать Excel как программу по умолчанию для xml, мне кажется не стоит. Я говорил о том, что файл, сохраненный с расширением xls с контентом xml, открывается в Excel с предупреждением, что файл поврежден.
                  0
                  Предупреждение действительно появляется. Оно гласит что расширение не соотвествует контенту, и необходимо убедиться что файл не поврежден и получен из достоверного источника. Если файл назвать *.xml то предупреждения не будет, но может случиться так что файл будет открыт не в Excel.

                  Файл приготовил, только пока не нашел как его прицепить к топику…
                    +2
                    Т.е. варианта с «нормальным» расширением (не xml) и без предупреждения нет?
                    0
                    Вот пример текста файла:
                    <?xml version="1.0"?>
                    <?mso-application progid="Excel.Sheet"?>
                    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
                        <Styles>
                            <Style ss:ID="bold">
                                <Font ss:Bold="1"/>
                            </Style>
                        </Styles>
                        <Worksheet ss:Name="manual">
                            <Table>
                                <Row>
                                    <Cell ss:StyleID="bold"><Data ss:Type="String">h1</Data></Cell>
                                    <Cell ss:StyleID="bold"><Data ss:Type="String">h2</Data></Cell>
                                    <Cell ss:StyleID="bold"><Data ss:Type="String">h3</Data></Cell>
                                    <Cell ss:StyleID="bold"><Data ss:Type="String">h4</Data></Cell>
                                    <Cell ss:StyleID="bold"><Data ss:Type="String">h5</Data></Cell>
                                </Row>
                                <Row>
                                    <Cell><Data ss:Type="String">data11</Data></Cell>
                                    <Cell><Data ss:Type="String">data12</Data></Cell>
                                    <Cell><Data ss:Type="String">data13</Data></Cell>
                                    <Cell><Data ss:Type="String">data14</Data></Cell>
                                    <Cell><Data ss:Type="String">data15</Data></Cell>
                                </Row>
                                <Row>
                                    <Cell><Data ss:Type="String">data22</Data></Cell>
                                    <Cell><Data ss:Type="String">data23</Data></Cell>
                                    <Cell><Data ss:Type="String">data21</Data></Cell>
                                    <Cell><Data ss:Type="String">data24</Data></Cell>
                                    <Cell><Data ss:Type="String">data25</Data></Cell>
                                </Row>
                                <Row>
                                    <Cell><Data ss:Type="String">data31</Data></Cell>
                                    <Cell><Data ss:Type="String">data32</Data></Cell>
                                    <Cell><Data ss:Type="String">data33</Data></Cell>
                                    <Cell><Data ss:Type="String">data34</Data></Cell>
                                    <Cell><Data ss:Type="String">data35</Data></Cell>
                                </Row>
                                <Row>
                                    <Cell><Data ss:Type="String">data41</Data></Cell>
                                    <Cell><Data ss:Type="String">data42</Data></Cell>
                                    <Cell><Data ss:Type="String">data43</Data></Cell>
                                    <Cell><Data ss:Type="String">data44</Data></Cell>
                                    <Cell><Data ss:Type="String">data45</Data></Cell>
                                </Row>
                            </Table>
                        </Worksheet>
                    </Workbook>
                    


                    Таким методом я генерировал файлы размером более 2 мегабайт и более 30 000 строк.
            0
            Давно решал задачу генерации файла на сервере и столкнулся с немаловажной особенностью поведения excel. У нас шла генерация шаблонов, в которые юзеры заносили данные и загружали обратно на сервер, у пользователей никаких проблем не было, однако серврер ожидали сюрпризы т.к. на сервер приходил не бинарный формат xls. Цепочка выглядела так: сервер генерит xml или html, передает правильные заголовки Content-Type: application/x-msexcel и Content-Disposition: attachment; filename=что-то.xls, пользователь замечательным образом открывает все в excel, правит, сохраняет, далее грузит файл обратно, а вот серверу приходится определять, что же к нему приехало т.к. в зависимости от настроек у пользователя, версии офиса и способа сохранения к нам прилетали файлы с расширением xls, а содержимое могло быть как бинарными данными, так и xml и даже html.
            Самый надежный способ — изначально генерить бинарный xls и отправлять его пользователю, тогда обратно всегда будет загружаться бинарник и можно будет с ним работать.
            Если файлы отправляются пользователю и не предполагается их обратная загрузка, то генерация xml/html тут подходит.
            На всякий случай: для генерации бинарника используем CSharpJExcel, для чтения — ADO.NET т.к. работаю на платформе .NET.
              0
              Может это Вам люди из старого (2003) оффиса высылают или высылают старый формат?
              0
              А можно таким способом вставить картинки в таблицу?
                0
                Нашел на это дело референс: msdn.microsoft.com/en-us/library/aa140066(v=office.10).aspx
                Картинки, походу, нельзя. Но вот данные выгружать за милую душу. Следующий шаг после CSV.
                  0
                  Спасибо за ссылку, она с самого начала была приведена в конце топика :)
                    0
                    Сорри, не заметил. Просто, раз возник вопрос…
                    Может назвать ее как MSDN XML Spreadsheet Reference?
                0
                Генерирую обычной HTML табличкой экспорт в эксель.
                Просто и работает, в т.ч. стили.
                  0
                  А можно подробности?
                    0
                    Я так понимаю, имеется ввиду обычный html с подменёнными заголовками (этот способ упомянут в топике, кстати).
                      0
                      Да, спасибо. Примерно это и имел ввиду. Только в статье по ссылке автор несколько усложнил все на мой взгляд.
                  • НЛО прилетело и опубликовало эту надпись здесь
                    +1
                    Специально сделал у себя выгрузку объектов и коллекций в XML. Достаточно создать пару подходящих экшенов (в контексте большинства фреймворков). Теперь эту выгрузку преобразовываю при помощи XSL в подобный формат. Пользователям просто предлагаю открыть этот файл в экселе принудительно. Все довольны. Данные выгружаются. XSLTProcessor в PHP работает достаточно быстро и на больших объемах данных. Гораздо шустрее PHPExcel. И, скорее всего, шустрее Смарти.
                      0
                      Думаю устраивать холивар по поводу того какой способ быстрее — бессмысленно, но все же отмечу: у меня файл размером более 30 000 строк генерируется без каких либо задержек (так же быстро как и файл). Думаю здесь довольно большое значение будет иметь скорость работы БД.
                      +2
                      Если нужно без стилей и без картинок— а просто данные, то лучше .csv ещё ничего наверное не придумали. Открывается всеми известными мне табличными процессорами сразу, без всяких «файл— импорт».
                        0
                          +1
                          > header(«Content-Disposition: attachment; filename=\»". iconv('UTF-8', 'CP1251', $object->getFileName()). "\";");

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

                          Если мне не изменяет память, в поле filename заголовка Content-Disposition стандарт разрешает использовать только ASCII, и разные браузеры требуют разные способы кодирования не-ASCII (в т.ч. русские буквы) символов: кто-то через url percent encoding, кто-то позволяет использовать сами буквы в кодировке utf-8.

                          Потому, вы должны либо распознавать user-agent, и подсовывать ему нужную версию названия, либо использовать только ASCII, либо (самое правильное решение) не ставить поле filename, а задавать имя файла с помощью URL: example.com/download-xls/Прайс-лист+компании+Рога+и+Копыта.xls
                            0
                            Кто-то знает, как на PHP без использования фреймворков работать с Google Docs? Редатирование и чтение.
                              0
                              А кто занимался подобным для формата odt? Я пока выгружаю в cvs, но надоедает формат файла постоянно корректировать. Хотелось бы иметь полноценную шапку и рамочки.
                                0
                                Для разбора xls пользуюсь классом excel_reader2.
                                Все безумно просто и понятно.
                                На практике проверялись файлы в несколько мегабайт и с количеством строк до 30000.
                                  +2
                                  Если нужен только текст, то можно генерировать CSV файл, который открывается Excel

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

                                Самое читаемое