一,开发过程中遇到了要用自定义excel表格,记录如下
里面对用的sql对相应的调整即可
方法:
public function downLoadFile()
{
import('Vendor.PHPExcel');
$phpExcel = new \PHPExcel();
$phpExcel->getDefaultStyle()->getFont()->setName('宋体');
$phpExcel->getDefaultStyle()->getFont()->setSize(11);
$phpExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$phpExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$workSheet = $phpExcel->getActiveSheet();
$workSheet->getColumnDimension('A')->setWidth(8.38);
$workSheet->getColumnDimension('B')->setWidth(16.88);
$workSheet->getColumnDimension('C')->setWidth(11.88);
//合并单元格A1到AH1
$workSheet->mergeCells('A1:C1');
//设置边框
$workSheet->getStyle('A1:C15')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
//设置行高为42
$workSheet->getRowDimension(1)->setRowHeight(42);
//在第一行写入'用户排班表'
$workSheet->setCellValue('A1', '技能导入表');
//设置样式
$workSheet->getStyle('A1')->getFont()->setSize(18);
$workSheet->getStyle('A1')->getFont()->setBold(true);
$workSheet->setCellValue('A2', '序号');
$workSheet->setCellValue('B2', '部门名称');
$workSheet->setCellValue('C2', '技能 ');
//插入数据 B列为部门
$departStr = '';
$departArr = M('user_organize')->where(array('level'=>6,'is_del'=>0))->group('judgecode')
->order('sort_num desc')->select();
foreach ($departArr as $ke => $va) {
$departStr .= $va['name'] . ',';
$departIds[] = $ke;
}
$departStr = rtrim($departStr, ',');
for($i = 3; $i <= 15; $i++) {
$objValidation = $workSheet->getCell('B' . $i)->getDataValidation();
$objValidation->setType( \PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle( \PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('输入错误');
$objValidation->setError('此值不在下拉列表中。');
$objValidation->setPromptTitle('请从列表中选择');
$objValidation->setPrompt('请从单元格下拉列表中选择一个值。');
$objValidation->setFormula1('"' . $departStr . '"'); // Make sure to put the list items between " and " !!!
$workSheet->getCell('B' . $i)->setDataValidation($objValidation);
}
//合并单元格A35-AH42
$workSheet->mergeCells('A12:C15');
//设置A35的文字对齐格式
$workSheet->getStyle('A12')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$workSheet->getStyle('A12')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
//让单元格内换行符起作用
$workSheet->getStyle('A12')->getAlignment()->setWrapText(true);
//定界符结束
$introduce = <<<EOF
//1、为保证导入数据成功,序号,部门名称和技能必填且要保证正确性哦!(重要)\n 2、同一部门的多个技能用英文符号(,)隔开,技能名不能重复并且当个技能名称不能大于20个字!\n
EOF;
$workSheet->setCellValue('A12', $introduce);
$result = \PHPExcel_IOFactory::createWriter($phpExcel, 'Excel2007');
//返回的对象太大,通过网络传输会有丢失信息情况,所以序列化
return serialize($result);
}
调用:
/*
* 下载样式
* */
public function downLoadFile()
{
import('Vendor.PHPExcel');
$RpcClient = RpcClient::getInstance();
$result = $RpcClient->sendRequest('/User/WorkSkill', 'downLoadFile', []);
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=1.xlsx");
header('Cache-Control: max-age=0');
$result1 = unserialize($result);
$result1->save('php://output');
}
网友评论