PHPSpreadsheet Issue with Excel to MariaDB

Question

I want to upload data from cells from a excel template to my mariadb lamp stack. I am using PHPSpreadsheet to read the cells. i got some problems with the library syntax. the server connection/db connections is working fine and i can insert some data to my tables by addressing specific paths of files. but i want to use the input type file for this.

thanks for any help and ideas

my code:

<?php
session_start();

require_once "db_connect.php";
require 'PhpSpreadsheet/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Reader\Ods\BaseReader;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

if ($_FILES["file"]["name"] != '') {
    $allowed_extension = array('xls', 'xlsx');
    $file_array = explode(".", $_FILES['file']['name']);
    $file_extension = end($file_array);
    if (in_array($file_extension, $allowed_extension)) {

        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('xlsx');
        $spreadsheet = $reader->load($_FILES['file']['name']);
        $data[1] = $spreadsheet->getActiveSheet()->getCell('K10')->getcalculatedValue();
        $data[2] = $spreadsheet->getActiveSheet()->getCell('K4')->getcalculatedValue();
        $data[3] = $spreadsheet->getActiveSheet()->getCell('K5')->getcalculatedValue();
        $data[4] = $spreadsheet->getActiveSheet()->getCell('K6')->getcalculatedValue();
        $data[5] = $spreadsheet->getActiveSheet()->getCell('K7')->getcalculatedValue();
        $data[6] = $spreadsheet->getActiveSheet()->getCell('K8')->getcalculatedValue();
        $data[7] = $spreadsheet->getActiveSheet()->getCell('K9')->getcalculatedValue();
        $data[8] = $spreadsheet->getActiveSheet()->getCell('N9')->getcalculatedValue();
        $data[9] = $spreadsheet->getActiveSheet()->getCell('Y4')->getcalculatedValue();
        $data[10] = $spreadsheet->getActiveSheet()->getCell('U5')->getcalculatedValue();
        $data[11] = $spreadsheet->getActiveSheet()->getCell('AA9')->getcalculatedValue();
        $data[12] = $spreadsheet->getActiveSheet()->getCell('AA10')->getcalculatedValue();
        $data[13] = $spreadsheet->getActiveSheet()->getCell('AA11')->getcalculatedValue();
        $data[14] = "1";
        $data[15] = $spreadsheet->getActiveSheet()->getCell('S7')->getcalculatedValue();
        $data[16] = $spreadsheet->getActiveSheet()->getCell('S8')->getcalculatedValue();
        $data[17] = $spreadsheet->getActiveSheet()->getCell('K15')->getcalculatedValue();
        $data[18] = $spreadsheet->getActiveSheet()->getCell('N15')->getcalculatedValue();
        $data[19] = $spreadsheet->getActiveSheet()->getCell('P15')->getcalculatedValue();
        $data[20] = $spreadsheet->getActiveSheet()->getCell('S15')->getcalculatedValue();
        $data[21] = $spreadsheet->getActiveSheet()->getCell('U15')->getcalculatedValue();
        $data[22] = $spreadsheet->getActiveSheet()->getCell('X15')->getcalculatedValue();
        $data[23] = $spreadsheet->getActiveSheet()->getCell('Z15')->getcalculatedValue();
        $data[24] = $spreadsheet->getActiveSheet()->getCell('AC15')->getcalculatedValue();
        $data[25] = $spreadsheet->getActiveSheet()->getCell('K14')->getcalculatedValue();
        $data[26] = $spreadsheet->getActiveSheet()->getCell('P14')->getcalculatedValue();
        $data[27] = $spreadsheet->getActiveSheet()->getCell('U14')->getcalculatedValue();
        $data[28] = $spreadsheet->getActiveSheet()->getCell('Z14')->getcalculatedValue();

        $sql_insert = "
 INSERT INTO 
          auftrag (...)
  VALUES
      ('$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]', '$data[7]', '$data[8]', '$data[9]', '$data[10]', '$data[11]', '$data[12]', '$data[13]', '$data[14]', '$data[15]', '$data[16]', '$data[17]', '$data[18]', '$data[19]', '$data[20]', '$data[21]', '$data[22]', '$data[23]', '$data[24]', '$data[25]', '$data[26]', '$data[27]', '$data[28]')
 ";
   
        print_r($data);
        try {
            $stmt = $pdo->prepare($sql_insert);
            $stmt->execute($data);
            echo "OK<br>";
        } catch (Exception $ex) {
            echo $ex->getMessage() . "<br>";
        }
    }
}

header("location:home.php");
?>

To troubleshoot this, get your php program to print_r your `$sql_insert` statement, examine it carefully, and even run it in a SQL client program like phpmyadmin or heidisql. — O. Jones, Oct 26 '21 at 10:21
$sql_insert already worked by inserting from files i wrote the path in IOFactory::createReader('path'); i think i used IOFactory::load(...); there — alb, Oct 26 '21 at 10:30

Slava Rozhnev · Answer 1 · 2021-10-26T12:07:55.453

0

You should to change prepared query from:

$sql_insert = "
 INSERT INTO auftrag (...)
 VALUES ('$data[1]', .... '$data[28]')
 ";

to

$sql_insert = "INSERT INTO auftrag (
    field1,
    ...
    field28
 ) VALUES (?, .... ?)"; // 28 placeholders

and $stmt->execute($data); will apply data instead the placeholders and insert it to DB

edited Oct 26 '21 at 12:07

answered Oct 26 '21 at 11:16

Slava Rozhnev

9,510
6
23
39

you mean placeholders for the CellValues? Or Placeholders for the $data[ n ]? – alb Oct 26 '21 at 11:49
Placeholders for $data[x] – Slava Rozhnev Oct 26 '21 at 12:08
ok i will try, but what will this act change afterall? – alb Oct 26 '21 at 12:10

PHPSpreadsheet Issue with Excel to MariaDB

1 Answers1