jdelaney

Create Excel file through "Microsoft.XMLDOM"

5 posts in this topic

#1 ·  Posted (edited)

I'm basing the basics (mostly just the XML structure) of this script off one of FireFox's: '?do=embed' frameborder='0' data-embedContent>>

I felt the need to make it more flexible, via the Microsoft.xmldom.

Using this object, not only can you create excel XML files, you can also update them.  XPaths are used for everything, so it's easy to add additional rows/columns/sheets on the fly...I'll add another function to read in an already present file in a bit (just added in), but that function would be as simple as loading the contents into the object.

Multiple internal functions, but all you need are:

Func EXml_LoadFile($sCallersFile)

Func EXml_CreateFile()

Func EXml_AddCell($oCallersXML,$sCallersSheetName,$iCallersCellCol,$iCallersCellRow,$sCallersCellData)...this one creates the cell, or updates it if already present

Func EXml_SaveFile($oCallersXML,$sCallersFileName)

Example Usage

#include "EXml.au3"

; Set for overwrite of variable
;~ Global $gsEXml_DocProp_Author = "someAuthor"
;~ Global $gsEXml_DocProp_LastAuthor = "LastAuthorText"
;~ Global $gsEXml_DocProp_Created = "CreatedText"
;~ Global $gsEXml_DocProp_Company = "CompnayText"
;~ Global $gsEXml_DocProp_Version = "VersionText"

$oXML = EXml_CreateFile()
; Can load, if the XML file is already present
;~ $oXML = EXml_LoadFile(@DesktopDir & "\test.xml")
; Add cell params: $oXMl, $sSheetName, $iColumn, $iRow, $sCellContents
EXml_AddCell($oXML,"SheetName",5,10,"SomeValue1")
EXml_AddCell($oXML,"SheetName",5,9,"SomeValue2")
EXml_AddCell($oXML,"SheetNameTWO",1,2,"SomeValue3")
EXml_AddCell($oXML,"SheetName",5,10,"SomeValueEDIT")
EXml_AddCell($oXML,"SheetName",15,6,"SomeValue4")
EXml_SaveFile($oXML,@DesktopDir & "\test.xml")
Run(@ComSpec & " /c " & @DesktopDir & "\test.xml")
;~ ConsoleWrite(StringRegExpReplace($oXML.xml, "><",">" & @CRLF & "<") & @CRLF)
Exit

Notice, that you can add cells in at any order, you are not constrained to add everything in order.

Where EXml.au3 is:

#include-once
#include <File.au3>
Global $gsEXml_DocProp_Author = "AuthorText"
Global $gsEXml_DocProp_LastAuthor = "LastAuthorText"
Global $gsEXml_DocProp_Created = "CreatedText"
Global $gsEXml_DocProp_Company = "CompnayText"
Global $gsEXml_DocProp_Version = "VersionText"

Func EXml_LoadFile($sCallersFile)
    Local $oXML = ObjCreate("Microsoft.XMLDOM")
    $oXML.Load($sCallersFile)
    Return $oXML
EndFunc
Func EXml_CreateFile()
;~  _FileCreate($sCallersFile)
    Local $oXML = ObjCreate("Microsoft.XMLDOM")
    $oXML.LoadXML('<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook />')

    Local $oWorkBook = $oXML.selectSingleNode("/Workbook")
    Local $oDocumentProperties = EXml_CreateFile_DocumentProperties($oXML,$oWorkBook)
    Local $oOfficeDocumentSettings = EXml_CreateFile_OfficeDocumentSettings($oXML,$oWorkBook)
    Local $oExcelWorkbook = EXml_CreateFile_ExcelWorkbook($oXML,$oWorkBook)
    Local $oStyles = EXml_CreateFile_Styles($oXML,$oWorkBook)

    With $oWorkBook
        .setAttribute("xmlns","urn:schemas-microsoft-com:office:spreadsheet")
        .setAttribute("xmlns:o","urn:schemas-microsoft-com:office:office")
        .setAttribute("xmlns:x","urn:schemas-microsoft-com:office:excel")
        .setAttribute("xmlns:ss","urn:schemas-microsoft-com:office:spreadsheet")
        .setAttribute("xmlns:html","http://www.w3.org/TR/REC-html40")
        .appendChild($oDocumentProperties)
        .appendChild($oOfficeDocumentSettings)
        .appendChild($oExcelWorkbook)
        .appendChild($oStyles)
    EndWith
    Return $oXML
EndFunc
Func EXml_AddCell($oCallersXML,$sCallersSheetName,$iCallersCellCol,$iCallersCellRow,$sCallersCellData)
    Local $oWorkBook = $oCallersXML.selectSingleNode("/Workbook")
    Local $oWorkSheet = $oCallersXML.selectSingleNode("//Worksheet[@ss:Name='" & $sCallersSheetName & "']")
    If Not IsObj($oWorkSheet) Then
        ; Create Worksheet, as needed
        $oWorkSheet = EXml_AddCell_CreateWorkSheet($oCallersXML,$sCallersSheetName)
        $oWorkBook.appendChild($oWorkSheet)
    EndIf

    Local $oTable = $oWorkSheet.selectSingleNode("./Table")
    Local $oRow = $oTable.selectSingleNode("./Row[@ss:Index='" & $iCallersCellRow & "']")
    If Not IsObj($oRow) Then
        ; Create Row, as needed
        $oRow = EXml_AddCell_CreateRow($oCallersXML,$oTable,$iCallersCellRow)
        $oRows = $oTable.selectNodes("./Row")
        If $oRows.length Then
            For $o In $oRows
                If Number($o.getAttribute("ss:Index")) > $iCallersCellRow Then
                    $oTable.insertBefore($oRow,$o)
                    ExitLoop
                EndIf
            Next
        Else
            $oTable.appendChild($oRow)
        EndIf
    EndIf

    Local $oCell = $oRow.selectSingleNode("./Cell[@ss:Index='" & $iCallersCellCol & "']")
    If Not IsObj($oCell) Then
        $oCell = EXml_AddCell_CreateCell($oCallersXML,$oTable,$iCallersCellCol,$sCallersCellData)
        $oCells = $oRow.selectNodes("./Cell")
        If $oCells.length Then
            For $o In $oCells
                If Number($o.getAttribute("ss:Index")) > $iCallersCellCol Then
                    $oRow.insertBefore($oCell,$o)
                    ExitLoop
                EndIf
            Next
        Else
            $oRow.appendChild($oCell)
        EndIf
    Else
        EXml_AddCell_UpdateCell($oCell,$sCallersCellData)
    EndIf
    Return $oCallersXML
EndFunc
Func EXml_SaveFile($oCallersXML,$sCallersFileName)
    $oCallersXML.save($sCallersFileName)
EndFunc
#region INTERNAL
Func EXml_CreateFile_DocumentProperties($oCallersXML, $oCallersWorkbook)
    With $oCallersXML
        Local $oDocProps = .createElement("DocumentProperties")
        Local $oAuthor = .createElement("Author")
        Local $oLastAuthor = .createElement("LastAuthor")
        Local $oCreated = .createElement("Created")
        Local $oCompany = .createElement("Company")
        Local $oVersion = .createElement("Version")
    EndWith
    $oAuthor.text = $gsEXml_DocProp_Author
    $oLastAuthor.text = $gsEXml_DocProp_LastAuthor
    $oCreated.text = $gsEXml_DocProp_Created
    $oCompany.text = $gsEXml_DocProp_Company
    $oVersion.text = $gsEXml_DocProp_Version
    With $oDocProps
        .setAttribute("xmlns","urn:schemas-microsoft-com:office:office")
        .appendChild($oAuthor)
        .appendChild($oLastAuthor)
        .appendChild($oCreated)
        .appendChild($oCompany)
        .appendChild($oVersion)
    EndWith
    Return $oCallersWorkbook.appendChild($oDocProps)
EndFunc
Func EXml_CreateFile_OfficeDocumentSettings($oCallersXML, $oCallersWorkbook)
    With $oCallersXML
        Local $oOfficeDocumentSettings = .createElement("OfficeDocumentSettings")
        Local $oAllowPNG = .createElement("AllowPNG")
    EndWith
    With $oOfficeDocumentSettings
        .setAttribute("xmlns","urn:schemas-microsoft-com:office:office")
        .appendChild($oAllowPNG)
    EndWith
    Return $oCallersWorkbook.appendChild($oOfficeDocumentSettings)
EndFunc
Func EXml_CreateFile_ExcelWorkbook($oCallersXML, $oCallersWorkbook)
    With $oCallersXML
        Local $oExcelWorkbook = .createElement("ExcelWorkbook")
        Local $oWindowHeight = .createElement("WindowHeight")
        Local $oWindowWidth = .createElement("WindowWidth")
        Local $oWindowTopX = .createElement("WindowTopX")
        Local $oWindowTopY = .createElement("WindowTopY")
        Local $oProtectStructure = .createElement("ProtectStructure")
        Local $oProtectWindows = .createElement("ProtectWindows")
    EndWith
    $oWindowHeight.text = 8160
    $oWindowWidth.text = 21570
    $oWindowTopX.text = 0
    $oWindowTopY.text = 0
    $oProtectStructure.text = "False"
    $oProtectWindows.text = "False"
    With $oExcelWorkbook
        .setAttribute("xmlns","urn:schemas-microsoft-com:office:excel")
        .appendChild($oWindowHeight)
        .appendChild($oWindowWidth)
        .appendChild($oWindowTopX)
        .appendChild($oWindowTopY)
        .appendChild($oProtectStructure)
        .appendChild($oProtectWindows)
    EndWith
    Return $oCallersWorkbook.appendChild($oExcelWorkbook)
EndFunc
Func EXml_CreateFile_Styles($oCallersXML, $oCallersWorkbook)
    With $oCallersXML
        Local $oStyles = .createElement("Styles")
        Local $oStyle = .createElement("Style")
        Local $oAlignment = .createElement("Alignment")
        $oAlignment.setAttribute("ss:Vertical","Bottom")
        Local $oBorders = .createElement("Borders")
        Local $oFont = .createElement("Font")
        $oFont.setAttribute("ss:FontName","Calibri")
        $oFont.setAttribute("x:Family","Swiss")
        $oFont.setAttribute("ss:Size","11")
        $oFont.setAttribute("ss:Color","#000000")
        Local $oInterior = .createElement("Interior")
        Local $oNumberFormat = .createElement("NumberFormat")
        Local $oProtection = .createElement("Protection")
    EndWith
    With $oStyle
        .setAttribute("ss:ID","Default")
        .setAttribute("ss:Name","Normal")
        .appendChild($oAlignment)
        .appendChild($oBorders)
        .appendChild($oFont)
        .appendChild($oInterior)
        .appendChild($oNumberFormat)
        .appendChild($oProtection)
    EndWith
    $oStyles.appendChild($oStyle)
    Return $oCallersWorkbook.appendChild($oStyles)
EndFunc
Func EXml_AddCell_CreateWorkSheet($oCallersXML,$sCallersWorkSheetName)
    With $oCallersXML
        Local $oWorkSheet = .createElement("Worksheet")
        Local $oTable = .createElement("Table")
    EndWith
    $oWorkSheet.setAttribute("ss:Name",$sCallersWorkSheetName)
    With $oTable
        .setAttribute("ss:ExpandedColumnCount",0)
        .setAttribute("ss:ExpandedRowCount",0)
        .setAttribute("x:FullColumns",1)
        .setAttribute("x:FullRows",1)
        .setAttribute("ss:DefaultColumnWidth",60)
        .setAttribute("ss:DefaultRowHeight",15)
    EndWith
    $oWorkSheet.appendChild($oTable)
    Return $oWorkSheet
EndFunc
Func EXml_AddCell_CreateRow($oCallersXML,$oCallersTable,$iCallersCellRow)
    Local $oRow = $oCallersXML.createElement("Row")
    With $oRow
        .setAttribute("ss:AutoFitHeight",0)
        .setAttribute("ss:Index",$iCallersCellRow)
    EndWith

    If Number($oCallersTable.getAttribute("ss:ExpandedRowCount")) < $iCallersCellRow Then
        $oCallersTable.setAttribute("ss:ExpandedRowCount",$iCallersCellRow)
    EndIf

    Return $oRow
EndFunc
Func EXml_AddCell_CreateCell($oCallersXML,$oCallersTable,$iCallersCellCol,$sCallersData)
    Local $oCell = $oCallersXML.createElement("Cell")
    Local $oData = $oCallersXML.createElement("Data")
    With $oData
        .setAttribute("ss:Type","String")
        .text = $sCallersData
    EndWith
    With $oCell
        .setAttribute("ss:Index",$iCallersCellCol)
        .appendChild($oData)
    EndWith

    If Number($oCallersTable.getAttribute("ss:ExpandedColumnCount")) < $iCallersCellCol Then
        $oCallersTable.setAttribute("ss:ExpandedColumnCount",$iCallersCellCol)
    EndIf

    Return $oCell
EndFunc
Func EXml_AddCell_UpdateCell($oCallersCell,$sCallersCellData)
    With $oCallersCell.selectSingleNode("./Data")
        .text = $sCallersCellData
    EndWith
EndFunc
#endregion INTERNAL

Debugging is still required when working with > 1 Sheets.

Things to add...styles on sheets, rows, cells.

Edited by jdelaney
3 people like this

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites



Great stuff. Thank you for sharing this useful code.

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

this is just what i was asking Santa for christmas!! jk lol, But really this is absolutely AMAZING!.

Edited by Wombat
1 person likes this

Just look at us.
Everything is backwards; everything is upside down. Doctors destroy health. Lawyers destroy justice. Universities destroy knowledge. Governments destroy freedom. The major media destroy information and religions destroy spirituality. ~ Michael Ellner


The internet is our one and only hope at a truly free world, do not let them take it from us...

Share this post


Link to post
Share on other sites

hi,

i can create and save a file with your UDF.. and open it to excel.. very nice..

now i need help on how to crate a function to read the file.

thanks

Share this post


Link to post
Share on other sites

Welcome to AutoIt and the forum!
There are a lot of threads discussing this subject. One of them uses ADO/SQL to read Excel.

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now