Create Excel file through "Microsoft.XMLDOM"

[jdelaney]

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:

expandcollapse popup

#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 November 1, 2013 by jdelaney

[dmob]

Great stuff. Thank you for sharing this useful code.

[Wombat]

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

Edited November 4, 2013 by Wombat

[apaansihini]

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

[water]

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