phpExcel 笔记
<?php
// +-------------------------------------------------------------------
// | KCDNS 开创网络科技
// +-------------------------------------------------------------------
// | Copyright (c) 2009-2016 http://www.kcdns.com All rights reserved.
// +-------------------------------------------------------------------
namespace Service\Util\Excel;
class Service {
protected $obj;
public function __construct()
{
include_once dirname(__FILE__) . "/PHPExcel.php";
$this->obj=new \PHPExcel();
}
function export_order($export_map){
$where_export=[];
$where_export['_string']="1";
$where_exist=false;
if(!is_administrator()&&!is_system_manager()){
$export_map["orders.shop_id"]=["in",array_keys(session('admin_detail.shop_list'))];
}
if(isset($export_map["extra.goods_id"])){
$goods_id=$export_map["extra.goods_id"];
$order_details_list=M("order_details")->where(["goods_id"=>$goods_id])->select();
$order_id_arr=[];
if(!empty($order_details_list)){
foreach ($order_details_list as $value){
$order_id_arr[]=$value["order_id"];
}
}
$where_export["orders.id"]=["in",$order_id_arr];
unset($export_map["extra.goods_id"]);
$where_exist=true;
}
if(isset($export_map["orders.payed_time"])){
$pay_time_arr=explode(",",$export_map["orders.payed_time"]);
if(!empty($pay_time_arr[0])){
$where_export['_string'].= " and (orders.payed_time >= '".$pay_time_arr[0]."')";
$where_exist=true;
}
if(!empty($pay_time_arr[1])){
$where_export['_string'].= " and (orders.payed_time <= '".$pay_time_arr[1]."')";
$where_exist=true;
}
unset($export_map["orders.payed_time"]);
}
$log_day="";
if(isset($export_map["orders.created_time"])){
$created_time=date("Y-m-d",strtotime($export_map["orders.created_time"]));
unset($export_map["orders.created_time"]);
if($created_time&&'1970-01-01'!=$created_time){
$log_day=$created_time;
$where_export['_string'].= " and (DATE_FORMAT(orders.created_time,'%Y-%m-%d')='".$log_day."')";
$where_exist=true;
}
}
if(!empty($export_map["extra.order_telphone"])){
$logistics_info=M("order_logistics")->where(["receive_phone"=>$export_map["extra.order_telphone"]])->select();
$order_id_arr=[];
if(!empty($logistics_info)){
foreach ($logistics_info as $value){
$order_id_arr[]=$value["order_id"];
}
}
$where_export['_string'].= " and (orders.id in (".implode(",",$order_id_arr)."))";
unset($export_map["extra.order_telphone"]);
$where_exist=true;
}
if(!empty($export_map["extra.logistics_no"])){
$logistics_info=M("order_logistics")->where(["logistics_no"=>$export_map["extra.logistics_no"]])->select();
$order_id_arr=[];
if(!empty($logistics_info)){
foreach ($logistics_info as $value){
$order_id_arr[]=$value["order_id"];
}
}
$where_export['_string'].= " and (orders.id in (".implode(",",$order_id_arr)."))";
unset($export_map["extra.logistics_no"]);
$where_exist=true;
}
if(!empty($export_map)){
foreach ($export_map as $key=>$value){
if($key=='orders.status'&&$value=='_EMPTY_VALUE_'){
continue;
}
$where_export[$key]=["eq",$value];
$where_exist=true;
}
}
if(!$where_exist){
$log_day=date("Y-m-d");
$where_export['_string'].= " and (DATE_FORMAT(orders.created_time,'%Y-%m-%d')='".$log_day."')";
}
$objGetSheet=$this->obj->getActiveSheet(0);
$objSetSheet=$this->obj->setActiveSheetIndex(0);
$this->obj->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->obj->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
if(empty($log_day)){
$fileName="orderDetails";
}else{
$fileName=$log_day."-orderDetails";
}
$objGetSheet->setTitle($fileName); //设置sheet名称
$_row=1;
$_cell=0;
$columns=[
['order_no',' 订单ID'],
['goods_id',' 商品id'],
['goods_name','商品/服务'],
['product_price','单价(元)'],
['product_number','数量'],
['subtotal','应收款(元)'],
['status','订单状态'],
['telphone','收货人电话'],
['created_time','下单时间'],
['payed_time','付款时间'],
['realname','收货人姓名'],
['user_order_address','收货人地址'],
['user_remark','用户备注'],
];
foreach ($columns as $key=>$value){
$objGetSheet->getColumnDimension($this->getPosition($_cell+$key))->setWidth(20);
$cell_key=$this->getPosition($_cell+$key).$_row;
$objSetSheet->setCellValue($cell_key, $value[1]);
}
$export_list=M("orders")
->alias("orders")
->join("RIGHT JOIN __ORDER_DETAILS__ order_details on orders.id=order_details.order_id")
//->join("__GOODS__ goods on order_details.goods_id=goods.id")//goods.goods_no
->join("__ORDER_ADDRESS__ order_address on orders.id=order_address.order_id")
->join("__SHOPS__ shops on orders.shop_id=shops.id")
->join("__USERS__ users on orders.user_id=users.user_id")
->where($where_export)
->field("orders.order_no,order_details.goods_id,order_details.goods_name,order_details.product_price,
order_details.product_number,order_details.subtotal,orders.status,
order_address.telphone,orders.created_time,orders.payed_time,order_address.realname,
CONCAT(order_address.province,order_address.city,order_address.county,order_address.address) as user_order_address,
orders.user_remark
")
->order("orders.created_time desc")
->select();
if(!empty($export_list)){
foreach ($export_list as $key=>$value){
$_row++;
foreach ($columns as $_key=>$_value){
$cell_key=$this->getPosition($_cell+$_key).$_row;
//$objSetSheet->getStyle($cell_key)->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
if($_value[0]=="status"){
$value_str="";
switch ($value[$_value[0]]){
case "0":
$value_str="删除";
break;
case "1";
$value_str="未付款";
break;
case "2";
$value_str="已付款";
break;
case "3";
$value_str="已发货";
break;
case "4";
$value_str="已签收";
break;
case "5";
$value_str="退货申请";
break;
case "6";
$value_str="退货中";
break;
case "7";
$value_str="已退货";
break;
case "8";
$value_str="取消交易";
break;
case "9";
$value_str="维权中";
break;
case "10";
$value_str="退款中";
break;
default:
$value_str="未知状态";
}
$objSetSheet->setCellValueExplicit($cell_key,$value_str,\PHPExcel_Cell_DataType::TYPE_STRING);
}else{
$objSetSheet->setCellValueExplicit($cell_key,$value[$_value[0]],\PHPExcel_Cell_DataType::TYPE_STRING);
}
}
}
}
$execl_path=C('UPLOAD_DIR').'Excel/'.md5(session("user_auth.uid")."jiatu");
if(file_exists($execl_path)){
$this->delPathFile($execl_path);
}
$fileName=$fileName.".xlsx";
$this->create_excel($execl_path,$fileName);
return ["file_path"=>$execl_path."/".$fileName];
}
function getPosition($num=""){
$cell_arr = 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');
$cell_len=count($cell_arr);
$divisor=intval($num/$cell_len);
if($divisor==0){
$_start_pre="";
}else{
$_start_pre=$cell_arr[($divisor-1)];
}
return $_start_pre.$cell_arr[$num%$cell_len];
}
public function create_excel($execl_path,$fileName){
if(!file_exists($execl_path)){
mkdir($execl_path,0777,true);
}
$file=$execl_path."/".$fileName;
(new \PHPExcel_Writer_Excel2007($this->obj))->save($file);
}
public function down_file($fileName){
$objWrite = \PHPExcel_IOFactory::createWriter($this->obj, 'Excel2007');
header('pragma:public');
header("Content-Disposition:attachment;filename=".$fileName.".xlsx");
$objWrite->save('php://output');exit;
}
public function delPathFile($path){
$handle = opendir($path);
while (($file=readdir($handle))) {
unlink($path."$file");
}closedir($handle);
}
public function updateOrderShipStatus($excel_path="",$shop_id_arr=array()){
$excel_path=C('UPLOAD_DIR').'Excel/demo_file/order_fahuo_demo.xlsx';
$arr = explode('.',$excel_path);
$file_type=array_pop($arr);
if ($file_type == 'xls') {
$reader = \PHPExcel_IOFactory::createReader('Excel5');
}
if ($file_type == 'xlsx') {
$reader = new \PHPExcel_Reader_Excel2007();
}
$PHPExcel = $reader->load($excel_path, 'utf-8'); // 载入excel文件
$sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumm = $sheet->getHighestColumn(); // 取得总列数
$end_index = \PHPExcel_Cell::columnIndexFromString($highestColumm);
$data = array();
for ($rowIndex = 1; $rowIndex <= $highestRow; $rowIndex++) { //循环读取每个单元格的内容。注意行从1开始，列从A开始
for ($colIndex = 0; $colIndex < $end_index; $colIndex++) {
$col_name = \PHPExcel_Cell::stringFromColumnIndex($colIndex);
$addr = $col_name . $rowIndex;
$cell = $sheet->getCell($addr)->getValue();
if ($cell instanceof PHPExcel_RichText) { //富文本转换字符串
$cell = $cell->__toString();
}
$data[$rowIndex][$colIndex] = $cell;
}
}
dump($data);
}
//生成订单批量发货demo
public function createOrderShipExcelDemo(){
$objGetSheet=$this->obj->getActiveSheet(0);
$objSetSheet=$this->obj->setActiveSheetIndex(0);
//$this->obj->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//$this->obj->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$fileName="order_fahuo_demo";
$objGetSheet->setTitle($fileName); //设置sheet名称
$_row=1;
$_cell=0;
$columns=[
['demo_item1',' 订单编号'],
['demo_item2',' 物流公司'],
['demo_item3','物流单号'],
['demo_item4','实际发货时间'],
['demo_item5','收货人姓名'],
['demo_item6','联系电话'],
['demo_item7','收货地址'],
];
foreach ($columns as $key=>$value){
$objGetSheet->getColumnDimension($this->getPosition($_cell+$key))->setWidth(20);
$lie=$this->getPosition($_cell+$key);
$cell_key=$lie.$_row;
$objGetSheet->getStyle($lie)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objSetSheet->setCellValue($cell_key, $value[1]);
}
$demo_num=10;
for ($i=0;$i<$demo_num;$i++){
$_row++;
$demo=[
"demo_item1"=>"831602019030896286",
"demo_item2"=>"圆通",
"demo_item3"=>"75135424731770",
"demo_item4"=>date("Y-m-d H:i:s"),
"demo_item5"=>"慈慈旗舰店",
"demo_item6"=>"18612697580",
"demo_item7"=>"南京多喜巴电子科技有限公司",
];
foreach ($columns as $_key=>$_value){
$cell_key=$this->getPosition($_cell+$_key).$_row;
$objSetSheet->setCellValueExplicit($cell_key,$demo[$_value[0]],\PHPExcel_Cell_DataType::TYPE_STRING);
}
}
$execl_path=C('UPLOAD_DIR').'Excel/demo_file/';
$fileName=$fileName.".xlsx";
$this->create_excel($execl_path,$fileName);
}
}
phpExcel 设置时间格式
foreach ($v as $key => $val) {
if (isset($csvTemplate[$key])) {
$PHPExcel->getActiveSheet()->setCellValue($csvTemplate[$key] . $highestRow ,$val);
if ($key == 'CSV_HOUR' && $v['min'] < 540) {
$PHPExcel->getActiveSheet()->getStyle( $csvTemplate[$key] . $highestRow)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
}
if ($key == 'CSV_CHECKIN_TIME_START' || $key == 'CSV_CHECKIN_TIME_END') {
if (!empty($val)) {
$PHPExcel->getActiveSheet()->setCellValue($csvTemplate[$key] . $highestRow, PHPExcel_Shared_Date::PHPToExcel(strtotime($val), true, 'Asia/Shanghai'));
$sheet->getStyle($csvTemplate[$key] . $highestRow)->getNumberFormat()->setFormatCode('hh:mm');
}
}
}
}
