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