PhpExcel Wrap text issue

Question

I have issues with converting .xlsx file to .csv. When I try to convert it, csv file appears with Text Wrap activated. Problem is, that I need that csv file to be imported to database, and current format does not let me to do that. I have question, how to disable text wrap when saving csv file, or ignore text wrap when importing file to database?

Convert:

require_once 'Classes/PHPExcel.php'; // (this should include the autoloader)
require_once 'Classes/PHPExcel/IOFactory.php';

$excel_readers = array(
    'Excel5' , 
    'Excel2003XML' , 
    'Excel2007'
);

$reader = PHPExcel_IOFactory::createReader('Excel2007');
$reader->setReadDataOnly(true);

$path = 'temp.xlsx';
$excel = $reader->load($path);

$writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
$writer->setDelimiter(',');
$writer->save('temp.csv');

Import to database:

if (file_exists('temp.csv')) {   
            $i=0;
            require "connection.php";
            $handle = fopen("temp.csv", "r");
            $import=$db->prepare("INSERT INTO adherence(
                    dateandtime,
                    lastname,
                    firstname,
                    paidtime,
                    approvedtime,
                    notadhering) VALUES(
                    ?,?,?,?,?,?)");
            while (($data = fgetcsv($handle, 1000, ',', "'")) !== FALSE) {
                if($i>0) {
                $data = str_replace('"', '', $data);
                $myDate =  date("Y/m/d",strtotime(str_replace('/','-',$data[0])));
                $import->bindParam(1, $myDate, PDO::PARAM_STR);
                $import->bindParam(2, $data[1], PDO::PARAM_STR);
                $import->bindParam(3, $data[2], PDO::PARAM_STR);
                $import->bindParam(4, $data[3], PDO::PARAM_STR);
                $import->bindParam(5, $data[4], PDO::PARAM_STR);
                $import->bindParam(6, $data[5], PDO::PARAM_STR);
                $import->execute();
                }
                $i++;

            }
            $removal=$db->prepare("delete FROM adherence WHERE approvedtime = '0' OR notadhering IS NULL");
            $removal->execute();

            fclose($handle);
            echo 'IMPORTED' ;
        }

Any assistance would be appreciated!

EDIT generated csv file opened with excel:

Same file after disabling wrap text

Answer 1

I doubt that it's "wrap text" that's causing problems, but new line characters in the cells. PHPExcel doesn't provide any means to remove these automatically. The best option would be to iterate over the cells, replacing all occurrences of "\n" with a space.

EDIT

Something like

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    foreach ($worksheet->getColumnIterator() as $column) {
        $cellIterator = $column->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(true);
        foreach ($cellIterator as $cell) {
            // Convert any rich text cells to plain text
            if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_INLINE) {
                $cell->setValueExplicit($cell->getValue()->getPlainText());
            }
            // Remove any newline characters in string cells
            if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_STRING) {
                $cell->setValue(str_replace("\n", " ", $cell->getValue()));
            }
        }
    }
}

might help