CODEWITHSUNDEEP
phpexcelexportnumericphpspreadsheet
Brill Brenhill
Brill Brenhill

Reputation: 11

Error "invalid numeric value for data type numeric in...." while exporting database to excel

I've used phpspreadsheet to export data to excel. This works fine but, sometime it gives the error as invalid numeric value for data type numeric in c:\xamp\htdocs\phpspreadsheet\vendor\phpoffice\spreadsheet\src\phpspeeadsheet\Cell\Cell.php:221 The image that I've attached is the error I get. Please help me.

session_start();
//php_spreadsheet_export.php

include 'C:/xampp/htdocs/phpspreadsheet/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;


$connect = new PDO("mysql:host=localhost;dbname=IDC", "root", " ");

$school=$_SESSION['code'];
$name="name";
$query = "SELECT * FROM SCHOOL WHERE School='".$school."' AND Name!='".$name."'";
$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

if(isset($_POST["export"]))
{
 $file = new Spreadsheet();

 $active_sheet = $file->getActiveSheet();

 $active_sheet->setCellValue('A1', 'Name');
 $active_sheet->setCellValue('B1', 'Phone');
 $active_sheet->setCellValue('C1', 'DOB');
 $active_sheet->setCellValue('D1', 'Father');
 $active_sheet->setCellValue('E1', 'Mother');
 $active_sheet->setCellValue('F1', 'Address');
 $active_sheet->setCellValue('G1', 'Blood');
 $active_sheet->setCellValue('H1', 'Admission');
 $active_sheet->setCellValue('I1', 'Photo link');
 $active_sheet->setCellValue('J1', 'Class');
 $active_sheet->setCellValue('K1', 'Section');
 
 $count = 2;

 foreach($result as $row)
 {
   $active_sheet->setCellValue('A' . $count, $row["Name"]);
   $active_sheet->setCellValue('B' . $count, $row["Phone"]);
   $active_sheet->setCellValue('C' . $count, $row["DOB"]);
   $active_sheet->setCellValue('D' . $count, $row["Father"]);
   $active_sheet->setCellValue('E' . $count, $row["Mother"]);
   $active_sheet->setCellValue('F' . $count, $row["Address"]);
   $active_sheet->setCellValue('G' . $count, $row["Blood"]);
   $active_sheet->setCellValue('H' . $count, $row["Adm_no"]);
   $active_sheet->setCellValue('I' . $count, $row["Photo_link"]);
   $active_sheet->setCellValue('J' . $count, $row["Class"]);
   $active_sheet->setCellValue('K' . $count, $row["Section"]);
   

   $count = $count + 1;
 }

 $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($file, $_POST["file_type"]);

 $file_name = time() . '.' . strtolower($_POST["file_type"]);

 $writer->save($file_name);

 header('Content-Type: application/x-www-form-urlencoded');
 header('Content-Transfer-Encoding: Binary');

 header("Content-disposition: attachment; filename=\"".$file_name."\"");

 readfile($file_name);

 unlink($file_name);

 exit;

}

?> ```

This is the code and it works fine for all data except for this error.

Upvotes: 0

Views: 2635

Answers (1)

Brill Brenhill
Brill Brenhill

Reputation: 11

I got the error solved. The error was that, the numbers had a space at the end and was not matching the data type detected by value binder. So I set the data type as string. Here is the changed code, if it will help anyone who is facing similar trouble.

$active-sheet->setCellValueExplicit( 'B'. $count, $row["phone"],\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);

I got this answer from https://phpspreadsheet.readthedocs.io/en/latest/topics/accessing-cells/

Upvotes: 1

Related Questions