During a recent data conversion project I needed to convert around 250 CSV files to Excel (xls) format. As this was a PHP project I decided to write a small PHP script using the PhpSpreadsheet library.
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.
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 PhpOffice\PhpSpreadsheet\Writer\Xlxs; .. $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 ‘\PhpOffice\PhpSpreadsheet\Reader\Csv’ 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 Windows-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.
\n" ); } $spreadsheet = new Spreadsheet(); $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv(); $reader->setDelimiter(','); $reader->setEnclosure('"'); $reader->setSheetIndex(0); $spreadsheet = $reader->load($argv); $writer = new Xls($spreadsheet); $writer->save($argv); $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.