/** * 导出台账 */ @RequestMapping(params = "ExportXltz") @ResponseBody public void ExportXltz(HttpServletRequest request, ModelMap modelMap, HttpServletResponse response, OutputStream output) throws UnsupportedEncodingException { String invoiceno = request.getParameter("invoiceno");//发票号 String sendTime_begin = request.getParameter("sendTime_begin");//发送日期 String sendTime_end = request.getParameter("sendTime_end");//发送日期 String orderSupply = request.getParameter("orderSupply");//代理商 String zdDate_begin = request.getParameter("zdDate_begin");//账单日期 String zdDate_end = request.getParameter("zdDate_end");//账单日期 String declaredate_begin = request.getParameter("declaredate_begin");//申报日期开始时间 String declaredate_end = request.getParameter("declaredate_end");//申报日期结束时间 String wheresql = " "; if(invoiceno!=null && !invoiceno.equals("")) { wheresql += " and a.invoice_code='" + invoiceno + "'"; } if(sendTime_begin!=null && !sendTime_begin.equals("")) { wheresql += " and a.send_time >='" + sendTime_begin + "'"; } if(sendTime_end!=null && !sendTime_end.equals("")) { wheresql += " and a.send_time <='" + sendTime_end + "'"; } if(orderSupply!=null && !orderSupply.equals("")) { wheresql += " and c.order_supplyid='" + orderSupply + "'"; } if(zdDate_begin!=null && !zdDate_begin.equals("")) { wheresql += " and c.zdDate >='" + zdDate_begin + "'"; } if(zdDate_end!=null && !zdDate_end.equals("")) { wheresql += " and c.zdDate <='" + zdDate_end + "'"; } if(declaredate_begin!=null && !declaredate_begin.equals("")) { wheresql += " and b.declaration_data >='" + declaredate_begin + "'"; } if(declaredate_end!=null && !declaredate_end.equals("")) { wheresql += " and b.declaration_data <='" + declaredate_end + "'"; } String sql = " select * " + " from dec_order a " + " left join dec_main b on (b.erp_No = a.invoice_code)" + " left join (select Invoiceno,order_supply,order_supplyid,zdDate" + " from ldc_order_tax " + " group by Invoiceno) c on (c.Invoiceno = a.invoice_code)"+ wheresql + " order by b.create_date desc "; String lujing = request.getSession().getServletContext().getRealPath("/"); String lujing1 = lujing + "export\template\jkfymx.xlsx"; List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql); InputStream in; try { in = new FileInputStream(new File(lujing1)); XSSFWorkbook work = null; work = new XSSFWorkbook(in); this.ExportXltzOut(request, response, work, maps); } catch (Exception e) { e.printStackTrace(); } } /** * 导出 */ public void ExportXltzOut(HttpServletRequest request, HttpServletResponse response, XSSFWorkbook work, List<Map<String, Object>> list) { XSSFSheet sheetAt = work.getSheetAt(0); XSSFCellStyle setBorder = work.createCellStyle(); setBorder.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框 setBorder.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框 setBorder.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框 setBorder.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框 setBorder.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中 setBorder.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中 SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); Map<String, Object> map = new HashMap<>(); String invoice_code_old = ""; int cnt = 0; for(int i=0;i<list.size();i++){ if(i<list.size()){ map = list.get(i); } String invoice_code = String.valueOf(map.get("invoice_code")); XSSFRow row = sheetAt.createRow(i+2); //物流公司名称 if(map.get("order_supply") !=null) { row.createCell(0).setCellValue(String.valueOf(map.get("order_supply"))); row.getCell(0).setCellStyle(setBorder); }else { row.createCell(0).setCellValue(""); row.getCell(0).setCellStyle(setBorder); } //进口发票号码 if(map.get("invoice_code") !=null) { row.createCell(1).setCellValue(String.valueOf(map.get("invoice_code"))); row.getCell(1).setCellStyle(setBorder); }else { row.createCell(1).setCellValue(""); row.getCell(1).setCellStyle(setBorder); } //到厂日期 JSONObject jsonb = JSONObject.parseObject(String.valueOf(map.get("content"))); if(StringUtils.isNotBlank(jsonb.getString("dcdate"))) { row.createCell(4).setCellValue(jsonb.getString("dcdate")); row.getCell(4).setCellStyle(setBorder); }else { row.createCell(4).setCellValue(""); row.getCell(4).setCellStyle(setBorder); } //报关单号 if(map.get("entry_Id") !=null) { row.createCell(5).setCellValue(String.valueOf(map.get("entry_Id"))); row.getCell(5).setCellStyle(setBorder); }else { row.createCell(5).setCellValue(""); row.getCell(5).setCellStyle(setBorder); } //贸易方式 if(StringUtils.isNotBlank(jsonb.getString("tradeMode_value"))) { row.createCell(6).setCellValue(jsonb.getString("tradeMode_value")); row.getCell(6).setCellStyle(setBorder); }else { row.createCell(6).setCellValue(""); row.getCell(6).setCellStyle(setBorder); } //申报日期 if(StringUtils.isNotBlank(jsonb.getString("declarationData"))) { row.createCell(7).setCellValue(jsonb.getString("declarationData")); row.getCell(7).setCellStyle(setBorder); }else { row.createCell(7).setCellValue(""); row.getCell(7).setCellStyle(setBorder); } String str = String.valueOf(map.get("json_extract")); JSONObject json = JSONObject.parseObject(str); if(json.containsKey("jsBLorawbNo")){ String jsBLorawbNo = json.getString("jsBLorawbNo");/**B/L OR AWB NO*/ row.createCell(2).setCellValue(jsBLorawbNo); row.getCell(2).setCellStyle(setBorder); }else{ row.createCell(2).setCellValue(""); row.getCell(2).setCellStyle(setBorder); } if(json.containsKey("jsEta")){ String jsEta = json.getString("jsEta");/**ETA*/ row.createCell(3).setCellValue(jsEta); row.getCell(3).setCellStyle(setBorder); }else{ row.createCell(3).setCellValue(""); row.getCell(3).setCellStyle(setBorder); } if(json.containsKey("jsHy")){ String jsHy = json.getString("jsHy");/**海运整柜（注明箱型）*/ row.createCell(8).setCellValue(jsHy); row.getCell(8).setCellStyle(setBorder); }else{ row.createCell(8).setCellValue(""); row.getCell(8).setCellStyle(setBorder); } if(json.containsKey("jsJzxNum")){ String jsJzxNum = json.getString("jsJzxNum");/**集装箱数量*/ row.createCell(9).setCellValue(jsJzxNum); row.getCell(9).setCellStyle(setBorder); }else{ row.createCell(9).setCellValue(""); row.getCell(9).setCellStyle(setBorder); } if(json.containsKey("jsWeight")){ String jsWeight = json.getString("jsWeight");/**毛重*/ row.createCell(10).setCellValue(jsWeight); row.getCell(10).setCellStyle(setBorder); }else{ row.createCell(10).setCellValue(""); row.getCell(10).setCellStyle(setBorder); } if(json.containsKey("jsTj")){ String jsTj = json.getString("jsTj");/**体积*/ row.createCell(11).setCellValue(jsTj); row.getCell(11).setCellStyle(setBorder); }else{ row.createCell(11).setCellValue(""); row.getCell(11).setCellStyle(setBorder); } if(json.containsKey("jsSumfy")){ String jsSumfy = json.getString("jsSumfy");/**物流费用合计金额*/ row.createCell(30).setCellValue(jsSumfy); row.getCell(30).setCellStyle(setBorder); }else{ row.createCell(30).setCellValue(""); row.getCell(30).setCellStyle(setBorder); } /**关税金额（必须和关税单金额核对）*/ String tariff = "";//关税 String addedTax = "";//增值税 double sumNum = 0;//同发票号关税和 double sumNum1 = 0;//同发票号增值税和 if(jsonb.containsKey("decLists")) { String string = jsonb.getString("decLists"); JSONArray picArray = JSONArray.parseArray(string); for(int q =0;q<picArray.size();q++){ JSONObject json1 = picArray.getJSONObject(q); if(json1.get("tariff")!=null){ sumNum += Double.parseDouble(json1.getString("tariff")); //**关税金额（必须和关税单金额核对）*//* sumNum1 += Double.parseDouble(json1.getString("addedTax")); } } row.createCell(31).setCellValue(sumNum+" "); row.getCell(31).setCellStyle(setBorder); row.createCell(32).setCellValue(sumNum1+" "); row.getCell(32).setCellStyle(setBorder); } if(json.containsKey("jsFybm")){ String jsFybm = json.getString("jsFybm");/**费用部门*/ row.createCell(33).setCellValue(jsFybm); row.getCell(33).setCellStyle(setBorder); }else{ row.createCell(33).setCellValue(""); row.getCell(33).setCellStyle(setBorder); } String sql = " select * from ldc_order_tax a where a.Invoiceno = '"+invoice_code+"' "; List<Map<String, Object>> list1 = jdbcTemplate.queryForList(sql); for(int j=12;j<31;j++){ row.createCell(j).setCellValue(""); } for(int k=0;k<list1.size();k++){ Map<String, Object> map1 = new HashMap<>(); if(k<list1.size()){ map1 = list1.get(k); } if(map1.get("order_tax_name")!=null){ String order_tax_name = String.valueOf(map1.get("order_tax_name")); String order_tax = String.valueOf(map1.get("order_tax")); if(map1.get("order_tax")!=null){ order_tax = String.valueOf(map1.get("order_tax")); }else{ order_tax = ""; } if("报关费".equals(order_tax_name)){ row.createCell(12).setCellValue(order_tax); } if("换单费".equals(order_tax_name)) { row.createCell(13).setCellValue(order_tax); } if("查验费".equals(order_tax_name)) { row.createCell(14).setCellValue(order_tax); } if("港杂费".equals(order_tax_name)) { row.createCell(15).setCellValue(order_tax); } if("THC".equals(order_tax_name)) { row.createCell(16).setCellValue(order_tax); } if("LSSBAFCAF".equals(order_tax_name.trim())) { row.createCell(17).setCellValue(order_tax); } if("报检费安保费".equals(order_tax_name)) { row.createCell(18).setCellValue(order_tax); } if("换单代理费".equals(order_tax_name)) { row.createCell(19).setCellValue(order_tax); } if("三检费".equals(order_tax_name)) { row.createCell(20).setCellValue(order_tax); } if("代垫仓库理货费(港建 理货 搬移 )".equals(order_tax_name)) { row.createCell(21).setCellValue(order_tax); } if("仓储费".equals(order_tax_name)) { row.createCell(22).setCellValue(order_tax); } if("木托销毁".equals(order_tax_name)) { row.createCell(23).setCellValue(order_tax); } if("检验代理费".equals(order_tax_name)) { row.createCell(24).setCellValue(order_tax); } if("拆箱费".equals(order_tax_name)) { row.createCell(25).setCellValue(order_tax); } if("运费".equals(order_tax_name)) { row.createCell(26).setCellValue(order_tax); } if("坏污箱费".equals(order_tax_name)) { row.createCell(27).setCellValue(order_tax); } if("集装箱超期费".equals(order_tax_name)) { row.createCell(28).setCellValue(order_tax); } if("其它".equals(order_tax_name)) { row.createCell(29).setCellValue(order_tax); } if("物流费".equals(order_tax_name)) { row.createCell(30).setCellValue(order_tax); } // if("关税".equals(order_tax_name)) { // row.createCell(31).setCellValue(order_tax); // } // if("增值税".equals(order_tax_name)) { // row.createCell(32).setCellValue(order_tax); // } } } if(invoice_code.equals(invoice_code_old)){ cnt++; if(i==list.size()-1){ //当最后一行时看情况合并 for(int j=0;j<34;j++){ if(j!=4&&j!=5&&j!=6&&j!=7){ row.getCell(j).setCellValue(""); CellRangeAddress region1 = new CellRangeAddress(i-cnt+2, i+2, (short) j, (short) j); sheetAt.addMergedRegion(region1); } } } }else{ if(cnt>0){ //合并单元格 for(int j=0;j<34;j++){ if(j!=4&&j!=5&&j!=6&&j!=7){ row.getCell(j).setCellValue(""); CellRangeAddress region1 = new CellRangeAddress(i+2-cnt-1, i-1+2, (short) j, (short) j); sheetAt.addMergedRegion(region1); } } } cnt=0; } invoice_code_old=invoice_code; for(int j=12;j<31;j++){ row.getCell(j).setCellStyle(setBorder); } } //合计 XSSFRow row = sheetAt.createRow(list.size()+3); for (int i = 0; i <34; i++) { row.createCell(i); row.getCell(i).setCellStyle(setBorder); } row.createCell(0).setCellValue("合计："); row.getCell(0).setCellStyle(setBorder); row.getCell(8).setCellStyle(setBorder); for(int j=12;j<33;j++){ double sum = 0; for(int i=2;i<list.size()+2;i++){ String value = sheetAt.getRow(i).getCell(j).getStringCellValue(); if(StringUtils.isEmpty(value)){ value="0"; } sum += Double.parseDouble(value); } row.createCell(j).setCellValue(sum); row.getCell(j).setCellStyle(setBorder); } //前台反应 response.reset(); response.setCharacterEncoding("UTF-8"); response.setContentType("application/vnd.ms-excel"); //保证不乱码 try { Date date=new Date(); SimpleDateFormat format=new SimpleDateFormat("MMddHHmmss"); String time="bb"+format.format(date)+".xlsx"; response.setHeader("Content-Disposition","attachment;" + " filename=" + new String(time.getBytes("utf-8"), "ISO-8859-1")); } catch (UnsupportedEncodingException e1) { e1.printStackTrace(); } try { ByteArrayOutputStream oss =new ByteArrayOutputStream(); OutputStream os = response.getOutputStream(); work.write(oss); byte temp[] = oss.toByteArray(); ByteArrayInputStream in1 = new ByteArrayInputStream(temp); int n = 0; while ((n = in1.read(temp)) >0) { os.write(temp, 0, n); } os.flush(); os.close(); } catch(Exception e){ e.printStackTrace(); } }