CODEWITHSUNDEEP
phpregex
Johann Danner
Johann Danner

Reputation: 1

How can I convert a negative number from Excel with PHP

I import an "xlsx" file with php into a MySQL database.

In Excel the number looks like this (German number format): 2,564.30 or -2,564.30

I convert this to this string into a number in order to be able to save this in the MySQL as a number with the following:

floatval (preg_replace("/[-]?[^-0-9\.]/", "", $row[16]))

The number is imported but not including the minus. I only get positive numbers.

The output is always

2564.30

Unfortunately always without a minus!

I am not familiar with regex.

Update

Sorry, but none of the variants work.

This ist the Number in Excel: 1

And this in mysql: 2

In the mysql database formated as: float(12,2) Here the Code from the import.php:

<?php

include '../assets/inc/db.php';
include '../assets/inc/db2.php';
include '../assets/inc/SimpleXLSX.php';

require '../vendor/autoload.php';

if(isset($_GET['bvh'])){
  $bvh = $_GET['bvh'];
}else{
  echo "Kein BVH ausgewählt";
}

$inputFileType = 'Xlsx';
$inputFileName = './importe/as4u_export.xlsx';
$sheetname = '0001';

/**  Define a Read Filter class implementing \PhpOffice\PhpSpreadsheet\Reader\IReadFilter  */
class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    public function readCell($column, $row, $worksheetName = '') {
        //  Read rows 1 to 7 and columns A to E only
        if ($row >= 5 && $row <= 65536) {
            if (in_array($column,range('A','Q'))) {
                return true;
            }
        }
        return false;
    }
}

/**  Create an Instance of our Read Filter  **/
$filterSubset = new MyReadFilter();

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Tell the Reader that we want to use the Read Filter  **/
$reader->setReadFilter($filterSubset);
/**  Load only the rows and columns that match our filter to Spreadsheet  **/
$spreadsheet = $reader->load($inputFileName);

$data = $spreadsheet->getActivesheet()->toArray();

$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);

foreach($data as $row){
  $insert_data = array(
    ':as4u_KOA'       =>  $row[5],
    ':as4u_KOA_name'  =>  $row[8],
    ':as4u_text'      =>  $row[14],
    ':as4u_betrag'    =>  floatval(preg_replace('~^-\K|[^-\d.]~', '', $row[16])),
  );

  $query = "INSERT INTO ww_as4u (as4u_bvh_id, as4u_KOA, as4u_KOA_name, as4u_text, as4u_betrag) VALUES ('$bvh', :as4u_KOA, :as4u_KOA_name, :as4u_text, :as4u_betrag)";
  $statement = $conn->prepare($query);
  $statement->execute($insert_data);
}

Upvotes: 0

Views: 508

Answers (3)

nosurs
nosurs

Reputation: 665

As an alternative to using regular expressions, you could also use PHP's NumberFormatter class.

$formatter = new NumberFormatter("de-DE", NumberFormatter::DECIMAL);
$formatter->parse("2.564,30");  // Gives 2564.3
$formatter->parse("-2.564,30"); // Gives -2564.3

Since NumberFormatter is part of PHP's Intl (= Internationalization) extension, it might or might not be already installed/enabled, depending on your PHP version.

In case it's not readily available, see:

For NumberFormat itself, see:

Upvotes: 1

marac990
marac990

Reputation: 100

try this: "/[^-0-9.]/" for the regex

Upvotes: 0

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626738

You can use

preg_replace('~^-\K|[^-\d.]~', '', $row[16])

See the regex demo.

Details

  • ^-\K - matches a - at the start of string and omits the match
  • | - or
  • [^-\d.] - any char other than -, `digits and dots.

See a PHP demo:

$strs = [' 2,564.30', '-2,564.30'];
print_r(preg_replace('~^-\K|[^-\d.]~', '', $strs));
// => Array( [0] => 2564.30    [1] => -2564.30 )

Upvotes: 0

Related Questions