0x01 准备工作
1、下载PHPExcel,我用的是官网最新版1.8.0
2、安装PHPExcel到Codeigniter
1) 解压压缩包里的Classes文件夹中的内容到application\libraries目录下,目录结构如下:
-- application\libraries\PHPExcel.php
-- application\libraries\PHPExcel (文件夹)
到这里准备工作准备完毕。
0x02 使用代码
public function excel()
{
$errstr = str_replace('#bool#', 'false',$this->config->item('json_demo')['check_login']);
$where["cpCode"] = $this->session->userdata('cpcode');
$this->form_validation->set_data($this->input->get(NULL,TRUE));
$this->form_validation->set_rules('str_type', '订单类型', 'trim|integer');
$this->form_validation->set_rules('str_pay', '支付状态', 'trim|integer');
$this->form_validation->set_rules('str_release', '发货状态', 'trim|integer');
$this->form_validation->set_rules('str_balancing', '结算状态', 'trim|integer');
$this->form_validation->set_rules('startday', '起始下单时间', 'trim|integer');
$this->form_validation->set_rules('endday', '结束下单时间', 'trim|integer');
$this->form_validation->set_rules('strName', '商品名称', 'trim')
if ($this->form_validation->run() == false) { //验证通过
exit(str_replace('#message#', '订单导出筛选条件错误!', $errstr));
} else {
//设置筛选条件,无筛选条件导出则为空
$where['str_type'] = ($this->input->get('str_type',true) != 999)?$this->input->get('str_type',true):'';
$where['str_pay'] = ($this->input->get('str_pay',true) != 999)?$this->input->get('str_pay',true):'';
$where['str_release'] = ($this->input->get('str_release',true) != 999)?$this->input->get('str_release',true):'';
$where['str_balancing'] = ($this->input->get('str_balancing',true) != 999)?$this->input->get('str_balancing',true):'';
$where['startday'] = ($this->input->get('startday',true) != 0)?$this->input->get('startday',true):'';
$where['endday'] = ($this->input->get('endday',true) != 0)?$this->input->get('endday',true):'';
$where['str_name'] = $this->input->get('str_name',true);
$data = $this->order->excel_list($where);
$release = array(
0 => '未发货',
1 => '已发货',
2 => '已收货',
3 => '售后中'
);
$pay_status = array(
0 => '未支付',
1 => '已支付',
2 => '支付失败'
);
$balancing = array(
0 => '未结算',
1 => '已结算',
2 => '结算失败'
);
//开始导出
$this->load->library('PHPExcel');//导入PHPExcel
$objPHPExcel = new PHPExcel();//实例化使用
$objPHPExcel->setActiveSheetIndex(0);
ob_end_clean();//清除缓存,避免出错
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);//设置B列单元格宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(13);
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);//设置第一行单元格高度
$objPHPExcel->getActiveSheet()->getStyle('A1:M2')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);//单元格边框
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//这两行将A1单元格垂直水平居中
$objPHPExcel->getActiveSheet()->setTitle('订单列表');
$objPHPExcel->getActiveSheet()->mergeCells('A1:M1');//合并单元格
$objPHPExcel->getActiveSheet()->setCellValue('A1', '企业端订单列表');
$objPHPExcel->getActiveSheet()->setCellValue('A2', '订单号');
$objPHPExcel->getActiveSheet()->setCellValue('B2', '订单日期');
$objPHPExcel->getActiveSheet()->setCellValue('C2', '产品名称');
$objPHPExcel->getActiveSheet()->setCellValue('D2', '支付状态');
$objPHPExcel->getActiveSheet()->setCellValue('E2', '收货地址');
$objPHPExcel->getActiveSheet()->setCellValue('F2', '收货人');
$objPHPExcel->getActiveSheet()->setCellValue('G2', '收货人电话');
$objPHPExcel->getActiveSheet()->setCellValue('H2', '发货状态');
$objPHPExcel->getActiveSheet()->setCellValue('I2', '快递名称');
$objPHPExcel->getActiveSheet()->setCellValue('J2', '快递单号');
$objPHPExcel->getActiveSheet()->setCellValue('K2', '发货时间');
$objPHPExcel->getActiveSheet()->setCellValue('L2', '结算状态');
$objPHPExcel->getActiveSheet()->setCellValue('M2', '订单金额');
$count = count($data['excelList']);
$count = $count + 3;
for ($j = 3; $j < $count; $j++) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . $j, $data['excelList'][$j - 3]['order_code']);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $j, date('Y-m-d H:i:s', $data['excelList'][$j - 3]['order_time']));
$objPHPExcel->getActiveSheet()->setCellValue('C' . $j, $data['excelList'][$j - 3]['product_name']);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $j, $pay_status[$data['excelList'][$j - 3]['order_pay_status']]);
$objPHPExcel->getActiveSheet()->setCellValue('E' . $j, $data['excelList'][$j - 3]['address_name']);
$objPHPExcel->getActiveSheet()->setCellValue('F' . $j, $data['excelList'][$j - 3]['contact_name']);
$objPHPExcel->getActiveSheet()->setCellValue('G' . $j, $data['excelList'][$j - 3]['contact_phone']);
$objPHPExcel->getActiveSheet()->setCellValue('H' . $j, $release[$data['excelList'][$j - 3]['order_release']]);
$objPHPExcel->getActiveSheet()->setCellValue('I' . $j, $data['excelList'][$j - 3]['ex_api_code']);
$objPHPExcel->getActiveSheet()->setCellValue('J' . $j, " " . $data['excelList'][$j - 3]['ex_number']);
$objPHPExcel->getActiveSheet()->setCellValue('K' . $j, $data['excelList'][$j - 3]['express_time'] ? date('Y-m-d H:i:s', $data['excelList'][$j - 3]['express_time']) : ' ');
$objPHPExcel->getActiveSheet()->setCellValue('L' . $j, $balancing[$data['excelList'][$j - 3]['order_balancing']]);
$objPHPExcel->getActiveSheet()->setCellValue('M' . $j, $data['excelList'][$j - 3]['buy_price'] . '元');
}
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="企业订单详情_' . date('ymd') . '.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit();
}
}