php excel 输出条形码,封装一个 PHPExcel 的导出方法

本篇以 ThinkPHP 为示例

public function getExcel($property,$headArr,$data){

Vendor('Excel.PHPExcel');

$objPHPExcel = new \PHPExcel();

$objPHPExcel

-> getProperties()

-> setCreator($property['creator'])

-> setLastModifiedBy($property['last'])

-> setTitle($property['title'])

-> setSubject($property['subject'])

-> setDescription($property['description'])

-> setKeywords($property['keywords'])

-> setCategory($property['category']);

$column = 1;

$span = ord("A");

foreach($headArr as $v){

$j = chr($span);

$objPHPExcel->setActiveSheetIndex(0) -> setCellValue($j.$column,$v);

$span ++;

}

$column ++;

$objActSheet = $objPHPExcel->getActiveSheet();

foreach($data as $key => $rows){

$span = ord('A');

foreach($rows as $keyName => $value){

$j = chr($span);

$objActSheet->setCellValue($j.$column, $value);

$span ++;

}

$column ++;

}

$objPHPExcel->setActiveSheetIndex(0);

ob_end_clean();

ob_start();

header("Content-Type: application/force-download");

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

header("Content-Disposition:attachment;filename =" . str_ireplace('+', '%20', URLEncode($fileName)).'.xlsx');

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

$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

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

exit ;

}

调用

public function _index_export(){

$property = array('title'=>'测试');

$headArr = array('好','坏');

$data = array(

array(

3,4

),

array(

5,6

)

);

$this->getExcel($property,$headArr,$data);

}

优化当超过 26 列

public function getExcel($property,$headArr,$data){

Vendor('Excel.PHPExcel');

$objPHPExcel = new \PHPExcel();

$objPHPExcel

-> getProperties()

-> setCreator($property['creator'])

-> setLastModifiedBy($property['last'])

-> setTitle($property['title'])

-> setSubject($property['subject'])

-> setDescription($property['description'])

-> setKeywords($property['keywords'])

-> setCategory($property['category']);

$column = 1;

$span = 0;

foreach($headArr as $v){

$letter = \PHPExcel_Cell::stringFromColumnIndex($span);

$objPHPExcel->setActiveSheetIndex(0) -> setCellValue($letter.$column,$v);

$span ++;

}

$objActSheet = $objPHPExcel->getActiveSheet();

foreach($data as $key => $rows){

$column ++;

$span = 0;

foreach($rows as $keyName => $value){

$letter = \PHPExcel_Cell::stringFromColumnIndex($span);

$objActSheet->setCellValue($letter.$column, $value);

$span ++;

}

}

$objPHPExcel->setActiveSheetIndex(0);

ob_end_clean();

ob_start();

header("Content-Type: application/force-download");

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

header("Content-Disposition:attachment;filename =" . str_ireplace('+', '%20', URLEncode($property['title'])).'.xlsx');

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

$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

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

exit ;

}

可以设置列宽的一种方法

public function exportExcel($expTitle,$expCellName,$expTableData){

$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称

$fileName = $expTitle.date('_Ymd_His');//or $xlsTitle 文件名称可根据自己情况设定

$cellNum = count($expCellName);

$dataNum = count($expTableData);

Vendor('Excel.PHPExcel');

$objPHPExcel = new \PHPExcel();

$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');

// $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格

// $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.' Export time:'.date('Y-m-d H:i:s'));//第一行标题

for($i=0;$i

$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1', $expCellName[$i][1]);

$objPHPExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth($expCellName[$i][2]); //设置宽度

}

// Miscellaneous glyphs, UTF-8

for($i=0;$i

for($j=0;$j

$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]);

}

}

header('pragma:public');

header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');

header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印

$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

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

exit;

}

调用

public function dumpLogsExcel(){

//查询数据

$where['member_card'] = 123456;

$xlsData = array();//二维数组

//构建excel

$xlsName = "用户评价信息表";

$xlsCell = array(

array('GongSiMc','门店名称',20),

array('order_num','保养单号',20),

array('wx_name','微信名',20),

array('member_card','会员卡号',15),

array('is_good_name','是否满意',10),

array('evaluate_item','具体评价',40),

array('score','得分',10),

array('create_time','创建时间',20),

);

$this->exportExcel($xlsName,$xlsCell,$xlsData);

}