Конвертирование из Excel в HTML: корректно, качественно, просто.

 АЛЕКСЕЙ МИЧУРИН

Конвертирование из Excel в HTML:

корректно, качественно, просто

Итак, наша задача состоит в том, чтобы корректно конвертировать документ из формата xls в формат HTML с учётом форматирования исходного документа и при этом обойтись «малой кровью»

Постановка задачи. Или в чем проблема?

Многие веб-мастера часто сталкиваются с задачей конвертирования файлов Microsoft Excel в другие форматы. Нередко это сопряжено с трудностями, так как формат xls, как все прекрасно знают, не документирован.

В некоторых случаях возможно сохранение данных Excel в документированных форматах и последующая их обработка. Но часто этот способ работает неудовлетворительно. Простые форматы, удобные для обработки, не способны сохранить всю информацию о форматировании документа, а реализация обработчика сложных форматов неоправданно трудоёмка.

За примером далеко ходить не надо. Множество фирм, которые имеют свои веб-страницы и периодически их обновляют, ведут свои дела с использованием Excel. Перед веб-мастером при каждом обновлении информации на сервере возникает задача конвертирования. Причём задача может осложняться следующими аспектами:

Во-первых, это изменение дизайна. Price-list, подготовленный в Excel, обычно рассчитан на распечатку на чёрно-белом принтере. Price-list на веб-сайте – нет. Хотя бы поэтому простое «Сохранить как веб-страницу» не подходит (я уж молчу о том, какого качества получается HTML-код при таком сохранении).

Во-вторых, при конвертировании необходимо учесть специфику Excel. Например, очень многие люди, редактирующие price-list, широко применяют команду «Формат/строка/скрыть». При этом высота строки становится нулевой, и строка как бы исчезает с экрана и на печати. Понятно, что и на веб-сайт такие строки попасть не должны. Однако они превосходно сохраняются в других форматах и ничем не отличаются от обычных, не скрытых, строк. Это приводит к тому, что доктрина «Сохранить как текст с разделителями и обработать» не даёт удовлетворительных результатов.

В-третьих, в price-list часто используется форматирование, сохранение которого критично. Например, наименования некоторых товаров могут быть зачёркнуты или выделены цветом в знак того, что эти товары были и обязательно будут, но сейчас их нет. Некоторые позиции могут быть выделены курсивом и так далее. Вся эта информация бесследно исчезает, если пересохранить price-list в простом формате, скажем, как текст с разделителями табуляции.

Итак, наша задача состоит в том, чтобы корректно конвертировать документ из формата xls в формат HTML с учётом форматирования исходного документа и при этом обойтись «малой кровью».

Я предлагаю разбить эту задачу на две. Первая – сохранение данных в простом формате, который тем не менее будет нести в себе всю необходимую нам информацию о разметке документа. Вторая – обработка этого формата и создание HTML-страницы.

Первую задачу (экспорт) я предлагаю решить средствами Excel. Тут у нас фактически нет выбора, формат xls может обработать только то единственное на свете приложение, которое его понимает. Это диалектика.

Вторую задачу я предлагаю решить средствами языка Perl. Почему? Потому, что этот язык ориентирован на работу со строками и на решение задач, подобных нашей (Perl – Practical Extraction and Report Language – то, что надо). Потому, что этот язык знает достаточно много программистов, связанных с веб-разработками (если вы не относитесь к их числу и планируете заниматься web, то искренне рекомендую обратить внимание на Perl). Потому, что этот язык бесплатен и доступен любому пользователю на любой платформе. И потому, что мой код на Perl можно будет потом легко модифицировать, заставив его, допустим, помещать каждый раздел price-list в отдельный файл, различным образом сортировать позиции прайса, отслеживать обновления и динамику цен, снабжать каждую позицию полями HTML-форм для on-line заказа в веб-магазине... В конце концов, мой скрипт легко превратить в CGI-приложение для администрирования веб-сервера.

Такое решение представляется мне максимально гибким, функциональным и компактным, потому что каждая часть задачи решается тем средством, которое наиболее приспособлено для её решения.

Давайте оттолкнёмся от конкретного примера. В качестве «подопытного кролика» предлагаю следующий прайс (см. рис. 1).

Рисунок 1. Пример прайса

Как видите, он сочетает в себе все неприятные элементы, упомянутые выше: и форматирование (фон, перечёркивания, жирный шрифт), и скрытые строки (если приглядеться, то можно заметить, что после девятой сразу идёт двенадцатая строка). Давайте им займёмся.

Экспорт данных из Excel

Приступим к решению первой задачи. Для экспорта данных из Excel я предлагаю несложный макрос на Visual Basic (номера строк приведены только для удобства комментирования):

1: Sub table2table()

2: '

3: ' макрос, сохраняющий выделенный фрагмент таблицы

4: ' в текстовом формате с отметками о форматировании

5: '

6:   With ActiveWindow.RangeSelection

7:         c1 = .Columns.Column

8:         c2 = .Columns.Count - 1 + c1

9:         r1 = .Rows.Row

10:        r2 = .Rows.Count - 1 + r1

11:   End With

12:   If (r1 - r2 = 0 And c1 - c2 = 0) Then

13:     MsgBox _

14:       "что-то мало выделено (для сохранения) ,-)", _

15:       vbCritical, "сообщение макроса"

16:   End If

17:   fileSaveName = Application.GetSaveAsFilename( _

18:       InitialFileName:="file", _

19:       fileFilter:="Text Files (*.txt), *.txt", _

20:       Title:="сохранение страницы в нашем формате")

21:   If fileSaveName = False Then

22:     MsgBox _

23:       "файл-то не выбран. никаких действий не предпринято.", _

24:       vbCritical, "сообщение макроса"

25:   Else

26:     sep = Chr(9)    ' разделитель

27:     subsep = Chr(8) ' под-разделитель

28:     Open fileSaveName For Output As #1

29:     For r = r1 To r2

30:       l = CStr(Rows(r).RowHeight)

31:       For c = c1 To c2

32:         With Cells(r, c)

33:           l = l + sep + CStr(.Text) + _

34:                subsep + CStr(.MergeCells) + _

35:                subsep + CStr(.Font.Bold) + _

36:                subsep + CStr(.Font.Strikethrough)

37:         End With

38:       Next

39:       Print #1, l

40:     Next

41:     Close #1

42:   End If

43: End Sub

Этот макрос сохраняет выделенную часть прайса в заданный файл. Макрос можно добавить к рабочей версии прайса и сделать для его вызова кнопку (вне области печати), а можно хранить в отдельном файле. Поместить его в документ очень просто: вызовите редактор Visual Basic (меню: «Сервис –> Макрос –> Редактор Visual Basic»; или <Alt-F11>), создайте новый модуль (меню: «Вставить –> Модуль») и введите приведённый здесь текст (без номеров строк). Теперь можно нарисовать кнопку (инструмент на панели «Формы») и назначить ей макрос.

Давайте вкратце рассмотрим, как работает этот код.

Первая строка – объявление макроса. Как видите, я назвал его незамысловато table2table, вы можете наречь его более звучно.

В строках с 6 по 11 мы определяем границы выделенной части документа (ведь мы будем сохранять только выделенную часть). Теперь c1 и c2 – номера первого и последнего столбца, а r1 и r2 – первой и последней строки выделенной области.

Далее, в строках с 12 по 16 проверяем, а была ли выделена область или нашему макросу предстоит работать только с одной ячейкой. Этого, конечно, можно и не делать, но ведь, скорее всего, запускать этот макрос будете не вы, а менеджеры, редактирующие прайс, на их аккуратность не всегда можно рассчитывать. Итак, если ничего не было выделено, то наш макрос выдаст предупреждение (см. рис. 2).

Рисунок 2. Предупреждение макроса

В строках с 17 по 20 мы вызываем диалог Application.GetSaveAsFilename, чтобы пользователь мог выбрать имя файла (см. рис. 3).

Рисунок 3. Диалог сохранения пользователем файла

Опять же вы можете просто задать фиксированное имя, но мне кажется, что это неудобно даже в том случае, если вы сами эксплуатируете этот макрос.

В строках с 21 по 42 следует конструкция if-then-else с проверкой, было ли указано имя файла для сохранения или пользователь нажал кнопку «Отмена» диалога «Сохранить как...».

Если пользователь отказался от сохранения, то выдаётся соответствующее сообщение (строки с 22 по 24), если имя файла указано, то начинается самое интересное – сохранение данных.

Но прежде чем обсудить процедуру сохранения (строки с 26 по 41), давайте скажем пару слов о том, в каком же именно формате мы намерены сохранять данные. Предлагаю самый простой для обработки формат: ASCII-текст. Каждая строка соответствует строке сохраняемой таблицы. Поля разделены одно символьными разделителями. Первое поле – высота строки (эта информация необходима, чтобы отфильтровать «скрытые» строки). Все последующие поля – содержимое ячеек, но каждое из этих полей содержит несколько подполей, разделённых своими разделителями. Подполя несут различную информацию о ячейке: содержание, параметры форматирования.

У нас разделители полей и подполей задаются ASCII-кодами в строках 26 и 27 соответственно. Вы можете выбрать более удобные разделители. Например, если вы уверены, что в ваших данных никогда не встречается символ «:», то можно взять его в качестве разделителя или подразделителя.

Далее (строка 28) открываем файл.

Организуем цикл по строкам (строка 29).

Для каждой строки вычисляем высоту. Заодно начинаем готовить строку для сохранения в файл в переменной l (строка 30 листинга).

В цикле (строка листинга 31) по ячейкам сохраняемой строки таблицы добавляем к строке l всю интересующую нас информацию о ячейках, снабжая её разделителями.

Какие свойства ячейки мы сохраняем?

Первым делом – текст ячейки. Обратите внимание, что мы используем именно свойство .Text, а не свойство .Value. Это не случайно. Свойство .Value возвращает истинное содержимое ячейки, свойство .Text возвращает тот текст, который отображается на мониторе и выводится на печать. Эти две величины могут не совпадать (и обычно не совпадают), потому что на экран значения выводятся согласно заданному формату ячейки (например, числа выводятся с заданным количеством знаков после запятой).

Свойство .MergeCells говорит о том, является ли ячейка частью группы объединённых ячеек.

Свойство .Font.Bold отражает жирность текста в ячейке.

Свойство .Font.Strikethrough говорит о том, был ли текст оформлен как зачёркнутый.

Для нашего примера нам, пожалуй, больше ничего не потребуется. Однако не могу не отметить ряд полезных свойств, которые могут вам пригодиться. Имена этих свойств достаточно красноречивы, и я не буду их комментировать, ограничусь перечислением:

• .Font.Name
• .Font.FontStyle
• .Font.Size
• .Font.Underline
• .Font.ColorIndex
• .Font.Italic
• .HorizontalAlignment
• .VerticalAlignment
• .ColorIndex
• .Pattern

Обратите внимание, все свойства явно приводятся к строчному типу функцией CStr (строки с 33 по 36). Это весьма полезная процедура, навсегда избавляющая вас от головной боли о преобразованиях типов.

Здесь следует сделать важную оговорку. Дело в том, что функция CStr не в состоянии обработать неопределённые значения. Если таковые будут встречаться в ваших документах, то вместо CStr вы можете использовать собственную функцию преобразования величин в текстовый формат. Например, safeCStr:

1: Function safeCStr(p As Variant) As String
2: If IsNull(p) Then safeCStr = "" Else safeCStr = CStr(p)
3: End Function

Должен отметить, что сам я никогда не сталкивался с такими ситуациями, но научно-технический консультант журнала без труда нашёл в Интернете прайс-лист, в некоторых ячейках которого свойство .Font.Bold было не определено. Я полагаю, что такие документы могут возникать в результате экспорта данных из других приложений. Например, продукты «1C» допускают экспорт данных в Excel. Одним словом, такая ситуация возможна. – Примечание автора.

Вот почти и всё. Осталось сохранить готовую строку l в файл (строка 39 листинга) и закрыть файл по завершении всего цикла по строкам (строка 41).

Итак, вы выделяете сохраняемую область (в нашем примере это первые три столбца таблицы, строки с 4 по 21), нажимаете созданную вами кнопку, выбираете имя файла, и файл сохранён. Что с ним делать дальше?

Создание HTML-страницы по экспортированным данным

С этим файлом можно сделать всё что угодно, потому что его формат нам полностью известен (приятно это осознавать). Я приведу пример генерации HTML-страницы.

Как я и говорил, предлагаю скрипт на Perl:

1: #!/usr/bin/perl -w

2:

3: #use strict;

4:

5: # my ($TRUE, $FALSE)=('Истина', 'Ложь');

6:   my ($TRUE, $FALSE)=('True',  'False');

7:

8: sub qtnum {

9:     my $t=shift;

10:     $t=~s|,(\d+)|,<small>$1</small>|;

11:     return $t;

12: }

13:

14: sub qtstring {

15:     my $t=shift;

16:     $t=~s/\&/\&amp;/g;

17:     $t=~s/\"/\&quot;/g;

18:     $t=~s/\>/\&gt;/g;

19:     $t=~s/\</\&lt;/g;

20:     return $t;

21: }

22:

23: print <<'TEXT';

24: <html>

25: <head>

26: <title>прайс некой фирмы</title>

27: <style type="text/css">

28: <!--

29: .al {background-color: #FFFFFF}

30: -->

31: </style>

32: </head>

33: <body bgcolor="#E8E8E8">

34: <table cellspacing="0" cellpadding="1" border="0">

35: <tr><td colspan="3" height="1" bgcolor="#8F0000" nowrap></td></tr>

36: <tr>

37: <th colspan="3" bgcolor="#FFFFFF"><big>прайс некой фирмы</big></th>

38: </tr>

39: <tr><td colspan="3" height="1" bgcolor="#8F0000" nowrap></td></tr>

40: <tr>

41: <th rowspan="3">наименование товара</th>

42: <th colspan="2"><small>цена</small></th>

43: </tr>

44: <tr><td colspan="2" height="1" bgcolor="#8F0000" nowrap></td></tr>

45: <tr>

46: <th><small>у.е.</small></th>

47: <th><small>руб.</small></th>

48: </tr>

49: <tr><td colspan="3" height="1" bgcolor="#8F0000" ></td></tr>

50: TEXT

51:

52: while (<>) {

53:   s/[\x0A\x0D]+$//;

54:   my @f=split /\x09/;

55:   my $lh=shift @f;

56:   my ($name, $usd, $rub)=map {[split /\x08/]} @f;

57:   if ($lh) {

58:     if ($name->[1] eq $TRUE) { # обработка заголовка раздела

59:       print '<tr><th colspan="3" bgcolor="#FFFF00">' .

60:               $name->[0] .

61:               "</th>\n";

62:     } else {                   # обработка обычной строки

63:       print <<'TEXT' .

64: <tr

65:  onMouseOver="this.className='al';"

66:  onMouseOut="this.className = '';"><td>

67: TEXT

68:            ($name->[3] eq $TRUE?'<font color="#888888"><strike>':'') .

69:            qtstring($name->[0]) .

70:            ($name->[3] eq $TRUE?'</strike></font>':'') .

71:            qq|</td>\n<td align="right">| .

72:            ($usd->[2] eq 

                 $TRUE?'<font color="#880000"><b>':'') .

73:            qtnum($usd->[0]) .

74:            ($usd->[2] eq $TRUE?'</b></font>':'') .

75:            qq|</td>\n<td align="right">| .

76:            ($rub->[2] eq 

                 $TRUE?'<font color="#880000"><b>':'') .

77:            qtnum($rub->[0]) .

78:            ($rub->[2] eq $TRUE?'</b></font>':'') .

79:            "</td>\n";

80:      }

81:      print <<'TEXT';

82: </tr>

83: <tr><td colspan="3" height="1" bgcolor="#8F0000" nowrap></td></tr>

84: TEXT

85:   } else {

86:      warn 'hidden line: '.$name->[0]."\n";

87:   }

88: }

89:

90: print <<'TEXT';

91: </table>

92: </body>

93: </html>

94: TEXT

Скрипт принимает входные данные из файла, указанного как параметр командной строки, или со стандартного ввода и выдаёт HTML-код на стандартный выход. То есть запускать его можно так:

perl file2html.pl file.txt >file.html

или, например, так:

cat file.txt | perl file2html.pl >file.html

Разберёмся, как работает этот скрипт (я буду предполагать, что читатель немного знаком с Perl).

Первая строка – стандартная магическая строка любого UNIX-сценария. Пользователи Windows могут её проигнорировать. В третьей строке – закомментированная инструкция use strict. Она будет полезна вам только при отладке.

В строках 5 и 6 мы определим переменные $TRUE и $FALSE, которые будут содержать значения истины и лжи, выдаваемые Excel. Дело в том, что русский Excel использует русские слова, европейский – английские. Откомментируйте подходящую вам строку и закомментируйте лишнюю.

Процедура qtnum (с 8 по 12 строки) добавляет к записи числа теги, превращая «3,14» в «3,<small>14</small>». То есть центы и копейки будут отображаться меньшим шрифтом. Это чисто косметическая мера.

Процедура qtstring (строки с 13 по 21) квотирует «небезопасные» символы: & (and), “ (двойная кавычка), < (больше), > (меньше). Это, как вы понимаете, обязательная мера.

В строках с 23 по 50 печатается «шапка» HTML-документа.

В цикле while (строки с 52 по 88) мы считываем построчно входной файл, преобразуем его в HTML-документ и выдаём в стандартный поток вывода stdout.

В строке 53 от очередной считанной строки отрезается символ(ы) конца строки. Я не использую стандартные функции Perl chop и chomp, потому что обрабатываемый файл создаётся под Windows, а обработчик (наш сценарий на Perl) может работать и под UNIX. Файл же может передаваться весьма экзотическими путями. Например, наверняка многие захотят чуть доработать мой код и превратить его в CGI-приложение для администрирования их родного сервера. Поэтому я не полагаюсь на стандартные функции, а прописываю явно, что мне необходимо удалить все символы \x0A и \x0D в конце строки.

Далее строка разделяется на поля (строка 54). Здесь используется тот же разделитель, что и в макросе на Visual Basic (строка макроса 26).

Первое поле – высота строки – сохраняется в переменной $lh (строка 55). Все остальные поля разделяются на под-поля. В результате переменным $name, $usd, $rub присваиваются указатели на массивы, содержащие всю необходимую информацию о содержимом и форматировании соответствующей ячейки. $name – ячейка с наименованием товара, $usd – ячейка с ценой в долларах, $rub – ячейка с ценой в рублях. Делается это одной-единственной строкой 56.

Если высота строки не равна нулю, то выполняем блок с 58 по 84 строки. В противном случае выдаём предупреждение в стандартный поток ошибок stderr о том, что обнаружена и проигнорирована скрытая строка (строка 86).

Обработка строк таблицы только на первый взгляд может показаться сложной.

Прежде всего мы выясняем, с чем мы имеем дело: в строке 58 проверяем истинность свойства .MergeCells ячейки с наименованием товара. Если эта ячейка объединена, то это заголовок раздела, тогда выполняется код генерирующий заголовок (строки с 59 по 61).

Если оказывается, что мы имеем дело с обычной строкой, то выполняется блок else (строки с 63 по 79). Здесь формируется строка HTML-таблицы, в которую вставляются дополнительные элементы форматирования (для тех строк таблицы, где это необходимо).

Обратите внимание, что мы встроили в наш документ элементарный DHTML-приём. В таблице всегда подсвечивается строка, на которой находится указатель мыши. Это упрощает чтение таблицы. Согласитесь, что добиться такого эффекта средствами Excel (путём сохранения документа как веб-страницы) затруднительно.

В строках 90–94 выводим завершающие теги документа. Обратите внимание, что в конце файла с программой обязательно должна быть пустая строка. Иначе последнее слово («TEXT») (строка 94) не будет правильно обработано интерпретатором Perl.

То что получается на выходе, смотрите на рисунке (стр. 82).

Согласитесь, было за что биться!

BUGS. Что ещё можно добавить?

Я не сомневаюсь, что хотя мои примеры полностью работоспособны, мало кто будет использовать их без малейших модификаций. Хочу подбросить пару мыслей о том, что можно улучшить в этих скриптах, чтобы при их модификации вы не делали лишней работы, а убивали сразу как можно больше зайцев.

По сценарию на Visual Basic

Здесь вам скорее всего придётся изменить набор сохраняемых параметров ячейки. Список наиболее полезных я привожу в обсуждении этого сценария. Если вам понадобится какая-нибудь экзотика, обращайтесь к документации Microsoft, свойства объекта Range.

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

Рисунок 4. Конечный прайс

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

Наконец, читатель может справедливо спросить, зачем макрос для Excel сохраняет скрытые строки, ведь их можно отсеять уже на стадии экспорта? Снимаю шляпу перед внимательностью читателя (втайне надеясь на ответный жест в адрес моей проницательности). Сохранять скрытые строки действительно совсем не обязательно, просто я привык сохранять всё. Причины? Возможно, скрытые строки всё-таки понадобятся. Или вы захотите точно знать, какие строки были проигнорированы (мой Perl-сценарий, как вы помните, сообщает о каждой скрытой строке). Кроме того, информация о высоте строк может быть критерием для идентификации заголовков... Хотя, конечно, вы можете слегка модифицировать VB-код и не сохранять скрытые строки.

По скрипту на Perl

Конечно, весь HTML-код (а это большая часть скрипта) вы скорее всего значительно измените. Конечно, вам придётся изменить количество столбцов, шапку, многие удалят из HTML-кода мои пустые строчки-разделители, накрутят вложенных таблиц, изменят DHTML-функции, добавят CSS-таблицы... Но это не самое главное и не принципиальное изменение.

Скорее всего вам придётся «научить» этот сценарий разбивать большие документы на разделы и сохранять эти разделы в разных файлах, потому что прайс-лист весьма средней фирмы в формате HTML может потянуть на сотни килобайт. Не всякий веб-странник дождётся конца загрузки такого документа. Возможно, вы захотите добавить сортировку (если позиции в печатном прайс-листе и в веб-прайсе должны следовать в разном порядке).

Несомненно, будет полезна функция сравнения текущего прайса с предыдущим, которая будет добавлять информацию об обновлениях и динамике цен.

Я бы советовал организовать подобные процедуры (не относящиеся непосредственно к HTML-вёрстке) в виде отдельных программ или модулей. Кстати, часть работы по HTML-вёрстке можно доверить механизму SSI, а скрипт пусть собирает SHTML-документ. Набор простых инструментов всегда удобнее, гибче и управляемее, чем один универсальный. Держитесь подальше от грабель, по которым гуляют создатели телефона-микроволновки и телевизора-зубной щётки.

Список советов и предложений можно продолжать бесконечно, но я думаю, что уже разбудил вашу фантазию, и дальше вы справитесь и без меня. Адаптация приведённого здесь примера к вашим конкретным условиям, возможно, потребует несколько часов работы. Зато потом вы будете щедро вознаграждены, ведь все последующие обновления информации на вашем веб-сервере вы будете делать буквально в несколько касаний клавиатуры и мыши!

Комментарии могут оставлять только зарегистрированные пользователи