1 """

2 Report Generator - Full Production Version

3 Generates comprehensive Excel reports from REAL extracted metadata and conversion
results.
4 """
5
6 from openpyxl import Workbook
7 from [Link] import Font, PatternFill, Alignment, Border, Side
8 from [Link] import get_column_letter
9 from datetime import datetime
10 from typing import Dict, List, Any
11 from pathlib import Path
12
13
14 # ── Colour palette ──
15 CLR_HEADER_BLUE = '1E4D8C'
16 CLR_HEADER_GREEN = '1B7B4A'
17 CLR_HEADER_ORANGE = 'CC5500'
18 CLR_HEADER_RED = 'AA2222'
19 CLR_HEADER_PURPLE = '5B3A8A'
20 CLR_ROW_ALT = 'F0F4FA'
21 CLR_SUCCESS = 'D4EDDA'
22 CLR_PARTIAL = 'FFF3CD'
23 CLR_FAILED = 'F8D7DA'
24 CLR_CALC_HIGH = 'FFC7CE'
25 CLR_CALC_MED = 'FFEB9C'
26 CLR_CALC_LOW = 'C6EFCE'
27
28
29 def _hdr_style(ws, row: int, bg: str = CLR_HEADER_BLUE, bold=True, fg='FFFFFF'):
30 for cell in ws[row]:
31 [Link] = Font(bold=bold, color=fg, size=10)
32 [Link] = PatternFill(start_color=bg, end_color=bg, fill_type='solid')
33 [Link] = Alignment(horizontal='left', vertical='center', wrap_text=True)
34
35
36 def _auto_width(ws, min_w=10, max_w=80):
37 for col in [Link]:
38 col_letter = get_column_letter(col[0].column)
39 best = min_w
40 for cell in col:
41 if [Link]:
42 val_len = len(str([Link]))
43 if val_len > best:
44 best = val_len
45 ws.column_dimensions[col_letter].width = min(best + 2, max_w)
46
47
48 def _alt_row(ws, row: int, ncols: int):
49 if row % 2 == 0:
50 for c in range(1, ncols + 1):
51 [Link](row=row, column=c).fill = PatternFill(
52 start_color=CLR_ROW_ALT, end_color=CLR_ROW_ALT, fill_type='solid')
53
54
55 def _status_fill(status: str) -> PatternFill:
56 s = str(status).lower()
57 if 'success' in s or 'auto' in s or 'created' in s:
58 return PatternFill(start_color=CLR_SUCCESS, end_color=CLR_SUCCESS, fill_type=
'solid')
59 if 'partial' in s or 'manual' in s or 'simulated' in s:
60 return PatternFill(start_color=CLR_PARTIAL, end_color=CLR_PARTIAL, fill_type=
'solid')
61 return PatternFill(start_color=CLR_FAILED, end_color=CLR_FAILED, fill_type='solid')
62
63
64 class ReportGenerator:
 65 def __init__(self, output_dir: Path, logger=None):
66 self.output_dir = output_dir
67 [Link] = logger
68 self.output_dir.mkdir(exist_ok=True)
69
70 def _log(self, level, message):
71 if [Link]:
72 getattr([Link], level)(message)
73
74 # ──────────────────────────── Public API ────────────────────────────────
75
76 def generate_all_reports(self, results: Dict, metadata: Dict, session_id: str) ->
Dict:
77 self._log('info', "GENERATING EXCEL REPORTS")
78 report_files = {}
79
80 try:
81 fn = self._gen_conversion_summary(results, metadata, session_id)
82 report_files['conversion_summary'] = fn
83 self._log('info', f" ✓ {fn}")
84 except Exception as e:
85 self._log('error', f" ✗ Summary report: {e}")
86
87 try:
88 fn = self._gen_metadata_export(metadata, session_id)
89 report_files['metadata_export'] = fn
90 self._log('info', f" ✓ {fn}")
91 except Exception as e:
92 self._log('error', f" ✗ Metadata report: {e}")
93
94 try:
95 fn = self._gen_manual_steps(results, session_id)
96 report_files['manual_steps'] = fn
97 self._log('info', f" ✓ {fn}")
98 except Exception as e:
99 self._log('error', f" ✗ Manual steps report: {e}")
100
101 try:
102 fn = self._gen_dax_report(results, metadata, session_id)
103 report_files['dax_formulas'] = fn
104 self._log('info', f" ✓ {fn}")
105 except Exception as e:
106 self._log('error', f" ✗ DAX report: {e}")
107
108 try:
109 fn = self._gen_field_catalog(metadata, session_id)
110 report_files['field_catalog'] = fn
111 self._log('info', f" ✓ {fn}")
112 except Exception as e:
113 self._log('error', f" ✗ Field catalog: {e}")
114
115 self._log('info', f"Generated {len(report_files)} reports")
116 return report_files
117
118 # ────────────────────── Report 1: Conversion Summary ────────────────────
119
120 def _gen_conversion_summary(self, results: Dict, metadata: Dict, session_id: str) ->
str:
121 fname = f"conversion_summary_{session_id}.xlsx"
122 fpath = self.output_dir / fname
123 wb = Workbook()
124 [Link]([Link])
125
126 self._summary_overview(wb.create_sheet("Overview"), results, metadata, session_id
)
127 self._summary_object_details(wb.create_sheet("All Objects"), results)
128 self._summary_pbi_objects(wb.create_sheet("PBI Objects Created"), results)
129 self._summary_connection_map(wb.create_sheet("Connection Mapping"), metadata)
130 self._summary_successful(wb.create_sheet("Successful"), results)
131 self._summary_partial(wb.create_sheet("Partial"), results)
132 self._summary_failed(wb.create_sheet("Failed"), results)
133
134 [Link](fpath)
135 return fname
136
137 def _summary_overview(self, ws, results, metadata, session_id):
138 stats = [Link]('statistics', {})
139 total = [Link]('total', 0)
140 s = [Link]('successful', 0)
141 p = [Link]('partial', 0)
142 f = [Link]('failed', 0)
143 rate = [Link]('success_rate', 0)
144
145 [Link] = "Overview"
146 ws['A1'] = 'TABLEAU → POWER BI CONVERSION SUMMARY'
147 ws['A1'].font = Font(size=14, bold=True, color='FFFFFF')
148 ws['A1'].fill = PatternFill(start_color=CLR_HEADER_BLUE, end_color=
CLR_HEADER_BLUE, fill_type='solid')
149 ws.merge_cells('A1:D1')
150
151 rows = [
152 ['SESSION ID', session_id],
153 ['Run Date', [Link]().strftime('%Y-%m-%d %H:%M:%S')],
154 ['', ''],
155 ['── CONVERSION STATISTICS ──', ''],
156 ['Total Objects', total],
157 ['✓ Successful', s],
158 ['◑ Partial', p],
159 ['✗ Failed', f],
160 ['Automation Rate', f"{rate:.1f}%"],
161 ['', ''],
162 ['── OBJECT BREAKDOWN (metadata) ──', ''],
163 ['Workbooks', len([Link]('workbooks', []))],
164 ['Datasources', len([Link]('datasources', []))],
165 ['Views', len([Link]('views', []))],
166 ['', ''],
167 ['── METADATA TOTALS ──', ''],
168 ['Total Worksheets', [Link]('summary', {}).get('total_worksheets', 0)],
169 ['Total Dashboards', [Link]('summary', {}).get('total_dashboards', 0)],
170 ['Total Calculated Fields', [Link]('summary', {}).get(
'total_calculations', 0)],
171 ['Total Columns/Fields', [Link]('summary', {}).get('total_columns', 0
)],
172 ['Total Connections', [Link]('summary', {}).get('total_connections', 0
)],
173 ['Total Parameters', [Link]('summary', {}).get('total_parameters', 0)],
174 ['', ''],
175 ['── POWER BI OBJECTS CREATED ──', ''],
176 ['Datasets Created', sum(1 for d in [Link]('conversion_details', [])
177 if [Link]('result', {}).get('datasets_created', 0) > 0
)],
178 ['Reports Created', sum(1 for d in [Link]('conversion_details', [])
179 if [Link]('result', {}).get('reports_created', 0) > 0
)],
180 ['Auto-Converted Measures', sum(
181 len([c for c in [Link]('result', {}).get('converted_components', [])
182 if [Link]('type') == 'measure'])
183 for d in [Link]('conversion_details', []))],
184 ['Manual Measures Needed', sum(
185 len([c for c in [Link]('result', {}).get('unconverted_components', [])
186 if [Link]('type') == 'measure'])
187 for d in [Link]('conversion_details', []))],
188 ]
189
190 for i, row_data in enumerate(rows, start=2):
191 [Link](row=i, column=1, value=row_data[0]).font = Font(bold=True, size=10)
192 [Link](row=i, column=2, value=row_data[1]).font = Font(size=10)
193
194 ws.column_dimensions['A'].width = 35
195 ws.column_dimensions['B'].width = 30
196
197 def _summary_object_details(self, ws, results):
198 headers = ['Object Name', 'Type', 'Status', 'Conversion Rate',
199 'Components Auto', 'Components Manual',
200 'PBI Objects', 'Datasets Created', 'Measures Created', 'Reports
Created']
201 [Link](headers)
202 _hdr_style(ws, 1)
203 for i, det in enumerate([Link]('conversion_details', []), start=2):
204 r = [Link]('result', {})
205 auto = len([c for c in [Link]('converted_components', []) if [Link]('type') !=
'dataset'])
206 manual = len([Link]('unconverted_components', []))
207 row = [
208 [Link]('object', ''),
209 [Link]('type', '').upper(),
210 [Link]('status', '').upper(),
211 f"{[Link]('conversion_rate', 0):.1f}%",
212 auto,
213 manual,
214 len([Link]('powerbi_objects', [])),
215 [Link]('datasets_created', 0),
216 [Link]('measures_created', 0),
217 [Link]('reports_created', 0),
218 ]
219 [Link](row)
220 status_cell = [Link](row=i, column=3)
221 status_cell.fill = _status_fill([Link]('status', ''))
222 _alt_row(ws, i, len(headers))
223 _auto_width(ws)
224
225 def _summary_pbi_objects(self, ws, results):
226 headers = ['Tableau Object', 'Type', 'PBI Object Name', 'PBI Object Type',
227 'PBI Object ID', 'Tables/Pages', 'Columns', 'Status']
228 [Link](headers)
229 _hdr_style(ws, 1)
230 row_idx = 2
231 for det in [Link]('conversion_details', []):
232 obj_name = [Link]('object', '')
233 obj_type = [Link]('type', '')
234 for pbi in [Link]('result', {}).get('powerbi_objects', []):
235 [Link]([
236 obj_name,
237 obj_type.upper(),
238 [Link]('name', ''),
239 [Link]('type', '').upper(),
240 [Link]('id', ''),
241 [Link]('table_count', [Link]('pages_needed', '')),
242 [Link]('column_count', ''),
243 [Link]('status', ''),
244 ])
245 [Link](row=row_idx, column=8).fill = _status_fill([Link]('status', ''))
246 row_idx += 1
247 _auto_width(ws)
248
249 def _summary_connection_map(self, ws, metadata):
250 headers = ['Workbook / Datasource', 'Tableau Connection Type', 'Power BI
Equivalent',
251 'Server', 'Database', 'Port', 'Schema', 'Action Required']
252 [Link](headers)
253 _hdr_style(ws, 1, CLR_HEADER_PURPLE)
254 row_idx = 2
255 for wb in [Link]('workbooks', []):
256 wb_name = [Link]('name', '')
257 for conn in [Link]('connections', []):
258 [Link]([
259 wb_name,
260 [Link]('type', ''),
261 [Link]('powerbi_equivalent', [Link]('type', '')),
262 [Link]('server', ''),
263 [Link]('database', ''),
264 [Link]('port', ''),
265 [Link]('schema', ''),
266 'Create matching connection in Power BI',
267 ])
268 _alt_row(ws, row_idx, len(headers))
269 row_idx += 1
270 for ds in [Link]('datasources', []):
271 ds_name = [Link]('name', '')
272 for conn in [Link]('connections', []):
273 [Link]([
274 f"[DS] {ds_name}",
275 [Link]('type', ''),
276 [Link]('powerbi_equivalent', [Link]('type', '')),
277 [Link]('server', ''),
278 [Link]('database', ''),
279 [Link]('port', ''),
280 [Link]('schema', ''),
281 'Create matching connection in Power BI',
282 ])
283 _alt_row(ws, row_idx, len(headers))
284 row_idx += 1
285 _auto_width(ws)
286
287 def _summary_successful(self, ws, results):
288 headers = ['Object Name', 'Type', 'Conversion Rate', 'PBI Objects',
289 'Datasets', 'Reports', 'Measures Auto-Converted']
290 [Link](headers)
291 _hdr_style(ws, 1, CLR_HEADER_GREEN)
292 for i, item in enumerate([Link]('successful', []), start=2):
293 d = [Link]('details', {})
294 [Link]([
295 [Link]('object', ''),
296 [Link]('type', '').upper(),
297 f"{[Link]('conversion_rate', 0):.1f}%",
298 len([Link]('powerbi_objects', [])),
299 [Link]('datasets_created', 0),
300 [Link]('reports_created', 0),
301 [Link]('measures_created', 0),
302 ])
303 _alt_row(ws, i, len(headers))
304 _auto_width(ws)
305
306 def _summary_partial(self, ws, results):
307 headers = ['Object Name', 'Type', 'Conversion Rate', 'Auto Components',
308 'Manual Components', 'Manual Measures', 'Notes']
309 [Link](headers)
310 _hdr_style(ws, 1, CLR_HEADER_ORANGE)
311 for i, item in enumerate([Link]('partial', []), start=2):
312 d = [Link]('details', {})
313 manual_m = len([c for c in [Link]('unconverted_components', []) if [Link](
'type') == 'measure'])
314 [Link]([
315 [Link]('object', ''),
316 [Link]('type', '').upper(),
317 f"{[Link]('conversion_rate', 0):.1f}%",
318 len([Link]('converted_components', [])),
319 len([Link]('unconverted_components', [])),
320 manual_m,
321 'See Manual Steps report for details',
322 ])
323 _alt_row(ws, i, len(headers))
324 _auto_width(ws)
325
326 def _summary_failed(self, ws, results):
327 headers = ['Object Name', 'Type', 'Failure Reason']
328 [Link](headers)
329 _hdr_style(ws, 1, CLR_HEADER_RED)
330 for i, item in enumerate([Link]('failed', []), start=2):
331 [Link]([
332 [Link]('object', ''),
333 [Link]('type', '').upper(),
334 [Link]('reason', 'Unknown error'),
335 ])
336 _alt_row(ws, i, len(headers))
337 _auto_width(ws)
338
339 # ─────────────────────── Report 2: Metadata Export ───────────────────────
340
341 def _gen_metadata_export(self, metadata: Dict, session_id: str) -> str:
342 fname = f"tableau_metadata_{session_id}.xlsx"
343 fpath = self.output_dir / fname
344 wb = Workbook()
345 [Link]([Link])
346
347 self._meta_workbook_inventory(wb.create_sheet("Workbook Inventory"), metadata)
348 self._meta_views(wb.create_sheet("Views"), metadata)
349 self._meta_worksheets(wb.create_sheet("Worksheets"), metadata)
350 self._meta_dashboards(wb.create_sheet("Dashboards"), metadata)
351 self._meta_connections(wb.create_sheet("Connections"), metadata)
352 self._meta_datasources(wb.create_sheet("Datasources"), metadata)
353 self._meta_parameters(wb.create_sheet("Parameters"), metadata)
354 self._meta_flows(wb.create_sheet("Prep Flows"), metadata)
355 self._meta_statistics(wb.create_sheet("Extraction Statistics"), metadata)
356
357 [Link](fpath)
358 return fname
359
360 def _meta_workbook_inventory(self, ws, metadata):
361 headers = ['Workbook Name', 'Project', 'Description', 'Created', 'Updated',
362 'Size MB', 'Views', 'Worksheets', 'Dashboards', 'Datasources',
363 'Calc Fields', 'Parameters', 'Connections', 'Tags', 'XML Extracted',
'Errors']
364 [Link](headers)
365 _hdr_style(ws, 1, CLR_HEADER_BLUE)
366 for i, wb in enumerate([Link]('workbooks', []), start=2):
367 bi = [Link]('basic_info', {})
368 st = [Link]('statistics', {})
369 [Link]([
370 [Link]('name', ''),
371 [Link]('project', ''),
372 [Link]('description', ''),
373 [Link]('created_at', ''),
374 [Link]('updated_at', ''),
375 [Link]('size_mb', 0),
376 [Link]('view_count', 0),
377 [Link]('total_worksheets', 0),
378 [Link]('total_dashboards', 0),
379 [Link]('total_datasources', 0),
380 [Link]('total_calculated_fields', 0),
381 [Link]('total_parameters', 0),
382 [Link]('total_connections', 0),
383 ', '.join([Link]('tags', [])),
384 'Yes' if [Link]('xml_extracted') else 'No',
385 '; '.join([Link]('extraction_errors', [])),
386 ])
387 _alt_row(ws, i, len(headers))
388 _auto_width(ws)
389
390 def _meta_views(self, ws, metadata):
391 headers = ['Workbook', 'View Name', 'Content URL', 'Created', 'Updated',
392 'Total Views (usage)', 'Tags']
393 [Link](headers)
394 _hdr_style(ws, 1)
395 row_idx = 2
396 for wb in [Link]('workbooks', []):
397 wb_name = [Link]('name', '')
398 for v in [Link]('views', []):
399 [Link]([
400 wb_name,
401 [Link]('name', ''),
402 [Link]('content_url', ''),
403 [Link]('created_at', ''),
404 [Link]('updated_at', ''),
405 [Link]('total_views', 0),
406 ', '.join([Link]('tags', [])),
407 ])
408 _alt_row(ws, row_idx, len(headers))
409 row_idx += 1
410 # Also standalone views
411 for v in [Link]('views', []):
412 [Link]([
413 f"[Standalone] {[Link]('workbook_id', '')}",
414 [Link]('name', ''),
415 [Link]('content_url', ''),
416 [Link]('created_at', ''),
417 [Link]('updated_at', ''),
418 [Link]('total_views', 0),
419 ', '.join([Link]('tags', [])),
420 ])
421 _alt_row(ws, row_idx, len(headers))
422 row_idx += 1
423 _auto_width(ws)
424
425 def _meta_worksheets(self, ws, metadata):
426 headers = ['Workbook', 'Worksheet Name', 'Mark Type', 'PBI Visual',
427 'Fields on Rows', 'Fields on Cols', 'Fields in Marks',
428 'Filter Count', 'Field Count', 'Dual Axis', 'Trend Line',
429 'Forecast', 'Reference Line', 'Annotations', 'Uses Params', 'Sort
Count']
430 [Link](headers)
431 _hdr_style(ws, 1)
432 row_idx = 2
433 from conversion_engine import MARK_TO_VISUAL
434 for wb in [Link]('workbooks', []):
435 wb_name = [Link]('name', '')
436 for ws_item in [Link]('worksheets', []):
437 mark = ws_item.get('mark_type', '')
438 [Link]([
439 wb_name,
440 ws_item.get('name', ''),
441 mark,
442 MARK_TO_VISUAL.get(mark, f'Review: {mark}'),
443 ', '.join(ws_item.get('fields_on_rows', [])),
444 ', '.join(ws_item.get('fields_on_cols', [])),
445 ', '.join([Link]('field', '') if isinstance(f, dict) else str(f)
446 for f in ws_item.get('fields_in_marks', [])),
447 ws_item.get('filter_count', 0),
448 ws_item.get('field_count', 0),
449 'Yes' if ws_item.get('has_dual_axis') else '',
450 'Yes' if ws_item.get('has_trend_line') else '',
451 'Yes' if ws_item.get('has_forecast') else '',
452 'Yes' if ws_item.get('has_reference_line') else '',
453 'Yes' if ws_item.get('has_annotations') else '',
454 'Yes' if ws_item.get('has_parameters') else '',
455 ws_item.get('sort_count', 0),
456 ])
457 _alt_row(ws, row_idx, len(headers))
458 row_idx += 1
459 _auto_width(ws, max_w=60)
460
461 def _meta_dashboards(self, ws, metadata):
462 headers = ['Workbook', 'Dashboard Name', 'Width', 'Height', 'Zones',
463 'Worksheets Used', 'Device Layouts', 'Filter Actions', 'URL Actions',
464 'Highlight Actions', 'Navigate Actions', 'Set Actions', 'Param
Actions']
465 [Link](headers)
466 _hdr_style(ws, 1)
467 row_idx = 2
468 for wb in [Link]('workbooks', []):
469 wb_name = [Link]('name', '')
470 for db in [Link]('dashboards', []):
471 [Link]([
472 wb_name,
473 [Link]('name', ''),
474 [Link]('width', ''),
475 [Link]('height', ''),
476 [Link]('zone_count', 0),
477 ', '.join([Link]('worksheets_used', [])),
478 'Yes' if [Link]('has_device_layouts') else 'No',
479 'Yes' if [Link]('has_filter_actions') else '',
480 'Yes' if [Link]('has_url_actions') else '',
481 'Yes' if [Link]('has_highlight_actions') else '',
482 'Yes' if [Link]('has_navigation_actions') else '',
483 'Yes' if [Link]('has_set_actions') else '',
484 'Yes' if [Link]('has_parameter_actions') else '',
485 ])
486 _alt_row(ws, row_idx, len(headers))
487 row_idx += 1
488 _auto_width(ws)
489
490 def _meta_connections(self, ws, metadata):
491 headers = ['Workbook / Datasource', 'Named Connection', 'Tableau Type',
492 'Power BI Equivalent', 'Server', 'Port', 'Database',
493 'Schema', 'Warehouse', 'Catalog', 'File Path', 'Authentication']
494 [Link](headers)
495 _hdr_style(ws, 1, CLR_HEADER_PURPLE)
496 row_idx = 2
497 for wb in [Link]('workbooks', []):
498 for conn in [Link]('connections', []):
499 [Link]([
500 [Link]('name', ''),
501 [Link]('named_connection', ''),
502 [Link]('type', ''),
503 [Link]('powerbi_equivalent', ''),
504 [Link]('server', ''),
505 [Link]('port', ''),
506 [Link]('database', ''),
507 [Link]('schema', ''),
508 [Link]('warehouse', ''),
509 [Link]('catalog', ''),
510 [Link]('filepath', ''),
511 [Link]('authentication', ''),
512 ])
513 _alt_row(ws, row_idx, len(headers))
514 row_idx += 1
515 for ds in [Link]('datasources', []):
516 for conn in [Link]('connections', []):
517 [Link]([
518 f"[DS] {[Link]('name', '')}",
519 [Link]('named_connection', ''),
520 [Link]('type', ''),
521 [Link]('powerbi_equivalent', ''),
522 [Link]('server', ''),
523 [Link]('port', ''),
524 [Link]('database', ''),
525 [Link]('schema', ''),
526 [Link]('warehouse', ''),
527 [Link]('catalog', ''),
528 [Link]('filepath', ''),
529 [Link]('authentication', ''),
530 ])
531 _alt_row(ws, row_idx, len(headers))
532 row_idx += 1
533 _auto_width(ws)
534
535 def _meta_datasources(self, ws, metadata):
536 headers = ['Workbook', 'Datasource Name', 'Caption', 'Inline',
537 'Column Count', 'Measure Count', 'Dimension Count',
538 'Calc Field Count', 'Group Count', 'Set Count',
539 'Primary Connection Type', 'Relation Type']
540 [Link](headers)
541 _hdr_style(ws, 1)
542 row_idx = 2
543 for wb in [Link]('workbooks', []):
544 wb_name = [Link]('name', '')
545 for ds in [Link]('datasources', []):
546 conns = [Link]('connections', [])
547 prim_conn = conns[0].get('type', '') if conns else ''
548 rel = [Link]('relation', {})
549 [Link]([
550 wb_name,
551 [Link]('name', ''),
552 [Link]('caption', ''),
553 'Yes' if [Link]('inline') else 'No',
554 [Link]('column_count', len([Link]('columns', []))),
555 [Link]('measure_count', 0),
556 [Link]('dimension_count', 0),
557 [Link]('calculated_field_count', len([Link]('calculated_fields',
[]))),
558 len([Link]('groups', [])),
559 len([Link]('sets', [])),
560 prim_conn,
561 [Link]('type', ''),
562 ])
563 _alt_row(ws, row_idx, len(headers))
564 row_idx += 1
565 # Standalone datasources
566 for ds in [Link]('datasources', []):
567 conns = [Link]('connections', [])
568 prim_conn = conns[0].get('type', '') if conns else ''
569 [Link]([
570 '[Standalone]',
571 [Link]('name', ''),
572 '',
573 '',
574 len([Link]('columns', [])),
575 [Link]('statistics', {}).get('measure_count', 0),
576 [Link]('statistics', {}).get('dimension_count', 0),
577 len([Link]('calculated_fields', [])),
578 '', '',
579 prim_conn,
580 '',
581 ])
582 _alt_row(ws, row_idx, len(headers))
583 row_idx += 1
584 _auto_width(ws)
585
586 def _meta_parameters(self, ws, metadata):
587 headers = ['Workbook', 'Parameter Name', 'Caption', 'Data Type',
588 'Domain Type', 'Default Value', 'Allowed Values / Range']
589 [Link](headers)
590 _hdr_style(ws, 1, CLR_HEADER_ORANGE)
591 row_idx = 2
592 for wb in [Link]('workbooks', []):
593 wb_name = [Link]('name', '')
594 for p in [Link]('parameters', []):
595 allowed = [Link]('allowed_values', [])
596 allowed_str = ''
597 if allowed:
598 if allowed[0].get('type') == 'range':
599 r = allowed[0]
600 allowed_str = f"Range: step={[Link]('min', '')}, max={[Link]('max',
'')}"
601 else:
602 vals = [str([Link]('value', '')) for a in allowed[:15]]
603 allowed_str = ', '.join(vals)
604 [Link]([
605 wb_name,
606 [Link]('name', ''),
607 [Link]('caption', ''),
608 [Link]('datatype', ''),
609 [Link]('domain_type', ''),
610 [Link]('default_value', ''),
611 allowed_str,
612 ])
613 _alt_row(ws, row_idx, len(headers))
614 row_idx += 1
615 _auto_width(ws)
616
617 def _meta_statistics(self, ws, metadata):
618 ws['A1'] = 'EXTRACTION STATISTICS'
619 ws['A1'].font = Font(size=13, bold=True, color='FFFFFF')
620 ws['A1'].fill = PatternFill(start_color=CLR_HEADER_BLUE, end_color=
CLR_HEADER_BLUE, fill_type='solid')
621 ws.merge_cells('A1:C1')
622
623 summary = [Link]('summary', {})
624 rows = [
625 ['Extraction Date', [Link]('extraction_date', '')],
626 ['Session Dir', [Link]('session_dir', '')],
627 ['Total Objects', [Link]('total_objects', 0)],
628 ['', ''],
629 ['Workbooks Processed', [Link]('workbooks_processed', 0)],
630 ['Datasources Processed', [Link]('datasources_processed', 0)],
631 ['Views Processed', [Link]('views_processed', 0)],
632 ['', ''],
633 ['Total Worksheets', [Link]('total_worksheets', 0)],
634 ['Total Dashboards', [Link]('total_dashboards', 0)],
635 ['Total Calculated Fields', [Link]('total_calculations', 0)],
636 ['Total Columns/Fields', [Link]('total_columns', 0)],
637 ['Total Connections', [Link]('total_connections', 0)],
638 ['Total Parameters', [Link]('total_parameters', 0)],
639 ['', ''],
640 ]
641 # Per-workbook XML extraction status
642 [Link](row=len(rows) + 3, column=1).value = 'XML Extraction Status:'
643 [Link](row=len(rows) + 3, column=1).font = Font(bold=True)
644 row_offset = len(rows) + 4
645 for wb in [Link]('workbooks', []):
646 [Link](row=row_offset, column=1).value = [Link]('name', '')
647 [Link](row=row_offset, column=2).value = 'XML Extracted' if [Link](
'xml_extracted') else 'API Only'
648 [Link](row=row_offset, column=2).fill = (
649 PatternFill(start_color=CLR_SUCCESS, end_color=CLR_SUCCESS, fill_type=
'solid')
650 if [Link]('xml_extracted') else
651 PatternFill(start_color=CLR_PARTIAL, end_color=CLR_PARTIAL, fill_type=
'solid')
652 )
653 row_offset += 1
654
655 for i, (k, v) in enumerate(rows, start=2):
656 [Link](row=i, column=1, value=k).font = Font(bold=True, size=10)
657 [Link](row=i, column=2, value=v).font = Font(size=10)
658
659 _auto_width(ws)
660
661 # ─────────────────────── Report 3: Manual Steps ──────────────────────────
662
663
664 def _meta_flows(self, ws, metadata):
665 """Sheet: Tableau Prep Flows"""
666 headers = ['Flow Name', 'Project', 'Created', 'Updated', 'Description',
'Conversion Note']
667 [Link](headers)
668 _hdr_style(ws, 1)
669 row_idx = 2
670 flows = [Link]('flows', [])
671 if not flows:
672 [Link](['No Prep Flows extracted'])
673 _auto_width(ws)
674 return
675 for fl in flows:
676 [Link]([
677 [Link]('name', ''),
678 [Link]('project_name', ''),
679 str([Link]('created_at', ''))[:10],
680 str([Link]('updated_at', ''))[:10],
681 [Link]('description', ''),
682 [Link]('note', 'Must be recreated manually as Power BI Dataflow or Power
Query'),
683 ])
684 _alt_row(ws, row_idx, len(headers))
685 row_idx += 1
686 _auto_width(ws, max_w=80)
687
688 def _gen_manual_steps(self, results: Dict, session_id: str) -> str:
689 fname = f"manual_steps_{session_id}.xlsx"
690 fpath = self.output_dir / fname
691 wb = Workbook()
692 [Link]([Link])
693
694 all_steps = [Link]('manual_steps_required', [])
695
696 # Summary sheet
697 ws_summ = wb.create_sheet("Steps Summary")
698 self._manual_steps_summary(ws_summ, all_steps)
699
700 # Group by type
701 type_groups = {}
702 for step in all_steps:
703 t = [Link]('type', 'other')
704 type_groups.setdefault(t, []).append(step)
705
706 type_sheet_names = {
707 'connection': 'Data Connections',
708 'data_model': 'Data Model',
709 'parameters': 'Parameters',
710 'manual_dax': 'Manual DAX',
711 'worksheet': 'Worksheets',
712 'dashboard': 'Dashboards',
713 'validation': 'Validation',
714 'datasource': 'Datasources',
715 'view': 'Views',
716 }
717 for t, steps in type_groups.items():
718 sheet_name = type_sheet_names.get(t, [Link]())[:31]
719 ws = wb.create_sheet(sheet_name)
720 self._manual_steps_detail(ws, steps, t)
721
722 [Link](fpath)
723 return fname
724
725 def _manual_steps_summary(self, ws, steps):
726 [Link] = "Steps Summary"
727 headers = ['Step #', 'Type', 'Object Name', 'Workbook',
728 'Complexity', 'Est. Time (min)', 'Status']
729 [Link](headers)
730 _hdr_style(ws, 1, CLR_HEADER_ORANGE)
731 type_clr = {
732 'connection': 'D6E4FF',
733 'data_model': 'D9F0D9',
734 'parameters': 'FFF2CC',
735 'manual_dax': 'FFD7D7',
736 'worksheet': 'E8F4FD',
737 'dashboard': 'F0E8FF',
738 'validation': 'F5F5F5',
739 }
740 for i, step in enumerate(steps, start=1):
741 [Link]([
742 i,
743 [Link]('type', '').upper(),
744 [Link]('name', ''),
745 [Link]('workbook', ''),
746 [Link]('complexity', 'Medium'),
747 [Link]('estimated_time', ''),
748 'Pending',
749 ])
750 t = [Link]('type', '')
751 clr = type_clr.get(t, 'FFFFFF')
752 for c in range(1, 8):
753 [Link](row=i + 1, column=c).fill = PatternFill(
754 start_color=clr, end_color=clr, fill_type='solid')
755 _auto_width(ws)
756
757 def _manual_steps_detail(self, ws, steps, step_type):
758 headers = ['Step #', 'Object Name', 'Workbook', 'Complexity', 'Est. Time',
'Detailed Instructions']
759 [Link](headers)
760 _hdr_style(ws, 1, CLR_HEADER_ORANGE)
761 ws.column_dimensions['F'].width = 120
762 for i, step in enumerate(steps, start=1):
763 [Link]([
764 i,
765 [Link]('name', ''),
766 [Link]('workbook', ''),
767 [Link]('complexity', ''),
768 [Link]('estimated_time', ''),
769 [Link]('instructions', ''),
770 ])
771 [Link](row=i + 1, column=6).alignment = Alignment(wrap_text=True, vertical=
'top')
772 ws.row_dimensions[i + 1].height = max(80, min(len([Link]('instructions', ''
)) // 3, 400))
773 _alt_row(ws, i + 1, len(headers))
774 for c in ['A', 'B', 'C', 'D', 'E']:
775 ws.column_dimensions[c].width = 25
776
777 # ─────────────────────── Report 4: DAX Formulas ──────────────────────────
778
779 def _gen_dax_report(self, results: Dict, metadata: Dict, session_id: str) -> str:
780 fname = f"dax_formulas_{session_id}.xlsx"
781 fpath = self.output_dir / fname
782 wb = Workbook()
783 [Link]([Link])
784
785 # All auto-converted
786 ws_auto = wb.create_sheet("Auto-Converted")
787 self._dax_auto(ws_auto, results)
788
789 # All manual
790 ws_manual = wb.create_sheet("Manual Required")
791 self._dax_manual(ws_manual, results)
792
793 # Full formula catalog (from metadata – all calcs including unconverted)
794 ws_cat = wb.create_sheet("Full Formula Catalog")
795 self._dax_catalog(ws_cat, metadata)
796
797 [Link](fpath)
798 return fname
799
800 def _dax_auto(self, ws, results):
801 headers = ['Workbook/Object', 'Field Name', 'Tableau Formula', 'DAX Formula',
802 'Complexity', 'Functions Used', 'Status']
803 [Link](headers)
804 _hdr_style(ws, 1, CLR_HEADER_GREEN)
805 row_idx = 2
806 for det in [Link]('conversion_details', []):
807 obj_name = [Link]('object', '')
808 for comp in [Link]('result', {}).get('converted_components', []):
809 if [Link]('type') == 'measure':
810 [Link]([
811 obj_name,
812 [Link]('name', ''),
813 [Link]('tableau_formula', ''),
814 [Link]('dax_formula', [Link]('dax', '')),
815 [Link]('complexity', ''),
816 ', '.join([Link]('functions_used', [])),
817 '✓ Auto-Converted',
818 ])
819 for c in range(1, 8):
820 [Link](row=row_idx, column=c).fill = PatternFill(
821 start_color=CLR_CALC_LOW, end_color=CLR_CALC_LOW, fill_type=
'solid')
822 [Link](row=row_idx, column=3).alignment = Alignment(wrap_text=True)
823 [Link](row=row_idx, column=4).alignment = Alignment(wrap_text=True)
824 row_idx += 1
825 _auto_width(ws, max_w=80)
826
827 def _dax_manual(self, ws, results):
828 headers = ['Workbook/Object', 'Field Name', 'Tableau Formula', 'DAX Draft /
Notes',
829 'Complexity', 'Functions Used', 'Reason']
830 [Link](headers)
831 _hdr_style(ws, 1, CLR_HEADER_RED)
832 row_idx = 2
833 for det in [Link]('conversion_details', []):
834 obj_name = [Link]('object', '')
835 for comp in [Link]('result', {}).get('unconverted_components', []):
836 if [Link]('type') == 'measure':
837 complexity = [Link]('complexity', '')
838 clr = {'Very High': CLR_CALC_HIGH, 'High': CLR_CALC_HIGH,
839 'Medium': CLR_CALC_MED, 'Low': CLR_CALC_LOW}.get(complexity,
 'FFFFFF')
840 [Link]([
841 obj_name,
842 [Link]('name', ''),
843 [Link]('tableau_formula', ''),
844 [Link]('dax_formula', [Link]('dax', '')),
845 complexity,
846 ', '.join([Link]('functions_used', [])),
847 [Link]('reason', ''),
848 ])
849 for c in range(1, 8):
850 [Link](row=row_idx, column=c).fill = PatternFill(
851 start_color=clr, end_color=clr, fill_type='solid')
852 [Link](row=row_idx, column=3).alignment = Alignment(wrap_text=True)
853 [Link](row=row_idx, column=4).alignment = Alignment(wrap_text=True)
854 row_idx += 1
855 _auto_width(ws, max_w=80)
856
857 def _dax_catalog(self, ws, metadata):
858 headers = ['Workbook', 'Datasource', 'Field Name', 'Tableau Formula',
859 'Data Type', 'Complexity', 'Functions Used',
860 'Referenced Fields', 'LOD / Table Calc']
861 [Link](headers)
862 _hdr_style(ws, 1, CLR_HEADER_PURPLE)
863 row_idx = 2
864 for wb in [Link]('workbooks', []):
865 wb_name = [Link]('name', '')
866 # Calcs from XML per datasource
867 for ds in [Link]('datasources', []):
868 ds_name = [Link]('caption', [Link]('name', ''))
869 for calc in [Link]('calculated_fields', []):
870 self._dax_catalog_row(ws, wb_name, ds_name, calc, row_idx)
871 row_idx += 1
872 # Top-level calcs
873 for calc in [Link]('calculated_fields', []):
874 self._dax_catalog_row(ws, wb_name, '', calc, row_idx)
875 row_idx += 1
876 # Standalone datasource calcs
877 for ds in [Link]('datasources', []):
878 ds_name = [Link]('name', '')
879 for calc in [Link]('calculated_fields', []):
880 self._dax_catalog_row(ws, '[Standalone DS]', ds_name, calc, row_idx)
881 row_idx += 1
882 _auto_width(ws, max_w=80)
883
884 def _dax_catalog_row(self, ws, wb_name, ds_name, calc, row_idx):
885 complexity = [Link]('complexity', '')
886 funcs = [Link]('functions_used', [])
887 is_lod = '{' in ([Link]('formula', '')) or any(
888 f in funcs for f in ('FIXED', 'INCLUDE', 'EXCLUDE'))
889 is_table_calc = any(f in funcs for f in (
890 'LOOKUP', 'WINDOW_SUM', 'RUNNING_SUM', 'SIZE', 'INDEX', 'FIRST', 'LAST'))
891 flag = ('LOD' if is_lod else '') + (' Table Calc' if is_table_calc else '')
892 clr = {'Very High': CLR_CALC_HIGH, 'High': CLR_CALC_HIGH,
893 'Medium': CLR_CALC_MED, 'Low': CLR_CALC_LOW}.get(complexity, 'FFFFFF')
894 [Link]([
895 wb_name, ds_name,
896 [Link]('caption', [Link]('name', '')),
897 [Link]('formula', ''),
898 [Link]('datatype', ''),
899 complexity,
900 ', '.join(funcs),
901 ', '.join([Link]('referenced_fields', [])),
902 flag,
903 ])
904 for c in range(1, 10):
905 [Link](row=row_idx, column=c).fill = PatternFill(
906 start_color=clr, end_color=clr, fill_type='solid')
907 [Link](row=row_idx, column=4).alignment = Alignment(wrap_text=True)
908
909 # ─────────────────────── Report 5: Field Catalog ─────────────────────────
910
911 def _gen_field_catalog(self, metadata: Dict, session_id: str) -> str:
912 fname = f"field_catalog_{session_id}.xlsx"
913 fpath = self.output_dir / fname
914 wb = Workbook()
915 [Link]([Link])
916
917 ws = wb.create_sheet("Field Catalog")
918 headers = ['Workbook', 'Datasource', 'Field Name (Caption)', 'Internal Name',
919 'Data Type', 'Power BI Type', 'Role', 'Default Aggregation',
920 'Semantic Role', 'Hidden', 'Is Calculated', 'Formula', 'Complexity',
921 'Referenced Fields', 'Groups/Sets', 'Aliases Count']
922 [Link](headers)
923 _hdr_style(ws, 1, CLR_HEADER_BLUE)
924
925 dtype_map = {
926 'string': 'Text', 'integer': 'Whole Number', 'real': 'Decimal Number',
927 'boolean': 'True/False', 'date': 'Date', 'datetime': 'Date/Time', 'spatial':
'Text',
928 }
929 row_idx = 2
930 for wb_meta in [Link]('workbooks', []):
931 wb_name = wb_meta.get('name', '')
932 for ds in wb_meta.get('datasources', []):
933 ds_name = [Link]('caption', [Link]('name', ''))
934 all_cols = [Link]('all_columns', []) or [Link]('columns', [])
935 calcs = [Link]('calculated_fields', [])
936 groups = {[Link]('field', ''): [Link]('name', '') for g in [Link]('groups',
[])}
937 sets_list = {[Link]('name', '') for s in [Link]('sets', [])}
938 for col in all_cols + calcs:
939 field_name = [Link]('name', '')
940 grp = [Link](field_name, '')
941 in_set = 'Yes' if field_name in sets_list else ''
942 [Link]([
943 wb_name, ds_name,
944 [Link]('caption', field_name),
945 [Link]('raw_name', field_name),
946 [Link]('datatype', ''),
947 dtype_map.get([Link]('datatype', '').lower(), [Link]('datatype'
, '')),
948 [Link]('role', ''),
949 [Link]('default_aggregation', ''),
950 [Link]('semantic_role', ''),
951 'Yes' if [Link]('hidden') else '',
952 'Yes' if [Link]('is_calculated') else '',
953 [Link]('formula', ''),
954 [Link]('complexity', ''),
955 ', '.join([Link]('referenced_fields', [])),
956 grp or in_set,
957 len([Link]('aliases', {})),
958 ])
959 if [Link]('is_calculated'):
960 [Link](row=row_idx, column=11).fill = PatternFill(
961 start_color='FDEBD0', end_color='FDEBD0', fill_type='solid')
962 _alt_row(ws, row_idx, len(headers))
963 row_idx += 1
964
965 # Standalone datasource fields
966 for ds in [Link]('datasources', []):
967 ds_name = [Link]('name', '')
968 for col in ([Link]('columns', []) + [Link]('calculated_fields', [])):
969 [Link]([
970 '[Standalone DS]', ds_name,
971 [Link]('caption', [Link]('name', '')),
972 [Link]('raw_name', [Link]('name', '')),
973 [Link]('datatype', ''),
974 dtype_map.get([Link]('datatype', '').lower(), ''),
975 [Link]('role', ''),
976 [Link]('default_aggregation', ''),
977 [Link]('semantic_role', ''),
978 'Yes' if [Link]('hidden') else '',
979 'Yes' if [Link]('is_calculated') else '',
980 [Link]('formula', ''),
981 [Link]('complexity', ''),
982 ', '.join([Link]('referenced_fields', [])),
983 '',
984 len([Link]('aliases', {})),
985 ])
986 _alt_row(ws, row_idx, len(headers))
987 row_idx += 1
988
989 _auto_width(ws, max_w=60)
990 [Link](fpath)
991 return fname
992