Building custom automated PDF financial reports and invoices for WooCommerce using custom PhpSpreadsheet components

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

/**
 * Generates a PDF invoice for a given WooCommerce order using PhpSpreadsheet.
 *
 * @param int $order_id The ID of the WooCommerce order.
 * @return string|false Path to the generated PDF file or false on failure.
 */
function generate_woocommerce_pdf_invoice(int $order_id): string|false {
    $order_data = get_custom_woocommerce_order_data( $order_id );

    if ( ! $order_data ) {
        error_log("Failed to retrieve order data for order ID: " . $order_id);
        return false;
    }

    // Ensure Mpdf is installed: composer require mpdf/mpdf
    if ( ! class_exists( 'Mpdf' ) ) {
        error_log("Mpdf library not found. Please install it via Composer.");
        return false;
    }

    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Invoice #' . $order_id);

    // --- Styling and Layout ---
    $headerStyle = [
        'font' => ['bold' => true, 'size' => 14],
        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'D3D3D3']], // Light gray
        'borders' => ['bottom' => ['borderStyle' => Border::BORDER_THIN]],
    ];

    $subHeaderStyle = [
        'font' => ['bold' => true, 'size' => 12],
        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
        'borders' => ['bottom' => ['borderStyle' => Border::BORDER_THIN]],
    ];

    $cellStyle = [
        'font' => ['size' => 10],
        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
        'borders' => ['allborders' => ['borderStyle' => Border::BORDER_THIN]],
    ];

    $currencyFormat = NumberFormat::FORMAT_ACCOUNTING_USD; // Adjust currency format as needed

    // --- Populate Data ---

    // Company Info (Top Left)
    $sheet->setCellValue('A1', 'Your Company Name');
    $sheet->setCellValue('A2', '123 Business St');
    $sheet->setCellValue('A3', 'City, State, ZIP');
    $sheet->setCellValue('A4', '[email protected]');
    $sheet->getStyle('A1:A4')->applyFromArray(['font' => ['bold' => true, 'size' => 12]]);

    // Invoice Title (Top Right)
    $sheet->setCellValue('F1', 'INVOICE');
    $sheet->getStyle('F1')->applyFromArray($headerStyle);
    $sheet->mergeCells('F1:H1');
    $sheet->getStyle('F1:H1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

    // Invoice Details
    $sheet->setCellValue('F2', 'Invoice #:');
    $sheet->setCellValue('G2', $order_data['order_id']);
    $sheet->setCellValue('F3', 'Date:');
    $sheet->setCellValue('G3', $order_data['order_date']);
    $sheet->setCellValue('F4', 'Status:');
    $sheet->setCellValue('G4', $order_data['order_status']);
    $sheet->getStyle('F2:G4')->applyFromArray(['font' => ['bold' => true]]);

    // Customer Info
    $sheet->setCellValue('A6', 'Bill To:');
    $sheet->getStyle('A6')->applyFromArray($subHeaderStyle);
    $sheet->setCellValue('A7', $order_data['customer_name']);
    $sheet->setCellValue('A8', $order_data['customer_address']); // Assumes formatted address includes newlines
    $sheet->setCellValue('A9', $order_data['customer_email']);

    // Line Items Header
    $sheet->setCellValue('A11', 'Product Name');
    $sheet->setCellValue('C11', 'Quantity');
    $sheet->setCellValue('D11', 'Unit Price'); // Assuming we can derive this
    $sheet->setCellValue('E11', 'Subtotal');
    $sheet->setCellValue('F11', 'Total');
    $sheet->getStyle('A11:F11')->applyFromArray($subHeaderStyle);

    // Line Items Data
    $row_start = 12;
    $current_row = $row_start;
    foreach ( $order_data['line_items'] as $item ) {
        $sheet->setCellValue('A' . $current_row, $item['product_name']);
        $sheet->setCellValue('C' . $current_row, $item['quantity']);
        // Calculate unit price if possible, otherwise use subtotal/quantity
        $unit_price = $item['quantity'] > 0 ? $item['subtotal'] / $item['quantity'] : 0;
        $sheet->setCellValue('D' . $current_row, $unit_price);
        $sheet->setCellValue('E' . $current_row, $item['subtotal']);
        $sheet->setCellValue('F' . $current_row, $item['total']);

        $sheet->getStyle('A' . $current_row)->applyFromArray($cellStyle);
        $sheet->getStyle('C' . $current_row)->applyFromArray($cellStyle);
        $sheet->getStyle('D' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);
        $sheet->getStyle('E' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);
        $sheet->getStyle('F' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);

        $current_row++;
    }

    // Totals Section
    $sheet->setCellValue('E' . $current_row, 'Total:');
    $sheet->getStyle('E' . $current_row)->applyFromArray($subHeaderStyle);
    $sheet->setCellValue('F' . $current_row, $order_data['total_amount']);
    $sheet->getStyle('F' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);
    $sheet->getStyle('E' . $current_row . ':F' . $current_row)->getFont()->setBold(true);

    // Adjust column widths
    foreach (range('A', 'F') as $columnID) {
        $sheet->getColumnDimension($columnID)->setAutoSize(true);
    }
    $sheet->getColumnDimension('A')->setWidth(30); // Product Name might need more width

    // --- PDF Rendering ---
    $writer = new Mpdf($spreadsheet);
    $upload_dir = wp_upload_dir();
    $pdf_filename = "invoice_{$order_id}_" . date('YmdHis') . ".pdf";
    $pdf_path = trailingslashit($upload_dir['basedir']) . $pdf_filename;

    try {
        $writer->save($pdf_path);
        return $pdf_path;
    } catch (\Exception $e) {
        error_log("Error saving PDF for order ID {$order_id}: " . $e->getMessage());
        return false;
    }
}

Implementing the PDF Generation Trigger

You need a mechanism to trigger the PDF generation. Common approaches include:

• Admin Order List: Add a custom action link to each order in the WooCommerce Orders list.
• Order Status Change: Automatically generate a PDF when an order reaches a specific status (e.g., ‘Processing’ or ‘Completed’).
• Customer Account: Allow customers to download invoices from their “My Account” page.
• Cron Job: For batch generation of reports (e.g., daily sales summaries).

Example: Adding an Admin Action Link

This example demonstrates how to add a “Generate Invoice” link to the WooCommerce order list in the WordPress admin area. Clicking this link will trigger the generate_woocommerce_pdf_invoice function and prompt the user to download the PDF.

/**
 * Add custom action link to WooCommerce order list.
 */
add_filter( 'woocommerce_admin_order_actions', 'add_generate_invoice_order_action', 10, 2 );
function add_generate_invoice_order_action( $actions, $order ) {
    $actions['generate_invoice'] = [
        'url'    => wp_nonce_url( admin_url( 'admin-ajax.php?action=generate_wc_invoice&order_id=' . $order->get_id() ), 'generate_wc_invoice_nonce' ),
        'name'   => __( 'Generate Invoice', 'your-text-domain' ),
    ];
    return $actions;
}

/**
 * Handle AJAX request for generating invoice.
 */
add_action( 'wp_ajax_generate_wc_invoice', 'handle_generate_wc_invoice_ajax' );
function handle_generate_wc_invoice_ajax() {
    if ( ! isset( $_GET['order_id'] ) || ! isset( $_GET['_wpnonce'] ) ) {
        wp_die( __( 'Invalid request.', 'your-text-domain' ) );
    }

    $order_id = intval( $_GET['order_id'] );

    if ( ! wp_verify_nonce( $_GET['_wpnonce'], 'generate_wc_invoice_nonce' ) ) {
        wp_die( __( 'Security check failed.', 'your-text-domain' ) );
    }

    if ( ! current_user_can( 'edit_shop_orders' ) ) {
         wp_die( __( 'You do not have permission to perform this action.', 'your-text-domain' ) );
    }

    $pdf_path = generate_woocommerce_pdf_invoice( $order_id );

    if ( $pdf_path && file_exists( $pdf_path ) ) {
        $file_size = filesize( $pdf_path );
        $file_info = pathinfo( $pdf_path );
        $filename = $file_info['basename'];

        header( 'Content-Description: File Transfer' );
        header( 'Content-Type: application/pdf' );
        header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
        header( 'Content-Transfer-Encoding: binary' );
        header( 'Expires: 0' );
        header( 'Cache-Control: must-revalidate' );
        header( 'Pragma: public' );
        header( 'Content-Length: ' . $file_size );

        readfile( $pdf_path );
        exit;
    } else {
        wp_die( __( 'Failed to generate PDF invoice.', 'your-text-domain' ) );
    }
}

Advanced Customizations and Considerations

The provided code is a starting point. For production-ready solutions, consider the following:

• Error Handling and Logging: Implement robust error handling and logging for failed PDF generations. Use WordPress’s built-in logging functions or a dedicated logging library.
• Custom Fields and Meta Data: Extend get_custom_woocommerce_order_data to include custom product meta, order meta, or user meta relevant to your reports.
• Complex Calculations: For reports requiring intricate financial calculations (e.g., profit margins, tax breakdowns), perform these calculations within your PHP function before populating the spreadsheet.
• Templating Engine: For highly complex layouts, consider using PhpSpreadsheet’s ability to load from HTML or integrate with a templating engine like Twig, then render that HTML to PDF.
• Performance Optimization: For very large datasets or frequent generation, optimize database queries and consider caching strategies. PhpSpreadsheet itself can be memory-intensive.
• Security: Always sanitize inputs and use nonces for AJAX requests. Ensure file permissions are correctly set for generated PDFs.
• Internationalization: Use WordPress’s internationalization functions (__(), _e()) for all user-facing strings. Adjust currency formatting based on locale.
• Alternative PDF Libraries: While Mpdf is powerful, explore Dompdf or TCPDF if they better suit your specific needs or licensing requirements. Ensure you install the chosen library via Composer.
• Report Types: Adapt the data retrieval and PhpSpreadsheet population logic to create different report types (e.g., daily sales summaries, product sales reports, customer lifetime value reports).

Conclusion

By combining WooCommerce’s data access capabilities with the flexibility of PhpSpreadsheet, you can build sophisticated, automated PDF financial reports and invoices tailored to your specific business requirements. This approach offers a high degree of customization beyond what typical plugins provide, empowering developers to create truly bespoke reporting solutions.

// Assume this is within a custom plugin or theme's functions.php file
// Ensure Composer's autoloader is included: require_once __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf; // Or Dompdf, TCPDF

/**
 * Fetches detailed data for a specific WooCommerce order.
 *
 * @param int $order_id The ID of the WooCommerce order.
 * @return array|null Order data array or null if order not found.
 */
function get_custom_woocommerce_order_data(int $order_id): ?array {
    if ( ! class_exists( 'WC_Order' ) ) {
        return null; // WooCommerce not active
    }

    $order = wc_get_order( $order_id );

    if ( ! $order ) {
        return null; // Order not found
    }

    $order_data = [
        'order_id'          => $order->get_id(),
        'order_date'        => $order->get_date_created()->format( 'Y-m-d H:i:s' ),
        'order_status'      => wc_get_order_status_name( $order->get_status() ),
        'customer_name'     => $order->get_formatted_billing_full_name(),
        'customer_email'    => $order->get_billing_email(),
        'customer_address'  => $order->get_formatted_billing_address(),
        'total_amount'      => $order->get_total(),
        'currency'          => $order->get_currency(),
        'line_items'        => [],
    ];

    foreach ( $order->get_items() as $item_id => $item ) {
        $product = $item->get_product();
        $order_data['line_items'][] = [
            'product_name'  => $product ? $product->get_name() : $item->get_name(),
            'quantity'      => $item->get_quantity(),
            'subtotal'      => $item->get_subtotal(),
            'total'         => $item->get_total(),
            'sku'           => $product ? $product->get_sku() : '',
        ];
    }

    // Add more data points as needed: shipping, taxes, meta, etc.
    // $order_data['shipping_address'] = $order->get_formatted_shipping_address();
    // $order_data['tax_total'] = $order->get_total_tax();

    return $order_data;
}

Generating the Spreadsheet with PhpSpreadsheet

Now, let’s integrate this data retrieval with PhpSpreadsheet to create a report. We’ll instantiate a Spreadsheet object, add worksheets, and populate them with the fetched order data. For PDF generation, PhpSpreadsheet relies on external libraries like Mpdf, Dompdf, or TCPDF. Mpdf is often a good choice for its comprehensive features and performance.

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

/**
 * Generates a PDF invoice for a given WooCommerce order using PhpSpreadsheet.
 *
 * @param int $order_id The ID of the WooCommerce order.
 * @return string|false Path to the generated PDF file or false on failure.
 */
function generate_woocommerce_pdf_invoice(int $order_id): string|false {
    $order_data = get_custom_woocommerce_order_data( $order_id );

    if ( ! $order_data ) {
        error_log("Failed to retrieve order data for order ID: " . $order_id);
        return false;
    }

    // Ensure Mpdf is installed: composer require mpdf/mpdf
    if ( ! class_exists( 'Mpdf' ) ) {
        error_log("Mpdf library not found. Please install it via Composer.");
        return false;
    }

    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Invoice #' . $order_id);

    // --- Styling and Layout ---
    $headerStyle = [
        'font' => ['bold' => true, 'size' => 14],
        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'D3D3D3']], // Light gray
        'borders' => ['bottom' => ['borderStyle' => Border::BORDER_THIN]],
    ];

    $subHeaderStyle = [
        'font' => ['bold' => true, 'size' => 12],
        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
        'borders' => ['bottom' => ['borderStyle' => Border::BORDER_THIN]],
    ];

    $cellStyle = [
        'font' => ['size' => 10],
        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
        'borders' => ['allborders' => ['borderStyle' => Border::BORDER_THIN]],
    ];

    $currencyFormat = NumberFormat::FORMAT_ACCOUNTING_USD; // Adjust currency format as needed

    // --- Populate Data ---

    // Company Info (Top Left)
    $sheet->setCellValue('A1', 'Your Company Name');
    $sheet->setCellValue('A2', '123 Business St');
    $sheet->setCellValue('A3', 'City, State, ZIP');
    $sheet->setCellValue('A4', '[email protected]');
    $sheet->getStyle('A1:A4')->applyFromArray(['font' => ['bold' => true, 'size' => 12]]);

    // Invoice Title (Top Right)
    $sheet->setCellValue('F1', 'INVOICE');
    $sheet->getStyle('F1')->applyFromArray($headerStyle);
    $sheet->mergeCells('F1:H1');
    $sheet->getStyle('F1:H1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

    // Invoice Details
    $sheet->setCellValue('F2', 'Invoice #:');
    $sheet->setCellValue('G2', $order_data['order_id']);
    $sheet->setCellValue('F3', 'Date:');
    $sheet->setCellValue('G3', $order_data['order_date']);
    $sheet->setCellValue('F4', 'Status:');
    $sheet->setCellValue('G4', $order_data['order_status']);
    $sheet->getStyle('F2:G4')->applyFromArray(['font' => ['bold' => true]]);

    // Customer Info
    $sheet->setCellValue('A6', 'Bill To:');
    $sheet->getStyle('A6')->applyFromArray($subHeaderStyle);
    $sheet->setCellValue('A7', $order_data['customer_name']);
    $sheet->setCellValue('A8', $order_data['customer_address']); // Assumes formatted address includes newlines
    $sheet->setCellValue('A9', $order_data['customer_email']);

    // Line Items Header
    $sheet->setCellValue('A11', 'Product Name');
    $sheet->setCellValue('C11', 'Quantity');
    $sheet->setCellValue('D11', 'Unit Price'); // Assuming we can derive this
    $sheet->setCellValue('E11', 'Subtotal');
    $sheet->setCellValue('F11', 'Total');
    $sheet->getStyle('A11:F11')->applyFromArray($subHeaderStyle);

    // Line Items Data
    $row_start = 12;
    $current_row = $row_start;
    foreach ( $order_data['line_items'] as $item ) {
        $sheet->setCellValue('A' . $current_row, $item['product_name']);
        $sheet->setCellValue('C' . $current_row, $item['quantity']);
        // Calculate unit price if possible, otherwise use subtotal/quantity
        $unit_price = $item['quantity'] > 0 ? $item['subtotal'] / $item['quantity'] : 0;
        $sheet->setCellValue('D' . $current_row, $unit_price);
        $sheet->setCellValue('E' . $current_row, $item['subtotal']);
        $sheet->setCellValue('F' . $current_row, $item['total']);

        $sheet->getStyle('A' . $current_row)->applyFromArray($cellStyle);
        $sheet->getStyle('C' . $current_row)->applyFromArray($cellStyle);
        $sheet->getStyle('D' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);
        $sheet->getStyle('E' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);
        $sheet->getStyle('F' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);

        $current_row++;
    }

    // Totals Section
    $sheet->setCellValue('E' . $current_row, 'Total:');
    $sheet->getStyle('E' . $current_row)->applyFromArray($subHeaderStyle);
    $sheet->setCellValue('F' . $current_row, $order_data['total_amount']);
    $sheet->getStyle('F' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);
    $sheet->getStyle('E' . $current_row . ':F' . $current_row)->getFont()->setBold(true);

    // Adjust column widths
    foreach (range('A', 'F') as $columnID) {
        $sheet->getColumnDimension($columnID)->setAutoSize(true);
    }
    $sheet->getColumnDimension('A')->setWidth(30); // Product Name might need more width

    // --- PDF Rendering ---
    $writer = new Mpdf($spreadsheet);
    $upload_dir = wp_upload_dir();
    $pdf_filename = "invoice_{$order_id}_" . date('YmdHis') . ".pdf";
    $pdf_path = trailingslashit($upload_dir['basedir']) . $pdf_filename;

    try {
        $writer->save($pdf_path);
        return $pdf_path;
    } catch (\Exception $e) {
        error_log("Error saving PDF for order ID {$order_id}: " . $e->getMessage());
        return false;
    }
}

Implementing the PDF Generation Trigger

You need a mechanism to trigger the PDF generation. Common approaches include:

• Admin Order List: Add a custom action link to each order in the WooCommerce Orders list.
• Order Status Change: Automatically generate a PDF when an order reaches a specific status (e.g., ‘Processing’ or ‘Completed’).
• Customer Account: Allow customers to download invoices from their “My Account” page.
• Cron Job: For batch generation of reports (e.g., daily sales summaries).

Example: Adding an Admin Action Link

This example demonstrates how to add a “Generate Invoice” link to the WooCommerce order list in the WordPress admin area. Clicking this link will trigger the generate_woocommerce_pdf_invoice function and prompt the user to download the PDF.

/**
 * Add custom action link to WooCommerce order list.
 */
add_filter( 'woocommerce_admin_order_actions', 'add_generate_invoice_order_action', 10, 2 );
function add_generate_invoice_order_action( $actions, $order ) {
    $actions['generate_invoice'] = [
        'url'    => wp_nonce_url( admin_url( 'admin-ajax.php?action=generate_wc_invoice&order_id=' . $order->get_id() ), 'generate_wc_invoice_nonce' ),
        'name'   => __( 'Generate Invoice', 'your-text-domain' ),
    ];
    return $actions;
}

/**
 * Handle AJAX request for generating invoice.
 */
add_action( 'wp_ajax_generate_wc_invoice', 'handle_generate_wc_invoice_ajax' );
function handle_generate_wc_invoice_ajax() {
    if ( ! isset( $_GET['order_id'] ) || ! isset( $_GET['_wpnonce'] ) ) {
        wp_die( __( 'Invalid request.', 'your-text-domain' ) );
    }

    $order_id = intval( $_GET['order_id'] );

    if ( ! wp_verify_nonce( $_GET['_wpnonce'], 'generate_wc_invoice_nonce' ) ) {
        wp_die( __( 'Security check failed.', 'your-text-domain' ) );
    }

    if ( ! current_user_can( 'edit_shop_orders' ) ) {
         wp_die( __( 'You do not have permission to perform this action.', 'your-text-domain' ) );
    }

    $pdf_path = generate_woocommerce_pdf_invoice( $order_id );

    if ( $pdf_path && file_exists( $pdf_path ) ) {
        $file_size = filesize( $pdf_path );
        $file_info = pathinfo( $pdf_path );
        $filename = $file_info['basename'];

        header( 'Content-Description: File Transfer' );
        header( 'Content-Type: application/pdf' );
        header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
        header( 'Content-Transfer-Encoding: binary' );
        header( 'Expires: 0' );
        header( 'Cache-Control: must-revalidate' );
        header( 'Pragma: public' );
        header( 'Content-Length: ' . $file_size );

        readfile( $pdf_path );
        exit;
    } else {
        wp_die( __( 'Failed to generate PDF invoice.', 'your-text-domain' ) );
    }
}

Advanced Customizations and Considerations

The provided code is a starting point. For production-ready solutions, consider the following:

• Error Handling and Logging: Implement robust error handling and logging for failed PDF generations. Use WordPress’s built-in logging functions or a dedicated logging library.
• Custom Fields and Meta Data: Extend get_custom_woocommerce_order_data to include custom product meta, order meta, or user meta relevant to your reports.
• Complex Calculations: For reports requiring intricate financial calculations (e.g., profit margins, tax breakdowns), perform these calculations within your PHP function before populating the spreadsheet.
• Templating Engine: For highly complex layouts, consider using PhpSpreadsheet’s ability to load from HTML or integrate with a templating engine like Twig, then render that HTML to PDF.
• Performance Optimization: For very large datasets or frequent generation, optimize database queries and consider caching strategies. PhpSpreadsheet itself can be memory-intensive.
• Security: Always sanitize inputs and use nonces for AJAX requests. Ensure file permissions are correctly set for generated PDFs.
• Internationalization: Use WordPress’s internationalization functions (__(), _e()) for all user-facing strings. Adjust currency formatting based on locale.
• Alternative PDF Libraries: While Mpdf is powerful, explore Dompdf or TCPDF if they better suit your specific needs or licensing requirements. Ensure you install the chosen library via Composer.
• Report Types: Adapt the data retrieval and PhpSpreadsheet population logic to create different report types (e.g., daily sales summaries, product sales reports, customer lifetime value reports).

Conclusion

By combining WooCommerce’s data access capabilities with the flexibility of PhpSpreadsheet, you can build sophisticated, automated PDF financial reports and invoices tailored to your specific business requirements. This approach offers a high degree of customization beyond what typical plugins provide, empowering developers to create truly bespoke reporting solutions.

composer require phpoffice/phpspreadsheet

This command installs PhpSpreadsheet and its dependencies into the vendor directory. You’ll need to include Composer’s autoloader in your PHP scripts to access the library’s classes.

Core Components: Data Retrieval and PhpSpreadsheet Integration

The foundation of any reporting system is accurate data retrieval. For WooCommerce, this typically involves querying the WordPress database for order details, customer information, product data, and meta fields. We’ll then use PhpSpreadsheet to structure this data into a spreadsheet format, which can subsequently be rendered as a PDF.

Custom Order Data Retrieval Function

Let’s create a helper function to fetch detailed order information. This example focuses on retrieving core order data, line items, and customer details. For production, you’d expand this to include custom meta fields, shipping details, tax information, etc.

// Assume this is within a custom plugin or theme's functions.php file
// Ensure Composer's autoloader is included: require_once __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf; // Or Dompdf, TCPDF

/**
 * Fetches detailed data for a specific WooCommerce order.
 *
 * @param int $order_id The ID of the WooCommerce order.
 * @return array|null Order data array or null if order not found.
 */
function get_custom_woocommerce_order_data(int $order_id): ?array {
    if ( ! class_exists( 'WC_Order' ) ) {
        return null; // WooCommerce not active
    }

    $order = wc_get_order( $order_id );

    if ( ! $order ) {
        return null; // Order not found
    }

    $order_data = [
        'order_id'          => $order->get_id(),
        'order_date'        => $order->get_date_created()->format( 'Y-m-d H:i:s' ),
        'order_status'      => wc_get_order_status_name( $order->get_status() ),
        'customer_name'     => $order->get_formatted_billing_full_name(),
        'customer_email'    => $order->get_billing_email(),
        'customer_address'  => $order->get_formatted_billing_address(),
        'total_amount'      => $order->get_total(),
        'currency'          => $order->get_currency(),
        'line_items'        => [],
    ];

    foreach ( $order->get_items() as $item_id => $item ) {
        $product = $item->get_product();
        $order_data['line_items'][] = [
            'product_name'  => $product ? $product->get_name() : $item->get_name(),
            'quantity'      => $item->get_quantity(),
            'subtotal'      => $item->get_subtotal(),
            'total'         => $item->get_total(),
            'sku'           => $product ? $product->get_sku() : '',
        ];
    }

    // Add more data points as needed: shipping, taxes, meta, etc.
    // $order_data['shipping_address'] = $order->get_formatted_shipping_address();
    // $order_data['tax_total'] = $order->get_total_tax();

    return $order_data;
}

Generating the Spreadsheet with PhpSpreadsheet

Now, let’s integrate this data retrieval with PhpSpreadsheet to create a report. We’ll instantiate a Spreadsheet object, add worksheets, and populate them with the fetched order data. For PDF generation, PhpSpreadsheet relies on external libraries like Mpdf, Dompdf, or TCPDF. Mpdf is often a good choice for its comprehensive features and performance.

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

/**
 * Generates a PDF invoice for a given WooCommerce order using PhpSpreadsheet.
 *
 * @param int $order_id The ID of the WooCommerce order.
 * @return string|false Path to the generated PDF file or false on failure.
 */
function generate_woocommerce_pdf_invoice(int $order_id): string|false {
    $order_data = get_custom_woocommerce_order_data( $order_id );

    if ( ! $order_data ) {
        error_log("Failed to retrieve order data for order ID: " . $order_id);
        return false;
    }

    // Ensure Mpdf is installed: composer require mpdf/mpdf
    if ( ! class_exists( 'Mpdf' ) ) {
        error_log("Mpdf library not found. Please install it via Composer.");
        return false;
    }

    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Invoice #' . $order_id);

    // --- Styling and Layout ---
    $headerStyle = [
        'font' => ['bold' => true, 'size' => 14],
        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'D3D3D3']], // Light gray
        'borders' => ['bottom' => ['borderStyle' => Border::BORDER_THIN]],
    ];

    $subHeaderStyle = [
        'font' => ['bold' => true, 'size' => 12],
        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
        'borders' => ['bottom' => ['borderStyle' => Border::BORDER_THIN]],
    ];

    $cellStyle = [
        'font' => ['size' => 10],
        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
        'borders' => ['allborders' => ['borderStyle' => Border::BORDER_THIN]],
    ];

    $currencyFormat = NumberFormat::FORMAT_ACCOUNTING_USD; // Adjust currency format as needed

    // --- Populate Data ---

    // Company Info (Top Left)
    $sheet->setCellValue('A1', 'Your Company Name');
    $sheet->setCellValue('A2', '123 Business St');
    $sheet->setCellValue('A3', 'City, State, ZIP');
    $sheet->setCellValue('A4', '[email protected]');
    $sheet->getStyle('A1:A4')->applyFromArray(['font' => ['bold' => true, 'size' => 12]]);

    // Invoice Title (Top Right)
    $sheet->setCellValue('F1', 'INVOICE');
    $sheet->getStyle('F1')->applyFromArray($headerStyle);
    $sheet->mergeCells('F1:H1');
    $sheet->getStyle('F1:H1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

    // Invoice Details
    $sheet->setCellValue('F2', 'Invoice #:');
    $sheet->setCellValue('G2', $order_data['order_id']);
    $sheet->setCellValue('F3', 'Date:');
    $sheet->setCellValue('G3', $order_data['order_date']);
    $sheet->setCellValue('F4', 'Status:');
    $sheet->setCellValue('G4', $order_data['order_status']);
    $sheet->getStyle('F2:G4')->applyFromArray(['font' => ['bold' => true]]);

    // Customer Info
    $sheet->setCellValue('A6', 'Bill To:');
    $sheet->getStyle('A6')->applyFromArray($subHeaderStyle);
    $sheet->setCellValue('A7', $order_data['customer_name']);
    $sheet->setCellValue('A8', $order_data['customer_address']); // Assumes formatted address includes newlines
    $sheet->setCellValue('A9', $order_data['customer_email']);

    // Line Items Header
    $sheet->setCellValue('A11', 'Product Name');
    $sheet->setCellValue('C11', 'Quantity');
    $sheet->setCellValue('D11', 'Unit Price'); // Assuming we can derive this
    $sheet->setCellValue('E11', 'Subtotal');
    $sheet->setCellValue('F11', 'Total');
    $sheet->getStyle('A11:F11')->applyFromArray($subHeaderStyle);

    // Line Items Data
    $row_start = 12;
    $current_row = $row_start;
    foreach ( $order_data['line_items'] as $item ) {
        $sheet->setCellValue('A' . $current_row, $item['product_name']);
        $sheet->setCellValue('C' . $current_row, $item['quantity']);
        // Calculate unit price if possible, otherwise use subtotal/quantity
        $unit_price = $item['quantity'] > 0 ? $item['subtotal'] / $item['quantity'] : 0;
        $sheet->setCellValue('D' . $current_row, $unit_price);
        $sheet->setCellValue('E' . $current_row, $item['subtotal']);
        $sheet->setCellValue('F' . $current_row, $item['total']);

        $sheet->getStyle('A' . $current_row)->applyFromArray($cellStyle);
        $sheet->getStyle('C' . $current_row)->applyFromArray($cellStyle);
        $sheet->getStyle('D' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);
        $sheet->getStyle('E' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);
        $sheet->getStyle('F' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);

        $current_row++;
    }

    // Totals Section
    $sheet->setCellValue('E' . $current_row, 'Total:');
    $sheet->getStyle('E' . $current_row)->applyFromArray($subHeaderStyle);
    $sheet->setCellValue('F' . $current_row, $order_data['total_amount']);
    $sheet->getStyle('F' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);
    $sheet->getStyle('E' . $current_row . ':F' . $current_row)->getFont()->setBold(true);

    // Adjust column widths
    foreach (range('A', 'F') as $columnID) {
        $sheet->getColumnDimension($columnID)->setAutoSize(true);
    }
    $sheet->getColumnDimension('A')->setWidth(30); // Product Name might need more width

    // --- PDF Rendering ---
    $writer = new Mpdf($spreadsheet);
    $upload_dir = wp_upload_dir();
    $pdf_filename = "invoice_{$order_id}_" . date('YmdHis') . ".pdf";
    $pdf_path = trailingslashit($upload_dir['basedir']) . $pdf_filename;

    try {
        $writer->save($pdf_path);
        return $pdf_path;
    } catch (\Exception $e) {
        error_log("Error saving PDF for order ID {$order_id}: " . $e->getMessage());
        return false;
    }
}

Implementing the PDF Generation Trigger

You need a mechanism to trigger the PDF generation. Common approaches include:

• Admin Order List: Add a custom action link to each order in the WooCommerce Orders list.
• Order Status Change: Automatically generate a PDF when an order reaches a specific status (e.g., ‘Processing’ or ‘Completed’).
• Customer Account: Allow customers to download invoices from their “My Account” page.
• Cron Job: For batch generation of reports (e.g., daily sales summaries).

Example: Adding an Admin Action Link

This example demonstrates how to add a “Generate Invoice” link to the WooCommerce order list in the WordPress admin area. Clicking this link will trigger the generate_woocommerce_pdf_invoice function and prompt the user to download the PDF.

/**
 * Add custom action link to WooCommerce order list.
 */
add_filter( 'woocommerce_admin_order_actions', 'add_generate_invoice_order_action', 10, 2 );
function add_generate_invoice_order_action( $actions, $order ) {
    $actions['generate_invoice'] = [
        'url'    => wp_nonce_url( admin_url( 'admin-ajax.php?action=generate_wc_invoice&order_id=' . $order->get_id() ), 'generate_wc_invoice_nonce' ),
        'name'   => __( 'Generate Invoice', 'your-text-domain' ),
    ];
    return $actions;
}

/**
 * Handle AJAX request for generating invoice.
 */
add_action( 'wp_ajax_generate_wc_invoice', 'handle_generate_wc_invoice_ajax' );
function handle_generate_wc_invoice_ajax() {
    if ( ! isset( $_GET['order_id'] ) || ! isset( $_GET['_wpnonce'] ) ) {
        wp_die( __( 'Invalid request.', 'your-text-domain' ) );
    }

    $order_id = intval( $_GET['order_id'] );

    if ( ! wp_verify_nonce( $_GET['_wpnonce'], 'generate_wc_invoice_nonce' ) ) {
        wp_die( __( 'Security check failed.', 'your-text-domain' ) );
    }

    if ( ! current_user_can( 'edit_shop_orders' ) ) {
         wp_die( __( 'You do not have permission to perform this action.', 'your-text-domain' ) );
    }

    $pdf_path = generate_woocommerce_pdf_invoice( $order_id );

    if ( $pdf_path && file_exists( $pdf_path ) ) {
        $file_size = filesize( $pdf_path );
        $file_info = pathinfo( $pdf_path );
        $filename = $file_info['basename'];

        header( 'Content-Description: File Transfer' );
        header( 'Content-Type: application/pdf' );
        header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
        header( 'Content-Transfer-Encoding: binary' );
        header( 'Expires: 0' );
        header( 'Cache-Control: must-revalidate' );
        header( 'Pragma: public' );
        header( 'Content-Length: ' . $file_size );

        readfile( $pdf_path );
        exit;
    } else {
        wp_die( __( 'Failed to generate PDF invoice.', 'your-text-domain' ) );
    }
}

Advanced Customizations and Considerations

The provided code is a starting point. For production-ready solutions, consider the following:

• Error Handling and Logging: Implement robust error handling and logging for failed PDF generations. Use WordPress’s built-in logging functions or a dedicated logging library.
• Custom Fields and Meta Data: Extend get_custom_woocommerce_order_data to include custom product meta, order meta, or user meta relevant to your reports.
• Complex Calculations: For reports requiring intricate financial calculations (e.g., profit margins, tax breakdowns), perform these calculations within your PHP function before populating the spreadsheet.
• Templating Engine: For highly complex layouts, consider using PhpSpreadsheet’s ability to load from HTML or integrate with a templating engine like Twig, then render that HTML to PDF.
• Performance Optimization: For very large datasets or frequent generation, optimize database queries and consider caching strategies. PhpSpreadsheet itself can be memory-intensive.
• Security: Always sanitize inputs and use nonces for AJAX requests. Ensure file permissions are correctly set for generated PDFs.
• Internationalization: Use WordPress’s internationalization functions (__(), _e()) for all user-facing strings. Adjust currency formatting based on locale.
• Alternative PDF Libraries: While Mpdf is powerful, explore Dompdf or TCPDF if they better suit your specific needs or licensing requirements. Ensure you install the chosen library via Composer.
• Report Types: Adapt the data retrieval and PhpSpreadsheet population logic to create different report types (e.g., daily sales summaries, product sales reports, customer lifetime value reports).

Conclusion

By combining WooCommerce’s data access capabilities with the flexibility of PhpSpreadsheet, you can build sophisticated, automated PDF financial reports and invoices tailored to your specific business requirements. This approach offers a high degree of customization beyond what typical plugins provide, empowering developers to create truly bespoke reporting solutions.

Leveraging PhpSpreadsheet for Advanced WooCommerce PDF Reporting

Generating dynamic, customized PDF financial reports and invoices directly from WooCommerce data can be a significant challenge. While many plugins offer basic templating, achieving granular control over layout, complex calculations, and integration with custom business logic often requires a more robust solution. This post details how to build such a system using PhpSpreadsheet, a powerful PHP library for reading and writing spreadsheet files, adapted for PDF generation via its integrated support for various renderers, including PDF.

Setting Up the Development Environment

Before diving into code, ensure your WordPress development environment is properly configured. This involves having Composer installed and a local WordPress instance running. We’ll be adding PhpSpreadsheet as a dependency.

Navigate to your WordPress root directory in your terminal and run:

composer require phpoffice/phpspreadsheet

This command installs PhpSpreadsheet and its dependencies into the vendor directory. You’ll need to include Composer’s autoloader in your PHP scripts to access the library’s classes.

Core Components: Data Retrieval and PhpSpreadsheet Integration

The foundation of any reporting system is accurate data retrieval. For WooCommerce, this typically involves querying the WordPress database for order details, customer information, product data, and meta fields. We’ll then use PhpSpreadsheet to structure this data into a spreadsheet format, which can subsequently be rendered as a PDF.

Custom Order Data Retrieval Function

Let’s create a helper function to fetch detailed order information. This example focuses on retrieving core order data, line items, and customer details. For production, you’d expand this to include custom meta fields, shipping details, tax information, etc.

// Assume this is within a custom plugin or theme's functions.php file
// Ensure Composer's autoloader is included: require_once __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf; // Or Dompdf, TCPDF

/**
 * Fetches detailed data for a specific WooCommerce order.
 *
 * @param int $order_id The ID of the WooCommerce order.
 * @return array|null Order data array or null if order not found.
 */
function get_custom_woocommerce_order_data(int $order_id): ?array {
    if ( ! class_exists( 'WC_Order' ) ) {
        return null; // WooCommerce not active
    }

    $order = wc_get_order( $order_id );

    if ( ! $order ) {
        return null; // Order not found
    }

    $order_data = [
        'order_id'          => $order->get_id(),
        'order_date'        => $order->get_date_created()->format( 'Y-m-d H:i:s' ),
        'order_status'      => wc_get_order_status_name( $order->get_status() ),
        'customer_name'     => $order->get_formatted_billing_full_name(),
        'customer_email'    => $order->get_billing_email(),
        'customer_address'  => $order->get_formatted_billing_address(),
        'total_amount'      => $order->get_total(),
        'currency'          => $order->get_currency(),
        'line_items'        => [],
    ];

    foreach ( $order->get_items() as $item_id => $item ) {
        $product = $item->get_product();
        $order_data['line_items'][] = [
            'product_name'  => $product ? $product->get_name() : $item->get_name(),
            'quantity'      => $item->get_quantity(),
            'subtotal'      => $item->get_subtotal(),
            'total'         => $item->get_total(),
            'sku'           => $product ? $product->get_sku() : '',
        ];
    }

    // Add more data points as needed: shipping, taxes, meta, etc.
    // $order_data['shipping_address'] = $order->get_formatted_shipping_address();
    // $order_data['tax_total'] = $order->get_total_tax();

    return $order_data;
}

Generating the Spreadsheet with PhpSpreadsheet

Now, let’s integrate this data retrieval with PhpSpreadsheet to create a report. We’ll instantiate a Spreadsheet object, add worksheets, and populate them with the fetched order data. For PDF generation, PhpSpreadsheet relies on external libraries like Mpdf, Dompdf, or TCPDF. Mpdf is often a good choice for its comprehensive features and performance.

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

/**
 * Generates a PDF invoice for a given WooCommerce order using PhpSpreadsheet.
 *
 * @param int $order_id The ID of the WooCommerce order.
 * @return string|false Path to the generated PDF file or false on failure.
 */
function generate_woocommerce_pdf_invoice(int $order_id): string|false {
    $order_data = get_custom_woocommerce_order_data( $order_id );

    if ( ! $order_data ) {
        error_log("Failed to retrieve order data for order ID: " . $order_id);
        return false;
    }

    // Ensure Mpdf is installed: composer require mpdf/mpdf
    if ( ! class_exists( 'Mpdf' ) ) {
        error_log("Mpdf library not found. Please install it via Composer.");
        return false;
    }

    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Invoice #' . $order_id);

    // --- Styling and Layout ---
    $headerStyle = [
        'font' => ['bold' => true, 'size' => 14],
        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'D3D3D3']], // Light gray
        'borders' => ['bottom' => ['borderStyle' => Border::BORDER_THIN]],
    ];

    $subHeaderStyle = [
        'font' => ['bold' => true, 'size' => 12],
        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
        'borders' => ['bottom' => ['borderStyle' => Border::BORDER_THIN]],
    ];

    $cellStyle = [
        'font' => ['size' => 10],
        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
        'borders' => ['allborders' => ['borderStyle' => Border::BORDER_THIN]],
    ];

    $currencyFormat = NumberFormat::FORMAT_ACCOUNTING_USD; // Adjust currency format as needed

    // --- Populate Data ---

    // Company Info (Top Left)
    $sheet->setCellValue('A1', 'Your Company Name');
    $sheet->setCellValue('A2', '123 Business St');
    $sheet->setCellValue('A3', 'City, State, ZIP');
    $sheet->setCellValue('A4', '[email protected]');
    $sheet->getStyle('A1:A4')->applyFromArray(['font' => ['bold' => true, 'size' => 12]]);

    // Invoice Title (Top Right)
    $sheet->setCellValue('F1', 'INVOICE');
    $sheet->getStyle('F1')->applyFromArray($headerStyle);
    $sheet->mergeCells('F1:H1');
    $sheet->getStyle('F1:H1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

    // Invoice Details
    $sheet->setCellValue('F2', 'Invoice #:');
    $sheet->setCellValue('G2', $order_data['order_id']);
    $sheet->setCellValue('F3', 'Date:');
    $sheet->setCellValue('G3', $order_data['order_date']);
    $sheet->setCellValue('F4', 'Status:');
    $sheet->setCellValue('G4', $order_data['order_status']);
    $sheet->getStyle('F2:G4')->applyFromArray(['font' => ['bold' => true]]);

    // Customer Info
    $sheet->setCellValue('A6', 'Bill To:');
    $sheet->getStyle('A6')->applyFromArray($subHeaderStyle);
    $sheet->setCellValue('A7', $order_data['customer_name']);
    $sheet->setCellValue('A8', $order_data['customer_address']); // Assumes formatted address includes newlines
    $sheet->setCellValue('A9', $order_data['customer_email']);

    // Line Items Header
    $sheet->setCellValue('A11', 'Product Name');
    $sheet->setCellValue('C11', 'Quantity');
    $sheet->setCellValue('D11', 'Unit Price'); // Assuming we can derive this
    $sheet->setCellValue('E11', 'Subtotal');
    $sheet->setCellValue('F11', 'Total');
    $sheet->getStyle('A11:F11')->applyFromArray($subHeaderStyle);

    // Line Items Data
    $row_start = 12;
    $current_row = $row_start;
    foreach ( $order_data['line_items'] as $item ) {
        $sheet->setCellValue('A' . $current_row, $item['product_name']);
        $sheet->setCellValue('C' . $current_row, $item['quantity']);
        // Calculate unit price if possible, otherwise use subtotal/quantity
        $unit_price = $item['quantity'] > 0 ? $item['subtotal'] / $item['quantity'] : 0;
        $sheet->setCellValue('D' . $current_row, $unit_price);
        $sheet->setCellValue('E' . $current_row, $item['subtotal']);
        $sheet->setCellValue('F' . $current_row, $item['total']);

        $sheet->getStyle('A' . $current_row)->applyFromArray($cellStyle);
        $sheet->getStyle('C' . $current_row)->applyFromArray($cellStyle);
        $sheet->getStyle('D' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);
        $sheet->getStyle('E' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);
        $sheet->getStyle('F' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);

        $current_row++;
    }

    // Totals Section
    $sheet->setCellValue('E' . $current_row, 'Total:');
    $sheet->getStyle('E' . $current_row)->applyFromArray($subHeaderStyle);
    $sheet->setCellValue('F' . $current_row, $order_data['total_amount']);
    $sheet->getStyle('F' . $current_row)->applyFromArray($cellStyle)->getNumberFormat()->setFormatCode($currencyFormat);
    $sheet->getStyle('E' . $current_row . ':F' . $current_row)->getFont()->setBold(true);

    // Adjust column widths
    foreach (range('A', 'F') as $columnID) {
        $sheet->getColumnDimension($columnID)->setAutoSize(true);
    }
    $sheet->getColumnDimension('A')->setWidth(30); // Product Name might need more width

    // --- PDF Rendering ---
    $writer = new Mpdf($spreadsheet);
    $upload_dir = wp_upload_dir();
    $pdf_filename = "invoice_{$order_id}_" . date('YmdHis') . ".pdf";
    $pdf_path = trailingslashit($upload_dir['basedir']) . $pdf_filename;

    try {
        $writer->save($pdf_path);
        return $pdf_path;
    } catch (\Exception $e) {
        error_log("Error saving PDF for order ID {$order_id}: " . $e->getMessage());
        return false;
    }
}

Implementing the PDF Generation Trigger

You need a mechanism to trigger the PDF generation. Common approaches include:

• Admin Order List: Add a custom action link to each order in the WooCommerce Orders list.
• Order Status Change: Automatically generate a PDF when an order reaches a specific status (e.g., ‘Processing’ or ‘Completed’).
• Customer Account: Allow customers to download invoices from their “My Account” page.
• Cron Job: For batch generation of reports (e.g., daily sales summaries).

Example: Adding an Admin Action Link

This example demonstrates how to add a “Generate Invoice” link to the WooCommerce order list in the WordPress admin area. Clicking this link will trigger the generate_woocommerce_pdf_invoice function and prompt the user to download the PDF.

/**
 * Add custom action link to WooCommerce order list.
 */
add_filter( 'woocommerce_admin_order_actions', 'add_generate_invoice_order_action', 10, 2 );
function add_generate_invoice_order_action( $actions, $order ) {
    $actions['generate_invoice'] = [
        'url'    => wp_nonce_url( admin_url( 'admin-ajax.php?action=generate_wc_invoice&order_id=' . $order->get_id() ), 'generate_wc_invoice_nonce' ),
        'name'   => __( 'Generate Invoice', 'your-text-domain' ),
    ];
    return $actions;
}

/**
 * Handle AJAX request for generating invoice.
 */
add_action( 'wp_ajax_generate_wc_invoice', 'handle_generate_wc_invoice_ajax' );
function handle_generate_wc_invoice_ajax() {
    if ( ! isset( $_GET['order_id'] ) || ! isset( $_GET['_wpnonce'] ) ) {
        wp_die( __( 'Invalid request.', 'your-text-domain' ) );
    }

    $order_id = intval( $_GET['order_id'] );

    if ( ! wp_verify_nonce( $_GET['_wpnonce'], 'generate_wc_invoice_nonce' ) ) {
        wp_die( __( 'Security check failed.', 'your-text-domain' ) );
    }

    if ( ! current_user_can( 'edit_shop_orders' ) ) {
         wp_die( __( 'You do not have permission to perform this action.', 'your-text-domain' ) );
    }

    $pdf_path = generate_woocommerce_pdf_invoice( $order_id );

    if ( $pdf_path && file_exists( $pdf_path ) ) {
        $file_size = filesize( $pdf_path );
        $file_info = pathinfo( $pdf_path );
        $filename = $file_info['basename'];

        header( 'Content-Description: File Transfer' );
        header( 'Content-Type: application/pdf' );
        header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
        header( 'Content-Transfer-Encoding: binary' );
        header( 'Expires: 0' );
        header( 'Cache-Control: must-revalidate' );
        header( 'Pragma: public' );
        header( 'Content-Length: ' . $file_size );

        readfile( $pdf_path );
        exit;
    } else {
        wp_die( __( 'Failed to generate PDF invoice.', 'your-text-domain' ) );
    }
}

Advanced Customizations and Considerations

The provided code is a starting point. For production-ready solutions, consider the following:

• Error Handling and Logging: Implement robust error handling and logging for failed PDF generations. Use WordPress’s built-in logging functions or a dedicated logging library.
• Custom Fields and Meta Data: Extend get_custom_woocommerce_order_data to include custom product meta, order meta, or user meta relevant to your reports.
• Complex Calculations: For reports requiring intricate financial calculations (e.g., profit margins, tax breakdowns), perform these calculations within your PHP function before populating the spreadsheet.
• Templating Engine: For highly complex layouts, consider using PhpSpreadsheet’s ability to load from HTML or integrate with a templating engine like Twig, then render that HTML to PDF.
• Performance Optimization: For very large datasets or frequent generation, optimize database queries and consider caching strategies. PhpSpreadsheet itself can be memory-intensive.
• Security: Always sanitize inputs and use nonces for AJAX requests. Ensure file permissions are correctly set for generated PDFs.
• Internationalization: Use WordPress’s internationalization functions (__(), _e()) for all user-facing strings. Adjust currency formatting based on locale.
• Alternative PDF Libraries: While Mpdf is powerful, explore Dompdf or TCPDF if they better suit your specific needs or licensing requirements. Ensure you install the chosen library via Composer.
• Report Types: Adapt the data retrieval and PhpSpreadsheet population logic to create different report types (e.g., daily sales summaries, product sales reports, customer lifetime value reports).

Conclusion

By combining WooCommerce’s data access capabilities with the flexibility of PhpSpreadsheet, you can build sophisticated, automated PDF financial reports and invoices tailored to your specific business requirements. This approach offers a high degree of customization beyond what typical plugins provide, empowering developers to create truly bespoke reporting solutions.