Exports one gamelist xml file to Excel.

export_one.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
'''
Script to export one gamelist.xml file to an Excel spreadsheet.
Without arguments
 * it searches for a `gamelist.xml` file in the running dir
 * outputs a `gamelist.xlsx` file in the running dir
'''

import xml.etree.ElementTree as et
import logging as log
import os.path
import fnmatch
import argparse
import xlsxwriter

from datetime import datetime as dt

# Set up logging using the logging module.
log.basicConfig(level=log.INFO, format=u"%(asctime)s %(levelname)-6s %(message)s")
logger = log.getLogger(__name__)

# Date time format
DATE_TIME_FORMAT = "%Y%m%dT%H%M%S"

def get_xml_element_text(xml, node_name):

    if xml.find(node_name) is None or xml.find(node_name).text is None:
        return None
    else:
        return xml.find(node_name).text.strip()


def is_number(s):

    try:
        int(s)
        return True
    except:
        return False


def is_float(s):

    try:
        float(s)
        return True
    except:
        return False


def get_xml_element_bool(xml, node_name):
    """
    Returns either yes or None, depending on the value of the @parm node_name.
    """
    if xml.find(node_name) is None:
        return None

    elif xml.find(node_name).text.lower() == "false" or xml.find(node_name).text.lower() == "no":
        return None

    else:
        return "yes"


def get_xml_element_date(xml, node_name):
    """
    Returns a DateTime or a String, depending on the value of the @parm node_name.
    """
    
    global DATE_TIME_FORMAT
    
    if not xml.find(node_name) is None and not xml.find(node_name).text is None:
        date_text = xml.find(node_name).text
        # Release date can appear as both ISO date or just as an year.
        # If it's an ISO date, then try to convert it, otherwise just return the text
        if len(date_text) < 6:
            return date_text
        else:
            try:
                date = dt.strptime(xml.find(node_name).text, DATE_TIME_FORMAT)
                return date
            except ValueError:
                return date_text
    else:
        return None


def get_xml_element_int(xml, node_name):
    """
    Returns None or a Number, depending on the value of the @parm.
    """
    if xml.find(node_name) is None:
        return None

    else:
        try:
            return int(xml.find(node_name).text)
        except ValueError:
            return xml.find(node_name).text
        except TypeError:
            return None


class System(object):
    """
    Class that models an ES System, storing the attributes of the System and its list of Games
    """
    info_keys = ("name")

    def __init__(self, name):
        self.info = { 'name': name }
        self.games = []  # List of games

    def __str__(self):
        return self.info['name'] + ", games: " + str(len(self.games))


class Game:
    info_keys  = ['name', 'gametype', 'genre', 'version', 'originaltitle', 'alternatetitle', 'desc', 'publisher', 'developer', 'hackedby', 'translatedby']
    info_keys += ['path', 'playerstext', 'releasetext']
    info_keys += ['video', 'marquee', 'thumbnail']
    info_keys += ['region', 'platform', 'media', 'controller']
    info_keys += ['boxfront', 'cart', 'title', 'action', 'threedbox']
    info_keys += ['gamefaq', 'manual', 'vgmap', 'license', 'programmer', 'musician']
    info_date  = ['releasedate', 'hackreleasedate', 'transreleasedate']
    info_int   = ['players']

    @staticmethod
    def get_headers():
        return (Game.info_keys + Game.info_date + Game.info_int)

    def __init__():
        self.info = dict.fromkeys(Game.get_headers())

    def __init__(self, obj):
        self.info = dict.fromkeys(Game.info_keys)

        # Get the text metadata
        for attr in self.info.keys():
            self.info[attr] = get_xml_element_text(obj, attr)

        # Get the date metadata
        for attr in Game.info_date:
            self.info[attr] = get_xml_element_date(obj, attr)

        # Get the integer metadata
        for attr in Game.info_int:
            self.info[attr] = get_xml_element_int(obj, attr)

    def __str__(self):
        return str("{0}\t{1}".format(self.info["name"]), str(self.info["path"]))


def check_rom(rom_folder, rom_path):
    """
    Method to check if a ROM is present in the filesystem.
    Returns true if the ROM is present, false otherwise.
    """
    # The Rom path in the gamelist might be absolute or relative.
    # Check if the path begins with an '/' to decide if it's an absolute path.
    path_to_check = rom_path

    if not rom_path.startswith('/'):
        path_to_check = rom_folder + "/" + rom_path

    return os.path.isfile(path_to_check)

def get_rom_path(rom_folder, rom_path):
    if not rom_path.startswith('/'):
        path_to_check = rom_folder + "/" + rom_path

    return os.path.realpath(path_to_check)
    

def parse_gamelist(gamelist_path = "gamelist.xml"):

    s = System("games")
    systems = []
    
    try:    
        gamelist = et.parse(gamelist_path)
    except IOError:
        logger.warn("Could not open the gamelist file %s !", gamelist_path)
        exit(1)
    except et.ParseError as v:
        logger.error("Incorrect XML file: %s", format(v))
        exit(1)

    # Ok, we have the gamelist, get each game and parse it.
    for game in gamelist.findall('game'):
        rom = Game(game)
        logger.debug('Found - %s', rom.info['name'])
        s.games.append(rom)

    # If we have more than 1 ROM in the system, add it to the exported list
    if len(s.games) > 0:
        systems.append(s)
    else:
        logger.debug(
            "System %s has no games/roms, it's excluded from the export", s.info['name'])

    return systems
    
    
# Export the system list to excel
def xlsx_export_workbook(systems, output='export.xlsx'):

    if not len(systems):
        raise "Exported system list is empty"
        return

         
    # Create the Workbook
    wb = xlsxwriter.Workbook(output,
                             {'default_date_format': 'dd-mm-yyyy',
                              'in_memory': True,
                              })

    # Add some metadata to it
    wb.set_properties({
        'title': 'Game List Export',
        'subject': 'Game List Export',
        'category': 'Gaming',
        'author': "XlsxWriter (github.com/jmcnamara/XlsxWriter), version " + xlsxwriter.__version__,
    })

    wb.set_custom_property('Date Exported', dt.now())

    fmt_bold = wb.add_format({'bold': True})
    fmt_bold_2 = wb.add_format({'bold': True, 'bg_color': 'red', 'color': 'white'})
    fmt_sys_header = wb.add_format({'bold': True, 'bg_color': 'green', 'color': 'white'})
    fmt_fav_row = wb.add_format({'bg_color': '#FFCC7C'})

    table_headers = list(map(lambda x: {'header': str(x).capitalize()}, Game.get_headers()))

    for i, s in enumerate(systems):
        # Add a worksheet for each system.
        b = wb.add_worksheet(s.info['name'])

        # Create a table with each system and the # of games detected in each system.        
        # Print the table header
        b.set_column(0, 0, 50)
        t = b.add_table(0, 0, len(s.games), len(Game.get_headers()) - 1,
                        {
            'style': 'Table Style Medium 7',
            'columns': table_headers,
            # The name of the Table should only containt letters + numbers.
            # 'name'c: s.info["name"].replace('[^[a-zA-Z0-9]', ''),
            'autofilter': False,
            'banded_rows': False,
        })

        # Print the table rows
        for j, g in enumerate(s.games):
            xlsx_export_system_row(wb, b, j+1, g)

    # Close the workbook
    wb.close()


def xlsx_export_system_row(workbook, sheet, row_number, game, system_name=None):
    fmt_fav = workbook.add_format({'align': 'center'})

    # On special collections, 1st column is the name of the system where the game belongs
    # Only shown when set.
    if system_name is not None:
        sheet.write(row_number, 0, system_name)
        offset = 1
    else:
        offset = 0

    for column, header in enumerate(Game.get_headers()):

        if header in Game.info_date and type(game.info[header]).__name__ == "datetime":
            sheet.write_datetime(row_number, column + offset, game.info[header])

        elif header in ('playcount', 'players') and is_number(game.info[header]):
            sheet.write_number(row_number, column + offset, int(game.info[header]))

        elif header in ('rating',) and is_float(game.info[header]):
            sheet.write_number(row_number, column + offset, float(game.info[header]))

        elif header.lower() in ('favorite', 'kidgame', 'hidden'):
            sheet.write(row_number, column + offset, game.info[header], fmt_fav)

        else:
            sheet.write(row_number, column + offset, game.info[header])

        # If we're on the 'All' sheet, add the description of the game in the cell comments
        if sheet.get_name().lower() == "all" and header.lower() == "name" and not game.info['desc'] is None:
            sheet.write_comment(row_number, column + offset,
                                game.info['desc'], {'x_scale': 4, 'y_scale': 4})


def parse_arguments():
    parser = argparse.ArgumentParser(
        description='Export an XML formatted file to an Excel spreadsheet')
    
    parser.add_argument('input', nargs='?',
                        default="gamelist.xml",
                        help="Gamelist file to parse (default is 'gamelist.xml'")
                        
    parser.add_argument('output', nargs='?',
                        default="gamelist.xlsx",
                        help="Export file (default is 'gamelist.xlsx')")
                        
    parser.add_argument('-d', '--debug', action='store_true',
                        help="run script with with debug info", default=False)


    args = parser.parse_args()
    return (args.input, args.output, args.debug)


if __name__ == "__main__":
    # Parse arguments
    (input, output, debug) = parse_arguments()

    # Set logging level; default is INFO, add debugging if requested via parameter
    if debug:
        logger.setLevel(log.DEBUG)

    logger.debug("Starting")
    systems = parse_gamelist(input)
    
    # See how many games we have
    total_games = sum(map(lambda system: len(system.games), systems))

    logger.info("Total games after parsing gamelist files - " + str(total_games))
    if total_games < 1:
        logger.warn("No games to export, exiting..")
        exit(1)
        
    logger.info("Exporting to file %s",output)
    
    xlsx_export_workbook(systems, output)
    
    logger.debug("Finished")