Как преобразовать лист или ячейки Excel в таблицу HTML?

Конвертирование HTML в форматы Microsoft Excel

Необходимость преобразования HTML-таблицы в Excel может возникнуть в нескольких случаях. Может потребоваться преобразование данных веб-страниц из Интернета или HTML-файлов, которые использовались локально для других целей специальными программами. Часто конвертация осуществляется в пути. То есть таблица должна быть сначала преобразована из HTML в XLS или XLSX, затем обработана или изменена и, наконец, преобразована обратно в файл с тем же расширением, чтобы она могла выполнять свою первоначальную функцию. Действительно, работать с таблицами в Excel намного проще. Давайте узнаем, как преобразовать электронную таблицу HTML в Excel.

Парсинг нетабличных данных с сайтов

Excel может без проблем загружать табличные данные из Интернета. Надстройка Excel Power Query упрощает выполнение этой задачи. На вкладке Данные выберите команду Данные — Из Интернета, вставьте адрес нужной веб-страницы (например, основные показатели Центрального банка) и нажмите OK:

Power Query автоматически распознает все таблицы на веб-странице и отобразит их список в окне браузера:

Следующим шагом будет выбор нужной таблицы и загрузка ее в Power Query для дальнейшей обработки (кнопка Convert Data) или непосредственно в лист Excel (кнопка Load).

Согласно описанному выше сценарию, вам повезет, если ваши данные будут загружены с необходимого вам сайта.

К сожалению, существует множество сайтов, на которых при попытке загрузить Power Query таким образом, Power Query «не видит» таблиц с нужными данными, т.е. в окне Навигатора просто нет таблиц 0, 1, 2. или среди них нет таблицы с нужной нам информацией. Это может быть вызвано несколькими факторами, но самый распространенный — дизайнер сайта использовал в HTML-коде страницы не стандартную структуру тегов, а ее аналог, вложенные теги-контейнеры. Эта техника очень распространена на сайтах, но, к сожалению, Power Query не может ее распознать и загрузить данные.

Однако есть способ обойти это ограничение 😉

Для тренировки попробуем загрузить цены и описания товаров с торговой площадки Wildberries — например, книг из раздела детективов:

Загружаем HTML-код вместо веб-страницы

Сначала используйте тот же метод: выберите команду Из Интернета на вкладке Данные и введите адрес нужной страницы:

После этого вы увидите окно Навигатора, в котором нет никаких полезных таблиц, кроме непонятной Документ:

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

. а затем нажмите на значок шестеренки справа от шага «Источник», чтобы открыть его параметры:

В выпадающем меню Открыть файл как вместо HTML-страницы по умолчанию выберите Текстовый файл. Это заставит Power Query интерпретировать загруженные данные не как веб-страницу, а как обычный текст, т.е. Power Query не будет пытаться распознать HTML-теги и их атрибуты, ссылки, картинки, таблицы, а просто будет рассматривать исходный код страницы как текст.

После нажатия кнопки OK вы увидите этот HTML код (он довольно длинный, но не пугайтесь):

Ищем за что зацепиться

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

В случае с Wildberries, если прокрутить код вниз, можно легко понять простую логику:

• Строки с ценами всегда содержат этикетку нижней цены
• Строки с марками всегда содержат этикетку марки c-text-sm
• Название товара можно найти с помощью этикетки названия товара c-text-sm.

Иногда процесс поиска можно значительно упростить, используя инструменты поиска кода, доступные во всех современных браузерах. Щелкнув правой кнопкой мыши на элементе веб-страницы (например, на цене или описании товара), вы можете выбрать Inspect из контекстного меню, а затем отобразить код в удобном окне прямо рядом с содержимым страницы:

Фильтруем нужные данные

Теперь давайте отфильтруем нужные нам строки в коде страницы в соответствии с найденными символами самым обычным образом. Для этого в окне Power Query [1] выберите Text Filters — Contains, переключитесь в режим Advanced [2] и введите наши критерии:

Используйте кнопку Добавить предложение [3] для добавления условий. И не забудьте установить для всех условий значение OR вместо AND в выпадающих списках слева [4] — иначе фильтрация просто не будет работать.

После нажатия кнопки OK на экране останутся только строки, содержащие необходимую нам информацию:

Чистим мусор

Просто удалите все это удобным для вас способом (их много). Например, таким образом:

1. Удалить заменой на пустоту начальный тег: <span > через команду Главная — Замена значений (Home — Replace values) .
2. Разделить получившийся столбец по первому разделителю » > » слева командой Главная — Разделить столбец — По разделителю (Home — Split column — By delimiter) и затем ещё раз разделить получившийся столбец по первому вхождению разделителя » < » слева, чтобы отделить полезные данные от тегов:

Разбираем блоки по столбцам

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

Вы можете сделать это преобразование очень легко, буквально одной строкой кода на встроенном в Power Query языке M. Для этого нажмите на кнопку fx на панели формул (если вы ее не видите, активируйте ее на вкладке Вид) и введите следующее утверждение:

= Table.FromRows(List.Split( #»Замененное значение1″ [Column1.2.1] , 3 ))

Здесь — Список. В Шаге № «Подставленное значение1» мы разбиваем столбец с именем Column1.2.1 на три части одинаковых ячеек, и так далее для остальной части Таблицы. Вложенный список вложенных списков преобразуется обратно в таблицу, уже имеющую три столбца:

После этого остается только установить числовые форматы, переименовать столбцы и расположить их в нужном порядке. Затем вы можете выгрузить красивый результат в Excel, нажав Главная — Закрыть и загрузить.

3. Используйте онлайн-инструменты для преобразования Excel в таблицу HTML.

Существуют различные онлайн-инструменты, позволяющие конвертировать документы Excel в HTML. Вам нужно добавить свой лист Excel, и он покажет вам HTML-код. Есть два сайта, которые мне нравятся. Давайте рассмотрим шаги для обоих вариантов.

Метод 1. Использование инструментов преобразования

Шаг 2: С помощью кнопки Обзор перейдите к документу Excel, который необходимо преобразовать. Вы также можете перетащить файл.

Кнопка «Конвертировать» активирована. Нажмите ее, чтобы начать процесс преобразования.

Шаг 4: Нажмите на кнопку Загрузить файл, чтобы загрузить его на свой компьютер.

Если вы хотите просмотреть HTML-код загруженного файла, выполните действия, описанные выше в разделе «Просмотр HTML-кода веб-страницы». Чтобы просмотреть код страницы, откройте ее в обычном текстовом редакторе.

Метод 2: использование инструментов Beautify

Этот веб-сайт генерирует более чистый код, чем приведенный выше. Здесь вы найдете базовую таблицу.

Это делается следующим образом.

Затем нажмите кнопку Обзор. Выберите файл, который необходимо преобразовать.

Сгенерированный HTML-код появится в поле ниже. Вы можете скопировать и вставить его или нажать на кнопку «Загрузить», чтобы сохранить его в виде TXT-файла.

Вставка обновляемой информации

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

Рисунок 176.1. Этот сайт содержит информацию, которая часто меняется

1. Выберите Данные ► Получение внешних данных ► Из Интернета для открытия диалогового окна Создание веб-запроса.
2. В поле Адрес введите URL сайта и нажмите кнопку Пуск. Для этого примера URL-адрес веб-страницы, показанной на рис. 176.1, будет таким: http://cbr.ru. Обратите внимание, что диалоговое окно Создание веб-запроса содержит мини-браузер (Internet Explorer). Вы можете переходить по ссылкам и посещать сайты, пока не найдете данные, которые вас заинтересуют. Когда веб-страница отображается в окне Создание веб-запроса, вы видите одну или несколько желтых стрелок, которые соответствуют различным элементам на веб-странице.
3. Щелкните на желтой стрелке, и она превратится в зеленый флажок, который указывает, что данные этого элемента будут импортированы. Вы можете импортировать столько элементов, сколько нам нужно. Для этого
   примера я щелкну на стрелке рядом с таблицей курсов.
4. Нажмите кнопку Импорт для открытия диалогового окна Импорт данных.
5. В окне Импорт данных укажите место для импортированных данных. Это может быть ячейка в существующем или новом листе.
6. Нажмите кнопку ОК, и Excel импортирует данные (рис. 176.2).

Рисунок 176.2. Данные, импортированные с веб-страницы

Импортированные данные по умолчанию являются веб-запросами. Чтобы обновить информацию, щелкните правой кнопкой мыши любую ячейку в импортированном диапазоне и выберите в контекстном меню пункт Обновить. Если вы не хотите создавать обновляемый запрос, укажите это в шаге 5 предыдущего списка действий. В окне Импорт данных нажмите Свойства и снимите флажок Сохранить определение запроса.

Ввод формулы

Чтобы ввести формулу, перейдите на лист Sales Amounts и щелкните по ячейке B5.

Нажмите Enter.

Ввод функции VLOOKUP

Результат должен быть равен 40. Для получения дополнительной информации еще раз щелкните на B5. Поместите курсор мыши на точку автозаполнения в правом нижнем углу ячейки, чтобы курсор изменил форму на перекрестие.

Когда вы поместите курсор мыши на точку в правом нижнем углу ячейки, он превратится в перекрестие Автозаполнения.

Значения под колонкой необходимо заполнить дважды.

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

При желании можно запустить VLOOKUP на последующих столбцах, чтобы извлечь другие поля, такие как имя или состояние.