🔍 Code Extractor

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

Purpose

This function generates a comprehensive Excel workbook from warranty data with four distinct sheets: a main report with full disclosures, a summary view with truncated disclosures for quick overview, a complete data sheet with all fields, and a statistics sheet with aggregated metrics. It automatically formats column widths for readability and handles missing dependencies gracefully.

Source Code

def create_excel_report(warranties, output_file):
    """Create Excel report with multiple sheets."""
    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)
            
            # Statistics sheet
            stats_data = {
                'Metric': [
                    'Total Warranties',
                    'Unique Sections',
                    'Average Source Documents per Warranty',
                    '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),
                    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 have keys: 'Warranty_ID', 'Warranty_Title', 'Section_Name', 'Source_Documents_Count', 'Warranty_Text', 'Full_Disclosure', and 'Disclosure_Summary'. The Source_Documents_Count should be convertible to an integer.

output_file: String or Path object specifying the output Excel file path. 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). No return on other exceptions (they will propagate).

Dependencies

• pandas
• openpyxl
• logging
• datetime

Required Imports

import pandas as pd
import logging
from datetime import datetime

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)

# Sample warranty data
warranties = [
    {
        'Warranty_ID': 'W001',
        'Warranty_Title': 'Product Quality Warranty',
        'Section_Name': 'Quality Assurance',
        'Source_Documents_Count': '3',
        'Warranty_Text': 'All products meet quality standards',
        'Full_Disclosure': 'Complete disclosure text here with all details...',
        'Disclosure_Summary': 'Summary of disclosure (truncated)',
        'Additional_Field': 'Extra data'
    },
    {
        'Warranty_ID': 'W002',
        'Warranty_Title': 'Delivery Warranty',
        'Section_Name': 'Logistics',
        'Source_Documents_Count': '2',
        'Warranty_Text': 'Timely delivery guaranteed',
        'Full_Disclosure': 'Full delivery terms and conditions...',
        'Disclosure_Summary': 'Delivery terms summary',
        'Additional_Field': 'More data'
    }
]

# Create Excel report
output_file = 'warranty_report.xlsx'
success = create_excel_report(warranties, output_file)

if success:
    print(f'Report created successfully: {output_file}')
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 package (pip install openpyxl) for Excel functionality, otherwise the function will return False
• Configure a logger instance in the module scope before calling this function
• The Source_Documents_Count field must be convertible to an integer or the function will raise a ValueError
• Column widths are automatically adjusted but capped at 50 characters for readability
• The function will overwrite existing files without warning, so ensure output_file path is correct
• The Main_Report sheet is created first and will be the default sheet when opening the Excel file
• For large datasets, consider memory usage as pandas loads all data into memory

Tags

Similar Components

AI-powered semantic similarity - components with related functionality:

• function create_excel_report_improved 95.9% similar

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

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

• function create_csv_report 76.6% 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_csv_report_improved 75.6% 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_word_report 66.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

• function create_word_report_improved 64.8% 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