During a recent conversion project I needed to around 20 files to Excel (xls) format. As this was a project I decided to write a small script using the PhpSpreadsheet library.

Installation

Use composer to install PhpSpreadsheet into your project:

composer require phpoffice/phpspreadsheet

A simple example to read a CSV file and save to a Excel format is given below. The interesting thing is you do not have to parse the CSV yourself. This makes it quite easier to load a large number of CSV files.

<?php
 
require 'vendor/autoload.php';
 
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXls;
 
$spreadsheet = new Spreadsheet();
$reader = new PhpOfficePhpSpreadsheetReaderCsv();
 
/* Set CSV parsing options */
$reader->setDelimiter(',');
$reader->setEnclosure('"');
$reader->setSheetIndex(0);
 
/* Load a CSV file and save as a XLS */
$spreadsheet = $reader->load("countries.csv");
$writer = new Xls($spreadsheet);
$writer->save('countries.xls');
 
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);

In the above example we are writing the spreadsheet file in the ‘xls’ format. If you want to write in other formats – like ‘XLsx’, you will need to change the writer code in the above example.

..
use PhpOfficePhpSpreadsheetWriterXlxs;
..
$writer = new Xlxs($spreadsheet);

Note: All the CSV files were pre-processed before, so they were all in the same format – line endings, delimiters, headers etc. so not much error checking was required.

You need to set various CSV options before reading the CSV file. Often, CSV files are not really “comma separated”, or use semicolon (;) as a separator. Normally the separator will be auto-detected, so in most cases it should not be necessary to specify it. But in cases where auto-detection does not fit the use-case, then it can be set manually.

Note that ‘PhpOfficePhpSpreadsheetReaderCsv’ by default assumes that the loaded CSV file is UTF-8 encoded. If you are reading CSV files that were created in Microsoft Office Excel the correct input encoding may rather be -1252 (CP1252). Always make sure that the input encoding is set appropriately.

$reader->setInputEncoding('CP1252');
$reader->setDelimiter(';');
$reader->setEnclosure('');
$reader->setSheetIndex(0);

Converting from the command line

I added a small code to the above script so that I would be able to convert from the command line.

<?php
 
require 'vendor/autoload.php';
 
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXls;
 
if ($argc < 3 )
{
    exit( "Usage: php csv-to-excel.php <csv-file.csv> <excel-file.xls>n" );
}
 
 
$spreadsheet = new Spreadsheet();
$reader = new PhpOfficePhpSpreadsheetReaderCsv();
 
$reader->setDelimiter(',');
$reader->setEnclosure('"');
$reader->setSheetIndex(0);
 
 
$spreadsheet = $reader->load($argv[1]);
$writer = new Xls($spreadsheet);
$writer->save($argv[2]);
 
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);

You can now convert from the command line.

E:localhost>php csv-to-excel.php countries.csv countries-e.xls

PHPSpreadsheet is a huge library, and there are many things you can easily do regarding data processing with Excel and other documents. Please read the extensive documentation for details.



Source link
thanks you RSS link
( https://www.codediesel.com/php/convert-csv-to-excel-format-in-php/)

LEAVE A REPLY

Please enter your comment!
Please enter your name here