导入
//示例
//TODO 先上传 保存文件/*设置上传路径*/$savePath = $this->up_path;$tmp_file = $_FILES ['file'] ['tmp_name'];$file_types = explode ( ".", $_FILES ['file'] ['name'] );$file_type = $file_types [count ( $file_types ) - 1];/*以时间来命名上传的文件*/$str = '商保商品黑名单'.$pack_id.date ( 'Ymdhis' );$file_name = $str . "." . $file_type;$file = $savePath . $file_name;/*是否上传成功*/if (!copy ($tmp_file, $file)){
return ['code' => $params['error'], 'message' => '上传失败', 'data' => ''];}$extension = $file_type;if ($extension =='xlsx') {
$objReader = new \PHPExcel_Reader_Excel2007();$objExcel = $objReader ->load($file);} else if ($extension =='xls') {
$objReader = new \PHPExcel_Reader_Excel5();$objExcel = $objReader ->load($file);} else if ($extension=='csv') {
$objReader = new \PHPExcel_Reader_CSV();//默认输入字符集$objReader->setInputEncoding('GBK');//默认的分隔符$objReader->setDelimiter(',');//载入文件$objExcel = $objReader->load($file);}$sheet = $objExcel->getSheet(); // 获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推$allColumn =$sheet->getHighestColumn(); // 获取总列数$allRow = $sheet->getHighestRow();// 获取总行数$ColumnNum = \PHPExcel_Cell::columnIndexFromString($allColumn); // 列号 转 列数for ($rowIndex = 2; $rowIndex <= $allRow; $rowIndex++) {
//循环读取每个单元格的内容。注意行从1开始，列从A开始for ($colIndex = 0; $colIndex < $ColumnNum; $colIndex++) {
$goods_info[$rowIndex - 2][] = (string)$sheet->getCellByColumnAndRow($colIndex, $rowIndex)->getValue();}}
//处理完数据删除文件unlink($file);
导出
//示例//TODO 超时设置ignore_user_abort(true);set_time_limit(0);ini_set("memory_limit", "-1");$obpe = new \PHPExcel();//TODO 设置外边框$styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => 'thin', //设置border样式'color' => array('rgb' => 'D9E0EC'), //设置border颜色),),'alignment' => array('horizontal' => 'left',),);//TODO 设置表格宽度$obpe->getactivesheet()->getColumnDimension('A')->setWidth(20);$obpe->getactivesheet()->getColumnDimension('B')->setWidth(25);$obpe->getactivesheet()->getColumnDimension('C')->setWidth(30);$obpe->getactivesheet()->getColumnDimension('D')->setWidth(10);$obpe->getactivesheet()->getColumnDimension('E')->setWidth(10);$obpe->getactivesheet()->getColumnDimension('F')->setWidth(15);$obpe->getactivesheet()->getColumnDimension('G')->setWidth(10);$obpe->getactivesheet()->getColumnDimension('H')->setWidth(20);$obpe->getactivesheet()->getColumnDimension('I')->setWidth(40);//TODO 设置表头$obpe->getactivesheet()->setcellvalue('A1', '支付时间')->getStyle('A1')->applyFromArray($styleThinBlackBorderOutline);;$obpe->getactivesheet()->setcellvalue('B1', '订单号')->getStyle('B1')->applyFromArray($styleThinBlackBorderOutline);;$obpe->getactivesheet()->setcellvalue('C1', '第三方单号')->getStyle('C1')->applyFromArray($styleThinBlackBorderOutline);;$obpe->getactivesheet()->setcellvalue('D1', '支付类型')->getStyle('D1')->applyFromArray($styleThinBlackBorderOutline);;$obpe->getactivesheet()->setcellvalue('E1', '支付金额')->getStyle('E1')->applyFromArray($styleThinBlackBorderOutline);;$obpe->getactivesheet()->setcellvalue('F1', '支付状态')->getStyle('F1')->applyFromArray($styleThinBlackBorderOutline);;$obpe->getactivesheet()->setcellvalue('G1', '退款金额')->getStyle('G1')->applyFromArray($styleThinBlackBorderOutline);;$obpe->getactivesheet()->setcellvalue('H1', '退款时间')->getStyle('H1')->applyFromArray($styleThinBlackBorderOutline);;$obpe->getactivesheet()->setcellvalue('I1', '退款原因')->getStyle('I1')->applyFromArray($styleThinBlackBorderOutline);;//TODO 设置行高$obpe->getActiveSheet()->getRowDimension('1')->setRowHeight(17);$num = 0;$i = 1;$all_totalfee = 0;$all_refund = 0;$all_count = count($list);$all_refund_count = 0;
//$list 需要导出的数据foreach ($list as $key => $val) {
$num++;if ($num == 1000) {
//清空内存防止溢出ob_flush();flush();$num = 0;}$i = $i + 1;$status = '';if ($val['status'] == 1) {
$status = '支付成功';} else if ($val['status'] == 2) {
$status = '退款中';} else if ($val['status'] == 3) {
$status = '退款成功';}$obpe->getActiveSheet()->getRowDimension($i)->setRowHeight(17);$obpe->getactivesheet()->setcellvalue('A' . $i, '`' . $val['paytime'])->getStyle('A' . $i)->applyFromArray($styleThinBlackBorderOutline);$obpe->getactivesheet()->setcellvalue('B' . $i, '`' . $val['ordernum'])->getStyle('B' . $i)->applyFromArray($styleThinBlackBorderOutline);$obpe->getactivesheet()->setcellvalue('C' . $i, '`' . $val['transaction_id'])->getStyle('C' . $i)->applyFromArray($styleThinBlackBorderOutline);$obpe->getactivesheet()->setcellvalue('D' . $i, $val['channel'] == 1 ? "支付宝" : "微信")->getStyle('D' . $i)->applyFromArray($styleThinBlackBorderOutline);$obpe->getactivesheet()->setcellvalue('E' . $i, '`' . $val['totalfee'])->getStyle('E' . $i)->applyFromArray($styleThinBlackBorderOutline);$obpe->getactivesheet()->setcellvalue('F' . $i, $status)->getStyle('F' . $i)->applyFromArray($styleThinBlackBorderOutline);$obpe->getactivesheet()->setcellvalue('G' . $i, $val['status'] == 3 ? '`' . $val['totalfee'] : '')->getStyle('G' . $i)->applyFromArray($styleThinBlackBorderOutline);$obpe->getactivesheet()->setcellvalue('H' . $i, $val['refund_time'] ? '`' . date('Y-m-d H:i:s', $val['refund_time']) : '')->getStyle('H' . $i)->applyFromArray($styleThinBlackBorderOutline);$obpe->getactivesheet()->setcellvalue('I' . $i, $val['refund_reason'])->getStyle('I' . $i)->applyFromArray($styleThinBlackBorderOutline);$all_totalfee = $val['totalfee']+$all_totalfee;if($val['status'] == 3){
$all_refund = $val['totalfee']+$all_refund;$all_refund_count = $all_refund_count + 1;}}$i = $i+1;$obpe->getactivesheet()->setcellvalue('A' . $i, '')->getStyle('A' . $i)->applyFromArray($styleThinBlackBorderOutline);$i = $i+1;$obpe->getactivesheet()->setcellvalue('D' . $i, '总计')->getStyle('A' . $i)->applyFromArray($styleThinBlackBorderOutline);$obpe->getactivesheet()->setcellvalue('E' . $i, (string)$all_totalfee)->getStyle('C' . $i)->applyFromArray($styleThinBlackBorderOutline);$obpe->getactivesheet()->setcellvalue('G' . $i, (string)$all_refund)->getStyle('E' . $i)->applyFromArray($styleThinBlackBorderOutline);$obpe->setactivesheetindex(0); //工作副本1$obpe->getActiveSheet(0)->setTitle('商保普惠明细');$fileName = $other['drugstoresInfo']['name'] . '商保普惠明细';if ($other['postdata']['start_time'] == $other['postdata']['end_time']) {
$fileName .= "_{
$other['postdata']['start_time']}.xls";} else {
$fileName .= "_{
$other['postdata']['start_time']}到{
$other['postdata']['start_time']}.xls";}$obpe->setActiveSheetIndex(0);header('Content-Type: application/vnd.ms-excel');header("Content-Disposition: attachment;filename=\"$fileName\"");header('Cache-Control: max-age=0');//写入类容$obwrite = \PHPExcel_IOFactory::createWriter($obpe, 'Excel5');//保存文件ob_start();$obwrite->save('php://output'); //文件通过浏览器下载$xlsData = ob_get_contents();ob_end_clean();$data = ['file' => "data:application/vnd.ms-excel;base64," . base64_encode($xlsData),'file_name' => $fileName];return ['code' => $this->successCode, 'message' => '操作成功', 'data' => $data];