How to read Excel data in PHP and convert it to a specified format

In web application development, Excel files are a very widespread and common data source format. However, the data format in the Excel file may not be compatible with our target application. Therefore, we need a method to convert Excel data into the data format we need. This article will introduce how to import Excel files in PHP and convert the data format.

Step 1: Install the PHPExcel library

PHPExcel is a PHP library for reading and writing Excel files. We can install this library using Composer. Enter the following command in the terminal:

composer require phpoffice/phpexcel

This will download and install the PHPExcel library and its dependencies.

Step 2: Write the code

In this step, we will write the PHP code to read the Excel file and convert it into the data format we need. The following code demonstrates how to read an Excel file through PHPExcel and convert it into an array:

<?php
require_once &#39;vendor/autoload.php&#39;;

$inputFileType = &#39;Excel5&#39;; //xls格式
$inputFileName = &#39;example.xls&#39;;
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true); 

print_r($sheetData);
?>

In the above example, we first specify the Excel file type and file name to be read. Then, we create a PHPExcel reader object and use its load() method to open the Excel file. Finally, we use the toarray() method of the PHPExcel object to convert the Excel data into an array and print the array.

Now, we have converted the Excel data into an array. However, our target application may require a different data format, so we must perform further format conversions on this array.

The following is a sample code to convert Excel data to JSON format:

<?php
require_once &#39;vendor/autoload.php&#39;;

$inputFileType = &#39;Excel5&#39;; //xls格式
$inputFileName = &#39;example.xls&#39;;
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true); 

$jsonData = json_encode($sheetData);

echo $jsonData;
?>

In the above example, we use PHP's json_encode() function to convert the Excel array to JSON format, and its printed out.

Step Three: Data Validation and Conversion

In the second step, we have converted the Excel data into the format we need, but we also need to Perform validation and conversion. For example, we can convert datetime to standard time format, convert numeric string to numeric type, etc.

The following is a simple date time format conversion example:

<?php
require_once &#39;vendor/autoload.php&#39;;

$inputFileType = &#39;Excel5&#39;; //xls格式
$inputFileName = &#39;example.xls&#39;;
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true); 

foreach($sheetData as $key => $row) {
    if($key > 1) {
        $sheetData[$key][3] = strtotime($row[3]);
    }
}

$jsonData = json_encode($sheetData);

echo $jsonData;
?>

In the above example, we iterate through each row in the array and convert the date time in column 4 to Unix Timestamp format.

Conclusion

By using the PHPExcel library, we can easily import Excel data into PHP applications and convert it into the format we need. However, before performing format conversion, we must carefully consider the issues of data validation and conversion to ensure the correctness and consistency of the data.

The above is the detailed content of How to read Excel data in PHP and convert it to a specified format. For more information, please follow other related articles on the PHP Chinese website!