Building custom automated PDF financial reports and invoices for WooCommerce using custom PHP-Spreadsheet exports

<?php
// ... (after generating $spreadsheet object)

// Create an HTML writer
$htmlWriter = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
$htmlContent = $htmlWriter->generateSheetData($sheet); // Generates HTML for the active sheet

// Initialize mPDF
$mpdf = new \Mpdf\Mpdf();

// Write the HTML content to mPDF
$mpdf->WriteHTML($htmlContent);

// Output the PDF
$filename = 'report.pdf';
$mpdf->Output($filename, \Mpdf\Output\Destination::INLINE); // Or DESTINATION_DOWNLOAD
exit;
?>

Option 2: Using LibreOffice Headless (Command Line)

This method requires LibreOffice to be installed on your server. You can execute a command from PHP using `shell_exec` or `exec`.

# Assuming your XLSX file is saved as 'report.xlsx'
# And you want to convert it to 'report.pdf'

# On Linux/macOS
/usr/bin/soffice --headless --convert-to pdf --outdir /path/to/output/directory /path/to/your/report.xlsx

# Or directly from PHP
$xlsxFilePath = '/path/to/your/report.xlsx';
$outputDir = '/path/to/output/directory';
$command = "/usr/bin/soffice --headless --convert-to pdf --outdir " . escapeshellarg($outputDir) . " " . escapeshellarg($xlsxFilePath);
shell_exec($command);
?>

This approach is often more reliable for complex formatting but adds a dependency on server software. Ensure the web server user has execute permissions for LibreOffice and write permissions for the output directory.

Implementation Strategies in WordPress

You can integrate this functionality into WordPress in several ways:

• Custom Plugin: The cleanest approach. Create a dedicated plugin that adds a new admin menu item (e.g., “Custom Reports”). This menu item would lead to a page with forms for date selection or order filtering, triggering the report generation.
• Theme Functions (`functions.php`): For simpler needs or theme-specific reports, you can add the code to your theme’s `functions.php` file. However, this couples the functionality tightly to the theme and is not recommended for complex or critical features.
• Admin AJAX: For a more interactive user experience, you can trigger the report generation via AJAX calls from an admin page. This allows for background processing and avoids full page reloads.

When implementing in WordPress, remember to hook into appropriate actions and filters, sanitize user inputs, and handle potential errors gracefully. For security, ensure that report generation is restricted to users with appropriate roles (e.g., administrators, shop managers).

Advanced Considerations

Performance: For very large datasets (thousands of orders), generating reports directly in the browser can lead to timeouts. Consider implementing background processing using WP-Cron or a dedicated job queue system.

Customization: PHP-Spreadsheet offers extensive styling options, charting capabilities, and data validation. You can tailor reports to include complex calculations, pivot tables, or visual representations of data.

Error Handling: Implement robust error handling for database queries, file operations, and external process calls (like LibreOffice). Log errors for debugging.

Security: Always sanitize and validate any user-provided input (like date ranges) before using it in queries or commands. Restrict access to report generation functions to authorized users.

composer require mpdf/mpdf

Then, after generating the XLSX, you can generate HTML from the spreadsheet and pass it to mPDF. This requires a bit more work to correctly map spreadsheet cells to HTML tables.

<?php
// ... (after generating $spreadsheet object)

// Create an HTML writer
$htmlWriter = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
$htmlContent = $htmlWriter->generateSheetData($sheet); // Generates HTML for the active sheet

// Initialize mPDF
$mpdf = new \Mpdf\Mpdf();

// Write the HTML content to mPDF
$mpdf->WriteHTML($htmlContent);

// Output the PDF
$filename = 'report.pdf';
$mpdf->Output($filename, \Mpdf\Output\Destination::INLINE); // Or DESTINATION_DOWNLOAD
exit;
?>

Option 2: Using LibreOffice Headless (Command Line)

This method requires LibreOffice to be installed on your server. You can execute a command from PHP using `shell_exec` or `exec`.

# Assuming your XLSX file is saved as 'report.xlsx'
# And you want to convert it to 'report.pdf'

# On Linux/macOS
/usr/bin/soffice --headless --convert-to pdf --outdir /path/to/output/directory /path/to/your/report.xlsx

# Or directly from PHP
$xlsxFilePath = '/path/to/your/report.xlsx';
$outputDir = '/path/to/output/directory';
$command = "/usr/bin/soffice --headless --convert-to pdf --outdir " . escapeshellarg($outputDir) . " " . escapeshellarg($xlsxFilePath);
shell_exec($command);
?>

This approach is often more reliable for complex formatting but adds a dependency on server software. Ensure the web server user has execute permissions for LibreOffice and write permissions for the output directory.

Implementation Strategies in WordPress

You can integrate this functionality into WordPress in several ways:

• Custom Plugin: The cleanest approach. Create a dedicated plugin that adds a new admin menu item (e.g., “Custom Reports”). This menu item would lead to a page with forms for date selection or order filtering, triggering the report generation.
• Theme Functions (`functions.php`): For simpler needs or theme-specific reports, you can add the code to your theme’s `functions.php` file. However, this couples the functionality tightly to the theme and is not recommended for complex or critical features.
• Admin AJAX: For a more interactive user experience, you can trigger the report generation via AJAX calls from an admin page. This allows for background processing and avoids full page reloads.

When implementing in WordPress, remember to hook into appropriate actions and filters, sanitize user inputs, and handle potential errors gracefully. For security, ensure that report generation is restricted to users with appropriate roles (e.g., administrators, shop managers).

Advanced Considerations

Performance: For very large datasets (thousands of orders), generating reports directly in the browser can lead to timeouts. Consider implementing background processing using WP-Cron or a dedicated job queue system.

Customization: PHP-Spreadsheet offers extensive styling options, charting capabilities, and data validation. You can tailor reports to include complex calculations, pivot tables, or visual representations of data.

Error Handling: Implement robust error handling for database queries, file operations, and external process calls (like LibreOffice). Log errors for debugging.

Security: Always sanitize and validate any user-provided input (like date ranges) before using it in queries or commands. Restrict access to report generation functions to authorized users.

<?php
// Assuming $orders is populated from get_woocommerce_orders_in_date_range()

if ( ! empty( $orders ) ) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Invoices');

    $current_row = 1; // Start at the top

    // Company details (replace with your actual company info)
    $company_name = get_bloginfo('name');
    $company_address = get_option('woocommerce_store_address') . ', ' . get_option('woocommerce_store_city') . ', ' . get_option('woocommerce_store_postcode');
    $company_email = get_option('admin_email'); // Or a specific company email

    foreach ( $orders as $order ) {
        /** @var WC_Order $order */

        // Invoice Header
        $sheet->setCellValue('A' . $current_row, 'INVOICE');
        $sheet->getStyle('A' . $current_row)->getFont()->setSize(18)->setBold(true);
        $current_row++;

        // Company Info
        $sheet->setCellValue('A' . $current_row, $company_name);
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $company_address);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $company_email);
        $current_row += 2; // Add some spacing

        // Invoice Details
        $sheet->setCellValue('F' . $current_row, 'Invoice #:');
        $sheet->setCellValue('G' . $current_row, $order->get_id());
        $current_row++;
        $sheet->setCellValue('F' . $current_row, 'Invoice Date:');
        $sheet->setCellValue('G' . $current_row, $order->get_date_created()->format('Y-m-d'));
        $current_row++;
        $sheet->setCellValue('F' . $current_row, 'Due Date:');
        // Calculate due date (e.g., 30 days from invoice date)
        $due_date = $order->get_date_created()->modify('+30 days');
        $sheet->setCellValue('G' . $current_row, $due_date->format('Y-m-d'));
        $current_row += 2;

        // Customer Info
        $sheet->setCellValue('A' . $current_row, 'Bill To:');
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_formatted_billing_full_name());
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_billing_address_1());
        $current_row++;
        if ( $order->get_billing_address_2() ) {
            $sheet->setCellValue('A' . $current_row, $order->get_billing_address_2());
            $current_row++;
        }
        $sheet->setCellValue('A' . $current_row, $order->get_billing_city() . ', ' . $order->get_billing_postcode());
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_billing_country());
        $current_row += 2;

        // Line Items Header
        $sheet->setCellValue('A' . $current_row, 'Description');
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('D' . $current_row, 'Quantity');
        $sheet->getStyle('D' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('E' . $current_row, 'Unit Price');
        $sheet->getStyle('E' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('F' . $current_row, 'Total');
        $sheet->getStyle('F' . $current_row)->getFont()->setBold(true);
        $current_row++;

        // Line Items
        $line_item_start_row = $current_row;
        foreach ( $order->get_items() as $item_id => $item ) {
            /** @var WC_Order_Item_Product $item */
            $product_name = $item->get_name();
            $quantity = $item->get_quantity();
            $unit_price = wc_get_price_including_tax( $item ) ? $item->get_total() / $quantity : $item->get_subtotal() / $quantity;
            $line_total = $item->get_total();

            $sheet->setCellValue('A' . $current_row, $product_name);
            $sheet->setCellValue('D' . $current_row, $quantity);
            $sheet->setCellValue('E' . $current_row, wc_format_decimal( $unit_price, 2 ));
            $sheet->setCellValue('F' . $current_row, wc_format_decimal( $line_total, 2 ));
            $current_row++;
        }
        $line_item_end_row = $current_row - 1;

        // Apply borders to line items
        $sheet->getStyle('A' . $line_item_start_row . ':F' . $line_item_end_row)->applyFromArray([
            'borders' => [
                'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
            ],
        ]);

        // Totals Section
        $current_row++; // Space before totals
        $sheet->setCellValue('E' . $current_row, 'Subtotal:');
        $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_subtotal(), 2 ));
        $current_row++;

        if ( $order->get_total_discount() > 0 ) {
            $sheet->setCellValue('E' . $current_row, 'Discount:');
            $sheet->setCellValue('F' . $current_row, '-' . wc_format_decimal( $order->get_total_discount(), 2 ));
            $current_row++;
        }

        // Taxes
        $taxes = $order->get_taxes();
        if ( ! empty( $taxes ) ) {
            foreach ( $taxes as $tax_rate_id => $tax_amount ) {
                $tax_rate = new WC_Tax();
                $tax_label = $tax_rate->get_rate_label( $tax_rate_id );
                $sheet->setCellValue('E' . $current_row, $tax_label . ':');
                $sheet->setCellValue('F' . $current_row, wc_format_decimal( $tax_amount, 2 ));
                $current_row++;
            }
        }

        // Shipping
        if ( $order->get_total_shipping() > 0 ) {
            $sheet->setCellValue('E' . $current_row, 'Shipping:');
            $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_total_shipping(), 2 ));
            $current_row++;
        }

        // Grand Total
        $sheet->setCellValue('E' . $current_row, 'Total:');
        $sheet->getStyle('E' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_total(), 2 ));
        $sheet->getStyle('F' . $current_row)->getFont()->setBold(true);
        $current_row++;

        // Add a separator for the next invoice
        $current_row += 3;
    }

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

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

    // Define the output filename
    $filename = 'invoices-' . date('Y-m-d') . '.xlsx';

    // Output the file to the browser
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer->save('php://output');
    exit;
} else {
    echo "No orders found to generate invoices.";
}
?>

Converting XLSX to PDF

PHP-Spreadsheet itself does not directly export to PDF. However, it can generate HTML, which can then be converted to PDF using libraries like mPDF or TCPDF. Alternatively, for server-side conversion, you might consider headless browser solutions like Puppeteer (Node.js) or command-line tools like LibreOffice in headless mode.

Option 1: Using mPDF (PHP Library)

First, install mPDF:

composer require mpdf/mpdf

Then, after generating the XLSX, you can generate HTML from the spreadsheet and pass it to mPDF. This requires a bit more work to correctly map spreadsheet cells to HTML tables.

<?php
// ... (after generating $spreadsheet object)

// Create an HTML writer
$htmlWriter = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
$htmlContent = $htmlWriter->generateSheetData($sheet); // Generates HTML for the active sheet

// Initialize mPDF
$mpdf = new \Mpdf\Mpdf();

// Write the HTML content to mPDF
$mpdf->WriteHTML($htmlContent);

// Output the PDF
$filename = 'report.pdf';
$mpdf->Output($filename, \Mpdf\Output\Destination::INLINE); // Or DESTINATION_DOWNLOAD
exit;
?>

Option 2: Using LibreOffice Headless (Command Line)

This method requires LibreOffice to be installed on your server. You can execute a command from PHP using `shell_exec` or `exec`.

# Assuming your XLSX file is saved as 'report.xlsx'
# And you want to convert it to 'report.pdf'

# On Linux/macOS
/usr/bin/soffice --headless --convert-to pdf --outdir /path/to/output/directory /path/to/your/report.xlsx

# Or directly from PHP
$xlsxFilePath = '/path/to/your/report.xlsx';
$outputDir = '/path/to/output/directory';
$command = "/usr/bin/soffice --headless --convert-to pdf --outdir " . escapeshellarg($outputDir) . " " . escapeshellarg($xlsxFilePath);
shell_exec($command);
?>

This approach is often more reliable for complex formatting but adds a dependency on server software. Ensure the web server user has execute permissions for LibreOffice and write permissions for the output directory.

Implementation Strategies in WordPress

You can integrate this functionality into WordPress in several ways:

• Custom Plugin: The cleanest approach. Create a dedicated plugin that adds a new admin menu item (e.g., “Custom Reports”). This menu item would lead to a page with forms for date selection or order filtering, triggering the report generation.
• Theme Functions (`functions.php`): For simpler needs or theme-specific reports, you can add the code to your theme’s `functions.php` file. However, this couples the functionality tightly to the theme and is not recommended for complex or critical features.
• Admin AJAX: For a more interactive user experience, you can trigger the report generation via AJAX calls from an admin page. This allows for background processing and avoids full page reloads.

When implementing in WordPress, remember to hook into appropriate actions and filters, sanitize user inputs, and handle potential errors gracefully. For security, ensure that report generation is restricted to users with appropriate roles (e.g., administrators, shop managers).

Advanced Considerations

Performance: For very large datasets (thousands of orders), generating reports directly in the browser can lead to timeouts. Consider implementing background processing using WP-Cron or a dedicated job queue system.

Customization: PHP-Spreadsheet offers extensive styling options, charting capabilities, and data validation. You can tailor reports to include complex calculations, pivot tables, or visual representations of data.

Error Handling: Implement robust error handling for database queries, file operations, and external process calls (like LibreOffice). Log errors for debugging.

Security: Always sanitize and validate any user-provided input (like date ranges) before using it in queries or commands. Restrict access to report generation functions to authorized users.

<?php
// Assuming $orders is populated from the previous step
if ( ! empty( $orders ) ) {
    // Create a new Spreadsheet object
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Monthly Sales Report');

    // Add headers
    $headers = array(
        'Order ID',
        'Order Date',
        'Customer Name',
        'Customer Email',
        'Total Amount',
        'Payment Method',
        'Order Status',
    );
    $sheet->fromArray( $headers, NULL, 'A1' );

    // Apply header styling (optional)
    $headerStyle = [
        'font' => ['bold' => true],
        'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFE0E0E0']],
    ];
    $sheet->getStyle('A1:' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($headers)) . '1')->applyFromArray($headerStyle);

    // Populate data rows
    $row_num = 2; // Start from the second row
    foreach ( $orders as $order ) {
        /** @var WC_Order $order */
        $customer_name = $order->get_formatted_billing_full_name();
        $customer_email = $order->get_billing_email();
        $total_amount = $order->get_total();
        $payment_method = $order->get_payment_method_title();
        $order_status = wc_get_order_status_name( $order->get_status() );

        $sheet->setCellValue('A' . $row_num, $order->get_id());
        $sheet->setCellValue('B' . $row_num, $order->get_date_created()->format('Y-m-d H:i:s'));
        $sheet->setCellValue('C' . $row_num, $customer_name);
        $sheet->setCellValue('D' . $row_num, $customer_email);
        $sheet->setCellValue('E' . $row_num, wc_format_decimal( $total_amount, 2 ));
        $sheet->setCellValue('F' . $row_num, $payment_method);
        $sheet->setCellValue('G' . $row_num, $order_status);

        $row_num++;
    }

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

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

    // Define the output filename
    $filename = 'sales-report-' . date('Y-m') . '.xlsx';

    // Output the file to the browser
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer->save('php://output');
    exit; // Important to stop further execution
} else {
    echo "No orders found for the specified date range.";
}
?>

Generating Individual Invoices

Creating individual invoices requires iterating through orders and extracting specific line item details, customer information, and company details. We can adapt the PHP-Spreadsheet library to create a more structured invoice format.

For this, we’ll create a separate worksheet for each invoice, or a single worksheet with multiple invoice layouts. Let’s opt for a single worksheet with distinct invoice blocks.

<?php
// Assuming $orders is populated from get_woocommerce_orders_in_date_range()

if ( ! empty( $orders ) ) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Invoices');

    $current_row = 1; // Start at the top

    // Company details (replace with your actual company info)
    $company_name = get_bloginfo('name');
    $company_address = get_option('woocommerce_store_address') . ', ' . get_option('woocommerce_store_city') . ', ' . get_option('woocommerce_store_postcode');
    $company_email = get_option('admin_email'); // Or a specific company email

    foreach ( $orders as $order ) {
        /** @var WC_Order $order */

        // Invoice Header
        $sheet->setCellValue('A' . $current_row, 'INVOICE');
        $sheet->getStyle('A' . $current_row)->getFont()->setSize(18)->setBold(true);
        $current_row++;

        // Company Info
        $sheet->setCellValue('A' . $current_row, $company_name);
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $company_address);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $company_email);
        $current_row += 2; // Add some spacing

        // Invoice Details
        $sheet->setCellValue('F' . $current_row, 'Invoice #:');
        $sheet->setCellValue('G' . $current_row, $order->get_id());
        $current_row++;
        $sheet->setCellValue('F' . $current_row, 'Invoice Date:');
        $sheet->setCellValue('G' . $current_row, $order->get_date_created()->format('Y-m-d'));
        $current_row++;
        $sheet->setCellValue('F' . $current_row, 'Due Date:');
        // Calculate due date (e.g., 30 days from invoice date)
        $due_date = $order->get_date_created()->modify('+30 days');
        $sheet->setCellValue('G' . $current_row, $due_date->format('Y-m-d'));
        $current_row += 2;

        // Customer Info
        $sheet->setCellValue('A' . $current_row, 'Bill To:');
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_formatted_billing_full_name());
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_billing_address_1());
        $current_row++;
        if ( $order->get_billing_address_2() ) {
            $sheet->setCellValue('A' . $current_row, $order->get_billing_address_2());
            $current_row++;
        }
        $sheet->setCellValue('A' . $current_row, $order->get_billing_city() . ', ' . $order->get_billing_postcode());
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_billing_country());
        $current_row += 2;

        // Line Items Header
        $sheet->setCellValue('A' . $current_row, 'Description');
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('D' . $current_row, 'Quantity');
        $sheet->getStyle('D' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('E' . $current_row, 'Unit Price');
        $sheet->getStyle('E' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('F' . $current_row, 'Total');
        $sheet->getStyle('F' . $current_row)->getFont()->setBold(true);
        $current_row++;

        // Line Items
        $line_item_start_row = $current_row;
        foreach ( $order->get_items() as $item_id => $item ) {
            /** @var WC_Order_Item_Product $item */
            $product_name = $item->get_name();
            $quantity = $item->get_quantity();
            $unit_price = wc_get_price_including_tax( $item ) ? $item->get_total() / $quantity : $item->get_subtotal() / $quantity;
            $line_total = $item->get_total();

            $sheet->setCellValue('A' . $current_row, $product_name);
            $sheet->setCellValue('D' . $current_row, $quantity);
            $sheet->setCellValue('E' . $current_row, wc_format_decimal( $unit_price, 2 ));
            $sheet->setCellValue('F' . $current_row, wc_format_decimal( $line_total, 2 ));
            $current_row++;
        }
        $line_item_end_row = $current_row - 1;

        // Apply borders to line items
        $sheet->getStyle('A' . $line_item_start_row . ':F' . $line_item_end_row)->applyFromArray([
            'borders' => [
                'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
            ],
        ]);

        // Totals Section
        $current_row++; // Space before totals
        $sheet->setCellValue('E' . $current_row, 'Subtotal:');
        $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_subtotal(), 2 ));
        $current_row++;

        if ( $order->get_total_discount() > 0 ) {
            $sheet->setCellValue('E' . $current_row, 'Discount:');
            $sheet->setCellValue('F' . $current_row, '-' . wc_format_decimal( $order->get_total_discount(), 2 ));
            $current_row++;
        }

        // Taxes
        $taxes = $order->get_taxes();
        if ( ! empty( $taxes ) ) {
            foreach ( $taxes as $tax_rate_id => $tax_amount ) {
                $tax_rate = new WC_Tax();
                $tax_label = $tax_rate->get_rate_label( $tax_rate_id );
                $sheet->setCellValue('E' . $current_row, $tax_label . ':');
                $sheet->setCellValue('F' . $current_row, wc_format_decimal( $tax_amount, 2 ));
                $current_row++;
            }
        }

        // Shipping
        if ( $order->get_total_shipping() > 0 ) {
            $sheet->setCellValue('E' . $current_row, 'Shipping:');
            $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_total_shipping(), 2 ));
            $current_row++;
        }

        // Grand Total
        $sheet->setCellValue('E' . $current_row, 'Total:');
        $sheet->getStyle('E' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_total(), 2 ));
        $sheet->getStyle('F' . $current_row)->getFont()->setBold(true);
        $current_row++;

        // Add a separator for the next invoice
        $current_row += 3;
    }

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

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

    // Define the output filename
    $filename = 'invoices-' . date('Y-m-d') . '.xlsx';

    // Output the file to the browser
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer->save('php://output');
    exit;
} else {
    echo "No orders found to generate invoices.";
}
?>

Converting XLSX to PDF

PHP-Spreadsheet itself does not directly export to PDF. However, it can generate HTML, which can then be converted to PDF using libraries like mPDF or TCPDF. Alternatively, for server-side conversion, you might consider headless browser solutions like Puppeteer (Node.js) or command-line tools like LibreOffice in headless mode.

Option 1: Using mPDF (PHP Library)

First, install mPDF:

composer require mpdf/mpdf

Then, after generating the XLSX, you can generate HTML from the spreadsheet and pass it to mPDF. This requires a bit more work to correctly map spreadsheet cells to HTML tables.

<?php
// ... (after generating $spreadsheet object)

// Create an HTML writer
$htmlWriter = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
$htmlContent = $htmlWriter->generateSheetData($sheet); // Generates HTML for the active sheet

// Initialize mPDF
$mpdf = new \Mpdf\Mpdf();

// Write the HTML content to mPDF
$mpdf->WriteHTML($htmlContent);

// Output the PDF
$filename = 'report.pdf';
$mpdf->Output($filename, \Mpdf\Output\Destination::INLINE); // Or DESTINATION_DOWNLOAD
exit;
?>

Option 2: Using LibreOffice Headless (Command Line)

This method requires LibreOffice to be installed on your server. You can execute a command from PHP using `shell_exec` or `exec`.

# Assuming your XLSX file is saved as 'report.xlsx'
# And you want to convert it to 'report.pdf'

# On Linux/macOS
/usr/bin/soffice --headless --convert-to pdf --outdir /path/to/output/directory /path/to/your/report.xlsx

# Or directly from PHP
$xlsxFilePath = '/path/to/your/report.xlsx';
$outputDir = '/path/to/output/directory';
$command = "/usr/bin/soffice --headless --convert-to pdf --outdir " . escapeshellarg($outputDir) . " " . escapeshellarg($xlsxFilePath);
shell_exec($command);
?>

This approach is often more reliable for complex formatting but adds a dependency on server software. Ensure the web server user has execute permissions for LibreOffice and write permissions for the output directory.

Implementation Strategies in WordPress

You can integrate this functionality into WordPress in several ways:

• Custom Plugin: The cleanest approach. Create a dedicated plugin that adds a new admin menu item (e.g., “Custom Reports”). This menu item would lead to a page with forms for date selection or order filtering, triggering the report generation.
• Theme Functions (`functions.php`): For simpler needs or theme-specific reports, you can add the code to your theme’s `functions.php` file. However, this couples the functionality tightly to the theme and is not recommended for complex or critical features.
• Admin AJAX: For a more interactive user experience, you can trigger the report generation via AJAX calls from an admin page. This allows for background processing and avoids full page reloads.

When implementing in WordPress, remember to hook into appropriate actions and filters, sanitize user inputs, and handle potential errors gracefully. For security, ensure that report generation is restricted to users with appropriate roles (e.g., administrators, shop managers).

Advanced Considerations

Performance: For very large datasets (thousands of orders), generating reports directly in the browser can lead to timeouts. Consider implementing background processing using WP-Cron or a dedicated job queue system.

Customization: PHP-Spreadsheet offers extensive styling options, charting capabilities, and data validation. You can tailor reports to include complex calculations, pivot tables, or visual representations of data.

Error Handling: Implement robust error handling for database queries, file operations, and external process calls (like LibreOffice). Log errors for debugging.

Security: Always sanitize and validate any user-provided input (like date ranges) before using it in queries or commands. Restrict access to report generation functions to authorized users.

<?php
/**
 * Fetches WooCommerce orders within a date range.
 *
 * @param string $start_date Start date in 'YYYY-MM-DD' format.
 * @param string $end_date End date in 'YYYY-MM-DD' format.
 * @return WC_Order[] Array of WC_Order objects.
 */
function get_woocommerce_orders_in_date_range( $start_date, $end_date ) {
    $args = array(
        'status'   => array( 'wc-processing', 'wc-completed' ), // Example statuses
        'date_query' => array(
            array(
                'after'     => $start_date,
                'before'    => $end_date,
                'inclusive' => true,
            ),
        ),
        'limit'    => -1, // Get all matching orders
    );

    return wc_get_orders( $args );
}

// Example usage:
$start_date = '2023-10-01';
$end_date   = '2023-10-31';
$orders     = get_woocommerce_orders_in_date_range( $start_date, $end_date );
?>

Generating an XLSX Sales Report

Now, let’s use PHP-Spreadsheet to create an XLSX file from the fetched order data. This involves instantiating a `Spreadsheet` object, adding worksheets, populating cells, and then writing the file.

<?php
// Assuming $orders is populated from the previous step
if ( ! empty( $orders ) ) {
    // Create a new Spreadsheet object
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Monthly Sales Report');

    // Add headers
    $headers = array(
        'Order ID',
        'Order Date',
        'Customer Name',
        'Customer Email',
        'Total Amount',
        'Payment Method',
        'Order Status',
    );
    $sheet->fromArray( $headers, NULL, 'A1' );

    // Apply header styling (optional)
    $headerStyle = [
        'font' => ['bold' => true],
        'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFE0E0E0']],
    ];
    $sheet->getStyle('A1:' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($headers)) . '1')->applyFromArray($headerStyle);

    // Populate data rows
    $row_num = 2; // Start from the second row
    foreach ( $orders as $order ) {
        /** @var WC_Order $order */
        $customer_name = $order->get_formatted_billing_full_name();
        $customer_email = $order->get_billing_email();
        $total_amount = $order->get_total();
        $payment_method = $order->get_payment_method_title();
        $order_status = wc_get_order_status_name( $order->get_status() );

        $sheet->setCellValue('A' . $row_num, $order->get_id());
        $sheet->setCellValue('B' . $row_num, $order->get_date_created()->format('Y-m-d H:i:s'));
        $sheet->setCellValue('C' . $row_num, $customer_name);
        $sheet->setCellValue('D' . $row_num, $customer_email);
        $sheet->setCellValue('E' . $row_num, wc_format_decimal( $total_amount, 2 ));
        $sheet->setCellValue('F' . $row_num, $payment_method);
        $sheet->setCellValue('G' . $row_num, $order_status);

        $row_num++;
    }

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

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

    // Define the output filename
    $filename = 'sales-report-' . date('Y-m') . '.xlsx';

    // Output the file to the browser
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer->save('php://output');
    exit; // Important to stop further execution
} else {
    echo "No orders found for the specified date range.";
}
?>

Generating Individual Invoices

Creating individual invoices requires iterating through orders and extracting specific line item details, customer information, and company details. We can adapt the PHP-Spreadsheet library to create a more structured invoice format.

For this, we’ll create a separate worksheet for each invoice, or a single worksheet with multiple invoice layouts. Let’s opt for a single worksheet with distinct invoice blocks.

<?php
// Assuming $orders is populated from get_woocommerce_orders_in_date_range()

if ( ! empty( $orders ) ) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Invoices');

    $current_row = 1; // Start at the top

    // Company details (replace with your actual company info)
    $company_name = get_bloginfo('name');
    $company_address = get_option('woocommerce_store_address') . ', ' . get_option('woocommerce_store_city') . ', ' . get_option('woocommerce_store_postcode');
    $company_email = get_option('admin_email'); // Or a specific company email

    foreach ( $orders as $order ) {
        /** @var WC_Order $order */

        // Invoice Header
        $sheet->setCellValue('A' . $current_row, 'INVOICE');
        $sheet->getStyle('A' . $current_row)->getFont()->setSize(18)->setBold(true);
        $current_row++;

        // Company Info
        $sheet->setCellValue('A' . $current_row, $company_name);
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $company_address);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $company_email);
        $current_row += 2; // Add some spacing

        // Invoice Details
        $sheet->setCellValue('F' . $current_row, 'Invoice #:');
        $sheet->setCellValue('G' . $current_row, $order->get_id());
        $current_row++;
        $sheet->setCellValue('F' . $current_row, 'Invoice Date:');
        $sheet->setCellValue('G' . $current_row, $order->get_date_created()->format('Y-m-d'));
        $current_row++;
        $sheet->setCellValue('F' . $current_row, 'Due Date:');
        // Calculate due date (e.g., 30 days from invoice date)
        $due_date = $order->get_date_created()->modify('+30 days');
        $sheet->setCellValue('G' . $current_row, $due_date->format('Y-m-d'));
        $current_row += 2;

        // Customer Info
        $sheet->setCellValue('A' . $current_row, 'Bill To:');
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_formatted_billing_full_name());
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_billing_address_1());
        $current_row++;
        if ( $order->get_billing_address_2() ) {
            $sheet->setCellValue('A' . $current_row, $order->get_billing_address_2());
            $current_row++;
        }
        $sheet->setCellValue('A' . $current_row, $order->get_billing_city() . ', ' . $order->get_billing_postcode());
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_billing_country());
        $current_row += 2;

        // Line Items Header
        $sheet->setCellValue('A' . $current_row, 'Description');
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('D' . $current_row, 'Quantity');
        $sheet->getStyle('D' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('E' . $current_row, 'Unit Price');
        $sheet->getStyle('E' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('F' . $current_row, 'Total');
        $sheet->getStyle('F' . $current_row)->getFont()->setBold(true);
        $current_row++;

        // Line Items
        $line_item_start_row = $current_row;
        foreach ( $order->get_items() as $item_id => $item ) {
            /** @var WC_Order_Item_Product $item */
            $product_name = $item->get_name();
            $quantity = $item->get_quantity();
            $unit_price = wc_get_price_including_tax( $item ) ? $item->get_total() / $quantity : $item->get_subtotal() / $quantity;
            $line_total = $item->get_total();

            $sheet->setCellValue('A' . $current_row, $product_name);
            $sheet->setCellValue('D' . $current_row, $quantity);
            $sheet->setCellValue('E' . $current_row, wc_format_decimal( $unit_price, 2 ));
            $sheet->setCellValue('F' . $current_row, wc_format_decimal( $line_total, 2 ));
            $current_row++;
        }
        $line_item_end_row = $current_row - 1;

        // Apply borders to line items
        $sheet->getStyle('A' . $line_item_start_row . ':F' . $line_item_end_row)->applyFromArray([
            'borders' => [
                'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
            ],
        ]);

        // Totals Section
        $current_row++; // Space before totals
        $sheet->setCellValue('E' . $current_row, 'Subtotal:');
        $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_subtotal(), 2 ));
        $current_row++;

        if ( $order->get_total_discount() > 0 ) {
            $sheet->setCellValue('E' . $current_row, 'Discount:');
            $sheet->setCellValue('F' . $current_row, '-' . wc_format_decimal( $order->get_total_discount(), 2 ));
            $current_row++;
        }

        // Taxes
        $taxes = $order->get_taxes();
        if ( ! empty( $taxes ) ) {
            foreach ( $taxes as $tax_rate_id => $tax_amount ) {
                $tax_rate = new WC_Tax();
                $tax_label = $tax_rate->get_rate_label( $tax_rate_id );
                $sheet->setCellValue('E' . $current_row, $tax_label . ':');
                $sheet->setCellValue('F' . $current_row, wc_format_decimal( $tax_amount, 2 ));
                $current_row++;
            }
        }

        // Shipping
        if ( $order->get_total_shipping() > 0 ) {
            $sheet->setCellValue('E' . $current_row, 'Shipping:');
            $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_total_shipping(), 2 ));
            $current_row++;
        }

        // Grand Total
        $sheet->setCellValue('E' . $current_row, 'Total:');
        $sheet->getStyle('E' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_total(), 2 ));
        $sheet->getStyle('F' . $current_row)->getFont()->setBold(true);
        $current_row++;

        // Add a separator for the next invoice
        $current_row += 3;
    }

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

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

    // Define the output filename
    $filename = 'invoices-' . date('Y-m-d') . '.xlsx';

    // Output the file to the browser
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer->save('php://output');
    exit;
} else {
    echo "No orders found to generate invoices.";
}
?>

Converting XLSX to PDF

PHP-Spreadsheet itself does not directly export to PDF. However, it can generate HTML, which can then be converted to PDF using libraries like mPDF or TCPDF. Alternatively, for server-side conversion, you might consider headless browser solutions like Puppeteer (Node.js) or command-line tools like LibreOffice in headless mode.

Option 1: Using mPDF (PHP Library)

First, install mPDF:

composer require mpdf/mpdf

Then, after generating the XLSX, you can generate HTML from the spreadsheet and pass it to mPDF. This requires a bit more work to correctly map spreadsheet cells to HTML tables.

<?php
// ... (after generating $spreadsheet object)

// Create an HTML writer
$htmlWriter = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
$htmlContent = $htmlWriter->generateSheetData($sheet); // Generates HTML for the active sheet

// Initialize mPDF
$mpdf = new \Mpdf\Mpdf();

// Write the HTML content to mPDF
$mpdf->WriteHTML($htmlContent);

// Output the PDF
$filename = 'report.pdf';
$mpdf->Output($filename, \Mpdf\Output\Destination::INLINE); // Or DESTINATION_DOWNLOAD
exit;
?>

Option 2: Using LibreOffice Headless (Command Line)

This method requires LibreOffice to be installed on your server. You can execute a command from PHP using `shell_exec` or `exec`.

# Assuming your XLSX file is saved as 'report.xlsx'
# And you want to convert it to 'report.pdf'

# On Linux/macOS
/usr/bin/soffice --headless --convert-to pdf --outdir /path/to/output/directory /path/to/your/report.xlsx

# Or directly from PHP
$xlsxFilePath = '/path/to/your/report.xlsx';
$outputDir = '/path/to/output/directory';
$command = "/usr/bin/soffice --headless --convert-to pdf --outdir " . escapeshellarg($outputDir) . " " . escapeshellarg($xlsxFilePath);
shell_exec($command);
?>

This approach is often more reliable for complex formatting but adds a dependency on server software. Ensure the web server user has execute permissions for LibreOffice and write permissions for the output directory.

Implementation Strategies in WordPress

You can integrate this functionality into WordPress in several ways:

• Custom Plugin: The cleanest approach. Create a dedicated plugin that adds a new admin menu item (e.g., “Custom Reports”). This menu item would lead to a page with forms for date selection or order filtering, triggering the report generation.
• Theme Functions (`functions.php`): For simpler needs or theme-specific reports, you can add the code to your theme’s `functions.php` file. However, this couples the functionality tightly to the theme and is not recommended for complex or critical features.
• Admin AJAX: For a more interactive user experience, you can trigger the report generation via AJAX calls from an admin page. This allows for background processing and avoids full page reloads.

When implementing in WordPress, remember to hook into appropriate actions and filters, sanitize user inputs, and handle potential errors gracefully. For security, ensure that report generation is restricted to users with appropriate roles (e.g., administrators, shop managers).

Advanced Considerations

Performance: For very large datasets (thousands of orders), generating reports directly in the browser can lead to timeouts. Consider implementing background processing using WP-Cron or a dedicated job queue system.

Customization: PHP-Spreadsheet offers extensive styling options, charting capabilities, and data validation. You can tailor reports to include complex calculations, pivot tables, or visual representations of data.

Error Handling: Implement robust error handling for database queries, file operations, and external process calls (like LibreOffice). Log errors for debugging.

Security: Always sanitize and validate any user-provided input (like date ranges) before using it in queries or commands. Restrict access to report generation functions to authorized users.

<?php
// In your custom plugin or theme's main file
require_once __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice.PhpSpreadsheet\Writer\Xlsx;
// ... other necessary use statements
?>

Accessing WooCommerce Order Data

WooCommerce stores order data in the WordPress database, primarily in the `wp_posts` and `wp_postmeta` tables. We can query this data using WordPress’s built-in functions or directly with SQL. For this example, we’ll focus on retrieving orders within a specific date range.

A common scenario is generating a sales report for a given month. We can use `wc_get_orders` for this purpose, which is a more robust and WooCommerce-aware method than direct database queries for posts.

<?php
/**
 * Fetches WooCommerce orders within a date range.
 *
 * @param string $start_date Start date in 'YYYY-MM-DD' format.
 * @param string $end_date End date in 'YYYY-MM-DD' format.
 * @return WC_Order[] Array of WC_Order objects.
 */
function get_woocommerce_orders_in_date_range( $start_date, $end_date ) {
    $args = array(
        'status'   => array( 'wc-processing', 'wc-completed' ), // Example statuses
        'date_query' => array(
            array(
                'after'     => $start_date,
                'before'    => $end_date,
                'inclusive' => true,
            ),
        ),
        'limit'    => -1, // Get all matching orders
    );

    return wc_get_orders( $args );
}

// Example usage:
$start_date = '2023-10-01';
$end_date   = '2023-10-31';
$orders     = get_woocommerce_orders_in_date_range( $start_date, $end_date );
?>

Generating an XLSX Sales Report

Now, let’s use PHP-Spreadsheet to create an XLSX file from the fetched order data. This involves instantiating a `Spreadsheet` object, adding worksheets, populating cells, and then writing the file.

<?php
// Assuming $orders is populated from the previous step
if ( ! empty( $orders ) ) {
    // Create a new Spreadsheet object
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Monthly Sales Report');

    // Add headers
    $headers = array(
        'Order ID',
        'Order Date',
        'Customer Name',
        'Customer Email',
        'Total Amount',
        'Payment Method',
        'Order Status',
    );
    $sheet->fromArray( $headers, NULL, 'A1' );

    // Apply header styling (optional)
    $headerStyle = [
        'font' => ['bold' => true],
        'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFE0E0E0']],
    ];
    $sheet->getStyle('A1:' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($headers)) . '1')->applyFromArray($headerStyle);

    // Populate data rows
    $row_num = 2; // Start from the second row
    foreach ( $orders as $order ) {
        /** @var WC_Order $order */
        $customer_name = $order->get_formatted_billing_full_name();
        $customer_email = $order->get_billing_email();
        $total_amount = $order->get_total();
        $payment_method = $order->get_payment_method_title();
        $order_status = wc_get_order_status_name( $order->get_status() );

        $sheet->setCellValue('A' . $row_num, $order->get_id());
        $sheet->setCellValue('B' . $row_num, $order->get_date_created()->format('Y-m-d H:i:s'));
        $sheet->setCellValue('C' . $row_num, $customer_name);
        $sheet->setCellValue('D' . $row_num, $customer_email);
        $sheet->setCellValue('E' . $row_num, wc_format_decimal( $total_amount, 2 ));
        $sheet->setCellValue('F' . $row_num, $payment_method);
        $sheet->setCellValue('G' . $row_num, $order_status);

        $row_num++;
    }

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

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

    // Define the output filename
    $filename = 'sales-report-' . date('Y-m') . '.xlsx';

    // Output the file to the browser
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer->save('php://output');
    exit; // Important to stop further execution
} else {
    echo "No orders found for the specified date range.";
}
?>

Generating Individual Invoices

Creating individual invoices requires iterating through orders and extracting specific line item details, customer information, and company details. We can adapt the PHP-Spreadsheet library to create a more structured invoice format.

For this, we’ll create a separate worksheet for each invoice, or a single worksheet with multiple invoice layouts. Let’s opt for a single worksheet with distinct invoice blocks.

<?php
// Assuming $orders is populated from get_woocommerce_orders_in_date_range()

if ( ! empty( $orders ) ) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Invoices');

    $current_row = 1; // Start at the top

    // Company details (replace with your actual company info)
    $company_name = get_bloginfo('name');
    $company_address = get_option('woocommerce_store_address') . ', ' . get_option('woocommerce_store_city') . ', ' . get_option('woocommerce_store_postcode');
    $company_email = get_option('admin_email'); // Or a specific company email

    foreach ( $orders as $order ) {
        /** @var WC_Order $order */

        // Invoice Header
        $sheet->setCellValue('A' . $current_row, 'INVOICE');
        $sheet->getStyle('A' . $current_row)->getFont()->setSize(18)->setBold(true);
        $current_row++;

        // Company Info
        $sheet->setCellValue('A' . $current_row, $company_name);
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $company_address);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $company_email);
        $current_row += 2; // Add some spacing

        // Invoice Details
        $sheet->setCellValue('F' . $current_row, 'Invoice #:');
        $sheet->setCellValue('G' . $current_row, $order->get_id());
        $current_row++;
        $sheet->setCellValue('F' . $current_row, 'Invoice Date:');
        $sheet->setCellValue('G' . $current_row, $order->get_date_created()->format('Y-m-d'));
        $current_row++;
        $sheet->setCellValue('F' . $current_row, 'Due Date:');
        // Calculate due date (e.g., 30 days from invoice date)
        $due_date = $order->get_date_created()->modify('+30 days');
        $sheet->setCellValue('G' . $current_row, $due_date->format('Y-m-d'));
        $current_row += 2;

        // Customer Info
        $sheet->setCellValue('A' . $current_row, 'Bill To:');
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_formatted_billing_full_name());
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_billing_address_1());
        $current_row++;
        if ( $order->get_billing_address_2() ) {
            $sheet->setCellValue('A' . $current_row, $order->get_billing_address_2());
            $current_row++;
        }
        $sheet->setCellValue('A' . $current_row, $order->get_billing_city() . ', ' . $order->get_billing_postcode());
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_billing_country());
        $current_row += 2;

        // Line Items Header
        $sheet->setCellValue('A' . $current_row, 'Description');
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('D' . $current_row, 'Quantity');
        $sheet->getStyle('D' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('E' . $current_row, 'Unit Price');
        $sheet->getStyle('E' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('F' . $current_row, 'Total');
        $sheet->getStyle('F' . $current_row)->getFont()->setBold(true);
        $current_row++;

        // Line Items
        $line_item_start_row = $current_row;
        foreach ( $order->get_items() as $item_id => $item ) {
            /** @var WC_Order_Item_Product $item */
            $product_name = $item->get_name();
            $quantity = $item->get_quantity();
            $unit_price = wc_get_price_including_tax( $item ) ? $item->get_total() / $quantity : $item->get_subtotal() / $quantity;
            $line_total = $item->get_total();

            $sheet->setCellValue('A' . $current_row, $product_name);
            $sheet->setCellValue('D' . $current_row, $quantity);
            $sheet->setCellValue('E' . $current_row, wc_format_decimal( $unit_price, 2 ));
            $sheet->setCellValue('F' . $current_row, wc_format_decimal( $line_total, 2 ));
            $current_row++;
        }
        $line_item_end_row = $current_row - 1;

        // Apply borders to line items
        $sheet->getStyle('A' . $line_item_start_row . ':F' . $line_item_end_row)->applyFromArray([
            'borders' => [
                'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
            ],
        ]);

        // Totals Section
        $current_row++; // Space before totals
        $sheet->setCellValue('E' . $current_row, 'Subtotal:');
        $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_subtotal(), 2 ));
        $current_row++;

        if ( $order->get_total_discount() > 0 ) {
            $sheet->setCellValue('E' . $current_row, 'Discount:');
            $sheet->setCellValue('F' . $current_row, '-' . wc_format_decimal( $order->get_total_discount(), 2 ));
            $current_row++;
        }

        // Taxes
        $taxes = $order->get_taxes();
        if ( ! empty( $taxes ) ) {
            foreach ( $taxes as $tax_rate_id => $tax_amount ) {
                $tax_rate = new WC_Tax();
                $tax_label = $tax_rate->get_rate_label( $tax_rate_id );
                $sheet->setCellValue('E' . $current_row, $tax_label . ':');
                $sheet->setCellValue('F' . $current_row, wc_format_decimal( $tax_amount, 2 ));
                $current_row++;
            }
        }

        // Shipping
        if ( $order->get_total_shipping() > 0 ) {
            $sheet->setCellValue('E' . $current_row, 'Shipping:');
            $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_total_shipping(), 2 ));
            $current_row++;
        }

        // Grand Total
        $sheet->setCellValue('E' . $current_row, 'Total:');
        $sheet->getStyle('E' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_total(), 2 ));
        $sheet->getStyle('F' . $current_row)->getFont()->setBold(true);
        $current_row++;

        // Add a separator for the next invoice
        $current_row += 3;
    }

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

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

    // Define the output filename
    $filename = 'invoices-' . date('Y-m-d') . '.xlsx';

    // Output the file to the browser
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer->save('php://output');
    exit;
} else {
    echo "No orders found to generate invoices.";
}
?>

Converting XLSX to PDF

PHP-Spreadsheet itself does not directly export to PDF. However, it can generate HTML, which can then be converted to PDF using libraries like mPDF or TCPDF. Alternatively, for server-side conversion, you might consider headless browser solutions like Puppeteer (Node.js) or command-line tools like LibreOffice in headless mode.

Option 1: Using mPDF (PHP Library)

First, install mPDF:

composer require mpdf/mpdf

Then, after generating the XLSX, you can generate HTML from the spreadsheet and pass it to mPDF. This requires a bit more work to correctly map spreadsheet cells to HTML tables.

<?php
// ... (after generating $spreadsheet object)

// Create an HTML writer
$htmlWriter = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
$htmlContent = $htmlWriter->generateSheetData($sheet); // Generates HTML for the active sheet

// Initialize mPDF
$mpdf = new \Mpdf\Mpdf();

// Write the HTML content to mPDF
$mpdf->WriteHTML($htmlContent);

// Output the PDF
$filename = 'report.pdf';
$mpdf->Output($filename, \Mpdf\Output\Destination::INLINE); // Or DESTINATION_DOWNLOAD
exit;
?>

Option 2: Using LibreOffice Headless (Command Line)

This method requires LibreOffice to be installed on your server. You can execute a command from PHP using `shell_exec` or `exec`.

# Assuming your XLSX file is saved as 'report.xlsx'
# And you want to convert it to 'report.pdf'

# On Linux/macOS
/usr/bin/soffice --headless --convert-to pdf --outdir /path/to/output/directory /path/to/your/report.xlsx

# Or directly from PHP
$xlsxFilePath = '/path/to/your/report.xlsx';
$outputDir = '/path/to/output/directory';
$command = "/usr/bin/soffice --headless --convert-to pdf --outdir " . escapeshellarg($outputDir) . " " . escapeshellarg($xlsxFilePath);
shell_exec($command);
?>

This approach is often more reliable for complex formatting but adds a dependency on server software. Ensure the web server user has execute permissions for LibreOffice and write permissions for the output directory.

Implementation Strategies in WordPress

You can integrate this functionality into WordPress in several ways:

• Custom Plugin: The cleanest approach. Create a dedicated plugin that adds a new admin menu item (e.g., “Custom Reports”). This menu item would lead to a page with forms for date selection or order filtering, triggering the report generation.
• Theme Functions (`functions.php`): For simpler needs or theme-specific reports, you can add the code to your theme’s `functions.php` file. However, this couples the functionality tightly to the theme and is not recommended for complex or critical features.
• Admin AJAX: For a more interactive user experience, you can trigger the report generation via AJAX calls from an admin page. This allows for background processing and avoids full page reloads.

When implementing in WordPress, remember to hook into appropriate actions and filters, sanitize user inputs, and handle potential errors gracefully. For security, ensure that report generation is restricted to users with appropriate roles (e.g., administrators, shop managers).

Advanced Considerations

Performance: For very large datasets (thousands of orders), generating reports directly in the browser can lead to timeouts. Consider implementing background processing using WP-Cron or a dedicated job queue system.

Customization: PHP-Spreadsheet offers extensive styling options, charting capabilities, and data validation. You can tailor reports to include complex calculations, pivot tables, or visual representations of data.

Error Handling: Implement robust error handling for database queries, file operations, and external process calls (like LibreOffice). Log errors for debugging.

Security: Always sanitize and validate any user-provided input (like date ranges) before using it in queries or commands. Restrict access to report generation functions to authorized users.

composer require phpoffice/phpspreadsheet

This command will download the PHP-Spreadsheet library and its dependencies into a `vendor` directory. You’ll then need to include Composer’s autoloader in your PHP scripts:

<?php
// In your custom plugin or theme's main file
require_once __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice.PhpSpreadsheet\Writer\Xlsx;
// ... other necessary use statements
?>

Accessing WooCommerce Order Data

WooCommerce stores order data in the WordPress database, primarily in the `wp_posts` and `wp_postmeta` tables. We can query this data using WordPress’s built-in functions or directly with SQL. For this example, we’ll focus on retrieving orders within a specific date range.

A common scenario is generating a sales report for a given month. We can use `wc_get_orders` for this purpose, which is a more robust and WooCommerce-aware method than direct database queries for posts.

<?php
/**
 * Fetches WooCommerce orders within a date range.
 *
 * @param string $start_date Start date in 'YYYY-MM-DD' format.
 * @param string $end_date End date in 'YYYY-MM-DD' format.
 * @return WC_Order[] Array of WC_Order objects.
 */
function get_woocommerce_orders_in_date_range( $start_date, $end_date ) {
    $args = array(
        'status'   => array( 'wc-processing', 'wc-completed' ), // Example statuses
        'date_query' => array(
            array(
                'after'     => $start_date,
                'before'    => $end_date,
                'inclusive' => true,
            ),
        ),
        'limit'    => -1, // Get all matching orders
    );

    return wc_get_orders( $args );
}

// Example usage:
$start_date = '2023-10-01';
$end_date   = '2023-10-31';
$orders     = get_woocommerce_orders_in_date_range( $start_date, $end_date );
?>

Generating an XLSX Sales Report

Now, let’s use PHP-Spreadsheet to create an XLSX file from the fetched order data. This involves instantiating a `Spreadsheet` object, adding worksheets, populating cells, and then writing the file.

<?php
// Assuming $orders is populated from the previous step
if ( ! empty( $orders ) ) {
    // Create a new Spreadsheet object
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Monthly Sales Report');

    // Add headers
    $headers = array(
        'Order ID',
        'Order Date',
        'Customer Name',
        'Customer Email',
        'Total Amount',
        'Payment Method',
        'Order Status',
    );
    $sheet->fromArray( $headers, NULL, 'A1' );

    // Apply header styling (optional)
    $headerStyle = [
        'font' => ['bold' => true],
        'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFE0E0E0']],
    ];
    $sheet->getStyle('A1:' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($headers)) . '1')->applyFromArray($headerStyle);

    // Populate data rows
    $row_num = 2; // Start from the second row
    foreach ( $orders as $order ) {
        /** @var WC_Order $order */
        $customer_name = $order->get_formatted_billing_full_name();
        $customer_email = $order->get_billing_email();
        $total_amount = $order->get_total();
        $payment_method = $order->get_payment_method_title();
        $order_status = wc_get_order_status_name( $order->get_status() );

        $sheet->setCellValue('A' . $row_num, $order->get_id());
        $sheet->setCellValue('B' . $row_num, $order->get_date_created()->format('Y-m-d H:i:s'));
        $sheet->setCellValue('C' . $row_num, $customer_name);
        $sheet->setCellValue('D' . $row_num, $customer_email);
        $sheet->setCellValue('E' . $row_num, wc_format_decimal( $total_amount, 2 ));
        $sheet->setCellValue('F' . $row_num, $payment_method);
        $sheet->setCellValue('G' . $row_num, $order_status);

        $row_num++;
    }

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

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

    // Define the output filename
    $filename = 'sales-report-' . date('Y-m') . '.xlsx';

    // Output the file to the browser
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer->save('php://output');
    exit; // Important to stop further execution
} else {
    echo "No orders found for the specified date range.";
}
?>

Generating Individual Invoices

Creating individual invoices requires iterating through orders and extracting specific line item details, customer information, and company details. We can adapt the PHP-Spreadsheet library to create a more structured invoice format.

For this, we’ll create a separate worksheet for each invoice, or a single worksheet with multiple invoice layouts. Let’s opt for a single worksheet with distinct invoice blocks.

<?php
// Assuming $orders is populated from get_woocommerce_orders_in_date_range()

if ( ! empty( $orders ) ) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Invoices');

    $current_row = 1; // Start at the top

    // Company details (replace with your actual company info)
    $company_name = get_bloginfo('name');
    $company_address = get_option('woocommerce_store_address') . ', ' . get_option('woocommerce_store_city') . ', ' . get_option('woocommerce_store_postcode');
    $company_email = get_option('admin_email'); // Or a specific company email

    foreach ( $orders as $order ) {
        /** @var WC_Order $order */

        // Invoice Header
        $sheet->setCellValue('A' . $current_row, 'INVOICE');
        $sheet->getStyle('A' . $current_row)->getFont()->setSize(18)->setBold(true);
        $current_row++;

        // Company Info
        $sheet->setCellValue('A' . $current_row, $company_name);
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $company_address);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $company_email);
        $current_row += 2; // Add some spacing

        // Invoice Details
        $sheet->setCellValue('F' . $current_row, 'Invoice #:');
        $sheet->setCellValue('G' . $current_row, $order->get_id());
        $current_row++;
        $sheet->setCellValue('F' . $current_row, 'Invoice Date:');
        $sheet->setCellValue('G' . $current_row, $order->get_date_created()->format('Y-m-d'));
        $current_row++;
        $sheet->setCellValue('F' . $current_row, 'Due Date:');
        // Calculate due date (e.g., 30 days from invoice date)
        $due_date = $order->get_date_created()->modify('+30 days');
        $sheet->setCellValue('G' . $current_row, $due_date->format('Y-m-d'));
        $current_row += 2;

        // Customer Info
        $sheet->setCellValue('A' . $current_row, 'Bill To:');
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_formatted_billing_full_name());
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_billing_address_1());
        $current_row++;
        if ( $order->get_billing_address_2() ) {
            $sheet->setCellValue('A' . $current_row, $order->get_billing_address_2());
            $current_row++;
        }
        $sheet->setCellValue('A' . $current_row, $order->get_billing_city() . ', ' . $order->get_billing_postcode());
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_billing_country());
        $current_row += 2;

        // Line Items Header
        $sheet->setCellValue('A' . $current_row, 'Description');
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('D' . $current_row, 'Quantity');
        $sheet->getStyle('D' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('E' . $current_row, 'Unit Price');
        $sheet->getStyle('E' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('F' . $current_row, 'Total');
        $sheet->getStyle('F' . $current_row)->getFont()->setBold(true);
        $current_row++;

        // Line Items
        $line_item_start_row = $current_row;
        foreach ( $order->get_items() as $item_id => $item ) {
            /** @var WC_Order_Item_Product $item */
            $product_name = $item->get_name();
            $quantity = $item->get_quantity();
            $unit_price = wc_get_price_including_tax( $item ) ? $item->get_total() / $quantity : $item->get_subtotal() / $quantity;
            $line_total = $item->get_total();

            $sheet->setCellValue('A' . $current_row, $product_name);
            $sheet->setCellValue('D' . $current_row, $quantity);
            $sheet->setCellValue('E' . $current_row, wc_format_decimal( $unit_price, 2 ));
            $sheet->setCellValue('F' . $current_row, wc_format_decimal( $line_total, 2 ));
            $current_row++;
        }
        $line_item_end_row = $current_row - 1;

        // Apply borders to line items
        $sheet->getStyle('A' . $line_item_start_row . ':F' . $line_item_end_row)->applyFromArray([
            'borders' => [
                'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
            ],
        ]);

        // Totals Section
        $current_row++; // Space before totals
        $sheet->setCellValue('E' . $current_row, 'Subtotal:');
        $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_subtotal(), 2 ));
        $current_row++;

        if ( $order->get_total_discount() > 0 ) {
            $sheet->setCellValue('E' . $current_row, 'Discount:');
            $sheet->setCellValue('F' . $current_row, '-' . wc_format_decimal( $order->get_total_discount(), 2 ));
            $current_row++;
        }

        // Taxes
        $taxes = $order->get_taxes();
        if ( ! empty( $taxes ) ) {
            foreach ( $taxes as $tax_rate_id => $tax_amount ) {
                $tax_rate = new WC_Tax();
                $tax_label = $tax_rate->get_rate_label( $tax_rate_id );
                $sheet->setCellValue('E' . $current_row, $tax_label . ':');
                $sheet->setCellValue('F' . $current_row, wc_format_decimal( $tax_amount, 2 ));
                $current_row++;
            }
        }

        // Shipping
        if ( $order->get_total_shipping() > 0 ) {
            $sheet->setCellValue('E' . $current_row, 'Shipping:');
            $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_total_shipping(), 2 ));
            $current_row++;
        }

        // Grand Total
        $sheet->setCellValue('E' . $current_row, 'Total:');
        $sheet->getStyle('E' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_total(), 2 ));
        $sheet->getStyle('F' . $current_row)->getFont()->setBold(true);
        $current_row++;

        // Add a separator for the next invoice
        $current_row += 3;
    }

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

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

    // Define the output filename
    $filename = 'invoices-' . date('Y-m-d') . '.xlsx';

    // Output the file to the browser
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer->save('php://output');
    exit;
} else {
    echo "No orders found to generate invoices.";
}
?>

Converting XLSX to PDF

PHP-Spreadsheet itself does not directly export to PDF. However, it can generate HTML, which can then be converted to PDF using libraries like mPDF or TCPDF. Alternatively, for server-side conversion, you might consider headless browser solutions like Puppeteer (Node.js) or command-line tools like LibreOffice in headless mode.

Option 1: Using mPDF (PHP Library)

First, install mPDF:

composer require mpdf/mpdf

Then, after generating the XLSX, you can generate HTML from the spreadsheet and pass it to mPDF. This requires a bit more work to correctly map spreadsheet cells to HTML tables.

<?php
// ... (after generating $spreadsheet object)

// Create an HTML writer
$htmlWriter = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
$htmlContent = $htmlWriter->generateSheetData($sheet); // Generates HTML for the active sheet

// Initialize mPDF
$mpdf = new \Mpdf\Mpdf();

// Write the HTML content to mPDF
$mpdf->WriteHTML($htmlContent);

// Output the PDF
$filename = 'report.pdf';
$mpdf->Output($filename, \Mpdf\Output\Destination::INLINE); // Or DESTINATION_DOWNLOAD
exit;
?>

Option 2: Using LibreOffice Headless (Command Line)

This method requires LibreOffice to be installed on your server. You can execute a command from PHP using `shell_exec` or `exec`.

# Assuming your XLSX file is saved as 'report.xlsx'
# And you want to convert it to 'report.pdf'

# On Linux/macOS
/usr/bin/soffice --headless --convert-to pdf --outdir /path/to/output/directory /path/to/your/report.xlsx

# Or directly from PHP
$xlsxFilePath = '/path/to/your/report.xlsx';
$outputDir = '/path/to/output/directory';
$command = "/usr/bin/soffice --headless --convert-to pdf --outdir " . escapeshellarg($outputDir) . " " . escapeshellarg($xlsxFilePath);
shell_exec($command);
?>

This approach is often more reliable for complex formatting but adds a dependency on server software. Ensure the web server user has execute permissions for LibreOffice and write permissions for the output directory.

Implementation Strategies in WordPress

You can integrate this functionality into WordPress in several ways:

• Custom Plugin: The cleanest approach. Create a dedicated plugin that adds a new admin menu item (e.g., “Custom Reports”). This menu item would lead to a page with forms for date selection or order filtering, triggering the report generation.
• Theme Functions (`functions.php`): For simpler needs or theme-specific reports, you can add the code to your theme’s `functions.php` file. However, this couples the functionality tightly to the theme and is not recommended for complex or critical features.
• Admin AJAX: For a more interactive user experience, you can trigger the report generation via AJAX calls from an admin page. This allows for background processing and avoids full page reloads.

When implementing in WordPress, remember to hook into appropriate actions and filters, sanitize user inputs, and handle potential errors gracefully. For security, ensure that report generation is restricted to users with appropriate roles (e.g., administrators, shop managers).

Advanced Considerations

Performance: For very large datasets (thousands of orders), generating reports directly in the browser can lead to timeouts. Consider implementing background processing using WP-Cron or a dedicated job queue system.

Customization: PHP-Spreadsheet offers extensive styling options, charting capabilities, and data validation. You can tailor reports to include complex calculations, pivot tables, or visual representations of data.

Error Handling: Implement robust error handling for database queries, file operations, and external process calls (like LibreOffice). Log errors for debugging.

Security: Always sanitize and validate any user-provided input (like date ranges) before using it in queries or commands. Restrict access to report generation functions to authorized users.

Leveraging PHP-Spreadsheet for Dynamic WooCommerce PDF Reports

Generating custom financial reports and invoices directly from WooCommerce data can be a complex task, especially when aiming for dynamic, spreadsheet-compatible outputs that can then be converted to PDF. While many plugins offer basic PDF generation, they often lack the flexibility required for detailed financial analysis or integration with existing accounting workflows. This guide details a robust approach using the PHP-Spreadsheet library to programmatically create custom reports and invoices, exporting them in formats like XLSX, which can then be easily converted to PDF using external tools or services.

Setting Up the Development Environment

Before diving into code, ensure your WordPress development environment is ready. This involves having a local or staging WordPress installation with WooCommerce active. The primary tool we’ll use is the PHP-Spreadsheet library. The most straightforward way to integrate this into a WordPress project is via Composer.

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

composer require phpoffice/phpspreadsheet

This command will download the PHP-Spreadsheet library and its dependencies into a `vendor` directory. You’ll then need to include Composer’s autoloader in your PHP scripts:

<?php
// In your custom plugin or theme's main file
require_once __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice.PhpSpreadsheet\Writer\Xlsx;
// ... other necessary use statements
?>

Accessing WooCommerce Order Data

WooCommerce stores order data in the WordPress database, primarily in the `wp_posts` and `wp_postmeta` tables. We can query this data using WordPress’s built-in functions or directly with SQL. For this example, we’ll focus on retrieving orders within a specific date range.

A common scenario is generating a sales report for a given month. We can use `wc_get_orders` for this purpose, which is a more robust and WooCommerce-aware method than direct database queries for posts.

<?php
/**
 * Fetches WooCommerce orders within a date range.
 *
 * @param string $start_date Start date in 'YYYY-MM-DD' format.
 * @param string $end_date End date in 'YYYY-MM-DD' format.
 * @return WC_Order[] Array of WC_Order objects.
 */
function get_woocommerce_orders_in_date_range( $start_date, $end_date ) {
    $args = array(
        'status'   => array( 'wc-processing', 'wc-completed' ), // Example statuses
        'date_query' => array(
            array(
                'after'     => $start_date,
                'before'    => $end_date,
                'inclusive' => true,
            ),
        ),
        'limit'    => -1, // Get all matching orders
    );

    return wc_get_orders( $args );
}

// Example usage:
$start_date = '2023-10-01';
$end_date   = '2023-10-31';
$orders     = get_woocommerce_orders_in_date_range( $start_date, $end_date );
?>

Generating an XLSX Sales Report

Now, let’s use PHP-Spreadsheet to create an XLSX file from the fetched order data. This involves instantiating a `Spreadsheet` object, adding worksheets, populating cells, and then writing the file.

<?php
// Assuming $orders is populated from the previous step
if ( ! empty( $orders ) ) {
    // Create a new Spreadsheet object
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Monthly Sales Report');

    // Add headers
    $headers = array(
        'Order ID',
        'Order Date',
        'Customer Name',
        'Customer Email',
        'Total Amount',
        'Payment Method',
        'Order Status',
    );
    $sheet->fromArray( $headers, NULL, 'A1' );

    // Apply header styling (optional)
    $headerStyle = [
        'font' => ['bold' => true],
        'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFE0E0E0']],
    ];
    $sheet->getStyle('A1:' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($headers)) . '1')->applyFromArray($headerStyle);

    // Populate data rows
    $row_num = 2; // Start from the second row
    foreach ( $orders as $order ) {
        /** @var WC_Order $order */
        $customer_name = $order->get_formatted_billing_full_name();
        $customer_email = $order->get_billing_email();
        $total_amount = $order->get_total();
        $payment_method = $order->get_payment_method_title();
        $order_status = wc_get_order_status_name( $order->get_status() );

        $sheet->setCellValue('A' . $row_num, $order->get_id());
        $sheet->setCellValue('B' . $row_num, $order->get_date_created()->format('Y-m-d H:i:s'));
        $sheet->setCellValue('C' . $row_num, $customer_name);
        $sheet->setCellValue('D' . $row_num, $customer_email);
        $sheet->setCellValue('E' . $row_num, wc_format_decimal( $total_amount, 2 ));
        $sheet->setCellValue('F' . $row_num, $payment_method);
        $sheet->setCellValue('G' . $row_num, $order_status);

        $row_num++;
    }

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

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

    // Define the output filename
    $filename = 'sales-report-' . date('Y-m') . '.xlsx';

    // Output the file to the browser
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer->save('php://output');
    exit; // Important to stop further execution
} else {
    echo "No orders found for the specified date range.";
}
?>

Generating Individual Invoices

Creating individual invoices requires iterating through orders and extracting specific line item details, customer information, and company details. We can adapt the PHP-Spreadsheet library to create a more structured invoice format.

For this, we’ll create a separate worksheet for each invoice, or a single worksheet with multiple invoice layouts. Let’s opt for a single worksheet with distinct invoice blocks.

<?php
// Assuming $orders is populated from get_woocommerce_orders_in_date_range()

if ( ! empty( $orders ) ) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Invoices');

    $current_row = 1; // Start at the top

    // Company details (replace with your actual company info)
    $company_name = get_bloginfo('name');
    $company_address = get_option('woocommerce_store_address') . ', ' . get_option('woocommerce_store_city') . ', ' . get_option('woocommerce_store_postcode');
    $company_email = get_option('admin_email'); // Or a specific company email

    foreach ( $orders as $order ) {
        /** @var WC_Order $order */

        // Invoice Header
        $sheet->setCellValue('A' . $current_row, 'INVOICE');
        $sheet->getStyle('A' . $current_row)->getFont()->setSize(18)->setBold(true);
        $current_row++;

        // Company Info
        $sheet->setCellValue('A' . $current_row, $company_name);
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $company_address);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $company_email);
        $current_row += 2; // Add some spacing

        // Invoice Details
        $sheet->setCellValue('F' . $current_row, 'Invoice #:');
        $sheet->setCellValue('G' . $current_row, $order->get_id());
        $current_row++;
        $sheet->setCellValue('F' . $current_row, 'Invoice Date:');
        $sheet->setCellValue('G' . $current_row, $order->get_date_created()->format('Y-m-d'));
        $current_row++;
        $sheet->setCellValue('F' . $current_row, 'Due Date:');
        // Calculate due date (e.g., 30 days from invoice date)
        $due_date = $order->get_date_created()->modify('+30 days');
        $sheet->setCellValue('G' . $current_row, $due_date->format('Y-m-d'));
        $current_row += 2;

        // Customer Info
        $sheet->setCellValue('A' . $current_row, 'Bill To:');
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_formatted_billing_full_name());
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_billing_address_1());
        $current_row++;
        if ( $order->get_billing_address_2() ) {
            $sheet->setCellValue('A' . $current_row, $order->get_billing_address_2());
            $current_row++;
        }
        $sheet->setCellValue('A' . $current_row, $order->get_billing_city() . ', ' . $order->get_billing_postcode());
        $current_row++;
        $sheet->setCellValue('A' . $current_row, $order->get_billing_country());
        $current_row += 2;

        // Line Items Header
        $sheet->setCellValue('A' . $current_row, 'Description');
        $sheet->getStyle('A' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('D' . $current_row, 'Quantity');
        $sheet->getStyle('D' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('E' . $current_row, 'Unit Price');
        $sheet->getStyle('E' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('F' . $current_row, 'Total');
        $sheet->getStyle('F' . $current_row)->getFont()->setBold(true);
        $current_row++;

        // Line Items
        $line_item_start_row = $current_row;
        foreach ( $order->get_items() as $item_id => $item ) {
            /** @var WC_Order_Item_Product $item */
            $product_name = $item->get_name();
            $quantity = $item->get_quantity();
            $unit_price = wc_get_price_including_tax( $item ) ? $item->get_total() / $quantity : $item->get_subtotal() / $quantity;
            $line_total = $item->get_total();

            $sheet->setCellValue('A' . $current_row, $product_name);
            $sheet->setCellValue('D' . $current_row, $quantity);
            $sheet->setCellValue('E' . $current_row, wc_format_decimal( $unit_price, 2 ));
            $sheet->setCellValue('F' . $current_row, wc_format_decimal( $line_total, 2 ));
            $current_row++;
        }
        $line_item_end_row = $current_row - 1;

        // Apply borders to line items
        $sheet->getStyle('A' . $line_item_start_row . ':F' . $line_item_end_row)->applyFromArray([
            'borders' => [
                'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
            ],
        ]);

        // Totals Section
        $current_row++; // Space before totals
        $sheet->setCellValue('E' . $current_row, 'Subtotal:');
        $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_subtotal(), 2 ));
        $current_row++;

        if ( $order->get_total_discount() > 0 ) {
            $sheet->setCellValue('E' . $current_row, 'Discount:');
            $sheet->setCellValue('F' . $current_row, '-' . wc_format_decimal( $order->get_total_discount(), 2 ));
            $current_row++;
        }

        // Taxes
        $taxes = $order->get_taxes();
        if ( ! empty( $taxes ) ) {
            foreach ( $taxes as $tax_rate_id => $tax_amount ) {
                $tax_rate = new WC_Tax();
                $tax_label = $tax_rate->get_rate_label( $tax_rate_id );
                $sheet->setCellValue('E' . $current_row, $tax_label . ':');
                $sheet->setCellValue('F' . $current_row, wc_format_decimal( $tax_amount, 2 ));
                $current_row++;
            }
        }

        // Shipping
        if ( $order->get_total_shipping() > 0 ) {
            $sheet->setCellValue('E' . $current_row, 'Shipping:');
            $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_total_shipping(), 2 ));
            $current_row++;
        }

        // Grand Total
        $sheet->setCellValue('E' . $current_row, 'Total:');
        $sheet->getStyle('E' . $current_row)->getFont()->setBold(true);
        $sheet->setCellValue('F' . $current_row, wc_format_decimal( $order->get_total(), 2 ));
        $sheet->getStyle('F' . $current_row)->getFont()->setBold(true);
        $current_row++;

        // Add a separator for the next invoice
        $current_row += 3;
    }

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

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

    // Define the output filename
    $filename = 'invoices-' . date('Y-m-d') . '.xlsx';

    // Output the file to the browser
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer->save('php://output');
    exit;
} else {
    echo "No orders found to generate invoices.";
}
?>

Converting XLSX to PDF

PHP-Spreadsheet itself does not directly export to PDF. However, it can generate HTML, which can then be converted to PDF using libraries like mPDF or TCPDF. Alternatively, for server-side conversion, you might consider headless browser solutions like Puppeteer (Node.js) or command-line tools like LibreOffice in headless mode.

Option 1: Using mPDF (PHP Library)

First, install mPDF:

composer require mpdf/mpdf

Then, after generating the XLSX, you can generate HTML from the spreadsheet and pass it to mPDF. This requires a bit more work to correctly map spreadsheet cells to HTML tables.

<?php
// ... (after generating $spreadsheet object)

// Create an HTML writer
$htmlWriter = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
$htmlContent = $htmlWriter->generateSheetData($sheet); // Generates HTML for the active sheet

// Initialize mPDF
$mpdf = new \Mpdf\Mpdf();

// Write the HTML content to mPDF
$mpdf->WriteHTML($htmlContent);

// Output the PDF
$filename = 'report.pdf';
$mpdf->Output($filename, \Mpdf\Output\Destination::INLINE); // Or DESTINATION_DOWNLOAD
exit;
?>

Option 2: Using LibreOffice Headless (Command Line)

This method requires LibreOffice to be installed on your server. You can execute a command from PHP using `shell_exec` or `exec`.

# Assuming your XLSX file is saved as 'report.xlsx'
# And you want to convert it to 'report.pdf'

# On Linux/macOS
/usr/bin/soffice --headless --convert-to pdf --outdir /path/to/output/directory /path/to/your/report.xlsx

# Or directly from PHP
$xlsxFilePath = '/path/to/your/report.xlsx';
$outputDir = '/path/to/output/directory';
$command = "/usr/bin/soffice --headless --convert-to pdf --outdir " . escapeshellarg($outputDir) . " " . escapeshellarg($xlsxFilePath);
shell_exec($command);
?>

This approach is often more reliable for complex formatting but adds a dependency on server software. Ensure the web server user has execute permissions for LibreOffice and write permissions for the output directory.

Implementation Strategies in WordPress

You can integrate this functionality into WordPress in several ways:

• Custom Plugin: The cleanest approach. Create a dedicated plugin that adds a new admin menu item (e.g., “Custom Reports”). This menu item would lead to a page with forms for date selection or order filtering, triggering the report generation.
• Theme Functions (`functions.php`): For simpler needs or theme-specific reports, you can add the code to your theme’s `functions.php` file. However, this couples the functionality tightly to the theme and is not recommended for complex or critical features.
• Admin AJAX: For a more interactive user experience, you can trigger the report generation via AJAX calls from an admin page. This allows for background processing and avoids full page reloads.

When implementing in WordPress, remember to hook into appropriate actions and filters, sanitize user inputs, and handle potential errors gracefully. For security, ensure that report generation is restricted to users with appropriate roles (e.g., administrators, shop managers).

Advanced Considerations

Performance: For very large datasets (thousands of orders), generating reports directly in the browser can lead to timeouts. Consider implementing background processing using WP-Cron or a dedicated job queue system.

Customization: PHP-Spreadsheet offers extensive styling options, charting capabilities, and data validation. You can tailor reports to include complex calculations, pivot tables, or visual representations of data.

Error Handling: Implement robust error handling for database queries, file operations, and external process calls (like LibreOffice). Log errors for debugging.

Security: Always sanitize and validate any user-provided input (like date ranges) before using it in queries or commands. Restrict access to report generation functions to authorized users.