-1

I wan't to import a xlsx file into my mysql database. For it I use the library nuovo spreadsheet reader: https://github.com/nuovo/spreadsheet-reader/ I can import into my database the simple xlsx file in this site: https://phppot.com/php/import-excel-file-into-mysql-database-using-php/

But when I MODIFY the file for import a player, the upload is done but there are nothing in the database. I don't understand why.

The code for read and inject the data in the database:


namespace App\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use App\Service\FileUploader;
use Psr\Log\LoggerInterface;
use SpreadsheetReader_XLSX;


/**
 * @Route("/file")
 */
class UploadController extends AbstractController
{
/**
 * @Route("/upload", name="upload_player")
 */
public function indexUpload()
{
    return $this->render('upload/form.html.twig', [
        'controller_name' => 'UploadController',
    ]);
}

    /**
     * @Route("/doUpload", name="doUpload")
     */
    public function index(Request $request, string $uploadDir, FileUploader $uploader, LoggerInterface $logger)
    {
        $token = $request->get("token");

        if (!$this->isCsrfTokenValid('upload', $token)) 
        {
            $logger->info("CSRF failure");

            return new Response("Operation not allowed",  Response::HTTP_BAD_REQUEST,
                ['content-type' => 'text/plain']);
        }        

        $file = $request->files->get('myfile');

        if (empty($file)) 
        {
            return new Response("No file specified",  
               Response::HTTP_UNPROCESSABLE_ENTITY, ['content-type' => 'text/plain']);
        }        

        $filename = $file->getClientOriginalName();
        $uploader->upload($uploadDir, $file, $filename);



        $conn = mysqli_connect("localhost","root","db_password","db_name");

        $targetPath = '../var/uploads/'.$filename;
            $Reader = new SpreadsheetReader_XLSX($targetPath);

            $sheetCount = count($Reader->sheets());
            for($i=0;$i<$sheetCount;$i++)
            {

                $Reader->ChangeSheet($i);

                foreach ($Reader as $Row)
                {

                    $lastname = "";
                    if(isset($Row[0])) {
                        $lastname = mysqli_real_escape_string($conn,$Row[0]);
                    }

                    $firstname = "";
                    if(isset($Row[1])) {
                        $firstname = mysqli_real_escape_string($conn,$Row[1]);
                    }

                    $picture = "";
                    if(isset($Row[2])) {
                        $picture = mysqli_real_escape_string($conn,$Row[2]);
                    }

                    $birthday = "";
                    if(isset($Row[3])) {
                        $birthday = mysqli_real_escape_string($conn,$Row[3]);
                    }

                    $nationality = "";
                    if(isset($Row[4])) {
                        $nationality = mysqli_real_escape_string($conn,$Row[4]);
                    }

                    $address = "";
                    if(isset($Row[5])) {
                        $address = mysqli_real_escape_string($conn,$Row[5]);
                    }

                    $postal = "";
                    if(isset($Row[6])) {
                        $postal = mysqli_real_escape_string($conn,$Row[6]);
                    }

                    $city = "";
                    if(isset($Row[7])) {
                        $city = mysqli_real_escape_string($conn,$Row[7]);
                    }

                    $Email = "";
                    if(isset($Row[8])) {
                        $Email = mysqli_real_escape_string($conn,$Row[8]);
                    }

                    $jerseyNumber = "";
                    if(isset($Row[9])) {
                        $jerseyNumber = mysqli_real_escape_string($conn,$Row[9]);
                    }

                    $footShoot = "";
                    if(isset($Row[10])) {
                        $footShoot = mysqli_real_escape_string($conn,$Row[10]);
                    }

                    $shoeSize = "";
                    if(isset($Row[11])) {
                        $shoeSize = mysqli_real_escape_string($conn,$Row[11]);
                    }

                    $jerseySize = "";
                    if(isset($Row[12])) {
                        $jerseySize = mysqli_real_escape_string($conn,$Row[12]);
                    }

                    $shortSize = "";
                    if(isset($Row[13])) {
                        $shortSize = mysqli_real_escape_string($conn,$Row[13]);
                    }

                    $licenceNumber = "";
                    if(isset($Row[14])) {
                        $licenceNumber = mysqli_real_escape_string($conn,$Row[14]);
                    }

                    $mobilePhone = "";
                    if(isset($Row[15])) {
                        $mobilePhone = mysqli_real_escape_string($conn,$Row[15]);
                    }

                    $fixPhone = "";
                    if(isset($Row[16])) {
                        $fixPhone = mysqli_real_escape_string($conn,$Row[16]);
                    }

                    $note = "";
                    if(isset($Row[17])) {
                        $note = mysqli_real_escape_string($conn,$Row[17]);
                    }

                    $team = "";
                    if(isset($Row[19])) {
                        $team = mysqli_real_escape_string($conn,$Row[19]);
                    }

                    $categories = "";
                    if(isset($Row[20])) {
                        $categories = mysqli_real_escape_string($conn,$Row[20]);
                    }

                    if (!empty($lastname) || !empty($firstname)) {

                        $query = "insert into player(lastname,firstname, picture, birthday, nationality, address, postal, city, Email, jerseyNumber, footShoot, shoeSize, jerseySize, shortSize, licenceNumber, mobilePhone, fixPhone, note, team, categories) values('".$lastname."','".$firstname."','".$picture."','".$birthday."','".$nationality."','".$nationality."','".$address."','".$postal."','".$city."','".$Email."','".$jerseyNumber."','".$footShoot."','".$shoeSize."','".$jerseySize."','".$shortSize."','".$licenceNumber."','".$mobilePhone."','".$fixPhone."','".$note."','".$team."','".$categories."')";

                        $result = mysqli_query($conn, $query);

                        if (! empty($result)) {
                            $type = "success";
                            $message = "Excel Data Imported into the Database";
                        } else {
                            $type = "error";
                            $message = "Problem in Importing Excel Data";
                        }
                    }
                 }

                }


        return new Response("File uploaded",  Response::HTTP_OK, ['content-type' => 'text/plain']);         

            }
        }

I have the response "file uploaded" but not the data in my database.

dani45
  • 1
  • 3
  • What do you mean by "not the data in my database"? Is the row missing or are values in it blank? On side notes: 1) you're wide open to [SQL injection](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) and 2) you really shouldn't be executing all that logic in the controller, you should create services to do the work and then just call them in your controller actions. – El_Vanja Mar 14 '20 at 18:05
  • With a xlsx file, i have this error: XMLReader::open(): Empty string supplied as input. With a xls file, i have this error : iconv(): Detected an incomplete multibyte character in input string. First, I wan't make it working, and then I will see to ameliorate my code. – dani45 Mar 15 '20 at 22:21

1 Answers1

0

finally I changed the library because I can't use numbers. I changed to PHPOFFICE. Now everything is working. My code is not the best and is only for dev but it works:


namespace App\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use App\Service\FileUploader;
use Exception;


/**
 * @Route("/file")
 */
class UploadController extends AbstractController
{
/**
 * @Route("/upload", name="upload_player")
 */
public function indexUpload()
{
    return $this->render('upload/form.html.twig', [
        'controller_name' => 'UploadController',
    ]);
}

    /**
     * @Route("/doUpload", name="doUpload")
     */
    public function index(Request $request, string $uploadDir, FileUploader $uploader)
    {


        $file = $request->files->get('myfile');

        if (empty($file)) 
        {
            return new Response("No file specified",  
               Response::HTTP_UNPROCESSABLE_ENTITY, ['content-type' => 'text/plain']);
        }        

        $inputFileName = $file->getClientOriginalName();
        $uploader->upload($uploadDir, $file, $inputFileName);

        $targetPath = '../var/uploads/'.$inputFileName;



        $conn = mysqli_connect("localhost","db_user","db_password","db_name");



        /**  Identify the type of $inputFileName  **/
        $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($targetPath);

        /**  Create a new Reader of the type that has been identified  **/
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

        /**  Load $inputFileName to a Spreadsheet Object  **/
        $spreadsheet = $reader->load($targetPath);

        $worksheet = $spreadsheet->getActiveSheet();

        $highestRow = $worksheet->getHighestRow(); // total number of rows

        $highestColumn = $worksheet->getHighestColumn(); // total number of columns

        $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5



        if ($highestRow <= 0) {
            Exit ('There is no data in the Excel table');
        }


        for ($row = 1; $row <= $highestRow; ++$row) {

            $lastname = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(1, $row)->getFormattedValue());
            $firstname = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(2, $row)->getFormattedValue());
            $picture = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(3, $row)->getFormattedValue());
            $birthday = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(4, $row)->getFormattedValue());
            $nationality = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(5, $row)->getFormattedValue());
            $address = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(6, $row)->getFormattedValue());
            $postal = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(7, $row)->getFormattedValue());
            $city = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(8, $row)->getFormattedValue());
            $email = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(9, $row)->getFormattedValue());
            $jersey_number = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(10, $row)->getFormattedValue());
            $foot_shoot = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(11, $row)->getFormattedValue());
            $shoe_size = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(12, $row)->getFormattedValue());
            $jersey_size = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(13, $row)->getFormattedValue());
            $short_size = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(14, $row)->getFormattedValue());
            $licence_number = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(15, $row)->getFormattedValue());
            $mobile_phone = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(16, $row)->getFormattedValue());
            $fix_phone = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(17, $row)->getFormattedValue());
            $note = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(18, $row)->getFormattedValue());

/*with foreign key:*/
            $team = ("SELECT id FROM team WHERE name = '". mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(19, $row)->getFormattedValue())."'"); 
            $category = ("SELECT id FROM categories WHERE type = '". mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(20, $row)->getFormattedValue())."'"); 

            $query = "insert into player(lastname, firstname, picture, birthday, nationality, address, postal, city, email, jersey_number, foot_shoot, shoe_size, jersey_size, short_size, licence_number, mobile_phone, fix_phone, note, team_id, categories_id) VALUES ('".$lastname."','".$firstname."','".$picture."','".$birthday."','".$nationality."','".$address."','".$postal."','".$city."','".$email."','".$jersey_number."','".$foot_shoot."','".$shoe_size."','".$jersey_size."','".$short_size."','".$licence_number."','".$mobile_phone."','".$fix_phone."','".$note."',(".$team."),(".$category."))";

        try {
            $result = mysqli_query($conn, $query);
            if ($result == true){
                echo 'OK ';
            }else{
                var_dump($result) . var_dump($conn) . var_dump($query);
            }

        } catch (Exception $e) {
        echo $e->getMessage();
        }
    }
        return new Response(" File uploaded",  Response::HTTP_OK, ['content-type' => 'text/plain']);  

    }
    }```
dani45
  • 1
  • 3