34

Can anyone guide me how to convert XLS to CSV using PHP?

I have excel spread sheet which contains a list of documents, I want to convert this with CSV format using PHP.

Hash
  • 4,647
  • 5
  • 21
  • 39
RAAAAM
  • 3,378
  • 19
  • 59
  • 108
  • 1
    Excel has a CSV export option. Is it not feasible to have your users use that? (Just thinking of the least work-intensive way...) – Pekka Oct 14 '11 at 10:28
  • A CSV file can only contain one table of data, so it's not possible to convert one XLS file with multiple tables into one CSV file. – hakre Oct 14 '11 at 10:32
  • Sure you can, and it's a worksheet you refer to. it will export the last worksheet by default. – Glenn Plas Dec 08 '13 at 13:50

5 Answers5

21

This will surely work,

require_once 'Classes/PHPExcel/IOFactory.php';

$inputFileType = 'Excel5';
$inputFileName = 'YOUR_EXCEL_FILE_PATH';

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcelReader = $objReader->load($inputFileName);

$loadedSheetNames = $objPHPExcelReader->getSheetNames();

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcelReader, 'CSV');

foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
    $objWriter->setSheetIndex($sheetIndex);
    $objWriter->save($loadedSheetName.'.csv');
}

Hope this helps...

Rajat Modi
  • 1,343
  • 14
  • 38
21

Rewrite the code provided by @Rajat Modi using PhpSpreadsheet library due to PHPExcel is deprecated.

https://github.com/PHPOffice/PhpSpreadsheet

https://phpspreadsheet.readthedocs.io/en/develop/

<?php 

require 'vendor\autoload.php';

use \PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use \PhpOffice\PhpSpreadsheet\Writer\Csv;

$xls_file = "Example.xlsx";

$reader = new Xlsx();
$spreadsheet = $reader->load($xls_file);

$loadedSheetNames = $spreadsheet->getSheetNames();

$writer = new Csv($spreadsheet);

foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
    $writer->setSheetIndex($sheetIndex);
    $writer->save($loadedSheetName.'.csv');
}
cww
  • 593
  • 1
  • 8
  • 16
  • I got "Fatal error: Uncaught Error: Class 'PhpSpreadsheet\Reader\Xlsx' ", any idea why? – 200313 Mar 26 '19 at 06:34
  • Did you include require 'vendor\autoload.php'; – cww Apr 03 '19 at 08:48
  • //Changed to $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet); //add BOM to anable UTF-8 $writer->setUseBOM(true); – Duc Manh Nguyen Sep 06 '22 at 03:23
13

Probably you can start reading a XLS using PHP.

Then, using the main logic to output what you want (csv in your case).

Good luck,

robermorales
  • 3,293
  • 2
  • 27
  • 36
6

You can use the php library PHPExcel to read the excel file, and just loop over the rows and cells and just write the data out to a csv file?

iWantSimpleLife
  • 1,944
  • 14
  • 22
  • 1
    It's now "PHPSpreadsheet". I managed to find these details about how to read and write from / to a file: https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-and-writing-to-file/ – Adamantus Apr 30 '20 at 14:07
1

This will work. Install Spout

<?php 
require 'vendor\autoload.php';
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;

$writer = WriterEntityFactory::createCSVWriter();
$reader = ReaderEntityFactory::createXLSXReader();
$writer->openToFile("Output CSV path");
$reader->open("Input XSLX path");

foreach ($reader->getSheetIterator() as $sheet) {
   foreach ($sheet->getRowIterator() as $row) {
       $writer->addRow($row);
   }
}

$writer->close();
$reader->close();
kalesh kannan
  • 11
  • 2