🔍 Code Extractor

Creates a multi-sheet Excel report from warranty data, including main report, summary view, complete data, references, and statistics sheets with auto-formatted columns.

Purpose

This function generates a comprehensive Excel workbook for warranty analysis and reporting. It organizes warranty information across multiple sheets for different viewing purposes: a main report with full disclosures, a summary view with truncated information for quick overview, a complete data sheet with all fields, a parsed references sheet, and a statistics sheet with key metrics. The function also applies automatic column width formatting for readability.

Source Code

def create_excel_report_improved(warranties, references_section, output_file):
    """Create Excel report with multiple sheets including references."""
    logger.info(f"Creating Excel report: {output_file}")
    
    try:
        with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
            # Main sheet with FULL disclosures (this will be the first/default sheet)
            main_df = pd.DataFrame(warranties)[['Warranty_ID', 'Warranty_Title', 'Section_Name', 'Source_Documents_Count', 'Warranty_Text', 'Full_Disclosure']]
            # Rename the disclosure column for clarity
            main_df = main_df.rename(columns={'Full_Disclosure': 'Complete_Disclosure'})
            main_df.to_excel(writer, sheet_name='Main_Report', index=False)
            
            # Summary sheet (with truncated disclosures for quick overview)
            summary_df = pd.DataFrame(warranties)[['Warranty_ID', 'Warranty_Title', 'Section_Name', 'Source_Documents_Count', 'Warranty_Text', 'Disclosure_Summary']]
            summary_df.to_excel(writer, sheet_name='Summary_View', index=False)
            
            # Complete sheet (all data including both summary and full disclosures)
            complete_df = pd.DataFrame(warranties)
            complete_df.to_excel(writer, sheet_name='All_Data', index=False)
            
            # References sheet
            if references_section:
                # Parse references into structured data
                ref_data = parse_references_section(references_section)
                if ref_data:
                    ref_df = pd.DataFrame(ref_data)
                    ref_df.to_excel(writer, sheet_name='References', index=False)
            
            # Statistics sheet
            stats_data = {
                'Metric': [
                    'Total Warranties',
                    'Unique Sections',
                    'Average Source Documents per Warranty',
                    'Total References Used',
                    'Report Generated'
                ],
                'Value': [
                    len(warranties),
                    len(set(w['Section_Name'] for w in warranties)),
                    round(sum(int(w['Source_Documents_Count']) for w in warranties) / len(warranties), 1),
                    len(set().union(*[w.get('Referenced_Numbers', []) for w in warranties])),
                    datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                ]
            }
            stats_df = pd.DataFrame(stats_data)
            stats_df.to_excel(writer, sheet_name='Statistics', index=False)
            
            # Format the Excel sheets
            for sheet_name in writer.sheets:
                worksheet = writer.sheets[sheet_name]
                
                # Auto-adjust column widths
                for column in worksheet.columns:
                    max_length = 0
                    column_letter = column[0].column_letter
                    
                    for cell in column:
                        try:
                            if len(str(cell.value)) > max_length:
                                max_length = len(str(cell.value))
                        except:
                            pass
                    
                    # Set reasonable limits
                    adjusted_width = min(max_length + 2, 50)
                    worksheet.column_dimensions[column_letter].width = adjusted_width
        
        logger.info(f"Created Excel report: {output_file}")
        
    except ImportError:
        logger.warning("openpyxl not available, skipping Excel export")
        return False
    
    return True

Parameters

Parameter Details

warranties: A list of dictionaries containing warranty information. Each dictionary must include keys: 'Warranty_ID', 'Warranty_Title', 'Section_Name', 'Source_Documents_Count', 'Warranty_Text', 'Full_Disclosure', and 'Disclosure_Summary'. May optionally include 'Referenced_Numbers' (list) and other fields for the complete data sheet.

references_section: A string or structured data containing reference information to be parsed and added to the References sheet. Can be None or empty if no references are available. Expected to be parseable by the parse_references_section() function.

output_file: String or Path object specifying the output file path for the Excel report. Should have .xlsx extension. The file will be created or overwritten if it exists.

Return Value

Returns a boolean value: True if the Excel report was successfully created, False if openpyxl library is not available (ImportError caught). Does not return False for other errors, which would raise exceptions.

Dependencies

• pandas
• openpyxl
• logging
• datetime

Required Imports

import pandas as pd
from datetime import datetime
import logging

Conditional/Optional Imports

These imports are only needed under specific conditions:

import openpyxl

Usage Example

import pandas as pd
import logging
from datetime import datetime

# Setup logger
logger = logging.getLogger(__name__)
logging.basicConfig(level=logging.INFO)

# Define parse_references_section helper (stub)
def parse_references_section(refs):
    if not refs:
        return None
    return [{'Reference_ID': '1', 'Document': 'Doc1', 'Page': '5'}]

# Sample warranty data
warranties = [
    {
        'Warranty_ID': 'W001',
        'Warranty_Title': 'Product Quality',
        'Section_Name': 'General Warranties',
        'Source_Documents_Count': '3',
        'Warranty_Text': 'Products meet specifications',
        'Full_Disclosure': 'Complete disclosure text here...',
        'Disclosure_Summary': 'Summary of disclosure',
        'Referenced_Numbers': ['REF-001', 'REF-002']
    }
]

references = 'REF-001: Document A, Page 10\nREF-002: Document B, Page 15'

# Create report
success = create_excel_report_improved(
    warranties=warranties,
    references_section=references,
    output_file='warranty_report.xlsx'
)

if success:
    print('Report created successfully')
else:
    print('Failed to create report - openpyxl not available')

Best Practices

• Ensure all warranty dictionaries contain the required keys before calling this function to avoid KeyError exceptions
• Install openpyxl library (pip install openpyxl) for Excel export functionality
• Configure a logger before calling this function to capture informational messages and warnings
• Implement the parse_references_section() function to handle your specific reference format
• Use reasonable file paths for output_file to avoid permission errors
• The function will overwrite existing files without warning, so ensure unique filenames or handle backups externally
• For large datasets, be aware that Excel has row limits (1,048,576 rows per sheet)
• Column widths are capped at 50 characters for readability; very long text will be truncated in display but preserved in cell value
• The 'Referenced_Numbers' field in warranties should be a list or set to avoid errors in statistics calculation

Tags

Similar Components

AI-powered semantic similarity - components with related functionality:

• function create_excel_report 95.9% similar

  Creates a multi-sheet Excel report from warranty data, including main report, summary view, complete data, and statistics sheets with auto-formatted columns.

  From: /tf/active/vicechatdev/convert_disclosures_to_table.py

• function create_csv_report_improved 76.7% similar

  Creates two CSV reports from warranty data: a summary report with key fields and a detailed report with all fields including full disclosures.

  From: /tf/active/vicechatdev/improved_convert_disclosures_to_table.py

• function create_csv_report 75.4% similar

  Creates two CSV reports (summary and detailed) from warranty data, writing warranty information to files with different levels of detail.

  From: /tf/active/vicechatdev/convert_disclosures_to_table.py

• function create_word_report_improved 70.3% similar

  Generates a formatted Microsoft Word document report containing warranty disclosures with table of contents, structured sections, and references.

  From: /tf/active/vicechatdev/improved_convert_disclosures_to_table.py

• function create_word_report 67.9% similar

  Generates a formatted Microsoft Word document report containing warranty disclosures with a table of contents, metadata, and structured sections for each warranty.

  From: /tf/active/vicechatdev/convert_disclosures_to_table.py