public function get_product_shipping_code($product_net_name, $input_user) { $user_id = fetch_user_id_by_login_name($input_user); return $this->get_one('product_net_name', 'shipping_code', array('user_id' => $user_id, 'net_name' => $product_net_name)); }
public function down_order_excel($id) { //$this->load->library('PHPExcel'); //$this->load->library('PHPExcel/IOFactory'); require_once APPPATH . 'libraries/PHPExcel' . EXT; require_once APPPATH . 'libraries/PHPExcel/IOFactory' . EXT; require_once APPPATH . 'libraries/PHPExcel/Worksheet/Drawing' . EXT; $CI =& get_instance(); $order = $this->order_model->get_order($id); //var_dump($order);die(); if ($order) { $print_date = date('Y.m.d'); $user = fetch_user_name_by_id(fetch_user_id_by_login_name($order->input_user)); $country_cn_name = get_country_name_cn($order->country); $objPHPExcel = new PHPExcel(); $objPHPExcel->createSheet(); //创建sheet $objPHPExcel->setActiveSheetIndex(0); $objActSheet = $objPHPExcel->getActiveSheet(); $objPHPExcel->getProperties()->setCreator("mallerp")->setLastModifiedBy("mallerp")->setTitle("Ebay ShipOrder List")->setSubject("Ebay ShipOrder List")->setDescription("Mansea, Mallerp, zhaosenlin, 278203374, 7410992")->setKeywords("Mansea, Mallerp, zhaosenlin, 278203374, 7410992")->setCategory("mallerp"); $objActSheet->getColumnDimension('E')->setWidth(20); $objActSheet->getColumnDimension('A')->setWidth(0); /*excel表头*/ $objPHPExcel->getActiveSheet()->setCellValue('A1', ''); $objPHPExcel->getActiveSheet()->setCellValue('A2', ''); $objPHPExcel->getActiveSheet()->setCellValue('A3', ''); $objPHPExcel->getActiveSheet()->setCellValue('A4', ''); $objActSheet->getStyle('B1:B4')->getFont()->setSize(12); $objActSheet->getStyle('B1:B4')->getFont()->setBold(true); $objActSheet->getStyle('D1:D4')->getFont()->setBold(true); $objActSheet->getStyle('F1:F4')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->mergeCells('H1:M4'); $objPHPExcel->getActiveSheet()->setCellValue('B1', '业务员'); $objPHPExcel->getActiveSheet()->setCellValue('C1', $user); $objPHPExcel->getActiveSheet()->setCellValue('D1', 'ERP订单号'); $objPHPExcel->getActiveSheet()->setCellValue('E1', $order->item_no); $objPHPExcel->getActiveSheet()->setCellValue('F1', '日期'); $objPHPExcel->getActiveSheet()->setCellValue('G1', $print_date); $objPHPExcel->getActiveSheet()->setCellValue('H1', '备注:' . $order->note); $objPHPExcel->getActiveSheet()->setCellValue('B2', '客户名称'); $objPHPExcel->getActiveSheet()->setCellValue('C2', $order->name); $objPHPExcel->getActiveSheet()->setCellValue('D2', '国家'); $objPHPExcel->getActiveSheet()->setCellValue('E2', $country_cn_name); $objPHPExcel->getActiveSheet()->setCellValue('F2', '运输方式'); $objPHPExcel->getActiveSheet()->setCellValue('G2', $order->is_register); $objPHPExcel->getActiveSheet()->setCellValue('B3', '订单总额$'); $objPHPExcel->getActiveSheet()->setCellValue('C3', $order->gross); $objPHPExcel->getActiveSheet()->setCellValue('D3', '运费$'); $objPHPExcel->getActiveSheet()->setCellValue('E3', $order->shippingamt); $objPHPExcel->getActiveSheet()->setCellValue('F3', '付款方式'); $objPHPExcel->getActiveSheet()->setCellValue('G3', $order->payment_type); $objPHPExcel->getActiveSheet()->setCellValue('B4', '订单总额¥'); $objPHPExcel->getActiveSheet()->setCellValue('C4', $order->gross * $order->ex_rate); $objPHPExcel->getActiveSheet()->setCellValue('D4', '运费¥'); $objPHPExcel->getActiveSheet()->setCellValue('E4', $order->shippingamt * $order->ex_rate); $objPHPExcel->getActiveSheet()->setCellValue('F4', '实际运费'); $objPHPExcel->getActiveSheet()->setCellValue('G4', ''); $objPHPExcel->getActiveSheet()->setCellValue('H4', '交易号'); $objPHPExcel->getActiveSheet()->setCellValue('I4', $order->transaction_id); //$objPHPExcel->getActiveSheet()->mergeCells("F5":"G5":"H5"); $objActSheet->getStyle('B5:M5')->getFont()->setSize(12); $objActSheet->getStyle('B5:M5')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->mergeCells('F5:H5'); $objPHPExcel->getActiveSheet()->setCellValue('B5', '货架号'); $objPHPExcel->getActiveSheet()->setCellValue('C5', 'SKU'); $objPHPExcel->getActiveSheet()->setCellValue('D5', '数量'); $objPHPExcel->getActiveSheet()->setCellValue('E5', '图片'); $objPHPExcel->getActiveSheet()->setCellValue('F5', '中文名称'); $objPHPExcel->getActiveSheet()->setCellValue('I5', '单价$'); $objPHPExcel->getActiveSheet()->setCellValue('J5', '单价¥'); $objPHPExcel->getActiveSheet()->setCellValue('K5', '总价¥'); $objPHPExcel->getActiveSheet()->setCellValue('L5', '成本¥'); $objPHPExcel->getActiveSheet()->setCellValue('M5', '利润¥'); $skus = explode(',', $order->sku_str); $qties = explode(',', $order->qty_str); $item_prices = explode(',', $order->item_price_str); $i = 5; foreach ($skus as $key => $sku) { $i++; $sql1 = 'name_cn,shelf_code,sale_price,image_url,price'; $myproduct = $CI->product_model->fetch_product_by_sku($sku, $sql1); $image = ""; if ($myproduct->image_url != '' && $myproduct->image_url != NULL && $myproduct->image_url != 'none') { if (strpos($myproduct->image_url, 'http://') !== false) { $image = "/var/www/html/mallerp/static/images/404-error.png"; } else { $image = '/var/www/html/mallerp' . $myproduct->image_url; } } else { $image = "/var/www/html/mallerp/static/images/404-error.png"; } //die($image); $objActSheet->getRowDimension($i)->setRowHeight(100); $objActSheet->getStyle('B' . $i . ':M' . $i)->getFont()->setSize(12); $objPHPExcel->getActiveSheet()->mergeCells('F' . $i . ':H' . $i); $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $myproduct->shelf_code); $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $sku); $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $qties[$key]); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('avatar'); $objDrawing->setDescription('avatar'); $objDrawing->setPath($image); $objDrawing->setHeight(100); $objDrawing->setWidth(100); $objDrawing->setCoordinates('E' . $i); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); //$objPHPExcel->getActiveSheet()->setCellValue('E'.$i, '图片'); $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $myproduct->name_cn); $objPHPExcel->getActiveSheet()->setCellValue('I' . $i, isset($item_prices[$key]) ? $item_prices[$key] : 0); $objPHPExcel->getActiveSheet()->setCellValue('J' . $i, isset($item_prices[$key]) ? $item_prices[$key] : 0 * $order->ex_rate); $objPHPExcel->getActiveSheet()->setCellValue('K' . $i, isset($item_prices[$key]) ? $item_prices[$key] : 0 * $order->ex_rate * $qties[$key]); $objPHPExcel->getActiveSheet()->setCellValue('L' . $i, ''); $objPHPExcel->getActiveSheet()->setCellValue('M' . $i, ''); } } $filename = date("Y-m-d_H_i_s") . '.xls'; ob_end_clean(); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename=' . $filename); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); }