OpenAhkXl - Automate Excel with native ahk. no excel need

Post your working scripts, libraries and tools for AHK v1.1 and older
nadure
Posts: 22
Joined: 26 Mar 2021, 23:02
Location: Korea
Contact:

OpenAhkXl - Automate Excel with native ahk. no excel need

Post by nadure » 04 Oct 2021, 16:30

hi.
i'm making excel treating library without ms excel. it can pick up value or writing cell without ms excel or excel com library. just use native AHK language and msxml com library.

TODO List:
  • 1. Reading Cell value -- Done
    2. Writing Cell value -- Done
    3. Add sheet -- Done
    4. Remove sheet -- Done
    5. Multicell reading/writing -- Done
    6. Multicell Writing optimizing -- half Done
    6. Add Style -- basic Done(not fully)
    7. Cell merging
    8. Cell Text alignment
2021-10-05
- first writing at forum site.

2021-10-11
- changed call architecture. a little.
- added adding sheet method.
- fixed getsheetBy func
- changed rowSpan Sorting func runs when saving file.

2021-10-20
- added removing sheet function
- optimizing writing cell function
- compared with COM excel.
: Writing B1:B1000 with "aaa"
! COM Excel average 35 sec
! this library average 20 sec(15sec faster)

2021-11-07
- added styling function
: it can modify Fill, Border, Font


below codes are working example code.
and i'll update github for some working example code soon.

Code: Select all


#Include class_sheet.ahk
#Include const.ahk


xl := new OpenAhkXl()
; bbbbbbbb.xlsx > tatally empty file.
xl.open("bbbbbbbb.xlsx")

; take sheet object for short calling
sheet := xl.GetSheetBySheetNo(1)

; call value from sheet object
Msgbox,% sheet.Range("B4").value

; set value
sheet.Range("B3").value := "Asdfasd"

; call changed value
Msgbox,% sheet.Range("B3").value

xl.addSheet("nadure")
sheet := xl.GetSheetBySheetNo(2)
sheet.Range("C2").value := "done?"

; save file
xl.save("Ttt.xlsx")

and here is the gif image for working example.
Image
or
https://blogfiles.pstatic.net/MjAyMTEwM ... s/bbbb.gif


source file:
20211107.OpenAhkXlexample.zip
(24.78 KiB) Downloaded 223 times

codes:

Code: Select all

#Include class_sheet.ahk
#Include const.ahk


StyleTest:
xl := new OpenAhkXl()
xl.open("aaaa.xlsx")
sheet := xl.GetSheetBySheetNo(1)

fil := Fill()
fil.rgb := "963232"
sheet.Range("C7").style.Fill := fil

fontt := Font()
fontt.color := "0000000"
fontt.fontSize := 15
fontt.Bold := True
sheet.Range("C8").style.Font := fontt

bborder := Border()
bborder.left["style"] := "thick"
bborder.right["style"] := "thick"
bborder.bottom["style"] := "thick"
bborder.top["style"] := "thick"
; bborder.bottom["color"] := "FFFF33"

; add style
sheet.Range("C9").style.Border := bborder
sheet.Range("C7").style.Font := fontt

xl.save("ttt.xlsx")
return



ComparisionWithCOMobj:
xl := new OpenAhkXl()
xl.open("aaaa.xlsx")
sheet := xl.GetSheetBySheetNo(1)

timeBefore := A_TickCount
; sheet.Range("B1:B1000").value := "aaa"
sheet.Range("B20").value := "aaa"
timeElaspe := A_TickCount - timeBefore
; FileAppend, % timeElaspe . "`n", ahkxlResult.txt
; sheet.Range("C2:C4").value := [["aa"],["bb"],["cc"]]
; Msgbox,% timeElaspe
xl.save("Ttt.xlsx")


return

MultiCellWritingTest:
xl := new OpenAhkXl()
xl.open("aaaa.xlsx")
sheet := xl.GetSheetBySheetNo(1)
sheet.Range("B2:B4").value := "aaa"
sheet.Range("C2:C4").value := [["aa"],["bb"],["cc"]]
xl.save("Ttt.xlsx")
return

MultiCellGetTest:
; MultiCell test
xl := new OpenAhkXl()
xl.open("aaaa.xlsx")
sheet := xl.GetSheetBySheetNo(1)
values := sheet.Range("B2:E3").value
; else
; values := sheet.Range("B2", "E3", "C3").value

for k, rows in values
{
    for j, cell in rows
    {
        Msgbox,% cell
    }
}

return

DeleteTest:
xl := new OpenAhkXl()
xl.open("aaaa.xlsx")
sheet := xl.GetSheetBySheetNo(2)

sheet.DeleteSheet()
xl.save("Ttt.xlsx")
return

CommonTest:
; Common Test Script
xl := new OpenAhkXl()
; bbbbbbbb.xlsx > tatally empty file.
xl.open("bbbbbbbb.xlsx")

; take sheet object for short calling
sheet := xl.GetSheetBySheetNo(1)

; call value from sheet object
Msgbox,% sheet.Range("B4").value

; set value
sheet.Range("B3").value := "Asdfasd"

; call changed value
Msgbox,% sheet.Range("B3").value

xl.addSheet("nadure")
sheet := xl.GetSheetBySheetNo(2)
sheet.Range("C2").value := "done?"

; delete selected sheet
; sheet.DeleteSheet()

; save file
xl.save("Ttt.xlsx")


return



; TODO:
; 새 시트 작성시 바꿔야 할 것
; xl\worksheet\sheet[N].xml 추가(빈 시트 xml 따로 필요)
; xl\workbook.xml
;   SheetName 지정(안 겹치게), sheetID 하나 올려서 추가, r:id 올려서 추가
; docProps\app.xml
;   Vector Size 올리고
;   lpStr 에 추가한 sheetName 추가하고
;   Variant > vt:i4 쪽 숫자도 하나 올림

; TODO:
; sharedStrings 정리



class OpenAhkXl
{
    __New()
    {
        ; clear unzipped files in temp folder
        this.Initialize()
        OnExit(ObjBindMethod(this, "ClearTempFolder"))
    }

    Open(ExcelFilePath:="")
    {
        ; Unzip Excel file to Temp folder for treating.
        this.xlsxPath := excelFilePath
        this.CheckValidation()

        SplitPath, % this.xlsxPath, FileName, FileDir, ,FileNoExt
        this.destPath := this.tempFolderBase . FileNoExt . "\"

        this.targetZipPath := FileDir . "\" . FileNoExt . ".zip"
        this.unZipFolderPath := destPath
        this.xmlBase := this.destPath
        this.UnZipXlsx()

        ; Load paths class
        this.paths := new this.PathInfo(this.destPath)
        this.GetSheetInfo()
    }

    addSheet(sheetName)
    {
        ; 새 시트 작성시 바꿔야 할 것
        ; xl\worksheet\sheet[N].xml 추가(빈 시트 xml 따로 필요)
        ; xl\workbook.xml
        ;   SheetName 지정(안 겹치게), sheetID 하나 올려서 추가, r:id 올려서 추가
        ; docProps\app.xml
        ;   Vector Size 올리고
        ;   lpStr 에 추가한 sheetName 추가하고
        ;   Variant > vt:i4 쪽 숫자도 하나 올림
        ; [ContentType].xml
        ;   sheet 추가

        if not sheetName
            throw, "There is no sheet name. it requires."

        ns := ""
        sheetCount := this.Paths.WorkSheetsPathList.Length() + 1

        if not newSheetXMLFormat
            throw, "There is no new SheetXML default form."

        ; Make new xml form with blank.
        filePath := this.Paths.workSheetPath . "\sheet" . sheetCount . ".xml"
        FileAppend, %newSheetXMLFormat%, %filePath%

        workBook := this.loadXML(this.Paths.workbook)

        ; check sheetName duplication
        for k, v in workBook.getElementsByTagName("sheet")
        {
            if k.getAttribute("name") = sheetName
            {
                throw, "there is same sheet name."
            }
        }

        ns := "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
        nsType := "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"
        nsContentType := "http://schemas.openxmlformats.org/package/2006/content-types"

        sheetElement := workBook.createNode(1, "sheet", ns)
        sheetElement.setAttribute("name", sheetName)
        sheetElement.setAttribute("sheetId", sheetCount)
        sheetElement.setAttribute("r:id", "rId" . sheetCount)

        ; sheets has just one.
        for k, v in workBook.getElementsByTagName("sheets")
        {
            k.appendChild(sheetElement)
        }

        workBook.save(this.Paths.workbook)
        
        ; Treat app file
        app := this.loadXML(this.Paths.app)
        for k, v in app.getElementsByTagName("vt:i4")
        {
            k.text := sheetCount
        }
        vtlpstrElement := app.createNode(1, "vt:lpstr", nsType)
        vtlpstrElement.text := sheetName

        for k, v in app.getElementsByTagName("vt:lpstr")
        {
            if k.parentNode.nodeName = "vt:vector"
            {
                k.parentNode.appendChild(vtlpstrElement)
                k.parentNode.setAttribute("size", sheetCount)
            }
        }
        app.save(this.Paths.app)
        
        ; Treat contenttype.xml
        contentType := this.LoadXML(this.Paths.ContentType)
        contentTypeAttr := "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"
        partName := "/xl/worksheets/sheet" . sheetCount . ".xml"

        overrideElement := contentType.createNode(1, "Override", nsContentType)
        overrideElement.setAttribute("PartName", partName)
        overrideElement.setAttribute("ContentType", contentTypeAttr)

        contentType.childNodes[1].appendChild(overrideElement)
        contentType.save(this.Paths.ContentType)

        this.GetSheetInfo()
    }

    ContentTypeSahredStringsOverrideCheck()
    {
        ; It requires when start from new sheet. because it has no sharedStrings.xml.
        contentType := this.LoadXML(this.Paths.ContentType)
        nsContentType := "http://schemas.openxmlformats.org/package/2006/content-types"
        contentTypeAttr := "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"
        partName := "/xl/sharedStrings.xml"

        for k, v in contentType.getElementsByTagName("Override")
        {
            if(k.getAttribute("PartName") = partName)
            {
                return
            }
        }

        overrideElement := contentType.createNode(1, "Override", nsContentType)
        overrideElement.setAttribute("PartName", partName)
        overrideElement.setAttribute("ContentType", contentTypeAttr)

        contentType.childNodes[1].appendChild(overrideElement)

        contentType.save(this.Paths.ContentType)

    }

    WorkBookRelsRearrange()
    {
        relsTypeNs := "http://schemas.openxmlformats.org/package/2006/relationships"
        workbookRel := this.LoadXML(this.Paths.Workbook_rels)

        idIdx := 0
        for k, v in workbookRel.childNodes[1].childNodes
        {
            k.parentNode.removeChild(workbookRel.childNodes[1].childNodes.item(0))
        }

        relsType := "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"
        Loop, Files, % this.Paths.workSheetPath . "\*.xml"
        {
            idIdx += 1
            relElement := this.RelsElementCreator(workbookRel, idIdx, relsType, "worksheets/" . A_LoopFileName, relsTypeNs)
            workbookRel.childNodes[1].appendChild(relElement)
        }

        relsType := "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme"
        Loop, Files, % this.Paths.theme . "\*.xml"
        {
            idIdx += 1
            relElement := this.RelsElementCreator(workbookRel, idIdx, relsType, "theme/" . A_LoopFileName, relsTypeNs)
            workbookRel.childNodes[1].appendChild(relElement)
        }

        relsType := "http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"
        idIdx += 1
        relElement := this.RelsElementCreator(workbookRel, idIdx, relsType, "styles.xml", relsTypeNs)
        workbookRel.childNodes[1].appendChild(relElement)
        

        relsType := "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"
        idIdx += 1
        relElement := this.RelsElementCreator(workbookRel, idIdx, relsType, "sharedStrings.xml", relsTypeNs)
        workbookRel.childNodes[1].appendChild(relElement)
        workbookRel.save(this.Paths.Workbook_rels)
    }

    RelsElementCreator(doc, idx, relType, Target, ns)
    {
        relsElement := doc.createNode(1, "Relationship", ns)
        relsElement.setAttribute("Id" , "rId" . idx)
        relsElement.setAttribute("Type" , relType)
        relsElement.setAttribute("Target" , Target)
        return relsElement
    }

    CheckValidation()
    {
        if not FileExist(this.xlsxPath)
            throw, "There is no .Xlsx File."

        SplitPath, % this.xlsxPath, FileName, FileDir, ,FileNoExt
        If not FileDir
            this.xlsxPath := A_ScriptDir . "\" . this.xlsxPath

        if this.pidListFromName(FileName).Length()
            throw, FileName . " 파일이 열려있는 중입니다. 확인해주세요."
            
    }

    Initialize()
    {
        this.tempFolderBase := A_Temp . "\NadureExcel\"
    }

    UnZipXlsx()
    {
        FileMove, % this.xlsxPath, % this.targetZipPath
        
        Command := "PowerShell.exe -Command Expand-Archive -LiteralPath '"
            . this.targetZipPath . "' -DestinationPath '" . this.destPath . "'"
                
        RunWait %Command%,, Hide

        FileMove, % this.targetZipPath , % this.xlsxPath

    }

    RearrangeRowSpan()
    {
        ; adjust row span value for all sheet.
        for n, sheetPath in this.paths.WorkSheetsPathList
        {
            ; TODO add if no modified. pass this process

            sheetDoc := this.LoadXML(sheetPath)
            spans := this.RowSpanCheck(sheetDoc)
            
            resRow := sheetDoc.getElementsByTagName("row")

            for row, v in resRow
            {
                row.setAttribute("spans", spans)
            }
            sheetDoc.save(sheetPath)
        }
    }

    save(toSavePath:="")
    {
        this.RearrangeRowSpan()
        this.WorkBookRelsRearrange()
        this.ContentTypeSahredStringsOverrideCheck()

        ; it just for save func.
        if not toSavePath
            toSavePath := this.xlsxPath

        SplitPath, % toSavePath, , FileDir, ,FileNoExt
        SplitPath, % this.xlsxPath, , , ,xlsxFileNoExt

        if not FileNoExt
            FileNoExt := xlsxFileNoExt
        if not FileDir
            FileDir := A_ScriptDir

        toSaveZipPath := FileDir . "\" . FileNoExt . ".zip"

        Command := "PowerShell.exe Compress-Archive -Path "
            . this.destPath . "/* -DestinationPath " . toSaveZipPath . " -Update"

        RunWait %Command%,, Hide
        FileMove, % toSaveZipPath , % toSavePath, 1

    }

    RowSpanCheck(sheetDoc)
    {
        columnNumberArray := Array()
        found := sheetDoc.getElementsByTagName("c")
        res := ""
        for k,v in found
        {
            res := this.RangeColumnToNumber(k.getAttribute("r"))
            columnNumberArray
                .Push(res)
        }
        if columnNumberArray.length() = 1
        {
            return res . ":" . res
        }
        return Min(columnNumberArray*) . ":" . Max(columnNumberArray*)
    }

    RangeColumnToNumber(range)
    {
        StringUpper, range, range
        RegExMatch(range, "[a-zA-Z]+", regexString)

        columnNumber := 0
        chars := Array()
        Loop, parse, regexString
        {   
            chars.Push(A_LoopField)
        }

        if chars.length() >= 4 
            throw, "too much column char"

        if chars.Length() = 1
            columnNumber += ord(chars[1]) - 64

        if chars.Length() = 2
            columnNumber := 26 + (ord(chars[2]) - 64) + 26*(ord(chars[1])-64-1)

        if chars.Length() =3
        {
            ; very hard to figure out this formula.
            columnNumber := 702 + (ord(chars[3]) - 64) 
                + 26*(ord(chars[2]) - 64 - (ord(chars[1]) - 64)) 
                + 702*(ord(chars[1]) - 64 - 1)
        }
        if columnNumber > 16384
            throw, "too big column number for excel keeping."
        return columnNumber
    }

    LoadXML(xml_path)
    {
        doc := ComObjCreate("MSXML2.DOMDocument.3.0")
        doc.async := false
        doc.Load(xml_path)

        Err := doc.parseError
        if Err.reason
        {
            msgbox % "Error: " Err.reason 
                . "`n" . A_ThisFunc . "`n" . xml_path
            ExitApp
        }
    return doc
    }

    ClearTempFolder()
    {
        ; Clear Temp folder function when exiting app.
        FileRemoveDir, % this.destPath, 1
    }

    IsSheetAlive()
    {
        ; TODO set. returning is sheet available.
        if not this.paths.workbook
            throw, "the paths are not initialized."

    }

    GetSheetInfo()
    {
        if not this.paths.workbook
            throw, "the paths are not initialized."
        doc := this.LoadXML(this.paths.workbook)
        res := doc.getElementsByTagName("sheet")

        this.sheetNameArray := Array()
        this.sheetNoArray := Array()
        
        for k, v in res
        {
            name := k.getAttribute("name")
            sheetrID := k.getAttribute("r:id")
            RegExMatch(sheetrID, "\d+$", sheetNo)
            
            this.sheetNameArray[name] := sheetNo
            this.sheetNoArray["Sheet" . sheetNo] := sheetNo
        }
    }

    GetSheetBySheetName(sheetName)
    {
        if not this.sheetNameArray[sheetName]
            throw, "Not initialized. Must open first."

        sheetNo := this.sheetNameArray[sheetName]
        sheetPath := this.paths.workSheetPath . "\sheet" . sheetNo . ".xml"
        Sheet := new Sheet(sheetPath, this.paths.sharedStrings)
        Sheet.Paths := this.paths
        Sheet.RangeColumnToNumber := this.RangeColumnToNumber
        return Sheet
    }

    GetSheetBySheetNo(sheetNo)
    {   
        if not this.sheetNoArray["Sheet" . sheetNo]
            throw, "Not initialized. Must open first."
        sheetPath := this.paths.workSheetPath . "\sheet" . sheetNo . ".xml"
        Sheet := new Sheet(sheetPath, this.paths.sharedStrings)
        Sheet.Paths := this.paths
        Sheet.RangeColumnToNumber := this.RangeColumnToNumber
        return Sheet
    }

    ; xml Paths class
    class PathInfo
    {
        __New(basePath:="")
        {
            this.basePath := basePath
            if not newSheetSharedStrings
                throw, "There is no SharedStrings.xml default doc."
            if not fileExist(this.sharedStrings)
            {
                FileAppend, %newSheetSharedStrings%, % this.sharedStrings
            }
        }

        app
        {
            get {
                return this.basePath . "\docProps\app.xml"
            }
        }

        core
        {
            get {
                return this.basePath . "\docProps\core.xml"
            }
        }

        sharedStrings
        {
            get {
                return this.basePath . "\xl\sharedStrings.xml"
            }
        }

        workbook
        {
            get {
                return this.basePath . "\xl\workbook.xml"
            }
        }

        workSheetPath
        {
            get {
                return this.basePath . "\xl\worksheets"
            }
        }

        WorkSheetsPathList
        {
            get {
                    pathList := Array()
                    Loop, Files, % this.workSheetPath . "\*.xml"
                    {
                        pathList.Push(A_LoopFileFullPath)
                    }
                    return pathList

                return 
            }
        }

        ContentType
        {
            get {
                return this.basePath . "\[Content_Types].xml"
            }
        }

        Workbook_rels
        {
            get {
                return this.basePath . "\xl\_rels\workbook.xml.rels"
            }
        }

        theme
        {
            get {
                return this.basePath . "\xl\theme"
            }
        }

        style
        {
            get {
                return this.basePath . "\xl\styles.xml"
            }
        }

    }

    pidListFromName(name) {
        static wmi := ComObjGet("winmgmts:\\.\root\cimv2")
        
        if (name == "")
            return

        PIDs := []
        for Process in wmi.ExecQuery("SELECT * FROM Win32_Process WHERE Name = '" name "'")
            PIDs.Push(Process.processId)
        return PIDs 
    }
    

}

Code: Select all

class BaseMethod
{

    findNode(xmlnodes, nodename:="")
    {
        for k, v in xmlnodes
        {
            if k.nodeName = nodename
            {
                return k
            }
            
            
            if k.hasChildNodes()
            {
                result := this.findNode(k.childNodes, nodename)
                if result
                    return result
            }
                
        }
        
    }

    LoadXML(xml_path)
    {
        doc := ComObjCreate("MSXML2.DOMDocument.3.0")
        doc.async := false
        doc.Load(xml_path)

        Err := doc.parseError
        if Err.reason
        {
            msgbox % "Error: " Err.reason . "`n: " . xml_path
            ExitApp
        }
    return doc
    }

    sheetData()
    {
        doc := this.LoadXML(this.sheetXML)
        this.sheetDataDoc := doc
        found := this.findNode(doc.childNodes, "sheetData")
        if not found
            throw,"There is no found at the Sheet. please check sheet.xml."
        return found
    }

    SharedStrings()
    {
        doc := this.LoadXML(this.sharedStringsXML)
        this.sharedStringsDoc := doc
        tTags:= doc.getElementsByTagName("t") 

        ; it has no __ENum. so rearrange.
        result := Array()
        for k, v in tTags
            result.Push(k)
        return result 
    }

    ; It seems not works when use with method or func.
    ; selectXPathNodes(xmlDoc, XPathString)
    ; {
    ;     found := Array()
    ;     for nodeItem in xmlDoc.selectNodes(XPathString)
    ;     {
    ;         ; Msgbox,% nodeItem.xml
    ;         found.Push(nodeItem)
    ;     }
    ;     return found
    ; }
}


; Sheet class
class Sheet extends BaseMethod
{
    __New(sheetXML:="", sharedStringsXML:="")
    {
        if not FileExist(sheetXML)
            throw, "Can't find sheet.xml file."

        if not FileExist(sharedStringsXML)
            throw, "Can't find sharedStrings.xml file."
        this._range := Array()
        this.sheetXML := sheetXML
        this.sharedStringsXML := sharedStringsXML

        this.isThisSheetDeleted := False
    }

    Range(params*)
    {
        if this.isThisSheetDeleted
            throw, "This sheet is already deleted."

        if this._range[params[1]]
            return this._range[params[1]]

        rangeClass := new RangeClass(this.sheetXML
            , this.sharedStringsXML, params*)
            
        rangeClass.RangeColumnToNumber := this.RangeColumnToNumber

        ; for assigning style path
        rangeclass.paths := this.paths

        this._range[params[1]] := rangeclass
        return rangeClass
    }

    DeleteSheet()
    {
        if this.isThisSheetDeleted
            throw, "This sheet is already deleted."
        ; delete sheetN file
        ; workbook에서 sheetN 제거
        ; app vt:i4에서 숫자 -1
        ; vt:lpstr 에서 시트이름 하나 제거
        ; vt:vector 에서 size -1
        ; ContentType에서 sheetN 제거

        ; Step1: Delete this.sheetXMl file
        WorkSheetsPathList := this.paths.WorkSheetsPathList
        FileDelete,% this.sheetXML

        ; GetSheetNo
        SplitPath, % this.sheetXML, , , , sheetXMLFileName
        RegExMatch(sheetXMLFileName, "\d+$", SheetNo)
        SheetNo -= 1
        
        ; Step2
        app := this.loadXml(this.paths.app)
        ; remove sheet[N] row
        found := app.documentElement.selectNodes("//vt:vector/vt:lpstr")
        ; Msgbox,% found.item(0).xml ; Start from Zero
        deleteSheetRow := found.item(SheetNo)
        deleteSheetRow.ParentNode.removeChild(deleteSheetRow)

        ; touch vt:i4
        found := app.documentElement.selectNodes("//vt:i4")
        foundItem := found.item(0)
        foundItem.text -= 1
        size := foundItem.text

        ; touch vector size
        found := app.documentElement.selectNodes("//TitlesOfParts[0]/vt:vector[0]")
        found.item(0).setAttribute("size", size)

        ; app file done
        app := app.save(this.paths.app)

        ; Reorder sheet N Files
        originSize := WorkSheetsPathList.Length()
        Loop,% WorkSheetsPathList.Length() - 1
        {
            firstElement := WorkSheetsPathList[A_Index]
            secondElement := WorkSheetsPathList[A_Index+1]
            this.ReorederSheetFile(firstElement, secondElement)
        }

        ; Step 3
        ; touch [ContentType] File
        contentType :=  this.loadXML(this.paths.ContentType)
        XPathString := "//Types/Override[@PartName=""/xl/worksheets/sheet" . originSize . ".xml""]"
        found := contentType.documentElement.selectNodes(XPathString)
        foundItem := found.item(0)
        foundItem.ParentNode.removeChild(foundItem)
        contentType.save(this.paths.ContentType)

        ; Step 4
        ; touch workbook.xml
        workbook := this.loadXML(this.paths.workbook)
        XPathString := "//sheet"
        found := workbook.documentElement.selectNodes(XPathString)
        foundItem := found.item(sheetNo)
        foundItem.ParentNode.removeChild(foundItem)

        found := workbook.documentElement.selectNodes(XPathString)
        Loop,% found.Length()
        {
            found.item(A_Index-1).setAttribute("sheetId", A_Index)
            found.item(A_Index-1).setAttribute("r:id", "rId" . A_Index)
        }
        workbook.save(this.paths.workbook)
        
        ; Check sheet deleted. prevent doing duplicate remove.
        this.isThisSheetDeleted := True
    }

    ReorederSheetFile(FirstFile, SecondFile)
    {
        if FileExist(FirstFile) and FileExist(SecondFile)
            return

        if !FileExist(FirstFile) and FileExist(SecondFile)
        {
            FileMove, % SecondFile, % FirstFile
            return
        }
    }
}

; Range Class
class RangeClass extends BaseMethod
{
    ; sheetXML - sheetXML path
    ; sharedStringsXML : sharedStringsXML path
    ; params : Cell Address
    __New(sheetXML, sharedStringsXML, params*)
    {
        ; sheetXML - sheet xml path
        ; sharedStringsXMl - sharedStrings xml path
        ; params - range object value. if intput is "b2" then key is params[1]

        if not FileExist(sheetXML)
            throw, "Can't find sheet.xml file."

        if not FileExist(sharedStringsXML)
            throw, "Can't find sharedStrings.xml file."

        this.sheetXML := sheetXML
        this.sharedStringsXML := sharedStringsXML
        this.params := params
        this.isStyle := ""
        

        this.mainns := "http://schemas.openxmlformats.org/spreadsheetml/2006/main" ; main:
        this.x14acns := "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" ; x14ac:
        this.rns := "http://schemas.openxmlformats.org/officeDocument/2006/relationships" ; r:
        this.mcns := "http://schemas.openxmlformats.org/markup-compatibility/2006" ; mc:

        if not this.sheetDataDoc.childNodes[1].getAttribute("xmlns:x14ac")
        {
            this.sheetDataDoc.childNodes[1].setAttribute("xmlns:x14ac", this.x14acns)
            this.sheetDataDoc.childNodes[1].setAttribute("mc:Ignorable", "x14ac")
            this.sheetDataDoc.childNodes[1].setAttribute("xmlns:mc", this.mcns)
        }

    }

    style
    {
        get {
            if this.isStyle
            {
                return this.isStyle
            }
            else
            {
                this.isStyle := new StyleXMLBuildTool(this.paths.style
                    , this.sheetXMLNameSpace
                    , this.GetRangeForStyle(this.params[1])
                    , this.sheetXML)
                this.isStyle.nameSpace := this.sheetXMLNameSpace
                
                return this.isStyle
            }

        }

        ; set {

        ; }
    }

    sheetXMLNameSpace
    {
        get {
            x := "xmlns:"
            nameSpace := format("{1}main='{2}' {1}x14ac='{3}' {1}r='{4}' {1}mc='{5}'"
                , x
                , this.mainNs
                , this.x14acns
                , this.rns
                , this.mcns )
            return nameSpace
        }
    }

    value
    {
        get {
            if Not this.sheetData
                throw, "there is no sheetDataDoc."  
            if (this.params.length() = 1) and (this.MultiCellCheck(this.params[1]) = False)
            {
                res := this.FindRange(this.params[1])
                if res
                {
                    return res.text
                }
                else
                {
                    return
                }
            }
            
            ; Msgbox,% this.MultiCellCheck(this.params[1])
            ; Get multi cell values
            if (this.params.length() = 1) and (this.MultiCellCheck(this.params[1]) = True)
            {
                ; it saids B3:E5 format
                addressObject := this.GetCellAddresses(this.params[1])
                res := Array()
                for k, row in addressObject
                {
                    rowArray := Array()
                    for j, cell in row
                    {
                        text := this.FindRange(cell).text
                        rowArray.Push(text)
                    }
                    res.Push(rowArray)
                }
                return res
            }
            if(this.params.length() >= 2 )
            {
                res := Array()
                for k, cell in this.params
                {
                    res.Push(this.FindRange(cell).text)
                }
                return res
            }
        }

        set {
            ; takes assigning value to value var
            if IsObject(value)
            {
                ; if value is object(multiple values)

                if (this.params.length() = 1) and (this.MultiCellCheck(this.params[1]) = True)
                {
                    addressObject := this.GetCellAddresses(this.params[1])
                    for k, row in addressObject
                    {
                        for j, cell in row
                        {
                            this.WriteCell(cell, value[k][j])
                        }
                    }
                }
            }

            else
            {
                ; if value is not object.
                
                ; if not multi cell
                if (this.params.length() = 1) and (this.MultiCellCheck(this.params[1]) = False)
                {
                    this.WriteCell(this.params[1], value)
                }
                    ; this.WriteCell(this.params[1], value)

                ; write whole range with single value
                if (this.params.length() = 1) and (this.MultiCellCheck(this.params[1]) = True)
                {
                    addressObject := this.GetCellAddresses(this.params[1])
                    for k, row in addressObject
                    {
                        for j, cell in row
                        {
                            this.WriteCell(cell, value)
                        }
                    }
                }
                
                ; write certain cells with single value
                if(this.params.length() >= 2 )
                {
                    for k, cell in this.params
                    {
                        this.WriteCell(cell, value)
                    }
                }

                
            }
            
        }
    }

    MultiCellCheck(range)
    {
        StringSplit, splitedRange, range, :
        ; Msgbox,% splitedRange0
        if splitedRange0 > 2
            throw, "Invald Range.`n" . A_ThisFunc
        if splitedRange0 = 2
            return True
        if splitedRange0 = 1
            return False
    }
    
    GetCellAddresses(range)
    {
        ; range
        ; it looks like A3:E8 format
        ; output  > array("A3", "B3", "C4" ----) like this format
        
        ; Split range
        StringSplit, splitedRange, range, :
        if splitedRange0 > 2
            throw, "Invald Range.`n" . A_ThisFunc

        ; Range Check
        rangeColumnNum1 := this.RangeColumnToNumber(splitedRange1)
        rangeColumnNum2 := this.RangeColumnToNumber(splitedRange2)

        if rangeColumnNum1 > rangeColumnNum2
            throw, "Invalid Range.`n" . A_ThisFunc
        
        RegExMatch(splitedRange1, "\d+$", rowNumber1)
        RegExMatch(splitedRange2, "\d+$", rowNumber2)

        if rowNumber1 > rowNumber2
            throw, "Invalid Range.`n" . A_ThisFunc
        
        res := Array()
        
        ; Loop Row
        Loop, % (rowNumber2 - rowNumber1) + 1
        {
            currentRow := A_Index + rowNumber1 -1
            rowArray := Array()
            ; Loop Column
            Loop, % (rangeColumnNum2 - rangeColumnNum1) + 1
            {
                cellAddress := this.NumberToRangeColumn(rangeColumnNum1 + A_Index - 1) . currentRow
                rowArray.Push( cellAddress )
            }
            res.Push(rowArray)
        }
        return res
    }

    NumberToRangeColumn(columnNumber)
    {
        columnName := ""

        while (columnNumber > 0.5) ; i don't know whether it is ok for using 0.5 float. :)
        {
            modulo := Mod((columnNumber - 1), 26)
            columnName := Chr(65 + modulo) . columnName
            columnNumber := (columnNumber - modulo) / 26
        } 

        return Trim(columnName)
    }

    GetRangeForStyle(range)
    {
        sheetDoc := this.LoadXML(this.sheetXML)
        sheetDoc.setProperty("SelectionLanguage", "XPath")
        sheetDoc.setProperty("SelectionNamespaces" , this.sheetXMLNameSpace)
        StringUpper, range, range
        
        ; use selectSingleNode method for the performance
        
        foundRange := sheetDoc.DocumentElement.selectSingleNode("//main:c[@r='" . range . "']")
        if foundRange
            return foundRange
        else
        {
            chracterElement := sheetDoc.createNode(1, "c", this.mainns)

            chracterElement.setAttribute("r", range)
            RegExMatch(range, "\d+$", rowNumber)
            rowElem := sheetDoc.DocumentElement.selectSingleNode("//main:row[@r='" . rowNumber . "']")

            if rowElem
            {
                rowElem.appendChild(chracterElement)
            }
            else
            {
                ; make row node
                row := sheetDoc.createNode(1, "row", this.mainns)
                row.setAttribute("spans", "")
                row.setAttribute("r", rowNumber)
                row.setAttribute("x14ac:dyDescent", 0.3)
                row.appendChild(chracterElement)

                ; append row to sheetdata node
                sheetDataElem := sheetDoc.DocumentElement.selectSingleNode("//main:sheetData")
                sheetDataElem.appendChild(row)
            }
            return chracterElement
        }
    }

    WriteCell(range, value)
    {
        ; TODO: for optimizing.
        ; this.sheetXMLNameSpace
        ; sharedDoc := this.LoadXML(this.sharedStringsXML)
        sheetDoc := this.LoadXML(this.sheetXML)
        sheetDoc.setProperty("SelectionLanguage", "XPath")
        sheetDoc.setProperty("SelectionNamespaces" , this.sheetXMLNameSpace)
        StringUpper, range, range
        
        ; use selectSingleNode method for the performance
        foundRange := sheetDoc.DocumentElement.selectSingleNode("//main:c[@r='" . range . "']")
        if value is not integer
        {
            elemCount := this.WriteTextToSharedDoc(value)
        }

        if foundRange ; if Range is
        {
            if value is integer
            {
                foundRange.removeAttribute("t")
                foundRange.selectSingleNode("//main:v").text := value
            }
            else
            {
                ; attribute for string type to "s"
                foundRange.setAttribute("t", "s")
                foundRange.selectSingleNode("main:v").text := elemCount
            }
        }

        else ; if not 
        {
            ; make new character Element
            chracterElement := sheetDoc.createNode(1, "c", this.mainns)
            v := sheetDoc.createNode(1, "v", this.mainns)

            if value is integer
            {
                v.text := value
            }
            else
            {
                chracterElement.setAttribute("t", "s")
                v.text := elemCount
            }
            
            chracterElement.setAttribute("r", range)
            chracterElement.appendChild(v)

            RegExMatch(range, "\d+$", rowNumber)
            rowElem := sheetDoc.DocumentElement.selectSingleNode("//main:row[@r='" . rowNumber . "']")

            if rowElem
            {
                ; make row node
                rowElem.appendChild(chracterElement)
            }
            else
            {
                row := sheetDoc.createNode(1, "row", this.mainns)
                row.setAttribute("spans", "")
                row.setAttribute("r", rowNumber)
                row.setAttribute("x14ac:dyDescent", 0.3)
                row.appendChild(chracterElement)

                ; append row to sheetdata node
                sheetDataElem := sheetDoc.DocumentElement.selectSingleNode("//main:sheetData")
                sheetDataElem.appendChild(row)
            }
        }
        sheetDoc.save(this.sheetXML)

    }

    WriteTextToSharedDoc(value)
    {
        ; return value : SharedDocStringNumber
        sharedDoc := this.LoadXML(this.sharedStringsXML)

        si := sharedDoc.createNode(1, "si", this.mainns)
        t := sharedDoc.createNode(1, "t", this.mainns) ; text
        phoneticPr := sharedDoc.createNode(1, "phoneticPr", this.mainns) ; text sibling
        phoneticPr.setAttribute("fontId", "1")
        phoneticPr.setAttribute("type", "noConversion")

        t.text := value
        si.appendChild(t), si.appendChild(phoneticPr)

        ; sst := sharedDoc.getElementsByTagName("sst")

        sharedDoc.setProperty("SelectionLanguage", "XPath")
        sharedDoc.setProperty("SelectionNamespaces" 
            , Format("xmlns:main='{1}'", this.mainns))

        elemCount := sharedDoc.DocumentElement.selectNodes("//main:si").length

        sst := sharedDoc.DocumentElement.selectSingleNode("//main:sst")
        
        count := sst.getAttribute("count")
        sst.setAttribute("count", count+1)
        sst.appendChild(si)

        sharedDoc.save(this.sharedStringsXML)
        return elemCount
    }

    WriteCell_legacy(range, value)
    {
        ns := "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
        ns2 := "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"
        x14acns := "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"
        mcns := "http://schemas.openxmlformats.org/markup-compatibility/2006"

        
        sharedDoc := this.LoadXML(this.sharedStringsXML)
        
        StringUpper, range, range

        chracterElementCheck := this.FindRange(range, rangeOnly:=True)

        ; Find range at the sheetDataDoc
        ; if exist found.
        if chracterElementcheck
        {
            ; it from found element
            chracterElement := chracterElementcheck
            chracterElement.removeAttribute("t")
        }
        else
        {
            ; make new "c" elem
            chracterElement := sharedDoc.createNode(1, "c", ns)
            v := sharedDoc.createNode(1, "v", ns)
            chracterElement.setAttribute("r", range)
            chracterElement.appendChild(v)

        }

        if value is integer
        {
            ; check value whether integer or the other.
            chracterElement.childNodes[0].text := value
        }
        else
        {
            ; when string or other(not checked other type yet.)
            chracterElement.setAttribute("t", "s")

            ; .createNode(Type, name, namespaceURI)
            ; 1 : element
            ; 2 : text
            ; Type : https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms766473(v=vs.85)
            
            si := sharedDoc.createNode(1, "si", ns)
            t := sharedDoc.createNode(1, "t", ns) ; text
            phoneticPr := sharedDoc.createNode(1, "phoneticPr", ns) ; text sibling
            phoneticPr.setAttribute("fontId", "1")
            phoneticPr.setAttribute("type", "noConversion")

            t.text := value
            si.appendChild(t), si.appendChild(phoneticPr)

            sst := sharedDoc.getElementsByTagName("sst")

            ; sst has just one.
            for k, v in sst
                {
                    count := k.getAttribute("count")
                    k.setAttribute("count", count+1)
                    k.appendChild(si)
                }
            
            elemCount := sharedDoc.getElementsByTagName("t").length

            chracterElement.childNodes[0].text := elemCount -1

            if not chracterElementcheck
            {
                ; insert to row
                ; if exist, just put there
                ; else make new row, and adjust rowspan value
                if foundRow := this.FindRow(range)
                {
                    foundRow.appendChild(chracterElement)
                }
                else
                {   
                    ; make row node
                    RegExMatch(range, "\d+$", rowNumber)
                    row := sharedDoc.createNode(1, "row", ns)
                    row.setAttribute("spans", "")
                    row.setAttribute("r", rowNumber)
                    row.setAttribute("x14ac:dyDescent", 0.3)
                    row.appendChild(chracterElement)

                    ; append row to sheetdata node
                    resTag := this.sheetDataDoc.getElementsByTagName("sheetData")
                    for k, v in resTag
                    {
                        k.appendchild(row)
                    }

                }
            }
            sharedDoc.save(this.sharedStringsXML)
        }
        this.sheetDataDoc.save(this.sheetXML)
    }

    FindRow(rangeAddress)
        {
            RegExMatch(rangeAddress, "\d+$", rowNumber)
            found := this.sheetDataDoc.getElementsByTagName("row")
            
            for k, v in found
            {
                if k.getAttribute("r") = rowNumber
                {
                    return k
                }
            }

            return False
        }

    FindRange(rangeAddress, rangeOnly:=False)
    {
        sheetData := this.sheetData()
        found := sheetData.getElementsByTagName("c")
        for k,v in found
        {
            if k.getAttribute("r") = rangeAddress
            {   
                if rangeOnly
                    return k

                if k.getAttribute("t") = "s"
                {
                    temp := this.SharedStrings()
                    return temp[k.text+1]
                }
                else
                {
                    return k
                }

            }
        }
        
    }
}



class StyleXMLBuildTool
{
    __New(stylePath, nameSpace, rangeXml, sheetPath)
    {
        this.stylePath := stylePath
        this.nameSpace := namespace
        this.rangeXml := rangeXML
        this.sheetPath := sheetPath
        this.mainns := "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
        this.prevCellXfNo := ""
        
        ; this.nameSpace  - namespace
        xml := ComObjCreate("MSXML2.DOMDocument.6.0")
        xml.async := false
        xml.Load(this.stylePath)

        xml.setProperty("SelectionLanguage", "XPath")
        xml.setProperty("SelectionNamespaces" , this.nameSpace)
        
        Err := xml.parseError
        if Err.reason
        {
            msgbox % "Error: " Err.reason . "`n: " . this.stylePath
            ExitApp
        }

        this.xml := xml

        this.cellXfs := xml.DocumentElement.selectSingleNode("//main:cellXfs")
        this.cellXfsCount := this.cellXfs.getAttribute("count")

        ; numFmtId, fontId, fillId, borderId, xfId
        cloneNode := xml.DocumentElement.selectSingleNode("//main:cellXfs/main:xf[1]").cloneNode(true)
        this.CellXf := this.cellXfs.appendChild(cloneNode)
    }
    
    loadStyleXML()
    {
        xml := ComObjCreate("MSXML2.DOMDocument.6.0")
        xml.async := false
        xml.Load(this.stylePath)

        xml.setProperty("SelectionLanguage", "XPath")
        xml.setProperty("SelectionNamespaces" , this.nameSpace)
        
        Err := xml.parseError
        if Err.reason
        {
            msgbox % "Error: " Err.reason . "`n: " . this.stylePath
            ExitApp
        }

        return xml
    }

    ReloadXMLAndElement()
    {
        if this.prevCellXfNo
        {
            this.xml := this.loadStyleXML()
            this.cellXfs := this.xml.DocumentElement.selectSingleNode("//main:cellXfs")
            this.cellXf := this.cellXfs.childNodes.item(this.prevCellXfNo)
            this.cellXfsCount := this.prevCellXfNo
        }
    }
    
    Save()
    {
        if not this.prevCellXfNo
        {
            this.rangeXml.ownerDocument.save(this.sheetPath)
            this.cellXfs.setAttribute("count", this.cellXfsCount+1)
            this.prevCellXfNo := this.cellXfsCount
            this.xml.save(this.stylePath)
        }
        else
        {
            this.xml.save(this.stylePath)
        }
    }

    ChangeXfAttribute(AttributeName, value)
    {
        if value = 0
        {
            this.CellXf.removeAttribute("apply" . AttributeName)
        }
        else
        {
            this.CellXf.setAttribute("apply" . AttributeName, 1)
            if not this.prevCellXfNo
                this.rangeXml.setAttribute("s", this.cellXfsCount)
        }
        this.CellXf.setAttribute(AttributeName . "Id", value)
        this.Save()
    }

    _CreateElement(nodeName)
    {
        if nodeName = ""
            throw, A_ThisFunc . "`nnode name is null."
        return this.xml.createNode(1, nodeName, this.mainns)
    }

    ; just for viewing
    _SetAttribute(node, key, value)
    {
        node.setAttribute(key, value)
        return node
    }

    ; just for viewing
    GetAttribute(node)
    {
        node.getAttribute(key)
        return node
    }

    Fill
    {
        set
        {
            if value = ""
            {
                this.ChangeXfAttribute("fill", 0)
            }
            else if value.__class = "FillStyleBuild"
            {
                this.ReloadXMLAndElement()
                ; rgb set only
                fills := this.xml.DocumentElement.selectSingleNode("//main:fills")
                fillsCount := fills.getAttribute("count")
                
                fill := this._CreateElement("fill")
                patternFill := this._CreateElement("patternFill")
                patternFill.setAttribute("patternType", "solid")

                fgColor := this._CreateElement("fgColor")
                fgColor.setAttribute("rgb", value.rgb)

                bgColor := this._CreateElement("bgColor")
                bgColor.setAttribute("indexed", 64)

                patternFill.appendChild(fgColor)
                patternFill.appendChild(bgColor)

                fill.appendChild(patternFill)
                fills.appendChild(fill)
                fills.setAttribute("count", fillsCount + 1)

                ; fills.ownerDocument.save(this.stylePath)
                this.xml.save(this.stylePath)

                this.ChangeXfAttribute("fill", fillsCount)
            }
            else
            {
                throw, "please use Fill function. for building style"
            }
        }
    }

    Font
    {
        set
        {
            if value = ""
            {
                this.ChangeXfAttribute("font", 0)
            }
            else if value.__class = "FontStyleBuild"
            {
                this.ReloadXMLAndElement()
                ; rgb set only
                fonts := this.xml.DocumentElement.selectSingleNode("//main:fonts")
                fontsCount := fonts.getAttribute("count")
                
                font := this.xml.DocumentElement.selectSingleNode("//main:font[1]").cloneNode(true)
                ; font sub nodes below
                
                if value.fontSize
                {
                    sz := font.selectSingleNode("main:sz")
                    sz.setAttribute("val", value.fontSize)
                }

                if value.fontName
                {
                    fontName := font.selectSingleNode("main:name")
                    fontName.setAttribute("val", value.fontName)
                }

                if value.Bold
                {
                    Bold := this._CreateElement("b")
                    font.appendChild(Bold)
                }

                if value.strike
                {
                    strike := this._CreateElement("strike")
                    font.appendChild(strike)
                }

                if value.underline
                {
                    underline := this._CreateElement("u")
                    if value.underline = "double"
                        underline.setAttribute("val", "double")
                    font.appendChild(underline)
                }

                color := font.selectSingleNode("main:color")
                if value.color
                {
                    color.removeAttribute("theme")
                    color.setAttribute("rgb", value.color)
                }
                Else
                {
                    if value.color = 0
                    {
                        color.removeAttribute("theme")
                        color.setAttribute("rgb", "000000")
                    }
                }

                fonts.appendChild(font)
                fonts.setAttribute("count", fontsCount + 1)

                ; fonts.ownerDocument.save(this.stylePath)
                this.xml.save(this.stylePath)
                this.ChangeXfAttribute("font", fontsCount)
            }
            else
            {
                throw, "please use Font function. for building style"
            }

        }
    }

    Border {
        set
        {
            if value = ""
            {
                this.ChangeXfAttribute("border", 0)
            }
            else if value.__class = "BorderStyleBuild"
            {
                this.ReloadXMLAndElement()
                borders := this.xml.DocumentElement.selectSingleNode("//main:borders")
                bordersCount := borders.getAttribute("count")
                
                border := this.xml.DocumentElement.selectSingleNode("//main:border[1]").cloneNode(true)
                value.StyleCheck()
                if value.left["style"]
                {
                    left := border.selectSingleNode("main:left")
                    left.setAttribute("style", value.left["style"])

                    color := this._CreateElement("color")
                    if not value.left["color"]
                    {
                        color.setAttribute("indexed", 64)
                    }
                    else
                    {
                        color.setAttribute("rgb", value.left["color"])
                    }
                    left.appendChild(color)
                }

                if value.right["style"]
                {
                    right := border.selectSingleNode("main:right")
                    right.setAttribute("style", value.right["style"])

                    color := this._CreateElement("color")
                    if not value.right["color"]
                    {
                        color.setAttribute("indexed", 64)
                    }
                    else
                    {
                        color.setAttribute("rgb", value.right["color"])
                    }
                    right.appendChild(color)
                }

                if value.top["style"]
                {
                    top := border.selectSingleNode("main:top")
                    top.setAttribute("style", value.top["style"])

                    color := this._CreateElement("color")
                    if not value.top["color"]
                    {
                        color.setAttribute("indexed", 64)
                    }
                    else
                    {
                        color.setAttribute("rgb", value.top["color"])
                    }
                    top.appendChild(color)
                }

                if value.bottom["style"]
                {
                    bottom := border.selectSingleNode("main:bottom")
                    bottom.setAttribute("style", value.bottom["style"])

                    color := this._CreateElement("color")
                    if not value.bottom["color"]
                    {
                        color.setAttribute("indexed", 64)
                    }
                    else
                    {
                        color.setAttribute("rgb", value.bottom["color"])
                    }
                    bottom.appendChild(color)
                }

                borders.appendChild(border)
                borders.setAttribute("count", bordersCount + 1)

                ; borders.ownerDocument.save(this.stylePath)
                this.xml.save(this.stylePath)
                this.ChangeXfAttribute("border", bordersCount)

                
            }
        }
    }

   
}
    

Font()
{
    return new FontStyleBuild()
}

Fill()
{
    
    return new FillStyleBuild()
}

Border()
{
    return new BorderStyleBuild()
}

class FontStyleBuild
{
    __New()
    {
        this.fontName := "" ; set default font when assigning.
        this.fontSize := ""
        this.color := ""
        this.family := ""
        this.underline := "" ; 1. true, 2. "double", 3. ""
        this.Bold := false
        this.Italic := false
        this.Strike := false
        this.Shadow := false
        this.Outline := false

        this.sz := this.fontSize
        this.name := this.fontName
        this.b := this.Bold
    }
}

class BorderStyleBuild
{
    __New()
    {
        ; style - thin, thick, medium, dotted
        ; color - indexed=64(black default) or rgb=000000
        this.availableStyle := "thin|thick|medium|dotted"

        this.left := Array(), this.right := Array()
        this.top := Array(), this.bottom := Array()
        
        this.left["style"] := "", this.right["style"] := ""
        this.top["style"] := "", this.bottom["style"] := ""

        this.left["color"] := "", this.right["color"] := ""
        this.top["color"] := "", this.bottom["color"] := ""

    }

    StyleCheck()
    {   
        if this.left["style"]
        {
            if not InStr(this.availableStyle, this.left["style"])
                throw, "you pushed invalid "
                . "Border Style. the style is : " . this.left["style"]
        }
        
        if this.right["style"]
        {
            if not InStr(this.availableStyle, this.right["style"])
                throw, "you pushed invalid "
                . "Border Style. the style is : " . this.right["style"]
        }
        
        if this.top["style"]
        {
            if not InStr(this.availableStyle, this.top["style"])
                throw, "you pushed invalid "
                . "Border Style. the style is : " . this.top["style"]
        }

        if this.bottom["style"]
        {
            if not InStr(this.availableStyle, this.bottom["style"])
                throw, "you pushed invalid "
                . "Border Style. the style is : " . this.bottom["style"]
        }
        
    }
}

class FillStyleBuild
{
    __New()
    {
        ; Set only Solid Type..
        ; simple color only..
        this.rgb := ""
        this.bgCoilorIndexed := 64

    }

    

}

Code: Select all

global newSheetXMLFormat, newSheetSharedStrings

newSheetXMLFormat =
(
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
    xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac"
    xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
    <dimension ref="A1"/>
    <sheetViews>
        <sheetView workbookViewId="0"/>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="16.5" x14ac:dyDescent="0.3"/>
    <sheetData/>
    <phoneticPr fontId="1" type="noConversion"/>
    <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
    <pageSetup paperSize="9" orientation="portrait" r:id="rId1"/>
</worksheet>
)

newSheetSharedStrings = 
(
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="1" uniqueCount="1">
</sst>
)
github:
https://github.com/goglgo/openAutohotkeyxl
(not arranged yet.)

making blog post:
current progress
https://blog.naver.com/goglkms/222543118924
(But, the writing language is korean. it can be hard to read it)


Below is simple manual.

1. Open Xlsx file.

Code: Select all

xl := new OpenAhkXl()
xl.open("aaaa.xlsx")


2. Get Sheet
  • GetSheetBySheetNo

    Code: Select all

    sheet := xl.GetSheetBySheetNo(1)
  • GetSheetBySheetName

    Code: Select all

    sheet := xl.GetSheetBySheetName("SheetName")

3. Treating Sheet
  • Add Sheet

    Code: Select all

    xl.addSheet("SheetName")
  • Delete Sheet(assigned sheet)

    Code: Select all

    sheet.DeleteSheet()

4. Treating Range
  • Treating Single value.

    Code: Select all

    ; Get value from range.
    Msgbox,% sheet.Range("B4").value
    
    ; Set value to range
    sheet.Range("B3").value := "Asdfasd"
    ```
  • Treating Multi values.

    Code: Select all

    ; Get values from range.
    values := sheet.Range("B2:E3").value
    
    ; Set values to range.
    sheet.Range("B2:B4").value := "aaa"
    sheet.Range("C2:C4").value := [["aa"],["bb"],["cc"]]
5. Styling Cell
  • Fill

    make fill object from Fill function.

    Code: Select all

    fil := Fill()
    fil.rgb := "963232"
    sheet.Range("C7").style.Fill := fil
    It can use only rgb option. now.
  • Font

    Code: Select all

    fontt := Font()
    fontt.color := "0000000"
    fontt.fontSize := 15
    fontt.Bold := True
    sheet.Range("C8").style.Font := fontt
    ; Font options.

    Code: Select all

    this.fontName := "" ; set default font when assigning.
    this.fontSize := ""
    this.color := ""
    this.underline := "" ; 1. true, 2. "double", 3. ""
    this.Bold := false
    this.Italic := false
    this.Strike := false
    this.Shadow := false
    this.Outline := false
  • Border

    Code: Select all

    bborder := Border()
    bborder.left["style"] := "thin"
    bborder.right["style"] := "thick"
    bborder.bottom["style"] := "medium"
    bborder.top["style"] := "thick"
    bborder.top["color"] := "963232" ; set border color
    sheet.Range("C9").style.Border := bborder

    Code: Select all

    ; Border available line style
    "thin|thick|medium|dotted"
  • Style Combination

    Code: Select all

    sheet.Range("C9").style.Border := bborder
    sheet.Range("C9").style.Font := fontt
5. Save
save to abstract path or current path.

Code: Select all

xl.save("Ttt.xlsx")
Last edited by nadure on 07 Nov 2021, 08:58, edited 6 times in total.

User avatar
Xeo786
Posts: 759
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: OpenAhkXl - Automate Excel with native ahk. no excel need

Post by Xeo786 » 05 Oct 2021, 23:34

Great....!

but I have few suggestions

You should make a Class for Range just like you did for sheet so we can do sheet.range(adrs).value
then you can make class for color and borders too
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory

nadure
Posts: 22
Joined: 26 Mar 2021, 23:02
Location: Korea
Contact:

Re: OpenAhkXl - Automate Excel with native ahk. no excel need

Post by nadure » 06 Oct 2021, 05:08

Xeo786 wrote:
05 Oct 2021, 23:34
Great....!

but I have few suggestions

You should make a Class for Range just like you did for sheet so we can do sheet.range(adrs).value
then you can make class for color and borders too
thank you for your suggestion. I was wandering if i use returned original XML object(now code) or recreate Range Claas for adding new method there. :roll:

I'll think hard to do it. :idea:

then. I'll update soon :D

nadure
Posts: 22
Joined: 26 Mar 2021, 23:02
Location: Korea
Contact:

Re: OpenAhkXl - Automate Excel with native ahk. no excel need

Post by nadure » 11 Oct 2021, 22:25

Xeo786 wrote:
05 Oct 2021, 23:34
Great....!
but I have few suggestions

i've doen that.
now you can call like this

Code: Select all

sheet.range( Addrs ).value 
:D

toralf
Posts: 868
Joined: 27 Apr 2014, 21:08
Location: Germany

Re: OpenAhkXl - Automate Excel with native ahk. no excel need

Post by toralf » 09 Aug 2022, 05:17

Hi nadure,

This seems very good.
Your last edit is from 7th November 2021 and also your last github commit is from 7th of November 2021.
Have you worked on this in the meantime? Are you actively developing this class further?
ciao
toralf

User avatar
Xeo786
Posts: 759
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: OpenAhkXl - Automate Excel with native ahk. no excel need

Post by Xeo786 » 11 Aug 2022, 01:34

nadure wrote:
11 Oct 2021, 22:25
Xeo786 wrote:
05 Oct 2021, 23:34
Great....!
but I have few suggestions

i've doen that.
now you can call like this

Code: Select all

sheet.range( Addrs ).value
:D
Wonderfull ......! :cookie:
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory

toralf
Posts: 868
Joined: 27 Apr 2014, 21:08
Location: Germany

Re: OpenAhkXl - Automate Excel with native ahk. no excel need

Post by toralf » 11 Aug 2022, 02:05

I played around with the library to read large Excel files and even started a modification of this class to speed it up. I gained roughly a factor of 15.
Unfortunately the FindRange(rangeAddress, rangeOnly:=False) method of the Range class is still too slow for large Excel files.
The only way to speed it up, would be to read the whole sheet content into an AHK object.
But that would defeat the goal to work directly on the XML files and there are faster alternatives here in this forum to just read the content. So I gave up.

For others to review; here is my mod. It only reads data!

Code: Select all

/* 
Target Premises:
- only call once "new XlsxLib(tempFolderBase)" and get a handle to the class
- each ".open(ExcelFilePath)" creates a new instance of Workbook class
- All Workbook are all kept open until closed
- A Workbook has pathInof and can have sheets and one sharedstrings
- When a sheet gets called, all data is read and kept until sheet or the workbook gets closed
- A Sheet can have ranges
- A Range isn't stored with data, since different Ranges can overlap an influence the data in the sheet
*/

  #Warn ClassOverwrite 
  SetBatchLines, -1
  
  Xlsx := new XlsxLib()
  wb := Xlsx.open("C:\YourExcelFile.xlsx")
  sheet := wb.GetSheetBySheetNo(1)
  values := sheet.Range(sheet.dimension).value

  text =
  for k, rows in values
  {
      text .= k
      for j, cell in rows
          text .= A_Tab cell 
      text .= "`n"
  }
  Msgbox,% text
return

class XlsxLib
{
    __New(tempFolderBase := ""){
        If !tempFolderBase
          tempFolderBase := A_Temp . "\XlsxLib"
        If !FileExist(tempFolderBase)
          Try FileCreateDir, %tempFolderBase%
        this.tempFolderBase := Trim(tempFolderBase, " \") . "\"
        this.Workbooks := {}
    }

    __Delete(){
        FileRemoveDir, % this.tempFolderBase, % Recurse := True
    }

    Open(ExcelFilePath:=""){
        ; Start := A_Tickcount
        xlsxPath := this.CheckValidation(ExcelFilePath)
        
        If this.Workbooks.haskey(xlsxPath)
          return this.Workbooks[xlsxPath]
        
        wb := new this.WorkbookClass(xlsxPath, this.tempFolderBase)
        return this.Workbooks[xlsxPath] := wb
    }
    
    ;ToDo: store all wb independent from open, so that get a workbook or close one or all wb is possible
    
    CheckValidation(xlsxPath){ 
        If !xlsxPath
            throw, "No file was given."
    
        if not FileExist(xlsxPath)
            throw, "The file " xlsxPath " does not exist."

        SplitPath, xlsxPath, FileName, FileDir, FileExt, FileNoExt
        
        If !(InStr(FileExt, "xls") = 1)
            throw, "The file " xlsxPath " does not have a '.xls' extension."
        
        If not FileDir
            xlsxPath := A_ScriptDir . "\" . xlsxPath

        if not FileExist(xlsxPath)
            throw, "The file " xlsxPath " does not exist."

        if this.WindowWithName(FileNoExt)   
            throw, "the file " FileNoExt " is opened. Please close it."
        Return xlsxPath
    }

    WindowWithName(FileNoExt) {
        oldTMM := A_TitleMatchMode 
        SetTitleMatchMode, 2
        Found := 0
        If WinExist(FileNoExt)
          Found := 1
        SetTitleMatchMode, %oldTMM%
        Return Found
    }

    Class WorkbookClass
    {
        __New(xlsxPath, tempFolderBase){
            ; Start := A_Tickcount
            SplitPath, % xlsxPath, FileName, FileDir, ,FileNoExt
            this.xlsxPath := xlsxPath
            this.targetZipPath := FileDir . "\" . FileNoExt . ".zip"  
            this.destPath := tempFolderBase . FileNoExt

            this.UnZipXlsx()

            this.paths := new this.PathInfo(this.destPath)
            this.GetAllSheetNamesFromWorkbook()
            this.XMLDocs := {}
            this.Sheets := {}
            
            xmldoc := this.LoadXML(this.paths.sharedStrings)
            tTags:= xmldoc.getElementsByTagName("t") 
            this.SharedStrings := Array()
            for k, v in tTags
                this.SharedStrings.Push(k.text)
        }
        
        close(){
            FileRemoveDir, % this.destPath, % Recurse := True
        }
      
        UnZipXlsx(){ 
            ;rename
            Try FileMove, % this.xlsxPath, % this.targetZipPath
            
            Command := "PowerShell.exe -Command Expand-Archive -LiteralPath '"
                     . this.targetZipPath . "' -DestinationPath '" . this.destPath . "'"
                    
            RunWait %Command%,, Hide

            ;rename back
            Try FileMove, % this.targetZipPath , % this.xlsxPath
            
            End := A_Tickcount
        }

        GetAllSheetNamesFromWorkbook(){
            if not this.paths.workbook
                throw, "the paths are not initialized."
            xmldoc := this.LoadXML(this.paths.workbook)
            res := xmldoc.getElementsByTagName("sheet")

            this.sheetNameArray := Array()
            this.sheetNoArray := Array()
            
            for k, v in res
            {
                name     := k.getAttribute("name")
                sheetrID := k.getAttribute("r:id")
                RegExMatch(sheetrID, "\d+$", sheetNo)
                
                this.sheetNameArray[name]            := sheetNo
                this.sheetNoArray["Sheet" . sheetNo] := sheetNo
            }
        }
        
        LoadXML(xml_path){  ;this method gets inherited to sheet and range class instances
            
            If this.XMLDocs.hasKey(xml_path)
                Return this.XMLDocs[xml_path]
            
            xmldoc := ComObjCreate("MSXML2.DOMDocument.3.0")
            xmldoc.async := false
            xmldoc.Load(xml_path)

            Err := xmldoc.parseError
            if Err.reason
            {
                msgbox % "Error: " Err.reason 
                    . "`n" . A_ThisFunc . "`n" . xml_path
                ExitApp
            }
            this.XMLDocs[xml_path] := xmldoc
            return xmldoc
        }

        GetSheetBySheetName(sheetName){
            if !(sheetNo :=  this.sheetNameArray[sheetName])
                throw, "Not initialized. Must open first."
            Return this._GetSheet(sheetNo)
        }
        GetSheetBySheetNo(sheetNo){   
            if !(this.sheetNoArray["Sheet" . sheetNo])
                throw, "Not initialized. Must open first."
            Return this._GetSheet(sheetNo)
        }
        _GetSheet(sheetNo){    ;ToDo: Rearrange/Reset, when new sheet is added to Workbook
            If this.Sheets.hasKey(sheetNo)
              Return this.Sheets[sheetNo]
              
            sheetPath := this.paths.workSheetPath . "\sheet" . sheetNo . ".xml"
            Sheet := new this.SheetClass(sheetPath)
            
            ;inheritance 
            Sheet.Paths               := this.paths
            Sheet.SharedStrings       := this.SharedStrings
            Sheet.LoadXML             := this.LoadXML
            Sheet.RangeColumnToNumber := this.RangeColumnToNumber
            Sheet.LoadSheetData()
            this.Sheets[sheetNo] := Sheet
            return Sheet
        }

        RangeColumnToNumber(range){   ;this method gets inherited to sheet and range class instances
            StringUpper, range, range
            RegExMatch(range, "[a-zA-Z]+", regexString)

            if (StrLen(regexString) > 3 )
                throw, "too many column char in range: " range

            ColNum := 0
            Loop, Parse, regexString
              ColNum := (ColNum*26) + ASC(A_LoopField) - 64
            Return ColNum
        }
     
         ; xml Paths class  ;the instance of this class gets inherited to sheet and range class instances
        class PathInfo      ;ToDo: does it require a class or could be a simple look up table function
        {
            __New(basePath:=""){
                this.basePath := basePath

                if not fileExist(this.sharedStrings)
                {
                    newSheetSharedStrings = 
    (
    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="1" uniqueCount="1">
    </sst>
    )
                    FileAppend, %newSheetSharedStrings%, % this.sharedStrings
                }
            }

            App
            {
                get {
                    return this.basePath . "\docProps\app.xml"
                }
            }

            Core
            {
                get {
                    return this.basePath . "\docProps\core.xml"
                }
            }

            SharedStrings
            {
                get {
                    return this.basePath . "\xl\sharedStrings.xml"
                }
            }

            Workbook
            {
                get {
                    return this.basePath . "\xl\workbook.xml"
                }
            }

            Workbook_rels
            {
                get {
                    return this.basePath . "\xl\_rels\workbook.xml.rels"
                }
            }

            WorkSheetPath
            {
                get {
                    return this.basePath . "\xl\worksheets"
                }
            }

            WorkSheetsPathList
            {
                get {
                    pathList := Array()
                    Loop, Files, % this.workSheetPath . "\*.xml"
                    {
                        pathList.Push(A_LoopFileFullPath)
                    }
                    return pathList
                }
            }

            ContentType
            {
                get {
                    return this.basePath . "\[Content_Types].xml"
                }
            }

            Theme
            {
                get {
                    return this.basePath . "\xl\theme"
                }
            }

            Style
            {
                get {
                    return this.basePath . "\xl\styles.xml"
                }
            }
        }

        class SheetClass
        {
            __New(sheetXML:=""){
                if (!sheetXML Or !FileExist(sheetXML))
                    throw, "Can't find sheet.xml file: " sheetXML

                this.sheetXML            := sheetXML
                
                this.isThisSheetDeleted := False

                this.SheetData := {}
            }
            
            LoadSheetData(){
                xmldoc := this.LoadXML(this.sheetXML)
                foundNode := this.findNode(xmldoc.childNodes, "sheetData")
                found := foundNode.getElementsByTagName("c")
                for k,v in found
                {
                    rangeAddress := k.getAttribute("r")
                    if k.getAttribute("t") = "s"
                        Value := this.SharedStrings[k.text + 1]
                    else 
                        Value := k.text
                        ;ToDo: is text correct? Or is (v)alue and (f)ormula better? When formula is in cell, text returns both (f . v)
                    this.SheetData[rangeAddress] := Value
                }
            }
            
            LastColumnNumber
            {
                get {
                    Return this.RangeColumnToNumber(this.LastColumn)
                }
            }
            LastColumn
            {
                get {
                    Array := StrSplit(this.Dimension, ":")
                    RegExMatch(Array.2, "^\D+", Match)
                    Return Match
                }
            }
            LastRow
            {
                get {
                    Array := StrSplit(this.Dimension, ":")
                    RegExMatch(Array.2, "\d+$", Match)
                    Return Match
                }
            }
            Dimension   ;in sheetx.xml:  <dimension ref="A1:R16"/>
            {
                get {       
                    Return this._DimensionNode().getAttribute("ref")
                }
                set {      
                    Return this._DimensionNode().setAttribute("ref")
                }
            }
            _DimensionNode(){
                xmldoc := this.LoadXML(this.sheetXML)        ;ToDo: loads each time. And isn't updated when data is written to XML.
                foundNode := this.findNode(xmldoc.childNodes, "dimension")
                if not foundNode
                  throw, "Could not find dimension in the sheet. please check sheet.xml."  ;ToDO: rephrase message
                Return foundNode
            }

            findNode(xmlnodes, nodename:=""){
                for k, v in xmlnodes
                {
                    if k.nodeName = nodename
                        return k
                    if k.hasChildNodes()
                        If result := this.findNode(k.childNodes, nodename)
                            return result
                }
            }

            Range(params*){
                if this.isThisSheetDeleted
                    throw, "This sheet is already deleted."

                Range := new this.RangeClass(params*)
                ; inheritance   
                Range.paths := this.paths   ; for assigning style path
                Range.LoadXML := this.LoadXML
                Range.findNode := this.findNode
                Range.sheetXML            := this.sheetXML
                Range.sheetData           := this.sheetData
                Range.SharedStrings       := this.SharedStrings
                Range.RangeColumnToNumber := this.RangeColumnToNumber

                return Range
            }

            class RangeClass
            {
                __New(params*){

                    this.params := params

                    this.mainns  := "http://schemas.openxmlformats.org/spreadsheetml/2006/main"           ; main:
                    this.x14acns := "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"         ; x14ac:
                    this.rns     := "http://schemas.openxmlformats.org/officeDocument/2006/relationships" ; r:
                    this.mcns    := "http://schemas.openxmlformats.org/markup-compatibility/2006"         ; mc:
                }

                FindRange(rangeAddress, rangeOnly:=False){
                    Return this.SheetData[rangeAddress]
                    sheetData := this.sheetData()
                    found := sheetData.getElementsByTagName("c")
                    for k,v in found
                    {
                        if k.getAttribute("r") = rangeAddress
                        {   
                            if rangeOnly
                                return k
                                                    ;ToDo: no expression??? is it returning "s" as a string?
                            if k.getAttribute("t") = "s"
                            {
                                return this.SharedStrings[k.text + 1]
                            }
                            return k.text  ;ToDo: is text correct? Or is (v)alue and (f)ormula better? When formula is in cell, text returns both (f . v)
                        }
                    }
                }

                value
                {
                    get {
                        if (this.params.length() = 1) and (this.MultiCellCheck(this.params[1]) = False)
                        {
                            res := this.FindRange(this.params[1])
                            return res
                        }
                        
                        ; Get multi cell values
                        if (this.params.length() = 1) and (this.MultiCellCheck(this.params[1]) = True)
                        {
                            ; it saids B3:E5 format
                            addressObject := this.GetCellAddresses(this.params[1])
                            res := Array()
                            for k, row in addressObject
                            {
                                rowArray := Array()
                                for j, cell in row
                                {
                                    text := this.FindRange(cell)
                                    rowArray.Push(text)
                                }
                                res.Push(rowArray)
                            }
                            return res
                        }
                        if(this.params.length() > 1 )   ; read certain cells
                        {
                            res := Array()
                            for k, cell in this.params         ;ToDo: MultiCellCheck to only allow single cells, or use this.GetCellAddresses
                                res.Push(this.FindRange(cell))
                            return res
                        }
                    }
                }

                MultiCellCheck(range){
                    StringSplit, splitedRange, range, :
                    if splitedRange0 > 2
                        throw, "Invald Range.`n" . A_ThisFunc
                    return splitedRange0 = 2 ? True : False
                }
                
                GetCellAddresses(range){
                    StringSplit, splitedRange, range, :    ;ToDo: Split in Array
                    if splitedRange0 > 2
                        throw, "Invald Range.`n" . A_ThisFunc

                    rangeColumnNum1 := this.RangeColumnToNumber(splitedRange1)
                    rangeColumnNum2 := this.RangeColumnToNumber(splitedRange2)

                    if (rangeColumnNum1 > rangeColumnNum2)
                        throw, "Invalid Range.`n" . A_ThisFunc
                    
                    RegExMatch(splitedRange1, "\d+$", rowNumber1)
                    RegExMatch(splitedRange2, "\d+$", rowNumber2)

                    if (rowNumber1 > rowNumber2)
                        throw, "Invalid Range.`n" . A_ThisFunc
                    
                    res := Array()
                    
                    ; Loop Row
                    Loop, % (rowNumber2 - rowNumber1) + 1
                    {
                        currentRow := A_Index + rowNumber1 - 1
                        rowArray := Array()
                        ; Loop Column
                        Loop, % (rangeColumnNum2 - rangeColumnNum1) + 1
                        {
                            cellAddress := this.NumberToRangeColumn(rangeColumnNum1 + A_Index - 1) . currentRow
                            rowArray.Push( cellAddress )
                        }
                        res.Push(rowArray)
                    }
                    return res
                }

                NumberToRangeColumn(columnNumber){
                    while (columnNumber > 0)
                    {
                        modulo := Mod((columnNumber - 1), 26)
                        columnName := Chr(65 + modulo) . columnName
                        columnNumber := Floor((columnNumber - modulo) / 26)
                    } 
                    return columnName
                }
                
            } ;end of range class
        } ;end of sheet class
    } ;end of workbook class
} ;end of XlsxLib class
ciao
toralf

william_ahk
Posts: 481
Joined: 03 Dec 2018, 20:02

Re: OpenAhkXl - Automate Excel with native ahk. no excel need

Post by william_ahk » 14 Aug 2022, 01:00

Amazing library! :thumbup:
Word when? :trollface:

User avatar
submeg
Posts: 326
Joined: 14 Apr 2017, 20:39
Contact:

Re: OpenAhkXl - Automate Excel with native ahk. no excel need

Post by submeg » 14 Aug 2022, 17:22

Hey @nadure, have you seen Joe's COM stuff?
____________________________________
Check out my site, submeg.com
Connect with me on LinkedIn
Courses on AutoHotkey :ugeek:

nadure
Posts: 22
Joined: 26 Mar 2021, 23:02
Location: Korea
Contact:

Re: OpenAhkXl - Automate Excel with native ahk. no excel need

Post by nadure » 04 Dec 2022, 19:15

submeg wrote:
14 Aug 2022, 17:22
Hey @nadure, have you seen Joe's COM stuff?
yes. I already read about that.
It's a just try to open excel file with native autohotkey.
:lol: :lol:

User avatar
oldbrother
Posts: 273
Joined: 23 Oct 2013, 05:08

Re: OpenAhkXl - Automate Excel with native ahk. no excel need

Post by oldbrother » 09 Dec 2022, 11:12

Nice lib! Thank you.

One question.

To create a new excel file, do I have to read an empty file first?

Code: Select all

xl := new OpenAhkXl()
; bbbbbbbb.xlsx > tatally empty file.
xl.open("bbbbbbbb.xlsx")

nadure
Posts: 22
Joined: 26 Mar 2021, 23:02
Location: Korea
Contact:

Re: OpenAhkXl - Automate Excel with native ahk. no excel need

Post by nadure » 03 Jan 2023, 23:24

oldbrother wrote:
09 Dec 2022, 11:12
Nice lib! Thank you.

One question.

To create a new excel file, do I have to read an empty file first?

Code: Select all

xl := new OpenAhkXl()
; bbbbbbbb.xlsx > tatally empty file.
xl.open("bbbbbbbb.xlsx")

yes,
the function for writing totally new file is not implemented. :D
i think it's very long way for that :crazy:

ahk7
Posts: 574
Joined: 06 Nov 2013, 16:35

Re: OpenAhkXl - Automate Excel with native ahk. no excel need

Post by ahk7 » 04 Jan 2023, 17:41

nadure wrote:
03 Jan 2023, 23:24
the function for writing totally new file is not implemented.
Perhaps this experiment is useful, you need to wrap it in a function/class yourself but the basis works. I took an empty excel file, base64 encoded it and store it in a variable (just a string) which you can decode using Skan's function which is included below. Here it creates "testfile.xlsx" but the desired filename is of course easy to pass on to a function call.

Code: Select all

Base64Data:= "
( LTrim Join
UEsDBBQACAgIAOVSJFYAAAAAAAAAAAAAAAALAAAAX3JlbHMvLnJlbHOtks9KAzEQh+99ipB7d7YVRGSzvYjQm0h9gJjM/mE3mTAZ
dX17gwhaqaUHj0l+8803Q5rdEmb1ipxHikZvqlorjI78GHujnw736xu9a1fNI85WSiQPY8qq1MRs9CCSbgGyGzDYXFHCWF464mCl
HLmHZN1ke4RtXV8D/2To9oip9t5o3vuNVof3hJewqetGh3fkXgJGOdHiV6KQLfcoRi8zvBFPz0RTVaAaTrtsL3f5e04IKNZbseCI
cZ24VLOMmL91PLmHcp0/E+eErv5zObgIRo/+vJJN6cto1cDRJ2g/AFBLBwhmqoK34AAAADsCAABQSwMEFAAICAgA5VIkVgAAAAAA
AAAAAAAAABEAAABkb2NQcm9wcy9jb3JlLnhtbG1SyW6DMBC99yuQ72AMVVUhIFJb5dRIlZKoVW+uPSFuwVj2JCR/XwMJ6ZLbvMVv
xh7ns0NTB3uwTrW6ICyKSQBatFLpqiDr1Ty8J4FDriWvWw0FOYIjs/ImFyYTrYUX2xqwqMAFPki7TJiCbBFNRqkTW2i4i7xDe3HT
2oajh7aihosvXgFN4viONoBccuS0DwzNlEhOkVJMkWZn6yFACgo1NKDRURYxevEi2MZdPTAoP5yNwqOBq9azOLkPTk3GruuiLh2s
fn5G3xbPy+GqodL9UwkgZX4aJBMWOIIMfEA2tjsrr+nj02pOyiRO0jBmYXy7YixLkixl7zn9c74PHOvWlr16Ab6W4IRVBv0OR/EX
4XHNdbXzD16CDtfLwTJR/Spr7nDhl75RIB+OPuMK5ykLe9V/lDIeHBPsW7jdxycIHPtPwNeosIaRPpf/Pk/5DVBLBwiy0WKFUAEA
AIgCAABQSwMEFAAICAgA5VIkVgAAAAAAAAAAAAAAABAAAABkb2NQcm9wcy9hcHAueG1snZDBTsMwEETvfEVkcW3sJG3UVI4rJMQJ
CQ4BjpXjXbdGiW3FpqR/jwGp7bm3nR3pze7w7TwO2RGnYJxtSZEzkqFVDozdt+Ste1qsSRaitCAHZ7ElJwxkK+746+Q8TtFgyBLB
hpYcYvQbSoM64ChDnmybHO2mUcYkpz11WhuFj059jWgjLRmrKc4RLSAs/BlI/ombY7wVCk793hfeu5NPPME7HP0gIwpOL2Pnohw6
M6JgaX0W/MH7wSgZUyPi2fQTvvxF0Dov8mVe3n8YC+477OZ1nV35u/TBJ6pIG2C6KqHQrF+xhjW1hnqFDJY9qrIGgKKp1hoqTq+T
OL1UKn4AUEsHCHhgax7/AAAAlwEAAFBLAwQUAAgICADlUiRWAAAAAAAAAAAAAAAAGAAAAHhsL3dvcmtzaGVldHMvc2hlZXQxLnht
bJ1VTW8bNxC991cQPOTUaiW1joNkpcCQq6aAYxmWkwC9UctZLWEuhyG5UuxfnyH3s24OQXVYkTPLN8M3b2bz999qzU7gvEKz4ovZ
nDMwBUpljiv+6WH72xvOfBBGCo0GVvwJPH+//iU/o3v0FUBgBGD8ilch2LdZ5osKauFnaMGQp0RXi0Bbd8y8dSBkOlTrbDmfv85q
oQxvEd66n8HAslQFXGPR1GBCC+JAi0Dp+0pZz9d5inDnWKl0APcRJaVdCu2BfFYcYQ/hk03+8IB3ZOjd2TrPusPrXCqKEFlhDsoV
v1pEd/J+VnD2kzXzFZ63lGSjhe+xkvEvp+SNMkDW4JrOeI/nDeoPRAWxPnX8Aw4Hg1PHitK7gTIMkEEc9qChCCCn53ZN0BRk/1Qf
UA8AEkrR6BBToHDoevuJMl5xEznVBIk2htiA1umOrIjv/k34r//g7Bmx3hdCE0OL+Xyyv03HX1ojlzfiCZtES+eNQjkgPkZTxJ3H
CqVbRG6tiKLqsuBMkPUEYzbjvj3K/NdJNbKhBNN1X5ptkg3VuWOCeP8AkVVKbDkjWT8T372lYxhbKm/gBJreTyGnNmKyvUH2rxDr
nGjz6RkJ1ML6WKIOtGh8wPqLkqEabJWSEswP46agtfgW+Vte0FLFxoxd+BQLESltoRaL2cUy8dBGTyldiyDIZJ0yYWdTW7CKtEb9
PGrzOOrypYWao1dKhU49owlCb6jXwE2uRAMjqOK/jqztsI/CHRUF1km989nlm8uLTtLjloqeBs7F8nL4UVUOGIitH3mq1DIjQIkY
Jvts6O7Gkq4suL16hpa6iYZL5XyISr1t6kM8vuDtJOgK1G8HrXAWYXcuxZZ4Ng8VmB0xQGVzighIw2fFLbrghCIVH7QoHq+M/FKp
MAwXJp2Y9HJBmt5gHaeYj+1oyNZ42L7M7mUprq1a8d/jRfoajJYCrYo1Tc3RsrVNHDGpypLqZELCH1PqzTsp/zyNelznKGU7odav
RG3fbdLz1dcGw7sHGoye3dLcu8damF/v4UiDz7XO9N5imf6u8myEiYhtMv8PMXLC0vouwXZYeTa9J22H79L6O1BLBwhVXbHdLQMA
ANsGAABQSwMEFAAICAgA5VIkVgAAAAAAAAAAAAAAAA0AAAB4bC9zdHlsZXMueG1s7VhPT9swHL3vU1i+jyQlFJjSIMbUaZcJjSIh
TTuYxEks/CeyXWj49Ps5TtOEwiZ1hxWpJ9svv/f88uyodpOLleDokWrDlJzh6CjEiMpM5UyWM3y7mH88w8hYInPClaQz3FCDL9IP
ibENpzcVpRaBgjQzXFlbfwoCk1VUEHOkairhSaG0IBaGugxMrSnJjSMJHkzCcBoIwiROE7kUc2ENytRSWrDRQ8g333IApzFGXu5K
5WDlK5VUE46DNAk6gTQplNzoxNgDaWKe0SPhIBK6ckkE9eNLzbxCQQTjjQcnraQn7kAP94beNi4UxnkfygR7IE1qYi3Vcg4D1PUX
TQ3JSlhqL9PW/aW61KSJJicDQtvAvPdK57C1hsvqIZQzUipJ+G09wwXhhuIe+qKe5BpME04LC8KalZVrraoDJ2KtEtBZc9zUXrnv
wPQZ5fzG7dO7YvP2IYiuiu19JdsBbH/nvet6pW5A6po3c+VErF7SDvjcloygS85KKeiLwmutLM1s+5m1cJqQdSGqlGbPIO0WsOy2
tfsqLcsc5N8XI0tX9oeyxKuApydN6gWAfYhM5u3E8MxUmsmHhZqz/jHEVPc2EFfZA83XJiuWA3VQGayKF0mFm5yiXXPqfL4MaggP
k1pvg/djZnIw84aZnb+tg5mDmYOZg5mDmV3MxMf79EsZR3vlJt4rN5N9cnP+n80Ew+O7P8wPzvHRrsf4VbHtfOjnH62/gzN90EU5
uCD1sU7xAEXuqjnD392dmw+Su18ybpn0o2CbcKWEIOv66GREOH6TgH6Gv3rSdESavkpaak1l1vSc0xEn/hNnNNfZiHf6Gu+a6gzW
oKecjyj+6rsJEwabv0fS31BLBwiMT4YUgwIAAGMRAABQSwMEFAAICAgA5VIkVgAAAAAAAAAAAAAAAA8AAAB4bC93b3JrYm9vay54
bWyNU8lu2zAQvfcrBN5tLV5qG5YDV46QAN0Qp8mZkkYWa4oUyPGWov/eEWWlKdpDDzY5C9+8mXla3pxr6R3BWKFVzMJhwDxQuS6E
2sXs22M6mDHPIlcFl1pBzC5g2c3q3fKkzT7Teu/Re2VjViE2C9+3eQU1t0PdgKJIqU3NkUyz821jgBe2AsBa+lEQTP2aC8U6hIX5
HwxdliKHjc4PNSjsQAxIjsTeVqKxbLUshYSnriGPN81nXhPthMuc+atX2l+Nl/F8f2hSyo5ZyaUFarTSpy/Zd8iROuJSMq/gCOE8
GPcpf0BopEwqQ87W8STgZH/HW9Mh3mkjXrRCLre50VLGDM3hWo2Iosj/Fdm2g3rkme2d52ehCn2KGa3o8uZ+ctdnUWBFC5yOZuPe
dwdiV2HMZuE8Yh7y7KEdVMwmAT0rhbHoijgUTp0cgeq1FjXkv+nI7aw/PeUG6l6GLVU67wuq7HSCFDoKKzJJjM1CUMDcF5FD7GGo
3ZzmLxAM5Sf6oIhC2HIyUH7SBUGsCe0af13O1d6ARE4kh0EQtrBwxo8W3XlVktR0/0tNUmQGOv04KTHvYETMfryfRtNkNo0G0Toc
DcLwdjL4MBpPBultmtLgkk0yT3+SrBzqgn5JR9+ioW/kAcrthVZ77iS2dpR8yur+HTO/V8TqF1BLBwiLeDPt9wEAAG4DAABQSwME
FAAICAgA5VIkVgAAAAAAAAAAAAAAABoAAAB4bC9fcmVscy93b3JrYm9vay54bWwucmVsc62QywrCMBBF9/2KMHub1oWINHUjQrdS
PyCk0we2ScjER//eiKIWunDharjzOPcy2fY29OyCjjqjBaRxAgy1MlWnGwHHcr9YwzaPsgP20ocVajtLLNxoEtB6bzeck2pxkBQb
izpMauMG6YN0DbdSnWSDfJkkK+6+GZBPmKyoBLiiSoGVo8Vf2KauO4U7o84Daj9jwcmPPVIgStegF/DUceAAn7df/tP+atyJWkT/
SfBuhXCPkr7CRBmffDi/A1BLBwiPAgIAvQAAAJgBAABQSwMEFAAICAgA5VIkVgAAAAAAAAAAAAAAABMAAABbQ29udGVudF9UeXBl
c10ueG1srZTLTsMwEEX3/YrIWxS7ZYEQStoFjyV0UdbI2JPENH7Idkv794wThFAV+hBZWfbMvWdmbLlY7HSbbcEHZU1JZnRKMjDC
SmXqkryunvJbsphPitXeQcgw14SSNDG6O8aCaEDzQK0Dg5HKes0jbn3NHBdrXgO7nk5vmLAmgol5TB5kXjxAxTdtzB53eNxzUU6y
+z4voUrCnWuV4BHDLEXZoM5DG44It0YeVJd/V0ZR2eWERrlw9TfBmfoAoHTqLJ0PKz4cDEu6AGpecNxeSciW3MdnrjGBvaVOGB25
nyGStGLprQt4LR7o8cEf4SV17tAIfFRwHhGtLwfaqlIC0GOjUUIhDVqCPJO9a9mn9evQAMTAumX27xqC88BlZ6Zb+uN/oo4Q9y2E
keG96RkTeLd2PTY7rVRzZU7w+8f9u4qRH/qkYN0HNf8CUEsHCIT705hHAQAAzwQAAFBLAQIUABQACAgIAOVSJFZmqoK34AAAADsC
AAALAAAAAAAAAAAAAAAAAAAAAABfcmVscy8ucmVsc1BLAQIUABQACAgIAOVSJFay0WKFUAEAAIgCAAARAAAAAAAAAAAAAAAAABkB
AABkb2NQcm9wcy9jb3JlLnhtbFBLAQIUABQACAgIAOVSJFZ4YGse/wAAAJcBAAAQAAAAAAAAAAAAAAAAAKgCAABkb2NQcm9wcy9h
cHAueG1sUEsBAhQAFAAICAgA5VIkVlVdsd0tAwAA2wYAABgAAAAAAAAAAAAAAAAA5QMAAHhsL3dvcmtzaGVldHMvc2hlZXQxLnht
bFBLAQIUABQACAgIAOVSJFaMT4YUgwIAAGMRAAANAAAAAAAAAAAAAAAAAFgHAAB4bC9zdHlsZXMueG1sUEsBAhQAFAAICAgA5VIk
Vot4M+33AQAAbgMAAA8AAAAAAAAAAAAAAAAAFgoAAHhsL3dvcmtib29rLnhtbFBLAQIUABQACAgIAOVSJFaPAgIAvQAAAJgBAAAa
AAAAAAAAAAAAAAAAAEoMAAB4bC9fcmVscy93b3JrYm9vay54bWwucmVsc1BLAQIUABQACAgIAOVSJFaE+9OYRwEAAM8EAAATAAAA
AAAAAAAAAAAAAE8NAABbQ29udGVudF9UeXBlc10ueG1sUEsFBgAAAAAIAAgA/QEAANcOAAAAAA==
)"

nBytes := Base64Dec( Base64Data, Bin )

File := FileOpen("testfile.xlsx", "w")
File.RawWrite(Bin, nBytes)
File.Close()

; SKAN https://www.autohotkey.com/boards/viewtopic.php?t=35964
Base64Dec( ByRef B64, ByRef Bin ) {  ; By SKAN / 18-Aug-2017
Local Rqd := 0, BLen := StrLen(B64)                 ; CRYPT_STRING_BASE64 := 0x1
  DllCall( "Crypt32.dll\CryptStringToBinary", "Str",B64, "UInt",BLen, "UInt",0x1
         , "UInt",0, "UIntP",Rqd, "Int",0, "Int",0 )
  VarSetCapacity( Bin, 128 ), VarSetCapacity( Bin, 0 ),  VarSetCapacity( Bin, Rqd, 0 )
  DllCall( "Crypt32.dll\CryptStringToBinary", "Str",B64, "UInt",BLen, "UInt",0x1
         , "Ptr",&Bin, "UIntP",Rqd, "Int",0, "Int",0 )
Return Rqd
}

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: OpenAhkXl - Automate Excel with native ahk. no excel need

Post by flyingDman » 04 Jan 2023, 21:43

@ahk7 This might be handy! Works well.
14.3 & 1.3.7

User avatar
oldbrother
Posts: 273
Joined: 23 Oct 2013, 05:08

Re: OpenAhkXl - Automate Excel with native ahk. no excel need

Post by oldbrother » 07 Jan 2023, 09:11

@ahk7It works well. Thanks a lot!

Post Reply

Return to “Scripts and Functions (v1)”