Laravel Import Data From Excel or CSV file by one single function.

If your excel or csv file has less then 26 column then this function will work and return you a clean array of each row of excel or csv.

use PhpOffice\PhpSpreadsheet\IOFactory;

set getDataArrayFromExcel function in helper or whatever you want and pass the excel or csv file to this method getDataArrayFromExcel and dd $data you will found data array.

Function in controller class

I create a helper class getDataArrayFromExcel, You can write your own.

Full code of above picture

public function importStaff(Request $request)
{

    $this->validate($request, [
        'attachments' => 'required|file|mimes:xls,csv,xlsx,txt'
    ]);
    $data = getDataArrayFromExcel($request->file('attachments'));

    /**
     * Found data row, need to use now.
     */
    return back()->with(['alert-type' => 'success', 'message' => 'Staff import successful']);
}
function getDataArrayFromExcel($theFile)
{
    try{
        $spreadsheet = IOFactory::load($theFile->getRealPath());
        $sheet = $spreadsheet->getActiveSheet();
        $highestColumn = $sheet->getHighestDataColumn();
        $highestRow = $sheet->getHighestDataRow();

        $highestColumnInNumeric = 1;
        foreach (range('A', $highestColumn) as $value) {
            $highestColumnInNumeric++;
        }
        $rowArray = array();
        $data = array();
        $firstRowCounter = 1; // only for data key constant variable
        foreach (range(2, $highestRow) as $row) {
            foreach(range(1, $highestColumnInNumeric) as $column) {
                if ($sheet->getCellByColumnAndRow($column, $firstRowCounter)->getValue()){
                    $rowArray [$sheet->getCellByColumnAndRow($column, $firstRowCounter)->getValue()] = $sheet->getCellByColumnAndRow($column, $row)->getValue();
                }
            }
            $data [] = $rowArray;
            $rowArray = [];
        }
    }catch (Exception $e) {
        return back()->with(['alert-type' => 'warning', 'message' => 'There was a problem uploading the data!']);
    }
    return $data;
}

— Shah Md. Iktakhirul Islam, Software Engineer.

Contact Number: 01683201359

Email: iktakhairul@gmail.com

GitHub Profile: https://github.com/iktakhairul