PHP Linux script operation practice: processing Excel files

Introduction:
In today's digital era, data processing is everywhere, and Excel, as a powerful and widely used spreadsheet software, is used in all walks of life. is widely used in. However, the complex format and structure of Excel cause us to face certain challenges when processing large batches of data. As a powerful and widely used server-side scripting language, PHP provides a wealth of libraries and tools for processing data and Excel. This article will focus on using PHP scripts to operate Excel files in a Linux environment. It will specifically introduce how to use PHP to extract, modify or create data from Excel files, and provide corresponding code examples.

1. Reading and extracting data from Excel files

1. Use the PHPExcel library to read Excel files
   Under Linux, we can use the PHPExcel library to read Excel files. PHPExcel is a PHP class library for processing Excel files in PHP. First, we need to install the PHPExcel library. PHPExcel can be installed through composer. The method is as follows:

composer require phpoffice/phpexcel

2. Reading data in Excel files
   Reading Excel files is mainly divided into two steps: opening the Excel file and reading data. The following is a specific code example:

require 'vendor/autoload.php';

// 打开Excel文件
$inputFile = 'path/to/excel/file.xlsx';
$reader = PhpOfficePhpSpreadsheetIOFactory::createReader('Xlsx');
$spreadsheet = $reader->load($inputFile);

// 选择活动工作表
$worksheet = $spreadsheet->getActiveSheet();

// 获取表格的行数和列数
$rowCount = $worksheet->getHighestRow();
$columnCount = $worksheet->getHighestColumn();

// 读取表格中的数据
$data = [];
for ($row = 1; $row <= $rowCount; $row++) {
    $rowData = [];
    for ($col = 'A'; $col <= $columnCount; $col++) {
        $stringValue = $worksheet->getCell($col . $row)->getValue();
        $rowData[] = $stringValue;
    }
    $data[] = $rowData;
}

// 打印数据
foreach ($data as $rowData) {
    foreach ($rowData as $cellValue) {
        echo $cellValue . "    ";
    }
    echo PHP_EOL;
}

The above code example uses the PHPExcel library to open the Excel file, read the data in the table, and print the data.

2. Modification and Saving of Excel Files

1. Modify the data in the Excel file
   Modifying the Excel file is mainly divided into two steps: reading the Excel file and modifying the data. The following is a specific code example:

require 'vendor/autoload.php';

// 打开Excel文件
$inputFile = 'path/to/excel/file.xlsx';
$reader = PhpOfficePhpSpreadsheetIOFactory::createReader('Xlsx');
$spreadsheet = $reader->load($inputFile);

// 选择活动工作表
$worksheet = $spreadsheet->getActiveSheet();

// 修改表格中的数据
$worksheet->setCellValue('A1', 'New Value');
$worksheet->setCellValue('B1', 'New Value');

// 保存修改后的Excel文件
$outputFile = 'path/to/output/excel/file.xlsx';
$writer = PhpOfficePhpSpreadsheetIOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save($outputFile);

The above code example opens the Excel file, modifies the data in cells A1 and B1, and then saves the modified Excel file.

3. Creating Excel files and writing data

1. Creating Excel files and writing data
   Creating Excel files is mainly divided into two steps: creating Excel objects and writing Enter data. The following is a specific code example:

require 'vendor/autoload.php';

// 创建Excel对象
$spreadsheet = new PhpOfficePhpSpreadsheetSpreadsheet();
$worksheet = $spreadsheet->getActiveSheet();

// 写入数据
$worksheet->setCellValue('A1', 'Value 1');
$worksheet->setCellValue('B1', 'Value 2');

// 保存Excel文件
$outputFile = 'path/to/output/excel/file.xlsx';
$writer = PhpOfficePhpSpreadsheetIOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save($outputFile);

The above code example creates a new Excel file, writes data to cells A1 and B1, and then saves the Excel file.

Conclusion:
The above are some practical methods of using PHP to operate Excel files in the Linux environment. Whether it is reading, modifying, or creating Excel files, PHPExcel provides powerful functions and convenient code interfaces. By rationally using these methods, we can efficiently process large batches of data in Excel files and improve the efficiency and accuracy of data processing. I hope this article can be helpful to everyone and arouse interest and thinking in PHP operating Excel files under Linux.

The above is the detailed content of PHP Linux script operation practice: processing Excel files. For more information, please follow other related articles on the PHP Chinese website!