Vengala Vinay

Having 12+ Years of Experience in Software Development

Home » Implementing automated compliance reporting for custom portfolio project grids ledgers using custom PHP-Spreadsheet exports

Implementing automated compliance reporting for custom portfolio project grids ledgers using custom PHP-Spreadsheet exports

Leveraging PHP-Spreadsheet for Automated Compliance Reporting on Custom Project Grids

Many custom WordPress projects involve intricate data management, often visualized in custom-built grid interfaces or tables. When these grids represent financial transactions, asset portfolios, or compliance-sensitive data, the need for automated, auditable reporting becomes paramount. This post details a robust solution for generating automated compliance reports directly from such custom data structures, utilizing the powerful PHP-Spreadsheet library for export to common spreadsheet formats like XLSX and CSV.

Data Model and Retrieval Strategy

Assume a custom post type (CPT) named ‘portfolio_item’ with custom meta fields representing key data points for a financial portfolio. These might include:

  • portfolio_value (numeric)
  • acquisition_date (date)
  • asset_class (string, e.g., ‘Equity’, ‘Bond’, ‘Real Estate’)
  • risk_level (string, e.g., ‘Low’, ‘Medium’, ‘High’)
  • compliance_status (string, e.g., ‘Compliant’, ‘Pending Review’, ‘Non-Compliant’)

The retrieval strategy will involve querying these posts and their meta data. For a compliance report, we’ll focus on filtering by compliance_status and potentially date ranges. A typical WordPress query might look like this:

WordPress Query for Portfolio Items

<?php
$args = array(
    'post_type'      => 'portfolio_item',
    'posts_per_page' => -1, // Retrieve all items
    'meta_query'     => array(
        'relation' => 'AND',
        array(
            'key'     => 'compliance_status',
            'value'   => 'Non-Compliant', // Example filter for a specific report
            'compare' => '=',
        ),
        // Optional: Add date range filtering if needed
        // array(
        //     'key'     => 'acquisition_date',
        //     'value'   => array( '2023-01-01', '2023-12-31' ),
        //     'compare' => 'BETWEEN',
        //     'type'    => 'DATE',
        // ),
    ),
);
$portfolio_query = new WP_Query( $args );

$report_data = array();
if ( $portfolio_query->have_posts() ) {
    while ( $portfolio_query->have_posts() ) {
        $portfolio_query->the_post();
        $post_id = get_the_ID();
        $report_data[] = array(
            'ID'               => $post_id,
            'Title'            => get_the_title(),
            'Asset Class'      => get_post_meta( $post_id, 'asset_class', true ),
            'Acquisition Date' => get_post_meta( $post_id, 'acquisition_date', true ),
            'Portfolio Value'  => get_post_meta( $post_id, 'portfolio_value', true ),
            'Risk Level'       => get_post_meta( $post_id, 'risk_level', true ),
            'Compliance Status'=> get_post_meta( $post_id, 'compliance_status', true ),
        );
    }
    wp_reset_postdata();
}
?>

Integrating PHP-Spreadsheet

The PHP-Spreadsheet library (a fork of PhpOffice/PHPExcel) is a robust choice for generating Excel and other spreadsheet files. It needs to be installed within your WordPress environment. The recommended method is via Composer.

Composer Installation

Navigate to your WordPress root directory (or a dedicated plugin/theme directory) in your terminal and run:

composer require phpoffice/phpspreadsheet

This will create a vendor directory and an autoload.php file. You’ll need to include this autoloader in your script.

Generating the XLSX Report

The core logic involves instantiating the spreadsheet, adding a worksheet, populating it with headers and data, and then saving it to a file or outputting it directly. For a WordPress context, this would typically be triggered by an admin action (e.g., a button click in the admin dashboard).

Core Export Logic

<?php
// Ensure Composer autoloader is included
require 'path/to/your/vendor/autoload.php'; // Adjust path as necessary

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Assume $report_data is populated from the WP_Query above

if ( ! empty( $report_data ) ) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    // Set worksheet title
    $sheet->setTitle('Compliance Report');

    // Add headers
    $headers = array_keys( $report_data[0] ); // Get headers from the first data row
    $columnIndex = 1;
    foreach ( $headers as $header ) {
        $sheet->setCellValueByColumnAndRow( $columnIndex++, 1, $header );
    }

    // Add data rows
    $rowIndex = 2;
    foreach ( $report_data as $row_data ) {
        $columnIndex = 1;
        foreach ( $row_data as $cell_data ) {
            // Basic data type handling: attempt to format numbers and dates
            if ( is_numeric( $cell_data ) ) {
                // Check if it's a date-like string that should be treated as a number
                if ( preg_match('/^\d{4}-\d{2}-\d{2}$/', $cell_data) ) {
                    // Attempt to convert to Excel date format if it's a valid date string
                    try {
                        $dateObject = DateTime::createFromFormat('Y-m-d', $cell_data);
                        if ($dateObject) {
                            $sheet->setCellValueByColumnAndRow( $columnIndex, $rowIndex, $dateObject );
                            $sheet->getStyleByColumnAndRow( $columnIndex, $rowIndex )->setFormatCode('yyyy-mm-dd');
                        } else {
                            $sheet->setCellValueByColumnAndRow( $columnIndex, $rowIndex, $cell_data );
                        }
                    } catch ( Exception $e ) {
                        $sheet->setCellValueByColumnAndRow( $columnIndex, $rowIndex, $cell_data );
                    }
                } else {
                    $sheet->setCellValueByColumnAndRow( $columnIndex, $rowIndex, (float)$cell_data );
                }
            } elseif ( $cell_data instanceof DateTime ) {
                $sheet->setCellValueByColumnAndRow( $columnIndex, $rowIndex, $cell_data );
                $sheet->getStyleByColumnAndRow( $columnIndex, $rowIndex )->setFormatCode('yyyy-mm-dd');
            } else {
                $sheet->setCellValueByColumnAndRow( $columnIndex, $rowIndex, $cell_data );
            }
            $columnIndex++;
        }
        $rowIndex++;
    }

    // Auto-size columns for better readability
    foreach(range('A', $sheet->getHighestDataColumn()) as $columnID) {
        $sheet->getColumnDimension($columnID)->setAutoSize(true);
    }

    // Create a writer
    $writer = new Xlsx( $spreadsheet );

    // Set HTTP headers for download
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="compliance_report_' . date('Ymd') . '.xlsx"');
    header('Cache-Control: max-age=0');

    // Output the file
    $writer->save('php://output');
    exit; // Important to exit after sending the file
} else {
    // Handle case where no data is found
    wp_die( 'No compliance data found for the specified criteria.' );
}
?>

Generating the CSV Report

For simpler reporting needs or compatibility with older systems, CSV export is a viable alternative. PHP-Spreadsheet also supports this format.

CSV Export Logic

<?php
// Ensure Composer autoloader is included
require 'path/to/your/vendor/autoload.php'; // Adjust path as necessary

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

// Assume $report_data is populated from the WP_Query above

if ( ! empty( $report_data ) ) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    // Add headers
    $headers = array_keys( $report_data[0] );
    $sheet->fromArray( $headers, NULL, 'A1' );

    // Add data rows
    $rowIndex = 2;
    foreach ( $report_data as $row_data ) {
        $columnIndex = 1;
        foreach ( $row_data as $cell_data ) {
            // CSV doesn't handle complex formatting like XLSX, so just output raw data
            $sheet->setCellValueByColumnAndRow( $columnIndex++, $rowIndex, $cell_data );
        }
        $rowIndex++;
    }

    // Create a writer
    $writer = new Csv( $spreadsheet );
    $writer->setDelimiter(',');
    $writer->setEnclosure('"');
    $writer->setLineEnding("\r\n");
    $writer->setSheetIndex(0); // Write the first sheet

    // Set HTTP headers for download
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment;filename="compliance_report_' . date('Ymd') . '.csv"');
    header('Cache-Control: max-age=0');

    // Output the file
    $writer->save('php://output');
    exit; // Important to exit after sending the file
} else {
    // Handle case where no data is found
    wp_die( 'No compliance data found for the specified criteria.' );
}
?>

Implementation within WordPress

To make this actionable within WordPress, you’ll typically create a custom plugin or add this functionality to your theme’s functions.php (though a plugin is more maintainable). The export process should be initiated via an AJAX request or a direct URL that triggers the script, ideally protected by WordPress’s nonce system for security.

Example: Admin Menu Action Hook

You can add a menu item in the WordPress admin area that, when clicked, triggers the report generation. This involves hooking into admin_menu and creating a page that contains a link or button to initiate the export.

<?php
/**
 * Plugin Name: Custom Compliance Reporter
 * Description: Generates compliance reports for portfolio items.
 * Version: 1.0
 * Author: Your Name
 */

// Include Composer autoloader
require_once plugin_dir_path( __FILE__ ) . 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

// Add admin menu page
function ccr_add_admin_menu() {
    add_menu_page(
        'Compliance Reports',
        'Compliance Reports',
        'manage_options',
        'compliance-reports',
        'ccr_render_report_page',
        'dashicons-chart-bar',
        80
    );
}
add_action( 'admin_menu', 'ccr_add_admin_menu' );

// Render the report page content
function ccr_render_report_page() {
    ?>
    <div class="wrap">
        <h1>Compliance Reports</h1>
        <p>Select report type and parameters to generate a compliance report.</p>

        <h2>Generate XLSX Report</h2>
        <p>
            <a href="?page=compliance-reports&action=export_xlsx&nonce=" class="button button-primary">Download XLSX Report (All Non-Compliant)</a>
        </p>

        <h2>Generate CSV Report</h2>
        <p>
            <a href="?page=compliance-reports&action=export_csv&nonce=" class="button button-secondary">Download CSV Report (All Non-Compliant)</a>
        </p>
    </div>
    <?php
}

// Handle report export actions
function ccr_handle_export() {
    if ( ! isset( $_GET['page'] ) || $_GET['page'] !== 'compliance-reports' || ! isset( $_GET['action'] ) || ! isset( $_GET['nonce'] ) ) {
        return;
    }

    if ( ! wp_verify_nonce( $_GET['nonce'], 'ccr_export_nonce' ) ) {
        wp_die( 'Security check failed.' );
    }

    if ( ! current_user_can( 'manage_options' ) ) {
        wp_die( 'You do not have sufficient permissions to access this page.' );
    }

    // --- Data Retrieval ---
    $args = array(
        'post_type'      => 'portfolio_item',
        'posts_per_page' => -1,
        'meta_query'     => array(
            array(
                'key'     => 'compliance_status',
                'value'   => 'Non-Compliant',
                'compare' => '=',
            ),
        ),
    );
    $portfolio_query = new WP_Query( $args );

    $report_data = array();
    if ( $portfolio_query->have_posts() ) {
        while ( $portfolio_query->have_posts() ) {
            $portfolio_query->the_post();
            $post_id = get_the_ID();
            $report_data[] = array(
                'ID'               => $post_id,
                'Title'            => get_the_title(),
                'Asset Class'      => get_post_meta( $post_id, 'asset_class', true ),
                'Acquisition Date' => get_post_meta( $post_id, 'acquisition_date', true ),
                'Portfolio Value'  => get_post_meta( $post_id, 'portfolio_value', true ),
                'Risk Level'       => get_post_meta( $post_id, 'risk_level', true ),
                'Compliance Status'=> get_post_meta( $post_id, 'compliance_status', true ),
            );
        }
        wp_reset_postdata();
    } else {
        wp_die( 'No compliance data found for the specified criteria.' );
    }

    // --- Report Generation ---
    if ( ! empty( $report_data ) ) {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Add headers
        $headers = array_keys( $report_data[0] );
        $sheet->fromArray( $headers, NULL, 'A1' );

        // Add data rows
        $rowIndex = 2;
        foreach ( $report_data as $row_data ) {
            $columnIndex = 1;
            foreach ( $row_data as $cell_data ) {
                // Basic data type handling for XLSX
                if ( $_GET['action'] === 'export_xlsx' ) {
                    if ( is_numeric( $cell_data ) ) {
                        if ( preg_match('/^\d{4}-\d{2}-\d{2}$/', $cell_data) ) {
                            try {
                                $dateObject = DateTime::createFromFormat('Y-m-d', $cell_data);
                                if ($dateObject) {
                                    $sheet->setCellValueByColumnAndRow( $columnIndex, $rowIndex, $dateObject );
                                    $sheet->getStyleByColumnAndRow( $columnIndex, $rowIndex )->setFormatCode('yyyy-mm-dd');
                                } else {
                                    $sheet->setCellValueByColumnAndRow( $columnIndex, $rowIndex, $cell_data );
                                }
                            } catch ( Exception $e ) {
                                $sheet->setCellValueByColumnAndRow( $columnIndex, $rowIndex, $cell_data );
                            }
                        } else {
                            $sheet->setCellValueByColumnAndRow( $columnIndex, $rowIndex, (float)$cell_data );
                        }
                    } elseif ( $cell_data instanceof DateTime ) {
                        $sheet->setCellValueByColumnAndRow( $columnIndex, $rowIndex, $cell_data );
                        $sheet->getStyleByColumnAndRow( $columnIndex, $rowIndex )->setFormatCode('yyyy-mm-dd');
                    } else {
                        $sheet->setCellValueByColumnAndRow( $columnIndex, $rowIndex, $cell_data );
                    }
                } else { // CSV export
                    $sheet->setCellValueByColumnAndRow( $columnIndex, $rowIndex, $cell_data );
                }
                $columnIndex++;
            }
            $rowIndex++;
        }

        // Auto-size columns for XLSX
        if ( $_GET['action'] === 'export_xlsx' ) {
            foreach(range('A', $sheet->getHighestDataColumn()) as $columnID) {
                $sheet->getColumnDimension($columnID)->setAutoSize(true);
            }
        }

        // --- Output ---
        if ( $_GET['action'] === 'export_xlsx' ) {
            $writer = new Xlsx( $spreadsheet );
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="compliance_report_' . date('Ymd') . '.xlsx"');
            header('Cache-Control: max-age=0');
            $writer->save('php://output');
        } elseif ( $_GET['action'] === 'export_csv' ) {
            $writer = new Csv( $spreadsheet );
            $writer->setDelimiter(',');
            $writer->setEnclosure('"');
            $writer->setLineEnding("\r\n");
            $writer->setSheetIndex(0);
            header('Content-Type: text/csv');
            header('Content-Disposition: attachment;filename="compliance_report_' . date('Ymd') . '.csv"');
            header('Cache-Control: max-age=0');
            $writer->save('php://output');
        }
        exit;
    }
}
add_action( 'admin_init', 'ccr_handle_export' );
?>

Advanced Considerations and Best Practices

  • Error Handling: Implement comprehensive error handling for database queries, file operations, and PHP-Spreadsheet operations. Log errors to a file or use WordPress’s error logging mechanisms.
  • Security: Always use nonces for any action that modifies data or triggers sensitive operations. Ensure proper user capabilities are checked (e.g., manage_options for admin reports).
  • Performance: For very large datasets, consider implementing pagination for the WordPress query and generating the report in chunks, or using background processing (e.g., WP-Cron with AJAX handlers or a dedicated queue system) to avoid overwhelming the server and timing out the request.
  • Customization: PHP-Spreadsheet offers extensive formatting options (fonts, colors, cell styles, formulas). Leverage these to create more professional and informative reports. You can also add charts and images.
  • Configuration: Allow users to select date ranges, specific asset classes, or compliance statuses via form inputs on the admin page, making the reporting more dynamic.
  • Data Validation: Before exporting, validate the data types and formats to ensure consistency and prevent errors in the generated spreadsheet.

By integrating PHP-Spreadsheet into your custom WordPress development workflow, you can provide powerful, automated reporting capabilities that significantly enhance the compliance and auditing posture of your portfolio management solutions.

Recent Posts

Top Categories

Our Products

Our Services