How to use phpexcel to convert Excel files to CSV files and open them

PHPEXCEL is an excellent PHP class library for reading and writing Excel files. It provides a very sufficient API that allows us to use PHP to read and write Excel files. Sometimes, we need to convert Excel files into CSV files for use on some occasions.

This article mainly describes how to use the PHPEXCEL class library to convert Excel files into CSV files and open them.

1. Convert Excel files to CSV format

Step 1: Install the PHPEXCEL class library

First, we need to use Composer to install the PHPEXCEL class library , you can use the following command:

composer require phpoffice/phpexcel

Step 2: Read the Excel file

Use PHPEXCEL to read the Excel file and convert it to a CSV file, we need to use the following code

// 加载类库
require_once 'vendor/autoload.php';

// 读取Excel文件
$inputExcel = './data/test.xlsx';
$phpExcel   = PHPExcel_IOFactory::load($inputExcel);

// 获取工作表
$sheet = $phpExcel->getActiveSheet();

// 循环遍历工作表
foreach ($sheet->getRowIterator() as $row) {
    $csv = [];
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);

    foreach ($cellIterator as $cell) {
        $csv[] = $cell->getValue();
    }

    echo implode(',', $csv), PHP_EOL;
}

Step 3: Convert the Excel file to a CSV file

We can replace the echo in the above code to write the data into the CSV file. The complete code is as follows

// 加载类库
require_once 'vendor/autoload.php';

// 读取Excel文件
$inputExcel = './data/test.xlsx';
$phpExcel   = PHPExcel_IOFactory::load($inputExcel);

// 定义CSV文件名和路径
$outputCSV  = './data/test.csv';

// 打开CSV文件并追加数据
$file = fopen($outputCSV, 'a+');

// 获取工作表
$sheet = $phpExcel->getActiveSheet();

// 循环遍历工作表
foreach ($sheet->getRowIterator() as $row) {
    $csv = [];
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);

    foreach ($cellIterator as $cell) {
        $csv[] = $cell->getValue();
    }

    // 将数据写入CSV文件中
    fwrite($file, implode(',', $csv) . PHP_EOL);
}

// 关闭CSV文件
fclose($file);

In the above code, we first define the file name and path to be written to the CSV file, then use the fopen function to open the CSV file, and set the opening method to append data. Next, we loop through each row of the Excel file and use foreach to loop through the cells in each row and store the cell value into an array. Finally, we use the fwrite function to write each row of data into the CSV file. Finally, we close the CSV file using the fclose function.

2. Open the CSV format file

After completing the conversion of the Excel file to a CSV file, we need to open the CSV file and use Excel or a text editor to view its content.

If you need to view a CSV file in Excel, you can use the following steps to open it:

1. Start the Excel application.
2. Click "File"->"Open".
3. Navigate to the location of the CSV file and select the file.
4. Select "Text File" in the "File Type" drop-down menu.
5. Click "Open".
6. In the "Text Import Wizard" window, make sure the "Delimiters" tab is selected. The "Delimiter Type" tab below should select "Comma".
7. Click "Next".
8. On the "Delimiter" tab, select the delimiter (such as comma) used to separate the data.
9. Click "Next".
10. If there is no problem with the format of the data column, no changes are needed, just click "Next".
11. In the "Destination" step, select the worksheet of the Excel file.
12. Click "Done".

If you need to use a text editor to open the CSV file, you can open it directly with a text editor. The data content of the file will be displayed in plain text.

Conclusion

In this article, we introduced how to use the PHPEXCEL class library to convert Excel files to CSV format and open them. I hope it can help readers make better use of the PHPEXCEL class library.

The above is the detailed content of How to use phpexcel to convert Excel files to CSV files and open them. For more information, please follow other related articles on the PHP Chinese website!