php模板批量输出多个txt文件,phpspreadsheet 批量导出与批量导入

使用组件

composer.json中添加该组件，执行composer update命令

"require": {

"phpoffice/phpspreadsheet": "^1.9"

},

1、导出列表数据

use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

use PhpOffice\PhpSpreadsheet\IOFactory;

use PhpOffice\PhpSpreadsheet\Shared\Date;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

public function export($list){

$title = '职位招聘信息列表';

$title_arr = ['职位类别','职位名称','城市','部门','类型','性质','薪资','人数','投递邮箱','截止时间', '岗位职责','任职资格','发布状态'];

$cell_arr = range('A','Z');

$spreadsheet = new Spreadsheet();

$spreadsheet->getActiveSheet()->setTitle($title);

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

$spreadsheet->getActiveSheet()->getStyle($cell_arr[$k] . 1)->getFont()->setBold(true);//标题字段加黑加粗

$spreadsheet->getActiveSheet()->getColumnDimension($cell_arr[$k])->setWidth($cell_arr[$k] > 'H' ? 25 : 15);//设置宽度

$spreadsheet->getActiveSheet()->setCellValue($cell_arr[$k] . 1, $v);//设置标题

}

$spreadsheet->setActiveSheetIndex(0);

//超过8位数的纯数字字符串，导出表格会默认为科学计数法。解决办法是在字段末尾拼接一个" "，空格字符串即可。

foreach ($list as $key => $val) {

$tmp = $key + 2;

$spreadsheet->getActiveSheet()->setCellValue('A' . $tmp, $val['category']);

$spreadsheet->getActiveSheet()->setCellValue('B' . $tmp, $val['position']);

$spreadsheet->getActiveSheet()->setCellValue('C' . $tmp, $val['city']);

$spreadsheet->getActiveSheet()->setCellValue('D' . $tmp, $val['department']);

$spreadsheet->getActiveSheet()->setCellValue('E' . $tmp, $val['type']);

$spreadsheet->getActiveSheet()->setCellValue('F' . $tmp, $val['nature']);

$spreadsheet->getActiveSheet()->setCellValue('G' . $tmp, $val['salary']);

$spreadsheet->getActiveSheet()->setCellValue('H' . $tmp, $val['num']);

$spreadsheet->getActiveSheet()->setCellValue('I' . $tmp, $val['email']);

$spreadsheet->getActiveSheet()->setCellValue('J' . $tmp, $val['end_date']);

$spreadsheet->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);

$spreadsheet->getActiveSheet()->setCellValue('K' . $tmp, $val['task']);

$spreadsheet->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);

$spreadsheet->getActiveSheet()->setCellValue('L' . $tmp, $val['ask']);

$spreadsheet->getActiveSheet()->setCellValue('M' . $tmp, $val['status']);

}

//或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=GB2312');

header("Pragma: public");

header('Content-Disposition: attachment;filename="'. $title .'.xlsx"');

header('Cache-Control: max-age=0');

$objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');

return $objWriter->save('php://output');

}

2、导出模板

public function template(){

$title = '职位录入模板'；

$title_arr = ['职位类别','职位名称','城市','部门','类型','性质','薪资','人数','投递邮箱','截止时间,'岗位职责','任职资格'];

$cell_arr = range('A','Z');

$spreadsheet = new Spreadsheet();

$spreadsheet->getActiveSheet()->setTitle($title);

$spreadsheet->setActiveSheetIndex(0);

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

$cell_num = $cell_arr[$k] . 1;

$spreadsheet->getActiveSheet()->getStyle($cell_num)->getFont()->setBold(true); //加黑加粗

$spreadsheet->getActiveSheet()->getColumnDimension($cell_arr[$k])->setWidth($cell_arr[$k] > 'H' ? 25 : 15);//设置宽度

$spreadsheet->getActiveSheet()->setCellValue($cell_num, $v);//设置标题

}

$dropdown_arr = [ //表格下拉选项

'A' => ['研发类','行政类','销售类'],

'C' => ['深圳市','广州市','上海市'],

'D' => ['研发部','人事部','财务部'],

'E' => ['校招','社招'],

'F' => ['全职','兼职'],

];

foreach ($dropdown_arr as $column => $item) { //渲染下拉列表

$objValidation = $spreadsheet->getActiveSheet()->getDataValidation('$'.$column.':$'.$column);//$1:$1

$objValidation->setType(DataValidation::TYPE_LIST)

->setErrorStyle(DataValidation::STYLE_INFORMATION)

->setAllowBlank(false)

->setShowInputMessage(true)

->setShowErrorMessage(true)

->setShowDropDown(true)

->setErrorTitle('输入的值有误')

->setError('您输入的值不在下拉框列表内.')

->setPromptTitle('请选择')

->setFormula1('"'.implode(',', $item).'"');

}

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=GB2312');

header("Pragma: public");

header('Content-Disposition: attachment;filename="'. $title .'.xlsx"');

header('Cache-Control: max-age=0');

$objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');

return $objWriter->save('php://output');

}

3、批量导入

public function import(){

set_time_limit(60);

ini_set('memory_limit', '256M');

$file_size = $_FILES['file']['size'];

if ($file_size > 5 * 1024 * 1024) {

throw new \Exception('文件大小不能超过5M');

}

$suffix = substr(strrchr($_FILES['file']["name"], '.'), 1);

if ($suffix != 'xlsx') {

throw new \Exception('必须为excel表格，且必须为xlsx格式！');

}

$filename = $_FILES['file']['tmp_name'];

if (is_uploaded_file($filename)) {

$objReader = IOFactory::createReader('Xlsx');

$objReader->setReadDataOnly(true);//忽略任何格式的信息

$objPHPExcel = $objReader->load($filename);

$sheet = $objPHPExcel->getSheet(0); //excel中的第一张sheet

$highestRow = $sheet->getHighestRow(); // 取得总行数

// $highestColumn = $sheet->getHighestColumn(); // 取得总列数

$cates = ['1' => '研发类', '2' => '行政类', '3' => '销售类'];

$cites = ['1' => '深圳市','2' => '广州市', '3' => '上海市'];

$departments =['1' => '研发部', '2' => '人事部', '3' => '财务部'];

$types = ['1' => '校招', '2' => '社招'];

$natures = ['1' => '全职', '2' => '兼职'];

$data = [];

for ($j = 2; $j <= $highestRow; $j++) {

$category = $objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue();

if(empty($category)) {

throw new \Exception('A列第'.$j.'行职位类别不能为空');

}

$cate_id = array_search($category, $cates);

if(empty($cate_id)) {

throw new \Exception('A列第'.$j.'行职位类别不存在，请先在系统中添加该类型');

}

$position = $objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue();

if(empty($position)) {

throw new \Exception('B列第'.$j.'职位名称不能为空');

}

$city = $objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue();

if(empty($city)) {

throw new \Exception('C列第'.$j.'行城市信息不能为空');

}

$city_id = array_search($city, $cites);

if(empty($city_id)) {

throw new \Exception('C列第'.$j.'行城市信息不存在，请先在系统中添加该类型');

}

$department = $objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue();

if(empty($department)) {

throw new \Exception('D列第'.$j.'行部门信息不能为空');

}

$department_id = array_search($department, $departments);

if(empty($department_id)) {

throw new \Exception('D列第'.$j.'行部门信息不存在，请先在系统中添加该类型');

}

$type = $objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue();

if(empty($type)) {

throw new \Exception('E列第'.$j.'行招聘类型信息不能为空');

}

$type_id = array_search($type, $types);

if(empty($type_id)) {

throw new \Exception('E列第'.$j.'行招聘类型信息错误');

}

$nature = $objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue();

if(empty($nature)) {

throw new \Exception('F列第'.$j.'行招聘性质信息不能为空');

}

$nature_id = array_search($nature, $natures);

if(empty($nature_id)) {

throw new \Exception('F列第'.$j.'行招聘性质信息错误');

}

$salary = $objPHPExcel->getActiveSheet()->getCell("G" . $j)->getValue();

if(empty($salary)) {

throw new \Exception('G列第'.$j.'薪资不能为空');

}

$num = $objPHPExcel->getActiveSheet()->getCell("H" . $j)->getValue();

if(empty($num)) {

throw new \Exception('H列第'.$j.'招聘人数不能为空');

}

$email = $objPHPExcel->getActiveSheet()->getCell("I" . $j)->getValue();

if(empty($email)) {

throw new \Exception('I列第'.$j.'投递邮箱不能为空');

}

$end_date = $objPHPExcel->getActiveSheet()->getCell("J" . $j)->getValue();//获取的时间为浮点型

$end_date = date('Y-m-d', Date::excelToTimestamp($end_date));//转化为时间类型

$task = $objPHPExcel->getActiveSheet()->getCell("K" . $j)->getValue();

if(empty($task)) {

throw new \Exception('K列第'.$j.'岗位职责描述不能为空');

}

$ask = $objPHPExcel->getActiveSheet()->getCell('L' . $j)->getValue();

if(empty($ask)) {

throw new \Exception('L列第'.$j.'任职资格描述不能为空');

}

$data[] = [

'cate_id' => $cate_id,

'position' => $position,

'city_id' => $cate_id,

'department_id' => $department_id,

'type' => $type_id,

'nature' => $nature_id,

'salary' => $salary,

'num' => $num,

'email' => $email,

'end_date' => $end_date,

'task' => $task,

'ask' => $ask,

'status' => 1

];

}

$model = new JobModel();

$ret = $model->saveAll($data);

return true;

}

}