Tarlyun blog

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

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

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

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

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

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

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

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

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

Интерфейс

Блок № 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>

Код

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

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