How to read Excel files with PHP and answers to common questions

PHP Reading Excel File Methods and FAQ

Excel is a very common spreadsheet file format, and many businesses and data are stored in Excel files. During the development process, if you need to import the data in the Excel file into the system, you need to use PHP to read the Excel file. This article will introduce how to read Excel files with PHP and answer common questions.

1. How to read Excel files with PHP

1. Use PHPExcel class library

PHPExcel is a PHP class library that can easily read data in Excel files. You can install it using the composer command by adding the PHPExcel component package in the composer.json file.

After the installation is completed, introduce the PHPExcel class library into the code, use the PHPExcel_IOFactory::load() method to open the Excel file, and use PHP's loop statement to traverse and read the data.

The following is a sample code for reading an Excel file:

require_once '/path/to/PHPExcel.php';

// The path here is absolute to the Excel file Path
$reader = PHPExcel_IOFactory::load('/path/to/example.xlsx');
// Get all worksheets in the Excel file
$sheets = $reader->getAllSheets( );

$data = array();
// Loop to read the data in each worksheet
foreach ($sheets as $sheet) {

// 获取当前工作表的数据行数和列数
$rowCount = $sheet->getHighestRow(); // 数据行数
$columnCount = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); // 数据列数

// 循环获取每行数据
for ($i = 1; $i <= $rowCount; $i++) {
    // 循环获取每列数据
    $row = array();
    for ($j = 0; $j < $columnCount; $j++) {
        // 通过坐标获取单元格数据
        $cellValue = $sheet->getCellByColumnAndRow($j, $i)->getValue();
        // 将数据存入数组
        $row[] = $cellValue;
    }
    // 将行数据存入数组
    $data[] = $row;
}

}

var_dump($data);

2. Use PHPOffice/PhpSpreadsheet class library

PhpSpreadsheet is an alternative class library to PHPExcel, which provides better performance and more completeness function. It can be installed through composer command.

The following is a sample code for using PhpSpreadsheet to read an Excel file:

require_once '/path/to/vendor/autoload.php';

use PhpOfficePhpSpreadsheetIOFactory;

// The path here is the absolute path of the Excel file
$reader = IOFactory::createReaderForFile('/path/to/example.xlsx');
// Open the Excel file and read all data into memory
$spreadsheet = $reader->load('/path/to/example.xlsx');

$data = array();
// Iterate over all worksheets and row
foreach ($spreadsheet->getActiveSheet()->getRowIterator() as $row) {

$rowData = array();
// 迭代所有单元格
foreach ($row->getCellIterator() as $cell) {
    // 获取单元格数据
    $cellValue = $cell->getValue();
    // 将数据存入数组
    $rowData[] = $cellValue;
}
// 将行数据存入数组
$data[] = $rowData;

}

var_dump($data);

2. Frequently Asked Questions

1. How to deal with spaces in Excel?

Unnecessary spaces often appear in Excel tables and need to be processed when reading data. You can use the trim() function to remove spaces from both ends of a string.

The following sample code shows how to remove spaces when reading Excel data:

require_once '/path/to/PHPExcel.php';

$reader = PHPExcel_IOFactory: :load('/path/to/example.xlsx');
$data = array();
foreach ($reader->getActiveSheet()->getRowIterator() as $row) {

$rowData = array();
foreach ($row->getCellIterator() as $cell) {
    $cellValue = $cell->getValue();
    $cellValue = trim($cellValue); // 去除空格
    $rowData[] = $cellValue;
}
$data[] = $rowData;

}

var_dump($data);

2. How to deal with dates in Excel?

The date value in Excel is actually a number and needs to be formatted after reading the data. You can use PHP's date() function to convert numbers to date format.

The following is the sample code:

require_once '/path/to/PHPExcel.php';

$reader = PHPExcel_IOFactory::load('/path/to/example .xlsx');
$data = array();
foreach ($reader->getActiveSheet()->getRowIterator() as $row) {

$rowData = array();
foreach ($row->getCellIterator() as $cell) {
    // 获取单元格数据
    $cellValue = $cell->getValue();
    // 如果当前单元格是日期值，则进行格式化
    if (PHPExcel_Shared_Date::isDateTime($cell)) {
        $cellValue = PHPExcel_Shared_Date::ExcelToPHP( $cellValue ); // 将数字转换为时间戳
        $dateValue = date("Y-m-d H:i:s", $cellValue); // 格式化日期
        $rowData[] = $dateValue;
    } else {
        $rowData[] = $cellValue;
    }
}
$data[] = $rowData;

}

var_dump($data);

3. How to deal with number format in Excel?

Numeric values ​​in Excel may have thousands separators, decimal places, etc., and need to be formatted after reading the data. You can use PHP's number_format() function to format numbers.

The following is the sample code:

require_once '/path/to/PHPExcel.php';

$reader = PHPExcel_IOFactory::load('/path/to/example .xlsx');
$data = array();
foreach ($reader->getActiveSheet()->getRowIterator() as $row) {

$rowData = array();
foreach ($row->getCellIterator() as $cell) {
    // 获取单元格数据
    $cellValue = $cell->getValue();
    // 如果当前单元格是数字值，则进行格式化
    if (is_numeric($cellValue)) {
        $cellFormat = $cell->getStyle()->getNumberFormat()->getFormatCode(); // 获取单元格格式
        $cellValue = number_format($cellValue, PHPExcel_Style_NumberFormat::toFormattedString($cellValue, $cellFormat), '.', ''); // 进行格式化
        $rowData[] = $cellValue;
    } else {
        $rowData[] = $cellValue;
    }
}
$data[] = $rowData;

}

var_dump($data);

The above is the method of reading Excel files in PHP and the content of FAQs. You can easily read Excel file data by using PHPExcel or PhpSpreadsheet class library. At the same time, you need to pay attention to handling spaces, dates and number formats in Excel.

The above is the detailed content of How to read Excel files with PHP and answers to common questions. For more information, please follow other related articles on the PHP Chinese website!