Numbers stored as text

[Rayza73]

I'm exporting data from a MySQL DB to Excel using PHPSpreadsheet. The spreadsheet is working fine, the data is exporting correctly, however, the numbers are being stored as text. I can get PHPSpreadsheet to convert the cell to a number, but the number is still stored as text.

    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
    
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet(); 
    
    
    $sheet->setCellValue('A1', 'Reference'); 
    $sheet->setCellValue('B1', 'Project Name'); 
    $sheet->setCellValue('C1', 'Value'); 
    $sheet->setCellValue('D1', 'Sales Rep');
    $sheet->setCellValue('E1', 'Stage'); 

    if ($totalrows_lpr > 0) {
        $i =1;
        while ($row_lpr = mysqli_fetch_assoc($result_lpr)) {
            $i++;
            $projectID = $row_lpr['projectID'];
            $projectRef = $row_lpr['projectRef'];
            $projectName = $row_lpr['projectName'];
            $value = projectvalue2($projectID);
            $staffName = $row_lpr['firstName'] . " " . $row_lpr['surname'];
            $stageName = $row_lpr['stageName'];
            $sheet->setCellValue('A'.$i , $projectRef);
            $sheet->setCellValue('B'.$i , $projectName);
            $sheet->setCellValue('C'.$i , $value);
            $sheet->getStyle('C'.$i)->getNumberFormat()->setFormatCode('#,##0.00');
            $sheet->setCellValue('D'.$i , $staffName);
            $sheet->setCellValue('E'.$i , $stageName);
        }
    }

The code for the function (projectvalue2):

$sql_pv = "SELECT MAX(productTotal)
                    FROM
                    ( SELECT SUM(productTotal) AS productTotal 
                        FROM quote_items
                        LEFT JOIN quote_details
                        ON quote_items.quoteID = quote_details.quoteID
                        WHERE quote_items.projectID = $projectID AND quote_details.visible = 1
                        GROUP BY quote_items.quoteID
                    )
                    quote_items
                    ";

The number is formatted correctly with thousand separators and 2 decimal points. If the cell has a zero value as in no data from the database then it is stored as a number in the cell.

[MarkBaker]

If the numbers are being stored as text, then it's because $value is a non-numeric string; so what PHP datatype is being returned from projectvalue2()?

[Rayza73]

The number is stored in the db as a decimal(13,2) so it seems as though PHP is handling it as a string. I've tried using gmp_int() and floatval() to convert the string into an integer but it's still being handled as text in Excel.

[Rayza73]

I changed the data type in the database from a decimal(13,2) to a float and now it works. Thanks for pointing me in the right direction Mark.