使用 Laravel-Excel 选择工作表

Selecting a sheet using Laravel-Excel

提问by haakym

I'm using the very useful https://github.com/Maatwebsite/Laravel-Excelpackage.

我正在使用非常有用的https://github.com/Maatwebsite/Laravel-Excel包。

As I liked the idea of keeping my controllers clear of the excel import code, I'm loading the uploaded file using ExcelFile injections, see here: http://www.maatwebsite.nl/laravel-excel/docs/import#injection

由于我喜欢让我的控制器远离 excel 导入代码的想法，我正在使用 ExcelFile 注入加载上传的文件，请参见此处：http: //www.maatwebsite.nl/laravel-excel/docs/import#injection

This is my code for the ExcelFile Injection:

这是我的 ExcelFile 注入代码：

StudentImport.php

学生导入.php

namespace App\Excel;

class StudentImport extends \Maatwebsite\Excel\Files\ExcelFile {

    public function getFile()
    {
        return \Input::file('student_file');
    }

    public function getFilters()
    {
        return [];
    }

}

However, my problem is that I don't understand where I run methods like: ->selectSheets('mysheet')when using this approach.

但是，我的问题是我不明白我在哪里运行方法，例如：->selectSheets('mysheet')使用这种方法时。

My current work around is doing the following in my controller after using the ExcelFile injection to grab the file.

我目前的工作是在使用 ExcelFile 注入获取文件后在我的控制器中执行以下操作。

ExcelController.php

ExcelController.php

public function import(StudentImportFormRequest $request, StudentImport $import)
{
    $results = $import->get();

    foreach($results as $sheet) {
        $sheetTitle = $sheet->getTitle();

        if($sheetTitle === 'students') {
            foreach($sheet as $row) {
                // do something
            }
        }
    }

}

I believe I may need to extend the ExcelFile class and add a new method which will be a wrapper around the selectSheets() method or add it in to the loadFile() method somehow - this seems to be the place where the filters and settings are set up so I guess this is where you might add in a selection of a specific sheet?

我相信我可能需要扩展 ExcelFile 类并添加一个新方法，该方法将作为 selectSheets() 方法的包装器或以某种方式将其添加到 loadFile() 方法中 - 这似乎是过滤器和设置所在的地方设置所以我想这是您可以添加特定工作表的选择的地方？

Also, I would like to know how to set certain columns to strings as they currently being read as numbers. Currently I have text being output as floats (i.e. they have a following decimal point!), when I dd() the first and only row without using a Value Binder I get the following:

另外，我想知道如何将某些列设置为字符串，因为它们当前被读取为数字。目前，我将文本输出为浮点数（即它们有一个小数点！），当我 dd() 第一行也是唯一一行而不使用值绑定器时，我得到以下信息：

CellCollection {#862 ▼
  #title: null
  #items: array:8 [▼
    "name" => "John Smith"
    "refno" => "s123"
    "nid" => 1234567890.0
    "birth_date" => Carbon {#861 ▼
      +"date": "1971-01-05 00:00:00.000000"
      +"timezone_type": 3
      +"timezone": "UTC"
    }
    "is_active" => 1.0
    "course_title" => "Computer Science"
    "institution_id" => 1.0
    "course_id" => 1.0
  ]
}

I've tried implementing a ValueBinder as mentioned in the docs: http://www.maatwebsite.nl/laravel-excel/docs/import#formattingbut have had no success yet. I only have one column which needs to be read as a date, the rest should be read as text and the following code doesn't work:

我已经尝试实现文档中提到的 ValueBinder：http: //www.maatwebsite.nl/laravel-excel/docs/import#formatting但还没有成功。我只有一列需要作为日期读取，其余的应该作为文本读取，以下代码不起作用：

namespace App\Excel;

use PHPExcel_Cell;
use PHPExcel_Cell_DataType;
use PHPExcel_Cell_IValueBinder;
use PHPExcel_Cell_DefaultValueBinder;

class StudentValueBinder extends PHPExcel_Cell_DefaultValueBinder implements PHPExcel_Cell_IValueBinder
{
    public function bindValue(PHPExcel_Cell $cell, $value = null)
    {
        if ($cell->getColumn() !== 'D') {

            $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);

            return true;
        }

        // else return default behavior
        return parent::bindValue($cell, $value);
    }
}

Any advice would be most appreciated! Thank you.

任何建议将不胜感激！谢谢你。

回答by Deciple

The getFile method is supposed to return a file name:

getFile 方法应该返回一个文件名：

namespace App\Excel 
class MyExcelClass {
public function getFile()
    {
        return storage_path('excel') . '/file.xls';
    }
}

The storage path just points to your storage folder and then whatever sub directory you pass to the storage_path.

存储路径只指向您的存储文件夹，然后是您传递给 storage_path 的任何子目录。

So really StudentImport->getFile(), at least in the docs, just returns '/path/to/MyProject/storage/excel/file.xls;.

所以真的StudentImport->getFile()，至少在文档中，只返回'/path/to/MyProject/storage/excel/file.xls;。

While you can separate and extend control further, let's start with a base case.

虽然您可以进一步分离和扩展控制，但让我们从一个基本案例开始。

In your controller:

在您的控制器中：

use Maatwebsite\Excel\Facades\Excel;
use App\Excel\MyExcelClass;
class ExcelController extends Controller
{
    public function importAndManipulate() {
        Excel::load(MyExcelClass::getFile(), function ($reader){
            $reader->first(); // get first sheet
            foreach($reader as $sheet) // loop through sheets
            {
               $sheetTitle = $sheet->getTitle();
            }
        });
    }
}

You can also try $reader->selectSheet('NameOfSheet'). If this doesn't work trying calling selectSheet on the facade either after loading the sheet or within the callback.

你也可以试试 $reader->selectSheet('NameOfSheet')。如果这不起作用，请尝试在加载工作表后或在回调中调用外观上的 selectSheet。

I admit the documentation can be hard to parse. Why don't you start with storing an excel file somewhere in the storage path and hardcoding the path into the load function.

我承认文档很难解析。为什么不先在存储路径中的某个位置存储一个 excel 文件，然后将该路径硬编码到加载函数中。

http://www.maatwebsite.nl/laravel-excel/docs/import

http://www.maatwebsite.nl/laravel-excel/docs/import