thinkphp导入excel到mysql_Thinkphp5使用PHPExcel导入excel文件到数据库中

/**

* @param Request $request

* @return string

* @throws \PHPExcel_Exception

* @throws \PHPExcel_Reader_Exception

* @throws \think\db\exception\DataNotFoundException

* @throws \think\db\exception\ModelNotFoundException

* @throws \think\exception\DbException

* @author Bob

*/

public function uploadExcel(Request $request)

{

Loader::import('PHPExcel.PHPExcel', EXTEND_PATH);

Loader::import('PHPExcel.PHPExcel.IOFactory.PHPExcel_IOFactory', EXTEND_PATH);

Loader::import('PHPExcel.PHPExcel.Reader.Excel5', EXTEND_PATH);

$file = $request->file('excel');

$file_path = ROOT_PATH . 'public' . DS . 'upload' . DS . 'temp';

$file_name = Auth::$user['dep'] ? Auth::$user['dep'] . '_goodsin_' . time() : '0_goodsin_' . time();

$info = $file->move($file_path, $file_name);

if ($info) {

//echo $info->getFilename();

$exclePath = $info->getSaveName(); //获取文件名

$accept = strrchr($exclePath, '.');

$objReader = '';

if ($accept == '.xlsx') {

$objReader = \PHPExcel_IOFactory::createReader('Excel2007');

} elseif ($accept == '.xls') {

$objReader = \PHPExcel_IOFactory::createReader('Excel5');

}

$obj_PHPExcel = $objReader->load($file_path . DS . $exclePath, $encode = 'utf-8'); //加载文件内容,编码utf-8

$worksheet = $obj_PHPExcel->getsheet(0);

$excel_array = $worksheet->toArray(); //转换为数组格式

array_shift($excel_array); //删除第一个数组(标题);

$imageFilePath = ROOT_PATH . 'public' . DS . 'upload' . DS . 'imgtemp';//图片保存目录

foreach ($worksheet->getDrawingCollection() as $img) {

list ($startColumn, $startRow) = \PHPExcel_Cell::coordinateFromString($img->getCoordinates());//获取列与行号

$imageFileName = time() . mt_rand(100, 999);

/*表格解析后图片会以资源形式保存在对象中，可以通过getImageResource函数直接获取图片资源然后写入本地文件中*/

switch ($img->getMimeType()) {

case'image/jpg':

case'image/jpeg':

$imageFileName .= '.jpg';

imagejpeg($img->getImageResource(), $imageFilePath . DS . $imageFileName);

break;

case 'image/gif':

$imageFileName .= '.gif';

imagegif($img->getImageResource(), $imageFilePath . DS . $imageFileName);

break;

case 'image/png':

$imageFileName .= '.png';

imagepng($img->getImageResource(), $imageFilePath . DS . $imageFileName);

break;

}

$excel_array[$startRow - 2][$startColumn] = $imageFileName; //存到数组里

}

$data = [];

foreach ($excel_array as $k => $v) {

$data[$k]['brand_name'] = $v[0]; //商品品牌

$data[$k]['brand_id'] = $this->findByName($v[0]); //商品品牌

$data[$k]['name'] = $v[1]; //商品名称

$data[$k]['self_no'] = $v[2]; //商品自编号

$data[$k]['goods_no'] = $v[3]; //商品货号

$data[$k]['status'] = $v[4]; //商品状态

$data[$k]['status_collect'] = $v[5]; //集采状态

$data[$k]['unit'] = $v[6]; //计量单位

$data[$k]['stock'] = $v[7]; //库存

$data[$k]['price_market'] = $v[8]; //市场价格

$data[$k]['price_sell'] = $v[9]; //销售价格

$data[$k]['price_member'] = $v[10]; //会员价格

$data[$k]['price_collect'] = $v[11]; //集采价格

$data[$k]['cost_elevator'] = $v[12]; //电梯力费

$data[$k]['cost_statirs'] = $v[13]; //楼梯力费

$data[$k]['spec'] = $v[14]; //规格

$data[$k]['color'] = $v[15]; //颜色

$data[$k]['features'] = $v[16]; //商品特性

$data[$k]['sell_attr'] = $v[17]; //销售属性

$data[$k]['describe'] = $v[18]; //商品描述

$data[$k]['pic1'] = $v['T'] ?? null; //商品图片1

$data[$k]['pic2'] = $v['U'] ?? null; //商品图片2

$data[$k]['pic3'] = $v['V'] ?? null; //商品图片3

}

$commodity = new CommodityModel();

$commodity->saveAll($data);

unset($info);

unlink($file_path . DS . $file_name . $accept); //删除文件

} else {

return $file->getError();

}

}

转载声明：本站文章除注明转载外，均为本站原创或编译。欢迎任何形式的转载，但请务必注明出处，尊重他人劳动。

欢迎捐赠赞赏