调用：
//制作excel进行导出；
$excelkey= ['1'=>'序号','2'=>'学员姓名','3'=>'学员身份证号码','4'=>'学员性别(男，女，保密)','5'=>'省','6'=>'市','7'=>'区',
'8'=>'联系电话','9'=>'单位','10'=>'专业技术职务系列','11'=>'职称','12'=>'课程名称','13'=>'报名时间','14'=>'错误信息'];
$excelval=['1'=>'num','2'=>'stu_name','3'=>'stu_cardid','4'=>'stu_sex','5'=>'stu_province','6'=>'stu_city','7'=>'stu_area',
'8'=>'stu_mobile','9'=>'stu_emp_name','10'=>'stu_dutid_name','11'=>'stu_pos_name','12'=>'cou_name','13'=>'sig_date','14'=>'msg'];
$return = $this->outPutExcel($error,$excelkey,$excelval,'报名错误数据',false,'20','');
function alphabetical()
{
return ['1' => 'A', '2' => 'B', '3' => 'C', '4' => 'D', '5' => 'E', '6' => 'F', '7' => 'G', '8' => 'H', '9' => 'I', '10' => 'J',
'11' => 'K', '12' => 'L', '13' => 'M', '14' => 'N', '15' => 'O', '16' => 'P', '17' => 'Q', '18' => 'R', '19' => 'S', '20' => 'T', '21' => 'U',
'22' => 'V', '23' => 'W', '24' => 'X', '25' => 'Y', '26' => 'Z'];
}
protected function outPutExcel($data=[],$excelkey=[],$excelval=[],$filedownname='',$yasuo=false, $columnWidth=10, $title='')
{
ini_set('memory_limit', '1024M');
if($title == '')
$title = $filedownname;
$abcdef=alphabetical();//获取字母排序
// 新建一个excel对象 大神已经加入了PHPExcel 不用引了 直接用！
$objPHPExcel = new \PHPExcel(); //在vendor目录下 \不能少 否则报错
// 设置文档的相关信息
$objPHPExcel->getProperties()->setCreator("博瑞科技");/*设置作者*/
$objPHPExcel->getDefaultStyle()->getFont()->setName('微软雅黑');//字体
//设置第一行的背景颜色
$objPHPExcel->getActiveSheet()->getStyle('A1:M1')->getFill()->applyFromArray(array(
'type' => \PHPExcel_Style_Fill::FILL_SOLID, //PHPEXcel //PHPExcel_Style_Fill
'startcolor' => array(
'rgb' => '2e96ff'
),
'borders' => array(
'allborders' => array(
'style' => \PHPExcel_Style_Border::BORDER_THIN
)
)
));
$objPHPExcel->getActiveSheet()->getStyle('N1:N1')->getFill()->applyFromArray(array(
'type' => \PHPExcel_Style_Fill::FILL_SOLID, //PHPEXcel //PHPExcel_Style_Fill
'startcolor' => array(
'rgb' => 'CC5A29'
),
'borders' => array(
'allborders' => array(
'style' => \PHPExcel_Style_Border::BORDER_THIN
)
)
));
/*设置表头*/
// $objPHPExcel->getActiveSheet()->mergeCells('A1:S1');//合并第一行的单元格
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', "$title");//标题
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(25); // 第一行的默认高度
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);//设置是否加粗
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置文字居左（HORIZONTAL_LEFT，默认值）中（HORIZONTAL_CENTER）右（HORIZONTAL_RIGHT）
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
$myrow = 1;/*表头所需要行数的变量，方便以后修改*/
/*表头数据填充*/
$objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(30);/*设置行高*/
$i=1;
foreach ($excelkey as $key=>$val){
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue($abcdef[$i] . $myrow, $val);//设置一张sheet为活动表 添加表头信息
$i++;
}
// 关键数据
$myrow = $myrow + 1; //刚刚设置的行变量
$mynum = 1;//序号
//遍历接收的数据，并写入到对应的单元格内
foreach ($data as $key => $value) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $myrow, $mynum);
$i=2;
foreach ($excelval as $k1=>$v1){
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($abcdef[$k1] . $myrow, $value[$excelval[$k1]]);
$i++;
}
$objPHPExcel->getActiveSheet()->getRowDimension('' . $myrow)->setRowHeight(20);/*设置行高 不能批量的设置 这种感觉 if（has（蛋）！=0）{疼();}*/
$myrow++;
$mynum++;
}
$mynumdata = $myrow - 1; //获取主要数据结束的行号
$objPHPExcel->setActiveSheetIndex(0)->getstyle('A1:P' . $mynumdata)->getAlignment()->setHorizontal(\PHPExcel_style_Alignment::HORIZONTAL_CENTER);/*设置格式 水平居中*/
/*设置数据的边框 手册上写的方法只显示竖线 非常坑爹 所以采用网上搜来的方法*/
// $style_array = array(
// 'borders' => array(
// 'allborders' => array(
// 'style' => \PHPExcel_Style_Border::BORDER_THIN
// )
// ),
// );
//设置宽width 由于自适应宽度对中文的支持是个BUG因此坑爹的手动设置了每一列的宽度 这种感觉 if（has（蛋）！=0）{碎();}
$j=1;
foreach ($excelkey as $k1=>$v1){
//处理特殊行 I行
if($abcdef[$j]!='N'){
if(in_array($abcdef[$j],['A','B'])) {
$objPHPExcel->getActiveSheet()->getColumnDimension($abcdef[$j])->setWidth(($columnWidth/1.5));
}else{
$objPHPExcel->getActiveSheet()->getColumnDimension($abcdef[$j])->setWidth($columnWidth);
}
}else{
$objPHPExcel->getActiveSheet()->getColumnDimension($abcdef[$j])->setWidth(($columnWidth*2.5));
}
$j++;
}
$objPHPExcel->getActiveSheet()->getStyle('A2:P' . $myrow)->getAlignment()->setWrapText(true);//设置单元格允许自动换行
/*设置表相关的信息*/
$objPHPExcel->getActiveSheet()->setTitle('Title'); //活动表的名称
$objPHPExcel->setActiveSheetIndex(0);//设置第一张表为活动表
//纸张方向和大小 为A4横向
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
//浏览器交互 导出
$file=ROOT_PATH. DS .'public'.DS .'uploads'. DS .'downexcel'. DS .date('Y-m-d');
$downfile=DS .'uploads'.DS .'downexcel'. DS .date('Y-m-d');
if(!is_dir($file)){
mkdir($file,0777,true);
}
$filedate=date('YmdHis',time());
$filename=$file. DS .$filedownname.$filedate.".xlsx";
$downfilename=$downfile. DS .$filedownname.$filedate.".xlsx";
// $filename = iconv("UTF-8", "GBK", $filename); // 重命名表
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
if(!file_exists($filename)){
fopen($filename,"w");
}
$save=$objWriter->save($filename);
if($yasuo){
$zip = new ZipArchive();//使用本类，linux需开启zlib，windows需取消php_zip.dll前的注释
$zipName = $file. DS .'download.zip';
$signfile=ROOT_PATH.'public'. DS .'uploads'.DS .'downexcel'.DS .'template'.DS .'BaoMing201904290000001.xlsx';
$files = array($filename,$signfile);
if ($zip->open($zipName, \ZIPARCHIVE::OVERWRITE | \ZIPARCHIVE::CREATE)!==TRUE) {
exit('无法打开文件，或者文件创建失败');
}
foreach($files as $val){
if(is_file(iconv("UTF-8", "GB2312//IGNORE", $val))){
//addFile函数首个参数如果带有路径，则压缩的文件里包含的是带有路径的文件压缩
$zip->addFile(iconv("UTF-8", "GB2312//IGNORE", $val),basename($val));//第二个参数是放在压缩包中的文件名称，如果文件可能会有重复，就需要注意一下
}else{
exit("1找不到".$val);
}
}
$zip->close();//关闭
if(!file_exists($zipName)){
exit("无法找到文件"); //即使创建，仍有可能失败
}
$data=['filename'=>"download.zip"];
return ['code'=>'1','filename'=>$data['filename']];
}
if($save==null){
$data=['filename'=>$downfilename];
return ['code'=>'1','filename'=>$data['filename']];
}else{
return ['code'=>'0','msg'=>'导出异常'];
}
exit;
}
/**
* 使用phpspreadsheet导出大数据的csv格式
* 注意传值的顺序要保持一致$excelkey，标题title的文字，要和数据中的字段顺序保持一致；
* $excelval只是字段的键值，去重，
*/
public function outPutCsv($data = [], $comments = [], $outname = '导出')
{
set_time_limit(0);
if (!$data || !$comments) $this->error('暂无导出信息');
$arrData = array_merge($comments, $data);
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
// 设置单元格格式 可以省略
$styleArray = [
'font' => [
'bold' => true,
'size' => 14,
],
];
$spreadsheet->getActiveSheet()->getStyle('A1:B1')->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(25);
$spreadsheet->getActiveSheet()->fromArray($arrData);
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
$filedir = DS . 'uploads' . DS . 'downexcel' . DS . date('Y-m-d');//文件目录
$file = ROOT_PATH . 'public' . $filedir;//文件绝对目录
if (!is_dir($file)) {
mkdir($file, 0777, true);
}
$filedate = $outname . date('YmdHis', time());//文件名字
$filename = $file . DS . $filedate . ".csv";//文件绝对路径的名字
$fp = fopen($filename, 'a+');//打开output流
$dataNum = count($arrData);
$perSize = 1000;//每次导出的条数
$pages = ceil($dataNum / $perSize);
for ($i = 1; $i <= $pages; $i++) {
$arrDataOut = array_slice($arrData, $perSize * ($i - 1), $perSize);
foreach ($arrDataOut as $item) {
// mb_convert_variables('GBK', 'UTF-8', $item);
fputcsv($fp, $item);
}
//刷新输出缓冲到浏览器
ob_flush();
flush();//必须同时使用 ob_flush() 和flush() 函数来刷新输出缓冲。
}
fclose($fp);
$data = $filedir . DS . $filedate . ".csv";
return $this->success('导出成功', $data);
}
发表评论 取消回复