flowmattic-xlsx-conversion-api.php

<?php
/**
 * Plugin Name:       FlowMattic XLSX Conversion API
 * Plugin URI:        https://flowmattic.com
 * Description:       A plugin to convert XLSX files to CSV or JSON. CSV is uploaded to the media library, and JSON is returned.
 * Version:           1.0.0
 * Author:            FlowMattic
 * Author URI:        https://flowmattic.com
 * Text Domain:       flowmattic-xlsx-conversion-api
 * Domain Path:       /languages
 */

// Ensure this file is called by WordPress.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// Load PhpSpreadsheet library (use composer autoload, if installed via composer).
require_once __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

/**
 * Convert XLSX file to CSV or JSON.
*
* @param string $file_url The URL of the XLSX file.
* @param string $conversion_type Either 'csv' or 'json'.
*
* @return array|string If conversion type is 'json', returns an array with the JSON output, otherwise, returns an empty array.
*/
function flowmattic_convert_xlsx( $file_url, $conversion_type ) {
	// Download the XLSX file to a temporary location.
	$temp_file = download_url( $file_url );

	if ( is_wp_error( $temp_file ) ) {
		return array( 'error' => 'Failed to download file.' );
	}

	// Load the XLSX file.
	try {
		$spreadsheet = IOFactory::load( $temp_file );
	} catch ( Exception $e ) {
		@unlink( $temp_file );
		return array( 'error' => 'Failed to load XLSX file.' );
	}

	// Get the original filename without extension for naming the CSV.
	$path_parts = pathinfo( $file_url );
	$base_filename = $path_parts['filename']; // Filename without extension

	// Handle conversion type.
	if ( 'csv' === $conversion_type ) {
		$csv_file = flowmattic_xlsx_to_csv( $spreadsheet, $base_filename );

		if ( ! is_wp_error( $csv_file ) ) {
			@unlink( $temp_file ); // Clean up temporary file.
			return array( 'csv_url' => wp_get_attachment_url( $csv_file ) );
		} else {
			return array( 'error' => 'CSV conversion failed.' );
		}
	} elseif ( 'json' === $conversion_type ) {
		$json_output = flowmattic_xlsx_to_json( $spreadsheet );
		@unlink( $temp_file ); // Clean up temporary file.
		return array( 'json_output' => wp_json_encode( $json_output ) );
	}

	@unlink( $temp_file ); // Clean up temporary file.
	return array( 'error' => 'Invalid conversion type.' );
}

/**
 * Convert XLSX to CSV and upload to the media library.
*
* @param PhpOffice\PhpSpreadsheet\Spreadsheet $spreadsheet The spreadsheet object.
* @param string $base_filename The base filename to use for the CSV file.
*
* @return int|WP_Error The attachment ID on success, or WP_Error on failure.
*/
function flowmattic_xlsx_to_csv( $spreadsheet, $base_filename ) {
	// Create temporary file for CSV.
	$csv_file_path = wp_tempnam( $base_filename );

	$worksheet = $spreadsheet->getActiveSheet();
	$data      = $worksheet->toArray();

	// Open CSV file for writing.
	$csv_file = fopen( $csv_file_path, 'w' );

	// Filter out rows that only have content in the first column, and empty rows/columns.
	foreach ( $data as $row ) {
		$filtered_row = array_filter( $row, function ( $cell ) {
			return $cell !== null && $cell !== '';
		});

		// Skip rows that have only the first column filled.
		if ( count( $filtered_row ) === 1 && ! empty( $row[0] ) ) {
			continue; // Skip this row if only the first column is filled.
		}

		// Only write non-empty rows that have more than just the first column.
		if ( ! empty( $filtered_row ) ) {
			fputcsv( $csv_file, $filtered_row );
		}
	}

	fclose( $csv_file );

	// Prepare CSV file for upload to media library.
	$file_array = array(
		'name'     => $base_filename . '.csv',
		'tmp_name' => $csv_file_path,
	);

	// Upload CSV file to the media library.
	$attachment_id = media_handle_sideload( $file_array, 0 );

	if ( is_wp_error( $attachment_id ) ) {
		@unlink( $csv_file_path ); // Clean up temporary file.
		return $attachment_id;
	}

	return $attachment_id;
}

/**
 * Convert XLSX to JSON.
*
* @param PhpOffice\PhpSpreadsheet\Spreadsheet $spreadsheet The spreadsheet object.
*
* @return array The JSON output.
*/
function flowmattic_xlsx_to_json( $spreadsheet ) {
	$worksheet   = $spreadsheet->getActiveSheet();
	$data        = $worksheet->toArray();

	// Filter out empty rows and empty columns.
	$filtered_data = array_map(
		function( $row ) {
			// Remove empty columns.
			return array_filter(
				$row,
				function( $cell ) {
					return $cell !== null && $cell !== '';
				}
			);
		},
		$data
	);

	// Remove rows that are completely empty.
	$filtered_data = array_filter( $filtered_data, 'count' );

	return array_values( $filtered_data );
}