CoreXLSX icon indicating copy to clipboard operation
CoreXLSX copied to clipboard

[Enhancement] Access worksheet by name

Open GoldenJoe opened this issue 5 years ago • 1 comments

It would be convenient if there was something like file.parseWorksheet(filepath: "xmlfile.xml", name: "myworksheet")

GoldenJoe avatar Jun 30 '19 19:06 GoldenJoe

I came up with a little convenience function for this earlier today. Wasn't sure if you would rather have this functionality in CoreXLSX.swift or as an extension of Worksheet, so I figure I'd just post it here and let you decide where to put it.

func getWorksheet(file: XLSXFile, name: String) throws -> Worksheet?
{
    // Get the ID of the sheet we want
    // NOTE:A workbook should not be able to have two worksheets with the same name...should we assume this is always true, though?
    //      If not, then we can use the commented code below.
    let sheets = try file.parseWorkbooks()
        .flatMap { $0.sheets.items }
        .filter { $0.name == name }
    if(sheets.isEmpty){ // no sheet with name
        return nil
    }
    // Throw Error? - multiple sheets with same name (should not be possible)
    // if(sheets.count > 1){}
    
    let rID = sheets[0].relationship
    //let rID = sheets.map { $0.relationship } // if multiple sheets with the same name is possible, use this to get an array of relationship IDs
    
    // Explanation:
    // parseDocumentPaths will give us something like: ["xl/workbook.xml"]
    // get the relationships for that workbook (Path, [Relationship])
    //      CoreXLSX.Relationship(id: "rId3", type: CoreXLSX.Relationship.SchemaType.worksheet, target: "worksheets/sheet3.xml"),
    //      CoreXLSX.Relationship(id: "rId2", type: CoreXLSX.Relationship.SchemaType.worksheet, target: "worksheets/sheet2.xml"),
    //      CoreXLSX.Relationship(id: "rId1", type: CoreXLSX.Relationship.SchemaType.worksheet, target: "worksheets/sheet1.xml"),
    // filter out the relationships with the IDs we want, and flatMap the paths
    let paths = try file.parseDocumentPaths().map {
        // For each document path, gets its relationships
        try file.parseDocumentRelationships(path: $0) // returns (Path, [Relationship])
        }.flatMap { (path, relationships) -> [String] in
            let worksheets = relationships.items.filter { $0.id == rID }
            //let worksheets = relationships.items.filter { rIDArr.contains($0.id) } // if multiple sheets with the same name is possible
            guard !path.isRoot else { return worksheets.map { $0.target } }
            
            // .rels file has paths relative to its directory,
            // storing that path in `pathPrefix`
            let pathPrefix = path.components.dropLast().joined(separator: "/")
            
            return worksheets.map { "\(pathPrefix)/\($0.target)" }
    }
    
    if(paths.isEmpty){ // no paths for the worksheets
        return nil
    }
    // Throw Error? - multiple paths for the worksheet name (should not be possible)
    // if(paths > 1){}
    
    return try file.parseWorksheet(at: paths[0])
}

GoldenJoe avatar Jul 02 '19 03:07 GoldenJoe