public function view_internalIoSellExportExcel() { header("Content-type:text/html;charset=utf-8"); //基础代码准备 $InternalIoSellManagement = new InternalIoSellManagementAct(); $condition = array(); $ordersn = trim($_POST['ordersnInput']); //单据号 $ioTypeinvoiceChoose = $_POST['ioTypeinvoiceChoose']; //出入库单类型 $ioStatus = $_POST['ioStatus']; //审核状态 if (!empty($_POST['startTime']) && !empty($_POST['endTime'])) { $startTime = strtotime(trim($_POST['startTime'])); //开始时间 $endTime = strtotime(trim($_POST['endTime'])); //结束时间 $condition[] = "createdTime BETWEEN {$startTime} AND {$endTime}"; $startTime = trim($_POST['startTime']); //开始时间 $endTime = trim($_POST['endTime']); //结束时间 } else { $condition[] = "createdTime BETWEEN " . strtotime($startTime) . " and " . strtotime($endTime); } if (!empty($ordersn)) { $condition[] = "ordersn = '{$ordersn}'"; } if (!empty($ioTypeinvoiceChoose)) { $condition[] = "invoiceTypeId = '{$ioTypeinvoiceChoose}'"; } else { $condition[] = "invoiceTypeId in (1,2,3,4,5)"; } if (!empty($ioStatus)) { $condition[] = "ioStatus = '{$ioStatus}'"; } $this->where = "WHERE " . implode(" and ", $condition) . " and is_delete = 0 "; $iostoreArr = $InternalIoSellManagement->act_iostoreList($this->where); //单据类型 $this->where = " where groupId = 1"; //内部使用分组单据类型 $invoiceTypeArr = $InternalIoSellManagement->act_invoiceTypeList($this->where); $invoiceNameArr = array(); //出入库单据类型 foreach ($invoiceTypeArr as $invoiceName) { $invoiceNameArr[$invoiceName['id']] = $invoiceName['invoiceName']; } //付款方式 $payMethods = array(); $paymentMethodsArr = $InternalIoSellManagement->act_changeCategoriesSkip(); foreach ($paymentMethodsArr as $payValue) { $payMethods[$payValue['id']] = $payValue['method']; } //单据明细 $iostoreDetails = array(); $iostoreDetailNum = array(); //合计件数 $iostoreDetailDue = array(); //应付款总金额 if (!empty($iostoreArr)) { foreach ($iostoreArr as $ioId) { $this->where = " where iostoreId = " . $ioId['id']; $iostoredetailArr = $InternalIoSellManagement->act_iostoredetailList($this->where); if (empty($iostoredetailArr)) { continue; } $sumNum = 0; $due = 0; foreach ($iostoredetailArr as $ioNumInfo) { $sumNum += $ioNumInfo['amount']; $due += $ioNumInfo['cost']; } $iostoreDetailNum[$ioId['id']] = $sumNum; $iostoreDetailDue[$ioId['id']] = $due; $iostoreDetails[$ioId['id']] = $iostoredetailArr; } } //准备导出 require_once '../lib/PHPExcel.php'; $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '单据号'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', '申请类型'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', '出入类型'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', '申请人'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1', '付款方式'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F1', '申请时间'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G1', '备注'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H1', '审核状态'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I1', '审核人'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J1', '审核时间'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K1', '合计件数'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L1', '总金额'); $a = 2; if (!empty($iostoreArr)) { //单据信息 foreach ($iostoreArr as $key => $iostoreExportArr) { $objPHPExcel->setActiveSheetIndex(0)->getCell('A' . $a)->setValueExplicit(@$iostoreExportArr['ordersn'], PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCell('B' . $a)->setValueExplicit($invoiceNameArr[$iostoreExportArr['invoiceTypeId']], PHPExcel_Cell_DataType::TYPE_STRING); if ($iostoreExportArr['ioType'] == 1) { $objPHPExcel->setActiveSheetIndex(0)->getCell('C' . $a)->setValueExplicit('出库', PHPExcel_Cell_DataType::TYPE_STRING); } else { if ($iostoreExportArr['ioType'] == 2) { $objPHPExcel->setActiveSheetIndex(0)->getCell('C' . $a)->setValueExplicit('入库', PHPExcel_Cell_DataType::TYPE_STRING); } else { $objPHPExcel->setActiveSheetIndex(0)->getCell('C' . $a)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING); } } $objPHPExcel->setActiveSheetIndex(0)->getCell('D' . $a)->setValueExplicit($iostoreExportArr['userId'], PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCell('E' . $a)->setValueExplicit($payMethods[$iostoreExportArr['paymentMethodsId']], PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCell('F' . $a)->setValueExplicit(date("Y-m-d H:i:s", $iostoreExportArr['createdTime']), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCell('G' . $a)->setValueExplicit(@trim($iostoreExportArr['note']), PHPExcel_Cell_DataType::TYPE_STRING); if ($iostoreExportArr['ioStatus'] == 1) { $objPHPExcel->setActiveSheetIndex(0)->getCell('H' . $a)->setValueExplicit("未审核", PHPExcel_Cell_DataType::TYPE_STRING); } else { if ($iostoreExportArr['ioStatus'] == 2) { $objPHPExcel->setActiveSheetIndex(0)->getCell('H' . $a)->setValueExplicit("审核通过", PHPExcel_Cell_DataType::TYPE_STRING); } else { if ($iostoreExportArr['ioStatus'] == 3) { $objPHPExcel->setActiveSheetIndex(0)->getCell('H' . $a)->setValueExplicit("审核不通过", PHPExcel_Cell_DataType::TYPE_STRING); } else { $objPHPExcel->setActiveSheetIndex(0)->getCell('H' . $a)->setValueExplicit("", PHPExcel_Cell_DataType::TYPE_STRING); } } } $objPHPExcel->setActiveSheetIndex(0)->getCell('I' . $a)->setValueExplicit($iostoreExportArr['operatorId'], PHPExcel_Cell_DataType::TYPE_STRING); if (empty($iostoreExportArr['endTime'])) { $objPHPExcel->setActiveSheetIndex(0)->getCell('J' . $a)->setValueExplicit("无", PHPExcel_Cell_DataType::TYPE_STRING); } else { $objPHPExcel->setActiveSheetIndex(0)->getCell('J' . $a)->setValueExplicit(date("Y-m-d H:i:s", $iostoreExportArr['endTime']), PHPExcel_Cell_DataType::TYPE_STRING); } $objPHPExcel->setActiveSheetIndex(0)->getCell('K' . $a)->setValueExplicit($iostoreDetailNum[$iostoreExportArr['id']], PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCell('L' . $a)->setValueExplicit($iostoreDetailDue[$iostoreExportArr['id']], PHPExcel_Cell_DataType::TYPE_STRING); $a++; //单据SKU明细 $objPHPExcel->setActiveSheetIndex(0)->getCell('A' . $a)->setValueExplicit("", PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCell('B' . $a)->setValueExplicit("SKU", PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCell('C' . $a)->setValueExplicit("数量", PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCell('D' . $a)->setValueExplicit("单价(RMB)", PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCell('E' . $a)->setValueExplicit("采购", PHPExcel_Cell_DataType::TYPE_STRING); $a++; foreach ($iostoreDetails[$iostoreExportArr['id']] as $detailList) { $objPHPExcel->setActiveSheetIndex(0)->getCell('A' . $a)->setValueExplicit("", PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCell('B' . $a)->setValueExplicit($detailList['sku'], PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCell('C' . $a)->setValueExplicit($detailList['amount'], PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCell('D' . $a)->setValueExplicit($detailList['cost'], PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCell('E' . $a)->setValueExplicit($detailList['purchaseId'], PHPExcel_Cell_DataType::TYPE_STRING); $a++; } } } $objPHPExcel->getActiveSheet(0)->getStyle('A1:L500')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(50); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(10); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(10); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(15); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(30); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(30); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(10); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('I')->setWidth(20); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('J')->setWidth(30); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('K')->setWidth(10); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('L')->setWidth(10); $title = "internalIoSellInfo" . date('Y-m-d'); $titlename = "internalIoSellInfo" . date('Y-m-d') . ".xls"; $objPHPExcel->getActiveSheet()->setTitle($title); $objPHPExcel->setActiveSheetIndex(0); header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment;filename={$titlename}"); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; }