laravel—Excel 导入导出应用官方的包 maatwebsiteexcel

导入：简单版 

public function import(Request $request) { $filePath = "public" . $request->get("path"); try { Excel::load($filePath, function ($reader) { //获取excel的第几张表 $reader = $reader->getSheet(1); //获取表中的数据 $data = $reader->toArray(); for ($row = 3; $row < count($data); $row++) { $food = Food::where("name", $data[$row]['1'])->first(); if (!empty($food)) continue; $list = new Food(); $list->type = $data[$row]['2']; $list->code = $data[$row]['0']; $list->name = $data[$row]['1']; $list->norm = $data[$row]['6']; $list->save(); } }); return response()->json(["code" => CodeUtil::SUCCESS, 'msg' => "成功"]); } catch (\Exception $e) { return response()->json(["code" => CodeUtil::ERROR, 'msg' => "失败"]); } }

 

复杂版

 

注:下面的换了一个包，laravel官网的包导入时丢失小数（坑），转用tp框架的包，还蛮好用的

use PhpOffice\PhpSpreadsheet\IOFactory;

public function import(Request $request) { $now = Carbon::now()->toDateString(); $s = Sale::where("time", $now)->first(); if (!empty($s)) return response()->json(["code" => CodeUtil::ERROR, 'msg' => "今日数据已导过"]); $filePath = public_path($request->get("path")); $objReader = IOFactory::createReader('Xlsx'); $spreadsheet = $objReader->load($filePath); //载入excel表格 $reader = $spreadsheet->getSheet(0)->toArray(); $reader1 = $spreadsheet->getSheet(0); $row_num = $reader1->getHighestRow(); // 总行数 $m = 0;//获取最后一列数 foreach ($reader[3] as $v) { $m++; } try { $list = new Sale(); $list->bill_num = $reader1->getCellByColumnAndRow($m, 5)->getValue(); $list->people_num = $reader1->getCellByColumnAndRow($m, 6)->getValue(); $list->run_price = $reader1->getCellByColumnAndRow($m, 7)->getValue(); $list->discount_price = $reader1->getCellByColumnAndRow($m, 9)->getValue(); $list->tip = $reader1->getCellByColumnAndRow($m, 10)->getValue(); $list->time = Carbon::now()->toDateString();//今天; $list->save(); $n = 0; for ($i = 11; $i <= $row_num; $i++) { $name = $reader1->getCellByColumnAndRow(1, $i)->getValue(); if ($name == "优惠组成") { $n = $i; break; } } for ($i = 11; $i < $n; $i++) { $sale_income = new SaleIncome(); $sale_income->sale_id = $list->id; $sale_income->name = $reader1->getCellByColumnAndRow(2, $i)->getValue(); $sale_income->sum = $reader1->getCellByColumnAndRow($m, $i)->getValue(); $sale_income->save(); $ch = Channel::where("is_del", 1)->get()->toArray(); foreach ($ch as $v) { $name = $v['name']; if ($name == $sale_income->name) { $sale_tip = new SaleTip(); $sale_tip->sale_id = $list->id; $sale_tip->name = $name; $sale_tip->sum = $sale_income->sum * $v['rate'] / 100; $sale_tip->save(); } } } for ($i = $n; $i <= $row_num; $i++) { $sale_dis = new SaleDiscount(); $sale_dis->sale_id = $list->id; $sale_dis->name = $reader1->getCellByColumnAndRow(2, $i)->getValue(); $sale_dis->sum = $reader1->getCellByColumnAndRow($m, $i)->getValue(); $sale_dis->save(); } $sale_tip_sum = SaleTip::where("sale_id", $list->id)->sum("sum"); $sale = Sale::where("id", $list->id)->first(); $sale->deduction = $sale_tip_sum; $sale->true_price = $list->run_price - $list->discount_price - $sale->deduction; $sale->save(); return response()->json(["code" => CodeUtil::SUCCESS, 'msg' => "成功"]); } catch (\Exception $e) { \Log::info("sale_import" + $e); return response()->json(["code" => CodeUtil::ERROR, 'msg' => "失败"]); } }

 

 

导出 ：简单版

public function export(Request $request) { $code = $request->get("code"); $name = $request->get("name"); $type = $request->get("type"); $start = $request->get("start"); $end = $request->get("end"); $obj = $this->select($code, $name, $type, $start, $end); $list = $obj->select("code", "name", "type", "norm", "num", "time")->get()->toArray(); $file_name = date("Ymd_His-") . "菜品销售管理表"; $title = ["菜品编码", "菜品名称", "菜品类别", "规格", "流水数量", "点菜时间"]; $cellData = array_merge([$title], $list); Excel::create($file_name, function ($excel) use ($cellData) { $excel->sheet('score', function ($sheet) use ($cellData) { $sheet->rows($cellData)->setWidth(array(//调整导出表格单元格宽度 'A' => '15%', 'F' => '25%', )); }); })->export('xlsx'); }

 

复杂版1：针对于多维表头

（需求图）

public function export(Request $request) { $code = $request->get("code"); $name = $request->get("name"); $obj = $this->select($code, $name); $list = $obj->select("code", "name", "type", "norm", 'total_price', "burden")->get()->toArray(); foreach ($list as $k => $v) { $bur_num[] = count($v['burden']); } $bur_max = max($bur_num); $title1 = ["菜品编码", "菜品名称", "菜品类别", "规格", "合计"]; $title2 = []; //处理多维表头 for ($i = 1; $i < $bur_max + 1; $i++) { if ($i == 1) $title2[] = ["", "", "", "", "", "用料", "规格", "用量", "成本价"]; else $title2[] = ["用料", "规格", "用量", "成本价"]; $title3[] = ["菜品配料" . $i, "", "", ""]; } $sum_cell = ($i - 1) * 4 + 5;//列数 // return $sum_cell; $title2 = array_collapse($title2); $title3 = array_collapse($title3); $title = array_collapse([$title1, $title3]);//表头 $val = []; $data = [];//处理数据 foreach ($list as $k => $v) { if (!empty($v['burden'])) { foreach ($v['burden'] as $vv) { $val[] = [ $vv['name'], $vv['norm'], $vv['num'], $vv['price'] ]; } } unset($v['burden']); $val = array_collapse($val); $data[] = array_collapse([$v, $val]); } $file_name = date("Ymd_His-") . "菜品管理表"; $cellData = array_merge([$title, $title2], $data); Excel::create($file_name, function ($excel) use ($cellData, $sum_cell, $bur_max) { $excel->sheet('score', function ($sheet) use ($cellData, $sum_cell, $bur_max) { $sheet->rows($cellData)->setWidth(array(//调整导出表格单元格宽度 "B" => '25%', )); // for ($a = 0; $a < $sum_cell + 1; $a++) { // $sheet->setWidth(array(//调整导出表格单元格宽度 // $this->intToChr($a) => '20%', // )); // } for ($n = 0; $n < $sum_cell; $n++) { if ($n < 5) { //前5个表格单元格格式，合并每一列的前两行 $sheet->mergeCells($this->intToChr($n) . '1:' . $this->intToChr($n) . '2'); } elseif ($n == 5) { //对数组 规格 数据进行处理 for ($m = 0; $m < $bur_max; $m++) { $sheet->mergeCells($this->intToChr($n + $m * 4) . '1:' . $this->intToChr($n + +3 + ($m * 4)) . '1'); } } } }); })->export('xlsx'); // $code = $request->get("code"); // $name = $request->get("name"); // $obj = $this->select($code, $name); // $list = $obj->select("code", "name", "type", "norm", 'total_price', "burden")->get()->toArray(); // foreach ($list as $k => $v) { // $bur_num[] = count($v['burden']); // } // $bur_max = max($bur_num); // $title1 = ["菜品编码", "菜品名称", "菜品类别", "规格", "合计"]; // $title2 = []; // for ($i = 0; $i < $bur_max; $i++) { // $title2[] = ["用料", "规格", "用量", "成本价"]; // } // $title2 = array_collapse($title2); // $title = array_collapse([$title1, $title2]);//拿到表头 // // $val = []; // $data = []; // foreach ($list as $k => $v) { // if (!empty($v['burden'])) { // foreach ($v['burden'] as $vv) { // $val[] = [ // $vv['name'], // $vv['norm'], // $vv['num'], // $vv['price'] // ]; // } // } // unset($v['burden']); // $val = array_collapse($val); // $data[] = array_collapse([$v, $val]); // } // // $file_name = date("Ymd_His-") . "菜品管理表"; // $cellData = array_merge([$title], $data); // Excel::create($file_name, function ($excel) use ($cellData) { // $excel->sheet('score', function ($sheet) use ($cellData) { // $sheet->rows($cellData)->setWidth(array(//调整导出表格单元格宽度 // 'A' => '15%', // 'B' => '25%', // )); // }); // })->export('xlsx'); }

(效果图）

复杂版2

（需求图）

public function export(Request $request) { $time = $request->get("time"); $obj = Sale::select("id", "bill_num", "people_num", "run_price", "discount_price", "true_price", "tip", "deduction"); if (!empty($time)) { $data = $obj->where('time', $time)->get(); $list = $data->toArray(); } else return response()->json(["code" => CodeUtil::ERROR, 'msg' => "请选择导出数据的时间"]); $file_name = date("Ymd_His-") . "店员收银表"; $head = ["列表1", "列表2", "合计"]; $bill_num = ["账单数量", ""]; $people_num = ["客流数量", ""]; $run_price = ["流水金额", ""]; $discount_price = ["优惠金额", ""]; $true_price = ["实收金额", ""]; $tip = ["小费", ""]; $deduction = ["扣点", ""]; $sum = $data = []; //插入4个表中，一个主表，3组的附表 foreach ($list as $v) { $sum["bill_num"] = [$v["bill_num"]]; $sum["people_num"] = [$v["people_num"]]; $sum["run_price"] = [$v["run_price"]]; $sum["discount_price"] = [$v["discount_price"]]; $sum["true_price"] = [$v["true_price"]]; $sum["tip"] = [$v["tip"]]; $sum["deduction"] = [$v["deduction"]]; $bill_num = array_merge($bill_num, $sum["bill_num"]); $people_num = array_merge($people_num, $sum["people_num"]); $run_price = array_merge($run_price, $sum["run_price"]); $discount_price = array_merge($discount_price, $sum["discount_price"]); $true_price = array_merge($true_price, $sum["true_price"]); $tip = array_merge($tip, $sum["tip"]); $deduction = array_merge($deduction, $sum["deduction"]); $data = [$bill_num, $people_num, $run_price, $discount_price, $true_price, $tip, $deduction];//数据 $v["income_info"] = SaleIncome::where("sale_id", $v['id'])->get(); $v["tip_info"] = SaleTip::where("sale_id", $v['id'])->get(); $v["discount_info"] = SaleDiscount::where("sale_id", $v['id'])->get(); $i = $m = $n = 0; if (count($v["income_info"]) > 0) { foreach ($v["income_info"] as $val) { // 处理 空表格 $i++; if ($i == 1) $data[] = ["实收组成", $val['name'], $val['sum']]; else $data[] = ["", $val['name'], $val['sum']]; } } if (count($v["tip_info"]) > 0) { foreach ($v["tip_info"] as $val) { $n++; if ($n == 1) $data[] = ["渠道扣点", $val['name'], $val['sum']]; else $data[] = ["", $val['name'], $val['sum']]; } } if (count($v["discount_info"]) > 0) { foreach ($v["discount_info"] as $val) { $m++; if ($m == 1) $data[] = ["优惠组成", $val['name'], $val['sum']]; else $data[] = ["", $val['name'], $val['sum']]; } } } $cellData = array_merge([$head], $data); Excel::create($file_name, function ($excel) use ($cellData) { $excel->sheet('score', function ($sheet) use ($cellData) { $sheet->rows($cellData)->setWidth(array(//调整导出表格单元格宽度 'A' => '20%', 'B' => '20%', 'C' => '20%', )); }); })->export('xlsx'); }

(效果图）