🔍 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 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
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)

# Define parse_references_section helper
def parse_references_section(refs):
    if not refs:
        return []
    return [{'Reference': 'Doc1', 'Description': 'Sample'}]

# Sample warranty data
warranties = [
    {
        'Warranty_ID': 'W001',
        'Warranty_Title': 'Product Quality',
        'Section_Name': 'Quality Assurance',
        'Source_Documents_Count': '3',
        'Warranty_Text': 'Product meets specifications',
        'Full_Disclosure': 'Complete disclosure text here...',
        'Disclosure_Summary': 'Summary of disclosure',
        'Referenced_Numbers': ['REF001', 'REF002']
    }
]

references = 'REF001: Document A\nREF002: Document B'
output_path = 'warranty_report.xlsx'

# Create report
success = create_excel_report_improved(warranties, references, output_path)
if success:
    print(f'Report created: {output_path}')
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 functionality, otherwise the function will return False
• Configure a logger before calling this function as it uses module-level 'logger' variable
• Implement the parse_references_section() function to handle reference parsing appropriately for your data format
• The function overwrites existing files without warning, so ensure output_file path is correct
• For large datasets, be aware that auto-adjusting column widths iterates through all cells which may impact performance
• The 'Source_Documents_Count' field is expected to be a string representation of an integer for statistics calculation
• Column widths are capped at 50 characters to prevent extremely wide columns from making the spreadsheet unwieldy

Tags

Similar Components

AI-powered semantic similarity - components with related functionality:

• function create_excel_report 96.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 74.4% 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 72.9% 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 67.7% 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 65.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