PHPexcel的demo

//数字转excel列

function IntToChr($index, $start = 65)
{
    $str = '';
    if (floor($index / 26) > 0) {
        $str .= IntToChr(floor($index / 26) - 1);
    }
    return $str . chr($index % 26 + $start);
}

//下载
function downdata()
{

    require_once './vendor/Excel/PHPExcel.php';
    require_once './vendor/Excel/PHPExcel/CachedObjectStorageFactory.php';
    require_once './vendor/Excel/PHPExcel/Settings.php';
    $model = new Items();
    $where=[];
    //自己组合where条件
    $list = $model->get_list($where);
    $PHPExcel = new \PHPExcel();//实例化
    $objActSheet = $PHPExcel->getActiveSheet();
    $objActSheet->setTitle('产品列表');
    $objActSheet->setCellValue('A1', '列表页');
    $objActSheet->setCellValue('A18', '  ');
    $objActSheet->setCellValue('A19', '柴油机参数');
    $objActSheet->setCellValue('A25', '  ');
    $objActSheet->setCellValue('A27', '发电机参数');
    $objActSheet->setCellValue('A32', '  ');
    $objActSheet->setCellValue('A34', '控制器'); 
    $objActSheet->setCellValue('B39', 'ID：');
    $objActSheet->getColumnDimension('A1')->setWidth(40);
    $objActSheet->getColumnDimension('B1')->setWidth(40);

    //合并单元格
    $objActSheet->mergeCells('A1:A19');
    $objActSheet->mergeCells('A20:A25');
    $objActSheet->mergeCells('A27:A32');
    $objActSheet->mergeCells('A34:A39');
    foreach ($list as $k => $val) {
        $objActSheet->setCellValue(IntToChr($k + 2) . '1', $val['crew_model']);
        $objActSheet->setCellValue(IntToChr($k + 2) . '2', $val['engine_brand']); 
    }

    /*  for($i=0;$i<count($list);$i++){
          for($m=0;$m<36;$m++){
              $objActSheet->getColumnDimension(IntToChr($i + 2).$m)->setWidth(120);
          }
      }*/
    $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, "Excel2007");//创建生成的格式
    header('Content-Disposition: attachment;filename="产品列表.xlsx"');//下载下来的表格名
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    $PHPWriter->save("php://output");  //表示在$path路径下面生成demo.xlsx文件
}

//解析excel
function itemUpload()
    {
        require_once './vendor/Excel/PHPExcel.php';
        set_time_limit(0);
        ini_set('memory_limit', '256M');
        $file = request()->file('file');
        $info = $file->validate(['ext' => 'xls,xlsx,csv'])->move('public/uploads/items', 'item' . time());//上传验证后缀名,以及上传之后移动的地址
        $extension = explode('.', $_FILES['file']['name']);
        $filetype = $extension[1];
        $exclePath = $info->getSaveName();  //获取文件名
        $file_name = 'public/uploads/items/' . $exclePath;   //上传文件的地址
        if ($filetype == 'xlsx')
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        if ($filetype == 'csv')
            $objReader = \PHPExcel_IOFactory::createReader('CSV');
        if ($filetype == 'xls')
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        $obj_PHPExcel = $objReader->load($file_name);  //加载文件内容,编码utf-8
        $excel_array = $obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式
        $excel_array = aryy_x($excel_array);
        array_shift($excel_array);
        array_shift($excel_array);
        foreach ($excel_array as $k => $v) {
            if ($v[0]) {
                $sizearr = explode('*', $v[5]);
                $data[$k]['crew_model'] = $v[0];
                $data[$k]['engine_brand'] = $v[1];
                $data[$k]['diesel_engine_model'] = $v[2];
                $data[$k]['generator_brand'] = $v[3];
                $data[$k]['generator_model'] = $v[4];
                $data[$k]['size1'] = $sizearr[0];
                $data[$k]['size2'] = $sizearr[1];
                $data[$k]['size3'] = $sizearr[2];
                $data[$k]['weight'] = $v[6];
                $data[$k]['main_power'] = $v[7];
                $data[$k]['spare_power'] = $v[8];
                $data[$k]['inspecting_power'] = $v[9];
                $data[$k]['rated_fuel_consumption'] = $v[10];
                $data[$k]['rated_engine_consumption'] = $v[11];
                $data[$k]['rated_voltage'] = $v[12];
                $data[$k]['excitation_type'] = $v[13];
                $data[$k]['controller_brand'] = $v[14];
                $data[$k]['controller_model'] = $v[15];
                $data[$k]['show_type'] = $v[16];
                $data[$k]['std'] = $v[17];
                $data[$k]['gj'] = $v[18];
                $data[$k]['xc'] = $v[19];
                $data[$k]['zs'] = $v[20];
                $data[$k]['pl'] = $v[21];
                $data[$k]['czyyggl'] = $v[22];
                $data[$k]['ccbyyggl'] = $v[23];
                $data[$k]['ljfs'] = $v[25];
                $data[$k]['zc'] = $v[26];
                $data[$k]['dzzcz'] = $v[27];
                $data[$k]['fzyyggl'] = $v[28];
                $data[$k]['fbyyggl'] = $v[29];
                $data[$k]['jyfhdj'] = $v[30];
                $data[$k]['ycd'] = $v[32];
                $data[$k]['fdjjkxm'] = $v[33];
                $data[$k]['cyjjkxm'] = $v[34];
                $data[$k]['zdgn'] = $v[35];
                $data[$k]['yylx'] = $v[36];
                $data[$k]['cyjzzc'] = $v[37];
                $data[$k]['orgid'] = session('orgid');
                if ($v[38]) {
                    $flag = true;//修改
                    $data[$k]['id'] = $v[38];
                } else
                    $data[$k]['addtime'] = time();
            }
        }
        unset($info);
        @unlink($file_name);
        $model = new Items();
        if ($flag)
            $model->isUpdate(true)->saveAll($data);
        else
            $model->isUpdate(false)->saveAll($data);
        rsm(0,[],'上传成功');

    }