How to convert csv to xls in php

During the actual development process, we may need to convert data in CSV format into Excel format (XLS or XLSX). Because the CSV file is just a simple text file, and the Excel file supports more functions, such as data filtering, sorting, chart display, etc. PHP provides a powerful function library for processing CSV and Excel. The following will introduce how to use PHP to convert CSV files into Excel files.

1. Read CSV file

PHP provides the fgetcsv() function for reading CSV files and parsing each line into an array . The following is an example of reading a CSV file:

$csvFile = 'data.csv';
if(!file_exists($csvFile)) {
    die("File not found");
}
$fp = fopen($csvFile, 'r');
if(!$fp) {
    die("Error opening file");
}
$data = array();
while($row = fgetcsv($fp)) {
    $data[] = $row;
}
fclose($fp);

This code first checks whether the CSV file exists, then opens the file, and uses the fgetcsv() function to read each line of the file and stores the result in an array middle.

2. Create Excel files

PHPExcel is a very powerful PHP extension for creating and manipulating Excel files. We can download PHPExcel and include it into our PHP project. The method to create an empty Excel file is as follows:

require_once 'PHPExcel.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Sheet1');

This code uses PHPExcel to create an empty Excel file and sets a default worksheet.

3. Import CSV data into an Excel file

Using the setCellValue() method in PHPExcel, we can import the data in the CSV file into an Excel file. The following is a sample code:

require_once 'PHPExcel.php';

$csvFile = 'data.csv';
if(!file_exists($csvFile)) {
    die("File not found");
}
$csvData = file_get_contents($csvFile);
$data = array_map("str_getcsv", preg_split('/\r*\n+|\r+/', $csvData));

$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Sheet1');

$row = 1;
foreach($data as $fields) {
    $col = 0;
    foreach($fields as $value) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
        $col++;
    }
    $row++;
}

This code first uses the file_get_contents() function to read the contents of the CSV file, and then uses the preg_split() function to split the file contents into a two-dimensional array. Next, we use PHPExcel’s setCellValueByColumnAndRow() function to import the data into the Excel file.

4. Save the Excel file

Finally, we use PHPExcel’s save() method to save the file in XLS or XLSX format. The following is the complete sample code:

require_once 'PHPExcel.php';

$csvFile = 'data.csv';
if(!file_exists($csvFile)) {
    die("File not found");
}
$csvData = file_get_contents($csvFile);
$data = array_map("str_getcsv", preg_split('/\r*\n+|\r+/', $csvData));

$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Sheet1');

$row = 1;
foreach($data as $fields) {
    $col = 0;
    foreach($fields as $value) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
        $col++;
    }
    $row++;
}

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('data.xls');

This code imports data into a PHPExcel object and uses the createWriter() method of PHPExcel_IOFactory to generate an Excel5Writer object for saving the PHPExcel object as a file in XLS format. It should be noted that in order to use Excel2007 format (XLSX), you need to change Excel5 to Excel2007.

Summary

The above is the complete process of converting CSV files into Excel files. First read the CSV file, then import the data into a PHPExcel object and save the PHPExcel object as an Excel file. It should be noted that in actual applications, we may need to perform format verification and cleaning on the imported data to ensure the integrity and correctness of the data.

The above is the detailed content of How to convert csv to xls in php. For more information, please follow other related articles on the PHP Chinese website!