Разбираем xlsx в PHP без готовых библиотек

В первую очередь опишу проблему, которая заставила в тысячный раз вернуться к обсосанному со всех сторон вопросу: бестолковые менеджеры — без консультации с программистами — пообещали заказчику загрузку данных на сайт из xls(x).

Все бы ничего, но хостер заказчика дает 64мб памяти под выполнение и плевать он хотел на то, что у клиента Exсel файлы вообще без форматирования весят по 10-15мб, что при загрузке его PHPExel съедает (на тест-сервере) что-то около 500мб памяти.
Решение под катом с трудом дотянуло до 5мб.

Предусловия:
1. Имеется Exсel документ листов так в 10-20 с данными о товарах в интернет-каталоге. В каждом листе шапка — «название», «цена» и т.п. + воз доп. характеристик в 40 столбцов — и собственно данные в количестве «у-экселя-сантиметровый-скроллер»;
2. никакого CSV использовать нельзя. Все данные у заказчика уже в Exel и пересохранять их он не собирается… пообещали тут и все;
3. Spreadsheet_Excel_Writer откинут по причине неуниверсальности, хотя написано про него много хорошего. Жду комментариев по memory tests;
4. что удивительно, универсальных решений гугль не предложил. Неужели никто не сталкивался с такой проблемой на PHP *nix, удивился я.

Решение:
После перебора различных способов, вежливо предоставленных гуглом, решили почитать спецификации (эхх, учил меня отец...). Увидев там ключевые слова основан на Open XML и используется сжатие ZIP быстро позвонили заказчику и перевели разговор в русло xlsx и только: «Ну вы же понимаете! 21 век все-таки! Зачем нам хвататься за старое! Нужно одной ногой стоять в будущем!»

Далее алгоритм таков: принимаем файл, распаковываем его и внимательно смотрим на получившееся.
Полную инвентаризацию надо будет на досуге провести, а сейчас же нам наиболее интересно содержимое директории [xl], конкретно — /xl/worksheets/ и файл /xl/sharedStrings.xml.
В файле /xl/workbook.xml лежит описание листов, но т.к. задачи собрать названия листов не стояло, этот пункт я пропущу. При необходимости разобраться в нем не составит труда.

/xl/sharedStrings.xml

...
    <si>
        <t>Наименование</t>
    </si>
    <si>
        <t>Описание</t>
    </si>
    <si>
        <t>Изображение</t>
    </si>
    <si>
        <t>URL</t>
    </si>
    <si>
        <t>!Классификация</t>
    </si>
    <si>
        <t>!Бренд</t>
    </si>
    <si>
        <t>~1ф, 220-240 В, 50 Гц</t>
    </si>
...

и так далее в том же духе. Представляет собой текстовые данные в ячейках исходного документа. Со всех листов! Пока просто соберем эти данные в массив.
    $xml = simplexml_load_file(PATH . '/upload/xls_data/xl/sharedStrings.xml');
    $sharedStringsArr = array();
    foreach ($xml->children() as $item) {
        $sharedStringsArr[] = (string)$item->t;
    }


/xl/worksheets/
Это директория с файлами типа «sheet1.xml» с описанием данных листов. Конкретно в каждом файле нас интересует содержимое и его детей <row ...>.
...
<sheetData>
...
<row r="1" spans="1:43" ht="48.75" customHeight="1" x14ac:dyDescent="0.2">
            <c r="A1" s="1" t="s">
                <v>0</v>
            </c>
            <c r="B1" s="1" t="s">
                <v>1</v>
            </c>
            <c r="C1" s="2" t="s">
                <v>2</v>
            </c>
            <c r="E2" s="12">
                <v>2</v>
            </c>
            <c r="F2" s="12"/>
           ....
</row>
<row r="2" spans="1:43" ht="13.5" customHeight="1" x14ac:dyDescent="0.2">
...
</sheetData>
...

Методом сопоставлений и экспериментов было выяснено, что атрибут [t=«s»] у ячейки (судя по всему type=string) является указанием на то, что значение берем из файла sharedStrings.xml. Указатель — значение — номер элемента из $sharedStringsArr. Если не указан — берем само значение за значение ячейки.

Собираем:
    $handle = @opendir(PATH . '/upload/xls_data/xl/worksheets');
    $out = array();
    while ($file = @readdir($handle)) {
        //проходим по всем файлам из директории /xl/worksheets/
        if ($file != "." && $file != ".." && $file != '_rels') {
            $xml = simplexml_load_file(PATH . '/upload/xls_data/xl/worksheets/' . $file);
            //по каждой строке
            $row = 0;
            foreach ($xml->sheetData->row as $item) {
                $out[$file][$row] = array();
                //по каждой ячейке строки
                $cell = 0;
                foreach ($item as $child) {
                    $attr = $child->attributes();
                    $value = isset($child->v)? (string)$child->v:false;
                    $out[$file][$row][$cell] = isset($attr['t']) ? $sharedStringsArr[$value] : $value;
                    $cell++;
                }
                $row++;
            }
        }
    }
    var_dump($out);


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

Напоследок скажу, что толком в спецификации xlsx не разбирался, а только выполнил поставленную задачу с конкретными xlsx документами. Куда-то ведь должны писаться формулы и изображения (t=«i»?). Когда столкнусь с такой задачей — непременно опишу, а пока представляю нетребовательный к системе алгоритм для сбора текстовых данных из xslx. Надеюсь, будет востребован, т.к. в поисках подобного не встречал.

P.S. Только расставляя метки наткнулся на Работа с большими файлами экселя. Хабрить надо было, а не гуглить — много бы времени сэкономил.

UPD:
Вот только что вот оказалось, что пустая ячейка может быть представлена как отсутствием параметра <v> в <c>, так и отсутсвием самого <c>. Необходимо сверять атрибут «r».
            <c r="A1" s="1" t="s"/>
            <c r="B1" s="1" t="s">
                <v>1</v>
            </c>
<!--тут пропущена ячейка С1-->
            <c r="D1" s="2" t="s">
                <v>2</v>
            </c>
            <c r="E1" s="12"/>

Исправлю по возможности.

Similar posts

AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 10

    +4
    Не так просто пересадить клиента с xls на xlsx.
    Обычно клиенты хотят поддержки обоих форматов, и чаще старый, нежели новый.
    В данной ситуации повезло =)
      +3
      Формула пишется в теге f (на одном уровне с v).

      Тип (t) может быть «inlineStr», тогда строка записана не в SharedString, а прямо внутри ячейки, но в теге is (а не v) (хотя практически я с этим не сталкивался).

      Целые числа и даты, а также вещественные числа и время (дата со временем) выглядят одинаково в теге v. Потому что дата, например, — это число дней с 01.01.1900.
      Чтобы понять что это на самом деле нужно смотреть в атрибут s — это индекс стиля из workbook.xml.
        0
        P.S. Только расставляя метки наткнулся на Работа с большими файлами экселя. Хабрить надо было, а не гуглить — много бы времени сэкономил.

        Там же через OLEDB работают.
          0
          Важно не само решение, а то, что практически в самом начале поста автор описывает (вракце) формат.
          в отличие от бинарных xls, xlsx — по сути zip архив с данными.
          0
          Не очень универсальное решение получилось. Как только вы попробуете использовать эту наработку на еще более урезанном сервере, либо размер файлов клиента увеличится в пару раз (что совсем не фантастика), памяти опять не будет хватать. Как решение, можно читать файл небольшими блоками, кормить ими xmlparser. Результат обрабатывать на лету, а не складывать в массив.
            0
            помню, что когда-то нужно было обработать несколько десятков msword документов из табличними данними — и вот почесалась репа и накидалось на коленке решение с использованием php streams:

            $questions = array();
            foreach(new DirectoryIterator(realpath('data')) as $file)
            {
            	if($file->isFile())
            	{
            		$path = "zip://" . $file->getPathname() . '#word/document.xml';
            		_parse($path, $questions);
            	}
            }
            
            function _parse($path, &$questions)
            {
            	$xml = simplexml_load_file($path);
            	$ns = $xml->getDocNamespaces();
            	$xml->registerXPathNamespace('w', $ns['w']);
            	
            	foreach($xml->xpath('/w:document/w:body/w:tbl') as $elem)
            	{
            
              0
              А как экранируются служебные символы, например < >?
                0
                Методом проб и ошибок выяснил, что с помощью htmlspecialchars
                P.s. Сам спросил, сам ответил. Надеюсь кому то будет полезно.
                0
                Ну логично.
                  0
                  Дополнение от reak002

                  Хочу предложить небольшое дополнение к коду в виде:

                  $keyN = preg_replace('/\d/', '', $attr['r']);
                  $out[$file][$row][$keyN] = isset($attr['t']) ? $sharedStringsArr[$value] : $value;
                  

                  вместо

                  $out[$file][$row][$cell] = isset($attr['t']) ? $sharedStringsArr[$value] : $value;
                  $cell++;

                  после чего код будет собирать полную сетку документа.

                  Вот собственно видоизмененный код:

                  $handle = @opendir(TEMP_DIR_PATH . '/' . $name.'/xl/worksheets');
                  $out = array();
                  while ($file = @readdir($handle)) {
                      //проходим по всем файлам из директории /xl/worksheets/
                      if ($file != "." && $file != ".." && $file != '_rels') {
                          $xml = simplexml_load_file(TEMP_DIR_PATH . '/' . $name.'/xl/worksheets/' . $file);
                          //по каждой строке
                          $row = 0;
                          foreach ($xml->sheetData->row as $item) {
                              $out[$file][$row] = array();
                              //по каждой ячейке строки
                              foreach ($item as $child) {
                                  $attr = $child->attributes();
                                  $value = isset($child->v)? (string)$child->v:false;
                                  $keyN = preg_replace('/\d/', '', $attr['r']);
                                  $out[$file][$row][$keyN] = isset($attr['t']) ? $sharedStringsArr[$value] : $value;
                              }
                              $row++;
                          }
                      }
                  }
                  return $out;

                  Only users with full accounts can post comments. Log in, please.