CSV Export/Import with PHPExcel

Question

Hi Could you please guide me or provide me with some sample codes for performing CSV export and import using the PHPExcel library?

Excel export and import is fine but I need csv export/import as well. I have other means of CSV export and import but can it be done via phpexcel also?

Any kind of help is greatly appreciated.

Thanks in advance.

Answer 1

To import a CSV file into a PHPExcel object

$inputFileType = 'CSV';
$inputFileName = 'testFile.csv';
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

To export a CSV file from a PHPExcel object

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$objWriter->save('testExportFile.csv');

EDIT

How to read through the rows and cells:

$worksheet = $objPHPExcel->getActiveSheet();
foreach ($worksheet->getRowIterator() as $row) {
    echo 'Row number: ' . $row->getRowIndex() . "\r\n";

    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
    foreach ($cellIterator as $cell) {
        if (!is_null($cell)) {
            echo 'Cell: ' . $cell->getCoordinate() . ' - ' . $cell->getValue() . "\r\n";
        }
    }
}

How to write to a PHPExcel object: You don't say where your data comes from: here's how to do it from a MySQL Query

$query = sprintf("SELECT firstname, lastname, age, date_of_birth, salary FROM employees WHERE firstname='%s' AND lastname='%s'",
                  mysql_real_escape_string($firstname),
                  mysql_real_escape_string($lastname));
$result = mysql_query($query);

$row = 1;
$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, 'First Name')
                              ->setCellValue('B'.$row, 'Last Name')
                              ->setCellValue('C'.$row, 'Age')
                              ->setCellValue('D'.$row, 'Date of birth')
                              ->setCellValue('E'.$row, 'Salary');
$row++;
while ($row = mysql_fetch_assoc($result)) {
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $row['firstname'])
                                  ->setCellValue('B'.$row, $row['lastname'])
                                  ->setCellValue('C'.$row, $row['age'])
                                  ->setCellValue('D'.$row, PHPExcel_Shared_Date::stringToExcel($row['date_of_birth']))
                                  ->setCellValue('E'.$row, $row['salary']);
    $objPHPExcel->getActiveSheet()->getStyle('D'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);
    $objPHPExcel->getActiveSheet()->getStyle('E'.$row)->getNumberFormat()->setFormatCode('£#,##0.00');
    $row++;
}