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