· 8 years ago · Feb 18, 2018, 04:50 PM
1#!/usr/bin/env python
2# -*- coding: utf-8 -*-
3import xml.etree.ElementTree as et
4import logging as log
5import os.path
6import argparse
7import xlsxwriter
8
9from datetime import datetime as dt
10
11# List of systems that are not to be exported.
12skipped_systems = ['retropie', 'kodi']
13
14# Set up logging using the logging module.
15log.basicConfig(level=log.INFO, format="%(asctime)s %(levelname)-6s %(message)s")
16logger = log.getLogger(__name__)
17
18
19def get_xml_element_text(xml, node_name):
20
21 if xml.find(node_name) is None:
22 return ""
23 else:
24 return xml.find(node_name).text
25
26
27def is_number(s):
28
29 try:
30 int(s)
31 return True
32 except:
33 return False
34
35
36def is_float(s):
37
38 try:
39 float(s)
40 return True
41 except:
42 return False
43
44
45def get_xml_element_bool(xml, node_name):
46 """
47 Returns either yes or None, depending on the value of the @parm node_name.
48 """
49 if xml.find(node_name) is None or not len(xml.find(node_name)):
50 return None
51
52 elif xml.find(node_name).text.lower() == "false" or xml.find(node_name).text.lower() == "no":
53 return None
54
55 else:
56 return "yes"
57
58
59def get_xml_element_date(xml, node_name):
60 """
61 Returns a DateTime or a String, depending on the value of the @parm node_name.
62 """
63 ES_TIME_FORMAT = "%Y%m%dT%H%M%S"
64 if not xml.find(node_name) is None and len(xml.find(node_name)):
65 date_text = xml.find(node_name).text
66
67 # Release date can appear as both ISO date or just as an year.
68 # If it's an ISO date, then try to convert it, otherwise just return the text
69 if len(date_text) < len('19860101T000000'):
70 return date_text
71 else:
72 try:
73 date = dt.strptime(xml.find(node_name).text, ES_TIME_FORMAT)
74 return date
75 except ValueError:
76 return date_text
77 else:
78 return None
79
80
81def get_xml_element_int(xml, node_name):
82 """
83 Returns None or a Number, depending on the value of the @parm.
84 """
85 if xml.find(node_name) is None or not len(xml.find(node_name)):
86 return None
87
88 else:
89 try:
90 return int(xml.find(node_name).text)
91 except ValueError:
92 return xml.find(node_name).text
93
94
95class System(object):
96 """
97 Class that models an ES System, storing the attributes of the System and its list of Games
98 """
99 info_keys = ("name", "fullname", "path", "platform", "extension")
100
101 def __init__(self, xml):
102 self.info = dict.fromkeys(System.info_keys)
103 self.games = [] # List of games
104
105 for key in System.info_keys:
106 self.info[key] = get_xml_element_text(xml, key)
107
108 def __str__(self):
109 return str(self.info['fullname'] + " (" + self.info['platform'] + "), path: " +
110 self.info['path'] + ", games: " + str(len(self.games)))
111
112 @staticmethod
113 def get_collection(collection_name):
114
115 o = System.__new__(System)
116 o.name = collection_name
117 o.fullname = collection_name
118 o.games = []
119
120 return o
121
122
123class Game:
124 info_keys = ("name", "path", "publisher", "developer", "genre", "players", "rating")
125 info_desc = ("desc")
126 info_bool = ("favorite", "kidgame", "hidden")
127 info_date = ("releasedate", "lastplayed")
128 info_int = ("playcount",)
129
130 @staticmethod
131 def get_headers():
132 return (Game.info_keys + Game.info_date + Game.info_bool + Game.info_int)
133
134 def __init__():
135 self.info = dict.fromkeys(Game.get_headers())
136
137 def __init__(self, obj):
138 self.info = dict.fromkeys(Game.info_keys)
139
140 # Get the text metadata
141 for attr in self.info.keys():
142 self.info[attr] = get_xml_element_text(obj, attr)
143
144 # Get the date metadata
145 for attr in Game.info_date:
146 self.info[attr] = get_xml_element_date(obj, attr)
147
148 # Get the boolean metadata
149 for attr in Game.info_bool:
150 self.info[attr] = get_xml_element_bool(obj, attr)
151
152 # Get the integer metadata
153 for attr in Game.info_int:
154 self.info[attr] = get_xml_element_int(obj, attr)
155
156 # Get the description
157 self.info["desc"] = get_xml_element_text(obj, "desc")
158
159 def __str__(self):
160 return str("{0}\t{1}".format(self.info["name"]), str(self.info["path"]))
161
162
163# The gamelist.xml can be found in
164# * ROM folder for the system
165# * $HOME/.emulationstation/gamelists/$name
166def get_gamelist(system, rom_folder):
167 rom_folder_gamelist = rom_folder + "/gamelist.xml"
168 es_folder_gamelist = "{0}/.emulationstation/gamelists/{1}/gamelist.xml".format(
169 os.environ['HOME'], system)
170
171 if os.path.isfile(rom_folder_gamelist):
172 return rom_folder_gamelist
173 elif os.path.isfile(es_folder_gamelist):
174 return es_folder_gamelist
175 else:
176 return None
177
178
179def check_rom(rom_folder, rom_path):
180 """
181 Method to check if a ROM is present in the filesystem.
182 Returns true if the ROM is present, false otherwise.
183 """
184 # The Rom path in the gamelist might be absolute or relative.
185 # Check if the path begins with an '/' to decide if it's an absolute path.
186 path_to_check = rom_path
187
188 if not rom_path.startswith('/'):
189 path_to_check = rom_folder + "/" + rom_path
190
191 return os.path.isfile(path_to_check)
192
193
194def skip_system(system_name):
195 return str(system_name).upper() in map(lambda x: x.upper(), skipped_systems)
196
197 # Parsing the 'es_systems.cfg' file, from either $HOME/.emulationstation or /etc/emulationstaton
198
199
200def parse_systems():
201
202 es_system_file = '/etc/emulationstation/es_systems.cfg'
203 systems = []
204
205 if os.path.isfile(os.environ['HOME'] + "/.emulationstation/es_systems.cfg"):
206 es_system_file = os.environ['HOME'] + "/.emulationstation/es_systems.cfg"
207
208 logger.info("Emulationstation systems file used: " + es_system_file)
209
210 # Parse the Emulationstation systems file
211 sys = et.parse(es_system_file)
212
213 for system in sys.getroot().findall('system'):
214 s = System(system)
215
216 if s.info['path'] is None or s.info['name'] is None:
217 logger.debug("System {0} has no path or name, skipping".format(s.info['fullname']))
218 continue
219
220 if skip_system(s.info['name']):
221 logger.info("System {0} is skipped as configured".format(s.info['fullname']))
222 continue
223
224 # Try to open and parse the gamelist for this system.
225 logger.debug("Analyzing system: {0} ({1})".format(s.info['fullname'], s.info['name']))
226
227 try:
228 gamelist_path = get_gamelist(s.info['name'], s.info['path'])
229
230 if gamelist_path is None:
231 logger.debug("{0} system has no gamelist, skipping".format(s.info['fullname']))
232 continue
233
234 gamelist = et.parse(gamelist_path)
235 except IOError:
236 logger.warn("Could not open the gamelist for " + s.info['name'] + ", skipping !")
237 continue
238
239 # Ok, we have the gamelist, get each game and parse it.
240 for game in gamelist.getroot().findall('game'):
241 rom = Game(game)
242
243 # Check if the ROM/Game file is on disk. Add it to the list only of it exists.
244 if check_rom(s.info['path'], rom.info['path']):
245 s.games.append(rom)
246 else:
247 logger.debug(u"ROM {0} not found in {1}, removed from export".format(
248 rom.info['name'], s.info['path']))
249
250 # Show how many games we have on the system
251 logger.debug("Found {0} game(s) for {1} ({2})".format(
252 len(s.games), s.info['fullname'], s.info['name']))
253
254 # If we have more than 1 ROM in the system, add it to the exported list
255 if len(s.games) > 0:
256 systems.append(s)
257 else:
258 logger.debug(
259 "System {0} has no games/roms, it's excluded from the export".format(s.info['name']))
260
261 return systems
262
263
264# Export the system list to excel
265def xlsx_export_workbook(systems, output='export.xlsx'):
266
267 if not len(systems):
268 raise "Exported system list is empty"
269 return
270
271 # Special collections. Some of them might be empty
272 # * All games
273 # * Favorite games
274 # * Kid games
275 all_collection = System.get_collection('all')
276 fav_collection = System.get_collection('favorite')
277 kid_collection = System.get_collection('kid')
278
279 # Create the Workbook
280 wb = xlsxwriter.Workbook(output,
281 {'default_date_format': 'dd-mm-yyyy',
282 'in_memory': True,
283 })
284
285 # Add some metadata to it
286 wb.set_properties({
287 'title': 'Game List Export',
288 'subject': 'Emulationstation Games',
289 'category': 'Gaming',
290 'author': "XlsxWriter (github.com/jmcnamara/XlsxWriter), version " + xlsxwriter.__version__,
291 'comments': 'This is a complete list of games registered in Emulationstation.\nDocument produced on ' + dt.now().strftime("%c") +
292 '\nSystems: ' +
293 ', '.join(list(sorted(set(map(lambda system: system.info['fullname'], systems)))))
294 })
295
296 wb.set_custom_property('Date Exported', dt.now())
297
298 fmt_bold = wb.add_format({'bold': True})
299 fmt_bold_2 = wb.add_format({'bold': True, 'bg_color': 'red', 'color': 'white'})
300 fmt_sys_header = wb.add_format({'bold': True, 'bg_color': 'green', 'color': 'white'})
301 fmt_fav_row = wb.add_format({'bg_color': '#FFCC7C'})
302
303 # Add a summary sheet as the 1st sheet in the workbook
304 start = wb.add_worksheet("Summary")
305 start.write_row(0, 0, ("System", "Total"), fmt_bold_2)
306 start.set_tab_color('blue')
307 start.set_column(0, 0, 50)
308
309 # Add special collection sheets
310 all_sheet = wb.add_worksheet("All")
311 all_sheet.set_tab_color('green')
312
313 fav_sheet = wb.add_worksheet("Favorites")
314 fav_sheet.set_tab_color("yellow")
315
316 kid_sheet = wb.add_worksheet("Kid Games")
317 kid_sheet.set_tab_color('pink')
318
319 # The table headers for the each system's sheet
320 table_headers = list(map(lambda x: {'header': str(x).capitalize()}, Game.get_headers()))
321
322 for i, s in enumerate(systems):
323
324 # Add a worksheet for each system.
325 b = wb.add_worksheet(s.info['name'])
326
327 # Create a table with each system and the # of games detected in each system.
328 # Make the system column be a link to the sheet with the system games.
329 start.write_url(i+1, 0, "internal:'" + s.info['name'] + "'!A1",
330 string="{0} ({1})".format(s.info['fullname'], s.info['name'])
331 )
332 start.write(i+1, 1, len(s.games))
333
334 # Print the table header
335 b.set_column(0, 0, 50)
336 t = b.add_table(0, 0, len(s.games), len(Game.get_headers()) - 1,
337 {
338 'style': 'Table Style Medium 7',
339 'columns': table_headers,
340 'name': s.info["name"],
341 'autofilter': True,
342 'banded_rows': False,
343 })
344
345 # Print the table rows
346 for j, g in enumerate(s.games):
347
348 xlsx_export_system_row(wb, b, j+1, g)
349
350 # Add the game to the 'All' collection
351 g.info["system"] = s.info["name"]
352 all_collection.games.append(g)
353
354 # Check if the game goes into another special collection (favorites, kidgames)
355 if g.info["favorite"]:
356 fav_collection.games.append(g)
357
358 if g.info["kidgame"]:
359 kid_collection.games.append(g)
360
361 # Hide the 'Path' column (2nd one)
362 b.set_column('B:B', None, None, {'hidden': True})
363 # Set the size for the Release Date, Last played
364 b.set_column('H:H', 12)
365 b.set_column('I:I', 12)
366
367 # Add a total row on the start sheet
368 start.write(len(systems)+1, 0, "Total", fmt_bold)
369 start.write_formula(len(systems)+1, 1, "=SUM(B1:B" + str(len(systems) + 1) + ")",
370 fmt_bold,
371 sum(map(lambda system: len(system.games), systems)))
372
373 # Write the special Collection
374 special_collections = (
375 (all_sheet, all_collection, "All"),
376 (fav_sheet, fav_collection, "Favorites"),
377 (kid_sheet, kid_collection, "KidGames")
378 )
379
380 for (sheet, collection, name) in special_collections:
381 sheet.set_column(0, 0, 20) # System column size
382 sheet.set_column(1, 1, 50) # Game name column size
383
384 t = sheet.add_table(0, 0, len(collection.games), len(Game.get_headers()),
385 {
386 'style': 'Table Style Light 9',
387 'columns': [{'header': "System"}] + table_headers,
388 'name': name
389 })
390
391 for j, g in enumerate(collection.games):
392 xlsx_export_system_row(wb, sheet, j+1, g, g.info["system"])
393
394 # hide the Path column and set the size for Release date and LastPlayed
395 sheet.set_column('C:C', None, None, {'hidden': True})
396 sheet.set_column('I:I', 12)
397 sheet.set_column('J:J', 12)
398
399 # Close the workbook
400 wb.close()
401
402
403def xlsx_export_system_row(workbook, sheet, row_number, game, system_name=None):
404 fmt_fav = workbook.add_format({'align': 'center'})
405
406 # On special collections, 1st column is the name of the system where the game belongs
407 # Only shown when set.
408 if system_name is not None:
409 sheet.write(row_number, 0, system_name)
410 offset = 1
411 else:
412 offset = 0
413
414 for column, header in enumerate(Game.get_headers()):
415
416 if header in Game.info_date and type(game.info[header]).__name__ == "datetime":
417 sheet.write_datetime(row_number, column + offset, game.info[header])
418
419 elif header in ('playcount', 'players') and is_number(game.info[header]):
420 sheet.write_number(row_number, column + offset, int(game.info[header]))
421
422 elif header in ('rating',) and is_float(game.info[header]):
423 sheet.write_number(row_number, column + offset, float(game.info[header]))
424
425 elif header.lower() in ('favorite', 'kidgame', 'hidden'):
426 sheet.write(row_number, column + offset, game.info[header], fmt_fav)
427
428 else:
429 sheet.write(row_number, column + offset, game.info[header])
430
431 # If we're on the 'All' sheet, add the description of the game in the cell comments
432 if sheet.get_name().lower() == "all" and header.lower() == "name":
433 sheet.write_comment(row_number, column + offset,
434 game.info['desc'], {'x_scale': 4, 'y_scale': 4})
435
436
437def parse_arguments():
438 parser = argparse.ArgumentParser(
439 description='Export Emulationstation gamelist files to an Excel file')
440 parser.add_argument('output', nargs='?',
441 default="export_" + dt.now().strftime("%d-%m-%Y") + ".xlsx",
442 help="Export file (default is 'export_" + dt.now().strftime("%d-%m-%Y") + ".xlsx')")
443 parser.add_argument('-d', '--debug', action='store_true',
444 help="run script with with debug info", default=False)
445
446
447 args = parser.parse_args()
448 return (args.output, args.debug)
449
450
451if __name__ == "__main__":
452 # Parse arguments
453 (output, debug) = parse_arguments()
454
455 # Set logging level; default is INFO, add debugging if requested via parameter
456 if debug:
457 logger.setLevel(log.DEBUG)
458
459 logger.debug("Starting")
460 systems = parse_systems()
461
462 # See how many games we have
463 total_games = sum(map(lambda system: len(system.games), systems))
464
465 logger.info("Total games after parsing gamelist files - " + str(total_games))
466 logger.info("Exporting to file {0}".format(output))
467
468 xlsx_export_workbook(systems, output)
469 logger.debug("Finished")