先去下载phpexcel文件,下载地址:https://archive.codeplex.com/?p=phpexcel
phpexcel的导出
$conditions = ' 1=1 ';
$status = !empty($_POST['status'])?$_POST['status']:0;
if ( !empty($status)) {
$conditions .=" AND status=".$status;
}
$order_list = $this->model_order()->find(
array(
'conditions'=>"$conditions",
'join' => 'has_orderextm'
)
);
$excelObject = new PHPExcel();
$excelObject->setActiveSheetIndex(0)
->setCellValue('A1', '订单编号')
->setCellValue('B1', '收件人')
->setCellValue('C1', '固话')
->setCellValue('D1', '手机')
->setCellValue('E1', '地址')
->setCellValue('F1', '发货信息')
->setCellValue('G1', '备注')
->setCellValue('H1', '订单状态')
->setCellValue('I1', '保价金额')
->setCellValue('J1', '业务类型');
$rowIndex=2;
$order_status = ['10'=>'未付款','20'=>'已付款,待发货','30'=>'已发货','40'=>'已完成'];
foreach ($order_list as $k => $row) {
$status_name = $order_status[$row['status']];
$sql = "select * from ecm_order_goods WHERE order_id=".$row['order_id'];
$products = $this->model_order()->getAll($sql);
$productsInfo = '';
foreach ($products as $product) {
$productsInfo .= $product['goods_name'] . 'x' . $product['quantity'] . ';';
}
$excelObject->getActiveSheet()
->setCellValueExplicit('A' . $rowIndex, (string)$row['order_sn'])
->setCellValue('B' . $rowIndex, $row['consignee'])
->setCellValue('C' . $rowIndex, $row['phone_tel'])
->setCellValueExplicit('D' . $rowIndex, $row['phone_mob'])
->setCellValue('E' . $rowIndex, $row['region_name'].$row['address'])
->setCellValue('F' . $rowIndex, $productsInfo)
->setCellValue('G' . $rowIndex, $row['buyer_msg'])
->setCellValue('H' . $rowIndex, $status_name)
->setCellValue('I' . $rowIndex, '')
->setCellValue('J' . $rowIndex, '');
$rowIndex++;
}
$title = date('Y-m-d'). "$order_status[$status]". '订单列表';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $title . '.xls"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
$objWriter = new PHPExcel_Writer_Excel5($excelObject);
$objWriter->save('php://output');
phpexcel导入:
include_once ROOT_PATH . '/includes/phpexcel/Classes/PHPExcel.php';
include_once ROOT_PATH . '/includes/phpexcel/Classes/PHPExcel/Writer/Excel5.php';
include_once ROOT_PATH . '/includes/phpexcel/Classes/PHPExcel/Writer/Excel2007.php';
$file = "../data_focus/mall/excel/excel1525742575/2018-05-07.xls";//存放文件的路径
$PHPReader = new PHPExcel_Reader_Excel5();
$objPHPExcel = $PHPReader->load($file);
$sheet = $objPHPExcel->getSheet(0);
// 取得总行数
$highestRow = $sheet->getHighestRow();
// 取得总列数
$highestColumn = $sheet->getHighestColumn();
//循环读取excel文件,读取一条,插入一条
$key=[];
//从第一行开始读取数据
for($currentRow=1;$currentRow<=$highestRow;$currentRow++){
//从A列读取数据
for($currentColumn='A';$currentColumn<=$highestColumn;$currentColumn++){
$address = $currentColumn . $currentRow; // 数据坐标
$cellValue = $sheet->getCell($address)->getValue();
// 读取单元格
/* $data[$currentRow][]=$objPHPExcel->getActiveSheet()->getCell("$currentColumn$currentRow")->getValue();*/
if ($cellValue == '快递名称') {
$key['name'] = $currentColumn;
continue;
} elseif ($cellValue == '订单编号') {
$key['order_sn'] = $currentColumn;
continue;
} else if ($cellValue == '运单号') {
$key['wuliudanhao'] = $currentColumn;
continue;
}
if ($currentColumn == $key['name']) {
$ExlData[$currentRow]['name'] = $cellValue;
} else if ($currentColumn == $key['order_sn']) {
$ExlData[$currentRow]['order_sn'] = $cellValue;
} else if ($currentColumn == $key['wuliudanhao']) {
$ExlData[$currentRow]['wuliudanhao'] = $cellValue;
}
if (!$cellValue) {
break;
}
}
}
// 更新订单物流数据
$result = ['error' => [], 'success' => []];
if (!empty($ExlData)) {
foreach ($ExlData as $row) {
$wuLiuName = 'other';
switch ($row['name']) {
case '中通快递':
$wuLiuName = 'zhongtong';
break;
case 'EMS快递':
$wuLiuName = 'ems';
break;
case '申通快递':
$wuLiuName = 'shentong';
break;
case '顺丰快递':
$wuLiuName = 'shunfeng';
break;
case '圆通快递':
$wuLiuName = 'yuantong';
break;
case '韵达电子':
case '韵达快递':
$wuLiuName = 'yunda';
break;
case '百世汇通快递':
$wuLiuName = 'huitong';
break;
case '天天快递':
$wuLiuName = 'tiantian';
break;
}
$data =['shipping_no' => $row['wuliudanhao'], 'shipping_com' => $wuLiuName, 'status' => '30'];
$queryResult = $this->model_order()->edit("order_sn='{$row['order_sn']}'",$data);
$sql = "SELECT order_id FROM ecm_order WHERE order_sn='".$row['order_sn']."'";
$order_id = $this->model_order()->getOne($sql);
$logistic_data =[
'order_id'=>$order_id,
'shipping_no'=>$row['wuliudanhao'],
'shipping_com'=>$wuLiuName,
'shipping_memo'=>$row['name'],
'shipping_time' =>time()
];
$this->model_orderlogistic()->add($logistic_data);
if (!$queryResult) {
$result['error'][] = $row['order_sn'];
} else {
$result['success'][] = $row['order_sn'];
}
}
$string = '订单物流状态更新完成,成功' . count($result['success']) . '条,失败' . count($result['error']) . '条';
if (count($result['error']) > 0) {
$string .= ',失败订单号:' . implode(',', $result['error']);
}
echo $string;
就这样实现了phpexel导入跟导出