Sai A Sai A
Updated date Nov 29, 2023
In this blog, we will learn how to streamline your data organization process in web development by converting PHP arrays into Excel sheets. This blog covers two methods, providing insights into manual array manipulation with PHPExcel and using PHP's built-in libraries for a simpler solution.

Introduction:

Managing data is a crucial aspect of web development, and often, presenting data in a structured manner becomes essential. Excel sheets are a widely used format for organizing and analyzing data. In this blog, we will explore how to convert arrays into Excel sheets using PHP, a server-side scripting language commonly used in web development.

Method 1: Manual Approach

Let's start with a manual approach. This method involves iterating through the array and populating an Excel sheet cell by cell. Here's a simple PHP program to illustrate this:

<?php
// Sample array data
$data = [
    ['Name', 'Age', 'Country'],
    ['John Doe', 25, 'USA'],
    ['Jane Smith', 30, 'Canada'],
    ['Bob Johnson', 22, 'UK']
];

// Load PHPExcel library
require_once 'PHPExcel.php';

// Create a new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator('Your Name')
    ->setLastModifiedBy('Your Name')
    ->setTitle('Array to Excel Example')
    ->setSubject('Array to Excel Example')
    ->setDescription('Converting array data to Excel sheet');

// Set active sheet
$objPHPExcel->setActiveSheetIndex(0);

// Populate Excel sheet from array
$row = 1;
foreach ($data as $rowData) {
    $col = 'A';
    foreach ($rowData as $cellData) {
        $objPHPExcel->getActiveSheet()->setCellValue($col . $row, $cellData);
        $col++;
    }
    $row++;
}

// Save Excel file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('array_to_excel_method1.xls');

echo 'Excel file generated successfully!';
?>

Output:

You will find a file named array_to_excel_method1.xls in the same directory as your PHP script. Open it, and you should see the array data neatly arranged in an Excel sheet.

In this method, we use the PHPExcel library to create and manipulate Excel files. The script initializes a PHPExcel object, sets document properties, and then iterates through the array, populating the Excel sheet cell by cell. Finally, it saves the Excel file with a given name.

Method 2: Utilizing PHP's Built-in Libraries

An alternative method involves using PHP's built-in functions for handling Excel files. This method may be preferred for its simplicity and minimal dependencies.

<?php
// Sample array data
$data = [
    ['Name', 'Age', 'Country'],
    ['John Doe', 25, 'USA'],
    ['Jane Smith', 30, 'Canada'],
    ['Bob Johnson', 22, 'UK']
];

// Create a new Excel object
$excel = new COM("Excel.Application") or die("Unable to instantiate Excel");

// Add a workbook
$workbook = $excel->Workbooks->Add();

// Select the first sheet
$sheet = $workbook->Worksheets(1);

// Populate Excel sheet from array
$row = 1;
foreach ($data as $rowData) {
    $col = 1;
    foreach ($rowData as $cellData) {
        $sheet->Cells($row, $col)->Value = $cellData;
        $col++;
    }
    $row++;
}

// Save Excel file
$workbook->SaveAs('array_to_excel_method2.xlsx');

// Close Excel
$excel->Quit();

echo 'Excel file generated successfully!';
?>

Output:

A file named array_to_excel_method2.xlsx will be created in the same directory as your PHP script. Open it, and you should see the array data formatted in an Excel sheet.

In this method, we use COM (Component Object Model) to interact with Excel. The script creates an instance of Excel, adds a workbook, and populates the sheet by iterating through the array. Finally, it saves the Excel file with a given name.

Conclusion:

In this blog, we have explored two methods to convert arrays to Excel sheets using PHP. The manual approach with the PHPExcel library offers flexibility and control, while the second method utilizes PHP's built-in functions for a simpler solution.

Comments (0)

There are no comments. Be the first to comment!!!