使用phpexcel导出数据，基本使用方法

最新推荐文章于 2022-05-05 20:29:18 发布

Berger_

最新推荐文章于 2022-05-05 20:29:18 发布

阅读量320

点赞数 1

分类专栏： PHP class 文章标签： class

本文链接：https://blog.youkuaiyun.com/qq_28742763/article/details/53130145

版权

PHP 同时被 2 个专栏收录

10 篇文章

订阅专栏

class

1 篇文章

订阅专栏

先去官网相下载phpexcel –http://phpexcel.codeplex.com/

//首先引入类
include 'source/plugin/hjw_shop/function/PHPExcel/Classes/PHPExcel.php';
include 'source/plugin/hjw_shop/function/PHPExcel/Classes/PHPExcel/Writer/Excel2007.php'; //用于输出.xls/.pdf的 2007的

//拿到想要导出的一组二维数组
$order_list =array();
$order_list = DB::fetch_all(" SELECT a.order_sn,a.total,a.suppliers_id,a.create_time,b.consignee,b.address,b.mobile,a.best_time 
     FROM ".DB::table('hjw_order')." AS  a LEFT JOIN ".DB::table('hjw_addr')." AS b ON a.addr_id=b.addr_id ".$where);

// 创建一个excel
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Phpmarker")->setLastModifiedBy("Phpmarker")->setTitle("Phpmarker")->setSubject("Phpmarker")->setDescription("Phpmarker")->setKeywords("Phpmarker")->setCategory("Phpmarker");
//设置第一行
$objPHPExcel->setActiveSheetIndex(0)->
setCellValue('A1', '编号')->
setCellValue('B1', '订单号')->
setCellValue('C1', '订单总金额')->
setCellValue('D1', '商家')->
setCellValue('E1', '下单时间')->
setCellValue('F1', '收货人')->
setCellValue('G1', '收货地址')->
setCellValue('H1', '手机')->
setCellValue('I1', '最佳送货')
;

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('11月订单');

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
// 第一行的默认高度
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(25);
//冻结窗口
$objPHPExcel->getActiveSheet()->freezePane('A2');

$i = 2;
//遍历数据 我大部分是用剧居中了 样式
foreach($order_list as $data){

     $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i-1)->getStyle('A'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);

     $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, ' '.$data[order_sn])->getStyle('B'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);

     $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $data[total])->getStyle('C'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);

     $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'. $i, $data[suppliers_id],PHPExcel_Cell_DataType::TYPE_STRING);

     $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $data[create_time])->getStyle('E'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);

     $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, iconv('gbk', 'utf-8',$data[consignee]))->getStyle('F'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);

     $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, iconv('gbk', 'utf-8',$data[address]))->getStyle('G'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);

     $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $data[mobile])->getStyle('H'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);

     $objPHPExcel->getActiveSheet()->setCellValue('I' . $i, iconv('gbk', 'utf-8',$data[best_time]))->getStyle('I'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);


     $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getNumberFormat()->setFormatCode("@");

     // 设置文本格式
     $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'. $i,  iconv('gbk', 'utf-8',date('Y-m-d',$data[create_time])) ,PHPExcel_Cell_DataType::TYPE_STRING);

     $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getAlignment()->setWrapText(true);

     //递增
     $i++ ;
}



$objActSheet = $objPHPExcel->getActiveSheet();


// 设置CELL填充颜色
$cell_fill = array(
  'A1','B1','C1', 'D1','E1','F1','G1','H1','I1'
);

foreach($cell_fill as $cell_fill_val){
 $cellstyle = $objActSheet->getStyle($cell_fill_val);
 // background
 // $cellstyle->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('fafa00');
 // set align
 $cellstyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
 // font
 $cellstyle->getFont()->setSize(12)->setBold(true);

 // border
 $cellstyle->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
 $cellstyle->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
 $cellstyle->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
 $cellstyle->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
}

//设置默认高度
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
 //自己手动设置宽度
$objActSheet->getColumnDimension('A')->setWidth(10);
$objActSheet->getColumnDimension('B')->setWidth(35);
$objActSheet->getColumnDimension('C')->setWidth(15);
$objActSheet->getColumnDimension('D')->setWidth(15);
$objActSheet->getColumnDimension('E')->setWidth(20);
$objActSheet->getColumnDimension('F')->setWidth(10);
$objActSheet->getColumnDimension('G')->setWidth(30);
$objActSheet->getColumnDimension('H')->setWidth(18);
$objActSheet->getColumnDimension('I')->setWidth(30);
$filename = '2';



ob_end_clean();//清除缓冲区,避免乱码 
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
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 = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save('php://output');

中间会遇到中文乱码用php函数转化，有些是gbk

iconv('gbk', 'utf-8','要转化的中文')

还有如果位数超过18位的，excel默认会转为科学计算法并且后面的用0 代替，所以思路把数值型
转化成字符串型，前面加‘ ’就行。（网上大神说可以在数字中间加符号隔开，如空格 - 等）

更多的使用方法可以去官网参考