65 lines
2 KiB
Python
65 lines
2 KiB
Python
|
#!/usr/bin/env python
|
||
|
|
# -*- coding: utf-8 -*-
|
||
|
|
|
||
|
|
import os
|
||
|
|
import sys
|
||
|
|
import re
|
||
|
|
|
||
|
|
# Add repository root and 'ntuh' directory to sys.path
|
||
|
|
script_dir = os.path.dirname(os.path.abspath(__file__))
|
||
|
|
base_dir = os.path.dirname(script_dir)
|
||
|
|
ntuh_dir = os.path.join(base_dir, 'ntuh')
|
||
|
|
|
||
|
|
if base_dir not in sys.path:
|
||
|
|
sys.path.insert(0, base_dir)
|
||
|
|
if ntuh_dir not in sys.path:
|
||
|
|
sys.path.insert(0, ntuh_dir)
|
||
|
|
|
||
|
|
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'ntuh.settings')
|
||
|
|
|
||
|
|
import django
|
||
|
|
django.setup()
|
||
|
|
|
||
|
|
import pandas as pd
|
||
|
|
from registry.models import PathologyReport
|
||
|
|
|
||
|
|
illegal_re = re.compile(r'[\x00-\x08\x0b-\x0c\x0e-\x1f]')
|
||
|
|
|
||
|
|
|
||
|
|
def clean_string_for_excel(val):
|
||
|
|
if not isinstance(val, str):
|
||
|
|
return val
|
||
|
|
# Remove illegal XML / openpyxl control characters
|
||
|
|
val = illegal_re.sub('', val)
|
||
|
|
# Strip unnecessary head, style, script, meta, link tags so report body fits within cell limits
|
||
|
|
h = re.sub(r'<(?:head|style|script|meta|link)[^>]*?>.*?</(?:head|style|script|meta|link)>', '', val, flags=re.DOTALL | re.IGNORECASE)
|
||
|
|
h = re.sub(r'<(?:meta|link)[^>]*?>', '', h, flags=re.IGNORECASE)
|
||
|
|
h = re.sub(r'\n\s*\n', '\n', h)
|
||
|
|
return h.strip()
|
||
|
|
|
||
|
|
|
||
|
|
def fetch_pathology_reports(output_file=None):
|
||
|
|
if output_file is None:
|
||
|
|
output_file = os.path.join(script_dir, 'registry_pathologyreport.xlsx')
|
||
|
|
|
||
|
|
print(f"Fetching PathologyReport records from database...")
|
||
|
|
qs = PathologyReport.objects.all().values()
|
||
|
|
data = list(qs)
|
||
|
|
|
||
|
|
print(f"Cleaning string fields & HTML body for Excel cell limit...")
|
||
|
|
for item in data:
|
||
|
|
for k, v in item.items():
|
||
|
|
if isinstance(v, str):
|
||
|
|
item[k] = clean_string_for_excel(v)
|
||
|
|
|
||
|
|
df = pd.DataFrame(data)
|
||
|
|
print(f"Fetched {len(df)} rows. Writing to {output_file}...")
|
||
|
|
df.to_excel(output_file, index=False)
|
||
|
|
print(f"Successfully exported {len(df)} rows to {output_file}")
|
||
|
|
|
||
|
|
|
||
|
|
if __name__ == '__main__':
|
||
|
|
default_output = os.path.join(script_dir, 'registry_pathologyreport.xlsx')
|
||
|
|
target_path = sys.argv[1] if len(sys.argv) > 1 else default_output
|
||
|
|
fetch_pathology_reports(target_path)
|