""" Enhanced Excel Generator with Beautiful Formatting and Charts Professional Excel reports with charts, conditional formatting, and modern styling """ import io import xlsxwriter from datetime import datetime from decimal import Decimal import logging logger = logging.getLogger(__name__) class EnhancedExcelGenerator: """Generate beautiful, professional Excel reports with charts and formatting""" def __init__(self): self.colors = { 'primary': '#3B82F6', 'secondary': '#10B981', 'accent': '#F59E0B', 'danger': '#EF4444', 'dark': '#1F2937', 'light_gray': '#F3F4F6', 'medium_gray': '#9CA3AF', 'white': '#FFFFFF' } def _create_formats(self, workbook): """Create custom formats for professional appearance""" formats = {} # Title format formats['title'] = workbook.add_format({ 'font_size': 18, 'bold': True, 'font_color': self.colors['primary'], 'align': 'center', 'valign': 'vcenter', 'bg_color': self.colors['light_gray'], 'border': 1, 'border_color': self.colors['medium_gray'] }) # Header format formats['header'] = workbook.add_format({ 'font_size': 12, 'bold': True, 'font_color': 'white', 'align': 'center', 'valign': 'vcenter', 'bg_color': self.colors['primary'], 'border': 1, 'border_color': self.colors['primary'] }) # Subheader format formats['subheader'] = workbook.add_format({ 'font_size': 11, 'bold': True, 'font_color': self.colors['dark'], 'align': 'left', 'valign': 'vcenter', 'bg_color': self.colors['light_gray'], 'border': 1, 'border_color': self.colors['medium_gray'] }) # Data formats formats['data'] = workbook.add_format({ 'font_size': 10, 'align': 'left', 'valign': 'vcenter', 'border': 1, 'border_color': self.colors['medium_gray'] }) formats['data_center'] = workbook.add_format({ 'font_size': 10, 'align': 'center', 'valign': 'vcenter', 'border': 1, 'border_color': self.colors['medium_gray'] }) formats['data_right'] = workbook.add_format({ 'font_size': 10, 'align': 'right', 'valign': 'vcenter', 'border': 1, 'border_color': self.colors['medium_gray'] }) # Currency format formats['currency'] = workbook.add_format({ 'font_size': 10, 'align': 'right', 'valign': 'vcenter', 'num_format': 'KES #,##0.00', 'border': 1, 'border_color': self.colors['medium_gray'] }) # Percentage format formats['percentage'] = workbook.add_format({ 'font_size': 10, 'align': 'right', 'valign': 'vcenter', 'num_format': '0.0%', 'border': 1, 'border_color': self.colors['medium_gray'] }) # Date format formats['date'] = workbook.add_format({ 'font_size': 10, 'align': 'center', 'valign': 'vcenter', 'num_format': 'dd/mm/yyyy', 'border': 1, 'border_color': self.colors['medium_gray'] }) # Status formats formats['status_active'] = workbook.add_format({ 'font_size': 10, 'align': 'center', 'valign': 'vcenter', 'bg_color': self.colors['secondary'], 'font_color': 'white', 'border': 1, 'border_color': self.colors['medium_gray'] }) formats['status_overdue'] = workbook.add_format({ 'font_size': 10, 'align': 'center', 'valign': 'vcenter', 'bg_color': self.colors['danger'], 'font_color': 'white', 'border': 1, 'border_color': self.colors['medium_gray'] }) formats['status_completed'] = workbook.add_format({ 'font_size': 10, 'align': 'center', 'valign': 'vcenter', 'bg_color': self.colors['primary'], 'font_color': 'white', 'border': 1, 'border_color': self.colors['medium_gray'] }) # Summary format formats['summary'] = workbook.add_format({ 'font_size': 11, 'bold': True, 'font_color': self.colors['dark'], 'align': 'right', 'valign': 'vcenter', 'bg_color': self.colors['accent'], 'border': 2, 'border_color': self.colors['dark'] }) return formats def _add_chart(self, workbook, worksheet, chart_type, data_range, title, position): """Add a chart to the worksheet""" try: chart = workbook.add_chart({'type': chart_type}) if chart_type == 'pie': chart.add_series({ 'categories': data_range['categories'], 'values': data_range['values'], 'data_labels': {'percentage': True}, }) elif chart_type in ['column', 'bar', 'line']: chart.add_series({ 'categories': data_range['categories'], 'values': data_range['values'], 'name': title, }) chart.set_title({'name': title, 'name_font': {'size': 14, 'bold': True}}) chart.set_style(10) # Modern style chart.set_size({'width': 480, 'height': 288}) worksheet.insert_chart(position, chart) except Exception as e: logger.error(f"Chart creation failed: {e}") def generate_dashboard_excel(self, data): """Generate comprehensive dashboard Excel report""" try: buffer = io.BytesIO() workbook = xlsxwriter.Workbook(buffer, {'in_memory': True}) formats = self._create_formats(workbook) # Summary Sheet summary_sheet = workbook.add_worksheet('Dashboard Summary') self._create_summary_sheet(summary_sheet, data.get('summary', {}), formats) # Portfolio Sheet if 'portfolio' in data: portfolio_sheet = workbook.add_worksheet('Portfolio Analysis') self._create_portfolio_sheet(portfolio_sheet, data['portfolio'], formats, workbook) # Loans Sheet if 'loans' in data: loans_sheet = workbook.add_worksheet('Loan Details') self._create_loans_sheet(loans_sheet, data['loans'], formats) # Risk Analysis Sheet if 'risk' in data: risk_sheet = workbook.add_worksheet('Risk Analysis') self._create_risk_sheet(risk_sheet, data['risk'], formats, workbook) # Financial Sheet if 'financial' in data: financial_sheet = workbook.add_worksheet('Financial Summary') self._create_financial_sheet(financial_sheet, data['financial'], formats, workbook) workbook.close() buffer.seek(0) return buffer except Exception as e: logger.error(f"Dashboard Excel generation failed: {e}") return None def _create_summary_sheet(self, worksheet, data, formats): """Create the summary dashboard sheet""" # Title worksheet.merge_range('A1:F1', 'DASHBOARD SUMMARY REPORT', formats['title']) worksheet.merge_range('A2:F2', f'Generated: {datetime.now().strftime("%B %d, %Y at %I:%M %p")}', formats['data_center']) # Key Metrics row = 4 worksheet.write(row, 0, 'KEY PERFORMANCE INDICATORS', formats['subheader']) row += 2 # Create KPI table kpi_headers = ['Metric', 'Value', 'Previous Period', 'Change', 'Status'] for col, header in enumerate(kpi_headers): worksheet.write(row, col, header, formats['header']) row += 1 # Sample KPIs (replace with actual data) kpis = [ ['Total Loans', data.get('total_loans', 0), 0, 0, 'Active'], ['Active Loans', data.get('active_loans', 0), 0, 0, 'Active'], ['Total Disbursed', data.get('total_disbursed', 0), 0, 0, 'Active'], ['Collection Rate', data.get('collection_rate', 0), 0, 0, 'Active'], ['Default Rate', data.get('default_rate', 0), 0, 0, 'Active'] ] for kpi in kpis: worksheet.write(row, 0, kpi[0], formats['data']) if 'amount' in kpi[0].lower() or 'disbursed' in kpi[0].lower(): worksheet.write(row, 1, kpi[1], formats['currency']) elif 'rate' in kpi[0].lower(): worksheet.write(row, 1, kpi[1]/100, formats['percentage']) else: worksheet.write(row, 1, kpi[1], formats['data_right']) worksheet.write(row, 2, kpi[2], formats['data_right']) worksheet.write(row, 3, kpi[3], formats['data_right']) worksheet.write(row, 4, kpi[4], formats['status_active']) row += 1 # Set column widths worksheet.set_column('A:A', 20) worksheet.set_column('B:E', 15) worksheet.set_column('F:F', 12) def _create_portfolio_sheet(self, worksheet, data, formats, workbook): """Create portfolio analysis sheet with charts""" # Title worksheet.merge_range('A1:H1', 'PORTFOLIO ANALYSIS', formats['title']) worksheet.merge_range('A2:H2', f'Generated: {datetime.now().strftime("%B %d, %Y")}', formats['data_center']) # Portfolio Summary row = 4 worksheet.write(row, 0, 'PORTFOLIO SUMMARY', formats['subheader']) row += 2 # Summary data summary_data = data.get('summary', {}) summary_items = [ ['Total Portfolio Value', summary_data.get('total_value', 0)], ['Outstanding Amount', summary_data.get('outstanding', 0)], ['Collection Rate', summary_data.get('collection_rate', 0)], ['Portfolio at Risk', summary_data.get('par', 0)] ] for item in summary_items: worksheet.write(row, 0, item[0], formats['data']) if 'rate' in item[0].lower() or 'risk' in item[0].lower(): worksheet.write(row, 1, item[1]/100, formats['percentage']) else: worksheet.write(row, 1, item[1], formats['currency']) row += 1 # Add portfolio distribution chart if 'distribution' in data: chart_data = data['distribution'] categories_range = f"'Portfolio Analysis'!A{row+3}:A{row+3+len(chart_data)-1}" values_range = f"'Portfolio Analysis'!B{row+3}:B{row+3+len(chart_data)-1}" # Write chart data row += 3 worksheet.write(row-1, 0, 'Portfolio Distribution', formats['subheader']) for i, (category, value) in enumerate(chart_data.items()): worksheet.write(row+i, 0, category, formats['data']) worksheet.write(row+i, 1, value, formats['currency']) # Add pie chart self._add_chart(workbook, worksheet, 'pie', { 'categories': categories_range, 'values': values_range }, 'Portfolio Distribution', 'D4') # Set column widths worksheet.set_column('A:A', 25) worksheet.set_column('B:H', 15) def _create_loans_sheet(self, worksheet, data, formats): """Create detailed loans sheet""" # Title worksheet.merge_range('A1:J1', 'LOAN DETAILS', formats['title']) worksheet.merge_range('A2:J2', f'Generated: {datetime.now().strftime("%B %d, %Y")}', formats['data_center']) # Headers row = 4 headers = ['Loan #', 'Borrower', 'Amount', 'Disbursed', 'Due Date', 'Status', 'Paid', 'Balance', 'Days Overdue', 'Risk Level'] for col, header in enumerate(headers): worksheet.write(row, col, header, formats['header']) row += 1 # Loan data loans = data.get('details', []) for loan in loans: worksheet.write(row, 0, loan.get('loan_number', ''), formats['data']) worksheet.write(row, 1, loan.get('borrower', ''), formats['data']) worksheet.write(row, 2, loan.get('amount', 0), formats['currency']) worksheet.write(row, 3, loan.get('disbursed_date', ''), formats['date']) worksheet.write(row, 4, loan.get('due_date', ''), formats['date']) # Status with conditional formatting status = loan.get('status', '').lower() if status == 'active': worksheet.write(row, 5, loan.get('status', ''), formats['status_active']) elif status == 'overdue': worksheet.write(row, 5, loan.get('status', ''), formats['status_overdue']) else: worksheet.write(row, 5, loan.get('status', ''), formats['status_completed']) worksheet.write(row, 6, loan.get('amount_paid', 0), formats['currency']) worksheet.write(row, 7, loan.get('balance', 0), formats['currency']) worksheet.write(row, 8, loan.get('days_overdue', 0), formats['data_center']) worksheet.write(row, 9, loan.get('risk_level', ''), formats['data_center']) row += 1 # Add totals row if loans: worksheet.write(row, 0, 'TOTALS', formats['summary']) worksheet.write(row, 1, '', formats['summary']) worksheet.write(row, 2, f'=SUM(C5:C{row})', formats['summary']) worksheet.write(row, 3, '', formats['summary']) worksheet.write(row, 4, '', formats['summary']) worksheet.write(row, 5, '', formats['summary']) worksheet.write(row, 6, f'=SUM(G5:G{row})', formats['summary']) worksheet.write(row, 7, f'=SUM(H5:H{row})', formats['summary']) worksheet.write(row, 8, '', formats['summary']) worksheet.write(row, 9, '', formats['summary']) # Set column widths worksheet.set_column('A:A', 12) worksheet.set_column('B:B', 20) worksheet.set_column('C:C', 15) worksheet.set_column('D:E', 12) worksheet.set_column('F:F', 10) worksheet.set_column('G:H', 15) worksheet.set_column('I:J', 12) def _create_risk_sheet(self, worksheet, data, formats, workbook): """Create risk analysis sheet with charts""" # Title worksheet.merge_range('A1:G1', 'RISK ANALYSIS', formats['title']) worksheet.merge_range('A2:G2', f'Generated: {datetime.now().strftime("%B %d, %Y")}', formats['data_center']) # Risk Summary row = 4 worksheet.write(row, 0, 'RISK SUMMARY', formats['subheader']) row += 2 risk_summary = data.get('summary', {}) risk_items = [ ['Total at Risk', risk_summary.get('total_at_risk', 0)], ['High Risk Loans', risk_summary.get('high_risk_count', 0)], ['Portfolio at Risk %', risk_summary.get('par_percentage', 0)], ['Average Days Overdue', risk_summary.get('avg_days_overdue', 0)] ] for item in risk_items: worksheet.write(row, 0, item[0], formats['data']) if 'percentage' in item[0].lower() or '%' in item[0]: worksheet.write(row, 1, item[1]/100, formats['percentage']) elif 'amount' in item[0].lower() or 'risk' in item[0].lower(): worksheet.write(row, 1, item[1], formats['currency']) else: worksheet.write(row, 1, item[1], formats['data_right']) row += 1 # High Risk Loans Table row += 2 worksheet.write(row, 0, 'HIGH RISK LOANS', formats['subheader']) row += 2 risk_headers = ['Loan #', 'Borrower', 'Amount', 'Days Overdue', 'Risk Score'] for col, header in enumerate(risk_headers): worksheet.write(row, col, header, formats['header']) row += 1 high_risk_loans = data.get('high_risk_loans', []) for loan in high_risk_loans[:20]: # Limit to top 20 worksheet.write(row, 0, loan.get('loan_number', ''), formats['data']) worksheet.write(row, 1, loan.get('borrower', ''), formats['data']) worksheet.write(row, 2, loan.get('amount', 0), formats['currency']) worksheet.write(row, 3, loan.get('days_overdue', 0), formats['data_center']) worksheet.write(row, 4, loan.get('risk_score', 0), formats['data_center']) row += 1 # Add risk distribution chart if 'risk_distribution' in data: chart_data = data['risk_distribution'] categories_range = f"'Risk Analysis'!A{row+3}:A{row+3+len(chart_data)-1}" values_range = f"'Risk Analysis'!B{row+3}:B{row+3+len(chart_data)-1}" # Write chart data row += 3 worksheet.write(row-1, 0, 'Risk Distribution', formats['subheader']) for i, (category, value) in enumerate(chart_data.items()): worksheet.write(row+i, 0, category, formats['data']) worksheet.write(row+i, 1, value, formats['data_right']) # Add column chart self._add_chart(workbook, worksheet, 'column', { 'categories': categories_range, 'values': values_range }, 'Risk Distribution', 'D4') # Set column widths worksheet.set_column('A:A', 20) worksheet.set_column('B:G', 15) def _create_financial_sheet(self, worksheet, data, formats, workbook): """Create financial summary sheet""" # Title worksheet.merge_range('A1:F1', 'FINANCIAL SUMMARY', formats['title']) worksheet.merge_range('A2:F2', f'Generated: {datetime.now().strftime("%B %d, %Y")}', formats['data_center']) # Revenue Summary row = 4 worksheet.write(row, 0, 'REVENUE SUMMARY', formats['subheader']) row += 2 revenue_data = data.get('revenue', {}) revenue_items = [ ['Interest Income', revenue_data.get('interest', 0)], ['Processing Fees', revenue_data.get('processing_fees', 0)], ['Late Fees', revenue_data.get('late_fees', 0)], ['Other Income', revenue_data.get('other', 0)], ['Total Revenue', revenue_data.get('total', 0)] ] for item in revenue_items: worksheet.write(row, 0, item[0], formats['data']) worksheet.write(row, 1, item[1], formats['currency']) row += 1 # Expense Summary row += 2 worksheet.write(row, 0, 'EXPENSE SUMMARY', formats['subheader']) row += 2 expense_data = data.get('expenses', {}) expense_items = [ ['Operating Expenses', expense_data.get('operating', 0)], ['Staff Costs', expense_data.get('staff', 0)], ['Loan Loss Provision', expense_data.get('provisions', 0)], ['Other Expenses', expense_data.get('other', 0)], ['Total Expenses', expense_data.get('total', 0)] ] for item in expense_items: worksheet.write(row, 0, item[0], formats['data']) worksheet.write(row, 1, item[1], formats['currency']) row += 1 # Profitability row += 2 worksheet.write(row, 0, 'PROFITABILITY', formats['subheader']) row += 2 profit_data = data.get('profitability', {}) profit_items = [ ['Gross Profit', profit_data.get('gross', 0)], ['Net Profit', profit_data.get('net', 0)], ['Profit Margin', profit_data.get('margin', 0)] ] for item in profit_items: worksheet.write(row, 0, item[0], formats['data']) if 'margin' in item[0].lower(): worksheet.write(row, 1, item[1]/100, formats['percentage']) else: worksheet.write(row, 1, item[1], formats['currency']) row += 1 # Set column widths worksheet.set_column('A:A', 25) worksheet.set_column('B:F', 15) def generate_loan_statement_excel(self, loan_data): """Generate loan statement Excel file""" try: buffer = io.BytesIO() workbook = xlsxwriter.Workbook(buffer, {'in_memory': True}) formats = self._create_formats(workbook) worksheet = workbook.add_worksheet('Loan Statement') # Title worksheet.merge_range('A1:F1', 'LOAN STATEMENT', formats['title']) worksheet.merge_range('A2:F2', f'Generated: {datetime.now().strftime("%B %d, %Y")}', formats['data_center']) # Loan Information row = 4 worksheet.write(row, 0, 'LOAN INFORMATION', formats['subheader']) row += 2 loan_info = [ ['Loan Number', loan_data.get('loan_number', 'N/A')], ['Borrower Name', loan_data.get('borrower_name', 'N/A')], ['Principal Amount', loan_data.get('principal_amount', 0)], ['Disbursement Date', loan_data.get('disbursement_date', 'N/A')], ['Due Date', loan_data.get('due_date', 'N/A')], ['Status', loan_data.get('status', 'N/A')], ['Outstanding Balance', loan_data.get('outstanding_balance', 0)] ] for info in loan_info: worksheet.write(row, 0, info[0], formats['data']) if 'amount' in info[0].lower() or 'balance' in info[0].lower(): worksheet.write(row, 1, info[1], formats['currency']) else: worksheet.write(row, 1, info[1], formats['data']) row += 1 # Repayment History if 'repayments' in loan_data and loan_data['repayments']: row += 2 worksheet.write(row, 0, 'REPAYMENT HISTORY', formats['subheader']) row += 2 headers = ['Date', 'Amount', 'Method', 'Reference', 'Balance After'] for col, header in enumerate(headers): worksheet.write(row, col, header, formats['header']) row += 1 for repayment in loan_data['repayments']: worksheet.write(row, 0, repayment.get('date', ''), formats['date']) worksheet.write(row, 1, repayment.get('amount', 0), formats['currency']) worksheet.write(row, 2, repayment.get('method', ''), formats['data']) worksheet.write(row, 3, repayment.get('reference', ''), formats['data']) worksheet.write(row, 4, repayment.get('balance_after', 0), formats['currency']) row += 1 # Set column widths worksheet.set_column('A:A', 20) worksheet.set_column('B:F', 15) workbook.close() buffer.seek(0) return buffer except Exception as e: logger.error(f"Loan statement Excel generation failed: {e}") return None