Implementing automated compliance reporting for custom custom product catalogs ledgers using custom PHP-Spreadsheet exports
<?php
/**
* Generates a compliance report for custom product catalog entries.
*/
// Ensure WordPress environment is loaded if this is a standalone script.
// In a plugin or theme, this might not be strictly necessary if run via an admin action.
if ( ! defined( 'ABSPATH' ) ) {
require_once( $_SERVER['DOCUMENT_ROOT'] . '/wp-load.php' );
}
// Include Composer autoloader
require_once __DIR__ . '/vendor/autoload.php'; // Adjust path if necessary
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
function generate_product_compliance_report() {
// 1. Initialize Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Product Compliance Report');
// 2. Define Headers
$headers = [
'SKU',
'Product Name',
'Manufacturer',
'Compliance Status',
'Last Compliance Check',
'Regulatory Approval Number',
'Material Composition'
];
// Apply headers to the first row
$col = 'A';
foreach ($headers as $header_text) {
$sheet->setCellValue($col . '1', $header_text);
// Style headers
$sheet->getStyle($col . '1')->getFont()->setBold(true);
$sheet->getStyle($col . '1')->getFill()->setFillType(Fill::FILL_SOLID);
$sheet->getStyle($col . '1')->getFill()->getStartColor()->setRGB('D3D3D3'); // Light gray
$sheet->getStyle($col . '1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$col++;
}
// 3. Query Product Catalog Data
$args = [
'post_type' => 'product_catalog',
'posts_per_page' => -1, // Get all posts
'post_status' => 'publish',
];
$products_query = new WP_Query($args);
$row_num = 2; // Start from the second row for data
if ($products_query->have_posts()) {
while ($products_query->have_posts()) {
$products_query->the_post();
global $post;
// Retrieve custom field values
$sku = get_post_meta($post->ID, 'sku', true);
$product_name = get_the_title(); // Product name is usually the post title
$manufacturer = get_post_meta($post->ID, 'manufacturer', true);
$compliance_status = get_post_meta($post->ID, 'compliance_status', true);
$last_compliance_check = get_post_meta($post->ID, 'last_compliance_check_date', true);
$regulatory_approval_number = get_post_meta($post->ID, 'regulatory_approval_number', true);
$material_composition = get_post_meta($post->ID, 'material_composition', true);
// Populate spreadsheet row
$col = 'A';
$sheet->setCellValue($col++ . $row_num, $sku);
$sheet->setCellValue($col++ . $row_num, $product_name);
$sheet->setCellValue($col++ . $row_num, $manufacturer);
$sheet->setCellValue($col++ . $row_num, $compliance_status);
$sheet->setCellValue($col++ . $row_num, $last_compliance_check);
$sheet->setCellValue($col++ . $row_num, $regulatory_approval_number);
$sheet->setCellValue($col++ . $row_num, $material_composition);
$row_num++;
}
wp_reset_postdata(); // Restore original post data
} else {
// Handle case where no products are found
$sheet->setCellValue('A2', 'No product catalog entries found.');
$sheet->mergeCells('A2:G2'); // Merge cells for the message
$sheet->getStyle('A2')->getFont()->setItalic(true);
}
// 4. Auto-size columns for better readability
foreach (range('A', $sheet->getHighestColumn()) as $columnID) {
$sheet->getColumnDimension($columnID)->setAutoSize(true);
}
// 5. Output as XLSX file
$writer = new Xlsx($spreadsheet);
$filename = 'product_compliance_report_' . date('Ymd_His') . '.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
// If you are in a secure environment, you might want to use these headers
// header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
// header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
// header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
// header('Pragma: public'); // HTTP/1.0
$writer->save('php://output');
exit; // Important to stop script execution after outputting file
}
// Example of how to trigger this function (e.g., via an admin menu page or a button click)
// For demonstration, we'll hook it to an admin action.
// In a real scenario, you'd likely have a dedicated button or menu item.
function add_compliance_report_button() {
// This is a simplified example. A proper admin page with a form and nonce is recommended.
if (isset($_GET['generate_report']) && $_GET['generate_report'] === 'product_compliance') {
// Basic security check: ensure it's an admin user and has capabilities.
if (current_user_can('manage_options')) {
generate_product_compliance_report();
} else {
wp_die('You do not have sufficient permissions to access this page.');
}
}
}
add_action('admin_init', 'add_compliance_report_button');
// To trigger this report, you could navigate to:
// your-wp-admin-url?page=your-admin-page-slug&generate_report=product_compliance
// Or, if you're not using a custom admin page, you might directly link to:
// your-site-url/wp-admin/admin-ajax.php?action=generate_product_report&nonce=your_nonce
// For simplicity in this example, we'll assume a direct URL access for demonstration.
// A more robust solution would involve an AJAX call or a dedicated admin page.
// For a simple direct link (less secure, for quick testing):
// add_action('admin_menu', function() {
// add_management_page(
// 'Compliance Report',
// 'Compliance Report',
// 'manage_options',
// 'compliance-report-generator',
// 'display_compliance_report_page'
// );
// });
// function display_compliance_report_page() {
// echo '<div class="wrap">';
// echo '<h1>Product Compliance Report</h1>';
// echo '<p>Click the button below to generate the compliance report.</p>';
// echo '<a href="' . admin_url('admin-ajax.php?action=generate_product_report&nonce=' . wp_create_nonce('generate_product_report_nonce')) . '" class="button button-primary">Generate Report</a>';
// echo '</div>';
// }
// add_action('wp_ajax_generate_product_report', 'generate_product_compliance_report');
?>
Integrating the Report Generation
The provided PHP script includes a basic mechanism to trigger the report generation via a GET parameter. In a production environment, you’d want a more secure and user-friendly approach:
Example: Triggering via Admin AJAX
Here’s how you might set up an AJAX action for a more robust integration. This code would typically go into your plugin’s main file or functions.php.
<?php
/**
* Adds an AJAX action for generating the product compliance report.
*/
function add_product_compliance_ajax_action() {
add_action('wp_ajax_generate_product_report', 'generate_product_compliance_report');
// If you need this for non-logged-in users (unlikely for admin reports), use wp_ajax_nopriv_
}
add_action('init', 'add_product_compliance_ajax_action');
// The generate_product_compliance_report() function from the previous example
// needs to be defined or included here as well.
// Ensure the Composer autoloader is included.
// To trigger this via AJAX, you would typically have a JavaScript button
// that makes a POST or GET request to admin-ajax.php with the 'action' parameter.
// Example JavaScript snippet (jQuery):
/*
jQuery.ajax({
url: ajaxurl, // WordPress provides this global variable
type: 'POST',
data: {
action: 'generate_product_report',
nonce: ' . wp_create_nonce('generate_product_report_nonce') . ' // Crucial for security
},
success: function(response) {
// Handle response - typically, the file download will start automatically
// if the PHP script outputs headers correctly.
console.log('Report generation initiated.');
},
error: function(xhr, status, error) {
console.error('Error generating report:', error);
}
});
*/
?>
Remember to replace the placeholder nonce generation and usage with proper nonce verification within your AJAX handler if you implement this. The `generate_product_compliance_report` function itself should include check_ajax_referer() before proceeding.
Security Considerations
When implementing automated reporting, especially for sensitive data, security is paramount:
- User Capabilities: Ensure that only users with appropriate roles and permissions (e.g., ‘manage_options’, ‘edit_posts’ for a specific CPT) can trigger the report generation. Use
current_user_can(). - Nonces: Always use WordPress nonces to verify that requests are legitimate and originate from your site, especially for AJAX requests and form submissions.
- Input Validation: If your report generation is parameterized (e.g., date ranges), always validate and sanitize any user-provided input.
- Output Sanitization: While PHP-Spreadsheet handles the file format, ensure that the data you pull from the database is appropriately escaped if it were to be displayed directly in HTML, though for spreadsheet export, this is less critical unless you’re embedding complex HTML within cells.
- File Permissions: If you choose to save reports to the server instead of direct download, ensure the directory has appropriate, restrictive file permissions.
Advanced Customizations
PHP-Spreadsheet offers extensive capabilities for further customization:
- Conditional Formatting: Highlight rows or cells based on compliance status (e.g., red for ‘Non-Compliant’).
- Formulas: Include calculated fields or summaries within the spreadsheet.
- Multiple Sheets: Organize different types of data or reports on separate sheets within the same workbook.
- Data Validation: Set up dropdown lists or data constraints within cells for easier data entry if the spreadsheet is intended for modification.
- Export Formats: Beyond XLSX, PHP-Spreadsheet supports CSV, PDF, HTML, and more. Choose the format that best suits your compliance workflow.
By integrating PHP-Spreadsheet into your WordPress workflow, you can transform manual, tedious compliance reporting into an automated, efficient process, ensuring your custom product catalog data remains auditable and compliant.
composer require phpoffice/phpspreadsheet
This command will download and install the PHP-Spreadsheet library and its dependencies into your WordPress installation’s vendor directory. You’ll then need to include the Composer autoloader in your PHP scripts.
Data Source: Custom Product Catalog Ledger
For this example, we’ll assume you have a custom post type (CPT) named product_catalog with custom fields storing critical compliance-related data. These fields might include:
sku(Stock Keeping Unit)product_namemanufacturercompliance_status(e.g., ‘Compliant’, ‘Pending Review’, ‘Non-Compliant’)last_compliance_check_dateregulatory_approval_numbermaterial_composition
We’ll create a PHP function that queries these posts and extracts the relevant data.
Core Logic: Generating the Spreadsheet
The core of our solution involves a PHP script that:
- Initializes the PHP-Spreadsheet writer.
- Queries the WordPress database for product catalog entries.
- Iterates through the results, populating the spreadsheet rows.
- Sets appropriate headers and formatting.
- Outputs the spreadsheet for download.
Let’s craft the PHP code. This script can be placed in a custom plugin, a theme’s functions.php file (though a plugin is generally preferred for maintainability), or as a standalone script if your WordPress setup allows.
PHP Script for Spreadsheet Generation
This script assumes you are running it within the WordPress environment, allowing access to WordPress functions like WP_Query. We’ll include the Composer autoloader to bring in PHP-Spreadsheet.
<?php
/**
* Generates a compliance report for custom product catalog entries.
*/
// Ensure WordPress environment is loaded if this is a standalone script.
// In a plugin or theme, this might not be strictly necessary if run via an admin action.
if ( ! defined( 'ABSPATH' ) ) {
require_once( $_SERVER['DOCUMENT_ROOT'] . '/wp-load.php' );
}
// Include Composer autoloader
require_once __DIR__ . '/vendor/autoload.php'; // Adjust path if necessary
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
function generate_product_compliance_report() {
// 1. Initialize Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Product Compliance Report');
// 2. Define Headers
$headers = [
'SKU',
'Product Name',
'Manufacturer',
'Compliance Status',
'Last Compliance Check',
'Regulatory Approval Number',
'Material Composition'
];
// Apply headers to the first row
$col = 'A';
foreach ($headers as $header_text) {
$sheet->setCellValue($col . '1', $header_text);
// Style headers
$sheet->getStyle($col . '1')->getFont()->setBold(true);
$sheet->getStyle($col . '1')->getFill()->setFillType(Fill::FILL_SOLID);
$sheet->getStyle($col . '1')->getFill()->getStartColor()->setRGB('D3D3D3'); // Light gray
$sheet->getStyle($col . '1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$col++;
}
// 3. Query Product Catalog Data
$args = [
'post_type' => 'product_catalog',
'posts_per_page' => -1, // Get all posts
'post_status' => 'publish',
];
$products_query = new WP_Query($args);
$row_num = 2; // Start from the second row for data
if ($products_query->have_posts()) {
while ($products_query->have_posts()) {
$products_query->the_post();
global $post;
// Retrieve custom field values
$sku = get_post_meta($post->ID, 'sku', true);
$product_name = get_the_title(); // Product name is usually the post title
$manufacturer = get_post_meta($post->ID, 'manufacturer', true);
$compliance_status = get_post_meta($post->ID, 'compliance_status', true);
$last_compliance_check = get_post_meta($post->ID, 'last_compliance_check_date', true);
$regulatory_approval_number = get_post_meta($post->ID, 'regulatory_approval_number', true);
$material_composition = get_post_meta($post->ID, 'material_composition', true);
// Populate spreadsheet row
$col = 'A';
$sheet->setCellValue($col++ . $row_num, $sku);
$sheet->setCellValue($col++ . $row_num, $product_name);
$sheet->setCellValue($col++ . $row_num, $manufacturer);
$sheet->setCellValue($col++ . $row_num, $compliance_status);
$sheet->setCellValue($col++ . $row_num, $last_compliance_check);
$sheet->setCellValue($col++ . $row_num, $regulatory_approval_number);
$sheet->setCellValue($col++ . $row_num, $material_composition);
$row_num++;
}
wp_reset_postdata(); // Restore original post data
} else {
// Handle case where no products are found
$sheet->setCellValue('A2', 'No product catalog entries found.');
$sheet->mergeCells('A2:G2'); // Merge cells for the message
$sheet->getStyle('A2')->getFont()->setItalic(true);
}
// 4. Auto-size columns for better readability
foreach (range('A', $sheet->getHighestColumn()) as $columnID) {
$sheet->getColumnDimension($columnID)->setAutoSize(true);
}
// 5. Output as XLSX file
$writer = new Xlsx($spreadsheet);
$filename = 'product_compliance_report_' . date('Ymd_His') . '.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
// If you are in a secure environment, you might want to use these headers
// header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
// header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
// header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
// header('Pragma: public'); // HTTP/1.0
$writer->save('php://output');
exit; // Important to stop script execution after outputting file
}
// Example of how to trigger this function (e.g., via an admin menu page or a button click)
// For demonstration, we'll hook it to an admin action.
// In a real scenario, you'd likely have a dedicated button or menu item.
function add_compliance_report_button() {
// This is a simplified example. A proper admin page with a form and nonce is recommended.
if (isset($_GET['generate_report']) && $_GET['generate_report'] === 'product_compliance') {
// Basic security check: ensure it's an admin user and has capabilities.
if (current_user_can('manage_options')) {
generate_product_compliance_report();
} else {
wp_die('You do not have sufficient permissions to access this page.');
}
}
}
add_action('admin_init', 'add_compliance_report_button');
// To trigger this report, you could navigate to:
// your-wp-admin-url?page=your-admin-page-slug&generate_report=product_compliance
// Or, if you're not using a custom admin page, you might directly link to:
// your-site-url/wp-admin/admin-ajax.php?action=generate_product_report&nonce=your_nonce
// For simplicity in this example, we'll assume a direct URL access for demonstration.
// A more robust solution would involve an AJAX call or a dedicated admin page.
// For a simple direct link (less secure, for quick testing):
// add_action('admin_menu', function() {
// add_management_page(
// 'Compliance Report',
// 'Compliance Report',
// 'manage_options',
// 'compliance-report-generator',
// 'display_compliance_report_page'
// );
// });
// function display_compliance_report_page() {
// echo '<div class="wrap">';
// echo '<h1>Product Compliance Report</h1>';
// echo '<p>Click the button below to generate the compliance report.</p>';
// echo '<a href="' . admin_url('admin-ajax.php?action=generate_product_report&nonce=' . wp_create_nonce('generate_product_report_nonce')) . '" class="button button-primary">Generate Report</a>';
// echo '</div>';
// }
// add_action('wp_ajax_generate_product_report', 'generate_product_compliance_report');
?>
Integrating the Report Generation
The provided PHP script includes a basic mechanism to trigger the report generation via a GET parameter. In a production environment, you’d want a more secure and user-friendly approach:
Example: Triggering via Admin AJAX
Here’s how you might set up an AJAX action for a more robust integration. This code would typically go into your plugin’s main file or functions.php.
<?php
/**
* Adds an AJAX action for generating the product compliance report.
*/
function add_product_compliance_ajax_action() {
add_action('wp_ajax_generate_product_report', 'generate_product_compliance_report');
// If you need this for non-logged-in users (unlikely for admin reports), use wp_ajax_nopriv_
}
add_action('init', 'add_product_compliance_ajax_action');
// The generate_product_compliance_report() function from the previous example
// needs to be defined or included here as well.
// Ensure the Composer autoloader is included.
// To trigger this via AJAX, you would typically have a JavaScript button
// that makes a POST or GET request to admin-ajax.php with the 'action' parameter.
// Example JavaScript snippet (jQuery):
/*
jQuery.ajax({
url: ajaxurl, // WordPress provides this global variable
type: 'POST',
data: {
action: 'generate_product_report',
nonce: ' . wp_create_nonce('generate_product_report_nonce') . ' // Crucial for security
},
success: function(response) {
// Handle response - typically, the file download will start automatically
// if the PHP script outputs headers correctly.
console.log('Report generation initiated.');
},
error: function(xhr, status, error) {
console.error('Error generating report:', error);
}
});
*/
?>
Remember to replace the placeholder nonce generation and usage with proper nonce verification within your AJAX handler if you implement this. The `generate_product_compliance_report` function itself should include check_ajax_referer() before proceeding.
Security Considerations
When implementing automated reporting, especially for sensitive data, security is paramount:
- User Capabilities: Ensure that only users with appropriate roles and permissions (e.g., ‘manage_options’, ‘edit_posts’ for a specific CPT) can trigger the report generation. Use
current_user_can(). - Nonces: Always use WordPress nonces to verify that requests are legitimate and originate from your site, especially for AJAX requests and form submissions.
- Input Validation: If your report generation is parameterized (e.g., date ranges), always validate and sanitize any user-provided input.
- Output Sanitization: While PHP-Spreadsheet handles the file format, ensure that the data you pull from the database is appropriately escaped if it were to be displayed directly in HTML, though for spreadsheet export, this is less critical unless you’re embedding complex HTML within cells.
- File Permissions: If you choose to save reports to the server instead of direct download, ensure the directory has appropriate, restrictive file permissions.
Advanced Customizations
PHP-Spreadsheet offers extensive capabilities for further customization:
- Conditional Formatting: Highlight rows or cells based on compliance status (e.g., red for ‘Non-Compliant’).
- Formulas: Include calculated fields or summaries within the spreadsheet.
- Multiple Sheets: Organize different types of data or reports on separate sheets within the same workbook.
- Data Validation: Set up dropdown lists or data constraints within cells for easier data entry if the spreadsheet is intended for modification.
- Export Formats: Beyond XLSX, PHP-Spreadsheet supports CSV, PDF, HTML, and more. Choose the format that best suits your compliance workflow.
By integrating PHP-Spreadsheet into your WordPress workflow, you can transform manual, tedious compliance reporting into an automated, efficient process, ensuring your custom product catalog data remains auditable and compliant.
Leveraging PHP-Spreadsheet for Automated Compliance Reporting on Custom Product Catalogs
Many WordPress sites, especially e-commerce platforms or those with custom product management systems, require regular compliance reporting. This often involves exporting detailed product catalog data into a structured format like a spreadsheet for auditing, regulatory checks, or internal review. Manually generating these reports is time-consuming and error-prone. This guide details how to implement automated compliance reporting for custom product catalog ledgers using the powerful PHP-Spreadsheet library.
Prerequisites and Setup
Before we begin, ensure you have Composer installed and a WordPress environment ready. We’ll be using PHP-Spreadsheet, a modern library for reading and writing spreadsheet files. The easiest way to integrate it into your WordPress project is via Composer.
Navigate to your WordPress root directory in your terminal and run:
composer require phpoffice/phpspreadsheet
This command will download and install the PHP-Spreadsheet library and its dependencies into your WordPress installation’s vendor directory. You’ll then need to include the Composer autoloader in your PHP scripts.
Data Source: Custom Product Catalog Ledger
For this example, we’ll assume you have a custom post type (CPT) named product_catalog with custom fields storing critical compliance-related data. These fields might include:
sku(Stock Keeping Unit)product_namemanufacturercompliance_status(e.g., ‘Compliant’, ‘Pending Review’, ‘Non-Compliant’)last_compliance_check_dateregulatory_approval_numbermaterial_composition
We’ll create a PHP function that queries these posts and extracts the relevant data.
Core Logic: Generating the Spreadsheet
The core of our solution involves a PHP script that:
- Initializes the PHP-Spreadsheet writer.
- Queries the WordPress database for product catalog entries.
- Iterates through the results, populating the spreadsheet rows.
- Sets appropriate headers and formatting.
- Outputs the spreadsheet for download.
Let’s craft the PHP code. This script can be placed in a custom plugin, a theme’s functions.php file (though a plugin is generally preferred for maintainability), or as a standalone script if your WordPress setup allows.
PHP Script for Spreadsheet Generation
This script assumes you are running it within the WordPress environment, allowing access to WordPress functions like WP_Query. We’ll include the Composer autoloader to bring in PHP-Spreadsheet.
<?php
/**
* Generates a compliance report for custom product catalog entries.
*/
// Ensure WordPress environment is loaded if this is a standalone script.
// In a plugin or theme, this might not be strictly necessary if run via an admin action.
if ( ! defined( 'ABSPATH' ) ) {
require_once( $_SERVER['DOCUMENT_ROOT'] . '/wp-load.php' );
}
// Include Composer autoloader
require_once __DIR__ . '/vendor/autoload.php'; // Adjust path if necessary
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
function generate_product_compliance_report() {
// 1. Initialize Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Product Compliance Report');
// 2. Define Headers
$headers = [
'SKU',
'Product Name',
'Manufacturer',
'Compliance Status',
'Last Compliance Check',
'Regulatory Approval Number',
'Material Composition'
];
// Apply headers to the first row
$col = 'A';
foreach ($headers as $header_text) {
$sheet->setCellValue($col . '1', $header_text);
// Style headers
$sheet->getStyle($col . '1')->getFont()->setBold(true);
$sheet->getStyle($col . '1')->getFill()->setFillType(Fill::FILL_SOLID);
$sheet->getStyle($col . '1')->getFill()->getStartColor()->setRGB('D3D3D3'); // Light gray
$sheet->getStyle($col . '1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$col++;
}
// 3. Query Product Catalog Data
$args = [
'post_type' => 'product_catalog',
'posts_per_page' => -1, // Get all posts
'post_status' => 'publish',
];
$products_query = new WP_Query($args);
$row_num = 2; // Start from the second row for data
if ($products_query->have_posts()) {
while ($products_query->have_posts()) {
$products_query->the_post();
global $post;
// Retrieve custom field values
$sku = get_post_meta($post->ID, 'sku', true);
$product_name = get_the_title(); // Product name is usually the post title
$manufacturer = get_post_meta($post->ID, 'manufacturer', true);
$compliance_status = get_post_meta($post->ID, 'compliance_status', true);
$last_compliance_check = get_post_meta($post->ID, 'last_compliance_check_date', true);
$regulatory_approval_number = get_post_meta($post->ID, 'regulatory_approval_number', true);
$material_composition = get_post_meta($post->ID, 'material_composition', true);
// Populate spreadsheet row
$col = 'A';
$sheet->setCellValue($col++ . $row_num, $sku);
$sheet->setCellValue($col++ . $row_num, $product_name);
$sheet->setCellValue($col++ . $row_num, $manufacturer);
$sheet->setCellValue($col++ . $row_num, $compliance_status);
$sheet->setCellValue($col++ . $row_num, $last_compliance_check);
$sheet->setCellValue($col++ . $row_num, $regulatory_approval_number);
$sheet->setCellValue($col++ . $row_num, $material_composition);
$row_num++;
}
wp_reset_postdata(); // Restore original post data
} else {
// Handle case where no products are found
$sheet->setCellValue('A2', 'No product catalog entries found.');
$sheet->mergeCells('A2:G2'); // Merge cells for the message
$sheet->getStyle('A2')->getFont()->setItalic(true);
}
// 4. Auto-size columns for better readability
foreach (range('A', $sheet->getHighestColumn()) as $columnID) {
$sheet->getColumnDimension($columnID)->setAutoSize(true);
}
// 5. Output as XLSX file
$writer = new Xlsx($spreadsheet);
$filename = 'product_compliance_report_' . date('Ymd_His') . '.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
// If you are in a secure environment, you might want to use these headers
// header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
// header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
// header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
// header('Pragma: public'); // HTTP/1.0
$writer->save('php://output');
exit; // Important to stop script execution after outputting file
}
// Example of how to trigger this function (e.g., via an admin menu page or a button click)
// For demonstration, we'll hook it to an admin action.
// In a real scenario, you'd likely have a dedicated button or menu item.
function add_compliance_report_button() {
// This is a simplified example. A proper admin page with a form and nonce is recommended.
if (isset($_GET['generate_report']) && $_GET['generate_report'] === 'product_compliance') {
// Basic security check: ensure it's an admin user and has capabilities.
if (current_user_can('manage_options')) {
generate_product_compliance_report();
} else {
wp_die('You do not have sufficient permissions to access this page.');
}
}
}
add_action('admin_init', 'add_compliance_report_button');
// To trigger this report, you could navigate to:
// your-wp-admin-url?page=your-admin-page-slug&generate_report=product_compliance
// Or, if you're not using a custom admin page, you might directly link to:
// your-site-url/wp-admin/admin-ajax.php?action=generate_product_report&nonce=your_nonce
// For simplicity in this example, we'll assume a direct URL access for demonstration.
// A more robust solution would involve an AJAX call or a dedicated admin page.
// For a simple direct link (less secure, for quick testing):
// add_action('admin_menu', function() {
// add_management_page(
// 'Compliance Report',
// 'Compliance Report',
// 'manage_options',
// 'compliance-report-generator',
// 'display_compliance_report_page'
// );
// });
// function display_compliance_report_page() {
// echo '<div class="wrap">';
// echo '<h1>Product Compliance Report</h1>';
// echo '<p>Click the button below to generate the compliance report.</p>';
// echo '<a href="' . admin_url('admin-ajax.php?action=generate_product_report&nonce=' . wp_create_nonce('generate_product_report_nonce')) . '" class="button button-primary">Generate Report</a>';
// echo '</div>';
// }
// add_action('wp_ajax_generate_product_report', 'generate_product_compliance_report');
?>
Integrating the Report Generation
The provided PHP script includes a basic mechanism to trigger the report generation via a GET parameter. In a production environment, you’d want a more secure and user-friendly approach:
Example: Triggering via Admin AJAX
Here’s how you might set up an AJAX action for a more robust integration. This code would typically go into your plugin’s main file or functions.php.
<?php
/**
* Adds an AJAX action for generating the product compliance report.
*/
function add_product_compliance_ajax_action() {
add_action('wp_ajax_generate_product_report', 'generate_product_compliance_report');
// If you need this for non-logged-in users (unlikely for admin reports), use wp_ajax_nopriv_
}
add_action('init', 'add_product_compliance_ajax_action');
// The generate_product_compliance_report() function from the previous example
// needs to be defined or included here as well.
// Ensure the Composer autoloader is included.
// To trigger this via AJAX, you would typically have a JavaScript button
// that makes a POST or GET request to admin-ajax.php with the 'action' parameter.
// Example JavaScript snippet (jQuery):
/*
jQuery.ajax({
url: ajaxurl, // WordPress provides this global variable
type: 'POST',
data: {
action: 'generate_product_report',
nonce: ' . wp_create_nonce('generate_product_report_nonce') . ' // Crucial for security
},
success: function(response) {
// Handle response - typically, the file download will start automatically
// if the PHP script outputs headers correctly.
console.log('Report generation initiated.');
},
error: function(xhr, status, error) {
console.error('Error generating report:', error);
}
});
*/
?>
Remember to replace the placeholder nonce generation and usage with proper nonce verification within your AJAX handler if you implement this. The `generate_product_compliance_report` function itself should include check_ajax_referer() before proceeding.
Security Considerations
When implementing automated reporting, especially for sensitive data, security is paramount:
- User Capabilities: Ensure that only users with appropriate roles and permissions (e.g., ‘manage_options’, ‘edit_posts’ for a specific CPT) can trigger the report generation. Use
current_user_can(). - Nonces: Always use WordPress nonces to verify that requests are legitimate and originate from your site, especially for AJAX requests and form submissions.
- Input Validation: If your report generation is parameterized (e.g., date ranges), always validate and sanitize any user-provided input.
- Output Sanitization: While PHP-Spreadsheet handles the file format, ensure that the data you pull from the database is appropriately escaped if it were to be displayed directly in HTML, though for spreadsheet export, this is less critical unless you’re embedding complex HTML within cells.
- File Permissions: If you choose to save reports to the server instead of direct download, ensure the directory has appropriate, restrictive file permissions.
Advanced Customizations
PHP-Spreadsheet offers extensive capabilities for further customization:
- Conditional Formatting: Highlight rows or cells based on compliance status (e.g., red for ‘Non-Compliant’).
- Formulas: Include calculated fields or summaries within the spreadsheet.
- Multiple Sheets: Organize different types of data or reports on separate sheets within the same workbook.
- Data Validation: Set up dropdown lists or data constraints within cells for easier data entry if the spreadsheet is intended for modification.
- Export Formats: Beyond XLSX, PHP-Spreadsheet supports CSV, PDF, HTML, and more. Choose the format that best suits your compliance workflow.
By integrating PHP-Spreadsheet into your WordPress workflow, you can transform manual, tedious compliance reporting into an automated, efficient process, ensuring your custom product catalog data remains auditable and compliant.