Итак, продолжаем разговор. На всякий случай уточню, что начало здесь.

Про строковые значения и метод их хранения я уже вскользь упоминал в первой части, а сейчас поговорим подробнее. Представим, что у нас есть таблица, заполненная строковыми данными, и что она большая. При этом крайне маловероятно, что все значения в ней будут уникальны. Некоторые из них нет-нет, да повторятся где-нибудь в разных частях таблицы. Хранить такой массив «как есть» внутри XML-разметки листа нерационально с точки зрения ресурсов ПК. Поэтому все строковые значения вынесены в отдельный файл, %file%/xl/sharedStrings.xml. Часть его, которая нас интересует, выглядит, допустим, так:

<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="4" uniqueCount="3">
	<si><t>Вася</t></si>
	<si><t>Петя</t></si>
	<si><t>Саша</t></si>
</sst>

Обратите внимание на атрибуты тега <sst /> «count» и «uniqueCount»: их значения различаются. Дело в том, что в книге одну из строк я использовал дважды. При этом атрибуты не обязательны, то есть если их убрать, то Excel ошибки не выдаст, но при сохранении файла нарисует опять.

Здесь же можно сказать, что здесь, внутри тега <si /> можно играть с настройками шрифта. Для этого используется доработанная напильником система пробегов, применяемая в MS Word (до него мы еще доберемся). Выглядит это примерно так:

	<si>
		<r>
			<t xml:space="preserve">Мама </t>
		</r>
		<r>
			<rPr>
				<b/>
			</rPr>
			<t>мыла</t>
		</r>
		<r>
			<rPr>
				<sz val="18"/>
			</rPr>
			<t xml:space="preserve"> раму</t>
		</r>
	</si>

Обратите внимание: в корневой тег <si/> в предыдущем примере был встроен непосредственно тег <t/>, содержавший текст. Здесь же он обернут тегом <r/>, то есть Run; по-русски его принято назвать «пробег». Пробег — это, если в двух словах — кусок текста, имеющий одинаковые стилевые настройки.

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

Первый:

<r>
	<t xml:space="preserve">Мама </t>
</r>

Этот пробег не содержит секции <rPr/>, поэтому использует стилевые настройки ячейки, в которой находится. В нем интересно другое: атрибут xml:space=«preserve». Дело в том, что по умолчанию что Excel, что Word обрезают концевые пробелы со всех пробегов. Может показаться, что в этом случае в месте стыка пробегов всегда должна получаться примерно такая картина: ВасяПетя. Но по опыту общения с тем же MS Word мы знаем, что это не так. Из-за чего? Вот как раз из-за xml:space=«preserve».

Второй:

<r>
	<rPr>
		<b/>
	</rPr>
	<t>мыла</t>
</r>

Здесь нет атрибута xml:space=«preserve». Нам без разницы, что Excel сделает с концевыми пробелами, которых нет. Зато есть блок <rPr/>. В принципе, в него можно поместить любые настройки шрифта, которые только есть в Excel. Я же сделал всего один, чтобы не раздувать объем примера.

Третий:

<r>
	<rPr>
		<sz val="18"/>
	</rPr>
	<t xml:space="preserve"> раму</t>
</r>

А здесь у нас есть и блок настроек шрифта и сохранение концевых пробелов.

Ну и еще коротенькая ремарка. Если есть необходимость сделать многострочную запись в ячейке, то здесь в строке просто будет обычный символ переноса, chr(10). Сам атрибут многострочности ячейки расположен в файле разметки листа. В однострочной ячейке символ переноса будет проигнорирован. Excel просто сделает вид, что его нет.

Перейдем в папку %file%/xl/worksheets. Здесь, как говорилось выше, каждый лист, содержащийся в книге, представлен файлом .xml.

Файл разметки листа содержит следующие ключевые элементы (и, что важно, желательно располагать их именно в таком порядке):

1. Тег <dimension>. Необязателен. Служит, насколько я смог понять, для указания системе размера занятой области, то есть на сколько прорисовывать строки и столбцы

2. Тег </sheetViews>. Необязателен, но иногда полезен. Я его использовал для указания на необходимость закрепления верхней строки: это полезно для больших отчетов. Выглядит это примерно так:

<sheetViews>
	<sheetView tabSelected="1" workbookViewId="0">
		<pane ySplit="1" topLeftCell="A2" activePane="bottomRight" state="frozen"/>
	</sheetView>
</sheetViews>

Здесь надо дать пояснение. Собственно закрепление строки — тег <pane />. И вот какие здесь использованы атрибуты:

  • ySplit — показывает количество закрепленных строк. Для закрепления столбцов есть аналогичный атрибут xSplit;
  • topLeftCell — указание левой верхней ячейки видимой по умолчанию НЕзакрепленной области;
  • activePane — указание местонахождения НЕзакрепленной области. В руководствах сказано, что этот атрибут регулирует, с какой стороны будет НЕзакрепленная область. Правда, попробовав разные значения, я почему-то получил одинаковый результат. Как вариант «by default» я для себя выбрал bottomRight;
  • state — указатель состояния закрепленной области. Для простого закрепления строки используется значение frozen

3. Тег <sheetFormatPr />. Пример:

<sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>

Интересен нам здесь в основном атрибут defaultRowHeight, то есть высота столбца по умолчанию. Стандартный, привычный нам вариант — 15 у.е. Если назначить его, скажем, 30 у.е., то строки, для которых высота не указана отдельно, станут в 2 раза выше. Однако, для того чтоб применить значение, отличное от дефолтного, необходимо указать атрибут customHeight со значением «true». Выглядит это примерно так:

<sheetFormatPr defaultRowHeight="30" customHeight="true" x14ac:dyDescent="0.25"/>

4. Тег </cols>. Помогает установить ширину столбцов отличную от дефолтной. В заполненном виде выглядит примерно так:

<cols>
	<col min="1" max="2" width="9" customWidth="1"/>
	<col min="3" max="4" width="16" customWidth="1"/>
	<col min="5" max="6" width="66" customWidth="1"/>
	<col min="7" max="8" width="42" customWidth="1"/>
</cols>

Вложенные теги </col> обозначают не каждый один столбец, как могло показаться, а группу столбцов, идущих подряд и имеющих единую ширину.

  • Атрибут min — первый столбец группы;
  • Атрибут max — последний столбец группы;
  • Атрибут width — ширина столбца из группы;
  • Атрибут customWidth — флаг применения кастомной ширины, без него ширина все равно будет дефолтной;

5. Тег <sheetData> — это, собственно, зона, где хранится содержимое ячеек, или, в зависимости от типа, ссылок на него. В среднем выглядит он так:

<sheetData>
	<row r="1">
		<c r="A1" s="3" t="s"><v>0</v></c>
		<c r="B1" s="3" t="s"><v>1</v></c>
		<c r="C1" s="3" t="s"><v>2</v></c>
	</row>
	<row r="2">
		<c r="A2" s="2"><v>1</v></c>
		<c r="B2" s="2"><v>37539</v></c>
		<c r="C2" s="2" t="s"><v>14</v></c>
	</row>
	<row r="3">
		<c r="A3" s="2"><v>2</v></c>
		<c r="B3" s="2"><v>33227</v></c>
		<c r="C3" s="2" t="s"><v>21</v></c>
	</row>
</sheetData>

Как видно, в тег <sheetData> вложены теги <row> с атрибутом «r», обозначающим фактический (а не порядковый) номер строки: надо помнить, что, скажем, строка 1 может быть пустой, а строка 2 — нет.

«Букв, что ли, пожалели?» — спросите вы. «Экономия памяти» — ответит Microsoft. Если вспомнить про ограничение в 16 миллионов с гаком ячеек, становится понятна их мотивация. Выходит, в теории один (!) лишний символ в имени атрибута может привести к миллионам лишних символов при чтении всего файла.

В тег <row /> вложены теги <с /> — ячейки. В примере видно, что основных атрибутов у ячейки три:

  • r — адрес ячейки;
  • s — стиль ячейки. Вспоминаем первую часть данной статьи: в файле styles.xml есть раздел <cellXfs />, в котором перечислены зарегистрированные стили оформления ячеек. атрибут «s» тега <с /> — как раз ссылка на элемент этого списка, начиная с 0;
  • t — указание на необходимость обращения к таблице строковых значений в файле sharedStrings.xml. Если атрибут указан — обращаемся, если нет — пишем как есть то, что в теле тега. Примечательно, что при попытке вписать в тело тега текст без указания данного атрибута, Excel при открытии файла ругнется, но послушно перенесет нашу фразу туда, где ей место (хотя я на его сообразительность рекомендую не полагаться и сразу писать строки в sharedStrings.xml);

Когда я говорил про файл sharedStrings.xml, я упомянул, что многострочные ячейки помечаются в файле разметки листа. Делается это, к примеру, так:

<row r="1" spans="1:9" ht="45" customHeight="1" x14ac:dyDescent="0.3">
	<c r="A1" s="2" t="s"><v>3</v></c>
</row>

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

6. Тег <mergeCells />. Как мы знаем, в Excel есть возможность объединения ячеек. Все объединенные ячейки на листе перечислены здесь. В заполненном виде тег выглядит примерно так:

<mergeCells count="40">
	<mergeCell ref="G15:I15"/>
	<mergeCell ref="E3:F3"/>
	<mergeCell ref="E4:F4"/>
</mergeCells>

Как видно, одна объединенная ячейка обозначена одним тегом <mergeCell /> с единственным атрибутом ref, задающим диапазон объединения.

7. Тег <autoFilter />. Фильтры, которые так любят видеть в отчетах наши пользователи. В заполненном виде тег выглядит так:

<autoFilter ref="A1:N1"/>

Нетрудно понять, что атрибут «ref» задает зону, занимаемую активными ячейками фильтров.

Ну и «на сладкое» в файле идут настройки страницы для печати. Вот пример из одного моего файла:

<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
<pageSetup paperSize="9" orientation="portrait" r:id="rId1"/>

Тег <pageMargins /> задает поля, а тег <pageSetup /> — предпочтительные настройки бумаги.

В комментариях к первой части была просьба поговорить собственно о редактировании этого чуда техники средствами PL/SQL. Следующая часть будет именно об этом.

Комментарии (10)


  1. NeverIn
    26.01.2018 19:17

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


  1. Azy
    27.01.2018 15:09

    А как записываются формулы?


    1. Lachrimae Автор
      28.01.2018 21:49

      Поэкспериментировал. С формулами оказалось интересно.
      Формулы хранятся в виде ключевого слова в файлах разметки листов, внутри тега ячейки. Выглядит это как-то так:

      <c><f>SUM(A1:C1)</f><v>6</v></c>

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


  1. Alex57
    28.01.2018 21:26

    Не очень понятно зачем shared строковые данные в формате, который ОБЯЗАТЕЛЬНО затем собирается архиватором ZIP. ZIP отлично сожмет текст, который повторяется несколько раз.
    Могу только предположить что формат (кусок про shared) придумали и утвердили чуть раньше чем решили зиповать…


    1. Lachrimae Автор
      28.01.2018 21:28

      ZIP-то. может, и сожмет, но разжимать-то потом Excel-ю. И, как следствие, на экран выводить ему же. Думаю, дело именно в этом.


      1. Alex57
        28.01.2018 22:59

        думаю что Excel хранит данные «в ОЗУ» в том же формате что и раньше (95, 97,2000,XP, 2003), приближенному к тому бинарному виду что потом на диске в формате xls.
        И еще ему нужно очень быстро это находить и обсчитывать, и тут проще с распакованным работать :)

        Для верности я провел опыт (Excel 2016, 64bit, все апдейты установлены, на компе 8 ГБ ОЗУ).
        Открыл Excel, вбил в ячейку A1 число 1. Скопировал на всю строку. Размер занятой памяти с 45 мегабайт поднялся до 48.
        Потом попросил всю ПЕРВУЮ строку скопироваться на все строки (миллион). Excel ОТКАЗАЛСЯ, потому что ему не хватит ОЗУ. Если бы он хранил это в компрессированом виде, то проблема была бы только хранить 1 млн. вхождений ОДИНАКОВОЙ строки.
        Однако в том же файле скопировать цифру 2 млн раз (на весь столбец) — вполне удалось. (занято 122 МБ).Т.е. в ОЗУ он хранит все таки распакованный вид, иначе ему что хранить ссылку на 2, что хранить ссылку на целый ряд «как первый» было примерно одинаково.


        1. Lachrimae Автор
          29.01.2018 10:22

          Получается, так. Значит, причина кроется скорее в скорострельности при открытии файла. Числа «1» и «2» — это замечательно, но памяти требует значительно меньше, чем строка из хотя бы 4 символов.


          1. Alex57
            29.01.2018 10:39

            ок :) Сделал «very long string», скопировал на миллион раз. Нормально. Сделал "=rand()" и еще миллион копий — нормально. Миллион формул и значений с большой дробной частью. (Памяти заняло примерно 120 мегов ОЗУ, в .xlsx 32 мега, в .xlsb — 15 мегов, в формате .xls сохранять отказался, ибо в нем все свыше 65 тыс строк отрежется…


            1. Lachrimae Автор
              29.01.2018 14:55

              Тогда Ich kapitulieren.
              И возникает вопрос, почему у нашей бухгалтерии начинаются адские тормоза при попытке сделать файл хотя бы в 10000 строк.


  1. Alex57
    30.01.2018 19:40

    Спасибо за статью, стал экспериментировать.

    Пару выводов:
    1) Для r и c не обязательно указывать полный адрес (r=«1», r=«A3» и т.п.)
    Такой код:

    <sheetData>
      <row><c t="inlineStr"><is><t>Paris</t></is></c></row>
      <row><c t="inlineStr"><is><t>Seattle</t></is></c></row>
      <row><c t="inlineStr"><is><t>London</t></is></c></row>
      <row><c t="inlineStr"><is><t>Copenhagen</t></is></c></row>
      <row><c t="inlineStr"><is><t>Paris</t></is></c></row>
      <row><c t="inlineStr"><is><t>London</t></is></c></row>
    </sheetData>
    

    вполне себе нормально делает Excel-файл из 6 строк.

    2) про inline строки — их вполне можно вставлять :) Правда хватает на один раз, при сохранении этого файла Excel-ем, он сконвертнет все в sharedstring. Но для вывода из какой-нибудь другой системы это реально проще, чем справочники делать.

    и пара вопросов:
    3) Есть ли у вас на примете какой-нибудь валидатор этого XML языка? Разметку Notepad++ подсвечивает, но вот незакрытые теги он не дает.

    4) есть ли на примете bat-файл для сжатия обратно в zip/xlsx из отдельных файлов? Разобрать можно одной командой вида
    7z.exe x file1.xlsx
    а вот собрать…