1.当前行num、每条记录的起始行startNum、每条记录的结束行endNum
2.不变更实际当前行
/**
* 报表导出
* @param $storeId
* @param $userId
* @param $isAdmin
* @param null $adminId
* @param null $listType
* @param $date
* @return array|bool
*/
public function emport($storeId, $userId, $isAdmin, $adminId = null, $listType = null, $date = null, $orderType = null, $dateType = null, $isCash = false)
{
$time = $_SERVER['REQUEST_TIME'];
try {
if (!$isAdmin) {
throw new \Exception('该账户非店长,无此权限');
}
if (empty($date)) {
$beginDate = date('Y-m', $time) . '-01';
} else {
$beginDate = $date . '-01';
}
$find = SoOrder::find();
$find->where([
SoOrder::tableName() . '.store_id' => $storeId,
SoOrder::tableName() . '.status' => OrderService::STATUS_NORMAL
]);
$find = self::orderListTypeFilter($find, $listType, false);
$beginTime = strtotime($beginDate);
$endDate = date('Y-m-d', strtotime('+1 month', $beginTime));
// 导出
if (!$isCash) {
$find->andWhere([
'and',
['>=', SoOrder::tableName() . '.date', $beginDate],
['<', SoOrder::tableName() . '.date', $endDate]
]);
} else {
$find->joinWith(['transactionOrders'], false);
$find->andWhere([
'and',
['>=', TransactionOrder::tableName() . '.finish_date', $beginDate],
['<', TransactionOrder::tableName() . '.finish_date', $endDate]
]);
}
$find = $this->baseOrderSearchCondition($find, null, $orderType, null, null, null, null, $dateType);
$orders = $find->orderBy(['create_time' => SORT_DESC])->all();
$list = [];
$otherFeeDetail = [];
foreach ($orders as $order) {
// $list[] = OrderManageService::orderFormat($order, false, false, true);
$data = OrderManageService::orderFormat($order, false, true, true);
if ($isCash && empty($data['payment_time_date'])) {
continue;
}
if ($data['order_type'] == OrderService::ORDER_TYPE_BUYER_PERIOD) {
$data['credit_order'] = $data['order_period_info'];
}
// $data['package_fee'] = '';
// $data['loading_fee'] = '';
// $data['other_fee_detail'] = '';
$data['other_fee'] = '';
$data['transportation_fee'] = '';
$data['discount_fee'] = '';
$feeInfo = !empty($data['fee_info']) ? $data['fee_info'] : [];
if (in_array($data['order_type'], [OrderService::ORDER_TYPE_SELLER_OFFLINE, OrderService::ORDER_TYPE_SELLER_CREDIT])) {
$extendData = !empty($feeInfo) ? $feeInfo[0] : '';
if ($extendData) {
// $data['other_fee_detail'] = $extendData['extend_data'];
$extendData['extend_data']['order_no'] = $order->order_no;
$otherFeeDetail[] = $extendData['extend_data'];
foreach ($feeInfo as $fee) {
$data[SoOrderFee::$FEE_TYPE_NAME[$fee['fee_type']]] = $fee['fee_amount'];
$data['memo'] = $data['memo'] . $fee['memo'];
}
}
} else {
foreach ($feeInfo as $key => $fee) {
$data[SoOrderFee::$FEE_TYPE_NAME[$fee['fee_type']]] = $fee['fee_amount'];
$data['memo'] = $data['memo'] . $fee['memo'];
}
}
$data['nickname'] = isset($data['buyer_info']) ? $data['buyer_info']['nickname'] : '';
$data['pay_amount'] = isset($data['credit_order']) ? HumanReadableHelper::format_money($data['credit_order']['pay_amount']) : '';
$data['unpay_amount'] = isset($data['credit_order']) ? HumanReadableHelper::format_money($data['credit_order']['unpay_amount']) : '';
// if($order->order_id == 3499){var_dump($data);die;}
$list[] = $data;
}
$objPHPExcel = new \PHPExcel();
$header = [
'A' => '编号',
'B' => '订单类型',
'C' => '订单号',
'D' => '下单时间',
'E' => '下单用户',
'F' => '联系方式',
'G' => '类别',
'H' => '品类',
'I' => '品种',
'K' => '商品名',
'J' => '规格',
'L' => '购买数量',
'M' => '单价',
// 'N' => '其他-包装',
// 'O' => '其他-运输',
// 'P' => '其他-装车',
'N' => '其他费用明细',
'O' => '其他费用',
'P' => '优惠金额',
'Q' => '小计',
'R' => '合计',
'S' => '付款时间',
'T' => '账期实际支付金额',
'U' => '账期未支付金额',
'V' => '业务员',
'W' => '备注'
];
$fieldName = [
'order_type_name' => 'B',
'order_no' => 'C',
'create_time_date' => 'D',
'nickname' => 'E',
'buyer_mobile' => 'F',
// 'package_fee' => 'N',
// 'transportation_fee' => 'O',
// 'loading_fee' => 'P',
'other_fee' => 'N',
'other_fee_detail' => 'O',
'discount_fee' => 'P',
// 'confirm_total_price' =>'S',
'order_amount' => 'R',
'payment_time_date' => 'S',
'pay_amount' => 'T',
'unpay_amount' => 'U',
'admin' => 'V',
'memo' => 'W'
];
$goodsField = [
'class_name' => 'G',
'parent_cate_name' => 'H',
'variety_name' => 'I',
'spec' => 'J',
'goods_name' => 'K',
'order_num' => 'L',
'confirm_price' => 'M',
'confirm_total_amount' => 'S'
];
// var_dump($otherFeeDetail);
// exit;
$getRes = $this->exportExcel($list, date('Ym', $beginTime), $header, $objPHPExcel, $fieldName, $goodsField, $storeId, $otherFeeDetail);
if (!$getRes) {
throw new \Exception('导出失败');
}
return $getRes;
} catch (\Exception $e) {
\Yii::info($e, __FUNCTION__);
$this->addError($e->getMessage());
}
return false;
}
// TODO 导出类
public function exportExcel($data, $filename = 'Excel', $header, $objPHPExcel = null, $fieldName, $goodsField, $storeId, $otherFeeDetail = null, $basePath = null)
{
// var_dump($data);exit;
try {
$objPHPExcel->getProperties()->setCreator("hermite.Qiu")
->setLastModifiedBy("hermite.Qiu")
->setTitle("用户导出")
->setSubject("数据EXCEL导出")
->setDescription("备份数据")
->setKeywords("excel")
->setCategory("数据导出");
$cellName = array_values($header); // 名
$cell = array_keys($header); // 单位元
// 设置标题
foreach ($cellName as $k => $v) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cell[$k] . '1', $v);
}
// 填充数据
$num = 1; // 起始行
$no = 0; // 初始编号
foreach ($data as $kk => $value) {
$no += 1;
$startNum = $num + 1;
$goodsInfo = $value['order_product_detail'] ? $value['order_product_detail'] : [];
if ($goodsInfo) {
foreach ($goodsInfo as $vv) {
$num += 1;
$vv = $vv['goods_excel'];
foreach ($vv as $goodsKey => $item) {
$objPHPExcel->getActiveSheet()->setCellValue($goodsField[$goodsKey] . $num, $item);
}
}
} else {
$num += 1;
}
foreach ($fieldName as $fieldKey => $name) {
if ($fieldKey == 'other_fee_detail' && is_array($value[$fieldKey]) && in_array($value['order_type'], [OrderService::ORDER_TYPE_SELLER_OFFLINE, OrderService::ORDER_TYPE_SELLER_CREDIT])) {
// $tempStartNum = $startNum;
$tempNum = $startNum;
foreach ($value[$fieldKey] as $kk2 => $vv2) {
if (!empty($vv2)) {
$tempNum++;
$objPHPExcel->getActiveSheet()->setCellValue($name . $tempNum, SoOrderFee::$TYPE_OTHER_FEE[$kk2] . $vv2);
}
}
if ($tempNum > $num) {
$num = $tempNum;
}
}
}
$endNum = $num;
foreach ($fieldName as $fieldKey => $name) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . $startNum, $no);
$objPHPExcel->getActiveSheet()->mergeCells("A$startNum" . ':' . "A$endNum");
$objPHPExcel->getActiveSheet()->getStyle($name . $startNum)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
if ($fieldKey == 'other_fee_detail' && is_array($value[$fieldKey]) && in_array($value['order_type'], [OrderService::ORDER_TYPE_SELLER_OFFLINE, OrderService::ORDER_TYPE_SELLER_CREDIT])) {
} else {
$objPHPExcel->getActiveSheet()->setCellValueExplicit($name . $startNum, " $value[$fieldKey] ", \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->mergeCells($name . $startNum . ':' . $name . $endNum);
}
}
}
// die;
$objPHPExcel->getActiveSheet()->setTitle('User');
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
return $objWriter;
} catch (\Exception $e) {
\Yii::info($e->getMessage(), __FUNCTION__);
$this->addError($e->getMessage());
}
return false;
}
/**
* 导出excel
* @param $data
* @param string $filename
* @param $header
* @param null $objPHPExcel
* @return bool
*/
public function exportExcel($data, $filename = 'Excel', $header, $objPHPExcel = null, $fieldName, $goodsField, $storeId, $otherFeeDetail = null, $basePath = null)
{
// var_dump($otherFeeDetail);exit;
try {
$objPHPExcel->getProperties()->setCreator("hermite.Qiu")
->setLastModifiedBy("hermite.Qiu")
->setTitle("用户导出")
->setSubject("数据EXCEL导出")
->setDescription("备份数据")
->setKeywords("excel")
->setCategory("数据导出");
$cellName = array_values($header); // 名
$cell = array_keys($header); // 单位元
// 设置标题
foreach ($cellName as $k => $v) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cell[$k] . '1', $v);
}
// 填充数据
$num = 1; // 起始行
$no = 0; // 初始编号
foreach ($data as $kk => $value) {
$no += 1;
$startNum = $num + 1;
$goodsInfo = $value['order_product_detail'] ? $value['order_product_detail'] : [];
if ($goodsInfo) {
foreach ($goodsInfo as $vv) {
$num += 1;
$vv = $vv['goods_excel'];
foreach ($vv as $goodsKey => $item) {
$objPHPExcel->getActiveSheet()->setCellValue($goodsField[$goodsKey] . $num, $item);
}
}
} else {
$num += 1;
}
// foreach ($fieldName as $fieldKey => $name) {
// if ($fieldKey == 'other_fee_detail' && is_array($value[$fieldKey]) && in_array($value['order_type'], [OrderService::ORDER_TYPE_SELLER_OFFLINE, OrderService::ORDER_TYPE_SELLER_CREDIT])) {
$tempStartNum = $startNum;
// $tempNum = $startNum;
// foreach ($value[$fieldKey] as $kk2 => $vv2) {
// if (!empty($vv2)) {
// $tempNum++;
// $objPHPExcel->getActiveSheet()->setCellValue($name . $tempNum, SoOrderFee::$TYPE_OTHER_FEE[$kk2] . $vv2);
// }
// }
// if ($tempNum > $num) {
// $num = $tempNum;
// }
// }
// }
$endNum = $num;
foreach ($fieldName as $fieldKey => $name) {
// var_dump($fieldKey);
if ($otherFeeDetail && $fieldKey == 'other_fee_detail' && in_array($value['order_type'], [OrderService::ORDER_TYPE_SELLER_OFFLINE, OrderService::ORDER_TYPE_SELLER_CREDIT])) {
$tempNum = $startNum;
foreach ($otherFeeDetail as $otherDetail) {
if ($otherDetail['order_no'] == $value['order_no']) {
foreach ($otherDetail as $feeKey => $fee) {
if (!empty($fee) && $feeKey != 'order_no') {
$objPHPExcel->getActiveSheet()->setCellValue($name . $tempNum, SoOrderFee::$TYPE_OTHER_FEE[$feeKey] . $fee);
}
$tempNum++;
}
}
}
if ($tempNum > $num) {
$num = $tempNum;
$endNum = $num;
}
} else {
$objPHPExcel->getActiveSheet()->getStyle($name . $startNum)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
if ($fieldKey == 'other_fee_detail') {
$objPHPExcel->getActiveSheet()->setCellValueExplicit($name . $startNum, "", \PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$objPHPExcel->getActiveSheet()->setCellValueExplicit($name . $startNum, " $value[$fieldKey] ", \PHPExcel_Cell_DataType::TYPE_STRING);
}
$objPHPExcel->getActiveSheet()->mergeCells($name . $startNum . ':' . $name . $endNum);
}
$objPHPExcel->getActiveSheet()->setCellValue('A' . $startNum, $no);
$objPHPExcel->getActiveSheet()->mergeCells("A$startNum" . ':' . "A$endNum");
}
}
$objPHPExcel->getActiveSheet()->setTitle('User');
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
return $objWriter;
} catch (\Exception $e)
{
\Yii::info($e->getMessage(), __FUNCTION__);
$this->addError($e->getMessage());
}
return false;
}