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

PHPz
Release: 2023-06-09 11:44:01
Original
3291 people have browsed it

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; }
Copy after login

}

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;
Copy after login

}

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;
Copy after login

}

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;
Copy after login

}

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;
Copy after login

}

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!