CODEWITHSUNDEEP
phpphpexcel
Heman G
Heman G

Reputation: 75

PHPEXCEL- how to remove hidden line breaks from cells

I'm trying to upload an excel sheet which contains financial data. It has account numbers on column A and when I upload the sheet COL A data comes with "x000D". When I google this it says something to do with hidden line breaks on cells. Does anybody know how to remove these line breaks from cells? Thanks

function import_data($filename, &$new_chart_accounts, $extension = '') {
    $this->init_worksheet($filename, $extension);

    $entries = array();
    $new_chart_accounts = array();
    $chart_accounts = ChartAccounts::read_all(true);

    $cha_description;
    $indent = $this->dimension[0][0];
    for ($row = $this->dimension[0][1] + 1; $row < $this->dimension[1][1]; $row++) {
        $entry = array();
        for ($col = $this->dimension[0][0]; $col <= $this->dimension[1][0]; $col++) {
            if ($this->sheet->cellExistsByColumnAndRow($col, $row)) {
                $cell = $this->sheet->getCellByColumnAndRow($col, $row);
                $style = $this->sheet->getStyle($cell->getCoordinate());
                $fill = $style->getFill();
                if ($fill->getFillType() == PHPExcel_Style_Fill::FILL_NONE 
                        || ($fill->getFillType() == PHPExcel_Style_Fill::FILL_SOLID && $fill->getStartColor()->getRGB() == 'FFFFFF')) {

                    $value = trim($cell->getValue());
                    //print $value;
                    switch ($col) {
                        case self::ACCOUNT_NUMBER:
                            $entry['cha_number'] = $value;
                            break;
                        case self::ACCOUNT_DESCRIPTION:
                            $entry['cha_description'] = $value;
                            break;
                        case self::AMOUNT:
                        case self::AMOUNT_2:
                            $value = str_replace(array(',', '$'), '', $value);
                            // $sign = substr($value, strlen($value) - 2, 2);
                            // if ($sign == 'cr' || $sign == 'dr') {
                                // $value = str_replace($sign, '', $value);
                            // } else {
                                // $sign = 'dr';
                            // }
                            // $entry['sign'] = $sign;
                            // if ($value != '' && is_numeric($value)) {
                                // $entry['data_amount'] = $value;
                            // }
            $entry['data_amount'] = $value;
                    }
                }
            }
        }

Upvotes: 1

Views: 4308

Answers (1)

mmr
mmr

Reputation: 516

This will replace any space contained in a string and will get the cell value with formatting

$cellValue = str_replace(' ', '', $cell->getFormattedValue());

Hope this will help, it would be better if we can have a copy of your excel file. Thanks.

Upvotes: 2

Related Questions