阅读excel文件并上传到数据库Laravel 5 [英] Reading excel file and uploading to database Laravel 5
问题描述
但是我尝试阅读文档 http://www.maatwebsite.nl/laravel -excel / docs / import ，但我似乎找不到我需要的那个。
例如在我第一行的excel文件中 John ，肯尼迪，男性
在我的数据库corrensponds 名字，姓氏，性别。
我如何阅读并上传？有人能帮我吗？
谢谢！
我现在的代码
public function postUploadCsv（）
{
$ rules = array（
'file'=>'required'，
'num_records '=>'required'，
）;
$ validator = Validator :: make（Input :: all（），$ rules）;
//处理窗体
if（$ validator-> failed（））
{
return Redirect :: to（'customer-upload'） - > withErrors $验证）;
}
else
{
$ file = Input :: file（'file'）;
dd（$ file）;
exit（）;
}
}
您的excel工作表列名称正好与数据库列名以下是足够的，
添加以下控制器类，
使用Maatwebsite\Excel\Facades\Excel;
和功能代码
public function postUploadCsv（）
{
$ rules = array（
'file'=>'required'，
'num_records'=> 'required'，
）;
$ validator = Validator :: make（Input :: all（），$ rules）;
//处理窗体
if（$ validator-> failed（））
{
return Redirect :: to（'customer-upload'） - > withErrors $验证）;
}
else
{
try {
Excel :: load（Input :: file（'file'）），function（$ reader）{
foreach（$ reader-> toArray（）as $ row）{
User :: firstOrCreate（$ row）;
}
}）;
\Session :: flash（'success'，'Users uploaded successfully。'）;
return redirect（route（'users.index'））;
} catch（\Exception $ e）{
\Session :: flash（'error'，$ e-> getMessage（））;
return redirect（route（'users.index'））;
}
}
}
更新
假设您在工作簿中有多个工作表，您将有额外的 foreach 来迭代表如下：
Excel :: load（Input :: file（'file'），function（$ reader）{
$ reader-> each（function（$ sheet）{
foreach（$ sheet-> toArray（）as $ row）{
User :: firstOrCreate（$ row） ;
}
}）;
}）;
如果您使用Laravel 5.3，并且您的Excel表格列不准确
使用以下代码来满足您的需求
/ **
*将文件导入数据库代码
*
* @var数组
*
public function importExcel（Request $ request）
{
if（$ request-> hasFile（'import_file' ）{
$ path = $ request-> file（'import_file'） - > getRealPath（）;
$ data = Excel :: load（$ path，function（$ reader）{}） - > get（）;
if（！empty（$ data）& $ data-> count（））{
foreach（$ data - > toArray（）as $ key => $ value）{
if（！empty（$ value））{
foreach（$ value as $ v） {
$ insert [] = ['title'=> $ v ['title']，'description'=> $ V [描述]];
}
}
}
if（！空（$ insert））{
Item :: insert（$ insert）;
return back（） - > with（'success'，'Insert Record successfully。'）;
}
}
}
返回（ ） - > with（'error'，'Please check your file，Something is wrong there。'）;
}
查看完整的教程 here
请注意，默认情况下
- 一旦您的数据从您的excel表中删除，所有列名称都将转换为小写，名称之间的所有空格都将被下划线替换。
I have this project where I should be able to upload an excel file and read the contents then upload the information to the database. So I decided to use a library to help me out which turns to be Maatwebsite/Laravel-Excel
But I tried reading the documentation http://www.maatwebsite.nl/laravel-excel/docs/import but I can't seem to find the one that I need.
For example in my excel file in the first row John, Kennedy, Male
which in my database corrensponds First Name, Last Name, Gender.
How can I read it and upload? Can someone help me?
Thanks!
My code as of now
public function postUploadCsv()
{
$rules = array(
'file' => 'required',
'num_records' => 'required',
);
$validator = Validator::make(Input::all(), $rules);
// process the form
if ($validator->fails())
{
return Redirect::to('customer-upload')->withErrors($validator);
}
else
{
$file = Input::file('file');
dd($file);
exit();
}
}
given your excel sheet column names are exactly as database column names following is suffice,
add following above controller class,
use Maatwebsite\Excel\Facades\Excel;
and function code,
public function postUploadCsv()
{
$rules = array(
'file' => 'required',
'num_records' => 'required',
);
$validator = Validator::make(Input::all(), $rules);
// process the form
if ($validator->fails())
{
return Redirect::to('customer-upload')->withErrors($validator);
}
else
{
try {
Excel::load(Input::file('file'), function ($reader) {
foreach ($reader->toArray() as $row) {
User::firstOrCreate($row);
}
});
\Session::flash('success', 'Users uploaded successfully.');
return redirect(route('users.index'));
} catch (\Exception $e) {
\Session::flash('error', $e->getMessage());
return redirect(route('users.index'));
}
}
}
UPDATE
Suppose you have more than one sheet in a workbook, you will have additional foreach to iterate over sheets as below,
Excel::load(Input::file('file'), function ($reader) {
$reader->each(function($sheet) {
foreach ($sheet->toArray() as $row) {
User::firstOrCreate($row);
}
});
});
In case you are using Laravel 5.3 and given that your excel sheet columns are not exact
Make use of the following code to suite your needs
/**
* Import file into database Code
*
* @var array
*/
public function importExcel(Request $request)
{
if($request->hasFile('import_file')){
$path = $request->file('import_file')->getRealPath();
$data = Excel::load($path, function($reader) {})->get();
if(!empty($data) && $data->count()){
foreach ($data->toArray() as $key => $value) {
if(!empty($value)){
foreach ($value as $v) {
$insert[] = ['title' => $v['title'], 'description' => $v['description']];
}
}
}
if(!empty($insert)){
Item::insert($insert);
return back()->with('success','Insert Record successfully.');
}
}
}
return back()->with('error','Please Check your file, Something is wrong there.');
}
Check out the full tutorial here
Note that by default - Once your data is extacted from you excel sheet, all the column names are converted to lower case, and all spaces between the names are replaced with underscore.
这篇关于阅读excel文件并上传到数据库Laravel 5的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
