[FIXED] How to change array header and insert the array into MySQL database in PHPExcel?

Issue

I have a piece of code for making an Excel Import (.xlsx). I have a question about inserting iterated array and changing the array header.

controller:

$this->load->library('Excel');
$file = 'upload/keuangan.xlsx';

//read file from path
$objPHPExcel = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel->setReadDataOnly(true);

$objPHPExcel = $objPHPExcel->load($file);
$objWorksheet = $objPHPExcel->getActiveSheet();

$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //dari 0

$data['nomor'] = $this->participant_model->get_dummy(false)->result_array();
$num = $this->participant_model->get_dummy(false)->num_rows();

for($row=2; $row <= $highestRow; ++$row){
    for($col=0; $col < $highestColumnIndex; ++$col){
        $ExcelData[$col] = $objWorksheet->getCellByColumnAndRow($col,$row)->getValue();
    }
    echo "<pre>";print_r($ExcelData);echo "</pre>";
    $this->session->set_flashdata('message_alert','<div class="alert alert-success">Data berhasil dimasukkan</div>');
    $this->participant_model->save($ExcelData);
}

model :

public function save($data_participant){
    $this->db->insert('t_keuangan',$data_participant);
}

This is keuangan.xlsx, it consists a string header. I select from second row. The column may be expanded.

No Ijazah   Jumlah      Keluar 1    Keluar 2    Keluar 3
1234/SH     100000000   21000000    19000000    18000000
2345/SK     120000000   16000000    19000000    13000000
1245/SA     140000000   20000000    15000000    25000000

The result of $ExcelData is :

Array
(
    [0] => 1234/SH
    [1] => 100000000
    [2] => 21000000
    [3] => 19000000
    [4] => 18000000
)
Array
(
    [0] => 2345/SK
    [1] => 120000000
    [2] => 16000000
    [3] => 19000000
    [4] => 13000000
)
Array
(
    [0] => 1245/SA
    [1] => 140000000
    [2] => 20000000
    [3] => 15000000
    [4] => 25000000
)

I'm trying to put this $ExcelData into my database MySQL. I can insert $ExcelData into my database but the header is still in numerical ([0],[1],[2],[3]), because $row and $col that used by $getCellByColumnAndRow are numeric.

How to insert these arrays into database after the header are changed into

Array
(
    [no_ijazah] => 1245/SA
    [jumlah] => 140000000
    [keluar_1] => 20000000
    [keluar_3] => 15000000
    [keluar_4] => 25000000
)

?

Solution

Hope this will help you:

You can change your $ExcelData data like this to insert into the database;

See working example : https://eval.in/1026123

Use array_combine to add key value pair like this:

$value1 = Array
(
    '0' => '1234/SH',
    '1' => 100000000,
    '2' => 21000000,
    '3' => 19000000,
    '4' => 18000000
);
$value2 = Array
(
    '0' => '2345/SK',
    '1' => 120000000,
    '2' => 16000000,
    '3' => 19000000,
    '4' => 13000000
);



$key = array('no_ijazah','jumlah','keluar_1','keluar_3','keluar_4');

$combined[] = array_combine($key, $value1);
$combined[] = array_combine($key, $value2);.

print_r($combined);
$this->participant_model->save($combined);

Output

Array
(
    [0] => Array
        (
            [no_ijazah] => 1234/SH
            [jumlah] => 100000000
            [keluar_1] => 21000000
            [keluar_3] => 19000000
            [keluar_4] => 18000000
        )

    [1] => Array
        (
            [no_ijazah] => 2345/SK
            [jumlah] => 120000000
            [keluar_1] => 16000000
            [keluar_3] => 19000000
            [keluar_4] => 13000000
        )
)

Use insert_batch to insert the data into table

$this->db->insert_batch('t_keuangan',$combined);

Whole code should be like this :

$key = array('no_ijazah','jumlah','keluar_1','keluar_3','keluar_4');
for($row=2; $row <= $highestRow; ++$row)
{
    for($col=0; $col < $highestColumnIndex; ++$col)
    {
        $ExcelData[$col] = $objWorksheet->getCellByColumnAndRow($col,$row)->getValue();
    }

    $combined[] = array_combine($key, $ExcelData);
}
$this->session->set_flashdata('message_alert','<div class="alert alert-success">Data berhasil dimasukkan</div>');
$this->participant_model->save($combined);

model function should be like this :

public function save($data_participant)
{
    $this->db->insert_batch('t_keuangan',$data_participant);
}

For more : http://php.net/manual/en/function.array-combine.php

Answered By - Pradeep

Answer Checked By - Mildred Charles (PHPFixing Admin)