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

深碍√TFBOYSˉ_ 2022-11-14 00:58 218阅读 0赞

本篇以 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本窗口打印