php导出合同模板到excel
/** * [export_excel 合同导出] * [@param itemid:单号] * @return [type] */ public function export_excel() { //接受数据 $itemid = I('itemid'); $public_m = D('Public'); if ($itemid == '') { return 100; } $order_trade_system_m = M('order_trade_system'); $list = $order_trade_system_m->where('tid="' . $itemid . '"')->find(); switch ($list['pay_type']) { case 'cash': $list['pay_type'] = '现金支付'; break; case 'transfers': $list['pay_type'] = '对公转账'; break; case 'alipay': $list['pay_type'] = '支付宝'; break; default: break; } //获取地址信息 $address_info = D('customer')->address_info($list['address_id']); $address_info = $address_info['list']; $receiver_state = $public_m->Get_areaname($address_info['receiver_state']); $receiver_city = $public_m->Get_areaname($address_info['receiver_city']); $receiver_district = $public_m->Get_areaname($address_info['receiver_district']); $address = $receiver_state . $receiver_city . $receiver_district . $address_info['receiver_address']; //查找供应商名称 $order_customer_m = M('order_customer'); $customer_info = $order_customer_m->where('customer_id="' . $list['customer_id'] . '"')->field('company_name,company_address')->find(); $company_name = $customer_info['company_name']; $Column_num = 6; //列数 $Line_num = 36; //行数 $ColumnWidth = array('5', '38', '14', '10', '10', '10', '16'); //列宽度 $LineHeight = array('30', '22', '22', '22', '22', '22', '22'); //行高度 //设置合并单元格数组 $mergeCells = array('A1:F1', 'A3:C3', 'E3:G3', 'A4:C4', 'E4:G4', 'A7:G7'); $contract_number = $list['contract_number']; if (empty($contract_number)) { $contract_number = $public_m->get_contract_number_no(); $list = $order_trade_system_m->where('tid="' . $itemid . '"')->data('contract_number=' . $contract_number . '')->save(); //更新订单数据 } //单元格值 $cell_arr = array( 'A1' => '产品购销合同', 'A3' => '甲方：**科技（上海）有限公司', 'E3' => '合同编号：' . $contract_number, 'A4' => '乙方：' . $company_name, 'E4' => '签定地点：**', 'A6' => '根据《中华人民共和国合同法》的有关规定，甲乙双方经友好协商一致，签订合同。', 'A7' => '一、 产品名称、型号、数量、金额', 'A8' => '序号', 'B8' => '产品名称', 'C8' => '规格型号', 'D8' => '单位', 'E8' => '数量', 'F8' => '单价(元)', 'G8' => '金额(元)', ); //设置字体样式 $font_arr = array( 'A1' => array('name' => '宋体', 'size' => 18, 'bold' => true), 'A7' => array('name' => '宋体', 'size' => 12, 'bold' => true), 'A8' => array('name' => '宋体', 'size' => 12, 'bold' => true), 'B8' => array('name' => '宋体', 'size' => 12, 'bold' => true), 'C8' => array('name' => '宋体', 'size' => 12, 'bold' => true), 'D8' => array('name' => '宋体', 'size' => 12, 'bold' => true), 'E8' => array('name' => '宋体', 'size' => 12, 'bold' => true), 'F8' => array('name' => '宋体', 'size' => 12, 'bold' => true), 'G8' => array('name' => '宋体', 'size' => 12, 'bold' => true), ); //对齐方式 $align_arr = array( //水平对齐 'h' => array( 'left' => array('A2', 'A3', 'A4'), 'center' => array('A1', 'A5', 'B5', 'B8', 'A8', 'C8', 'D8', 'E8', 'F8', 'G8', 'E4'), 'right' => array('B2', 'E3') ), //垂直对齐 'v' => array( 'center' => array('A1', 'A2', 'A3', 'A4', 'A8', 'A5', 'B5', 'E3', 'E4', 'A6', 'A8', 'B8', 'C8', 'D4', 'E6', 'F8', 'A7', 'D8', 'E8', 'F8', 'G8' ), ), ); //设置四边边框 $all_border = array( 'A8', 'B8', 'C8', 'D8', 'E8', 'F8', 'G8' ); //查看商品详细 $order_sku = M('order_sku'); $data = $order_sku->where('tid="' . $itemid . '"')->select(); foreach ($data as $key => $v) { $goods[$key]['title'] = $public_m->get_name($v['sku_id'], 'title'); $goods[$key]['number'] = $v['number']; $goods[$key]['price'] = $v['price']; $goods[$key]['properties'] = $public_m->get_sku_properties($v['sku_id'], 'sku_id'); $goods[$key]['unit_name'] = $public_m->get_sku_unit($v['sku_id']); } //设置循环单元格值 $while_arr = array( 'field' => array('A', 'B', 'C', 'D', 'E', 'F', 'G'), 'start' => 9, 'border' => true, 'format' => array('A' => '0', 'B' => '@', 'C' => '@', 'D' => '#,##0.00', 'E' => '0', 'F' => '#,##0.00', 'G' => '#,##0.00'), //水平对齐 'align' => array( 'h' => array( 'left' => array('B'), //左对齐 'center' => array('A', 'C', 'D', 'E', 'F', 'G'), //居中对齐 //'right'=>array()//右对齐 ), //垂直对齐 'v' => array( 'center' => array('A', 'B', 'C', 'D', 'E', 'F', 'G'), //水平垂直 ), ), ); foreach ($goods as $row) { $title = $row['title']; $number = $row['number']; $properties = $row['properties']; $new_price = $number * $row['price']; $allmoney+=$new_price; $price = number_format($row['price'], 2); $n = $i + 9; $LineHeight[] = 20; $while_arr['list'][] = array($i + 1, $title, $properties, $row['unit_name'], $number, $price, $new_price); //循环表格数值 $i++; } $title = '产品购销合同'; $path = './Public/Data/Order/'; if (!is_dir($path)) mkdir($path, 0755); $file_name = './Public/Data/Order/' . $contract_number . '.xls'; $all_moeny_capital = $this->cny($allmoney); $allmoney_str = strstr($allmoney, '.'); if (empty($allmoney_str)) { $all_moeny_capital = $all_moeny_capital . '整'; } $allmoney = number_format($allmoney, 2); //设置循环尾 $bank_name = C('bank_name'); $account_number = C('account_number'); $last_arr = array( 'jia' => array('0'), 'field' => array( array('A', 'D', 'G'), array('A'),//11 array('A'),//12 array('A'),//13 array('A'),//14 array('A'),//15 array('A'),//16 array('A'),//17 array('A'),//18 array('A'),//19 array('A'),//20 array('A'),//21 array('A'),//22 array('A'),//23 array('A'),//24 array('A'),//25---- array('A'),//26 array('A'),//27 array('A'),//28 array('A'),//29 array('A'),//30 array('A'),//31 array('A'),//32 array('A'),//33 array('A'),//34 array('A'),//35 array('A'),//36 array('A'),//37 array('A'),//38 array('A'),//39 array('A'),//40 array('A'),//41 array('A'),//42 array('A'),//43 array('A'),//44 array('A'),//45 array('A'),//46 array('A', 'C'),//47 array('A', 'C'),//48 array('A', 'C'),//49 array('A', 'C'),//50 array('A', 'C'),//51 array('A', 'C'),//52 ), 'value' => array( array('金额合计：' . $all_moeny_capital, '', '￥' . $allmoney), array(''), array('二、质量要求、技术标准、供方对质量负责的条件和期限'), array('按产品规范及有关的国家标准执行，甲方对所供产品在三十日内如出现质量问题，负责退换，质保期半年。'), array(''), array('三、交（退）货地点、方式'), array('由甲方负责将乙方所购产品交付到乙方指定的地点'), array('乙方指定的送货地址为：' . $address), array(''), array('四、运输方式及到达港和费用负担'), array('甲方负责运输并办理货物运输保险，运输费用及保险费由甲方负担。'), array(''), array('五、包装标准、包装物的供应和回收'), array('包装标准按产品要求和国家标准执行，费用由甲方负担。'), array(''), array('六、验收标准、方法、及提出异议期限'), //25--- array('在乙方指定的地点，按产品规范及有关的国家标准验收。'), array(''), array('七、 交货期限'), array('合同签订之日起 3 天内送货至乙方指定的地点。'), array(''), array('八、结算方式及期限'), array('结算方式：' . $list['pay_type']), array('付款期限:乙方收到货物之日起 2 日内付款至甲方指定账户 '), array('甲方指定的银行账户如下'), array('开户行:' . $bank_name), array('户名：***科技（上海）有限公司'), array('账号：' . $account_number), array('九、解决合同纠纷的方式'), array('执行本合同发生争议，由甲乙双方协商解决，如协商不成可提请仲裁委员会仲裁。'), array(''), array('十、其他约定事项'), array('甲乙双方盖章后生效。如有未尽事宜，甲乙双方协商解决。'), array(''), array('十一、 文本'), array('本合同一式二份，甲乙双方各执一份。'), array(''), array('甲方', '乙方'), array('单位名称：***科技（上海）有限公司', '单位名称：' . $company_name), array('地址：********', '地址：' . $customer_info['company_address']), array('法定代表人（盖章）：', '法定代表人（盖章）：'), array('时 间： 年 月 日', '时 间： 年 月 日'), array('电 话：021-60486481', '电 话：'), ), 'cell' => array( array('A', 'C'),//10 array('D', 'F'),//10 array('G', 'G'),//10 array('A', 'G'),//11 array('A', 'G'),//12 array('A', 'G'),//13 array('A', 'G'),//14 array('A', 'G'),//15 array('A', 'G'),//16 array('A', 'G'),//17 array('A', 'G'),//18 array('A', 'G'),//19 array('A', 'G'),//20 array('A', 'G'),//21 array('A', 'G'),//22 array('A', 'G'),//23 array('A', 'G'),//24 array('G', 'G'),// array('A', 'G'),//26 array('A', 'G'),//27 array('A', 'G'),//28 array('A', 'G'),//29 array('A', 'G'),//30 array('A', 'G'),//31 array('A', 'G'),//32 array('A', 'G'),//33 array('A', 'G'),//34 array('A', 'G'),//35 array('A', 'G'),//36 array('A', 'G'),//37 array('A', 'G'),//38 array('A', 'G'),//39 array('A', 'G'),//40 array('A', 'G'),//41 array('A', 'G'),//42 array('A', 'G'),//43 array('A', 'G'),//44 array('A', 'G'),//45 array('A', 'G'),//46 array('A', 'B'),//47 array('C', 'G'),//47 array('A', 'B'),//48 array('C', 'G'),//48 array('A', 'B'),//49 array('C', 'G'),//49 array('A', 'B'),//50 array('C', 'G'),//50 array('A', 'B'),//51 array('C', 'G'),//51 array('A', 'B'),//52 array('C', 'G'),//52 ), 'align' => array( array('h' => 'center', 'v' => 'center'), array(''), array('h' => 'right', 'v' => 'center'), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array('h' => 'center', 'v' => 'center'), array('h' => 'center', 'v' => 'center'), ), 'height' => array('30'), 'bold' => array('true', 'false', 'true', 'false', 'false', 'true', 'false', 'false', 'false', 'true', 'false', 'false', 'true', 'false', 'false', 'true', 'false', 'true', 'true', 'false', 'false', 'true', 'false', 'false', 'false', 'false', 'false', 'false', 'true', 'false', 'false', 'true', 'false', 'false', 'true'), 'borders' => array( array('left' => 'true', 'right' => 'true', 'top' => 'true', 'bottom' => 'true'), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array(''), array('left' => 'false', 'right' => 'true', 'top' => 'true', 'bottom' => 'false'), array('left' => 'false', 'right' => 'true', 'top' => 'false', 'bottom' => 'false'), array('left' => 'false', 'right' => 'true', 'top' => 'false', 'bottom' => 'false'), array('left' => 'false', 'right' => 'true', 'top' => 'false', 'bottom' => 'false'), array('left' => 'false', 'right' => 'true', 'top' => 'false', 'bottom' => 'false'), array('left' => 'false', 'right' => 'true', 'top' => 'false', 'bottom' => 'true'), array('left' => 'false', 'right' => 'true', 'top' => 'false', 'bottom' => 'true'), ), ); $res = $public_m->excels($title, $Column_num, $Line_num, $ColumnWidth, $LineHeight, $mergeCells, $cell_arr, $while_arr, $last_arr, $align_arr, $all_border, $font_arr, $file_name, $width_unit); if ($res == 'OK') { D('public')->WriteLog(session('user.personnel_code'), '导出合同', $itemid, 'trade'); $data = array('url' => $file_name); return $data; } }
function cny($ns) { static $cnums = array("零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖"), $cnyunits = array("圆", "角", "分"), $grees = array("拾", "佰", "仟", "万", "拾", "佰", "仟", "亿"); list($ns1, $ns2) = explode(".", $ns, 2); $ns2 = array_filter(array($ns2[1], $ns2[0])); $ret = array_merge($ns2, array(implode("", $this->_cny_map_unit(str_split($ns1), $grees)), "")); $ret = implode("", array_reverse($this->_cny_map_unit($ret, $cnyunits))); return str_replace(array_keys($cnums), $cnums, $ret); } function _cny_map_unit($list, $units) { $ul = count($units); $xs = array(); foreach (array_reverse($list) as $x) { $l = count($xs); if ($x != "0" || !($l % 4)) $n = ($x == '0' ? '' : $x) . ($units[($l - 1) % $ul]); else $n = is_numeric($xs[0][0]) ? $x : ''; array_unshift($xs, $n); } return $xs; }
Application\Oa\Model\PublicModel.class.php
/** * @desc 自动生成表格 * @param string $title 标题 * @param int $Column_num 列数 * @param int $Line_num 行数 * @param array $ColumnWidth 列宽 * @param array $LineHeight 行高 * @param array $mergeCells 合并单元格数组 * @param array $cell_arr 单元格值 * @param array $while_arr 循环单元格值 * @param array $last_arr 循环尾 * @param array $align_arr 对齐方式 * @param array $all_border 四边边框 * @param array $font_arr 字体样式 * @param string $file_name 文件名 */ function excels($title, $Column_num, $Line_num, $ColumnWidth, $LineHeight, $mergeCells, $cell_arr, $while_arr, $last_arr, $align_arr, $all_border, $font_arr, $file_name) { $ColumnName = 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', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'); Vendor('Excel.PHPExcel'); // 创建一个处理对象实例 $objExcel = new \PHPExcel(); // 创建文件格式写入对象实例, uncomment $objWriter = new \PHPExcel_Writer_Excel5($objExcel); //设置文档基本属性 $objProps = $objExcel->getProperties(); $objProps->setCreator("toby"); $objProps->setLastModifiedBy("toby"); $objProps->setTitle($title); $objProps->setSubject($title); $objProps->setDescription($title); $objProps->setKeywords($title); $objProps->setCategory($title); //缺省情况下，PHPExcel会自动创建第一个sheet被设置SheetIndex=0 $objExcel->setActiveSheetIndex(0); $objActSheet = $objExcel->getActiveSheet(); //设置当前活动sheet的名称 $objActSheet->setTitle($title); //设置合并单元格 foreach ($mergeCells as $v) { $objActSheet->mergeCells($v); } //设置列宽， for ($i = 0; $i < $Column_num; $i++) { $objActSheet->getColumnDimension($ColumnName[$i])->setWidth($ColumnWidth[$i]); } //设置行高度 for ($i = 0; $i < $Line_num; $i++) { $objActSheet->getRowDimension($i + 1)->setRowHeight($LineHeight[$i]); } //设置单元格值 foreach ($cell_arr as $k => $v) { $objActSheet->setCellValue($k, $v); } //设置循环单元格值 $i = $while_arr['start']; foreach ($while_arr['list'] as $vh) { $field = $while_arr['field']; $bord = $while_arr['border']; $h = $while_arr['align']['h']; $v = $while_arr['align']['v']; $format = $while_arr['format']; //print_r($h);exit; foreach ($vh as $k => $vs) { //设置单元格格式 $format_k = $format[$field[$k]]; if (!empty($format_k)) { $objActSheet->getStyle($field[$k] . $i)->getNumberFormat()->setFormatCode($format_k); } //设置单元格值 $objActSheet->setCellValue($field[$k] . $i, $vs); //设置四边边框 if ($bord) { $all_border[] = $ColumnName[$k] . $i; } $num = count($align_arr['h']['center']); //设置水平对齐 $align_arr['h']['center'][$num] = $h['center'][$k] . $i; //$align_arr['h'][]=array($h[$k]=>$field[$k].$i); //设置垂直对齐 $num_t = count($align_arr['v']['center']); $align_arr['v']['center'][$num_t] = $v['center'][$k] . $i; } $i++; } //设置循环尾 $column_s = 0; if (!empty($last_arr['value'])) { foreach ($last_arr['value'] as $k => $vh) { $i = $i + $last_arr['jia'][$k]; $field = $last_arr['field']; $bord = $last_arr['border']; $align = $last_arr['align']; $cell = $last_arr['cell']; $height = $last_arr['height']; $objActSheet->getRowDimension($i)->setRowHeight($height[$k]); //每列内容 foreach ($vh as $ks => $vs) { $objActSheet->mergeCells($cell[$column_s][0] . $i . ':' . $cell[$column_s][1] . $i); //额外 $tmp_st = false; $tmp = $last_arr['bold'][$k]; $t[] = $tmp; if ($tmp == 'true') { $tmp_st = true; } //加粗 $objStyleA2 = $objActSheet->getStyle($cell[$column_s][0] . $i . ':' . $cell[$column_s][1] . $i); $objFontA2 = $objStyleA2->getFont(); $objFontA2->setBold($tmp_st); //设置四边边框 $m = $last_arr['borders'][$k]; $left = $m['left']; $right = $m['right']; $top = $m['top']; $bottom = $m['bottom']; if ($left == 'true') { $objActSheet->getStyle($cell[$column_s][0] . $i . ':' . $cell[$column_s][1] . $i)->getBorders()->getLeft()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); } if ($right == 'true') { $objActSheet->getStyle($cell[$column_s][0] . $i . ':' . $cell[$column_s][1] . $i)->getBorders()->getRight()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); } if ($top == 'true') { $objActSheet->getStyle($cell[$column_s][0] . $i . ':' . $cell[$column_s][1] . $i)->getBorders()->getTop()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); } if ($bottom == 'true') { $objActSheet->getStyle($cell[$column_s][0] . $i . ':' . $cell[$column_s][1] . $i)->getBorders()->getBottom()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); } //额外***end //设置单元格值 $objActSheet->setCellValue($field[$k][$ks] . $i, $vs); //设置四边边框 if ($bord) { foreach ($ColumnName as $vv) { if ($vv == $cell[$column_s][0]) { $j = 1; } elseif ($j <> 1) { continue; } $all_border[] = $vv . $i; if ($vv == $cell[$column_s][1]) { break; } } } //设置水平对齐 $align_arr['h'][$align[$column_s]['h']][] = $field[$k][$ks] . $i; //设置垂直对齐 $align_arr['v'][$align[$column_s]['v']][] = $field[$k][$ks] . $i; $column_s++; } $i++; } } //设置水平对齐 foreach ($align_arr['h'] as $k => $v) { foreach ($v as $vs) { if ($k == 'left') { $objActSheet->getStyle($vs)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT); } if ($k == 'center') { $objActSheet->getStyle($vs)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); } if ($k == 'right') { $objActSheet->getStyle($vs)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); } } } //设置垂直对齐 foreach ($align_arr['v'] as $k => $v) { foreach ($v as $vs) { if ($k == 'top') { $objActSheet->getStyle($vs)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP); } if ($k == 'center') { $objActSheet->getStyle($vs)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); } if ($k == 'bottom') { $objActSheet->getStyle($vs)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_BOTTOM); } } } //设置字体样式 foreach ($font_arr as $k => $v) { $objStyleA1 = $objActSheet->getStyle($k); $objFontA1 = $objStyleA1->getFont(); $objFontA1->setName($v['name']); $objFontA1->setSize($v['size']); $objFontA1->setBold($v['bold']); }//设置四边边框 foreach ($all_border as $v) { $objActSheet->getStyle($v)->getBorders()->getTop()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); $objActSheet->getStyle($v)->getBorders()->getLeft()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); $objActSheet->getStyle($v)->getBorders()->getRight()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); $objActSheet->getStyle($v)->getBorders()->getBottom()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); } //输出内容 $objWriter->save($file_name); //print_r($last_arr['value']);print_r($t);print_r($last_arr['bold']);exit; return "OK"; }
导出模板
