Парсинг xml в vba excel: руководство для начинающих

Извлечение данных из html

В понятие «парсинг», кроме извлечения текстового содержимого сайтов, html-страниц или файлов, входит поиск и извлечение конкретных данных из всего полученного текстового содержимого.

Парсинг содержимого тегов

Извлечение содержимого тегов с помощью метода getElementsByTagName объекта HTMLFile:

С помощью этого кода извлекается текст, расположенный между открывающим и закрывающим тегами. В примере — это текст 6-го абзаца (p) между 5-й (нумерация с 0) парой отрывающего

Примеры тегов, используемых в html: «p» , «title» , «h1» , «h2» , «table» , «div» , «script» .

Пример извлечения содержимого тега «title» :

Парсинг содержимого Id

Извлечение текстового содержимого html-элементов, имеющих уникальный идентификатор — Id, с помощью метода getElementById объекта HTMLFile:

Для реализации представленных здесь примеров могут понадобиться дополнительные библиотеки. В настоящее время у меня подключены следующие (к данной теме могут относиться последние шесть):

Visual Basic For Applications
Microsoft Excel 16.0 Object Library
OLE Automation
Microsoft Office 16.0 Object Library
Microsoft Forms 2.0 Object Library
Ref Edit Control
Microsoft Scripting Runtime
Microsoft Word 16.0 Object Library
Microsoft Windows Common Controls 6.0 (SP6)
Microsoft ActiveX Data Objects 6.1 Library
Microsoft ActiveX Data Objects Recordset 6.0 Library
Microsoft HTML Object Library
Microsoft Internet Controls
Microsoft Shell Controls And Automation
Microsoft XML, v6.0

С этим набором библиотек все примеры работают. Тестирование проводилось в VBA Excel 2016.

Источник

Как начать работать с парсингом XML в VBA Excel

Импорт необходимых библиотек: Для работы с XML в VBA Excel вам понадобится импортировать необходимые библиотеки. Нажмите Alt + F11, чтобы открыть редактор VBA, затем выберите «Инструменты» > «Ссылки» и найдите «Microsoft XML, v6.0» в списке. Убедитесь, что галочка рядом с ним установлена и нажмите «ОК». Это позволит использовать объекты XML в вашем коде.
Загрузка XML файла: Первым шагом в парсинге XML в VBA Excel является загрузка XML файла. Для этого вы можете использовать объект DOMDocument из библиотеки Microsoft XML. Создайте новый экземпляр объекта DOMDocument и используйте его метод Load для загрузки XML файла.
Чтение данных из XML: После загрузки XML файла вы можете начать читать данные из него. В XML файле данные обычно организованы с помощью тегов и атрибутов. Используйте методы и свойства объекта DOMDocument для доступа к значениям тегов и атрибутов XML.
Обработка данных: После чтения данных из XML файла вы можете начать их обработку. Это может включать в себя анализ данных, фильтрацию или сохранение в другой формат. В VBA Excel вы можете использовать условные операторы, циклы и другие инструменты для обработки данных.
Отображение данных: После обработки данных вы можете отобразить их в Excel. Для этого вы можете использовать методы и свойства объекта Worksheet для записи данных в ячейки и форматирования таблицы. Также вы можете использовать графики и диаграммы для наглядного представления данных.

С помощью этих шагов вы можете начать работать с парсингом XML в VBA Excel. За дополнительной информацией и примерами кода вы можете обратиться к документации Microsoft XML или найти ресурсы и учебные материалы в Интернете.

Reading XML with the XmlDocument class

XML File content
<Store>
<Product>
<Product_id>1</Product_id>
<Product_name>Product 1</Product_name>
<Product_price>1000</Product_price>
</Product>
<Product>
<Product_id>2</Product_id>
<Product_name>Product 2</Product_name>
<Product_price>2000</Product_price>
</Product>
<Product>
<Product_id>3</Product_id>
<Product_name>Product 3</Product_name>
<Product_price>3000</Product_price>
</Product>
<Product>
<Product_id>4</Product_id>
<Product_name>Product 4</Product_name>
<Product_price>4000</Product_price>
</Product>
</Store>

Click here to download Product.xml

Parse XML with XmlDocument

The XmlNodeList Class proves to be a valuable tool for organizing and manipulating a sequence of nodes within XML data. The use of XPath expressions in conjunction with the XmlNode.SelectNodes method enhances the ease and efficiency of locating nodes based on specific criteria, making it a fundamental component of XML data processing and management. The following program describes how to use the XmlDocument class to parse an XML document in C#.

C# XmlDocument example
using System;
using System.Data;
using System.Xml;
using System.IO;
using System.Text;
using System.Windows.Forms;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
XmlDocument doc = new XmlDocument();
doc.Load(«d:\\product.xml»);
XmlNodeList nodes = doc.DocumentElement.SelectNodes(«/Store/Product»);
string product_id = «», product_name = «», product_price=»»;
foreach (XmlNode node in nodes)
{
product_id = node.SelectSingleNode(«Product_id»).InnerText;
product_name = node.SelectSingleNode(«Product_name»).InnerText;
product_price = node.SelectSingleNode(«Product_price»).InnerText;
MessageBox.Show(product_id + » » + product_name + » » + product_price);
}
}
}
}

VB.Net XmlDocument example

This following program describes how to use the XmlDocument class to parse an XML document in VB.Net.

Imports System.Xml
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim doc As New XmlDocument()
doc.Load(«d:\product.xml»)
Dim nodes As XmlNodeList = doc.DocumentElement.SelectNodes(«/Store/Product»)
Dim product_id As String = «», product_name As String = «», product_price As String = «»
For Each node As XmlNode In nodes
product_id = node.SelectSingleNode(«Product_id»).InnerText
product_name = node.SelectSingleNode(«Product_name»).InnerText
product_price = node.SelectSingleNode(«Product_price»).InnerText
MessageBox.Show(product_id & » » & product_name & » » & product_price)
Next
End Sub
End Class

Conclusion

The XmlDocument Class is a versatile tool for working with XML documents, offering the ability to load XML content into memory and providing efficient navigation and querying capabilities. By utilizing XmlDocument, developers gain a powerful toolset for effectively managing and processing XML data, making it a valuable choice for a wide range of XML-related tasks.

Часть 2. Реализация решения под MacOS и Excel 64-bit

В виду того, что работал я на двух машинах под управлением разных ОС, хотелось, чтобы решение было универсальным. В итоге, собрав по крупицам информацию по интернет-форумам с данной тематикой у меня вышло следующее решение. Принцип работы его остается схожим, а изменения были внесены в часть, где использовалась стандартная библиотека WindowsMsxml2.XMLHTTP.6.0, которой в MacOS не было по понятным причинам.

Чтобы обойти данное ограничение, был выбран единственный рабочий подход через cUrl, exec и функции. Данное решение точно работает под версией MacOS 10.14 и Excel 16.51. Функция ниже, в том или ином виде, встречается на различных форумах, однако на текущих версиях софта – не работает. В итоге, после небольших правок получили рабочий вариант:

Была отлажена функция вызова ExecShell:

И написаны отдельные функции для работы с различным методами GET / PUT / POST, которые на входе принимают URL и параметры):

Так как мы заменяем библиотеку Msxml2.XMLHTTP.6.0 – поменялась реализация макросов в этой части: мы заменили Msxml2 на написанные выше функции и получили следующее:

В итоге, у меня получилось аналогичное windows по работе и функционалу решение для MacOS c использованием Excel 64-bit.

На просторах интернета я не нашел какого-то сборного и единого описания, только фрагменты кода и подходов, которые в большинстве случаев не работали полностью или частично. Поэтому решил объединить все в рабочее решение и выложить на хабр для истории.

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

Файл-пример, который можно потыкать, пока жив сервер и «бессрочный» токен:

Источник

Зачем нужен парсинг XML

Парсинг XML — это процесс анализа и извлечения данных из XML-документа. Парсер XML разбирает документ, разделяя его на различные элементы и атрибуты, и предоставляет доступ к этим данным для дальнейшего использования.

Парсинг XML в VBA Excel имеет большое значение для работы с данными, хранящимися в формате XML. С помощью парсинга XML можно извлечь только нужные данные из большого XML-документа, игнорируя ненужные элементы и атрибуты. Это упрощает обработку информации и ускоряет процесс работы с данными.

Более того, парсинг XML позволяет сопоставить структуру XML-документа с соответствующей структурой в VBA Excel, что упрощает работу с данными и позволяет использовать функции Excel для анализа и обработки информации.

Парсинг XML также обеспечивает возможность автоматизации задач, связанных с обработкой данных в формате XML. Например, вы можете создать макрос VBA, который автоматически анализирует и обрабатывает XML-документ, основываясь на определенных условиях и критериях.

В целом, парсинг XML в VBA Excel является мощным инструментом, который расширяет возможности работы с данными в формате XML и упрощает обработку информации. Он позволяет извлекать только необходимые данные из XML-документа, связывать структуру XML с Excel, а также автоматизировать задачи, связанные с обработкой данных в формате XML.

Примеры использования парсинга XML в VBA Excel

Пример 1: Парсинг XML-файла с использованием DOM (Document Object Model)

DOM — это стандарт для представления и работы с XML-документами в виде дерева объектов. С помощью DOM можно достать различные узлы и атрибуты XML-документа.

Вот пример кода, демонстрирующий парсинг XML-файла с использованием DOM:

Пример 2: Использование парсинга XML для сохранения данных в Excel

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

Пример 3: Фильтрация данных при парсинге XML

Парсинг XML позволяет фильтровать данные по определенным условиям. В следующем примере мы парсим XML-файл, содержащий информацию о продуктах, и выводим только те продукты, у которых цена больше 10:

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

Using MSXML in VBA Macros

I created XML files for my menus and an XSL file to transform them into HTML.
Here is the code from my MenuFromXML Class.

Private WithEvents mySiteScanner As SiteScanner
Private strMainMenu As String
Private strNoteBookMenu As String
Private strWroxMenu As String

Private Sub mySiteScanner_WebPage(aPage As PageWindow)
    InsertMenu
    aPage.Close True
End Sub

Public Sub UpdateMenuFromXML()
    GetMenus
    Set mySiteScanner = New SiteScanner
    mySiteScanner.Recursive = True
    mySiteScanner.EnumPageWindows
End Sub

Private Sub GetMenus()
    Dim source As MSXML2.DOMDocument30
    Set source = New MSXML2.DOMDocument30
    Dim style As MSXML2.DOMDocument30
    Set style = New MSXML2.DOMDocument30
    
    style.Load "c:\web3\spider\_xml\menu3.xsl"
    
    source.Load "c:\web3\spider\_xml\mmenu.xml"
    strMainMenu = source.transformNode(style.documentElement)
    
    source.Load "c:\web3\spider\_xml\nbmenu.xml"
    strNoteBookMenu = source.transformNode(style.documentElement)
    
    source.Load "c:\web3\spider\_xml\wmenu.xml"
    strWroxMenu = source.transformNode(style.documentElement)
End Sub

Private Sub InsertMenu()
    Dim fpDoc As IHTMLDocument2
    Set fpDoc = ActiveDocument
    Dim myTable As IHTMLTable
    Dim colTables As IHTMLElementCollection
    Set colTables = fpDoc.all.tags("table")
    Dim sepDivs As IHTMLElementCollection
    Dim bIsChanged As Boolean
    For Each myTable In colTables
        bIsChanged = False
        If myTable.Id = "menu" Or myTable.Id = "mmenu" Then
            myTable.outerHTML = strMainMenu
            bIsChanged = True
        Else
            If myTable.Id = "nbmenu" Then
                myTable.outerHTML = strNoteBookMenu
                bIsChanged = True
            Else
                If myTable.Id = "wmenu" Then
                    myTable.outerHTML = strWroxMenu
                    bIsChanged = True
                End If
            End If
        End If
        If bIsChanged Then
            Set sepDivs = fpDoc.all.tags("div")
            Dim myDiv As IHTMLDivElement
            Dim n As Integer
            n = sepDivs.Length - 1
            For j = n To 0 Step -1
                Set myDiv = sepDivs(j)
                If myDiv.Id = "separator" Then
                    myDiv.outerHTML = "<hr>"
                End If
            Next
        End If
    Next
End Sub

Here is the Macro which uses the MenuFromXML Class.

Sub MenuUpdate()
    Dim myMenu As New MenuFromXML
    myMenu.UpdateMenuFromXML
End Sub

One problem I encountered was using <hr></hr> in my XSL
file. This would cause the VBA to fail when executing the line myTable.outerHTML
= strMainMenu. It looks like FrontPage considers <hr></hr>
to be invalid HTML. You can’t just put <hr> in your XSL file,
because it would not be a well formed document and the parser would flag an error.
My solution for now is to use a <div id=»separator»></div> to my
XSL file. In the VBA, I replace this div with an <hr> tag.
The VBA must replace the <div> tags in the collection in reverse
order, because they will disappear as the change is made, changing the contents
of the collection. Using the normal For Each construct to work through
the collection will cause the change to be applied to every other member of the
collection.

Some improvements for the future: The section of code which parses
the XML files and transforms them, needs to be more generic. The file names
should be obtained from a configuration file, written in XML. I should
go back to using <hr></hr> in the XSL file, and remove the
</hr> from the string before assigning it to myTable.outerHTML.
This would make the XML/XSL more usable in other environments. All workarounds
for FrontPage limitations should be kept in the VBA Macros and not affect the
nature of the XML/XSL files. It is also time to start using XSLT.

Scanning all pages in the site

Парсинг xml в vba excel: простое руководство для начинающих

Извлечение данных из html

Парсинг содержимого тегов

Парсинг содержимого Id

Рекомендации для начинающих парсеров XML в VBA Excel

Как начать работать с парсингом XML в VBA Excel

Reading XML with the XmlDocument class

Parse XML with XmlDocument

Conclusion

Часть 2. Реализация решения под MacOS и Excel 64-bit

Зачем нужен парсинг XML

Примеры использования парсинга XML в VBA Excel

Using MSXML in VBA Macros

Извлечение данных из html

Парсинг содержимого тегов

Парсинг содержимого Id

Рекомендации для начинающих парсеров XML в VBA Excel

Как начать работать с парсингом XML в VBA Excel

Reading XML with the XmlDocument class

Parse XML with XmlDocument

Conclusion

Часть 2. Реализация решения под MacOS и Excel 64-bit

Зачем нужен парсинг XML

Примеры использования парсинга XML в VBA Excel

Using MSXML in VBA Macros

Похожие записи:

Похожие записи: