Экспорт таблиц из Excel в HTML

27th Октябрь 2011 | Категории: VBA | Метки:

Часто перед блогером или веб-программистом возникает задача переноса данных из таблицы Excel на сайт. Стандартный механизм экспорта, встроенный в Excel, генерирует очень много мусора. Лишние классы, избыточное форматирование, кривые тэги и свойства для каждой ячейки. Всё это может привести к некорректному отображению на конкретном сайте. А так хотелось, чтобы можно было экспортировать таблицу в чистый HTML.

Сказано — сделано. Недаром же мы программисты. Благо в Excel встроен язык VBA (Visual Basic for Applications). Последний раз я что-то программировал на нем в 2003 году (и выполнял схожую задачу, только экспорт был для форума в bbcode сводной таблицы по состоянию альянсовых артефактов в игре DS). Но «вспомнить все» не составило большого труда. Если вас интересует итоговый результат, можете скачать xlsm-документ (документ Excel 2007 XML, с макросами) в конце статьи.

Пара слов о самом VBA. VBA — интерпретируемый язык. Имеет поддержку в следующих продуктах:

  • Microsoft Office
  • AutoCAD
  • SolidWorks
  • CorelDRAW
  • и некоторые другие.

Описывать каждую строчку кода не имеет смысла, опишу лишь итоговый интерфейс и некоторые нюансы.

Интерфейс Excel-документа

Скорее всего при открытии документа вы получите предупреждение:

Открытие документа

Открытие документа


По умолчанию Excel блокирует любые документы, содержащие макросы на VBA. Но вы ведь мне доверяете? Поэтому смело разрешайте выполнение, как показано на предыдущем рисунке.

А теперь непосредственно сам интерфейс:

Интерфейс

Интерфейс

Блок № 1 содержит настройки для экспорта. Вы можете изменить наименование класса для таблицы, и наименование класса для раскраски четных рядов.

Блок № 2 — это кнопка экспорта. Для удобства пользователя весь сгенерированный HTML код помещается в текстовое поле на форме.

Блок № 3 — форма, которая появляется после нажатия на кнопку «Export». Содержит TextBox и Button, нажав на который происходит копирование сгенерированного текста в буфер обмена и закрытие формы.

Блок № 4 — это сама таблица. Таблица начинается с ячейки A6.

Нюансы при оформлении таблицы

В первой строке (ячейка А6) находится caption для таблицы.
Высота таблицы определяется по столбцу А. Начиная с ячейчки А7 скрипт просматривает все ячейки вниз, пока не найдет пустую ячейку. Обнаружив пустую ячейку скрипт считает что это конец таблицы, и запоминает значение в переменную iLastLine. Аналогично происходит определение ширины таблицы. Начиная с ячейки А7 скрипт просматривает все ячейки вправо, пока не найдет пустую ячейку. Значение о последнем столбце заносится в переменную iLastCol. Если вам необходимо вставить пустую ячейку в первую строку или первый столбец можете воспользоваться

 

Допускается использование горизонтального объединения ячеек. Скрипт корректно заменяет их на colspan.
Скрипт определяет такое форматирование ячеек, как: bold, italic, center.
Если скрипт находит пустую ячейку внутри таблицы, то производится подстановка  

Ограничения при оформлении таблицы

  • Не использовать вертикальное объединение ячеек
  • Не использовать горизонтальное объединение ячеек, выходящее за границу шапки

Комментарии к коду

В VBA было принято ставить первую букву согласно типу переменной. Поэтому iFirstLine — это переменная типа int, которая содержит номер первой строки таблицы. А sLine — переменная типа string, которая содержит текст текущего ряда.
В VBA нет некоторых простых функций, поэтому для проверки четности переменной добавляем немного магии:

(k \ 2 = k / 2)

Смысл этого заклинания в том, что мы делим переменную целочисленно и точно на 2. Если число четное, оно без остатка разделится на 2, значения совпадут и выражение будет TRUE.

Также стоит заострить внимание на магии с объединенными ячейками.

У каждой ячейки есть свойство MergeCells, которое определяет наличие объединения. Если мы обнаружили объединенную ячейку, значит мы можем узнать длину объединения (через свойство MergeArea.Count) и сразу пропустить объединенные ячейчки (которые не содержат полезной информации).

Также стоит упомянуть о работе с объектами. Чтобы присвоить значению переменной объект (например ячейку) необходимо воспользоваться конструкцией:

Set oCurrentCell = ActiveSheet.Cells(k, j)

Для удаления значения объекта:

Set oCurrentCell = Nothing

Для проверки объекта:

If oCurrentCell is Nothing
If Not oCurrentCell is Nothing

Пример сгенерированой таблицы

<div>
<center>
<table class='tut' border=0 width=100% align=ceneter>
<caption>Пример таблицы</caption>
<tr><td>&nbsp;</td><td><b>0</b></td><td><b>25</b></td><td><b>50</b></td><td><b>75</b></td></tr>
<tr class ='odd'><td><b>Первая строка</b></td><td>1</td><td>2</td><td>3</td><td>4</td></tr>
<tr><td><b>Вторая строка</b></td><td colspan=4 style='text-align: center;'><i>курсив в 4 ячейках</i></td></tr>
<tr class ='odd'><td><b>Третья строка</b></td><td colspan=2 style='text-align: center;'>первое</td><td colspan=2 style='text-align: center;'>второе</td></tr>
</table>
</center>
</div>

Пример таблицы
  0 25 50 75
Первая строка 1 2 3 4
Вторая строка курсив в 4 ячейках
Третья строка первое второе

Код

Sub ExportHTML()
'1. Начальная инициализация. Начало таблицы находится в ячейке А6
'A6 - caption таблицы
'A7+ - строки таблицы
iFirstLine = 6
iFirstCol = 1
iLastLine = iFirstLine
iLastCol = iFirstCol
'HTML классы для таблицы и четного ряда данных
sTableClass = Cells(2, 2).Text
sOddRowClass = Cells(3, 2).Text
 
'Поиск ширины и высоты таблицы ведется по первому столбцу и первой строке таблицы после caption
'2. Поиск высоты таблицы
j = iFirstLine + 1
Do While Cells(j, iFirstCol).Text <> ""
    j = j + 1
Loop
iLastLine = j - 1
 
'3. Поиск Ширины таблиц
j = iFirstCol
Do While Cells(iFirstLine + 1, j).Text <> ""
    j = j + 1
Loop
iLastCol = j - 1
 
'4. Начинаем таблицу
sOutput = "<div><center><table class='" & sTableClass & "' border=0 width=100% align=ceneter>"
sOutput = sOutput & "<caption>" & Cells(iFirstLine, iFirstCol).Text & "</caption>"
 
'5. Обрабатываем Excel таблицу
For k = iFirstLine + 1 To iLastLine
    If (k \ 2 = k / 2) Then 'проверяем на четность
        sLine = "<tr class ='" & sOddRowClass & "'>"
    Else
        sLine = "<tr>"
    End If
    iCountColspan = 0 'счетчик объединенных ячеек
    For j = iFirstCol To iLastCol
 
        'Проверяем, не объединена ли эта ячейка с соседними.
        If Cells(k, j).MergeCells = True Then
            'Получаем число объединенных ячеек
            iCountColspan = Cells(k, j).MergeArea.Count
        Else
            iCountColspan = 0
        End If
 
        Set oCurrentCell = ActiveSheet.Cells(k, j)
 
        sLine = sLine & "<td"
 
        'Проверяем, нужно ли вставлять код объединения ячейки с соседними
        If iCountColspan > 1 Then
            sLine = sLine & " colspan=" & iCountColspan
            j = j + iCountColspan - 1 'пропускаем ячейки
            iCountColspan = 0
        End If
 
        'Если по центру
        If oCurrentCell.HorizontalAlignment = -4108 Then sLine = sLine & " style='text-align: center;'"
 
        sLine = sLine & ">"
 
        'Если пусто, прописываем &nbsp;
        If oCurrentCell.Text <> "" Then sValue = oCurrentCell.Text Else sValue = "&nbsp;"
 
        'Если жирный
        If oCurrentCell.Font.Bold = True Then sValue = "<b>" & sValue & "</b>"
 
        'Если курсив
        If oCurrentCell.Font.Italic = True Then sValue = "<i>" & sValue & "</i>"
 
        sLine = sLine & sValue & "</td>"
 
    Next j
    sOutput = sOutput & sLine & "</tr>"
Next k
 
 
'6. Заканчиваем таблицу
sOutput = sOutput & "</table></center></div>"
 
'7. Выводим на экран полученный HTML
UserForm1.TextBox1.Text = sOutput
UserForm1.Show
 
End Sub

Скачать книгу с макросом

Subscribe without commenting


  1. Blef
    13th Февраль 2012 в 13:24

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

    1 | 2 | 3 | 4
    5 | 6 | 7 | 8
    9 |10 |11 |12

    Как вы видите, после каждой ячейки ставится знак |. (это я сделал)
    Нужно убрать символы выводящиеся перед цифрами 1, 5, 9 и после цифр 4, 8, 12 и.т.д
    это возможно?

    Сейчас вывод такой: tut1|2|3|4|odd5|6|7|8|9|10|11|12| (перед 9, нужно как-то сделать переход на новую строку типа , но без символов)
    Очень жду ответа.

  2. Тарлюн Максим
    13th Февраль 2012 в 13:29

    @Blef
    загрузите VBA код на pastebin.com

  3. Blef
    13th Февраль 2012 в 13:55

    Загрузил, название KOD

  4. Тарлюн Максим
    13th Февраль 2012 в 14:19
  5. Blef
    13th Февраль 2012 в 14:48

    Отличная работа, ещё бы убрать лишнюю | в конце каждой строки.
    Как это сделать?

  6. Тарлюн Максим
    13th Февраль 2012 в 14:55

    Blef :

    ещё бы убрать лишнюю | в конце каждой строки.

    http://pastebin.com/WHphg24B

  7. Blef
    13th Февраль 2012 в 15:00

    Благодарю.

  8. Blef
    13th Февраль 2012 в 15:15

    В принципе всё устраивает, но лучше довести до идеала.

    В начале вывести:
    [table=head]

    Строку A6 (пример таблицы) не выводить

    В конце:
    [/table]

  9. Тарлюн Максим
    13th Февраль 2012 в 15:43
  10. Blef
    13th Февраль 2012 в 15:47

    Спасибо земляк.

  11. 7th Январь 2013 в 13:45

    Максим, сделал на базе вашего макроса аналогичный, только для экспорта выделенного диапазона ячеек.

    Код, и результаты его работы, можно посмотреть здесь:
    http://excelvba.ru/code/ExportHTML

  12. Тарлюн Максим
    7th Январь 2013 в 14:13

    @EducatedFool
    Интересное решение.

  13. Юл
    1st Июнь 2015 в 18:21

    Извините в первый раз сталкиваюсь с макросами , в пунке 7 есть возможность копировать не в буфер обмена а код по указоному пути например на сетевой ресурс \SER6\PAPKA\name.html

  14. Тарлюн Максим
    1st Июнь 2015 в 20:56

    Не уверен что такое можно.

    Хотя есть один способ. Давным давно я писал макрос, результат которого записывался в текстовый файл (например, c:\temp\report.txt)

    Open "c:\temp\report.txt" For Output As #1
    Print #1, "Hello File"
    Close #1

    Если есть доступ к удаленному ресурсу из локальной сети, то его можно подключить средствами Windows как сетевой диск (например X) и записывать результат туда.

  15. Юл
    2nd Июнь 2015 в 10:05

    а в данном конкретном примере, как экспортировать в фаил на с:, а не в буфер ? вы сталкивались с такой задачей:?

  16. Тарлюн Максим
    2nd Июнь 2015 в 10:13

    @Юл
    Скорее всего, в 7 пункте заменить те две строчки на:

    Open "c:\temp\report.txt" For Output As #1
    Print #1, sOutput
    Close #1

  17. Юл
    2nd Июнь 2015 в 10:56

    @Тарлюн Максим
    Cпасибо, огромное работает