До недавнего времени пользовался для конвертации xls файлов Excel на сервере.
Порядок был такой:
был написан VBA скрипт, положен отдельно в формате .bas. Скрипт выдавал на выходе XML файл нужной структуры. Данные брались из самого xls файла + из внешнего xls файла SQL запросом. Потом скрипт модернизаровал и внешний xls файл заменился БД MySQL (на базе OpenServer)
VBS скрипт на сервере, брал входящий xls файл, внедрял в него скрипт (bas файл) и запускал.
Было куча глюков и прочих неудобных вещей изза запуска Excel на сервере. В случае ошибки, приходилось лезть на сервер, убивать процесс...
Сегодня переписал скрипт по обработке XLS файлов без использования Excel.
Привожу пример кода. Максимально описал и расписал.
Именованные ячейки:
Имя файла XML на выходе в параметрах тикета
XLS файл
П.С.
Библиотека ACE.OLEDB у меня х64
Потому в настройках Run Script стоит тип скрипта для х64.
Порядок был такой:
был написан VBA скрипт, положен отдельно в формате .bas. Скрипт выдавал на выходе XML файл нужной структуры. Данные брались из самого xls файла + из внешнего xls файла SQL запросом. Потом скрипт модернизаровал и внешний xls файл заменился БД MySQL (на базе OpenServer)
VBS скрипт на сервере, брал входящий xls файл, внедрял в него скрипт (bas файл) и запускал.
Было куча глюков и прочих неудобных вещей изза запуска Excel на сервере. В случае ошибки, приходилось лезть на сервер, убивать процесс...
Сегодня переписал скрипт по обработке XLS файлов без использования Excel.
Привожу пример кода. Максимально описал и расписал.
Код:
'05 сентября 2019
'Доманский Александр
'Скрипт для парсинга XLS файла без участия Excel
'Плюс чтение данных из MySQL
'Запись XML файла на выходе
Function Main(inputs, outputFolder, params)
Dim inputFile, outputFile
'Из БД'
Dim Distorsia, Zub,
'Из XLS'
Dim JobNamber, CustomerName, CutTools
'адрес и имя первого входящего файла
inputFile = inputs(0)
'Название файла берется из первого значение списка параметров тикета Run Script
outputFile = outputFolder & "\" & params(0)
WScript.Echo inputFile
'WScript.Echo outputFile
'Читаем данные из XLS файла
Dim connStr, objConn, getNames
connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & inputFile & ";Extended Properties=""Excel 12.0 xml;HDR=No;IMEX=2;"";"
Set objConn = CreateObject("ADODB.Connection")
'Open Connection
objConn.open connStr
'Define recordset and SQL query
'тут мы через SQL запрос вытаскиваем данные из ИМЕННЫХ ячеек экселевского файла. Всё это в массив
Set rs = objConn.execute("SELECT * FROM JobNamber,CustomerName,CutTools")
'присваиваем значения переменным из массива
DO WHILE NOT rs.EOF
JobNamber = rs.Fields(0)
CustomerName = rs.Fields(1)
CutTools = rs.Fields(2)
rs.MoveNext
Loop
'Close connection XLS and release objects
objConn.Close
Set rs = Nothing
Set objConn = Nothing
' подключиться к БД:
' зная значение переменной CutTools из xls файла, получаем остальные данные из MySQL
Dim Connection, ConnectString, Sql, rec
'база данных pprint ; таблица tools
ConnectString = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;PORT=3306;Database=pprint;User=root;Password=;charset=utf8;Option=3;"
Set Connection = CreateObject("ADODB.Connection")
Connection.Open ConnectString
' запрос к MySQL. Результат в массив.
Sql = "SELECT * FROM tools WHERE IDCUT= '" & CutTools & "'"
Set rec = Connection.Execute(Sql)
With rec
'записываем данные из запроса в переменные.
'Все запятые переводим в точки.
Zub = .Fields("zub").Value
Distorsia = Replace(.Fields("HDist").Value, ",", ".")
End With
'Закрываем соединение с БД
Connection.Close
'формируем XML
Dim rootNode, subNode, xmlDoc, commentItem, Fragment
Set xmlDoc = CreateObject("Msxml2.DOMDocument")
'Создание объявления XML
xmlDoc.appendChild(xmlDoc.createProcessingInstruction("xml", "version='1.0' encoding='utf-8'"))
'Создание корневого элемента
Set rootNode = xmlDoc.CreateElement("JOBS")
xmlDoc.AppendChild rootNode
Set subNode = rootNode.appendChild(xmlDoc.createElement("JobNamber"))
subNode.text = JobNamber
Set subNode = rootNode.appendChild(xmlDoc.createElement("CustomerName"))
subNode.text = CustomerName
Set subNode = rootNode.appendChild(xmlDoc.createElement("CutTools"))
subNode.text = CutTools
Set subNode = rootNode.appendChild(xmlDoc.createElement("Zub"))
subNode.text = Zub
Set subNode = rootNode.appendChild(xmlDoc.createElement("Distorsia"))
subNode.text = Distorsia
'* -- написание XML-файла с отступом для более легкого использования независимо от того, какая программа использовалась для просмотра или редактирования файла -- *'
Dim rdr, wrt, oStream
Set rdr = CreateObject("MSXML2.SAXXMLReader")
Set wrt = CreateObject("MSXML2.MXXMLWriter")
Set oStream = CreateObject("ADODB.STREAM")
oStream.Open
oStream.Charset = "UTF-8"
wrt.Indent = True
wrt.Encoding = "UTF-8"
wrt.Output = oStream
Set rdr.ContentHandler = wrt
Set rdr.ErrorHandler = wrt
rdr.Parse xmlDoc
wrt.Flush
'запись xml файла
oStream.SaveToFile outputFile, 2
Set rdr = Nothing
Set wrt = Nothing
Main = "OK"
End function
Dim inputs()
Dim outputFolder
Dim params()
Main inputs, outputFolder, paramsИменованные ячейки:
Имя файла XML на выходе в параметрах тикета
XLS файл
П.С.
Библиотека ACE.OLEDB у меня х64
Потому в настройках Run Script стоит тип скрипта для х64.
Это до тех пор пока память на сервере не потечет, если библиотека большая понадобится.
