时间戳

首页 > 解决方案 > 如何将特定的单元格值导入 Laravel excel 中的模型

php - 如何将特定的单元格值导入 Laravel excel 中的模型

问题描述

我想将电子表格数据导入 sql 数据库，这是我的电子表格， 要导入的电子表格

这是我的视图和控制器，

<?php

namespace App\Http\Controllers;

use App\DailyAttendance;
use Illuminate\Http\Request;
use App\Exports\DailyAttendanceExport;
use App\Imports\DailyAttendanceImport;
use Maatwebsite\Excel\Facades\Excel;


class DailyAttendanceController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $dailyAttendance = DailyAttendance::all();

        return view('daily_attendance.index')->with('dailyAttendance', $dailyAttendance);
    }


     /**
     * Import function
     */
    public function import(Request $request)
    {
        if ($request->file('imported_file')) {

            $import = new DailyAttendanceImport();
            $import->onlySheets('Logs');
            Excel::import($import, request()->file('imported_file'));
            return back();
        }
    }
<form id="upload_excel" method="post" enctype="multipart/form-data" action="{{ route('dailyAttendance.import') }}" autocomplete="off">
                                    @csrf                                                       
                                    @method('post')                                                               
                                    <input type="file" name="imported_file" required>         
                            <button style="margin-left: 10px;" class="btn btn-info" type="submit">Upload attendance</button>                        
                            </form>

这也是我的模型课，

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class DailyAttendance extends Model
{
   /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'emp_id', 'date','start','end',
    ];
}

我还创建了导入类，

<?php

namespace App\Imports;

use App\DailyAttendance;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class DailyAttendanceImport implements ToModel, WithHeadingRow
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        //
    }
}


// change this to import data to an array instead of db...........

但根据文档，laravel excel 文档表值只能作为行导入，例如

return new DailyAttendance([
            'emp_id' => $row['emp_id'],
            'date' => $row['date'],
            'start' => $row['start'],
            'end' => $row['end'],
        ]);

由于我的 excel 表格格式，我想通过他们的单元格地址捕获值，谁能告诉我如何将单元格值导入模型，谢谢！

标签： phphtmllaravelmaatwebsite-excellaravel-excel

解决方案

Meabe，你需要运行一些宏（vba）来清理工作表日志......

文件清理 - 示例

并导入它...

宏代码。（测试）

Sub PrepararInfo()
    Dim x As Integer
      ' Establecer numrows = número de filas de datos.
      NumRows = InputBox("Ingrese cantidad", "Numero de Filas")
      ' Seleccionar celda a1.
      Range("A1").Select


      If True Then


        For x = 1 To NumRows

           ' Selecciona la celda 1 fila por debajo de la celda activa.
            ' MsgBox (ActiveCell.Value)
           If x < 6 Then
                If x = 4 Then
                    ActiveCell.Offset(1, 0).Select
                Else
                    ActiveCell.EntireRow.Delete
                End If

           Else
                If ActiveCell.Value = "No :" Or ActiveCell.Value = "1" Then

                   ActiveCell.EntireRow.Delete
                Else
                   ActiveCell.Offset(1, 0).Select
                End If

           End If


        Next

      End If
End Sub

你会导入（文件清理）到数组...

$data = Excel::toArray([], $request->file('select_file'));

并手动应用过滤器..等..等..

推荐阅读