I am trying to import excel using laravel excel package with formulated columns like dropdown of classes and phone number format, reference attached. Any help would be appreciated. But without formulas its working fine.
thanks.
Here is my controller.
<?php
namespace App\Http\Controllers;
use App\Imports\FeeVoucherUpload;
use Carbon\Carbon;
use Illuminate\Http\Request;
use File;
use Illuminate\Support\Facades\Storage;
use Maatwebsite\Excel\Facades\Excel;
class ImportFeeVoucherController extends Controller
{
public function index()
{
$data['schools'] = array();
$data['branches'] = array();
$data['panaltyType'] = array();
return view('import-fee-voucher.list', $data);
}
public function importExcel(Request $request)
{
$validateDate = $request->validate([
'feeVoucherExcelFile' => 'required|mimes:xls,xlsx'
]);
$name = $request->file('feeVoucherExcelFile')->getClientOriginalName();
$ext = $request->file('feeVoucherExcelFile')->getClientOriginalExtension();
if($ext != "xls" && $ext != "xlsx")
abort(400, 'Given data is invalid, Only csv, xlsx, xls format is acceptable.');
$fileName = Storage::disk('public')->putFileAs('sheets', $request->file('feeVoucherExcelFile'), $name);
$filePath = Storage::disk('public')->path($fileName);
$studentsData = [];
$studentsData = Excel::toArray(new FeeVoucherUpload(), $validateDate['feeVoucherExcelFile']);
$schoolID = $request->schoolID ?? session()->get('user')->schoolID;
$branchID = $request->branchID ?? session()->get('user')->branchID;
$penaltyType = $request->penaltyType;
$penaltyValue = $request->penaltyValue;
$voucherNote = $request->voucherNote;
$batchNo = Carbon::now()->format('Ymdhis');
$records = array();
$flag = true;
$heads = [];
foreach($studentsData[0] as $key => $row){
if($flag) {
foreach($row as $key => $value){
$heads[] = $value;
}
$flag = false;
continue;
}
$data = array();
$data['schoolID'] = $schoolID;
$data['branchID'] = $branchID;
$data['penaltyType'] = $penaltyType;
$data['penaltyValue'] = $penaltyValue;
$data['voucherNote'] = $voucherNote;
$data['studentName'] = trim($row[0]);
$data['classLevelName'] = trim($row[1]);
$data['classSection'] = trim($row[2]);
$data['rollNo'] = trim($row[3]);
$data['fatherMobileNo'] = trim($row[4]);
$data['motherMobileNo'] = trim($row[5]);
$data['periodName'] = trim($row[6]);
$data['feeAmount'] = trim($row[7]);
$data['invoiceNumber'] = trim($row[8]);
$data['invoiceDate'] = trim($row[9]);
$data['dueDate'] = trim($row[10]);
$data['endDate'] = trim($row[11]);
$data['batchNo'] = $batchNo;
$records['data'][] = $data;
}
if(File::exists($filePath)){
unlink($filePath);
}
return response()->json($records);
}
}
`
and my import file. In my import file i am justing getting data from excel and convert collection into array.
<?php
namespace App\Imports;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\SkipsEmptyRows;
use Maatwebsite\Excel\Concerns\ToCollection;
class FeeVoucherUpload implements ToCollection, SkipsEmptyRows
{
/**
* @param Collection $collection
*/
public function collection(Collection $collection)
{
return $collection->toArray();
}
}
