Jump to content

Create Excel file through "Microsoft.XMLDOM"


jdelaney
 Share

Recommended Posts

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
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.
Link to comment
Share on other sites

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

Edited by Wombat

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...

Link to comment
Share on other sites

  • 2 years later...

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...