|
Если вам приходится часто обновлять табличные
данные на своем сайте, вы наверняка сталкивались с проблемой
конвертации таблицы Excel’а в html. После пары дней
редактирования ручками того мусора, который делает Excel
при сохранении страницы как web-документа (Excel к
тому же тулит еще и файлы каскадных таблиц стилей и xml),
я решил, что будет проще написать такой конвертор самому.
Тем более что делов то на 5 минут ?
Итак, откроем новый документ Excel’а, войдем в редактор
VBA и нарисуем процедуру под названием ConvertXlsHtml().
Это и будет наш макрос конвертации.
Разобьем задачу на несколько подзадач. Во-первых, нам необходимо
создать новый файл с расширением html, куда мы будет
вписывать теги и текст, а во-вторых, необходимо должным образом
проанализировать таблицу, а именно получить характеристики
каждой ячейки и текста в ней. Первая задача решается просто
при помощи FSO (File System Object). Код создания файла
приведен в листинге 1.
ЛИСТИНГ 1.
Sub ConvertXlsHtml()
Dim fso As New
FileSystemObject
Dim fs As
TextStream
Dim strPath As
String
strPath = "D:\html" & Replace(Time, ":",
"_") & ".html"
Set fs = fso.CreateTextFile(strPath,
True)
fs.WriteLine ("<html>")
fs.WriteLine ("<body>")
fs.WriteLine ("<table border=""0""
bgcolor=""#000000"">")
‘здесь будет код обработки ячеек (листинг
2)
Set fs = Nothing
Set fso = Nothing
End Sub
В переменную strPath мы передаем директорию,
где будет размещен создаваемый файл и его имя. Функция Replace(Time,
":", "_") берет текущее время в формате
чч:мм:сс и возвращает строку в формате чч_мм_сс. То есть наш
файл будет иметь имя html10_40_49.html, например. Я использую
время в названии файла, чтобы случайно не создать два файла
с одним именем. Выбор названия файла остается всецело за вами.
Как вы уже, наверное, поняли строка кода fs.WriteLine ("<html>")
впишет в созданный нами файл тег <html>. Причем следующая
запись начнется с новой строки. Аналогичным образом мы будем
строить весь html-файл.
Теперь займемся обработкой данных таблицы. Я не вижу смысла
обрабатывать всю рабочую область в поисках таблицы, поэтому
предлагаю следующее: требуемую таблицу мы выделяем мышкой,
а наш код заставляем работать только с выделенной областью.
Для этого нам понадобится несколько дополнительных переменных.
ЛИСТИНГ 2.
Dim ColCountFirst
As Integer ‘номер
первой колонки выделенного диапазона
Dim ColRowsFirst As
Integer ‘номер первой строки выделенного
диапазона
Dim ColCountLast As
Integer ‘номер последней колонки
выделенного диапазона
Dim ColRowsLast As
Integer ‘номер последней строки
выделенного диапазона
Dim i As Integer
‘ рабочие переменные циклов обработки
ячеек
Dim j As Integer
‘
Dim strAlignCell As
String ‘выравнивание ячейки
Dim intColorIndex As
Integer ‘индекса цвета
Dim intFontSize As
Integer ‘размер шрифта
Dim strFontFace As
String ‘имя шрифта
Dim strColor As
String ‘цвет шрифта или ячейки
Dim strBoldBegin As
String 'открывающий тег <b>
Dim strBoldEnd As
String ‘закрывающий тег </b>
Dim strItalicBegin As
String 'открывающий тег <i>
Dim strItalicEnd As
String ‘закрывающий тег </i>
ColRowsFirst = Selection.Row
ColCountFirst = Selection.Column
ColCountLast = Selection.Columns.Count
ColRowsLast = Selection.Rows.Count
For i = ColRowsFirst
To ColRowsLast + ColRowsFirst - 1
fs.WriteLine ("<tr>") ‘начинаем
новую строку таблицы в нашем html-файле
For j = ColCountFirst To
ColCountLast + ColCountFirst - 1
''*****************************ОПРЕДЕЛЯЕМ ПАРАМЕТРЫ ЯЧЕЙКИ*****************
''*****************************Выравнивание ячейки*********************************
If ActiveSheet.Cells(i, j).HorizontalAlignment
= xlRight Then
strAlignCell = "Right"
ElseIf ActiveSheet.Cells(i, j).HorizontalAlignment
= xlLeft Then
strAlignCell = "Left"
Else
strAlignCell = "Center"
End If
''*******************************Цвет
ячейки****************************************
intColorIndex = Cells(i, j).Interior.ColorIndex
strColor = GetColorIndex(intColorIndex)
If strColor = "nothing"
Then
strColor = "White"
End If
''***************************************Выводим
тег ячейки таблицы*****************
fs.WriteLine (vbTab & "<td bgcolor="""
& strColor & """ align="""
& strAlignCell & """>")
''*******************************ОПРЕДЕЛЯЕМ
ПАРАМЕТРЫ ТЕКСТА В ЯЧЕЙКЕ*****
''*******************************Размер шрифта*************************************
If Cells(i, j).Font.Size <= 10
Then
intFontSize = 2
ElseIf Cells(i, j).Font.Size >
10 And Cells(i, j).Font.Size <
14 Then
intFontSize = 3
Else
intFontSize = 4
End If
''*******************************Имя
шрифта****************************************
strFontFace = Cells(i, j).Font.Name
''*******************************Цвет
шрифта***************************************
intColorIndex = Cells(i, j).Font.ColorIndex
strColor = GetColorIndex(intColorIndex)
If strColor = "nothing"
Then
strColor = "black"
End If
''*******************************BOLD
шрифт***************************************
If ActiveSheet.Cells(i, j).Font.Bold
= True Then
strBoldBegin = "<b>"
strBoldEnd = "</b>"
Else
strBoldBegin = ""
strBoldEnd = ""
End If
''******************************ITALIC
шрифт***************************************
If ActiveSheet.Cells(i, j).Font.Italic
= True Then
strItalicBegin = "<i>"
strItalicEnd = "</i>"
Первым делом нам необходимо определить границы
выделенной области, для этого мы ввели переменные ColCountFirst,
ColRowsFirst, ColCountLast, ColRowsLast и присвоили им соответствующие
значения. Затем задаем два цикла For по строкам и столбцам
(один вложен в другой), чтобы выбрать последовательно все
ячейки в выделенном диапазоне. В каждой отдельной ячейке мы
определяем выравнивание и цвет фона, и присваиваем эти значения
переменным strAlignCell и strColor. Выравнивание ячейки не
требует особых мозговых усилий ?, в то время как получение
цвета ячейки (как и шрифта), требует пояснения. Дело в том,
что Excel (к сожалению) не работает с цветом так, как это
делают все графические редакторы. Говоря просто, вам не удастся
получить параметр цвета в виде шеснадцатиричного число (#05c6ff45,
например). По крайней мере, у меня не получилось ?. Вместо
этого в Excel содержит индексы цвета (от 1 до 40, а может
и больше…), которым эти самые цвета соответствуют, и именно
эти индексы мы можем получить. Строка кода «intColorIndex
= Cells(i, j).Interior.ColorIndex» как раз и призвана сделать
это. Далее, зная индекс цвета и цвет, которому он соответствует,
мы можем вернуть цвет в уже нужном нам формате. Поскольку
нам придется возвращать название (или шеснадцетиричное представление)
цвета два раза (для ячейки и для шрифта), я вынес этот код
в отдельную функцию GetColorIndex (листинг 3).
ЛИСТИНГ 3.
Function GetColorIndex(intColorIndex
As Integer) As
String
If intColorIndex = 1 Then
GetColorIndex = "black"
ElseIf intColorIndex = 2 Then
GetColorIndex = "white"
ElseIf intColorIndex = 3 Then
GetColorIndex = "red"
ElseIf intColorIndex = 6 Or
intColorIndex = 36 Then
GetColorIndex = "yellow"
ElseIf intColorIndex = 10 Or
intColorIndex = 50 Then
GetColorIndex = "green"
ElseIf intColorIndex = 5 Or
intColorIndex = 41 Or intColorIndex
= 33 Then
GetColorIndex = "blue"
ElseIf intColorIndex = 48 Or
intColorIndex = 15 Then
GetColorIndex = "#9B9A9A" ‘здесь
индексы для серого цвета
Else
GetColorIndex = "nothing"
End If
End Function
Как видите, все просто. Вы можете забить в эту функцию все
индексы цветов Excel, и подобрать им более привлекательное
соответствие (как я, например, это сделал для серого цвета),
а можете ограничиться только своими потребностями. Тут я еще
добавлю, что в случае, если мы не предусмотрели какой-либо
индекс, функция вернет значение "nothing". И далее
в программе, если функция вернула это значение, мы ставим
для ячейки белый цвет, а для шрифта – черный. Чтобы, так сказать,
по умолчанию у нас не было бардака.
Определившись с этим нам остается построить в нашем html-файле
тег <td …>, что мы спокойно и делаем в строке кода «fs.WriteLine
(vbTab & "<td bgcolor=""" &
strColor & """ align="""
& strAlignCell & """>")». Вас
смущают кучи парных кавычек? Это для того, чтобы значения,
которые мы передаем через переменные, в самом html-файле были
«одеты» в парные кавычки.
Определение размера шрифта, так же как и определение цвета,
имеет тенденцию к усложнению. Если вас не устраивает тот вариант,
который я предлагаю, вы можете усложнить код настолько, насколько
считаете нужным.
Определение имени шрифта не должен вызвать у вас затруднение.
Определение цвета шрифта происходит по тем же правилам, что
и определение цвета ячейки. После этого мы смотрим, присутствуют
ли такие параметры шрифта, как bold (выделенный шрифт) и italic
(наклонный шрифт), и если да, то в переменные strBoldBegin,
strBoldEnd, strItalicBegin и strItalicEnd передаем теги этого
форматирования. В противном случае, оставляем переменные пустыми.
Получив все переменные шрифта, может приступать к построению
тега <font …> и передачи в файл содержимого ячейки.
Когда будут разнесены все ячейки данной строки в выделенном
диапазоне мы закрываем строку таблицы (fs.WriteLine ("</tr>"))
и переходим к следующей строке.
Вот и все. Для примера я привожу пару картинок.
На рисунке 1 таблица, сделанная в Excel, а на рисунке 2
конвертированная нашим макросом в html.
Рис.1
Рис.2
Да, чуть не забыл. Если вы вдумчивые и смышленые
ребята, то должны были обратить внимание на то, что я ни одним
словом не обмолвился об объединенных ячейках. Действительно,
наш макрос не обрабатывает объединенные ячейки. И если они
присутствуют в таблицах, то результат будет не совсем такой,
который вы ожидаете. Ну что ж, у вас есть поле для деятельности
и самосовершенствования ?. У меня не возникало пока потребности
обрабатывать подобным образом объединенные ячейки, возможно,
когда-нибудь и возникнет… Сразу напишу.
Вернуться
к разделу статей
|
