Hi
Here is my piece of code for uploading data into a table from xlsx file.
Few simple things for this:
- i use the 1st line to store the column names
- for my uploads the 1st column must always be populated
I have set up namespaces for my CI3 so i can use "use" tags before the class declaration in my controller/model
PHP Code:
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
PHP Code:
$file = '/path/to/your/file.xlsx';
// Instantiate new reader
$reader = new Xlsx();
// Set to read data only
$reader->setReadDataOnly(true);
// Get the active sheet (without calculating functions and without formatting)
$asheet = $reader->load($file)->getActiveSheet()->toArray(null, false, false, false);
// Set up arrays for insert ID's and header
$header = [];
// Counter used for rows in foreach loop
$c = 0;
// Start DB transaction
$this->db->trans_start();
// Loop through the active sheet
foreach ($asheet as $a)
{
// for some reason we get a lot of empty arrays so we should only consider ones that
// contain actual data (1st cell can't be blank for these uploads).
if (!empty($a[0]))
{
// The first row is always the column headers
if ($c == 0)
{
$header = $a;
}
else
{
// combine header and row data to get associative array for db insert
$temp = array_combine($header, $a);
$this->db->insert($table, $temp);
unset($temp);
}
$c ++;
}
}
$this->db->trans_complete();
