1

Currently, I can get the data coming from excel file with this format.

enter image description here

This is my working code.

        $validator = Validator::make(
        [
            'file'      => $request->file,
            'extension' => strtolower($request->file->getClientOriginalExtension()),
        ],
        [
            'file'          => 'required|max:5000',
            'extension'      => 'required|in:,csv,xlsx,xls',
        ]
    );

    $modal = "active";
    if($validator->fails()){
        return redirect()
        ->back()
        ->with(['errors'=>$validator->errors()->all()])
        ->with('modal',$modal);
    }

    $dateTime = date('Ymd_His');
    $file = $request->file('file');
    $fileName = $dateTime . '-' . $file->getClientOriginalName();
    $savePath = public_path('/upload/projectlist/');
    $file->move($savePath, $fileName);

    $excel = Importer::make('Excel');
    $excel->hasHeader(true);
    $excel->load($savePath.$fileName);
    $collection = $excel->getCollection();

    if(sizeof($collection[1]) == 5)
    {
       $arr = json_decode($collection,true);
            foreach ($arr as $row) {
                $insert_data[] = array(
                    'first_name'  => $row['first_name'],
                    'last_name'  => $row['last_name'],
                    'email'  => $row['email'],
                    'birthdate'  => $row['birthdate']['date'],
                    'created_at'  => now(),
                    'updated_at'  => now(),
                );

            }

        DB::table('tbl_sampleimport')->insert($insert_data);
    }
    else
    {
        return redirect()
        ->back()
        ->with(['errors'=> [0=> 'Please provide date in file according to your format.']])
        ->with('modal',$modal);
    }


    return redirect()->back()
    ->with(['success'=>'File uploaded successfully!'])
    ->with('modal',$modal);

Now that the format was changed. Added some fields with single value. Like Project Name and Project Date I'm trying to get its value. Just want to get the value but not necessary to save it too to the database.

enter image description here

How can I get the value of Project Name and Project Date to declare as a variable. and still be able to save the data of j.doe17 and j.doe18 ?

I'm using cyber-duck as my laravel/excel for import and so on.

Pablo
  • 1,357
  • 1
  • 11
  • 40
  • What is your importer class does ? Is it a custom class that you have made it or it is a library.? – Dhaval Purohit Nov 27 '19 at 06:00
  • Have you used `phpspreadsheet` before? – Dhaval Purohit Nov 27 '19 at 06:00
  • I'm using cyberduck it's a library for laravel/excel. The current file is named as `sample.xlsx` – Pablo Nov 27 '19 at 06:01
  • so what the basic things you can do is have two sheets one is for metadata like Project name and date and another is for data. – Dhaval Purohit Nov 27 '19 at 06:04
  • So you can get the data same as you are parsing before in collection. – Dhaval Purohit Nov 27 '19 at 06:04
  • Otherwise you need to manually go to every rows and columns and do the checks – Dhaval Purohit Nov 27 '19 at 06:05
  • not possible if the excel format is like in the picture? Since I have lot of fields in different cell range to be added soon.. That format is just a sample and will add more when I know how to get the values of specific cells – Pablo Nov 27 '19 at 06:05
  • what not possible having two sheets in one excel or manual thing? – Dhaval Purohit Nov 27 '19 at 06:07
  • one sheet only but all of the fields are there no need to migrate some fields on the other sheets – Pablo Nov 27 '19 at 06:07
  • 1
    I am suggesting you to go for other sheet for metadata in a same excel because if not the you are unable to get your collections as library unable to parse the header and unable to give you the key value pair of data. – Dhaval Purohit Nov 27 '19 at 06:12
  • Do I need to change my library just to get what I want? What library can you suggest – Pablo Nov 27 '19 at 06:16
  • 1
    Any library will have the same limitation. If you need collection then data should be like header row and data rows. I haven't found any solution till now that we can skip five rows and then get the collections. – Dhaval Purohit Nov 27 '19 at 06:20
  • 1
    gonna go for first option to separate sheets. – Pablo Nov 27 '19 at 06:22
  • Cool @RaeIan if any one has the solution for what i mentioned above then they will give the answer otherwise separating sheets would be the best option. – Dhaval Purohit Nov 27 '19 at 06:23
  • my problem now, how to get the collection of specific sheet since cyberduck documentation doesn't include per sheet.. – Pablo Nov 27 '19 at 06:39
  • You should have `setSheet` method as shown here https://packagist.org/packages/cyber-duck/laravel-excel – Dhaval Purohit Nov 27 '19 at 06:41

1 Answers1

0

Solution suggested by @Dhaval Purohit

create sheets and define setSheet($sheetNumber).

enter image description here

So I tried to create new sheet in the same excel file with this format only

enter image description here

and this will give me like this

[{
      "project_name":"Creator Doe",
      "project_date":"7/9/2019"
}]
Pablo
  • 1,357
  • 1
  • 11
  • 40