导入包含与数据库字段不同的标头的Excel文档 - Laravel 5.2

Trying to import an excel spreadsheet with custom headers (i.e. human readable) and match those to database fields for the excel import.

尝试导入带有自定义标题(即人类可读)的Excel电子表格,并将其与excel导入的数据库字段进行匹配。

Example: Column Header is: Region (ex. North America)
Database Column header is: region_code

示例:列标题为:Region(例如,North America)数据库列标题为:region_code

Any idea how to do this using maatwebsite/excel package and an Eloquent model?

知道如何使用maatwebsite / excel包和Eloquent模型吗?

1 个解决方案

#1

There are several ways you can approach this. By default, maatwerk-excel package converts the headers to slugs.

有几种方法可以解决这个问题。默认情况下,maatwerk-excel包将标头转换为slugs。

Note: by default these attributes will be converted to a slug. You can change the default inside the config excel::import.heading. Available options are: true|false|slugged|ascii|numeric|hashed|trans|original

注意:默认情况下,这些属性将转换为slug。您可以更改config excel :: import.heading中的默认值。可用选项包括:true | false | slugged | ascii | numeric | hashed | trans | original

True and slugged will be converted to ASCII as well when excel::import.to_ascii is set to true. You can change the default separator as well inside the config.

当excel :: import.to_ascii设置为true时,True和slugged也将转换为ASCII。您也可以在配置中更改默认分隔符。

source

You will be able to address the row by the slug, so Region would become region. You could see if there is a setting that suits you.

您将能够通过slug来处理行,因此Region将成为区域。您可以看到是否有适合您的设置。

Loop, map and save

Complexity depending on if you have multiple sheets or not, you can loop through everything and save it on the Eloquent model per field by mapping it as Mark Baker mentioned in the comment on your question. I don't know if the file gets uploaded or if you grab it from your local file system, so I took the file system option for my example:

复杂性取决于您是否有多张工作表,您可以循环浏览所有内容并将其保存在每个字段的Eloquent模型上,方法是将其映射为Mark Ba​​ker在您的问题评论中提到的。我不知道文件是否上传或者你是否从本地文件系统中获取文件,所以我为我的例子选择了文件系统选项:

Excel::load(storage_path('app/public/excel-import.xlsx'), function($reader) {

    $results = $reader->get();
    foreach($results as $result) {
        // Your model namespace here
        $model = new \App\ImportItem();
        $model->region_code = $result->region;
        $model->numeric_code = $result->code;
        $model->testfield = $result->test_field;
        $model->save();
    }
});

This isn't really easy to maintain when you have a lot of Excel files with a lot of fields, but if they are few and aren't prone to change, it could be a solution.

当您拥有大量具有大量字段的Excel文件时,这并不是很容易维护,但如果它们很少并且不易更改,则可能是一种解决方案。

Mass Assign

Another option is changing all the headers in the excel (If this even is an option) and then Mass Assign your models by replacing the content of the foreach shown above by something like:

另一种选择是更改excel中的所有标题(如果这是一个选项),然后通过用以下内容替换上面显示的foreach的内容来批量分配模型:

$model = App\ImportItem::create($result->toArray()); 

Be aware though, that all Eloquent models by default are protected against Mass Assignment. Look into the documentation to see whether you want to set attributes fillable or guarded (Take note of the security implication mentioned). I noticed sometimes there is a field 0 => null in the items array of the CellCollection on some files to import (apparently an empty column), so you can also call it like

但请注意,默认情况下所有Eloquent模型都受到Mass Assignment的保护。查看文档以查看是否要设置可填充或保护的属性(请注意所提到的安全隐含)。我注意到有些在要导入的某些文件的CellCollection的items数组中有一个字段0 => null(显然是一个空列),所以你也可以调用它

$reader->ignoreEmpty()->get()

Keep in mind though, that it could give unwanted results on other empty fields, so you could strip it in another way too.

但请记住,它可能会在其他空字段上产生不需要的结果,因此您也可以用其他方式剥离它。

Now, if you don't want to rename all columns in the Excel, you could also define mutators in your model, but I do not think that would be appropriate in this situation either. An example:

现在,如果您不想重命名Excel中的所有列,您也可以在模型中定义mutators,但我认为在这种情况下也不适合。一个例子:

class ImportItem extends Model
{
    protected $table = 'importitems';
    protected $fillable = [
        'region_code',
    ];

    /* 
     * If you have a field region in the Mass Assignment
     * and a field region_code in your database.
     */
    public function setRegionAttribute($value)
    {
        $this->attributes['region_code'] = $value;
    }
    // Same for each attribute! Ugh what a mess!

Maybe I wrote all this for nothing and there is a much easier and quicker way implemented into the package and/or Laravel itself for mapping like this, sorry for wasting your time in that case.

也许我一无所知地写了这一切,并且有一个更容易和更快的方式实现到包和/或Laravel本身用于这样的映射,抱歉在这种情况下浪费你的时间。