Importing and Exporting Excel Data in WordPress Using PhpSpreadsheet

In enterprise-level WordPress site development, customers often need to batch import product information, user data, or order records from Excel files, or export website data to Excel for reports. While there are some existing plugins for this, they might not perfectly match highly customized requirements. In such cases, we can use the PhpSpreadsheet library to achieve full control over the process.

Installing PhpSpreadsheet

The easiest way to install PhpSpreadsheet is via Composer:

composer require phpoffice/phpspreadsheet

Exporting Data to Excel

Below is a simplified example of how to fetch data from the WordPress database and export it to an .xlsx file.

use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;

function wprs_export_orders_to_excel()
{
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    // Set Header
    $sheet->setCellValue('A1', 'Order ID');
    $sheet->setCellValue('B1', 'Customer Name');
    $sheet->setCellValue('C1', 'Total Amount');

    // Fetch Data (simplified)
    global $wpdb;
    $orders = $wpdb->get_results("SELECT ID, post_title FROM {$wpdb->posts} WHERE post_type = 'shop_order' LIMIT 10");

    $row = 2;
    foreach ($orders as $order) {
        $sheet->setCellValue('A' . $row, $order->ID);
        // ... set other values
        $row++;
    }

    $writer = new Xlsx($spreadsheet);
    $file_path = WP_CONTENT_DIR . '/uploads/orders_export.xlsx';
    $writer->save($file_path);

    return content_url('uploads/orders_export.xlsx');
}

Importing Data from Excel

Importing data involves reading the Excel file and iterating through the rows to perform WordPress operations (like wp_insert_post or update_user_meta).

use PhpOfficePhpSpreadsheetIOFactory;

function wprs_import_products_from_excel($file_path)
{
    $spreadsheet = IOFactory::load($file_path);
    $sheet = $spreadsheet->getActiveSheet();
    $highestRow = $sheet->getHighestRow();

    for ($row = 2; $row <= $highestRow; $row++) {
        $product_title = $sheet->getCell('A' . $row)->getValue();
        $product_price = $sheet->getCell('B' . $row)->getValue();

        // Perform WordPress insertion
        // wp_insert_post([...]);
    }
}

Summary

PhpSpreadsheet is a powerful library that makes handling Excel files in WordPress much more professional and reliable. Compared to CSV, it supports multiple sheets, styling, and complex data types, which are often required in business environments.